SQL Server: SET NOCOUNT ON still returning data Following the example from this page, I'm unable to stop a stored procedure from returning a select dataset.

Executing this:

SET NOCOUNT OFF;
GO
-- Display the count message.
SELECT 'should be displayed'
GO
-- SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT 'should NOT be displayed'
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Will return me this:

Is this a bug? Is there something wrong? I'm on a SQL Server 13.0.4206

You are looking at the wrong part of SQL Servers's output. NOCOUNT only controls the extra "row(s) affected" messages that are output after SELECT/INSERT/UPDATE/DELETE/MERGE operations, not the rowsets output of any SELECTs that don't have a destination specified. You can see this if you set SSMS to show output as text:

SET NOCOUNT OFF
SELECT test='NoCount is OFF'
SET NOCOUNT ON
SELECT test='NoCount is ON'
SET NOCOUNT OFF
SELECT test='NoCount is OFF again'
-- now lets try with operations that store the rowset instead of sending as output
SET NOCOUNT OFF
PRINT 'A rows affected count should follow'
SELECT test='NoCount is OFF' INTO #temptable
SET NOCOUNT ON
PRINT 'But there won''t be one for this statement'
SELECT test='NoCount is ON' INTO #temptable2
SET NOCOUNT OFF
PRINT 'A rows affected count should follow'
SELECT test='NoCount is OFF again' INTO #temptable3

will produce the output:

test
--------------
NoCount is OFF

(1 row(s) affected)

test
-------------
NoCount is ON

test
--------------------
NoCount is OFF again

(1 row(s) affected)

A rows affected count should follow

(1 row(s) affected)
But there won't be one for this statement
A rows affected count should follow

(1 row(s) affected)

or if you leave SSMS set to show output in grids, you'll get the grids in the results tab and the following in the messages tab:

(1 row(s) affected)

(1 row(s) affected)
A rows affected count should follow

(1 row(s) affected)
But there won't be one for this statement
A rows affected count should follow

(1 row(s) affected)

There is no way (that I know of) to stop all rowsets being output by a stored procedure that wants to output them.

You can use the form INSERT <table> EXEC <procedure> <params> to put the output into a temporary table (then just drop the table or let it be dropped as your session ends) to hide the first set of results, but this has a number of significant limitations:

The table must already exist, you can't do the equivalent of SELECT <stuff> INTO <newtable> FROM ..., which adds a chunk of extra code (to create the table)
As with any other INSERT either the table must have the right number of columns or you need to specify the destination columns, and in either case they must, of course, be of compatible types
INSERT <table> EXEC ... can not be nested, so this will break if the procedure any dependencies it may have also use INSERT <table> EXEC ...
It can only capture one result set, so won't work as you desire if the called procedure returns more than one

Another thing to note about NOCOUNT is that the called stored procedure may override the setting you specify by using SET NOCOUNT {ON|OFF} itself so it might output counts ignoring your setting and you'll need to check/reset it after each call to make sure it is how you want it (see Artashes's answer for how to use @@OPTIONS to check).


Preferred approach to implement multiple many-to-many relationships? I am building a database that includes the following tables:

pages
users

A particular user can access (or is associated with) a page with one of the following roles (permissions):

Owner
Administrator
Editor

What will be the best approach in this kind of situation? Either to setup join tables like this:

owners (page_id, user_id)
administrators (page_id, user_id)
editors (page_id, user_id)

Or using a single join table:

pages_users (page_id, user_id, role)

Also, if the second approach is preferable, should role attribute be a string (such as 'owner', 'administrator', or 'editor') or something like role_id and the roles defined in a separate table as follows:

roles (id, name)

If there's a completely different approach which fits the need in a more appropriate manner, please let me know.

Also, is there any special name for this type of relationship(s) in database jargon? I am unable to craft the question's title right, edits are welcome.

Thank you for your answers

Since the primary key (page_id, user_id) is same for all roles it would be better to put all roles in a single table, the one you named pages_users.

The question on using role_id and a separate roles table is a matter of personal preference. I would use a separate table, at least if I'm going to populate an UI component list based on the choices.


Table with Clustered Columnstore Index bigger than a Clustered Rowstore Index We have a query that is doing an index scan on a 36 million row table that has a Clustered Columnstore Index. All the cost of the plan is pretty much on the index scan of this Clustered Columnstore index. I dropped the Clustered columnstore index and replaced it with a Clustered rowstore index, and created a non clustered covering index that covers the query in question. Performance of the query increased from 40 minutes down to a minute. Checking the Properties of the table after adding a Clustered Rowstore index, i noticed that the Data space of the table was greatly reduced. When it still  had a columnstore index, Data space was at 34,153MB. After changing it to rowstore index, its now only at 4,163MB. My question is, how is it that the size of this table is bigger when it had a clustered columnstore index when columnstore indexes supposedly compresses the data in a columnstore format?  I appreciate anyone that can provide a good link to read on for a similar issue.

Unfortunately, you destroyed all the evidence that could have lead to an answer when you got rid of the CCI.

I'd suspect a lack of maintenance (rebuilds/reorgs) lead to uncompressed rowgroups, or lots of deleted rows leftover in them.

One way to test this would be to create a second copy of the table with a CCI on it and see if it's still larger. A good way to get answers about what's going on in column store indexes generally is NikoNeugebauer's CISL GitHub repo.


Distributed Availability Group with Manual Seeding I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding.  I can get the automatic seeding working but when I try to manually seed I cannot get the secondary database into the AG on the forwarder.

If I add the distributed AG to the secondary before I try to add the database to the regular AG I get the following message:

Msg 41190, Level 16, State 7, Line 22
Availability group 'MYDB' failed to process add-database command.  The local availability replica is not in a state that could process the command.  Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

If I try to add the DB first without joining the Distributed AG on the secondary, I get the following message because it thinks it should be a primary:

Msg 927, Level 14, State 2, Line 22
Database 'MYDB' cannot be opened. It is in the middle of a restore.

I have none of these problems with automatic seeding.  Everything just magically works.  All of the examples I find online use automatic seeding.

thanks in advance

TL;DR:

It sounds like from the current description and comments that you may have added databases to the AG on the forward before joining the DAG. Instead, join the DAG first and then add the databases in the following order:

Create AG1

Add Databases to AG1

Create AG2 (no databases)
Create the DAG

Join AGs 1 & 2 to the DAG from AG1
Join AGs 1 & 2 to the DAG from AG2

Add databases to AG2
Profit?

Long(er) form answer

Assuming lots of things like...

Listeners/Cluster/Instances are already configured
There is already an AG...

on both the PRIMARY and the FORWARDER
that is not the same AG
and neither are already a member of another DAG
and the FORWARDER AG is empty and ready to seed

You have shared storage that is accessible from both replicas

... you can... The following scripts are in sqlcmd format.

Step 0. Disable log backups on |OLD_AG| (optional)

You can ignore this step if:

If you don't mind a potentially "moving target" LSN at the very end of the process or
You can complete the entire process inside the log backup window of all databases in the AG

Step 1. Create the DAG

CREATE once on the current primary and ALTER ... JOIN on the prospective forwarder. Execute as an appropriate service account so you don't end up with parts of the architecture owned by your user account.

On the current PRIMARY...

:connect |OLD_AG|.|DOMAIN|
execute as login = 'sa'

-- double check local replica is manual seeding first
alter availability group [|OLD_AG|]
    modify replica on '|THIS_REPLICA|'
    with (seeding_mode = manual);

create availability group [|DAG_X|]
    with (distributed)
    availability group on
        '|OLD_AG|' with (
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        ),
        '|NEW_AG|' with (
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        );
go

On the prospective FORWARDER...

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter availability group [|DAG_X|]
    join availability group on
        '|OLD_AG|' with (
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        ),
        '|NEW_AG|' with (
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        );
go

Step 2. Full Backups

Did you know you can append log backups to copy_only fulls? Neither did I until very recently! But by using copy_only here you can

protect your backup chain from any migration tomfoolery and
reduce the length of the restore chain at the end of the process.

exec as... is not strictly necessary for this step.

:connect |OLD_AG|.|DOMAIN|

backup database DB1 to disk = N'\\my.shared.storage\backups\DB1.bak'
    with copy_only, compression;
backup database DB2 to disk = N'\\my.shared.storage\backups\DB2.bak'
    with copy_only, compression;
go

Step 3. Restore

Once again, executing as an appropriate service account.

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

restore database DB1 from disk = N'\\my.shared.storage\backups\DB1.bak'
    with norecovery;
restore database DB2 from disk = N'\\my.shared.storage\backups\DB2.bak'
    with norecovery;
go

Step 3(b). Did you leave the log backups on?

Nbd, but append them now ¯\_(ツ)_/¯

Executing as an appropriate service account
with norecovery

Step 4. On the Forwarder, join DBs to new AG

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter database DB1 set hadr availability group = [|NEW_AG|];
alter database DB2 set hadr availability group = [|NEW_AG|];
go


Oracle Data Pump Import imports no records When we do an Oracle Data Pump import of our database, it runs without significant errors.  However, on one table Oracle reports that 0 records have been imported.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCHEMA1"."TABLE_NAME"                   34.26 GB       0 out of 402776 rows

The import does create the table.  It just does not import any rows.  There are other miscellaneous errors during the import -- none of which apply to this table.

If we drop the table and do a table-level import, it again creates the table but imports 0 rows.

We are using a basic data pump command line.

