What is the impact of LC_CTYPE on a PostgreSQL database?

What are the downside of using LC_CTYPE='C' over a specific
localization

The documentation mentions the relationship between locales and SQL features in Locale Support:

The locale settings influence the following SQL features:

Sort order in queries using ORDER BY or the standard comparison operators on textual data

The upper, lower, and initcap functions

Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions

The to_char family of functions

The ability to use indexes with LIKE clauses

The first item (sort order) is about LC_COLLATE and the others seem all to be about LC_CTYPE.
LC_COLLATE
LC_COLLATE affects comparisons between strings. In practice, the most visible effect is the sort order. LC_COLLATE='C' (or POSIX which is a synonym) means that it's the byte order that drives comparisons, whereas a locale in the language_REGION form means that cultural rules will drive the comparisons.
An example with french names, executed from inside an UTF-8 database:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "fr_FR";

Result:

 firstname
-----------
 béatrice
 bérénice
 bernard
 boris

béatrice comes before boris, because the accented E compares against O as if it was non-accented. It's a cultural rule.
This differs from what happens with a C locale:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "C";

Result:

 firstname
-----------
 bernard
 boris
 béatrice
 bérénice

Now the names with accented E are pushed at the end of the list.
The byte representation of é in UTF-8 is the hexadecimal C3 A9 and for o it's 6f. c3 is greater than 6f so under the C locale, 'béatrice' > 'boris'.
It's not just accents. There a more complex rules with hyphenation, punctuation, and weird characters like œ. Weird cultural rules are to be expected in every locale.
Now if the strings to compare happen to mix different languages, as when having a firstname column for people from all other the world, it might be that any particular locale should not dominate, anyway, because different alphabets for different languages have not been designed to be sorted against each other.
In this case C is a rational choice, and it has the advantage of being faster, because nothing can beat pure byte comparisons.
LC_CTYPE
Having LC_CTYPE set to 'C' implies that C functions like isupper(c) or tolower(c) give expected results only for characters in the US-ASCII range
(that is, up to codepoint 0x7F in Unicode).
Because SQL functions like upper(), lower() or initcap
are implemented in Postgres on top of these libc functions, they're
affected by this as soon as there are non US-ASCII characters in strings.
Example:
test=> show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8
(1 row)

-- Good result
test=> select initcap('élysée');
 initcap
---------
 Élysée
(1 row)

-- Wrong result
-- collate "C" is the same as if the db has been created with lc_ctype='C'
test=> select initcap('élysée' collate "C");
 initcap
---------
 éLyséE
(1 row)

For the C locale, é is treated as an uncategorizable character.
Similarly wrong results are also obtained with regular expressions:
test=> select 'élysée' ~ '^\w+$';
 ?column?
----------
 t
(1 row)

test=> select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column?
----------
 f
(1 row)


MySQL - Force index degraded performance over time

Without any index hint, the Optimizer uses statistics to decide which index to use.  And it updates the statistics in a reasonably (but not always) intelligent manner.
With a hint, such as FORCE INDEX, it ignores the statistics and uses the given index if at all possible.
Probably the FORCE was beneficial at first.  But later the data in the table shifted due to new rows, changing distribution of values, or the phase of the moon.  And now that index is no longer optimal.
Your experience is why I warn people against using hints, saying "it may help today, but hurt tomorrow."
If you would like to discuss the specific query, please provide SHOW CREATE TABLE and EXPLAIN SELECT...  It may be that a different composite query, without the hint, will work even better than what you had or have.
More on the history...  The Optimizer was heavily worked on in 5.6, 5.7, and 8.0.  A big change was moving to a "cost-based" model.  In theory, it produces better results.  In practice, it rarely changes anything.  And, as perhaps your case shows, it sometimes hurts.  The presumption is that the cost-based model is usually better than the previous algorithm.
The statistics are updated when any of these happens:

The table grows by 10%.  (This partially addresses your coworker's theory.)
You run ANALYZE TABLE.  In rare cases, this is a "fix" for a bad index choice, but it may or may not stick.
Some flavors of ALTER.


Cannot create relationship: Error:Missing index on column(s)

Ahoj Hynku,

zkus tam přidat CONSTRAINT id nebo/a FOREIGN KEYids referencí na druhou tabulku s 'Id' sloupcem.

Hi Hynek,

You can try add CONSTRAINT id or/and FOREIGN KEYid with refernece on second table with 'Id' column.


One field vs multiple fields to reference ID values of dependent information

Based on your update I understand your question better now. Table A is the more scalable, maintainable, and normalized solution that will lead to less data redundancy.
You can always join to the Breeds table to get the Parent Breed when needed (which may not always be the case). And if you ever have a reason to add a 3rd level (subcategory of a subcategory) then you don't need to change your schema at all to support this when going with Table A (and the Breeds Table) schema (as opposed to Table B where you'd need to add a 3rd column for Level 3, etc which defeats most of the purpose of the Breeds table). (E.g. You can get any level of relationships with a recursive query when using Table A schema.)

Based on my understanding of your schema, I think your Table structure is fine for the Parent-Child relationship as is. I just find the naming slightly confusing since Dog vs Lab isn't exactly the same thing, so to see them objectified the same way as "Breed" feels a little off.
Alternatively, as someone who is seeing the Table for the first time, I'd expect this table to have things like "Shepard" as the Parent under the Breed column and "German" and "Australian" as the children, since they're all breeds. And things like Dog and Cat would be in a different field called AnimalType.
But that's probably more my subjective opinion, I think for your goal this Table design makes sense, and maybe just thinking about a slightly different name for the "Breed" column could be a small change.


Will a delete then insert within a transaction cascade?

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.


MySQL - Add index to low cardinality text column?


For multiple mysql instance , fresh download required when one exist?

What i did is :

       i used existing my.cnf file and copy it in another location and edit
       some part of it :

         1. Commented #port=3306
         2. Added these lines at the end.
           [mysqld1]
           basedir ="C:\Program Files (x86)\MySQL\MySQL Server 5.6"
           port = 3307
           enable-named-pipe
           socket = mypipe1

           [mysqld2]
           basedir ="C:\Program Files (x86)\MySQL\MySQL Server 5.6"
           port = 3308
           enable-named-pipe
           socket = mypipe2


sql query to get results specific rows but count of all the rows of table

with the count(), I want to get the count of all the rows present
irrespective of the where clause.

This can be done with an independent subquery in the select-list, if it returns a single value, as in:
select name, email, (select count(*) from users) as "Total" from users WHERE ...

Example:
postgres=> select relname,(select count(*) from pg_class) as "Total"
           from  pg_class where relname like 'a%';

              relname              | Total
-----------------------------------+-------
 administrable_role_authorizations |   348
 applicable_roles                  |   348
 attributes                        |   348

Note that from the point of view of the relational model, this is an unorthodox trick . Normally each query is an answer to one question with a structure of result that fits exactly that question. Doing two queries would be right here.


Usage of NON Clustered Index in SQL Server

So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.

The updates will always work fine. Is this an update query you run very frequently?
Because in general (extra) non clustered indexes slow down inserts.
All your data is in the Clustered Index (CIX). The Non-Clustered Indexes (NCIX) are a copy of your Clustered Index, but in a order you specify generally in a different manner than your Clustered Index.

Update TableA SET Column3='' where Column1=''
the above Update was using Index 1st and then Key lookup to clustered Index
Update TableA SET Column3='' where Column3=''
This update was using 2nd index

In the first query, your server has to look where "Column1=''". The most quick way to do that is to use the NCIX that is sorted on Column1 and than look up the record in the CIX (that is probably not).
In your second query, it uses the other index because it is sorted on Column3

Based on the Update I thought I can delete 2nd index and update the first one with

Unfortunately no, when you include the column it is not used in sorting.

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC,
[Column3] asc
)

In this NCIX it is sorted on last, so it is probably not sorted well enough for the server to quickly find the record.
Look at this example where we sort on Column1, Column2, Column3:
Product      | Category   | Price
-----------------------------------
Apple        | Fruit      | 1.10
Apple pie    | Pastry     | 5.00
Apple Iphone | Electronic | 899.00
Apple Watch  | Electronic | 459.00
Bananas      | Fruit      | 1.50
Pear         | Fruit      | 1.25
Pineapple    | Fruit      | 2.00

See how the price is all over the place? and even the second column in the "index" isn't "sorted"?
The server has to scan the whole index/table to find the right price.
That's why it recommends a new Index on just Column3. But in my honest opinion, unless you run this query frequently do not create an index for this, more indexes make inserts and updates take longer (because they all need the latest values).


Is it possible to have a unique key condition based on another value?

Create a unique constraint on the 2 columns.

ALTER TABLE schemaName.tableName ADD CONSTRAINT
        uniTableName UNIQUE NONCLUSTERED
(ID, ENUM_VALUES)


Select unique value pairs based on two participants that occur more than once inside a table

CREATE VIEW uniqueness (id, User_id1, User_id2)
AS
SELECT MIN(id),
       LEAST(User_id1, User_id2) User_id1,
       GREATEST(User_id1, User_id2) User_id2
