How are binlogs updated for MySQL temporary tables?

Statements like CREATE TEMPORARY TABLE will land in the binary logs. This has plaugued me for years when troubleshooting. Here is why:

In MySQL Replication, if you create a temp table, it will remain in existence as long as the SQL thread is in session. I have had dozens of instances where someone would schedule a nightly backup which included STOP SLAVE; before the mysqldump and START SLAVE; afterwards. When you run STOP SLAVE;, it terminates both the IO thread and the SQL thread for Replication. When the SQL thread dies, all tables created with CREATE TEMPORARY TABLE are dropped immediately. When you try to run START SLAVE;, MySQL Replication breaks instantly, stating that the table it was INSERTing into or SELECTing from does not exist. How did I find the table? I would have to run mysqlbinlog against the master's binary logs to locate the position that the CREATE TEMPORARY TABLE was issued.

So, to answer both questions, all you have to do is this:

Any INSERT into a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO tmptable ... ;
SET SQL_LOG_BIN=1;

Any UPDATE of a temp table should like like this:

SET SQL_LOG_BIN=0;
UPDATE tmptable SET ... ;
SET SQL_LOG_BIN=1;

Any INSERT into a real table using data from a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO realtable SELECT ... FROM tmptable ... ;
SET SQL_LOG_BIN=1;

UPDATE 2012-03-19 17:06 EDT

I read your comment

Question 1 from your comments : What happens if two threads both create temp tables of the same name? Do the statements fall into the binlogs and conflict?

Answer : Each temp table of identical names (even identical schemas) are unique to the the DB Connection that opened it. Therefore, no conflict whatsoever. In fact, since replication is single-threaded along the SQL thread, all SQL statements with temp tables of identical names are processed in a serialized manner, i.e., like a FIFO queue.

Question 2 from your comments : Wouldn't this cause the changes to realtable to not be reflected in the binlogs?

Answer : You are correct. Therefore, since you want the changes to the realtable  replicated to the Slave, you will not need SET SQL_LOG_BIN=0;


SQL Server unpredictable select results (dbms error?)

Are we doing something wrong or is it SQL Server error?

It is a wrong-results bug, which you should report via your usual support channel. If you do not have a support agreement, it may help to know that paid incidents are normally refunded if Microsoft confirms the behaviour as a bug.
The bug requires three ingredients:

Nested loops with an outer reference (an apply)
An inner-side lazy index spool that seeks on the outer reference
An inner-side Concatenation operator

For example, the query in the question produces a plan like the following:

There are many ways to remove one of these elements, so the bug no longer reproduces.
For example, one could create indexes or statistics that happen to mean the optimizer chooses not to utilize a Lazy Index Spool. Or, one could use hints to force a hash or merge union instead of using Concatenation. One could also rewrite the query to express the same semantics, but which results in a different plan shape where one or more of the required elements are missing.
More details
A Lazy Index Spool lazily caches inner side result rows, in a work table indexed by outer reference (correlated parameter) values. If a Lazy Index Spool is asked for an outer reference it has seen before, it fetches the cached result row from its work table (a "rewind"). If the spool is asked for an outer reference value it has not seen before, it runs its subtree with the current outer reference value and caches the result (a "rebind"). The seek predicate on the Lazy Index Spool indicates the key(s) for its work table.
The problem occurs in this specific plan shape when the spool checks to see if a new outer reference is the same as one it has seen before. The Nested Loops Join updates its outer references correctly, and notifies operators on its inner input via their PrepRecompute interface methods. At the start of this check, inner side operators read the CParamBounds:FNeedToReload property to see if the outer reference has changed from last time. An example stack trace is shown below:

When the subtree shown above exists, specifically where Concatenation is used, something goes wrong (perhaps a ByVal/ByRef/Copy problem) with the bindings such that CParamBounds:FNeedToReload always returns false, regardless of whether the outer reference actually changed or not.
When the same subtree exists, but a Merge Union or Hash Union is used, this essential property is set correctly on each iteration, and the Lazy Index Spool rewinds or rebinds each time as appropriate. The Distinct Sort and Stream Aggregate are blameless, by the way. My suspicion is that Merge and Hash Union make a copy of the previous value, whereas Concatenation uses a reference. It is just about impossible to verify this without access to the SQL Server source code, unfortunately.
The net result is that the Lazy Index Spool in the problematic plan shape always thinks it has already seen the current outer reference, rewinds by seeking into its work table, generally finds nothing, so no row is returned for that outer reference. Stepping through the execution in a debugger, the spool only ever executes its RewindHelper method, and never its ReloadHelper method (reload = rebind in this context). This is evident in the execution plan because operators under the spool all have 'Number of Executions = 1'.

The exception, of course, is for the first outer reference the Lazy Index Spool is given. This always executes the subtree and caches a result row in the work table. All subsequent iterations result in a rewind, which will only produce a row (the single cached row) when the current iteration has the same value for the outer reference as the first time around.
So, for any given input set on the outer side of the Nested Loops Join, the query will return as many rows as there are duplicates of the first row processed (plus one for the first row itself of course).
Demo
Table and sample data:
CREATE TABLE #T1
(
    pk integer IDENTITY NOT NULL,
    c1 integer NOT NULL,

    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (pk)
);
GO
INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6);

The following (trivial) query produces a correct count of two for each row (18 in total) using a Merge Union:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C;

If we now add a query hint to force a Concatenation:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C
OPTION (CONCAT UNION);

The execution plan has the problematic shape:

And the result is now incorrect, just three rows:

Though this behaviour is not guaranteed, the first row from the Clustered Index Scan has a c1 value of 1. There are two other rows with this value, so three rows are produced in total.
Now truncate the data table and load it with more duplicates of the 'first' row:
TRUNCATE TABLE #T1;

INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (1), (1), (1), (1), (1);

Now the Concatenation plan is:

And, as indicated, 8 rows are produced, all with c1 = 1 of course:

I notice you have opened a Connect item for this bug but really that is not the place to report issues that are having a production impact. If that is the case, you really ought to contact Microsoft Support.

This wrong-results bug was fixed at some stage. It no longer reproduces for me on any version of SQL Server from 2012 onward. It does repro on SQL Server 2008 R2 SP3-GDR build 10.50.6560.0 (X64).


Cannot see SQL Agent Job history for more two days

SQL Server Agent stores the history of job execution in system tables
  in msdb database.

The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

SELECT
        [sJOB].[job_id] AS [JobID]
        , [sJOB].[name] AS [JobName]
        , CASE
            WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
            ELSE CAST(
                    CAST([sJOBH].[run_date] AS CHAR(8))
                    + ' '
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [LastRunDateTime]
        , CASE [sJOBH].[run_status]
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Canceled'
            WHEN 4 THEN 'Running' -- In Progress
          END AS [LastRunStatus]
        , STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':')
            AS [LastRunDuration (HH:MM:SS)]
        , [sJOBH].[message] AS [LastRunStatusMessage]
        , CASE [sJOBSCH].[NextRunDate]
            WHEN 0 THEN NULL
            ELSE CAST(
                    CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                    + ' '
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [NextRunDateTime]
    FROM
        [msdb].[dbo].[sysjobs] AS [sJOB]
        LEFT JOIN (
                    SELECT
                        [job_id]
                        , MIN([next_run_date]) AS [NextRunDate]
                        , MIN([next_run_time]) AS [NextRunTime]
                    FROM [msdb].[dbo].[sysjobschedules]
                    GROUP BY [job_id]
                ) AS [sJOBSCH]
            ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN (
                    SELECT
                        [job_id]
                        , [run_date]
                        , [run_time]
                        , [run_status]
                        , [run_duration]
                        , [message]
                        , ROW_NUMBER() OVER (
                                                PARTITION BY [job_id]
                                                ORDER BY [run_date] DESC, [run_time] DESC
                          ) AS RowNumber
                    FROM [msdb].[dbo].[sysjobhistory]
                    WHERE [step_id] = 0
                ) AS [sJOBH]
            ON [sJOB].[job_id] = [sJOBH].[job_id]
            AND [sJOBH].[RowNumber] = 1
    ORDER BY [JobName];

I am also attaching the output of after execution of this script.

For your ref Here and Here


MySQL database backup every day using java code

This is easy to do.

Write a script to create the backup using the "mysqldump" tool available at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html - Google "mysql backup unix/windows script" to find some examples.
Use a task scheduler to run this script. On UNIX this could be "cron", Windows as a "task scheduler". Best to do so at times when the app is under low-usage.

It is worth while putting some backup file cycling into the script so that you keep, say, every backup from the last week and delete the rest.

Also remember to backup to a physically different disk from the database itself so you have a backup if the disk dies.


Hitting a transactional table, or copying the data to a temporary table?

It could reduce the time you access the table, and possibly the execution time of your query.

If that is true will depend on the indexes on the table, the size of the table, ....

An example of executing your query on an unindexed heap:

While the rows returned are 15K, the residual I/O on the predicate is high, 120K rows for each table scan.

As you already know, adding an index could change this to a seek predicate.

Reducing the time you would access the table can be done by using a temp table.

Insert the data

Add an index

Do index seeks on your temp table

Conclusion

While reducing the time accessing the table, the execution time in total was twice as long on my test server. To help you confirm if it is a solution for you, we would need the table definition, the actual query and additionaly the test data.

If it is not an option to share these things, then you could do the test yourself, and share the execution plans and the SET STATISTICS IO,TIME output.

Test data used

CREATE TABLE CountingDays(CountingDaysId int identity(1,1), groupcolumn varchar(255), countingdate datetime2)

SET NOCOUNT ON;
DECLARE @i int = 0
WHILE @i < 15000
BEGIN
INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-4,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-7,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-8,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-3,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-6,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-5,getdate()))