userid="sys/********@INSTANCE AS SYSDBA" SCHEMAS=(SCHEMA1, SCHEMA2, ...) DIRECTORY=DPUMP_DIR LOGFILE=LOGFILE.IMP.LOG DUMPFILE=DUMPFILE_%U_DATE.dmpdp

We are trying to figure out why the rows are not being loaded and to get them loaded.

I suspect this may have something to do with you starting impdp as sysdba. Please see the note in http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL824:

"Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users."

Try your command as system.


Distributed - across multiple databases or servers - Transactions I have some doubts related to transactions in SQL. I perform below steps.

Connection 1 open for DB1 database
Begin transaction for Connection 1
Run the commands on Connection 1
Connection 2 open for DB2 database
Begin transaction for Connection 2
Run the commands on Connection 2
Commit transaction for Connection 2
Commit transaction for Connection 1

If  commit transaction on Connection 1 fails(rollback) then transaction on connection 2 should rollback.
Is it possible? How it can be done?

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).


Migrating database live while being severely handicapped I "inherited" a web application which is designed and implemented horribly (both the application and the database). For example, the main data is stored using a sort of emulated key-value storage in a Postgres 8.2 database, making it virtually impossible to extract useful data from it in a reasonable amount of time.

Currently I'm working hard on replacing the entire application + database, however it will take a few months before the new application is finished. This is a problem since the website is really slow due to the extremely bad database design and even worse queries. Therefore I'm planning to fix the database design and the queries on the live site until the website has an acceptable load time as a temporary solution.

I do however have a few limitations to work around, the most problematic ones are:

I'll have to do everything from inside a proprietary build CMS.
The queries are all distributed over many many files, and there's no way to search for them. Therefore it's next to impossible to guarantee that I've updated all queries to a particular table.
I don't have direct access to the database, but I can execute queries using a query editor in the CMS, but only to tables owned by the application (so no pg_* tables for me).
I don't have access to a developer environment, nor can I create one. Everything has to be done live.

So basically I'll have to do a migrate a database live over the course of a few days, while simultaneously updating the application live such that it can handle the new application, without being able to search for the usage of each table.

With all these handicaps taken into consideration I came up with the following plan:

Create initially empty tables to store the data in a sane way.
Create triggers on the old tables which can sync the data with the new ones.
Export the data from the old tables to the new tables & enable the triggers.
Replace the queries one by one.

Using this strategy I will have 2 synced databases after step 3. Initially all queries will go to the old database, but while I'm updating the queries slowly the old database will be used less, and the new one more.

Taking this "sub-optimal" situation in mind, is this a good strategy to fix some of the problems? What things should I take into consideration while doing this?

Note that I fully understand that this is a very risky suicide mission. However, I'll have to do something in a short amount of time, otherwise the website becomes entirely unusable.

If you can, it would be better to do views and triggers rather than bilateral replication of data between both sides.

So I would modify your plan slightly:

Create a sane schema
Create an emulation of the old schema using views.
Create triggers on the views to write to the new schema instead of the old.

The question is what comes after this.  Since you are building a new tool, it is good to be in maintenance mode.  This means, I think, only fixing the slowest of the queries that cause you problems.


ignore table does not exist error I'm creating a script to automaticly copy from multiple access databases into sql server databases.

My script currently creates a new sql database and creates all of the tables.  It then trys to insert the data from an access database (using linked servers) table into the newly created sql database table.

The problem is not all of the databases have all of the tables.  In these cases it is ok for the sql server table to remain empty.  However, the insert throws an error because the table does not exist in the linked server.

Is there any way to tell sql server to ignore the table not found error so that it will continue on to the next insert statment?

Does TRY/CATCH properly handle this?

BEGIN TRY
    INSERT dbo.localtable SELECT ... FROM linkedserver...table;
END TRY
BEGIN CATCH
    PRINT 'this table was skipped';
END CATCH


Availability Group Monitoring Redo Rates We currently use Idera Diagnostic Manager to monitor our SQL environment, which now includes a couple of Availability Groups. Idera monitors the redo synch rates and starts to complain when the rates are too low.

My question is this, should we care about the redo rate when there is nothing in the redo queue? It would seem to me that the Log Send Queue and Redo Queue are better indicators of when the replicas are behind on replication. Would an alert based on the these queues be a better alert than alerting on redo alerts?

Thanks.

Should we care about the redo rate when there is nothing in the redo queue?

If there is nothing in the redo queue and the send queue is empty then I wouldn't worry about it as there is literally nothing to be done. This will happen on AGs that have light usage or usage based on specific hours and is then idle the rest of the time.

If, however, there IS something in the log send queue and/or redo queue we are extremely interested in the redo rate.

We are ALWAYS interested in blocked redo threads as well.

  It would seem to me that the Log Send Queue and Redo Queue are better indicators of when the replicas are behind on replication. Would an alert based on the these queues be a better alert than alerting on redo alerts?

These all measure different aspects of the AG and where performance might be lacking. For example, we'd want to troubleshoot a large log send queue much differently than a low redo rate. Sure, they may be related but we'd be looking in two different areas at first.

It would be best to build in some logic for the alerts. I would still keep the redo alert and add one for blocked redo threads (since that one wasn't mentioned), but I would monitor the full stack as redo rate is just giving you one small piece of the puzzle.


Reducing I/O operations for often-updating data in MariaDB I have a big table where I update something like 10k rows per second on average, but just one column. Currently it looks to me that MariaDB writes those changes rather instantly onto the disk creating a constant significant write I/O.

However, for this one column, I don't care too much, if the data is saved to disk in real time, I rather want to have less write I/O on the disk and a higher risk of data loss.

Is there any way I can tell MariaDB to cache this data for a bit longer time in memory before writing it to the disk?

Basically, the column is a "information_last_verified_at" timestamp that I regularly update and I can live with it being a few minutes off in the unlikely event of some server problems.

There are two sources of write I/O when updating table data: transaction log flushes and checkpoints (dirty buffer pool page flushes). You cannot do much about the former, as it happens every time you commit, apart from placing your log onto faster storage.

Checkpoint activity can be somewhat configured (for InnoDB) by adjusting innodb_max_dirty_pages_pct, innodb_adaptive_flushing*, innodb_adaptive_checkpoint, and innodb_io_capacity parameters, among others -- there are some more details in this blog post. You'll need to monitor checkpoint activity to figure out what changes make sense in your situation.


Set timeout expiration in ms sql 7 I'm trying to perform a query in a transactional database that was used for more than 3 years. But it always says timeout expired after running for about 30 seconds when I try to run this query:

SELECT COUNT(*)
FROM tblBPLedgerBill LEFT JOIN
    tblAssessment ON
    tblBPLedgerBill.strAssessmentID = tblAssessment.ObjID
WHERE tblAssessment.intType = 0 AND curSurcharge != 0

Is it possible to adjust how many seconds the query should take before it terminates it.

Check the connection properties.
Click Option button when connecting to the Management Studio (EM).


Moving a database to a new server My database server is being decommissioned at work. We have a new database server to take its place. Both servers have the same operating system and SQL Server 2008. I need to move all my databases and data over to the new server.

What is the best way to do this to ensure as few problems as possible?

My data is not just tables, views and stored procedures. There are also several SQL Server Agent jobs and several database maintenance plans and scheduled backups.

The system is not being used at night, so I have a late night opportunity to take the database offline, if need be.

Can I just copy the MSSQL directory over to the new server? Or should I just backup each database individually? If I do backup each database, should I back up the system databases (like master, model and msdb) too?

Doing a backup and restore will be a good way to copy over all the user databases.

Backup DBs on the source server.
Copy the backup file to the destination server
Then restore the DBs onto the destination server.

The the system DBs are a bit different. Follow this link for one possible way. Then again, it depends what you need from the system tables. The master contains your SQL logins, which you can script out. Here is a link for scripting out the logins.


Does `COUNT` discard duplicates? My professor taught me this SQL statement:
SELECT COUNT(length) FROM product

will return 2 with the following dataset:
product
|id | length | code |
|-------------------|
| 1 |    11  | X00  |
| 2 |    11  | C02  |
| 3 |    40  | A31  |

She justified it by saying that COUNT doesn't count duplicates. I disagreed. After trying a lot of DBMS, I've never found one that has this behaviour. Does such an DBMS exist?

Either your professor made a mistake or you misunderstood what she said. In the context of relational DBMSes, as implemented by various vendors, the aggregate function COUNT(<expression>) returns the number of non-NULL values of <expression> in the result set (or a group).

There is a special case of COUNT(*), which returns the number of rows in the result set or group, not the number of values of anything. This is equivalent to COUNT(<constant expression>), such as COUNT(1).

Many databases support COUNT(DISTINCT <expression>), which will return the number of unique values of <expression>.


Query With Date Ranges I have a stored procedure that I want to 1st gather data between a @startdate & @enddate then I want to compare that data to the previous year.

I tried the below syntax (for testing obviously) to ensure I was getting my desired dates output before I added to my production syntax.  However, this gives me an error of

  Msg 102, Level 15, State 1, Line 11
  Incorrect syntax near '–'.
  Msg 102, Level 15, State 1, Line 14
  Incorrect syntax near '–'.

This is the syntax I tried.  What would I alter in order for my required dates to be output?

Declare @startdate date = '20170101'
    ,@enddate date = '20170212'

--one year prior to @startdate
Select CAST(DateAdd(yy, -1, @startdate) As Date)

--One year prior to @enddate
SELECT CAST(DateAdd(yy, -1, @enddate) As Date)

--This will get first day of prev yr
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0,
        DATEADD(YEAR, –1, @startdate)), 0)