GROUP BY LEAST(User_id1, User_id2),
         GREATEST(User_id1, User_id2);

and then

INSERT INTO Chat_rooms (id) SELECT id FROM uniqueness;

and

INSERT INTO Participants (chat_id, user_id)
SELECT id, User_id1 FROM uniqueness
UNION ALL
SELECT id, User_id2 FROM uniqueness;

You may create temporary table instead of view

-- or --

You may insert view's source text into INSERT queries


Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

In a comment you said your web.xml contains:

<init-param>
 <param-name>DatabaseConnectionString</param-name>
 <!-- The string "${USER}" in the connection string will be replaced
      by the active username when making a connection. -->
 <param-value>
  <!--jdbc:hsqldb:mem:${USER}-->
  <!--jdbc:oracle:thin:@127.0.0.1:1521:xe/${USER}-->
  jdbc:oracle:thin:@localhost:1521
 </param-value>
</init-param>

The connection string you showed at the top of the question is commented out, so you're actually connecting with jdbc:oracle:thin:@localhost:1521. That isn't supplying a service name; but your listener.ora does have DEFAULT_SERVICE_LISTENER = (XE), so this should be the equivalent of jdbc:oracle:thin:@localhost:1521/XE. (I've tested this with 11gR2 EE and the parentheses around the XE in the listener.ora don't seem to matter).

But that doesn't quite seem to work as expected. When you changed that and set the user name explicitly:

jdbc:oracle:thin:system/system@//localhost:1521/XE

... the TNS-12514 went away and was replaced with an ORA-01017, invalid username/password. That means the connection string is now working and your system account's password is something other than system. With a valid username and password it looks like it should now work.

If the ${USER} substitution works as the comment in the web.xml suggests, and contains both the username and password, then making your connection string look like this ought to work:

jdbc:oracle:thin:${USER}@//localhost:1521/XE

But only if ${USER} is actually a valid username/password string.


SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?

Detach the database.
sp_detach_db @dbname = 'EXAMPLE'

Physically delete the log file from disk (this is crucial, but can be risky - see mrdenny's comment below).

Attach the database using the sp_attach_single_file_db stored procedure.
sp_attach_single_file_db @dbname = 'EXAMPLE', @physname = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf'


You could implement database mirroring.  Synchronous database mirroring provides a mechanism to ensure all writes on the primary mirrored databases are sent to the secondary machine prior to being committed on the primary.  This is designed to provide an RPO as close to zero as practically possible.

In the event the primary goes down for any reason, the secondary mirror will become the primary and resume serving clients once it completes the recovery process for the database in question.  Recovery can take a very short amount of time assuming you ensure the redo queue is being processed reliably.


See Configure the network packet size Server Configuration Option:

sp_configure 'network packet size', ...;

But this setting is not the same as MySQL's max_allowed_packet, is more similar to net_buffer_length. SQL Server's maximum request limit is 65,536 * Network Packet Size, which is closer to max_allowed_packet meaning.


Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

Yes it is the same thing. See section 3 which explains this term.

First lets get few terms straight.

Windows Server Failover Clustering

  A Windows Server Failover Clustering (WSFC) cluster is a group of
  independent servers that work together to increase the availability of
  applications and services. SQL Server 2016 takes advantage of WSFC
  services and capabilities to support Always On availability groups and
  SQL Server Failover Cluster Instances.

Always On Availability Groups (SQL Server)

  The Always On availability groups feature is a high-availability and
  disaster-recovery solution that provides an enterprise-level
  alternative to database mirroring. Introduced in SQL Server 2012,
  Always On availability groups maximizes the availability of a set of
  user databases for an enterprise. An availability group supports a
  failover environment for a discrete set of user databases, known as
  availability databases, that fail over together. An availability group
  supports a set of read-write primary databases and one to eight sets
  of corresponding secondary databases. Optionally, secondary databases
  can be made available for read-only access and/or some backup
  operations. An availability group fails over at the level of an
  availability replica.

  Failovers are not caused by database issues such as a database
  becoming suspect due to a loss of a data file, deletion of a database,
  or corruption of a transaction log.

Always On Failover Cluster Instances (SQL Server)

  As part of the SQL Server Always On offering, Always On Failover
  Cluster Instances leverages Windows Server Failover Clustering (WSFC)
  functionality to provide local high availability through redundancy at
  the server-instance level—a failover cluster instance (FCI). An FCI is
  a single instance of SQL Server that is installed across Windows
  Server Failover Clustering (WSFC) nodes and, possibly, across multiple
  subnets. On the network, an FCI appears to be an instance of SQL
  Server running on a single computer, but the FCI provides failover
  from one WSFC node to another if the current node becomes unavailable.

Item number 2 and 3 can be combined and item 1 is a prerequisite for implementing any combination. 2 alone, 3 alone, 2 and 3 together.

When you combine 2 and 3 you get:  Failover Clustering and Always On Availability Groups (SQL Server)

  You can set up a second layer of failover at the server-instance level
  by implementing SQL Server failover clustering together with the WSFC
  cluster. An availability replica can be hosted by either a standalone
  instance of SQL Server or an FCI instance. Only one FCI partner can
  host a replica for a given availability group. When an availability
  replica is running on an FCI, the possible owners list for the
  availability group will contain only the active FCI node. + Always On
  availability groups does not depend on any form of shared storage.
  However, if you use a SQL Server failover cluster instance (FCI) to
  host one or more availability replicas, each of those FCIs will
  require shared storage as per standard SQL Server failover cluster
  instance installation.

I would highly recommend reading this FAQ by SQLHA (Allan Hirt) which clarifies many basic questions when you combine these 3 items.


Storing a transaction sensitive constant in Firebird

I think you can use Database trigger which fires on TRANSACTION COMMIT. However, you need to react only to those commits caused by the "upgrade script" so you need to set up an flag to indicate it. For that you could set an context variable in USER_TRANSACTION namespace. Ie your script would look like this

RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', 1);
-- other statements
...
-- in the end of the script commit
COMMIT WORK;

Now the database trigger can check for the IncrementVersion context variable and act accordingly

CREATE TRIGGER IncrementDBVersion
ACTIVE ON TRANSACTION COMMIT
AS
BEGIN
  IF(RDB$GET_CONTEXT('USER_TRANSACTION', 'IncrementVersion') IS NOT NULL)THEN BEGIN
     -- increment the version number
     GEN_ID(GEN_DB_Version, 1);
     -- just in case clear the context variable
     RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', NULL);
  END
END

Here trigger uses generator (aka sequence) named "GEN_DB_Version" to "store" the version number.

If you use COMMIT WORK statements in your script you have to make the system more complicated, either set up additional flags to ignore those commits in the middle of the script or set the flag up right before final commit or perhaps some third way - it all depends on what "side effects" you want to use.


How to set Persian calendar,currency,numbers,date and time and text in oracle database?

The magic key word is NLS (National Language Support). It will define the dateformat, calender format etc.

You can query your current NLS-settings with this command:

SELECT * FROM nls_database_parameters;

If you want to change it in a session or an instance you can query the current settings using this views:

SELECT * FROM nls_session_parameters;
-- or
SELECT * FROM nls_instance_parameters;

You need to set this NLS settings, maybe the proper ISO as character set too.

NLS_LANG=ARABIC_SAUDI ARABIA.UTF8
NLS_LANGUAGE=PERSIAN
NLS_CALENDAR=PERSIAN

You have some possible solutions to setting the NLS. If you want to use it globally I would suggest to set it in the initialization parameters.

You can read more about setting the NLS in the docs.

Oracle 12c should support persian, as stated in this whitepaper in the section "New Locale Coverage".

By the way, if you need a larger multi language support, you may want to use AL32UTF8 instead of normal UTF8 language sets. You can read something about it here and here.


SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @table_name SYSNAME
    DECLARE @xml XML

    SET @xml = EVENTDATA()

    SELECT
        @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' +
                      QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
    FROM @xml.nodes( 'EVENT_INSTANCE') t(c)

    IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
    BEGIN
        RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
        EXEC sp_CreateReplTrigger @TABLE_NAME
    END
    ELSE
        -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
        RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc


MariaDB Galera Cluster wsrep_cluster_address not working

You don't provide much information, so I am inferring it from what you say and matching it with a common Galera/Percona XtraDB Cluster/MariaDB Cluster misconception and mistake:

  "shutdown the cluster for maintenance and tried to start it again, only the first node will start"

When you stop the cluster fully (something that shouldn't happen under normal circumstances), you need to start the cluster in reverse order. This means: the last node to be down must be the first to start. Why? Because if you are making changes on all nodes, the last one to go down is the one that will get more ahead of the other regarding the transaction UUID. When you shutdown a node normally, the other nodes survive and continue receiving updates. If you now start and create what it is essentially a new cluster from scratch, galera cannot know that the first node that you start is not the "freshest", because you are bootstraping it.

So in short, always check that you do not bootstrap the cluster on a node that is not the last to get shutdown. If you do not know it, here it is a guide on how to do it (along with other useful information on restarting a Galera cluster and other gotchas).

Hope this helps.


Best way to display number of overspent projects

I am using SQL Server 2012, but this, or something close, should work in MySQL:

USE Test;
GO
CREATE TABLE Departments
(
    Department NVARCHAR(255)
);
CREATE TABLE Projects
(
    Department NVARCHAR(255)
    , Project NVARCHAR(255)
    , MaxHours INT
);
CREATE TABLE Assignments
(
    Project NVARCHAR(255)
    , HoursWorked INT
);
GO
CREATE VIEW Budgets AS
    SELECT ProjectInfo.Department
        , COUNT(ProjectInfo.Project) AS NumberOfProjects
        , SUM(Projectinfo.OverBudget) AS NumberOfOverbudgetProjects
    FROM (
        SELECT D.Department
            , P.Project
            , P.MaxHours AS ProjectMaxHours
            , SUM(HoursWorked) AS TotalHoursWorked
            , CASE WHEN (SUM(HoursWorked) > P.MaxHours) THEN 1 ElSE 0 END AS OverBudget
        FROM   Departments AS D
            INNER JOIN Projects AS P ON D.Department = P.Department
            INNER JOIN Assignments AS A ON P.Project = A.Project
        GROUP BY D.Department
            , P.Project
            , P.MaxHours
        ) AS ProjectInfo
    GROUP BY ProjectInfo.Department;
GO
INSERT INTO Departments (Department) VALUES ('Marketing');
INSERT INTO Departments (Department) VALUES ('Sales');
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject1', 10);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject2', 20);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject1', 15);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject2', 25);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('SalesProject1', 14);
SELECT * FROM Budgets;