SET @i+=1

END

SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all
select groupcolumn from CountingDays
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn

CREATE TABLE #temp(groupcolumn varchar(255), countingdate datetime2)
INSERT INTO #temp(groupcolumn,countingdate)
select groupcolumn,countingdate FROM CountingDays
CREATE INDEX IX_Countingdate on #temp(Countingdate) include(groupcolumn)

SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from #temp
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all
select groupcolumn from #temp
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn

DROP TABLE #temp


Best use ST_DWithin() in a LATERAL join:

SELECT t.*, neighbor.*  -- or only selected columns
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT *
   FROM   tbl t1
   WHERE  ST_DWithin(t.geo_point, t1.geo_point, 5000 * 1.609344)
   ) neighbor ON true
WHERE t.address = 'my_address';  -- to restrict to a particular address

1.609344 being the factor for converting miles to kilometers. Source: Wikipedia. Assuming meter as unit! The documentation:

  For Geometries: The distance is specified in units defined by the
  spatial reference system of the geometries. For this function to make
  sense, the source geometries must both be of the same coordinate
  projection, having the same SRID.

  For geography units are in meters

I think your SRID 4326 uses degrees, not meters. Instructions in this related answer:

ST_DWithin takes parameter as degree , not meters , why?

You need an index on geo_point to make this fast (used by ST_DWithin() automatically):

CREATE INDEX tbl_geo_point_gist ON tbl USING gist (geo_point);

A GiST index can also be used to identify nearest neighbors (in combination with a maximum number - LIMIT).

Related:

Order by distance


Where can I find a list of reserved logins?

If I try the following:
create login [public] WITH PASSWORD = '';

I get this error message:

Msg 15025, Level 16, State 1, Line 12
The server principal 'public' already exists.

That makes me think that reserved logins are the server principals that exist on a fresh install of SQL Server. I can get a list of those with valid login names with this query:
SELECT [name]
FROM sys.server_principals
WHERE [name] NOT LIKE '%\%';

Here are the results in SQL Server 2016:
╔═════════════════════════════════════════╗
║                  name                   ║
╠═════════════════════════════════════════╣
║ sa                                      ║
║ public                                  ║
║ sysadmin                                ║
║ securityadmin                           ║
║ serveradmin                             ║
║ setupadmin                              ║
║ processadmin                            ║
║ diskadmin                               ║
║ dbcreator                               ║
║ bulkadmin                               ║
║ ##MS_SQLResourceSigningCertificate##    ║
║ ##MS_SQLReplicationSigningCertificate## ║
║ ##MS_SQLAuthenticatorCertificate##      ║
║ ##MS_PolicySigningCertificate##         ║
║ ##MS_SmoExtendedSigningCertificate##    ║
║ ##MS_PolicyTsqlExecutionLogin##         ║
║ ##MS_PolicyEventProcessingLogin##       ║
║ ##MS_AgentSigningCertificate##          ║
╚═════════════════════════════════════════╝