--This will get last day of prev yr
SELECT DATEADD(MILLISECOND, –3, DATEADD(YEAR,
        DATEDIFF(YEAR, 0, DATEADD(YEAR, –1, @enddate)) + 1, 0))

EDIT
What I want to return is

01/01/2017
02/12/2017
01/01/2016
12/31/2016

Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:

Here is the easy way to compare the same range as the year before:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

SELECT @startdate, DATEADD(YEAR, -1, @startdate),
       @enddate,   DATEADD(YEAR, -1, @enddate);

This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.

When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:

-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
  AND col <  DATEFROMPARTS(YEAR(@startdate), 1, 1);

-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
  AND col <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

To sum up:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

-- sales from that period this year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= @startdate
    AND some_datetime_column <  DATEADD(DAY, 1, @enddate);

-- sales from same period previous year (again, not accounting for leap special case):

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
    AND some_datetime_column <  DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));

-- sales from all of previous year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
    AND some_datetime_column <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

Suggested reading:

What do BETWEEN and the devil have in common?
Bad Habits to Kick : Using shorthand with date/time operations
#BackToBasics : Dating Responsibly


calculate duration between intervals of 15 minute so i've been trying to find an answer for a problem i have.
i have a client with call information table.
the table look  like this:

and he want me to create a new table that splits every record to hour quarters (0-15,15-30,30-45,45-60) and using the login time and duration of the call calculate for each row how many seconds in each hour quarter he was in the agentsate. so if he was in a talking state for 120 seconds(duration column) and he started talking on logindatetime of 14.3.18 11:44 i should add two columns to the new table for each quarter in the hour and what was the duration for it.
something like this

does anyone have an idea?
i thought about a cursor but i dont understand how to do it and it sounds like its going to have a big impact on performance.
the duration in seconds can be up to a day, not only minutes or seconds.

thanks in advanced.

3 Step approach:

generate the intervals.
match calls against overlapping ranges
figure the duration of each call within that range

create table #calls
(id int not null identity primary key,
starttime datetime,
duration int)

insert into #calls (starttime, duration) values
('2018-05-14 8:33:00', 45) -- 1 period
,('2018-05-14 8:42:00', 460) -- 2 periods
,('2018-05-14 8:55:00', 3500) -- 5 periods

--Assume table [Number] (N int) with rows 1 to 1 mil.
;
with Intervals as
(select top (150000) dateadd(mi, n*15, '2018-01-01') interval_start,
dateadd(mi, n*15+15, '2018-01-01') interval_end
from Number order by N)
select * ,

case
--Full 15 min
when starttime <= interval_start and dateadd(ss, duration, c.starttime) >=  interval_end
then 15*60
-- partial, overlapping start
when starttime <= interval_start and dateadd(ss, duration, c.starttime) <  interval_end
then datediff(ss, interval_start, dateadd(ss, duration, c.starttime))
-- partial, overlapping end
when starttime > interval_start and dateadd(ss, duration, c.starttime) >= interval_end
then datediff(ss, c.starttime, interval_end)
-- partial, wholly contained
when starttime > interval_start and dateadd(ss, duration, c.starttime) < interval_end
then duration
else null
end  interval_duration

from #calls c inner join Intervals
on dateadd(ss, duration, c.starttime) >= interval_start
and c.starttime < interval_end

go

drop table #calls


Why aggregate operator used after unique index scan I have a table with a unique index filtered for non-nullable values. In the query plan there is use of distinct. Is there a reason for this?

USE tempdb

CREATE TABLE T1( Id INT NOT NULL  IDENTITY PRIMARY KEY ,F1 INT , F2 INT )
go
CREATE UNIQUE NONCLUSTERED INDEX UK_T1 ON T1 (F1,F2) WHERE F1 IS NOT NULL AND F2 IS NOT NULL
GO
INSERT INTO  T1(f1,F2) VALUES(1,1),(1,2),(2,1)

SELECT DISTINCT   F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL
SELECT  F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL

query plan :

This is a known SQL Server query optimizer limitation. It has been reported to Microsoft, but the Connect item (no longer available) was closed Won't Fix.

There are additional consequences of this limitation, including some that I wrote about in Optimizer Limitations with Filtered Indexes, the summary is quoted below:

  This post highlights two important optimizer limitations with filtered indexes:

  Redundant join predicates can be necessary to match filtered indexes
  Filtered unique indexes do not provide uniqueness information to the optimizer

  In some cases it may be practical to simply add the redundant predicates to every query. The alternative is to encapsulate the desired implied predicates in an unindexed view. The hash match plan in this post was much better than the default plan, even though the optimizer ought to be able to find the slightly better merge join plan. Sometimes, you may need to index the view and use NOEXPAND hints (required anyway for Standard Edition instances). In still other circumstances, none of these approaches will be suitable.


Why doesn't "Total Space Reserved" equal the sum of Data File space reserved and Transaction Log space reserved in the Disk Usage Report? When running the disk usage report, it's showing the "Total space reserved" as 275 GB but the "Data Files Space Reserved" is 279 GB and the "Transaction Log Space Reserved" is 1.7 GB. Why isn't the "Total space reserved" closer to the total of "Data Files Space Reserved" + "Transaction Log Space Reserved" ?
Thanks!

Those files can get out of sync.  Run DBCC UPDATEUSAGE
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql

Do not run DBCC UPDATEUSAGE routinely. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.


SqlPackage.exe unnecessarily drops and recreates indexed view with schemabinding Currently SqlPackage.exe used in conjunction with BitBucket for the deployment of the database schema changes.

Recently a pretty large indexed view has been created based on 3 source tables, which are key to the application. The process of creating the clustered index on the view and the accompanying downtime took around an hour to complete.

Lately a column had to be added to one of the source tables of the indexed view. Although the view has schemabinding, this shouldn't be a problem because the new column isn't used in the definition of the view (duh!). Although if we look at the script that has been generated by SqlPackage, it chooses to drop the indexed view to cope with the schemabinding although this is completely unnecessary. It seems to me that SqlPackage is unable to check the dependencies and therefore drops the view out of precaution.

If I simulate a similar situation on my local machine (SQL Server 2017 Dev edition), comparing DacPacs always result in the dropping and recreating of the view as well.

Now the question: does anyone know any workaround or any way to prevent SqlPackage dropping the view? I've checked all properties, but there doesn't seem to be any viable option there.

SqlPackage.exe often selects a hard way to do things.
We had a following process for issues like this one:
1. Generate change script on dev environments and look for any long steps, like the one you have.
2. Manually create a script to implement needed changes (not all changes, so in your case it will be the view update). Execute this script before SQLPackage.
3. Run SqlPackage again. It should notice that this view do not require changes and will implement only other changes.


When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have? Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)

I have a table that's got 19 million rows with about 30 indexes (so basically non trivial).

How much of a factor would the disk speed of the LOG & tempdb than if I was to have the same disk speeds as the data disk.

Edit:
Some more stats.

We have moved the LOG to the RAID data drive as test and unfortunately there was not improvement in the speed of the the REBUILD. It still too 1 hour.

tempdb is on it's own Raid10 array
LOG    is on it's own Raid10 array
Data   is on it's own Raid10 array
RAM > 100 Gb (yes 100Gb) and no limit on SQL using it for data or index creation

Note this is not a transactional system, rather a Data Warehouse.

Indexes are treated the same as any other data modification i.e. they are written to the log.  The performance of your log will directly impact the performance of all writes to your database.  If your LOG drive is 50 times slower that your data drive, you have serious performance limitations and should consider rectifying that as soon as possible.


Restoring Oracle DB using RMAN I am a application developer doing DBA work. I have to restore an oracle database on our test server. The databases are backed up using RMAN and the last db restore that was done on this server was in the year 2011. RMAN back up is running daily. Anyways I want to restore the test database to match the production production database. Can I copy the required datafiles and control file from the production server and place it on the test server at the exact location where the back up files are placed, or how to go about this. I want to perform a point in time recovery. I don't want to corrupt the production database. Since the production servers are backing up the database daily, I thought I would copy day before yesterday's copy and put it on the test server and give the path name to RMAN while doing point in time recovery..Please suggest me what to do..And this is the code I would use to recover, please let me know if it is ok. Thanks

SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
SET ORACLE_SID=
rman target / nocatalog
shutdown immediate;
startup mount;
 run
 {
  SET UNTIL TIME "TO_DATE('set to whatever time you want to recover','DD-MON-YYYY  HH24:MI:SS')";
  restore database;
  recover database;
  alter database open resetlogs;
 }

You don't want your test database to retain the same internal id number as the production database. Hence it would be best to use RMAN's command DUPLICATE, because it sets a different id (and also a new database name). This command is specifically designed to do what you require.


I am not able to create this DB2 SP. What is the issue with it? Not getting it I am not able to create this DB2 SP. What is the issue with it?

I get the following error:

  Category   Timestamp   Duration    Message     Line  Position
  Error 18/12/2013 09:43:46     0:00:00.031  - DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N  An unexpected token "PROTECTION" was found following "M, CUST_PREMIUM FROM".  Expected tokens may include:  "INTO".  11    271

I need to create sp accepting 2 input parameters and using same in select to return data and if no data found the raise exception saying no data found.