This returns the folloowing resultset, showing only the Marketing Department, which is over budget.


mysqldump: Error: 'Can't create/write to file

Perhaps you can change the owner with

chown -R mysql:mysql /var/tmp

Check to see if there is a [mysqldump] header in /etc/my.cnf. It may look like this:

[mysqldump]
tmpdir=/var/tmp

or perhaps under the [mysqld] header

[mysqldump]
tmpdir=/var/tmp

Try commenting it out and trying mysqldump again.


Recommendations for how I should create my table based on data given?

Can an equipment be part of multiple set's? ( assume: yes)
Can an item(one piece with one serial) be part of multiple equipments? (assume : no)
I would break the equip in parts at the serial level as smallest item.

Equip:
eqid
ssn
description
currentQty
totalQty

Item:
item_id
eqid
serial
location

Set:
setid
description?

Equip_to_set:
setid
equipid

With this model you have the location per unique serial item.
You have equip with multiple set assignments.
SSN and serial are optional.


Postgres cascades truncates to unrelated tables

I think TRUNCATE ... CASCADE doesn't do any reference checking on foreign key values, that would defeat the purpose. The mere presense of referential integrity constraints connecting attributes to users via accounts causes all three (and possibly more) tables to be truncated (which you would have noticed if accounts weren't empty).


Why is mysqlhotcopy slower compared to rsync?

rsync does not have any MySQL-specific gotchas to watch out for. rsync can copy literally anything with impunity.

mysqlhotcopy has to watch for opportunities to lock files which could delay actual start time for copying.

I wrote an earlier post contrasting rsync and XtraBackup along this line of reasoning

I also wrote a post on how to copy using rsync on a running mysql server with relative brief downtime.

Here is post I made in ServerFault

Here is still another post in ServerFault, with sample code !!!


RSync Database File Deltas in SQL Server 2008

I would not trust rsync to copy the ldf and mdf files for user or system databases, nor would I trust anything I hacked together (VSS or otherwise) in a production environment. SQL Server is very fussy about when (and if) things get written to the ldf and the mdf files. Software (rsync) that isn't designed with that in mind might not get it right, especially if it doesn't understand that the ldf files and mdf files need to be treated as an interrelated system of files. If that software doesn't get things right, nothing might be noticed until you failover, try to go live and have your databases flagged as suspect due to what SQL Server sees as data corruption. Even worse, "getting it right" might be dependant on how much load is on the system and you might not find the problem in a lightly-loaded test environment.

I have seen enough examples of people who thought that they were replicating their files but were not. They were left with corrupt files on the recovery site and with inaccessible backup files at the primary site. So, that meant no database for them.

In short, you are making an appointment for trouble.

If you had some sort of block-level replication technology like EMC's SRDF or were looking at shared-nothing clusters, that might be different. Those technologies interface with SQL Server and the clustering services that Windows provides in a way that your writes will be safe and your files will/should be consistent.

If my only disaster recovery option was a remote site that was normally down, I'd use log shipping and make sure that I had all of the pieces to restore the database(s) on the remote site. If you can't make the built-in log shipping do that, writing your own isn't that hard. I've probably written three or four (simplistic) log shipping systems from scratch in the last 14 years.

At a minimum, the key things you need are:
A full backup needs to be taken and copied over to the remote site.
Your tlog backups need to be taken and copied over to the remote site.
You need an automated method to restore that full backup and any relevant tlog backups. Ideally, this should be simple enough for someone else to do and/or simple enough for you to figure out at 3AM when your primary server fails and you are half asleep.

When you have an event, bringing up the other server will take longer because you would have so much manual stuff to do. That means that this isn't as good as simply implementing regular log shipping. You will need to test this periodically, as well.

(Of course, you need to worry about other things too, like jobs, packages, login and user synchronization, changing DSNs on the web servers during a failure, etc. If you have a large environment and a serious disaster, like the loss of your primary data center, you will be trying to do this when IIS guys, file server guys, network guys and whatever else guys are trying to bring their stuff up too.)

If it were me, I would be agitating for a warm standby server at the remote site. That would make (standard, out-of-the box) log shipping easier and database mirroring possible. It sounds like you have tried that.


The default roles all have db_ and then something descriptive of the roles permissions. I would stick with that.


Does "Heterogeneous" just refer to querying two different database engines in this context?

Basically, yes. A "heterogeneous" query is executed on a "heterogeneous linked server". All queries to this linked server will be heterogeneous.

And "heterogenous" is a subset of distributed queries

  Distributed data stored in multiple instances of SQL Server.
  Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

But "heterogeneous" pops up every now and then:

See linking servers on MSDN

  The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

sp_addlinkedserver

  A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.

And an MSDN example of querying Active Directory in "Joining Heterogeneous Data"

And MSDN again "Heterogeneous Database Replication"


Such a feature exists in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interesting question so I googled a little bit and came across an interesting article on postgresonline.com.

They show an approach that selects the columns directly from the schema:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark'
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

You could create a function that does something like that. Such topics were also discussed on the mailing lists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties


Upgrading 5.6 to 8.0 without losing any data

backup your data to sql dump file and upgrade by incrementing major version. 5.6 to 6 to 7 to 8.


Merging continous date range using T-SQL

Assuming hours are added to table in chronological order. If not, sort them before inserting them!

First we need to relate each record to successive records.
You say that is done by matching their hrs1 & hrs2, and making sure the initial record ends one day before the next starts.
Your sample data only includes two records that are sequential, so I added a third (startdte='1995-01-01') to show this works in such a case.
If we only expected two records to be part of the same sequence, we could just use a join.  But if an indefinite number
may be part of the same sequence, then we must use recursion.  In this case, a CTE.  The first part of the UNION gets the parent records,
and the second part of the UNION gets all children.  To uniquely track which sequence is which, we use the 'chain' field as
a sort of bread crumb trail.  Finally, we query the root ID (which we tracked as we went along) and the 'chain' field to find the
first and last record in each sequence.  Then we join it all back to the original data.
Last, we clean up null 'ENDDTE's for full time records.

declare @hours table (
ID int IDENTITY(1,1), -- we need a unique identifier to easily reference each row.
RootID int,
STARTDTE DATETIME,
ENDDTE DATETIME,
hrs1 DECIMAL(6,2),
hrs2 DECIMAL(6,2))

INSERT @hours (STARTDTE,ENDDTE,hrs1,hrs2) VALUES
('25 MAY 1990','30 NOV 1994',18,36),
('01 DEC 1994','31 DEC 1994',18,36),
('1 JAN 1995','1 FEB 1995',18,36),
('01 JAN 2000', NULL,20,36),
('16 JUN 2002','20 APR 2007',18,36),
('10 OCT 2008', NULL,20,36)