Both of the examples mentioned in the article are in the result set. Trying to create logins with any of those names seems to fail. For example, both of the following fail:
create login [diskadmin] WITH PASSWORD = '';
create login [##MS_SmoExtendedSigningCertificate##] WITH PASSWORD = '';

I can't say for sure that the above is the complete list but hopefully it helps.
Just for fun I tried creating all possible alphanumeric logins to see which ones would throw an error. With a single CPU I was only able to create and drop 55 logins per second. At that rate it would take about a year to test all possible six character combinations, so this doesn't seem to be a viable approach.


From the documentation Connection Pooling is:

  Connecting to a data source can be time consuming. To minimize the
  cost of opening connections, ADO.NET uses an optimization technique
  called connection pooling, which minimizes the cost of repeatedly
  opening and closing connections

Basically the provider keeps connections open, even if the code that opened them closes them. The next time the provider needs a connection the already open one is used.

These open connections are grouped into pools. A new pool is created for each combination of connection string and credential used to access SQL Server.

Having two logins per second could mean that connection pooling isn't working correctly or it could mean that there are multiple connections using a different combination of connection strings and credentials.


How to manage primary key value while multiple secondary server sync with primary server?

There are 3 possible solutions that I can think of to what you are asking.

First solution is exactly how you have it, set each server to increment an identity column by the same increment but start them offset by one. (Server 1: start at 1 increment by 2, Server 2: start at 2 increment by 2) This solution works well because it keeps your key small but allows you to generate them without running into conflicts. On the other hand you have to configure each identity column on each server so you have the maintenance overhead.
Another solution is to set your key to a guid, at 16 bytes the chances of running into a conflict are small, but it's a rather wide key and comes with it's own set of troubles.  See here for more info:  Kimberly Tripp GUIDs as Primary Key  Or here:  GUID vs INT Debate
Use a composite key where one column represents the server that created the record and the other column is an identity that increments by 1. This also has a disadvantage of having to configure the column on each server to represent the server it was created on.

In the end there is no right or wrong way to do it, it's all a matter of what trade offs you want to put up with.


Update performance: clustered versus covering index

Under the covers clustered and nonclustered indexes are the same. The clustered index just has the additional property that is is guaranteed to INCLUDE all columns. Therefore the data does not need to be maintained somewhere else. So, a clustered index and a nonclustered index that INCLUDEs all columns are virtually the same from an update cost perspective.

However, every index needs to be maintained if it contains a column that was changed during an updated. That means, the more indexes you have, the more expensive updates get.

So in your situation, I would try to keep the number of indexes to a minimum. That will help update performance more than worrying about if a particular index is better clustered or covering.

That all being said, your updates still need to find the row(s) to update as quickly as possible. Because you have two orders of magnitude more updates then select, updates should be looked at first when designing the indexing strategy. After they are taken care of, look at providing the minimal number of appropriate indexes for the read queries.


group rows when row count exceeds certain count

This would be fairly straight forward if MySQL had implemented window functions or CTEs (they are working on it but it hasn't been announced yet in which version it will appear.) With a CTE:

with grp as
  ( select city.name, sum(count) as views
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
  ),
the_first_three as
  ( select name, views
    from grp
    order by views desc
    limit 3
   ),
the_rest as
  ( select name, views
    from grp
    order by views desc
    limit 900000000000000 offset 3
   )
select name, views
from the_first_three

union all

select 'other', sum(views)
from the_rest

order by (name = 'other'), views desc ;

With CTEs and window functions:

with grp as
  ( select city.name, sum(count) as views,
           row_number() over (order by sum(count) desc) as rn
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
  )
select name, views, rn
from grp
where rn <= 3

union all

select 'other', sum(views), 4
from grp
where rn > 3

order by rn ;

And while we are waiting for CTEs and window functions to be implemented, in current MySQL versiosn, we'll have to either duplicate code or use some trickery to avoid duplication. In any case, I don't think we can avoid doing the group by and summation twice. Here is one way to get the wanted output. It's essentially a rewrite of the above code 1, using derived tables instead of CTEs:

  ( select city.name, sum(count) as views
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
    order by views desc
    limit 3
  )
union all
  ( select 'other', sum(views)
    from
      ( select city.name, sum(count) as views
        from profpostview as ppview
          inner join professional as prof
            on prof.pfid = ppview.viewerpfid
          left join cityinfo as city
            on city.cityid = prof.cityid
        where pfpid = _pfpid
        group by city.name
        order by views desc
        limit 900000000000000 offset 3
      ) grp
  )
order by (name = 'other'), views desc ;

If you want to resolve ties in some way, change the order by accordingly, eg. to order by views desc, name asc


I would implement it using a reference counter:

CREATE TRIGGER new_in_b AFTER INSERT ON B
FOR EACH ROW
BEGIN
  UPDATE A SET refcounter = refcounter + 1 WHERE id = new.foreignkey;
END;

CREATE TRIGGER remove_from_b AFTER DELETE ON B
FOR EACH ROW
BEGIN
  UPDATE A SET refcounter = refcounter - 1 WHERE id = old.foreignkey;
  DELETE FROM A WHERE id = old.foreignkey AND refcounter = 0;
END;


How to determine if it is necessary to increase the sga and the pga in Oracle database

Tuning usually starts with a reason.  Oracle has made great strides in reducing the need to tune for many use cases.  Most tuning I have done has been necessitated by poorly written application code or trying to mix an OLAP application and reporting on the same instance. Without knowing your operating system and specifications, how the database is used, size of the OS paging file, number of users only general advice can be given.  The documentation is excellent and worth your time.

If you don't have a specific issue then set the aggregate PGA target parameter to 0 and let Oracle do the work. The existing settings can be seen here

SELECT *
  FROM V$PGASTAT;

Official documentation is here.

The SGA can also be automatically managed by Oracle with these settings:

STATISTICS_LEVEL to TYPICAL or ALL
SGA_TARGET to a nonzero value
SGA_MAX_SIZE to a value larger than SGA_TARGET but small enough that there is enough memory free on the server for the OS and other running processes.

You can query the existing settings from the SQL command line with

SHOW SGA

A very rough rule of thumb is to allocate at least four to six GB for the OS on a dedicated server.

Official documentation is here


Optimize a WHERE IN condition using an index

Caveat: This answer applies to InnoDB in MySQL.  It may not apply to other MySQL Engines, nor to other RDBMSs.

A PRIMARY KEY is

Clustered with the data, and
A uniqueness constraint, and
An index

SELECT * FROM table WHERE type = ? AND id IN (?,?,?) is best handled by PRIMARY KEY(type, id) with the columns in that order.  (Second best is INDEX(type, id)).

Being an index, the lookup does not need to scan the entire table.
Being clustered, the lookup and the * (of SELECT *) are done at the same time.
Being unique is not relevant to this SELECT.
The column order is required because the = must come first.
If there is only one item in the IN, the IN will be optimized to =; the index is still optimal.

Cardinality of type versus id is, contrary to a well entrenched wives' tale, irrelevant, at least for this query.

Side note:  The order of items in the WHERE has no impact on performance; the order in the index does.

In this case the = should come first in the index, then more complex item(s), IN in this case.

id smells like a packed UUID.  For huge tables that is a terrible type of index, probably in all RDBMSs, since it is so random that caching is impractical.

Index Cookbook for MySQL/MariaDB.


how to use a variable as a part of a field name in mysql trigger declaration


Ruby on Rails cannot access production RDS on AWS

The @'localhost' bit is certainly an issue as you’re connecting to a remote RDS instance, not one located in the same instance as your Rails code.
Instead you can do something like this:
GRANT PROCESS ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'%';

However, depending on what the Rails code may be doing during the setup phase, you may want to consider granting all privileges:
GRANT ALL ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'%';

After the system is installed and running, then you can reduce the privileges to just what is needed.


how to find password for mySQL database server

You can do it with some simple commands:
sudo su

cd /etc/mysql
cat my.cnf

When the file my.cnf is opened you can read it's contents, and it should contain your username and password for the current installation.
Hope this helps! ;)


How to add a column in table via stored procedure?

You can't embed DDL in a stored procedure like this.

Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.


SQL Server Extended Events to Capture Objects access by a User

I gave up on EE and instead using SQL AUDIT now.
  https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15


Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
Turn off foreign key checks and turn on auto-commit.
If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..


Distributed - across multiple databases or servers - Transactions

Yes this can be done but you need a third party acting as a transaction coordinator. The standard protocol for this is called Two Phase Commit (2PC). This is usually done with a transaction manager acting as the coordinator.

This can also be generalized further to more than two databases. In fact it doesn't even have to be databases as the approach is generally applied to "transactional resources". Besides databases the other common transactional resource is a message queue. The usual example is to perform some work in a database (ex: update an account balance) and also send a message out on a message queue (ex: an outbound order).

For this to work your datasources need to support 2PC, usually via the XA protocol. How to do it depends on the specific type of datasource and programming language (ex: for Java you'd use an XADataSource).


pg_restore terminates with "out of memory" error

Figured out the problem.

I was trying to import a DB dump from Postgres 9.6 to Postgres 10.

Downgraded to Postgres 9.6 and it imported successfully.


Postgresql: sort by value position in array column, then by secondary order

That sounds simple:
ORDER BY array_position(pillars, 'c'), st_distance(....)


What is seed in RAND function in SQL Server

Microsoft docs explains this in details here: apparently RAND() returns a really random result only if it is called without specifying a seed value (because in this case seed itself is assigned randonly).

So calling RAND() without seed should always return a different value, while calling RAND(seed) should always return the same result if called with the same seed.

Here is an excerpt from Microsoft docs:

  Arguments seed Is an integer expression (tinyint, smallint, or int)
  that gives the seed value. If seed is not specified, the SQL Server
  Database Engine assigns a seed value at random. For a specified seed
  value, the result returned is always the same.

  Remarks
  Repetitive calls of RAND() with the same seed value return the
  same results.


There is no solution, because table rows in PostgreSQL don't have a fixed position in the table. Every UPDATE will change the position, and every new row will be inserted wherever there happens to be enough room.

Even if there are no modifications, sequential scans of the table don't necessarily start at the beginning.

The way to order rows is to use an appropriate ORDER BY clause with SELECT.


Best way to pg_dump PostgreSQL on RDS to S3

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli:

pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --sse aws:kms - s3://my-bucket/dump.sql.gz

At work we wrapped this in a script which additionally fetches the required credentials from parameter store and then stores the backup in s3. The script runs within a docker container as a scheduled ecs task. Maybe the code in this repository will help you.


When you set a database to single_user, you don't have any control over which session gets to connect, unless you're already connected to the database.
So simply connect before setting single_user to eliminate the race condition:
USE AdventureWorks2017
ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE


How to find column which causing "Conversion failed" error

How do people troubleshoot these without having to go through an arduous process of going field by field?

Exclude half of them.  If the error persists exclude half the remaining.  Repeat.


What are some important security-related error codes / events to monitor?

It's a good idea to create SQL alerts in the SQL agent for all errors of severity 16 through to 25. Also create 1 SQL alert for each of these error IDs 823 824 825.

I would create an operator that points to an AD group (probably DBA or ITsupport) so that a team can be alerted or you can drop users in and out of that group when you are away so that errors are not missed.

This will keep you in the loop when things are happening on your server that you should know about but something like Redgate SQL Monitor is a much better solution.


How to outer join multiple tables to retrieve uncommon records

If you're not dead set upon using OUTER JOIN a way to get the expected result is

SELECT a, b, c
FROM   (SELECT 'tbl1' name, a, b, c
        FROM   tbl1
        UNION ALL
        SELECT 'tbl2' name, a, b, c
        FROM   tbl2
        UNION ALL
        SELECT 'tbl3' name, a, b, c
        FROM   tbl2) d
GROUP BY a, b, c
HAVING COUNT(DISTINCT name) < 3

otherwise you need to use FULL OUTER JOIN a type of join not supported by MySQL, but it can be emulated with a composition like (Full Outer Join in MySQL)

SELECT * FROM tbl1
LEFT JOIN tbl2 ON t1.id = t2.id
UNION
SELECT * FROM tbl1
RIGHT JOIN tbl2 ON t1.id = t2.id


Join two sql's to return articles and its likes

You can simply join them. You have to use a left outer join. Otherwise the articles that didn't get any "like" yet, wouldnt be displayed.

SELECT A.LDRSHP_ID
    , A.LDRSHP_TITL_DESC
    , A.LDRSHP_CNTNT_TXT
    , A.LDRSHP_PUBD_DT
    , A.LDRSHP_ATHR_NM
    , A.MEDIA_URL
    , A.ARTCL_SRC_ID
    , A.MEDIA_TYPE_DESCRIP
    , B.TOTAL_LIKES
FROM
    (
            SELECT LEADERSHIP.LDRSHP_ID
                   , LEADERSHIP.LDRSHP_TITL_DESC
                  , LEADERSHIP.LDRSHP_CNTNT_TXT
                  , LEADERSHIP.LDRSHP_PUBD_DT
                  , LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.MEDIA_URL
                  , LEADERSHIP.ARTCL_SRC_ID
                  , MEDIA.MEDIA_TYPE_DESCRIP
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP
              INNER JOIN RSKLMOBILEB2E.MEDIA_TYPE MEDIA
                  ON (LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF)
              WHERE (LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.LDRSHP_PUBD_DT)
              IN (SELECT LDRSHP_ATHR_NM
                    , MAX(LDRSHP_PUBD_DT)
                  FROM RSKLMOBILEB2E.LEADERSHIP_CORNER
                  GROUP BY LDRSHP_ATHR_NM)
      ) A
      LEFT OUTER JOIN
      (
              SELECT LEADERSHIP1.LDRSHP_ID
              , COUNT(*) TOTAL_LIKES
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP1
              , RSKLMOBILEB2E.LEADERS_LIKES LIKES1
              WHERE LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID
              group by LEADERSHIP1.LDRSHP_I
      ) B
              ON (A.LDRSHP_ID=B.LDRSHP_ID)
      ORDER BY A.LDRSHP_PUBD_DT DESC


What is the purpose of a Row_GUID column?

ROWGUIDCOL is primarily used for MERGE replication, and is also required for FILESTREAM, but can be used in any scenario where you want an immutable column separate from the primary key (e.g. in the case where a primary key value can change, but you still want to be able to tell which row was which before and after the change).

USE tempdb;
GO

CREATE TABLE dbo.example
(
  name sysname PRIMARY KEY,
  rowguid uniqueidentifier NOT NULL DEFAULT NEWID() ROWGUIDCOL
);

INSERT dbo.example(name) VALUES(N'bob'),(N'frank');

SELECT * FROM dbo.example;

UPDATE dbo.example SET name = N'pat' WHERE name = N'bob';
UPDATE dbo.example SET name = N'bob' WHERE name = N'frank';

SELECT * FROM dbo.example;

DROP TABLE dbo.example;

Now, if replication, or your application, or what have you is paying attention, it will notice that:

See here, here, and the "Snapshot Considerations" section here for more info.


Should I create a new table or save "static" data in all the tables?

There is another design you could consider. country_name would be considered the natural key for table countries, but it is not the only natural key. Every country has unique codes defined by ISO. These values are as fixed as your country_id would be. They have the advantage of being somewhat human-readable, however. For example the code for the United States of America is "USA".

You could use one of these ISO codes as the key of countries. The ISO code will also be the foreign key in your other tables. Reports and queries will return the ISO codes and humans can read them directly. Only when the country's full name is required will a join be written.

This is discussed further at this question.

I would not duplicate non-key columns across referencing tables just for the sake of it. The opportunities for inconsistencies is just to great. The overhead of an additional join to a four-row table is slight. If, in the future, you achieve great scale and it can be shown categorically that this join is the bottleneck then I would consider denormalizing, having put robust code testing and reconciliation procedure in place.


How to monitor SQL Server data and log file growth?

If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file.

If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table.

It is difficult to understand exactly what you would want any reports against the stored data to look like, or which part is challenging.


Unable to delete a referenced row due to a trigger on a referencing table?

All the cascading deletes and your trigger function are running inside the same database transaction, so if the transaction is aborted by an error you raise in the trigger function, all actions that took place in the same transaction (including the triggering delete) are rolled back.


Select all results from two independent tables

Combine the two tables with UNION and ORDER BY timestamp. You can also add an addition (type) column so the source of a row can be identified:

SELECT
    'comment' AS type, id, comment_content AS content, time_stamp
FROM
    comments

UNION ALL

SELECT
    'activity', id, activity_content, time_stamp
FROM
    activities

ORDER BY
    timestamp ;


Cannot choose Storage option on a table in SSMS for standard edition

Does SSMS change appearance depending on which SQL Server edition it
  is running against?

Yes

We cannot track everything ssms is executing or making decisions on but we can get a general idea.

Tracing the right click on a table on a SQL Server 2014 standard edition instance will give us more of an idea on the lower amount of checks.

In total, 11 rows/statements found while tracing the action:

With the options shown:

And when doing the same on a SQL Server 2016 standard edition instance (build version: 13.0.5081.1)

In total, 59 rows/statements:

And options shown:

In short, on my ssms version (14.0.17119.0) and my SQL Server 2016 instance
(build version: 13.0.5081.1), the storage option was shown.

I would suggest updating your ssms to the latest version, or using T-SQL instead of  the GUI.

Patching your sql server instance(s) is also recommended as a general best practice. You could opt to go for SP2 & CU8.

Only for reference, the query that ran on both instances is this one:

        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
        select @RegPathParams=@InstanceRegPath+'\Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
        select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
        select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'

        declare @SmoAuditLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT

        declare @NumErrorLogs int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT

        declare @SmoLoginMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT

        declare @SmoMailProfile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT

        declare @BackupDirectory nvarchar(512)
        if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
        select @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512))
        else
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT

        declare @SmoPerfMonMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT

        if @SmoPerfMonMode is null
        begin
        set @SmoPerfMonMode = 1000
        end

        declare @InstallSqlDataDir nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT

        declare @MasterPath nvarchar(512)
        declare @LogPath nvarchar(512)
        declare @ErrorLog nvarchar(512)
        declare @ErrorLogPath nvarchar(512)
        declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))
        select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
        select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
        select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
        select @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog)))

        declare @SmoRoot nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT

        declare @ServiceStartMode int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT

        declare @ServiceAccount nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT

        declare @NamedPipesEnabled int
        exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT

        declare @TcpEnabled int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT

        declare @InstallSharedDirectory nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT

        declare @SqlGroup nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT

        declare @FilestreamLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT

        declare @FilestreamShareName nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT

        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
        SELECT @cluster_name = cluster_name,
        @quorum_type = quorum_type,
        @quorum_state = quorum_state
        FROM sys.dm_hadr_cluster
        END TRY
        BEGIN CATCH
        --Querying this DMV using a contained auth connection throws error 15562 (Module is untrusted)
        --because of lack of trustworthiness by the server. This is expected so we just leave the
        --values as default
        IF(ERROR_NUMBER() NOT IN (297,300, 15562))
        BEGIN
        THROW
        END
        END CATCH

SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode],
ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],
CAST(@@SERVICENAME AS sysname) AS [ServiceName],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
@ServiceStartMode AS [ServiceStartMode],
ISNULL(@ServiceAccount,N'') AS [ServiceAccount],
CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],
CAST(@TcpEnabled AS bit) AS [TcpEnabled],
ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],
ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],
case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState],
@FilestreamLevel AS [FilestreamLevel],
ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],
-1 AS [TapeLoadWaitTime],
CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],
SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],
ISNULL(@cluster_name, '') AS [ClusterName],
ISNULL(@quorum_type, 4) AS [ClusterQuorumType],
ISNULL(@quorum_state, 3) AS [ClusterQuorumState],
SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid],
CAST(SERVERPROPERTY('IsPolyBaseInstalled') AS bit) AS [IsPolyBaseInstalled],
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Name],
CAST(
        ISNULL(serverproperty(N'instancename'),N'')
       AS sysname) AS [InstanceName],
CAST(0x0001 AS int) AS [Status],
SERVERPROPERTY('PathSeparator') AS [PathSeparator],
0 AS [IsContainedAuthentication],
CAST(null AS int) AS [ServerType]


How would you track DB storage use and then extrapolate it?

You just need to start by collecting data.

There are a few different valid ways of judging the size of the database -- number of rows in each table, size of table and index extents, size of data files -- so first you have to define what decisions you intend to make based on the data. Do you want to know when you'll need to add storage? Or when you'll need to extend data files?