CREATE PROCEDURE "PROTECTION"."GET_PROSPER_PRE_APP_CUSTOMERS"
(IN "I_FSA_REF"  VARCHAR(6), IN "I_CUST_ID" VARCHAR(20))
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN
    SELECT FSA_REF, CUST_ID, CUST_DATE_INSERTED, CUST_EXPIRY, CUST_TITLE, CUST_FORENAMES, CUST_SURNAME, CUST_ADDRESS_1, CUST_ADDRESS_2, CUST_ADDRESS_3, CUST_ADDRESS_4, CUST_COUNTRY, CUST_POSTCODE, CUST_EMAIL, CUST_SEX, CUST_DOB, CUST_SUM_INSURED, CUST_TERM, CUST_PREMIUM FROM "PROTECTION"."PROSPER_PRE_APP_CUSTOMERS"
    WHERE FSA_REF = I_FSA_REF AND CUST_ID = I_CUST_ID;

    DECLARE handler_type HANDLER FOR NOT FOUND
    BEGIN
       SET MESSAGE_TEXT = 'NO DATA FOUND WITH GIVEN CONDITIONS';
       EXIT;
    END IF;

END;

You cannot use a simple SELECT statement in compound SQL. It must be SELECT INTO. Another problem with your code is that all DECLARE statements must come before any other statements.


How to protect a symmetric key? When a symmetric key is created we have the option to encrypted it by using at least one of the following: certificate, password, symmetric key, asymmetric key or EKM.

When a password is used, it it said that:

  When a symmetric key is encrypted with a password instead of a
  certificate (or another key), the TRIPLE DES encryption algorithm is
  used to encrypt the password. Because of this, keys that are created
  with a strong encryption algorithm, such as AES, are themselves
  secured by a weaker algorithm.

So, using password is not best way to protect it. I was not able to find any pros and cons for using the other techniques.

Can anyone tell is there are difference from security or performance matter or it is just a matter of choice?

As you show in your question, the Microsoft Docs say:

  When a symmetric key is encrypted with a password instead of a certificate (or another key), the TRIPLE DES encryption algorithm is used to encrypt the password. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm.

This is a simple warning that a key protected by a password is weaker than the same key protected by a certificate.

So, in order to compromise the symmetric key, you need to know the password.  An attacker might obtain the password by cracking the 3-DES encryption; they would then have access to symmetric key, and by extension everything that is protected by that key.

The take-away is, don't use a password to protect a key if the data protected by the key is valuable.  Use a certificate to encrypt the symmetric key.


Oracle user locks (dbms_lock): guaranteed order of requests granted? I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested.
In my tests it always was the case, but I couldn't find any official statement in the Oracle documentation.

So let's assume we have at least three sessions:

Session 1 requests the lock via dbms_lock.request(1) and is granted the lock immediately (as the lock is not yet held by any other session)
Session 2 requests the lock via dbms_lock.request(1) and has to wait
Session 3 requests the lock via dbms_lock.request(1) and also has to wait
Now Session 1 releases the lock via dbms_lock.release(1) (or trx end or session end)

Is it guaranteed that Session 2 will now receive the lock and Session 3 still has to wait until Session 2 releases? Or might Session 3 receive the lock before Session 2?

Edit: if the answer is "yes, guaranteed", please provide link to Oracle documentation, whitepaper, V$ view or the like.

Edit 2: Database version in question is 11g and up

Yes!

There is a wait queue and the first requester is the first served.
Beware of deadlocks...


How to work with multiple Postgres databases in psycopg2 I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure out a way how to do that using psycopg2.

So I was thinking I probably need two different cursors:

conn_local = psycopg2.connect(dbname='local_db', host='localhost')
conn_remote = psycopg2.connect(dbname='remote_db', host='some.other.server')

curs_local = conn_local.cursor()
curs_remote = conn_remote.cursor()

But how can I address those databases? For instance, when I try to join data from both tables:

curs_local.execute("""
    CREATE TABLE local_db.public.newtable AS
    SELECT remote_db.public.remotetable.rcolumn AS col_from_remote,
    local_db.public.localtable.lcolumn AS col_from_local
    FROM remote_db.public.remotetable, local_db.public.localtable""")

There will be an error in the style of psycopg2.NotSupportedError: cross-database references are not implemented: "local_db.public.new_table". The ATTACH TABLE command (as described in the solution here) does apparently not exist in Postgres / psycopg2.

Is it possible to work with multiple databases at a time? How?
Or will I have to copy (export / import) the data from remote_db to local_db first?

Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.

This does become more complicated in your schema definition but brings remote tables from host some.other.server database remote_db to appear as though they live on localhost in database local_db. A simple example on how to hook up the wrappers:

CREATE EXTENSION postgres_fdw;
CREATE SERVER some_remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'some.remote.server', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR local_user
  SERVER some_remote_server
  OPTIONS (user 'remote_user', password 'remote_user_password');
CREATE FOREIGN TABLE local_table_name (id int, value int)
  SERVER some_remote_server
  OPTIONS ( schema_name 'remote_schema_name', table_name 'remote_table_name');

Now locally you can just run

SELECT * from local_table_name

and the query will get executed against the remote host. Needless to say, this requires connectivity between the two servers.

Similarly, if you really have to, you can create a "remote" server against the localhost and point to a different local database for cross database queries. Feels dirty but it's possible.

As @a_horse_with_no_name mentioned though, this is not very efficient. If you find yourself doing this too frequently, you're not getting the most optimal performance and you'd better have very good reasons for keeping your databases separate at that point.


Inserting rows with WHERE condition Structure

1 table named: scan

2 columns named: id, and date

Problem

I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the condition is equal to 0.

Query 1

Show '1' if the latest ID row in the database has scan date of today otherwise show '0' when no today's date is present.

SELECT
  COUNT(scan.date) AS datecheck
FROM scan
WHERE scan.id = (SELECT
    MAX(scan.id) AS datecheck
  FROM scan
  WHERE scan.date = CURDATE())

Query 2

If there is no entry for today, meaning a results of '0' then create a new row with ID+1 and the current date.

INSERT INTO `scan`(`id`, `date`) SELECT (MAX(id)+1), CURDATE() FROM scan;

I have been trying for hours now with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.

Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert:

INSERT INTO scan (id, date)
SELECT (MAX(id)+1), CURDATE()
FROM scan
HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0;

To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)


General Question on how to setup db's for a social networking site I am currently responsible for designing the entire database setup for a start up website.  It is quite a responsibility and I want to get it right, not only for the site, but for my own experience.  And I know this is really a general question, and obviously I can't reveal table structures I plan to have because of proprietary info etc, but I was hoping that there was someone on here who had some experience with designing a usable website with heavy reliance on a db for functionality purposes.
Basically, I want to know all the pitfalls that could occur and the general best practices.  Things like, how normalized should the table structure be?  Is it really the best idea to store user pictures directly into a table, or to just store a link to its directory on the site?  I know this is a very general question, even if you could point me to general tutorials on such things I would appreciate it.  Thank you for your understanding.

There have been volumes upon volumes written on normalization. No one here can tell you how normalized your database should be without information. The best advice is to read and fully understand what normalization is and, once you have that understanding, you'll be able to apply it to your application and database design.

Storing images in database? All I can say is test test test. It's going to depend on the application, hardware, table-size, etc. Luckily, for something like that, it's easy enough to store both the image and path in the same table and you can easily QA each.

I say this with absolutely no malicious intent, but if you need to ask about database design at that high of a level, you may be much better off contracting an experienced DBA/Data Architect to do the design legwork once you have the application layer designed.


Designing a database structure for a permissions system Scenario

I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.:

A wants to allow B to access his name, birthday and work mail;
all other properties are private to B; and
A wants to allow C to access his name, phone number and private mail, etc.

Current considerations

I have a database scheme as shown in the picture below for an app that manages contact information for users:

The red table people contains basic information of the user, the yellow tables are arrays so that every user can have multiple email addresses, phone numbers etc. You may expose your contact data to other people by connecting to them as managed by the connection table.

The question

So my question now is, how can I design an efficient permission system that achieves the described goal?

"... the yellow tables are arrays so that every user can have multiple ..." -- Good.

Restricted access should be in the application layer.  No user should be able to write arbitrary SQL.

Dates should not be separated -- it will make inefficient to filter on a range of dates.  Anyway, where do you need "multiple dates"?

That's a lot of sensitive data; rethink storing all of it.


MySQL Slow Query Log - Long Query I enabled slow-query-log on my server, and set long_query_time to 10 seconds.

But what strange is, that queries that are taking in execution 0.0002 seconds are also being logged in my log file, and now my log file is about 100Gb , which means mysql is logging all queries.

I have look at the mysql-slow.log file and i haven't seen any query taking more than 1 second, note that long-query-time is 10 seconds and what is expected is to find queries that have taken 10 seconds and more in execution

An Example

# Query_time: 0.000278  Lock_time: 0.000072 Rows_sent: 28  Rows_examined: 65

Any advisors please

Turn OFF log_queries_not_using_indexes; it is virtually useless and can cause what you experienced.

If it is already off, then please show us one entire slowlog entry, together with the CREATE TABLE for the table(s) that are involved.  Also, what version are you using?

On most systems it is best to set long_query_time to no more than 2.


Does MySQL still handle indexes in this way? Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.

  If a table T is a MySQL table having four indexes (ndx1,ndx2,ndx3,ndx4)
  and you want to 'alter table T drop index ndx3;' here is exactly what
  happens under the hood:

  1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI.
  2) MySQL does 'alter table S add index ndx1 (...);
  3) MySQL does 'alter table S add index ndx2 (...);
  4) MySQL does 'alter table S add index ndx4 (...);
  5) MySQL deletes T.MYD and deletes T.MYI
  6) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI

Is this still true? Is his advice still valid?

  Given the same MyISAM table T having four indexes (ndx1,ndx2,ndx3,ndx4)
  and you want to 'alter table T drop index ndx3;' try this instead:

  1) create table T1 like T;
  This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
  2) alter table T1 drop index ndx3;
  This drops index ndx3 on the empty T1, which should be instantaneous.
  3) insert into T1 select * from T;
  This will populate table T and load all three(3) indexes for T1 in one pass.
  4) drop table table T;
  5) alter table T1 rename to T;

How do you all handle adding and removing indexes from large tables?

This is how MySQL 4.x did this and it used to severely aggravate me.
In fact, there was a formula I computed on how many such index maneuvers were needed
Table with 0 indexes and adding 1 index tooks 1 temp table
Table with 1 index   and adding 1 index tooks 3 temp tables
Table with 2 indexes and adding 1 index tooks 6 temp tables
Table with 3 indexes and adding 1 index tooks 10 temp tables (I had eyewitnessed this !!!)
.
.
.
Table with n indexes and adding 1 index took (n + 1) X (n + 2) / 2 temp tables
.
.
.
Table with 16 indexes and adding 1 index took 153 temp tables

Good News, MySQL 5.x does not do that !!!
If MySQL 5.x did this, I would be a PostgreSQL DBA today (No offense to PostgreSQL, it is an excellent RDBMS in its own right).
UPDATE
Oh my goodness, I read the post !!! That post came from me !!!
I never thought someone would dig this post up.
Please leave stuff like this dead and buried. Now I am having flashbacks !!!


Select a set of keywords from table1 and search for strings containing these keywords in table2 I have two tables: t1 and t2. There are three steps:

In t1 select a set_of_strings with id = 1 ("1a" and"3c").
From t1 select strings that correspond to OPTIONID ='A' and that contain substrings from the set_of_strings.

t1

ID  NAME
1   "1a"
2   "2b"
1   "3c"

t2

OPTIONID    TXT
  A     "7h 9t"
  B     "1a 8n"
  A     "2b 4r"
  A     "3c 6u"

Queries

SELECT
    NAME
FROM t1
WHERE ID = 1; -- 1a, 3c

SELECT
    TXT
FROM t2
WHERE OPTIONID = 'A' AND (TXT LIKE '%1a%' OR TXT LIKE '%3c%') --"3c 6u"

Question

How to automate the TXT LIKE '%1a%' OR TXT LIKE '%3c%' part?

LIKE'%1a%' is safe because of all entries have the same format, i.e. there is no 1a2 in TXT column.

Something like:

select t2.txt
from t1
join t2
    on t2.txt like '%' + t1.name + '%'
where t2.char = 'A'

Adding ID = 1 is left as an exercise for the reader.

I assume you just made an example up. If not, you should avoid using reserved words like CHAR as column names.

It's generally considered bad practice for sargability to use a wildcard on both sides of a field.  It may not matter in this instance, but if there was an index on that field, it would not be usable and could cause some performance issues.

It may be useful to use SELECT DISTINCT to cover the case where both '1a' and '3c' match the same row (which would otherwise result in the row returning twice).


Defining password complexity and its action on "grace" state USERS I have Oracle USERS linked to external applications that came to "grace" status.
I tried to change the assword time expiration to unlimited but it doesn't apply to those account (considering I guess that the "grace" status is responsible for that)

So, in order to keep the application's configuration the same, I wish to renew the passwords without changing them but the complexity policy doesn't allow it (two special characters or/and two capital letters)

    ORA-20000: password must contain 2 or more special characters
    28003. 00000 -  "password verification for the specified password failed"
    *Cause:    The new password did not meet the necessary complexity
               specifications and the password_verify_function failed
    *Action:   Enter a different password. Contact the DBA to know the rules for
               choosing the new password

For info, I migrated the database from 11c to 18 and those policies were simpler on the previous database..

So, first I am wondering how to edit the complexity policy
And also if it would apply to USERS that are in "grace" status ?

Thank you very much for your help

Have a nice day

The password complexity is managed by a function. You can edit the function to fit your requirements or disable it at all.

Check this link : https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/configuring-authentication.html#GUID-A39E191B-4A06-442D-94C7-5882B73DDCFA
On the section "Managing the Complexity of Passwords"

For the password "grace" time, you should check the Profiles and modify them accordingly.
On the same link check for "PASSWORD_GRACE_TIME"

If you want, you can disable the password verify function, assuming the all your users are in the default profile, alter profile default limit PASSWORD_VERIFY_FUNCTION NULL;


Is there a way to pass two SQL tables w/o JOIN for processing into R using MS SQL Server 2016? Is there a way to pass two SQL Server tables without a JOIN for processing into R using MS SQL Server 2016?

For Example, @input_data_1=TABLE, passes the 'TABLE' as a dataframe into R with default variable name InputDataSet.

Is there a way to somehow access other tables residing in SQL Server through R?

@input_data_1 can be a T-SQL statement, or a reference to a view masking a complex SQL statement, so things can really be as complicated as you make them.  Here's some simple examples of how to combine tables of the same or different structures.  You could conceivably do some filtering in the r script on the table source column (xsource):

DROP VIEW IF EXISTS dbo.vw_union1
DROP VIEW IF EXISTS dbo.vw_union2
DROP TABLE IF EXISTS dbo.table1
DROP TABLE IF EXISTS dbo.table2
DROP TABLE IF EXISTS dbo.table3
GO

CREATE TABLE dbo.table1 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,

    CONSTRAINT PK_table1 PRIMARY KEY ( col1 )
)
GO

-- Same structure as table1
CREATE TABLE dbo.table2 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,

    CONSTRAINT PK_table2 PRIMARY KEY ( col1 )
)
GO

-- Different structure to table1
CREATE TABLE dbo.table3 (
    col7 VARCHAR(5) NOT NULL,
    col8 DATE NOT NULL,

    CONSTRAINT PK_table3 PRIMARY KEY ( col7 )
)
GO

INSERT INTO dbo.table1 ( col1, col2 )
VALUES
    ( 1, 2 ),
    ( 3, 4 )

INSERT INTO dbo.table2 ( col1, col2 )
VALUES
    ( 1, 6 ),
    ( 3, 8 )

GO

INSERT INTO dbo.table3 ( col7, col8 )
VALUES
    ( 'A', GETDATE() ),
    ( 'B', GETDATE() )

GO

CREATE VIEW dbo.vw_union1
AS
SELECT 'table1' AS xsource, col1, col2
FROM dbo.table1
UNION ALL
SELECT 'table2' AS xsource, col1, col2
FROM dbo.table2
GO

CREATE VIEW dbo.vw_union2
AS
SELECT 'table1' AS xsource, col1, col2 , NULL AS col7, NULL AS col8
FROM dbo.table1
UNION ALL
SELECT 'table3' AS xsource, NULL AS col1, NULL AS col2, col7, col8
FROM dbo.table3
GO

-- Long SQL statement; same text as vw_union1
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT ''table1'' AS xsource, col1, col2 FROM dbo.table1 UNION ALL SELECT ''table2'' AS xsource, col1, col2 FROM dbo.table2',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$col1), FUN=median)'
GO

-- SELECT from view where structures are the same
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union1',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

-- SELECT from view where structures are the different
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union2',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

-- subset example
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union1',
    @script = N'InputDataSet <- subset( InputDataSet, xsource == "table1")
OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

There is not an @input_data_2 or @input_data_n at this time.


Migrating SQL server 2008 to 2016 Is there any check list for how to do migration from SQL Server 2008 to SQL server 2016 ?

I am building a new server for the 2016 want to move all the DBs from the old server 2008 to the 2016

any suggestions ?

Before you migrate any databases, run 'Assesment' in Data Migration Assistant. It'll point compatibility issues between versions and also show you some best-practice suggestions. Then proceed with actual migration.
I noticed that one thing that DMA doesn't do, is to raise a warning if a database is running in Sql Server 2005 (or earlier) compatibility mode. Sql 2016 does only support compatibility modes 2008 or higher. So don't trust it blindly as there may be more aspects it ignores.


Overly eager explicit row locking causes deadlock I'm running into deadlock problems on MariaDB using the INNODB engine.

In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those. I'm using a SELECT ... WHERE id IN (...) statement to load all relevant rows at once. I have been using LOCK IN SHARE MODE to ensure that other threads don't cause corruption.

While testing this on a table with a significantly reduced data set I have been experiencing deadlocks - although each thread works on a distinct set of rows. It seems the SELECT ... WHERE id IN (...) locks all rows when the number of ids exceeds a specific fraction of the size of the table. This causes a deadlock as threads attempt to acquire X locks on records which are S-locked by (all) other threads.

I've replicated this behaviour using 10.3.12-MariaDB-1:10.3.12+maria~xenial on a sample data set.

Setup:

CREATE TABLE items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    some_col bigint unsigned,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO items (some_col)
    VALUES
        (12),
        (36),
        (72),
        (11),
        (81),
        (53),
        (28),
        (37),
        (58),
        (87)
;

Steps using two threads:

Thread A: Acquire locks

START TRANSACTION;
SELECT * FROM items WHERE id IN (1, 2, 3, 4, 5) LOCK IN SHARE MODE;

(Using WHERE id = 1 OR id = 3 OR ... has the same effect.)
Thread B: Acquire locks