;with
hours (chain,recordsInChain,ID, ParentID, RootID,STARTDTE,ENDDTE,hrs1,hrs2) AS (
    select
        convert(varchar(1000),convert(varchar(10),hc.id)+','), -- This gives us a snapshot of the entire series of records to which this record is related.
        1, hc.ID, hc.ID ,hc.ID, hc.STARTDTE,
        hc.ENDDTE,
        hc.hrs1, hc.hrs2
    from
        @hours hc
        LEFT OUTER JOIN @hours hp on hc.hrs1=hp.hrs1 and hc.hrs2=hp.hrs2 and hc.STARTDTE = dateadd(dd,1,hp.ENDDTE) -- Record starts on the day after previous record ends.
    where hp.id is null -- We want root records.  Not records with parents.
    UNION ALL
    select convert(varchar(1000),chain +convert(varchar(10),hc.id)+ ','),recordsInChain +1,  hc.ID, hp.ID ParentID,
        convert(int,left(hp.chain,charINDEX(',',hp.chain)-1))
        , hp.STARTDTE, hc.ENDDTE, hc.hrs1, hc.hrs2
    from
        @hours hc
        INNER JOIN hours hp on (hc.hrs1=hp.hrs1 and hc.hrs2=hp.hrs2 and hc.STARTDTE = dateadd(dd,1,hp.enddte))
        -- We need the end date for fulltime records, but this next line is not allowed in the recursive part of a CTE so we will have to get it later.
        --LEFT OUTER JOIN @hours hFullTime on hc.ENDDTE is null and hFullTime.id = hc.id + 1 and hFullTime.STARTDTE > hc.STARTDTE
)
select hS.startdte,isnull(hE.ENDDTE,hFullTime.STARTDTE-1) enddte,hs.hrs1,hs.hrs2 --,hs.*,hE.*,isnull(hE.ENDDTE,hFullTime.STARTDTE-1)
from
    ( select rootid, max(recordsinchain) recordsInChain from hours  group by rootid ) hRange
    inner join hours hS on hRange.RootID = hS.ID
    inner join hours hE on hRange.rootID = hE.RootID and hE.recordsInChain=hRange.recordsInChain
    -- Now we will get the end date for full time records, since we couldn't do it in the CTE.
    LEFT OUTER JOIN @hours hFullTime on hE.ENDDTE is null and hFullTime.id = hE.id + 1 and hFullTime.STARTDTE > hE.STARTDTE


How can I return column names in dot notation in MySQL?

You first try was very close. Just prepend the table_name and a dot:

INSERT INTO columns(name)
SELECT CONCAT(TABLE_NAME,'.',COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='contacts' or table_name='companies';

Give it a Try !!!


E: Version '2.0.25' for 'mongodb-10gen' was not found while installing this specific version

if anone lands here in future then follow these steps for installing OLDER versions ( 2.4 and earlier versions )

  sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

        echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

        sudo apt-get update

    apt-get install mongodb-10gen=2.0.2

replace 2.0.2 with your required version.

for Versions 2.6 and above replace last step with this format

sudo apt-get install -y mongodb-org=2.6.9 mongodb-org-server=2.6.9 mongodb-org-shell=2.6.9 mongodb-org-mongos=2.6.9 mongodb-org-tools=2.6.9

replace 2.6.9 with your mongo version ( only 2.6 and above )


Firstly, rows_read are logical. The metric shows the number of rows the engine had to retrieve (from the buffer pool if available) and evaluate against SARGable predicates, if any. It is in contrast with the rows_selected metric that shows how many rows matched all query predicates and were therefore returned to the application.

If your query does not use any indexes, rows_read will in general be equal to the total number of rows in all tables that are involved. If an index is used, rows_read will be reduced to the number of rows that can be found matching whatever predicates by the index scan.

Subsequently, if a unique index scan does not find any matching key values, rows_read for that particular table will be 0. Other optimization techniques, such as the use of referential integrity and check constraints or partition elimination, might also allow the engine to satisfy the query without actually evaluating any rows. For example, if you have an int_column integer check (int_column > 0) and a query with where int_column < 0, the engine doesn't need to read any rows to satisfy that query.

The ratio of rows_read to rows_selected is one of the general measures of efficiency of your SQL queries; the lower it is, the better: it means the server does not waste time reading rows that do not match  query predicates.


You can force SQL Server to use the nonclustered index:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name))
WHERE  Name = 'NAME10';

dbfiddle here, plan here
Without the hint the query optimizer considers the plan with the index more costly for this small amount of data, but you can get this plan by increasing the number of rows in your table. I managed to achieve it with 600 rows:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here
If you want to get just the Index Seek, your query should return only Name, so that data is pulled only from the index.
SELECT Name
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here, plan here
But if other columns need to be returned too, you could use a covering index:
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name
    ON dbo.TestIndexSample(Name) INCLUDE (Code, ModifiedDate);

Obviously the index size will increase, but nothing important for this number of rows.
Now your current query will use an Index Seek:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here, plan here


For the Grid Infrastructure Management Repository (GIMR) a.k.a. MGMTDB (if you have it), datapatch is automatically executed as part of patching.
For regular databases, execute datapatch from the database home.


Upgrading from SQL Server 2008 R2 SP1 to SP3

This seems to be a pretty exhaustive list in your question. Service Packs are generally speaking safe affairs - but every so often things can go wrong.

Ideally you would run on a test instance and make sure things are good. Since you can't do that - making sure you take backups is key.

Stopping the SQL Server agent isn't necessary - but if you have many jobs and are trying to make sure a job doesn't kick off just before you start, etc - that is a good plan. Again not necessary, though.

I also like to read the notes on the CUs released after a Service Pack and look for details about potential issues. It doesn't happen all the time, but sometimes a SP ends up breaking something and that then gets fixed in a later CU.  In your case you are going with a fairly late release because Microsoft hasn't been generally releasing CUs - only security updates for that product with the current release now 3 versions higher. You can read the list of versions and builds and service packs and fixes post service packs here.


Where does Oracle store message properties for AQ messages?

General process to create an AQ

create an Object type
create a Queue Table
create the Queue

The queue message (1) is stored in a queue table (2).  You can view a list of queue tables by looking at ALL_QUEUE_TABLES.


Whenever you use functions on columns that are associated to indexes, the engine MIGHT NOT use the indexes, due to the computation it needs on top of the column values. If you create a function -based index (like you made), it's being created with the already computed values from the function. The caveat with this, is that this index is now usable only when the column is also being used with the function.


Deleting aged rows from postgresql table which don't have related entries in another table

Use a subrequest : this should work

DELETE FROM links_link WHERE id NOT IN (select answer_to from links_publicreply ) AND `submitted_on` < CURRENT_DATE-1 ;

You can have of course multiple subrequest chained.


DECLARE
 CURSOR t1 is
     select table_name from dba_tables where owner='SAPIENS_TESTE' and num_rows > 10000000;
BEGIN
 FOR t IN t1 LOOP
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SAPIENS_TESTE', TABNAME => t.table_name, partname => NULL, granularity => 'GLOBAL', CASCADE => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
 END LOOP;
END;
/


Insertion of data violates the foreign key constraint

In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.

a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.

For an example of using deferrable constraints, see my answer in this question:
  Implementation of a many-to-many relationship with total participation constraints in SQL.

b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:

with
  insert_emp as
  ( insert into employee
        (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
    values
        ('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
    returning
        *
  ),
  insert_dep
  ( insert into sp0090.department
        (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
    values
        ('Headquarter', 1, '888665555', '1981-06-19')
    returning
        *
  )
select *
from insert_emp as e
  full join insert_dep as d
  on  e.dno = d.dnumber
  and e.ssn = d.mgr_ssn ;


Release MySQL Memory and Performance Tunning

MySQL will not release global buffers after they are used (innodb_buffer_pool, key_buffer_size, query_cache_size, etc). It will release memory taken by per-thread buffers (join_buffer_size, sort_buffer_size, etc) but only after the thread is removed and not in the thread_cache.
The innodb_buffer_pool_size can be tuned dynamically in 5.7, but it is a very invasive operation:

When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn during an operation to decrease buffer pool size. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.

Is the additional buffer pool worth it? This query will give you an idea of the current maximum memory that will be used by the InnoDB buffer pool:
SELECT ENGINE, SUM(DATA_LENGTH)/1024/1024/1024 as DATA_LENGTH_IN_GB,
    SUM(INDEX_LENGTH)/1024/1024/1024 AS INDEX_LENGTH_IN_GB,
    SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as TOTAL_IN_GB
FROM information_schema.TABLES
WHERE ENGINE IN ("InnoDB")
    AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE
ORDER BY DATA_LENGTH DESC;

However, that is the total InnoDB size of data+indexes. The working set is what is going to matter during peak times, so this will include your most active tables/rows. If your working set already fits in memory, allocating more is not going to buy much for you. If it doesn't fit in memory, you're going to want to increase the buffer pool regardless of peak/non-peak traffic.
I would recommend reading the internal details section of the MySQL documentation of the InnoDB Buffer Pool as a great primer.
As for the rest of your configuration, the only thing I will say is that your temp_table_size=333M is not doing what you think it is, and is likely too high.
First:

The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.

This means, with max_heap_table_size defaulting to 16MB, the internal memory tables are going to be converted to on disk temporary files long before reaching 330MB.
Second, you might have increased temp_table_size to avoid temporary on disk table usage. But, there are scenarios where MySQL can never use in-memory temporary tables:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

Presence of a BLOB or TEXT column in the table

Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes for binary strings or 512 characters for nonbinary strings. (Before MySQL 5.7.3, the limit is 512 bytes regardless of string type.)

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

The SHOW COLUMNS and the DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

I would recommend setting temp_table_size and max_heap_table_size to 32M or 64M (max) and then examining queries for some of the conditions mentioned above. If you can't avoid on-disk temporary tables, hopefully your cloud provider puts the tmpdir on SSD.


Postgresql, commas in string_agg function

If you want to generate JSON, it is easier to use JSON functions for that, e.g.:

string_agg(distinct jsonb_build_object('name', param_name, 'value', value)::text, ',')

Or create a proper JSON array of the result:

jsonb_agg(distinct jsonb_build_object('name', param_name, 'value', value))

Online example: https://rextester.com/EPOX97933


@OneToMany mapping but only one is needed

Plan A:  Have a flag in the T&C table saying which is the latest.  This is clumsy to maintain, and may not meet the criteria of some entity being held by an older T&C.

Plan B:  Move the antiquated entries to another table.

Plan C:  The link (JOIN) from entity to T&C involves 2 columns:  which T&C, plus which version of it.  This may be the best version.


How do I change the DEFINER of a VIEW in Mysql?

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql


the role of " Intent lock " in transaction and concurrency

It is not that simple to understand without making your hands dirty and digging much deeper into the SQL Server locking. And yes, you must take into account the locking escalation as it is a completely different approach and while sometimes could be very useful, it has its own negative consequences

I would suggest reading All about locking in SQL Server where you can find a lot of details, explained in a simple and concise manner, that allows even the beginners to process this rather complex thing like SQL locking


What is the Best Database Design for multiple form which have some similar column, but some of it does have extra column that other form does not have

You have several options:

Create one table with lots of columns

This is what Gerard H. Pille suggested in the comments: create a single table, with all possible columns for all possible processes. Where a process does not have a value for a given column, set the value to NULL.

Pros:

Relatively easy to query and understand

Cons:

Meaning of NULL in a column becomes questionable: Is there no defined value for the column for this process, or does this column not apply to this process.
Table may require update with new processes, or changes to existing processes.
Many DBMSes have a maximum number of columns per table; if you have enough processes and unique columns per process, you could hit that limit.
More difficult to enforce referential integrity at the DB level, especially if certain columns should not be NULL for certain processes, but aren't used in every process.

One table per process

Here, you would have a different table for each process. These tables would presumably have some columns in common in every table; some that only appear in some of the tables; and some unique to a given table.

Pros:

Meaning of NULL in any column is clear.
Enforcing referential integrity is relatively easy.
Less likely to have problems with hitting maximum number of columns.
Easy to query within a given process.

Cons:

Have to create a new table for every new process.
Changes that impact all processes may require the same basic updates to all process tables.
Somewhat painful to query data from all processes.
Harder to maintain a unique global process ID, without a central table to work off.

Base process table, with EAV table

Create a base Process table to store the data common to all processes (proc date, time in/out, qty start). Then, create a table to hold all other attributes for each process. This table would have three basic columns:

the ID of the base process record (a foreign key pointing to the base Process table)
The name or ID of the attribute being stored (e.g. mc#, yld, epotek batch #)
the actual value for the given attribute

Pros:

Only store attributes that belong to the base Process table's process type
Changes to base process confined to one table; changes to other processes require no DB structural changes
No issue with hitting maximum number of columns.

Cons:

requires more disk space to store (in most cases)
More difficult to query, especially if WHERE clause needs to check values of several different attributes stored in EAV table
More difficult to enforce data integrity at the DB level (to ensure that EAV attributes are set for every base process row of a given process type, you basically need a metadata table that defines which attributes are required and which are optional, and you have to check that after each insert/update of a relevant base Process row is complete, or some similar process). If you do want to allow multiple entries for certain attributes, it becomes even more difficult to ensure your distributed Process row is valid.

Hybrid approach

Create a base process table with all column common to all processes. Create full or partial process-specific tables, with columns unique to a given process. you could even allow an EAV table for attributes that are always optional, or possibly that are new to the system.

Pros:

This can help prevent problems with the number of columns in a table.
Changes to the base process data happen in one place.
Maintaining data integrity should be easier.
Most querying is relatively simple.

Cons:

Have to create a new table for every new process.
Changes that move columns that were previously present in some process-specific tables to the base process table require a fair amount of maintenance work.

Other considerations

If you have a large number of fields that are rarely needed for sorting/searching, but just for display in reporting, then storing certain values as JSON (basically, a data structure materialized as a string value) may work well. Some DBMSes have been extended to explicitly handle JSON data, and even to allow it to be indexed.

Also, some databases may allow some sort of table inheritance. I don't know much about this, but if you have a lot of processes that are the same except for one or two unique data fields, this might allow you to set up a series of table types, all built off your base table; this could make maintenance of a "one table per process" solution easier, if a change to the base process table type would essentially flow through all the process-specific tables.

All well and good. However, in practice, the details of your specific application and of your specific data will really drive this.

If most columns are present in most processes, then a single table with a lot of columns may make a lot of sense.

If your processes are frequently in flux, a base table plus an EAV table may be the only sane solution; otherwise, you may find yourself constantly needing to alter your databases structures.

If the number of rows you'll have for certain processes will be much more significant than the rows for other processes, then it may make sense for the more significant processes to have all columns in the base process table, and to use extra tables or an EAV for the processes that will appear less frequently.

Note that different DBMSes will have their own strengths and weaknesses; some may perform better than others for a given set-up.

You also have to take your application into account. If you're already going to have to make a specific form/page for each process (reusing elements where possible, of course), then the idea of one table per process may make very good sense; If, on the other hand, you'll have a central form with the data common to all processes, and will simply list each additional attribute and value at the bottom of the common form, an EAV table would match your structure logically.

Ultimately, what's best for you will come down to the specifics of the problems your application is intended to resolve; the amount of time you expect to spend in maintenance on it; the size of maintenance windows available; the performance needs of your specific situation; and your (or your company's) preferred coding style and approach.

Obviously, we haven't seen all of your processes. If, however, almost all possible columns are used by multiple processes, and if there are no more than two or three non-common columns per process, and if your set of processes is well-defined (so new columns are unlikely to be needed), I would be inclined to use a single Process table, and have all the columns in it. Again, note all the caveats in the preceding sentence, and realize that's a personal preference - others might pick a different direction.


Replication successful but slave skips certain queries

--replicate-ignore-db applies only to the default database (determined by the USE statement). Because the ads database was specified explicitly in the statement, the statement has not been replicated.

My advice is to change the context with a USE instead of explicitly specify the database in the statement.

Max

EDIT (for questions)

1.

  Ok, got it but then what should be the my.cnf values if I want to log all databases on the master but selectively replicate on slave (like ignoring test and replicating ads)?

Note that your syntax is correct, all statements will be written on binary log (master) and the slave will ignore the mysql and test databases BUT YOU SHOULD EXIT THE CONTEXT mysql and test before run queries on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test: IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is toto: REPLICATED because of your replicate-wild-do-table and toto is not ignored by a replicate-ignore-db

USE toto;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

2.

  Looks like "replicate-wild-do-table" means "replicate only this db and
  ignore everything else".

replicate-do-table=ads will replicate statements on ads database (your context should be ads, USE ads;) and ignore the statements on other databases:

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

replicate-wild-do-table=ads will replicate statements on ads database even if your are in an other context (test for instance), and ignore the statements on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : REPLICATED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );


how to handle so much multiple value strings

To answer this question best we would need to a whole lot more specifics about what you are doing and what the kinds of value are going into each of the fields.

In general though since more values can be added to each set of possible values, a seperate table is advisable.  Unless the fields have overlap in their possible values it would be better to have each set of possible values as a seperate table.  In Oracle these could be Index Organized Tables.


What is the strict difference between key and constraint in SQL Server?

A unique constraint and a unique index are physically implemented the same way and have the same end result. Since a unique constraint is actually physically enforced via a unique index, and since both can impersonate a primary key in terms of relationships with other tables, this is why you will see it under "keys" - even if you say ADD CONSTRAINT you've still created a constraint that acts and smells like a key.

What people tend to do is create them based on the logical thing that represents their intention. The typical guidance I see is as follows:

Is it being created primarily to enforce uniqueness? Then it's a constraint.
Is it being created primarily to improve performance of certain queries or operators? Then it's an index.

These aren't hard and fast rules, and of course you may want to use different rules. The salient part of these rules, though, is not where these things appear in Object Explorer, but rather what the script says about the intent (ADD CONSTRAINT ... UNIQUE vs. CREATE UNIQUE ... INDEX).

"Constraint" and "key" as words themselves overlap, but they are not interchangeable. A NOT NULL or DEFAULT or CHECK constraint does not relate at all to a key, and in fact two of those effectively violate the definition of a key. On the flip side, any type of key you can create fit the definition of a constraint, since that word is far less specific.


Add credentials & set a specific role for a database

See the Database Security Tutorial on ToadWorld (originally SQLServerPedia)


Oracle expdp command executed in a command file not honoring %U instead using _U without a sequence

You can escape the % sign to stop Windows swallowing it before Oracle gets a chance to see it:

... dumpfile=TEST_20190906_%%U.dmp ...

As a quick test, in a .cmd file:

@echo expdp ... dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log
@echo expdp ... dumpfile=TEST_20190906_%%U.dmp logfile=TEST_20190906_expdp.log

then running that from a command prompt shows:

expdp ... dumpfile=TEST_20190906_U.dmp logfile=TEST_20190906_expdp.log
expdp ... dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log


Archiving data in SQL Server with stored procedure

What do you mean by archive? Do you want to delete the data?

Moving it to another db or server will still take up the same space. It may improve performance but you're just moving the problem around.

Do you need the data in its original form? Can you aggregate the data to reduce the number of rows?

If you do move the data to another table you may find a clustered columnstore may give better compression and save disk space.

Personally i would be using ssis and not  a sp using linked servers. As you have a primary key id be using that as a way of identifying what has been inserted and then can be deleted from the original table. An index on the column you are using for date range would be extremely beneficial. Id also consider using smaller batches such as one days worth at a time.


Sometimes this is called "a cycle" in database design, and while it is not forbidden (because as the commenters point out, you must take into account your purpose and business problem), it is considered a red flag for many designers.

Here is a related Stack Overflow question that attempts to point out why this can be a red flag: https://stackoverflow.com/questions/8115748/why-should-i-avoid-loops-when-designing-relationships-for-a-database


pgsql slow query on table of 500 million rows

For postgres, unlike other RDBMS, SELECT COUNT(*) ... from an unindexed table means a full scan of the table. So the first run is purely IO bound. If you want this query - consider adding some indexes to the table. (Without knowing the query I can not tell, what indexes you may need).

Another option - configure postgres to better utilize the RAM you have.


you get an impression of the performance looking at the comparison with a real column store:
https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout


Is there a way to store a PostgreSQL database directly on a block device (not filesystem)?

[Is it] possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

Nope.

PostgreSQL would have to implement its own file system to support this. It lets the operating system do that, on the theory that the OS will probably do it better.

The project tries to avoid duplicating functionality the operating system already provides. That's one of the reasons why it performs as well as it does; when the kernel folks make improvements, PostgreSQL gets them for free.

Of course, when the kernel folks add performance regressions and bugs we get those too, but that's true even if you use raw block devices. We're just exposed to more filesystem and memory management level differences too.

There are a lot more smart kernel hackers with a lot more time than there are PostgreSQL hackers. So letting them deal with the complexities of buffer eviction, NUMA scheduling, dirty page write-back, etc seems like a no-brainer and it's worked out well so far. Mostly.

  If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?

Oracle. It is virtually its own operating system; the Emacs of the database world.

MySQL with InnoDB can create InnoDB segments on raw disk partitions.

I know it's been looked into for SQLite but I don't know if anyone cared enough to finish the work.

  backup can be simpler

I question your assertion that using a raw partition makes backups simpler. I see few advantages and plenty of disadvantages to relying on raw block devices.


Trouble with differential backup - full backup not being recognized

You probably have a full backup before the ones that fails, like a snapshot backup or a VDI backup). Check the backup history tables in msdb and the errorlog file for clues.


Associate SQL Server Queries with Databases

You have to concentrate on Memory, Disk IO and CPU utilization to balance out your environment.

Best is to start collecting and dumping the results in a utility database and to give you a good baseline using sql agent on a scheduled basis.

Alternatively, you can use Performance data collector (applies to sql server 2008 and up)

MEMORY:

You can look into buffer pool usage (what databases are occupying more space in buffer pool ?)

From :Performance issues from wasted buffer pool memory

SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

DISK IO:

In terms of Disk IO, you need to move away the mdf/ldf files having the most disk IO to a better/faster LUN/Array :

From : How to examine IO subsystem latencies from within SQL Server

SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

Also refer to Leveraging sys.dm_io_virtual_file_stats

CPU:

From : Brent's answer

SELECT total_worker_time/execution_count AS AvgCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration
, total_elapsed_time AS TotalDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1
, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS txt
, query_plan
FROM sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 1 DESC

A gold mine of DMV related queries : SQL Server Diagnostic Information Queries for September 2014

For completeness, if you are using sql server 2012, you can use the system health reporting dashboard for Visualizing sp_server_diagnostics results.


Primary key type change not reflected in foreign keys with MySQL Workbench

Actually cascade updates for data types are not supported by Workbench.

One workaround, is to define User Data Types, use those in your tables, and if necessary, update the User Data Type. That will generate the correct DDL.

I usually create User Data Types for PrimaryKeys, Name, Description, Money, etc.

In your case will not work, as you should do that before creating the tables.
Now, if you must replace it in all your tables, do it for a User Type.

Other workaround for you will be to generate the database as is, go with the sugestion above, and then re-import the model to continue design.


I would take following approach.

From left side of the relations we see 3 primary keys:

Key AB with AB->CDE relation is represented in LAB table.
Key C with C->D relation is represented in LC table.
Key D with D->E and D->B relations are represented in LD table.

Does it make sense?

CREATE TABLE LD (
  D INT,
  B INT, /* B can be omitted here, and derived from AB */
  E INT,
  CONSTRAINT LD_PKEY PRIMARY KEY ( D )
);

CREATE TABLE LC (
  C INT,
  D INT,
  CONSTRAINT LC_PKEY PRIMARY KEY ( C ),
  CONSTRAINT LC_D_FKEY FOREIGN KEY ( D ) REFERENCES LD

);

CREATE TABLE LAB (
  /* Attributes D and E are dependent on C, so we don't store them here */
  A INT,
  B INT,
  C INT,
  CONSTRAINT LAB_PKEY PRIMARY KEY ( A, B ),
  CONSTRAINT LAB_C_FKEY FOREIGN KEY ( C ) REFERENCES LC
);


Should there be a temporary table to hold Order and OrderItems?

I personally don't see an advantage to separating them, especially given that you will probably have a primary key on the Orders table that the OrderItems table will need to reference.  I normally handle this by having a nullable column for "closed" and/or "voided" that is stamped with now() on the appropriate event but left NULL until then.  If you're concerned about having too many open orders, you could have a garbage collection script that runs during off-peak hours to remove them.


Do DB permissions flow from primary server to secondary server in SQL log shipping?

The quick answer is yes, but only for the user permission on the DB, if you got login and user permission on the server-side, you need to copy these ones to the secondary server to avoid the possible issue in case you do a fail over to the secondary server


How to minimize the need for recalculating index on SQL Server?

I asume by "recalculating" you are referring to completely rebuilding an index. SQL Server will never completely rebuild the indexes unless you explicitly tell it to, so you will not see random pauses in performance due to it deciding to kick off a rebuild.

Each index does increase the amount of work that needs to be done for a given INSERT or UPDATE operation because a small portion of the index needs to be updated but this is a small portion: something of the order of up-to-10 pages affected for 500M rows (assuming the index is over a small datatype such as an INT, DATETIME or UUID - an index over a long string type for instance will require a little more work as less entries fit in one page, but it will still be similarly little work relative to the whole index's size). During these small updates due to INSERT and UPDATE operations there are no huge locks unless a table lock was required for something else in the transaction (and even if there are, the smallness of the operation per row means you'd only see the lock having significant effect if acting on many rows).

Rebuilding a whole index very rarely needs to be done, and will only be done if you request it explicitly. You can reduce the affect of index rebuilds if you have Enterprise Edition by using online index builds - there will still be a time were the table is locked as the newly generated index is attached to the table but this will be small compared to the time for the whole index build.


Using concat inside a stored procedure parameters

The correct solution is not to try concatenating quotes around the argument.  That is a very naive approach, and leads to SQL injection vulnerabilities.

The QUOTE() function solves this, when it's needed.

However, you do not need these things to be quoted at all in the queries you're running.  Worse, adding literal quotes to these values is likely to get you wrong answers.

Additionally, you are doing unnecessary work by wrapping stored functions inside scalar subqueries.

IF 2 = (SELECT 2) is obviously a verbose way of saying IF 2 = 2 but in your examples, you're doing the same thing... IF(SELECT stored_function_call()) ... is an equivalent but less lightweight version of IF stored_function_call()  ...

Corrected and simplified examples:

IF search_number_in_r4(@the_number) = 'true' THEN
...
CALL register_lookup_r4(@the_id,@the_number);
...
ELSEIF search_number_in_r4(@the_number) THEN ...


Can a username and password be hard-coded in a system DSN

A System DSN is stored in the registry and does not have the ability to store a password.  If you want to store the password, you'd have to use a File DSN and that would require changing the CRM to use the file DSN which you say is not possible.  Usually applications that use a System DSN, rely on SQL Server authentication of the users for their security.  When the application opens it should prompt the user to login, and this is the username and password that is used with the System DSN information to connect to the database server.  If the DSN uses Windows authentication and you are eliminating your Windows Domain, you are stuck.  You would have to change the CRM to make it work a different way.


How do hierarchical databases records link together?

My experience was with IBM IMS. The root held file offset pointers to locate its children, which in turn held pointers to their children, and so forth. The nearest equivalent in the relational world is Row Identifiers (RIDs) which are sometimes used to link the leaves of BTree indexes to the table row.

The analogy with Windows file system is not a bad one, in terms of the nesting and how access to lower items is through higher ones. The idea of a table doesn't really work, however. It may be better to think of it like a JSON document in a NoSQL store that has been shreaded by the DBMS and then internally linked through file pointers.


Are you sure you have the ALTER ROUTINE privilege? See if you can create an own procedure and alter that.


Create Random Number up to 50 Digits and Store in Varchar

Here's one approach (demo).

Create a view as CRYPT_GEN_RANDOM won't be allowable in a function

CREATE VIEW dbo.LongRandom
AS
  SELECT CONVERT(VARCHAR(500), CRYPT_GEN_RANDOM(500), 2) AS random_hex_string

And then call it in a function and Replace all A-F with empty strings. The below uses TRANSLATE to map all B-F to A and a final REPLACE to remove all the As.

You could also simply use 6 nested REPLACE (if you are on a version < 2017 you will have to anyway)

CREATE FUNCTION dbo.reallyBigRandInt(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT LEFT(REPLACE(TRANSLATE(random_hex_string,'BCDEF','AAAAA'),'A',''), @Length)
         FROM   dbo.LongRandom)
  END

The string returned from the view is 10 times longer than the maximum final string required to make it extremely unlikely that removing these characters will leave insufficient characters.

Then select from that

SELECT dbo.reallyBigRandInt(50);

NB: You could also forgo the string parsing and just use CRYPT_GEN_RANDOM to generate three bigint that you glue together (without minus signs) but this is less random unless you are careful.

The maximum bigint is 9223372036854775807 so clearly there is a lower probability that the leftmost digit will be 9. Similarly there is a greater probability that the second character will be 1 or 0 than any other digit. As it is possible for the second character to be 3 only if the leading character is 0-8. Similar issues exist for the other positions but decline in importance as you move rightwards.

If this isn't a problem for you you can concatenate the right most 17 digits of three bigints together to get a 51 character numeric string and then cut that down to the desired length.

CREATE VIEW dbo.RandomBigInt
AS
  SELECT CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)) AS number