Approaches for Real time copy of database on same SQL Server instance

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster.

Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes.
That is a lot of additional manual work, but I don't think you will find a solution without that. Those procedures are created automatically when calling sp_addarticle (See value 0x02 for the @schema_option parameter and the @ins_cmd, @upd_cmd and @del_cmd parameters.)

However, you might be better of to take a copy at the beginning of the development cycle and then not apply new changes till the end of the cycle. That is a simple backup-restore with the added benefit that you do not have to constantly adjust testing scripts do deal with the changing data.


In Mysql 5.5, why is the upper bound of a character range not inclusive?

Perhaps you can try REGEXP with the BINARY operator

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[ab]';

if you want a range (say from A-M)

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-M]';

case sensitivity ranges

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-Ma-m]';

Notice I used the ^ regular expression operator. This searches "from the beginning of the string".

As for your question, why does BETWEEN 'a' and 'b' fail to get all b's?

Look at what you are asking for in the query: Every manu that is >= 'a' and <= 'b'.

Does 'a' return?. If you have a manu named 'a', then yes.
Does 'aa' return?. If you have a manu named 'a', then yes.
Does 'azzzzzzzzzzzzzzz' return?. I think you know the answer to this one.
Does 'b' return?. If you have a manu named 'b', then yes.
Does 'ba' return?. Even if you had a manu named 'ba', then no.

Although inefficient, here is a query that would have worked:

SELECT manufacturer FROM warehouse
WHERE LEFT(manufacturer,1) IN ('a','b');

UPDATE 2013-02-11 15:22 EDT

I have a nice suggestion: Create an index that only stores two characters of manufacturer.

Perhaps this:

ALTER TABLE manufacturer
ADD INDEX manufacturer_prefix (manufacturer(2));

If you have any index of the entire manufacturer field, drop that index. You can then do effective two-character range searches.


Is it possible for a MySQL database to have duplicate tables with the same name?

Make sure that the table name has no whitespaces that are not displayed in HTML.


Mysql high safety implementation (replication or separate writes or something...)

The simple answer - You need Galera or NDB cluster

This is very simple form of answer, because new problems could come after this.

Some often "next" questions from projects - all was fine on my old server, but now my UPDATE of 5000000 records - always fail!

Or - I have 3 nodes, but my insert start work much slower, help me please! I just every few minutes run SELECT GROUP BY over 500 000 rows - but it was perfect work before.

If high safety - it mandatory, cluster + replication (or any other solution for reporting part of application - such as AWS Redshift and etc) - http://severalnines.com/blog/how-set-asynchronous-replication-galera-cluster-standalone-mysql-server-gtid

The best way - install and test!


Why does the delete/update block each other in this case?

Answer based on comments by a-horse-with-no-name
You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.


Create multiple variable columns from single column value

For the modifications to the question, here is a modified query:

declare @month datetime = '20140708';

set datefirst 1;  -- align to Monday as first day of week

-- test data as per OP:
create table #t (
     Date       Date not null primary key
    ,IsActive   int  not null
);
insert #t(Date,IsActive)
values ('20140728', 1)
      ,('20140729', 0)
      ,('20140730', 1)
      ,('20140731', 0)
      ,('20140801', 1)
      ,('20140802', 0)
      ,('20140803', 0);

with  -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
        (1),(1),(1),(1),(1),
        (1),(1),(1),(1),(1)
    )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
    select 0 as N union all
    select ROW_NUMBER() over (order by N) as N
    from E4
)

,calendar as (
    select top (datepart(dd,  eomonth(@month)))
         cast(dateadd(dd,tally.N,@month) as date) as Date
        ,N
    from tally
    where N > 0
)

select
     WeekNo
    ,max(isnull(Mon,0)) as Mon
    ,max(isnull(Tue,0)) as Tue
    ,max(isnull(Wed,0)) as Wed
    ,max(isnull(Thu,0)) as Thu
    ,max(isnull(Fri,0)) as Fri
    ,max(isnull(Sat,0)) as Sat
    ,max(isnull(Sun,0)) as Sun
from (
    select
         calendar.Date
        ,cast(datename(dw,calendar.Date) as char(3)) as WeekDay
        ,datepart(ww,calendar.Date) as WeekNo
        ,data.IsActive
    from calendar
    left join #t data
       on data.Date     = calendar.Date
) t
pivot ( max(IsActive) for WeekDay in (Mon,Tue,Wed,Thu,Fri,Sat,Sun)) pvt
group by WeekNo
;
go

yielding this:

WeekNo      Mon         Tue         Wed         Thu         Fri         Sat         Sun
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
28          0           0           0           0           0           0           0
29          0           0           0           0           0           0           0
30          0           0           0           0           0           0           0
31          1           0           1           0           1           0           0
32          0           0           0           0           0           0           0

fro the original question:
Not quite sure what this achieves, but the following works nicely:

declare @month datetime = '20140710';

with  -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
        (1),(1),(1),(1),(1),
        (1),(1),(1),(1),(1)
    )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
    select 0 as N union all
    select ROW_NUMBER() over (order by N) as N
    from E4
)

select  -- the query requested by OP, using the CTE tally above
    [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
    [31]
from (
    select top (datepart(dd, eomonth(@month)))
        right('0' + cast(N as varchar(2)),2) as X,
        N
    from tally
    where N > 0
) t pivot (max(X) for N in (
    [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
    [31] )
) pvt
;

to yield this:

01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31

(1 row(s) affected)

or with @month int = '20140210' -- February we get:

01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   NULL NULL NULL

(1 row(s) affected)

No RDBMS tagged, so used SQL Server syntax above.


When dimensions have references to other dimensions, we call that a Snow Flake instead of a Star schema. Depending on how you query your model later on, one approach might be more efficient than the other. It is possible to transform a Snow Flake in a Star Schema by flattening everything in the dimension linked to the fact table(s) – customer_dim in your example.

Some tools (like Microstrategy) behaves very well with a Snow Flake model. On the contrary Oracle Business Intelligence Enterprise Edition will require you to model your business model as a star schema.

For ad-hoc query performance, both approach should be benchmarked. A pure star schema reduces the number of joins so it might be faster. In term of data quality and master data management, the snow flake is probably easier to handle.


What could trigger that a login created from a certificate is not visible in metadata

This code executed for me fine. The only issue I might suspect is that your server is case sensitive. The EXISTS check will come up empty if you have a login named cert01login but the create will fail because it is not case sensitive. So you'll want to make sure the EXISTS check is not case sensitive, e.g.

WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'


Assuming that multiple "words" are separated by a space character.

It's not clear whether you want a compare total counts across the whole table or counts per row. for the whole table:

SELECT word, count(*) AS ct
FROM   tbl, unnest(string_to_array(message, ' ')) word  -- implicit LATERAL join
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  100;  -- top 100

Or, faster, but less clean, with the set-returning function in the SELECT list:

SELECT unnest(string_to_array(message, ' ')) AS word, count(*) AS ct
FROM   tbl
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  100;

For natural language texts you could do a lot more: Remove punctuation characters, compare word stems etc. Full Text Search provides various tools. The debugging function ts_stat() seems particularly handy for this. For an English text:

SELECT word, ndoc, nentry
FROM   ts_stat($$SELECT to_tsvector('english', message) FROM tbl$$)
ORDER  BY nentry DESC
LIMIT  100;

ndoc .. the number of rows
nentry .. the number of occurrences (can be bigger with multiple instances in a single text.

The query returns the most common words in the column across the whole table - just like the first query, but now punctuation and stop words are trimmed and words are reduced to their English word stem.

To find the words contained in the most rows, use instead:

ORDER  BY ndoc DESC

One would think it should be possible to employ a functional GIN index on to_tsvector('english', message) to make this very fast. But I did not find a way in a quick test.


Merging tables, but ignoring duplicate columns

Proposed Solution #1

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;
INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;
DROP TABLE cities_new;

Explanation for Proposed Solution #1

Create Unique Indexes on (city,state_code) for both tables

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);

Create Temp Table with rows from cities that's not in cities_extended

CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;

Load the Temp Table into cities

INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;

Discard the Temp Table

DROP TABLE cities_new;

Proposed Solution #2

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(city,state_code,zip,latitude,longitude,county)
SELECT city,state_code,0,0.0,0.0,'' FROM cities;

Explanation for Proposed Solution #2

INSERT IGNORE only works with unique indexes and primary keys. In your case, a unique index on city,state_code was needed for cities_extended.

After making such an index, you can insert all columns except id.

You could also include id like this

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(id,city,state_code,zip,latitude,longitude,county)
SELECT NULL,city,state_code,0,0.0,0.0,'' FROM cities;

GIVE IT A TRY !!!


Restore Latest Copy of Full and Differential database From Azure Blob Container

Restore-DbaDatabase does have -AzureCredential as parameter.

Suggest you to download the latest version of dbatools and try it.

Alternatively, you can try ps_restoregene - which is powershell based script written by Paul brewer. (I have used it and its a life saver :-) )


'accidentally' set 'CHANGE MASTER TO' on wrong server

In earlier versions of MySQL, you would run this on the Master

STOP SLAVE;
CHANGE MASTER TO master_host = '';

and restart mysqld.

For MySQL 5.5/5.6, you would run this on the Master

STOP SLAVE;
RESET SLAVE ALL;

MySQL restart not required

For more details on this, please read Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL) from PERCONA MYSQL PERFORMANCE BLOG