START TRANSACTION;
SELECT * FROM items WHERE id IN (6, 7, 8, 9, 10) LOCK IN SHARE MODE;

Thread A: Update row with id = 1

UPDATE items SET some_col=0 WHERE id=1;

This thread hangs waiting for the X lock on the record with id = 1.
Thread B: Update row with id = 6

UPDATE items SET some_col=1 WHERE id=6;

At this point MariaDB will rollback one of the transactions because it detects a deadlock.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

SHOW ENGINE INNODB STATUS; indicates that thread A holds S locks on all records in the items table, not just the one returned by the SELECT query in step 1.

When using separate SELECT queries for locking in step 1, this issue does not arise.

  Thread A: Acquire locks

  SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

The documentation of LOCK IN SHARE says that "… a lock is acquired on the rows read by the query …". The observed behaviour indicates that this means all rows looked at, not just the ones selected.
ANALYZE format=json confirms that the access type is ALL for the SELECT query from step 1.

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0562,
    "table": {
      "table_name": "items",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 10,
      "r_rows": 10,
      "r_total_time_ms": 0.0373,
      "filtered": 50,
      "r_filtered": 50,
      "attached_condition": "items.`id` in (1,2,3,4,5)"
    }
  }
}

Is this correct? I.e. does LOCK IN SHARE MODE lock all rows looked at during query evaluation and not just the ones selected?

Is there a reliable way to read / lock only the rows selected (without using individual SELECT statements)?

Some more observations about INs locking behaviour:

When there is only one id in the IN list, the access type is shown by ANALYZE as const and no "over-eager" locking occurs.
When there are multiple ids (less than 50 % of the rows in the table) in the IN list, the access type is shown by ANALYZE as range and no "over-eager" locking occurs.
When there are more ids in the IN list than 50 % of the row count of the table, the access type is shown by ANALYZE as all and "over-eager" locking occurs.

Would it suffice to SELECT ... FOR UPDATE instead?
Can we look at the entire transaction?  Speeding it up will decrease the frequency of deadlocks.
Even if you find a way around this deadlock, you must prepare to get deadlocks.  Usually, it suffices to re-run starting with the START TRANSACTION.

Another approach might prevent the "over-eager" problem:

Put the list of ids in a table (possibly a TEMPORARY table).  Then JOIN to it instead of using IN ( big list ).


Return ID from partitioned table in postgres? When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm in dire need of RETURNING. Is there a way to accomplish this?

Assuming a parent's table like this:

CREATE TABLE parent AS (
  id not null default nextval('parent_id_seq'::regclass)
  ... other columns ...
);

Whether you're using a rule or a trigger to divert the INSERTs into the child tables, immediately after the INSERT you may use:

SELECT currval('parent_id_seq'::regclass);

to get the last id inserted by your session, independently of concurrent INSERTs, each session having its own copy of the last sequence value it has obtained.


I appears that some of your syntax is not entirely correct in the question, however it may just be a typo.  It appears also that you are attempting to use the old method of joining tables.

I noted also that you are having to return records from StaffName where there are no matches (nulls?) with the other tables.  To still join, and to show only blanks where there are no matches, you will need to use an outer join.  For simplicity, I have used only LEFT joins in the example below.

Also, note that I have returned the appropriate fields in your select list, referencing the joined tables, so as to get the result as shown in your requirement

select s.name, ig.Income_Range, el.Educational_Level, c.Country
from StaffName s
    left join Country c on s.Income_Group = c.ID
    left join IncomeGroup ig on s.Educational_Level = ig.id
    left join EducationalLevel el on s.Country = el.id

To better understand left joins, here is an excerpt from BOL.

  Using Left Outer Joins

  Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written. To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query:

USE AdventureWorks2008R2;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
    LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID

  The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.

I suspect your statement regarding Primary Keys and Foreign Keys are purely to say that we needn't worry about the effect it may have on your joins.  You may have your reasons for it, but please remember that having proper Primary Keys are fundamentally important to any database.


How can I get satisfactory random numbers from the RAND function (or elsewhere)? I'm creating a pseudo-random data set for application users to train on.

I'm surprised that if I seed the RAND() function with 1, 2, 3, etc., I get very nearly the same result from the seeded function.  However, this seems to be followed by "properly random" but repeatable values when a seed is not supplied.

SELECT   RAND(1) AS R1A, RAND() AS R1B, RAND(2) AS R2A, RAND() AS R2B,
         RAND(3) AS R3A, RAND() AS R3B, RAND(4) AS R4A, RAND() AS R4B

0.713591993212924

0.472241415009636

0.713610626184182

0.217821139260039

0.71362925915544

0.963400850719992

0.713647892126698

0.708980575436056

At first sight, it appears that I can evaluate RAND(@seed) and discard the result, then evaluate RAND() to get several really "random" numbers for my training data - so far I've planned to use four per record; I might need some more.

Is that plan going to work properly?  And, what am I looking at, here?  And, should it be in the documentation?  I haven't found it.

Documentation says this, which may be meant as a clue:

  The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function.  If no seed is provided, the system generates its own variable seed numbers.

Does the rand function in C produce similar output for similar seed input?

I think the documentation could also state more clearly that RAND(@number) followed by RAND() always generates the same numbers.  But that is what I wanted and what any experienced computer programmer will expect.

I suppose I could fill a table with random data keys obtained from https://www.random.org/
to use for the purpose - but that has drawbacks.

Update, provisional conclusion

I have the following conclusions about RAND() and for now I think I'll continue with it, but keeping alternatives in mind.

RAND(@int) sets the seed of the random number generator using the integer value given, and returns a float result which is not statistically independent, inasmuch as RAND(@int) and RAND(@int+1) produce almost exactly the same result.

Of course, RAND(@int) always produces the same result.

RAND(-@int) and RAND(@int) produce the same result.

RAND(0) is an exception: there may be other exceptions.  RAND(0) always produces the result 0.943597390424144, but it is not similar to the result of RAND(1).

RAND() called n times after RAND(@int) always produces the same n numbers.  If we call the nth number "rand(@int, @n)" -

CREATE PROCEDURE sproc_rand(@seed int, @nth int, @rand float OUTPUT) AS
SET @rand = RAND(@seed);
WHILE ( @nth > 0 ) BEGIN SET @rand = RAND(); SET @nth = @nth - 1; END

The difference between rand(@int, @n) and rand(@int+1, @n) "modulo 1" -