And function

CREATE FUNCTION dbo.reallyBigRandInt2(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT RIGHT(RIGHT(FORMAT(number, 'D19'),17) +
                      RIGHT(FORMAT(number, 'D19'),17) +
                      RIGHT(FORMAT(number, 'D19'),17), @Length)
FROM  dbo.RandomBigInt)
  END


Why would you want to?

  Critical Alert: ... User: >>> sa <<< ...

This could indicate a script or application attempting to connect to a database using "sa" credentials, which should never be allowed.

  Critical Alert: ... >>> Encrypted Password: 1 <<<, User: sa ...

This could actually indicate someone trying to hack your "sa" account.

Both of these require your attention.


Updating to CU8 on SQL Server 2012 SP3 with CU2

Sql Server updates are incremental. As long as the service pack level isn't changing, there is no need to install any intermediate updates.

In your case, SP2 CU2 can be patched directly to SP2 CU8. There is no need - or use - to install CU3 - CU7 before the CU8. Release notes are not cumulative. This means that to see all the changes between CU2 and CU8, you need to read the release notes for CUs 3-8.

Service packs are similar, RTM can be upgraded to the desired SP without installing other SP version(s) before.

Edit: As per the comment, a replication setup needs special steps for update installation. This is documented in Microsoft's support documentation and depends on topology.