Are there benefits to using more than 4 CPUs for MySQL?

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread.

However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, meaning that certain IO operations like flushing data, precaching and data purging can happen simultaneously, with some "laboratory" benchmarks by MySQL vendors showing that it can scale up to 64 cores under high stress.

Having said that, please understand that in most cases, a database like MySQL is not CPU-bound, but IO-bound, meaning that it is not the CPU power that is causing the bottleneck in latency, it is the read and write latency and throughput of the secondary storage. There are certain cases where you can be CPU-bound, for example, in some special cases while performing certain checksums in a FusionIO device, if you need SSL/other kind of encryption, if you perform complex mathematical operations at SQL level, or applying row filters that can be done exclusively on-memory.

If you have suffered CPU spikes in the past, or have constant high CPU usage, it may help, but only assuming you can parallelize the query computation: it won't made ALTER TABLES or SQL parsing faster for a single query. That, combined with the "lightweight" approach that many of us recommend when working with MySQL (trying to avoid business logic -stored procedures- at MySQL side) makes the laboratory benchmark that I mentioned before difficult to reproduce in reality. Getting to use the extra concurrency sometimes requires a recent version of MySQL and configuration changes. In general, for MySQL, it is way better to invest in more powerful cores than many of them.


The Filegroup PRIMARY for the database CRM running out of space

This isn't an error from your SQL Server. At least I couldn't find it in sys.messages. (If you didn't post the exact error message or if you have an additional error number, please adjust your post.)

You have some monitoring software which assumes that you want to get notified when a database is close to full (for some filegroup). You can choose to ignore that notification and rely on the autogrow functionality in SQL Server, or you can expend the size of the file manually. Up to you.

Manually growing can be better since you control when you do this. Not that growing a file block general operations, but if you wait for an autogrow to kick in, then you have one or more users that suffers during that autogrow (those who need more space at that point in time).

Also, make sure that you have reasonable autogrow settings and consider whether you want to turn on Instant File Initialization.


Normalizing a database without access to source data?

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it.  The two fundamental benefits to normalization are:

Data Integrity
Query Flexibility

The simple approach to normalization is to create a table for each person, place, thing, concept, or event.  By doing this, you have each characteristic of each person, place, thing, concept, or event of interest to you in one and only one place in the database.  The benefit of data integrity is achieved for when you insert and update data.  Because you have normalized you don't have redundant copies of the same characteristic in many places across the database, each with a different value, that you have to remember to update and keep in sync.  Second, you have only one place in the database to program checks when inserting or updating data to make sure the data is valid.  Since your data is coming in from many excel spreadsheets, normalizing your data gives you the opportunity to implement data integrity checks on the load to make sure you analysis is based on accurate data.  The benefit of query flexibility is achieved for when you want to read and analyze the data.  Because you have normalized the data you can connect up the tables in a flexible way based upon the question about the data you want answered, including only what you need to answer the specific question.  Second, this enables the database to return the answers to your questions much faster than if it had to scan through all the data, including the data not relevant to your question, in your un-normalized  tables .

Access is a simplified DBMS and does include a basic SQL processor that allows you to write queries and thus take advantage of the benefits of normalized data.  If you are eventually going to move to SQL Server, which is a full featured DBMS, then normalizing your data now will ease the transition and let you take advantage of the full capabilities of SQL Server and its very rich implementation of SQL.

As I mentioned in the beginning, to achieve these benefits you must be willing to do the up front programming to translate the data coming in from your various excel dumps and map the rows and columns in those spreadsheets to your normalized tables.  This is not a trivial exercise but doable using Access programming.  One approach would be to create tables that replicate the data as in the source and load the data into them.  These are known as stage tables.  Once you have the un-normalized data in Access tables you can then more easily write access code using SQL to extract the data from those stage tables, normalize it, identify data quality issues (say the same characteristic in two different excel dumps that should have the same value but do not), and load it into your normalized tables. This is the common method for normalizing data coming from an un-normalized source very common in subject area based data warehouses.

You will find this additional level of effort to be well worth it though once you have high quality, normalized data in your access database.  You report consumers will see that you are a real data professional when you show them examples where data quality was poor and you discovered that fact so it can be corrected in the sources.  Likewise, when they ask for a new report that analyzes the data in a very different way, you can rapidly create the new report using SQL to combine the data in the normalized tables in this very different way that wasn't originally anticipated.  They will be very impressed that you are able to do this quickly and easily!

I hope this helps explain why normalization would be of benefit to you.


Why would call to scalar function inside a Table Value Function be slower than outside the TVF?

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:

SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.


View operation timestamps for long running operations in PSQL console

There are various options for retrieving timing information while using psql.

The \timing command.
In scripts you can use

\echo `date`

to insert the date in the output.

You can have the date in prompt strings using eg

\set PROMPT1 = '%`date` %n@%m:%/%R%# '

For both 2 and 3 you can supply arguments to date to get the date in the format you want.

If using psql non-interactively, you can pipe the output through ts (from moreutils) or through awk '{ print strftime("%Y-%m-%d %H:%M:%S"), $0; fflush(); }'(GNU's version of awk is needed) to prepend a timestamp to each line of output.


Only describing postgres tables with "\d" (not sequences, indexes, views, ...)

How about simply using the \dt command? It's here to list tables only. You can get more help on PostgreSQL inner commands by issuing the \? command.

If you want to know what those commands do behind the scenes, you could also start psql with the -E option, as asked here. You could therefore rewrite this query to get exactly what you want, be it views, sequences, tables... or any combination of the above.


Select Data from two tables ONLY IF ID on both tables are SAME

Try this:

SELECT wl.id
FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id
WHERE  wp.product_id in (select product_id from wishlist_products_bought)


Schema for multiple identical queue tables

Answer originally left in comments by the question author

After reading up on partitioned tables, it seems that I could store multiple independent ring buffers in one since the partitions can be stored in separate filegroups, but I doubt I'm going to be able to sell the requirement for Enterprise Edition.

I am going to try for a poor man's partitioned table by creating pre-allocated queue slots in a single table using a clustered index on QueueId and SlotId.  As long as my queues don't grow beyond the number of allocated slots, I should be able to treat them as consecutive ring buffers for multiple logical queues.


left outer join - sort operations in the query plan - any ways of tuning this simple query?

You have an index that provides ordering by B.DT but

the plan first evaluates THE_ROW using this order
then the right hand sort orders by F.DT to evaluate THE_NUMBER_OF_RECORDS_ON_THIS_DAY
and finally the left hand sort puts things back into B.DT order for the THE_TOTAL_VALUE_FOR_THE_DAY.

You can get rid of one of the sorts by simply changing the order of the columns in the CTE so the F.DT one appears last (The connect item for this Unnecessary Sort is here)

WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0),
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

But you can get rid of both by changing the definition of THE_NUMBER_OF_RECORDS_ON_THIS_DAY to

CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY B.DT ) END

So it uses the same partitioning definition as the rest of the functions.

This shouldn't change anything in your example as your CASE expression will just assign 0 to any non matched rows anyway.

As for the rest of the plan see Partitioning and the Common Subexpression Spool

(Plan afterwards with no sorts)


Return only rows under certain constraints in join

SELECT * FROM parent
WHERE NOT EXISTS (SELECT 1 FROM child
                  WHERE child.parent_id = parent.id
                  AND child.status = 'at_home');


Drop constraints vs disable constraints during a data import