(1.0 + rand(@int, @n) - rand(@int+1, @n) % 1

Is a constant or nearly constant; for @n = 1 it is approximately 0.75.  For @n = 5 it is 0.991.  For @n = 6 it is 0.91.  For @n = 100 it is 0.83.

So, no, these are not good "random" numbers when a simple incremented seed is used - although they bounce around quite well for @n IN (1, 2, 3, 4).

What I'm now considering as a mitigation is using not RAND(row_id) but,

RAND(row_id * @factor_1 + @factor_2)

where @factor1 and @factor2 are constant terms and @factor_1 is approximately 10,000.  And for different projects, different factors.

So far, if I want repeatable results, the other method available (besides the Multiplicative Inverse thing, which I haven't quite understood yet) is to create a table and fill it with non-repeating random numbers from one of the other methods, then draw the numbers from that table, when required.

The only reason to ever provide a "seed" for RAND() is if you want the exact same sequence of (pseudo) random values for testing purposes.

Just call it without a seed, and that should be good enough for most purposes.

If you do need a predictable sequence, and you're simply unhappy with the starting (random) value, RAND() takes seeds from -2,147,483,648 all the way up to 2,147,483,647 (full range of int), so try some seeds that differ by more than 1 or 2.

Finally, if you were on SQL 2008 or better, you'd also have the option of considering CRYPT_GEN_RANDOM instead (warning, it returns a varbinary, not a float, so you'd likely need to change your code a bit): see this article for some differences and discussion.


Updating a SQL Server Extended Event session to add data storage - is it possible? I have an extended event session to track deadlocks, and have the data storage set up for Event_File, max file size of 20 MB, max number of files is 5, enable file rollover is true.
It looks like I can't change the data storage, even after I stop the session. It's all grayed out. I need to be able to increase the number of files, because for some reason it's not actually going to the max file size of 20 MB. I have 4 files already for this session, and the largest size is 47 KB.
How can I increase this? Do I just need to re-create the session? I already have history in it that I want to keep.

To keep the history just stop the session and copy all the event files for that session to another place. You can query them like the example provided by Dan Guzman's answer. You can query those files even if the event session that created them no longer exists.
About the files never reaching the specified size, it might be related to the chosen size, as you can see on the Event File Target doc:

When max_file_size is set to a size larger than the size of the
session buffers, it may be rounded down to the nearest multiple of the
session buffer size. This may create a target file that is smaller
than the specified value of max_file_size. For example, if the buffer
size is 100MB and max_file_size is set to 150MB, the resultant file
size is rounded down to 100MB because a second buffer would not fit in
the remaining 50MB of space.

Also, by stopping and starting a session a new file is created even though the limit size has not been filled on the previous file.
To alter the max_file_size of an existing XE Session (let's say the one from the answer I linked above) it would be something like this:
ALTER EVENT SESSION [errors] ON SERVER
DROP TARGET package0.event_file;

ALTER EVENT SESSION [errors] ON SERVER
ADD TARGET package0.event_file(
    SET
        filename=N'C:\TraceFiles\errors',
        max_rollover_files=(5),
        max_file_size=(512)
    );


Partially contained databases and Windows Logins - best practices? In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each database.

In 2012/2014+ my understanding is that this is not necessary, and that a user can be granted access to a database merely by granting their domain login permissions on the database directly.

Is there any reason why we as SQL Server Admins should define a server-level login for a Windows account if that account isn't intended to have any specific server-level role (backupoperator, sysadmin, etc)?  Is the best practice now to simply forego the server level login definition for Windows accounts altogether, unless you want to grant that login a server-level role?

I was going to just comment the link to the SQL Server 2012 Best Practice security white paper...but found out Microsoft took it off the Internet for some reason. You might be able to find a cached version of it some where online, but I will paste in the contents of contained database section of the document here.

There is also a BOL article for Security Best Practices with Contained Databases, this may be why the white paper was taken off but not sure.

Author of this whitepaper: Bob Beauchemin, SQLSkills; Published January 2012; Applies to SQL Server 2012

  Contained Databases and Authentication

  SQL Server 2012 introduced the concept of contained databases. A
  contained database is a database that includes all of the settings and
  metadata needed for its operation with dependencies on the instance.
  From a security perspective, a contained database makes it easier to
  have user accounts that are limited to a single database. SQL Server
  2012 supports partially contained databases; it does not yet support
  full containment. Although contained databases allow more control by
  the “application administrator” they do have security repercussions.

  Contained databases are interesting for a security point of view in
  that they allow defining users with authentication privileges, i.e.
  users who can log directly into a contained database without a
  corresponding login. Contained databases support two types of users:
  Windows users and groups that can directly connect to the database and
  do not need logins, and users with a password where the password is
  authenticated by the database, not the instance. This is not permitted
  by default; the instance administrator must specifically allow it by
  setting the “contained database authentication” configuration option
  on. To disallow connections to specific contained databases but allow
  access to other contained databases, a login trigger can be used.

  Allowing direct connection of users to a database changes the
  effective threat-level of some existing permissions. For example, the
  permission ALTER ANY USER in a contained database gives permission to
  add user-based access to an instance through a contained database.
  Logins with ALTER ANY DATABASE or CONTROL DATABASE permission could
  also set containment on a database and add instance access through
  users. In addition, contained database users can connect to other
  database in the instance if the guest account is enabled.

  Although you can use the system stored procedure
  sp_migrate_user_to_contained to migrate a database user mapped to a
  SQL login to a contained database user-with-password, contained
  databases’ user-with-password does not use password history and
  expiration policies as SQL logins do, although password complexity
  checks are still used. Therefore, attaching a contained database could
  allow weak passwords. In addition, the password hashes for these
  passwords are stored in the database, not in master. Anyone with
  access to the database file could perform a dictionary attack on a
  separate, unaudited instance.

  There exists the possibility of conflicts, if a login and contained
  database user have the same name. The rule for resolving this conflict
  is that if an initial catalog is specified in the connection string
  and it’s the contained database, access is checking against the user
  based principal, not the login. To ameliorate this possibility, do not
  create conflicting names or specify a contained database as an initial
  catalog in a connection string. In addition, members of the sysadmin
  role should not use initial catalog in a connection string.

  Best practices for contained databases

  Use the default (off) setting for contained database authentication    and only turn this setting on if it is required.
  Protect backups of contained databases using passwords.
  Audit the capabilities of users and modules in contained databases.
  Audit logins that have the ability to set containment, if contained    database authentication is allowed.
  Disable the guest account on databases that share an instance with    contained databases.
  Take care to avoid login/user-with-login naming conflicts
  Avoid connection strings with initial catalog if contained database    authentication is permitted.


SQL JOIN Syntax in MS SQL I have been taught in my MSSQL classes, this is how to join two tables

select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID

Now in my professional life, I came across JOIN queries like this

select * from FirstTable A, SecondTable B
where A.ID=B.ID

I know the second option was once norm but perhaps now abandoned.

I find that in complex queries where I join 6+ tables + have a number of sub queries , the second form is a lot easier to understand and is short and pretty.

Questions

Which one should I use?
Is there an advantage of one over the other?

Queries of the second type fall under what some call a SQL antipattern (check out the nice book written by Bill Karwin). The second query almost resembles a Cartesian JOIN whose WHERE clause has to be evaluated on the fly.

The first is cleaner and the order of execution can be better managed.

You could compare both ways by

getting the EXPLAIN plan for each and seeing if execution time is the same
creating a third type that refactors the query and get its EXPLAIN plan and running time
see if all indexes are being evaulated the same for the three query styles

You are better off going for the JOIN syntax because you can generate result sets with LEFT JOINs and RIGHT JOINs that may be far different from (maybe more desirable than) INNER JOINs.


Which queries are faster with Postgres than with MySQL InnoDB I've read Performance difference between MySQL and PostgreSQL for the same schema/queries. Here is a brief retelling of the article:

  PostgreSQL tables are heap tables (means no clustered index)... a primary key lookup for a (Postgres) table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record. This means a number of pieces of random disk I/O... InnoDB uses a different approach. With InnoDB, the table is a b-tree index (clustered, physically sorted)... less random disk I/O is required for PK lookup... At the same time, an index scan requires traversing two indexes instead of one (index -> PK index -> table row), meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Which kind of queries are much faster with Postgres than with MySQL InnoDB?

I understand why PK lookup is much better for MySQL. I DON'T understand:

Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?
Why InnoDB sequential scans are slower?

P.S. Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?

To avoid flame war, I'll just glance the way each storage work on querying, not really a benchmark. I'll use this table as reference (the code should be slightly modified to run on both RDBMS):

CREATE TABLE employees (
    emp_id int,
    name varchar,
    depto_no int,
    salary decimal,
    CONSTRAINT emp_pk PRIMARY KEY (emp_id);
);
CREATE INDEX emp_depto_idx ON employees (depto_no);

On PostgreSQL there will be 3 structures:

The employees heap, which is basically the table stored sequentially (just like you imagine a table)
The emp_pk index (which is also the primary key), stored as a B-tree index where each element has a pointer to the employee's heap, with the exact page/offset in disk
The emp_depto_idx index, that is like emp_pk, a B-tree with pointers to the heap, except that it does not enforce uniqueness

On MySQL InnoDB, there will be only two:

emp_pk and employees will be stored as one structure, a B-tree ordered by emp_id column, and just keep the value on the other columns as a payload in the leaf nodes.
emp_depto_idx index is a B-tree that on each element it will have the emp_id value referencing that row (not a physical location pointer).

Primary key lookup

  why PK lookup is much better for MySQL

I know you know that, but let's make it clear.

When you query it like:

SELECT * FROM employees WHERE emp_id = 10;

On PostgreSQL it can navigate through emp_pk index (one scan on the B-tree index) and then get the page/offset to get referencing row from employees heap table (one direct page/row fetch, not really a scan). So, one scan on the index, and one direct fetch on the heap.

On MySQL it will just navigate through primary key index (one scan on the B-tree index), as all the information is already there, no other lookup is required. So, just one scan on the index.

So, while PostgreSQL needs to do one scan and one fetch, MySQL just do one scan.

Secondary index lookup

  Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?

Now, assume this other query:

SELECT * FROM employees WHERE depto_no = 14;

On PostgreSQL, it won't be much different from the other. It will scan emp_depto_idx and then, for each row returned, fetch the value directly from the heap. So, one scan on the index, and a direct fetch on the heap for each row matched.

On MySQL it will scan the emp_depto_idx (one scan on the index), then, for each row returned it will get the referencing emp_id and scan the primary key index. So, one scan on a secondary index, and one scan on the primary index for each row matched.

See the difference? While PostgreSQL will do a scan and then fetch each matching row with a direct pointer, InnoDB will do a similar scan first, then another scan for each matching row. Now, it may be fast enough if department 14 has few employees, but really slow as it gets more employees (of course it will get slower on both RDBMS, but the curve is probably higher with InnoDB).

Full scan

  Why InnoDB sequential scans are slower?

Simple answer, because it is not really "sequential"...

Well, let's see the simplest (and certainly slow for all) query:

SELECT * FROM employees;

On PostgreSQL, it may can simple scan the whole employees heap, row by row, in its physical sequential order (no matter the insertion order here, what matters is how the tuples and pages are physically arranged now).

On InnoDB, it needs to traverse the index, which means more random scans (as index pages are not necessarily ordered physically and logically the same way).

If you think about magnetic disks, the difference is quite obvious, it is know that sequential access is way faster than random access. For SSDs it is not necessarily true, although there is still advantages on sequential access, like read-ahead. So in most scenarios, PostgreSQL's full scan will probably faster than InnoDB's, at least for considerable large tables (notice I didn't define what is "large", you must try it out and see where that difference really matters, it may just not matter in many cases). The best, for both RDBMS, is to design your model and your queries to avoid full scans, if possible.

Complex queries

  Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?

This is a huge topic, and probably the one that would generate more flame war, so I'll just give you some examples. It is common to say that PostgreSQL is better for complex queries, and it may be a true if you simple think on its query capabilities that are not present in MySQL (not considering performance yet), like:

Common Table Expression (CTE)
Window Functions
LATERAL joins
Arrays
JSON types, functions and operators
etc.

Besides that, there are many difference in the planner and executor of those two. For instance, PostgreSQL can do joins using Nested Loops, Hash Joins and Merge Joins, while MySQL can only do using Nested Loops. Despite that, MySQL has many optimizations in its Nested Loop algorithms, and PostgreSQL has a harder choice on its planner, and sometimes it makes mistake (but so does MySQLs).

Final words

This answer is just a really fast glance on the topic, there are still many things to consider for those two RDBMS when it comes to performance, like index-only scan, vacuum vs undo, parallelism, etc. The truth is that you can't simple say one is faster than the other, it is clear to me (is it to you?) that one can be faster in some environments while the other may be faster in others.


Design using multiple tables for 1 booking table Forgive if the answer to this is obvious, but I have no clue what keywords to google with.

I'm designing a database for a school that has a variety of IT resources it can lend out (such as iPads, laptops, projectors)

My database keeps track of the resources by using a booking log.
The problem I have is the resources can be booked by either a student, teacher, or a school associate member.

This is my design.

Student, staff and associate members each have their own table, and their own attributes.

How do I design the tables and relationships so the resource hire log keeps track of who's booked the resource?

Common table will be the best approach in my opinion. For example,

Person (person_id (PK), first_name, last_name, birthday);

Student (person_id(PK,FK to Person),...);
Staff (person_id(PK,FK to Person),job_title);
Association_member( person_id(PK,FK to Person), reg_date) ;
Resource_hire_log(resource_id, person_id (FK to Person), ....) ;

You may also think about Student, Staff, and Association member as roles which person can play .  Then you don't need Student, Staff, etc tables, but Role and Person_Role.

Alternatively, you can use exclusive arcs approach :

Resource_hire_log(resource_id,
 student_id (FK to Student), staff_id (FK to Staff),
 association_member_id (FK to Association_member), .... )

Then you will need to add check constraint to ensure only one of (student_id, staff_id, association_member_id ) is not null.


Huge MySQL slave replication lag (days) at almost idle servers. Slave is mostly 'Waiting for dependent transaction to commit' I have a strange situation with MySQL replication lag happening on relatively idle servers.

Slave shows no symptoms of IO saturation (according to top, iotop or iostat), no symptoms of CPU saturation. If I do SHOW SLAVE STATUS at regular intervals, it seems, that slave spends 99% of it's time at two events:

Waiting for slave workers to process their queues
Waiting for dependent transaction to commit (happening most often).

The problem is that the database is about 1Tb in size and it's not easy experimenting with it. Both master and slave are Xeon E5 with SSD RAID10 storage. Investigation showed no lagging of slave IO thread. Everything looks like slave SQL thread is constantly waiting for some event to complete before replaying next transaction from log.

Replication type used is row-based. We tried to switch to slave_parallel_type         = LOGICAL_CLOCK and set slave_parallel_workers = 24 but with no success. We see no server resource over-utilization. All metrics show that the server is mostly idle. log_slow_slave_statements shows nothing. innodb_flush_log_at_trx_commit = 2 to prevent fsyncs() on small transactions (not that it was necessary because we are nowhere near iops limit but we wanted to try something).

Putting additional load to server does not affect replication lag.

Database being replicated contains about 95% of InnoDB tables and some TokuDB tables.

This situation started about several months ago at a time point none can remember. Despite of the fact, that it is a visible point in Grafana for us, we can't remember or find something in logs that could bring us closer to the problem cause.

MySQL server version is 5.7 by Percona.

Check these two server parameters (my.cnf):

slave_pending_jobs_size_max should be same or more than the value of max_allowed_packet
https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_pending_jobs_size_max
slave_transaction_retries value should be twice of slave_parallel_workers.


Can I move data and full text search files to another drive and change the drive letter? My server's G: drive is rapidly running out of space.  It mainly contains data files (*.mdf) for both system and user databases.  The FullText Catalogs also exist on that drive.

IT added a much lager E: drive.

I would like to:

shutdown SQL Server
copy everything from G:\ to E:\
change the G:\ drive to X:\
change the E:\ drive to G:\
reboot

Will that cause any problems with SQL Server?  At the end of the process all of the files will be on the G:\ drive; just not on the original G:\ drive.

I just did something like this with my system databases for a SAN migration and it didn't seem to have any negative impact. Having said that, I tested it in a dev/test environment. Do you have an opportunity to test this in a dev/test environment?

Also, there's not much of a need to reboot. Just stop the services, do the move, and start the services.

One final consideration is that you copy the files appropriately. Managing NTFS permissions issues can be a real pain so use something like XCOPY (which is what I used) or RoboCopy to ensure that you preserve the integrity of those permissions.


Why do databases in mongodb have roles? I have read about mongodb role based access control and I have understood it to a large extent. I was going through this tutorial and when you run show roles on the mongo shell it returns a different set of roles for a given database.

Why does each database have its own roles? Yet we allocate roles to users. Does this mean that if you create a user in that db they can only be allocated roles present in that database?
What is importance of having an authentication database as opposed to saving all uses in a single database, for example like admin? Is there any advantage of having different authentication databases?

I don't think it makes any sense to define users and roles anywhere else than in admin database. If you define them somewhere else then likely your user/role management get unmanageable.

From db.createRole():

  Except for roles created in the admin database, a role can only
  include privileges that apply to its database and can only inherit
  from other roles in its database.

  A role created in the admin database can include privileges that apply
  to the admin database, other databases or to the cluster resource, and
  can inherit from roles in other databases as well as the admin
  database.

So, creating roles in "non-admin" database is much more limited. However, creating users and roles is usually an admin task and typically admins have full power anyway. Perhaps when you like to provide a MongoDB playground (e.g. https://mongoplayground.net/) then such limits could be useful.

Note, when you run a MongoDB Sharded Cluster then users and roles are commonly defined in the Config Server Replica Set. When you connect to a Shard directly then the shard contacts the Config Server in order to authenticate your connection. However, when your shard is disconnected from the cluster then it cannot contact the Config Server. For this you define a Shard Local User directly on the shards, see Create the shard-local user administrator. I think this is the only useful exception where users are not defined in common admin database.


SQL Server Always ON We have a SQL Server 2008 R2 cluster with 2 node and total data is about 2 TB now that cluster is log ship to 4000 Miles another data center. I know about a 2 years back we have mirroring setup instead of log shipping, but issue happening at night when load jobs runs @ take 22 hours instead of 3 to 4 hours every night so we try to put mirroring in asynchronous mode but still it didn't make any difference so we configure log shipping for every 15 minutes.

But now it needs upgrade to SQL Server 2012 & we like to do always on but its mirroring behind always on so will it work or is there any other way?

But now it needs upgrade to 2012

I would suggest you to go with SQL Server 2014, unless you have a compelling reason to stick with SQL Server 2012.

AlwaysON is getting a lot of fixes back ported from SQL Server 2016.

  we like to do AlwaysON  but its mirroring behind AlwaysON so will it work or is there any other way ?

This is not true. The mechanism is different.

Mirroring will flush the log block to disk and once completed locally, it sends the block to secondary.

AlwaysON flushes the log block(s) in parallel.  A secondary could have hardened log block(s) before the primary I/O completes.    This design increases performance and narrows the NOT IN SYNC window(s).

  total data is about 2 TB now that cluster is log ship to 4000 Miles another data center.

The data size matter to a certain extent - when doing an initial sync. What matters is the number of transactions occuring - meaning how busy your database is and the network bandwidth between the 2 data centers. The way you do index maintenance needs to be revisited as well.

There are other stuff like - backups (Full and log backups), application connection string chages to use multisubnet failvoer and hostTTL settings, reading from secondary, etc play crucial role when embracing a new technology.

I would suggest you to start reading : Stairway to AlwaysOn as a good start and then AlwaysOn Professional for some advance stuff.

Read : How It Works: Always On–When Is My Secondary Failover Ready?


What ports should I leave open for MySQL - Ubuntu server I'm building a database server that I want to run on a VPS. I'm planning on using IPTables to block unnecessarily opened ports. What are the ports I should be blocking if I only want to access to my server via ssh and mysql port 3306? I want to make sure I'm not blocking some important service.

You need to block all ports excepts the one you need 3306, and 22 if you need your servers to get accessed by MySQL and SSH only, but you need to keep in mind if there are some connections that should be opened in order to update your server and install new packages and that can be only determined by you. here is the list of known ports maybe you are use some of them.

  20 – FTP Data (For transferring FTP data)

  21 – FTP Control (For starting FTP connection)

  22 – SSH (For secure remote administration which uses SSL to encrypt
  the transmission)

  23 – Telnet (For insecure remote administration)

  25 – SMTP (Mail Transfer Agent for e-mail server such as SEND mail)

  53 – DNS (Special service which uses both TCP and UDP)

  67 – Bootp

  68 – DHCP

  69 – TFTP (Trivial file transfer protocol uses udp protocol for
  connection less transmission of data)

  80 – HTTP/WWW(Apache)

  88 – Kerberos

  110 – POP3 (Mail delivery Agent)

  123 – NTP (Network time protocol used for time syncing uses UDP
  protocol)

  137 – NetBIOS (nmbd)

  139 – SMB-Samba (smbd)

  143 – IMAP

  161 – SNMP (For network monitoring)

  389 – LDAP (For centralized administration)

  443 – HTTPS (HTTP+SSL for secure web access)

  514 – Syslogd (udp port)

  636 – ldaps (both ctp and udp)

  873 – rsync

  989 – FTPS-data

  990 – FTPS

  993 – IMAPS

  1194 – openVPN

  1812 – RADIUS

  995 – POP3s

  2049 – NFS (nfsd, rpc.nfsd, rpc, portmap)

  2401 – CVS server

  3306 – MySql

  3690 – SVN

  6000-6063-X11