Postgres: my "data/backup" directory is way too large

Here's a du -sh * output of a typical PostgreSQL 9.3 data directory:

4.0K    PG_VERSION
59G     base
47M     global
20M     pg_clog
28K     pg_multixact
80K     pg_notify
4.0K    pg_serial
4.0K    pg_snapshots
4.0K    pg_stat
368K    pg_stat_tmp
168K    pg_subtrans
4.0K    pg_tblspc
4.0K    pg_twophase
1.1G    pg_xlog
4.0K    postmaster.opts
4.0K    postmaster.pid

Notice that there's no backup directory: a PG instance does not need or create such a directory. It had to be created by a person or a script that is not part of PostgreSQL itself.

Since you mention pg_xlog, if the files in this directory follow the naming convention of WAL files (24 numbers in hexadecimal in a 3x8 layout) found in pg_xlog, it's plausible that they're WAL files copied for continuous archiving. Check the archive_command in postgresql.conf.

In this case, the name backup for the target directory make sense, but its location doesn't. Such backups are useful for disaster recovery when the data directory has disappeared, so obviously the data directory itself is the last place where you want such backups.


Execute query using daily timestamp (mysql event)


Update DATE field when another field in the same table changes value

A Trigger can't update another row of the same table as the row, the trigger was called from.

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

Another option for you might be the ON UPDATE constraint that you can add to your table
- this option comes only from 5.5 up.

See example here

mysql> create table your_table (date_updated DATETIME ON UPDATE CURRENT_TIMESTAMP, yearofarrival int  );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into your_table values (now() - INTERVAL 1 DAY, 100);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-06 12:00:59 |           100 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> update  your_table set yearofarrival= 1998;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-07 12:01:16 |          1998 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql>

Should i explain what i have posted here ?

By adding this constraint to your column , every time you alter your yearofarrival  column the date_updated  will be automaticly updated with the CURRENT_TIMESTAMP value!


Difficult to diagnose definitively based on the information provided but the likely cause is the batch was inadvertently invoked with a transaction already open. I suggest one or more the following to prevent the problem going forward:

Add SET XACT_ABORT ON; to the batch to help ensure explicit
transactions are automatically rolled back.
Remove the superfluous BEGIN TRAN and COMMIT since the automatic
single-statement transaction will fully rollback a delete batch if an error
occurs.
Add error handling


Query distinct then select option then choose and query choosed as html result on page

I manage to solve the problem, here is the code for others who need help.

$sid=$_GET['month'];
$sql = "SELECT * FROM diary where user_name = '$login_session'
    and month= '$sid' ORDER BY day";
$result = mysql_query($sql);
while ($rs=mysql_fetch_object($result)){

and then just wrap in tables:

echo $rs->day;
echo $rs->post;
}


Should I store data pre-ordered rather than ordering on the fly?

(DESCRIBE is not as descriptive as SHOW CREATE TABLE; we can't see what indexes you have.)

That one SELECT would benefit from this 'composite' index:

INDEX(profile_id, impressions) -- in that order.

Do you have a keyword that is 2083 characters long?  If not, why have such a big VARCHAR?

Why have both unique_key and id?  Is unique_key some form of UUID?  They are notoriously inefficient when the table gets huge.

LIMIT ?, ? ... ($start, $end) -- The two numbers in LIMIT are start and count, not end.

By using the index, above, and changing to "remember where you left off", you can make the ORDER BY...LIMIT work a lot faster.  Details .  This suggestion, if practical for your application, will be faster (at least after the first 'page') than your original question about ordering the data could ever be!  Why?  Because OFFSET (the first number in LIMIT) requires work.  My blog show how to get rid of that work.

More

When you could have multiple rows with the same value, and you need to be deterministic in ordering:

ORDER BY profile_id DESC, impression DESC, id DESC)
INDEX   (profile_id,      impression,      id)

Notes:

In the ORDER BY, all the items are in the same direction (DESC is usually what is wanted).
Mixing ASC and DESC prevents use of the index (until 8.0).
Since you are looking for a single profile_id, ASC and DESC on it have identical effect.

To deal with a 'compound' $leftoff, let's look at the above example.  After assuming that profile_id is constant, we want to somehow remember where you left off as a pair of $impression, $id, then do

WHERE   impression <= $impression
  AND ( impression <  $impression OR id < $id )

alternatively (and it is unclear whether these optimize differently in different versions of mysql):

WHERE ( impression = $impression AND id < $id
     OR impression < $impression )


There are no permissions on LISTEN and NOTIFY. It would not have made sense to have any until support for notify payloads was introduced in more recent versions.

If you want access control, create a table with the information that you want and then send a NOTIFY that's empty or has nothing but a primary key for that table in it. SELECT the row(s) of interest from the table, which can have access control, to get the sensitive data. This used to be the only way to use LISTEN and NOTIFY to send specific data before payloads were added.


GETDATE - Current date, Month Start and Month End in yyyymmdd

From SQL Server 2012 you can use EOMONTH (Transact-SQL)

SELECT DATEADD(DAY, 1-DAY(GETDATE()), GETDATE()) AS CurrentMonth_start,
       EOMONTH(GETDATE()) AS CurrentMonth_End,
       DATEADD(DAY, 1, EOMONTH(GETDATE())) AS NextMonth_start,
       EOMONTH(GETDATE(), 1) AS NextMonth_End;


BCP code to extract a large amount of data from MS SQL Server

There are plenty of examples on the internet - search for "bcp comma delimited"  Also, refer to the BCP syntax here.

This should get you started:

bcp <YourDatabase>.<YourSchema>.<YourTable> out C:\OutData.dat -o C:\OutData.log -S . -T -c -a 65535' -t, -b100000

BCP - the command
the fully qualified object you're exporting from Sql Server (this could also be a view)
out - the path and file name of the exported data
-o - the output log information about number of rows extracted
-S - the sql server instance name (default here)
-T - use a trusted connection
-c - export the data in character format
-a - packet size of 65535
-t - your column delimiter (comma in this case)
-b - batch size (for performance)


PREEMPTIVE_OS_WRITEFILEGATHER wait on database RESTORE with IFI on

wBob is right on the money, you need a service restart.  I wanted to enhance his answer and give a script that will also verify that IFI is indeed enabled.

This will determine whether or not you need to kick the service to allow the local policy to take.

USE MASTER;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --
--Exit if a database named DummyTestDB exists
IF DB_ID('DummyTestDB') IS NOT NULL
BEGIN
    RAISERROR (
            'A database named DummyTestDB already exists, exiting script'
            ,20
            ,1
            )
    WITH LOG
END

--Temptable to hold output from sp_readerrorlog
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL
    DROP TABLE #SqlLogs
GO

CREATE TABLE #SqlLogs (
    LogDate DATETIME2(0)
    ,ProcessInfo VARCHAR(20)
    ,TEXT VARCHAR(MAX)
    )

--Turn on trace flags 3004 and 3605
DBCC TRACEON (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog
CREATE DATABASE DummyTestDB
GO

--Turn off trace flags 3004 and 3605
DBCC TRACEOFF (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Remove the DummyDB
DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File
--This can take a while if you have a large errorlog file
INSERT INTO #SqlLogs (
    LogDate
    ,ProcessInfo
    ,TEXT
    )
EXEC sp_readerrorlog 0
    ,1
    ,'Zeroing'

IF EXISTS (
        SELECT *
        FROM #SqlLogs
        WHERE TEXT LIKE 'Zeroing completed%'
            AND TEXT LIKE '%DummyTestDB.mdf%'
            AND LogDate > DATEADD(HOUR, - 1, LogDate)
        )
BEGIN
    PRINT 'We do NOT have instant file initialization.'
    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
END
ELSE
BEGIN
    PRINT 'We have instant file initialization.'
END


QUERY #1

  position the user 2 has if I sort by time and server = 1

SET @given_user = 2;
SET @given_server = 1;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User
    FROM playerstats
    WHERE server = @given_server
    ORDER BY time DESC
) A
WHERE User = @given_user;

QUERY #2

  if I sort by the sum of the time of a user

SET @pos = 0;
SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
(
    SELECT User,SUM(time) TotalUserTime
    FROM playerstats
    GROUP BY User
) A ORDER BY TotalUserTime DESC;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

mysql> USE mydb
Database changed
mysql> CREATE TABLE playerstats
    -> (
    ->     User INT,
    ->     Time INT,
    ->     Server INT
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO playerstats VALUES
    -> (2, 10,1),
    -> (1, 50,1),
    -> (2, 60,3),
    -> (1, 20,2),
    -> (1,100,3),
    -> (2, 70,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM playerstats;
+------+------+--------+
| User | Time | Server |
+------+------+--------+
|    2 |   10 |      1 |
|    1 |   50 |      1 |
|    2 |   60 |      3 |
|    1 |   20 |      2 |
|    1 |  100 |      3 |
|    2 |   70 |      2 |
+------+------+--------+
6 rows in set (0.01 sec)

mysql>

QUERY #1 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @given_server = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User
    ->     FROM playerstats
    ->     WHERE server = @given_server
    ->     ORDER BY time DESC
    -> ) A
    -> WHERE User = @given_user;
+------+------+
| pos  | User |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.09 sec)

mysql>

QUERY #2 EXECUTED

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    -> (
    ->     SELECT User,SUM(time) TotalUserTime
    ->     FROM playerstats
    ->     GROUP BY User
    -> ) A ORDER BY TotalUserTime DESC;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    1 |    1 |           170 |
|    2 |    2 |           140 |
+------+------+---------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-26 15:32 EST

QUERY #3

SET @given_user = 2;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    (
        SELECT User,SUM(time) TotalUserTime
        FROM playerstats
        GROUP BY User
    ) A ORDER BY TotalUserTime DESC
) AA
WHERE User = @given_user;

QUERY #3 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    ->     (
    ->         SELECT User,SUM(time) TotalUserTime
    ->         FROM playerstats
    ->         GROUP BY User
    ->     ) A ORDER BY TotalUserTime DESC
    -> ) AA
    -> WHERE User = @given_user;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    2 |    2 |           140 |
+------+------+---------------+
1 row in set (0.06 sec)

mysql>


Corrupt InnoDB tables after every mysql restart. What's wrong?

markus you want to read the very last paragraph on mysql.com docs for resizing log file http://dev.mysql.com/doc/refman/5.1/en/innodb-data-log-reconfiguration.html - you only need to remove the ib_ log files and not the ibdata1 file

  Stop the MySQL server and make sure that it shuts down without errors
  (to ensure that there is no information for outstanding transactions
  in the log). Copy the old log files into a safe place in case
  something went wrong during the shutdown and you need them to recover
  the tablespace. Delete the old log files from the log file directory,
  edit my.cnf to change the log file configuration, and start the MySQL
  server again. mysqld sees that no InnoDB log files exist at startup
  and creates new ones.


Best tool to optimize SQL Server (not the queries, but the database itself)

I'd echo @Mark Storey-Smith's comment - a competent DBA is the best way to go here. You can't really automate a well tuned SQL Server but a good DBA can setup various maintenance items to keep it running well.

Sounds like you were asking a lot about maintenance so one great spot to look for some scripts to help setup a best of breed monitoring solution is Olla Hallengren's Maintenance Solution scripts explained on Olla's site here.

That will help ensure you are at least doing the important maintenance items (Index rebuilding/reorganizing, statistics updating, backups, updating statistics, checking database integrity, etc.)

As far as the ongoing optimization, I'd suggest picking up a copy of the Professional SQL Server 2008 Internals and Troubleshooting book. It has just the right amount of internals knowledge to help you understand the "why" behind best practices and contains plenty of practical examples for implementing the best practices.

Or I'd recommend the same Internals & Troubleshooting book but for SQL Server 2012. Contains some great chapters just for your question. Like how to perform a SQL Server Health Check, by Glenn Berry.


What is the reason for this warning in Sort operator?

You can see the warning description in properties of the element. I'm sure you will see information like below:

  Operator used tempdb to spill data during execution.....

It means that memory which was granted for the query turned out to be less than it needed. Two possible reasons for that:

Wrong cardinality estimation so the query asked for the less memory grant than it really needed
Lack of memory so the query asked enough memory but got less

Detailed information about memory grant can be found in the properties window of SELECToperation from your query plan.


pt-table-sync: syncing slave from master that has different credentials?

Yes you can do that, there are switches for the CLI so these will hopefully get you correct access to the slave...

--slave-user and --slave-password

Here is a link to the specific part of the documentation that provides this information

https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html#cmdoption-pt-table-sync-slave-user

Please note at the top of that page:

  This tool changes data, so for maximum safety, you should back up your data before using it.

Disclosure: I work for Percona


Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

No .. once you recover, you cannot apply transaction logs anymore. You have to start again.

What you can do is ... apply transaction log and have the db in standby using ..
 restore .. with Standby (available for read queries).

RESTORE LOG MyAdvWorks
   FROM MyAdvWorks_log1
   WITH STANDBY = 'c:\undo.ldf'
GO

Alternatively, you can also ask the vendor to compress the backups to reduce size.


MariaDB multiple auth modules for a user?

As you said, a given user can only support one plugin. It is described in more details in the MySQL manual.


Is it possible to login to an Oracle schema using a certificate?

I would say that the answer is yes. I am working on encrypting my listener. But it would start with building a wallet. Since I use Linux i use cron and OS authentication for jobs that I want to  run. You can do something similar with Windows. Just make sure that remote OS authentication is turned off.


Does subqueries "short-circuit" based on other conditions?

Yes, they do.

select
  case
    when (1 = 0 and (select 1/0 from dual) = 1)
    then 1
    else 0
  end as c
from dual;

         C
----------
         0

Here 1 = 0 is false, so the second argument of the AND is not executed, luckily for us, because it would be a division by zero. With 1 = 1 the second part is executed and we receive the expected error:

select
  case
    when (1 = 1 and (select 1/0 from dual) = 1)
    then 1
    else 0
  end as c
from dual;

ERROR at line 3:
ORA-01476: divisor is equal to zero


Error when upgrading SQL 2016 to SP2 - "The given key was not present in the dictionary."

What I am understanding from your requirement to patch a sql clustered environment you are removing a node. This is definitely not the right way to proceed. You need to patch the current passive node first. Once everything goes well do a failover at node level.

Now the patched passive node becomes active, do your health check for the patching done if all look green. Once ok, proceed with patching on current passive node.

If there is any node preference you can failback to original active node and do health checks to ensure patching went well.