As recommended by Mikael Eriksson, I'd do the import without dropping/disabling the Foreign Keys.

The downside to this is if you want to delete any records who's values are referenced by an FK or want to update values such that the original value is no longer in the table and referenced by an FK.
In these cases you'd need to keep track of any such issues and decide how to handle each "conflict" (don't update/delete, alter the data in the other table or some other solution).

This will be quicker, easier and ensure data integrity compared to dropping/disabling the FKs, importing and then restoring the FKs.


Capture row with highest value from each percentile

If I understand correctly your objective is this:
After ordering by position_id, get the row with the highest position_id for each of 100 percentiles.
DISTINCT ON does it:
SELECT DISTINCT ON (ntile)
       NTILE(100) OVER (ORDER BY position_id) AS ntile, *
FROM   position
WHERE  activity_id = 1
ORDER  BY ntile, position_id DESC;

db<>fiddle here
Since the PK is on (position_id, activity_id), position_id is unique for each activity_id and we need no tiebreaker.
Can be a single SELECT without subquery because DISTINCT ON is applied after window functions.
See:

Select first row in each GROUP BY group?
Best way to get result count before LIMIT was applied


multiple insert queries after a set of with's

You need to put the INSERT INTO "name-job" into another CTE. You could also put the last insert into "name-city" into a CTE as well, and add a SELECT to get all the inserted ids (and anything else you need from the inserted rows) back.

WITH
  x AS
  (INSERT INTO public.name VALUES (DEFAULT) RETURNING id),
  y AS
  (INSERT INTO public.job VALUES (DEFAULT) RETURNING id),
  z AS
  (INSERT INTO public.city VALUES (DEFAULT) RETURNING id),
  xy AS
  (INSERT INTO public."name-job" (name, job)
   SELECT x.id, y.id FROM x,y
   RETURNING name, job),
  xz AS
  (INSERT INTO public."name-city" (name, city)
   SELECT x.id, z.id FROM x,z
   RETURNING name, city)
SELECT x.id, y.id, z.id, xy.*, xz.*
FROM x, y, z, xy, xz ;

If you want to insert more than one row in the tables, the query would need to be more complicated (use joins and not implicit cross joins).
The naming of the tables and columns is going to be a constant trouble and pain. I have found it much easier in Postgres if all table and column names are lower case and no special characters are used (like the dash you have used). Only underscores and digits.  You can avoid the need of using double quotes everywhere that way.


SQL:StmtStarting event originated from application

Yes, this is an ad hoc SQL statement that is not part of a module. It could be sent on its own or it could be part of a batch.

Though I'm not sure why you're using Profiler; it is notoriously horrendous for performance. Just take a look at what Joe Sack observed regarding the impact Profiler has on throughput and similar observations by Jonathan Kehayias.

Or what value you gain from collecting the Starting events. The Completed events tell you much more about what happened (including things like duration).


What kind of SSD to speed up querying a lot of data?

You can't fix a performance problem by throwing hardware at it.

Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.

In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index.  Or even "re-thinking" the problem.

Let's discuss the query and see if it can be sped up for $0.00.


Repair GTID from Restore with sql_log_bin=0 and then drop table

The issue is that you have this blob of data that was no way of being replicated.
When you put it on the master and it set the gtid purged variable it basically says: I have this data, but I can't give it to you.

What you can do here is to use this dump to provision the slave/secondaries servers.
So you can execute this update in the master, but when you add a new member before it joins you must also execute the dump there.
Then they both can say: we have this lump of data we cannot replicate so we are in sync. Then the joiner can fetch whatever it is missing from the primary .


How does TOPN influence a query with group by?

My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Speaking from a SQL Server perspective, it depends. Here is an overview of what it depends on, and why:
Row Goals
Adding a top-level TOP (n) clause (with or without ORDER BY) has the same row goal effect as if a FAST (n) query hint had been specified. A row goal causes the optimizer to estimate plan costs on the basis of returning n rows quickly.
A small row goal therefore favours serial execution using non-blocking (pipelined) operators like (sort-free) merge or nested loops join, and semi-blocking (per group) operators like stream aggregate, over fully blocking operators like sort and eager hash distinct.
Combination operators, like hash join (which is blocking on its hash table build input, and pipelined on its probe input) may be chosen if the build input is expected to be small, and the build input is not expected to rebind much (or at all).
Pipelined execution
Where the logic of the query allows it, and suitable indexes exist, the optimizer may well be able to find a fully pipelined (or perhaps, semi-blocking) execution plan. A semi-blocking plan that blocks per group is potentially just fine, so long as the groups are small.
Even with optimal indexing, in some cases, it may be necessary to express the query using just the right syntax, perhaps also using various query and table hints, to obtain the most pipelined execution plan possible. It can be very tricky to achieve in all but the simplest cases.
A pipelined/semi-blocking plan means the SqlDataReader.Read() can fetch the first and subsequent rows with minimal latency. Essentially, both the C# code and the SQL Server execution engine are streaming rows. This is the case where specifying TOP (n) will make the most difference.
Default strategy
Where TOP (n) is not specified, the query optimizer targets total execution time for the full potential set of rows the query is expected to produce. This change in optimization goal tends to favour parallelism, fully blocking sorts & eager hash aggregation, and partially-blocking hash joins, for example. This would typically produce the full set significantly quicker than running a pipelined/semi-blocking plan (if available) to its final conclusion.
Where one or more blocking operators appears in a plan, the time-to-first-row may be a sizeable fraction of the potential overall execution time. This means the first SqlDataReader.Read() call will block for a long time. Stopping the DataReader after n rows are received by the client will have a relatively minor effect on elapsed time in this case, because the majority of the SQL Server work is already one before the first row becomes available.
Conclusion
How much effect adding TOP (n) has therefore depends at least on:

There being a pipelined execution plan solution (even in principle)
Suitable access methods (indexing)
Query syntax and optimizer features / limitations
The skill level and experience of the query developer

Example
Using the ContosoRetailDW sample dataset, where table FactOnlineSales has 12,627,600 rows. A query based on the template in the question is:
SELECT
    FOS.ProductKey,
    DS.StoreKey,
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

Adding a useful index:
CREATE INDEX i
ON dbo.FactOnlineSales
    (ProductKey, StoreKey)
INCLUDE
    (SalesAmount);

The execution plan is:

This runs for 1650ms. Notice the sort and hashing operations.
With TOP (n)
Choosing n = 50 arbitrarily, the query is now:
SELECT TOP (50)
    FOS.ProductKey,
    DS.StoreKey,
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

and the execution plan:

This runs for 57ms using pipelined iterators and per-group semi-blocking stream aggregate (instead of blocking eager hash).
Related question: How (and why) does TOP impact an execution plan?


How to choose a collation for international database?

The C collation is the right choice.

Everything is a bit faster without locale. And since no collation is right anyway, create the database without collation, meaning with C.

It may be a pain to have to provide a collation for many operations. There shouldn't be a noticeable difference in speed between the default collation and an ad-hoc collation, though. After all it's just unsorted data, and collation rules are applied when sorting.

Be aware that Postgres builds on the locale settings provided by the underlying OS, so you need to have locales generated for each locale to be used. More in related answer on SO here and here.

However, as @Craig already mentioned, indexes are the bottleneck in this scenario. The collation of the index has to match the collation of the applied operator in many cases that involve character data.

You can use the COLLATE specifier in indexes to produce matching indexes. Partial indexes may be the perfect choice if you are mixing data in the same table.

For example, a table with international strings:

CREATE TABLE string (
   string_id serial
  ,lang_id   int NOT NULL
  ,string    text NOT NULL
);

And you are mostly interested in one language at a time:

SELECT *
FROM   string
WHERE  lang_id = 5  -- 5 being German / Germany here
AND    string > 'foo' COLLATE "de_DE"
ORDER  BY string COLLATE "de_DE";

Then create partial indexes like:

CREATE INDEX string_string_lang_id_idx ON string (string COLLATE "de_DE")
WHERE lang_id = 5;

One for each language you need.

Actually, inheritance might be a superior approach for a table like this. Then you can have a plain index on each inherited table containing only strings for a single locale. You need to be comfortable with the special rules for inherited tables, of course.


I don't believe that full text search will match based on the back end of the word being passed in.  You could have it match "Wher*" but not "*here".  This is called prefix matching.

  < prefix term> ::= {"word*" | "phrase*" }

Trying to match based on "*here" would be a VERY expensive match for the full text engine to do.


Why MySQL doesn't use indexes in view with union select?

Because it is a kind of bug in the old MySQL version. UNION effect using indexes.
In the versions older than the 5.7.3 version of MySQL, UNION statement works with a temporary table. It means, first of all, your data move to the temporary table and read them from the temporary table before that implement filtering.

The server no longer uses a temporary table for UNION statements that
meet certain qualifications. Instead, it retains from temporary table
creation only the data structures necessary to perform result column
typecasting. The table is not fully instantiated and no rows are
written to or read from it; rows are sent directly to the client. The
result is reduced memory and disk requirements, and smaller delay
before the first row is sent to the client because the server need not
wait until the last query block is executed. EXPLAIN and optimizer
trace output will change: The UNION RESULT query block will not be
present because that block is the part that reads from the temporary
table.

Check out version 5.7.3 optimizer fixes.


Unable to query remote schema object using partial database link name

I had exactly the same problem: migrated Oracle 11.2 database to new installation 12.1 via DataPump. Database links in then 12c only worked when accessed with fully qualified names, but the application had been coded using only their partial names.
The problem was the domain of the new database's Global Name was different - see http://www.dba86.com/docs/oracle/12.1/ADMIN/ds_admin.htm#ADMIN029 (search for 'partial' within the dblinks section).
I created additional dblinks with fully qualified names using new domain name,  by extracting the ddl from the 11.2 database:
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;
 - see excellent hints from
 Alex Lima https://oraclespin.com/2008/03/28/how-to-extract-the-db-links-ddl-with-the-password
and Simon Krenger https://www.krenger.ch/blog/find-password-for-database-link/


Trigger or rule to convert all strings to lower case

Because we're talking about email addresses you should first read my answer to the question, What is the best way to store an email address in PostgreSQL?. I would adopt that method. It's not too late.

That said, the code above is certainly not ideal. You should instead just migrate to citext. This overrides the = to essentially be lower(a) = lower(b)

ALTER TABLE your_table
  ALTER COLUMN the_column
  SET DATA TYPE citext;


Can I turn auto-shrink off while on production server with people connected?

I have to admit I expected this to require a lock on the database which would mean you couldn't do it while someone else was in the database.  But just to be certain I did some testing and was able to update the setting while I had an open transaction on a table in the database.

So technically yes you could make the change during the day.  However, just because you can doesn't mean you should.  In general any changes like that should wait until after hours (or a maintenance window).  Unless of course auto_shrink is causing you production issues that you don't feel like you can wait on.

It's definitely a good idea to turn it off as soon as you can.  There are a few edge cases I've heard of where it's helpful but not very many.


pgAdmin-III - Option to load a favorite automatically when opening query window

There is no direct option, but I use command line option to load my favorite sql automatically at starting pgAdmin.

for exmaple in windows :
start "" "...../pgAdmin3.exe" -q -f "favorite.sql"

-q options means that you launch query tool only.
-f "filepath" instructs sql file automatically opened in query tool editor.

detail of options are
http://www.pgadmin.org/docs/dev/commandline.html


Do I need "New SQL Server Failover Cluster installation" to run Availability groups with manual failover?

If you want to just use stand alone instances - which is the most common method of implementing availability groups [stand alone to stand alone] - then you only need to do a stand alone install. So, yes, you can do the stand alone if that's what you're looking for.

It is possible to use a SQL Server failover cluster instance install to also work with availability groups but it is not required.


PostgreSQL: pg_upgrade failing because hstore incompatible

So I am having the same problem now that 9.6.1 is out. Here is the only thing I have found so far on-line but haven't tried it out for myself yet. I'll update this answer once I've been able to successful solve the problem.

EDIT: I followed these steps and everything works great. Hopefully when I upgrade to 9.7 it won't be so hard.

Currently on 9.5.4_1

Upgrading to 9.6.1

The answer below is take from this comment with the answer provided by Felix Bünemann. I have modified the steps for my version numbers (see above) and expanded some of the steps with explanations.

  1) While still on [9.5] make sure your hstore databases no longer use the deprecated => operator:

$ for db in `echo 'SELECT datname FROM pg_database;' |/usr/local/Cellar/postgresql/9.5.4_1/bin/psql -t`;do echo $db; echo 'ALTER EXTENSION "hstore" UPDATE;' | /usr/local/Cellar/postgresql/9.5.4_1/bin/psql $db;done

Running this in the terminal basically loops through each of your databases and tries to update the hstore extension for each. See here for more details. There are a few

  2) stop [9.5] server, brew upgrade postgresql and initdb the new data directory as in the guide

These steps are described in the main article and in my case were already done by this point.

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ brew update && brew upgrade postgresql
$ initdb /usr/local/var/postgres9.6 -E utf8

The next 3 steps are to create 3 shell scripts to replace (or wrap) 3 of the programs used in the upgrade process which will be explained later.

  3) Rename [9.6] pg_ctl to pg_ctl.bak and create a shell wrapper as pg_ctl:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.6.1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl.bak "$@"

  4) Rename [9.6] pg_dump to pg_dump.bak and create a shell wrapper pg_dump:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.5.4_1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dump.bak "$@"

  5) Rename [9.6] pg_dumpall to pg_dumpall.bak and create a shell wrapper pg_dumpall:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.5.4_1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall.bak "$@"

  6) Make the wrappers executable with chmod +x

$ chmod +x pg_ctl pg_dump pg_dumpall

As stated in a later comment by Felix:

  Essentially what the shell wrappers do is swap around between the [9.6.1] and [9.5.4] lib directories at the right points in the migration process. Because pg_dump needs the [9.5] versions and pg_ctl starts the server which needs the [9.6] ones.

Again remember I am swapping out my versions for everything which you will need to do as well.

  7) Start pg_upgrade as in the guide (this also works with the -k switch, if your low on space, like me)

pg_upgrade -d /usr/local/var/postgres -D /usr/local/var/postgres9.6 -b /usr/local/Cellar/postgresql/9.5.4_1/bin/ -B /usr/local/Cellar/postgresql/9.6.1/bin/ -v

  8) Remove the shell wrappers and rename the .bak files to their original name

  9) Swap your data directories as in the guide

$ mv /usr/local/var/postgres /usr/local/var/postgres9.5
$ mv /usr/local/var/postgres9.6 /usr/local/var/postgres

  10) Start the [9.6] server and analyze it using the generated analyze_all.sh script

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  # or, if you're running a current version of Homebrew
$ brew services start postgresql

  11) Enjoy postgres 9.6 and grab yourself a coffee for successfully working around the buggy pg_upgrade

  Note: If you’re using the pg gem for Rails, you should recompile:

$ gem uninstall pg
$ gem install pg


Order of DROP_EXISTING and ONLINE for CREATE INDEX

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in.

There is not a single millisecond without an index. (But there is a short-lived  Sch-M lock twice during the online build.)


And again ;) I recommend that too often these days.
The Data Model Resource Book, Volume 1.
Order management, address management - solved, problems explained, well documented. Complex but you can simplify - just reading it will really help you understand the problems at hand.


adding and removing a node from a mongo replica set

The command should be fine to run. Why ???

Each rs.add() and rs.remove() should trigger an election. Since the priorities of the remaining nodes have unique values:

_id : 7 as priority : 20
_id : 8 as priority : 10
_id : 9 as priority : 1

you can say the election is rigged. The primary node should remain the primary.


Comparison operators give wrong results with keyset pagination

Floating point numbers are subject to representation/rounding errors.  When translated to the client and back again, it is unlikely to be exactly equal to the original.


Oracle 12c database cannot log in as a local user

Strangely, changing my service name to UPPERCASE has helped!

Changed case of service name to UPPERCASE in tnsnames.ora for pluggable database ("ORCLPDB" in my case).
Follow below commands:

Note: service name in uppercase while connecting ex SCOTT/TIGER@ORCLPDB

Not sure why this has worked for me !!


Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

Actually it will work. Most SSB verbs accept parameters for they arguments (except queuename for RECEIVE, of course). The parameters are of type sysname:

Create Procedure SendJoinRequest
   @from sysname,
   @to sysname,
   @contract sysname,
   @messageType sysname,
   @body XML
AS
Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @from
   TO SERVICE @to
   ON CONTRACT @contract
   WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dialog_handle MESSAGE TYPE @messageType (@body);
COMMIT