Script to drop all databases except one not working I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error:

EXEC sp_MSforeachdb
'
USE [?]
IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'')
BEGIN
ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ''?''
END
'

Here is the error message:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'master'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'tempdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'model'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'msdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ABC'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.

Where should I correct here?
Thank you

How about something like below :

change your output in SSMS to text and once you are happy run the generated sql statements in another query window :

use master
go
select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go'
from sys.databases
where database_id > 4 -- exclude system databases
and name not in ('') -- exclude databases that you want to SAVE !!

Update :  Below is the script that will work in sql agent as well...

/****************************************************************************************
Author      :   Kin Shah
                written for dba.stackexchange.com

Purpose     :   Drop all the databases except system and user defined

Notes       :   1. EXCLUDE databases name not in ('drop1','drop2','drop3')
                2. Remove the commented ---exec (@sqltext) to ACTUALLY drop the databases
******************************************************************************************/

declare @dbname sysname
declare @sqltext nvarchar(max)

begin
    select @dbname = min(name) from sys.databases where database_id > 4 and
    name not in ('drop1','drop2','drop3')  ---- CHANGE HERE FOR Databases that needs to be excluded !!

    while @dbname is not null

    begin
        select @sqltext = 'ALTER DATABASE '+quotename(name)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
        FROM sys.databases where name = ''+@dbname+''

        select @sqltext = @sqltext+ char(10)+ 'DROP DATABASE '+QUOTENAME(name)+';'  FROM sys.databases where name = ''+@dbname+''

        print @sqltext

        ---exec (@sqltext)

        select @dbname = min(name) from sys.databases where database_id > 4 and name > @dbnamename and
        name not in ('drop1','drop2','drop3') ---- CHANGE HERE FOR Databases that needs to be excluded !!
    end
end


How to find values with multiple consecutive upper case characters For a project that I am working on, I need to identify values that have incorrect uppercase.

For example, I need it to identify the following types of values:

Mr JOHN Smith
MR John Smith
Mr John SMITH
Mr JOhn Smith

Ideas I have tried so far:

Select * from table where Name = upper(Name)
  collate SQL_Latin1_General_CP1_CS_AS

(which gives results for rows that are all upper case, such as MR JOHN SMITH)

and

Select * from table where right(Name,3) = upper(right(Name,3))
  collate SQL_Latin1_General_CP1_CS_AS

which picks up some rows too.

But I doubt this is the most efficient way to go about this.

This question is far more complicated than it appears to be on the surface (hence the longer-than-most-would-expect answer). If the strings being searched were codes (postal codes, ISO country codes, ISO state codes, SKUs, etc) or something where the characters used were a limited subset of all possible letters of all languages, then this would be fairly straight-forward. But when dealing with people's names, then no such luck.

                        All of the following example code and test cases can be found on Pastebin ( Searchingfor Case-Sensitive patterns in SQL Server ). The SQL posted on Pastebin includes additional test cases and additional examples related to various points mentioned below.

Very simplistically you can do the following, which does work with the sample data. I added two test cases to help determine whether or not this (or any) method works as it is data that should not match.

SET NOCOUNT ON;
DECLARE @SampleData TABLE
(
  Name NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);
INSERT INTO @SampleData (Name) VALUES (N'Mr JOHN Smith');
INSERT INTO @SampleData (Name) VALUES (N'MR John Smith');
INSERT INTO @SampleData (Name) VALUES (N'Mr John SMITH');
INSERT INTO @SampleData (Name) VALUES (N'Mr JOhn Smith');
INSERT INTO @SampleData (Name) VALUES (N'mr john smith'); -- all lower-case
INSERT INTO @SampleData (Name) VALUES (N'Mr John Smith'); -- proper-case

SELECT *
FROM @SampleData
WHERE Name COLLATE Latin1_General_100_CS_AS
                 LIKE N'%[ABCDEFGHIJKLMNOPQRSTUVWXYZ][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%';

Please note:

I am not using a collation that starts with SQL_ as those have been deprecated (or perhaps more accurately: obsoleted) since SQL Server 2000 was released.
I am using a collation in the 100 series as that is the most recent, and should be available starting in SQL Server 2008. Not all collations have a 100 series, but it is best to use the most recent one, which might be 90.
I am not using a binary collation (i.e. ending in _BIN2 or even _BIN though the _BIN collations have been deprecated since SQL Server 2005 was released, so only use _BIN2 if needing a binary collation). Binary collations are not "case sensitive"; they are "byte sensitive" (_BIN) / "code point sensitive" (_BIN2) and those are very different concepts. Binary collations only appear to be case sensitive when working with (or testing) US-English-only characters. Any characters with accents can potentially be handled incorrectly when using binary collations. For example:

PRINT N'U' + NCHAR(0x0308) +
   N' <-- U + NCHAR(0x0308) -- combining diaeresis [1 "character" from 2 code points / 4 bytes]';
   -- Ü
PRINT NCHAR(220) + N' <-- NCHAR(220) [1 "character" from 1 code point / 2 bytes]'; -- Ü
PRINT NCHAR(252) + N' <-- NCHAR(252) [1 "character" from 1 code point / 2 bytes]'; -- ü
PRINT '';

IF (NCHAR(252) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CI_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case INsensitive
IF (NCHAR(252) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case SENSITIVE
IF (NCHAR(220) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case SENSITIVE
IF (NCHAR(220) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_BIN2)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- Binary

For a more in-depth look into this topic, please see my blog post: No, Binary Collations are not Case-Sensitive.
Instead of using a single character range (i.e. the [A-Z] that works in LIKE and PATINDEX) I added each of the 26 letters. This is required in order for the preferred _CS (i.e. case sensitive) collations to work properly. If you specify a range of characters via the [A-Z] syntax, then it will appear to not respect the case sensitive collation. That, however, is a false interpretation of the behavior. Case sensitive comparisons can be tricky since they, by design, work differently between equality comparisons (e.g. WHERE N'zbz' LIKE N'%B%' or WHERE N'b' = N'B') and range comparisons (e.g. WHERE N'zbz' LIKE N'%[A-C]%'). Range comparisons are more like sorting. And, case sensitive sorting does not handle all of one case before the other case. Think in terms of how dictionaries are ordered (and in fact, the Microsoft documentation even uses the term "dictionary ordering" to describe non-binary collations): they don't place all words starting with A-Z prior to words starting with a-z. They group upper and lower case of each letter together: A,a,B,b,C,c, and so on, not A,B,C,a,b,c. Hence, assuming that upper-case are first (and which one comes first depends on if you are using a SQL_ or non-SQL_ collation), a range of [A-C] equates to [AaBbC], and if lower-case are first, that same range equates to [AbBcC]. In both cases, the lower-case "b" is validly in that range.

However, this approach mainly works if the data you are searching for is guaranteed to only ever have the 26 US-English characters in it, and never ever any other characters found in most other languages, especially those with accents / diacritical marks. Given that your data contains names, you cannot guarantee that, even if working in the US since people live here who came from somewhere else (or an ancestor did), or sign up for services hosted here even though they live anywhere in the world.

For example, add the following two test cases to the example code above:

INSERT INTO @SampleData (Name) VALUES (N'Mr ÜLala Jones');
INSERT INTO @SampleData (Name) VALUES (N'Mr üLala Jones');

Running the code again will not yield new results. The second line added uses a lower-case ü so it shouldn't match anyway. But the first line uses an upper-case Ü so it should match, but can't unless we either make the collation accent-insensitive (i.e. Latin1_General_100_CS_AI) or add the Ü character to both character ranges of the LIKE clause (e.g. [ABCDEFGHIJKLMNOPQRSTUVWXYZÜ]).

Making the comparison accent-insensitive might be an option when looking for any two capital letters next to each other, but not when comparing actual words. And adding the extra letters (the ones with accents) only works when using VARCHAR (i.e. Extended ASCII) data. If that is the case then this approach could be made to work IF you added all of the additional capital letters available in the code page specified by the Collation. To find that, you can do the following:

SELECT COLLATIONPROPERTY('Latin1_General_100_CI_AS', 'CodePage');
-- 1252

According to the Wikipedia page for Code Page 1252, there are 32 additional capitals, which are (I made it a heading so that the characters would be easier to see):

Š Ž ÀÁÂÃÄÅ Æ Ç ÈÉÊË ÌÍÎÏ Ð Ñ ÒÓÔÕÖ Ø ÙÚÛÜ Ý Þ

The result would be a LIKE clause single-character specification of:

[ABCDEFGHIJKLMNOPQRSTUVWXYZŠŽÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞ]

But that particular set of characters only works with Code Page 1252. If your collation uses another Code Page then that set of extra characters would most likely be different, even if there is some overlap.

On the other hand, if you are using Unicode / NVARCHAR data, then even adding those extra characters will not work. Not only can Unicode represent all accented characters (unlike any individual Code Page), it also allows for constructing accented characters by starting with a regular, non-accented character, such as "U" and then adding one or more accents to it. These accents are called "Combining Characters" since they only work in combination with certain base characters (for a more detailed look into Combining Characters, please see my answer to How To Strip Hebrew Accent Marks ). An example of this was shown in Note #3 above (e.g. PRINT N'U' + NCHAR(0x0308); -- Ü ).

If we now add the following two test cases to the example code above:

INSERT INTO @SampleData (Name) VALUES (N'Mr U' + NCHAR(0x0308) + N'Lala Jonez');
INSERT INTO @SampleData (Name) VALUES (N'Mr U' + NCHAR(0x0308) + NCHAR(0x0309) +
                                     NCHAR(0x0308) + N'Lala Jonezzz');

and re-run the test, those new entries will not show up, at least not when using the original LIKE definition that specified just the 26 letters of the US-English alphabet. That first new entry would show up if we added the Ü character since U + that combining character equate to the same thing. So, is it feasible to add the remaining characters like I showed above in the VARCHAR example? That was 32 extra characters. How many extra characters for NVARCHAR? About 1200 ;-). Um no, not feasible.

And if you did add in all 1200 additional capital letters, would that work? Nope, because there can still be more capitals that simply don't have a "pre-composed" equivalent. For example, that second new entry won't show up since there is no equivalent character to put into the LIKE definition. The only way to make this work would be to not only add in all 1200 additional capitals, but also use an accent-insensitive collation. This still doesn't seem very feasible. And it still would only work in limited scenarios such as this one (looking for any capitals, not specific ones).

Unfortunately, T-SQL is just not equipped to deal with this.

But fortunately, there is a way of accomplishing this: Regular Expressions (commonly referred to as just RegEx). RegEx allows for specifying "classes" of characters (e.g. \s = "any white-space character"), and one class -- \p{property} -- will match characters based on certain properties of Unicode characters. The following two class specifications will allow us to get any capital letter, even if it doesn't exist today:

\p{Lu} = An upper-case letter that has a lower-case equivalent
\p{M} = A combining mark

We can use those to come up with the following RegEx pattern:

(?:\p{Lu}\p{M}*){2}

To translate this for those who aren't familiar with RegEx syntax:

The ( and ) group everything inside to be a single pattern that can be repeated or referenced later
The ?: tells the RegEx engine to not save any matches for that grouping. We won't be using whatever matches so it is more efficient to not store it in memory.
The * indicates that there should be 0 or more matches for the preceding item (i.e. the \p{M}). This will match any number of diacritical marks, or none.
\p{Lu}\p{M}* altogether says to match any single upper-case letter by itself, or that is followed by any number of diacritical marks. This will match, for example: U, Ü, U + NCHAR(0x0308), and so on.
The {2} indicates that a match will consist of exactly 2 occurrences of the preceding item (i.e. the (?:\p{Lu}\p{M}*)). Without this part it would only match a single upper-case letter, not 2. And this is why we needed the parenthesis: to group the upper-case letter and the zero-or-more combining marks together to be a sub-pattern that could be repeated.

The only part left to take care of is the fact that SQL Server does not have any built-in support for Regular Expressions. For this we turn to SQLCLR which allows us to use the RegEx functionality that exists in .NET. If you do not already have SQLCLR RegEx functions, there are many code examples around the Interwebs (just be careful since a few are pretty good, but many are pretty bad), or you can download the Free version of SQL#, a pre-compiled library of SQLCLR functions (that I created) which contains several RegEx functions, including the one used in the following example code:

SELECT *
FROM @SampleData
WHERE SQL#.RegEx_IsMatch4k(Name, N'(?:\p{Lu}\p{M}*){2}', 1, '') = 1;

Running against the test data again will bring back all of the expected entries, including the final one with the 3 diacritical marks that isn't even a character in any language (at least, not yet). Using RegEx with this pattern will just work, always, for all known languages (well, languages that have capitals since some do not).


Stuck REDO Thread of an availability group We are facing issues with REDO queue fluctuating as we have readable secondary configuration.
Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current version is SQL Server 2017 running on VM with 64 cores.
I am seeing problems when select queries on the secondary run on the same cpu/scheduler where AG threads are running. I am assuming AG thread yields its scheduler and takes a back seat to let other queries run. And I believe this is the problem because queries coming in seem to be hitting same cpu_id where AG threads are doing redo work.
Example below: spid 91 is select query on cpu_id 6 and at same time spid 123 and 144 running threads for AG's as found from sys.dm_exec_requests dmv. REDO queue starts building up when 91 came up.

I am not aware if there is any process which makes your queries go to different cores rather than one where AGs thread might be running.

Can we control this to make queries hit other schedulers and not one
where AG working?

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag help as mentioned here

There might be some many other schedulers available. I am not able to understand why certain process is stuck in slot of Numa. I guess newer versions have all soft numa enabled so that makes bucket of 8 soft numas. 8*8 schedulers for 64 available. Is disabling soft numa a good idea here?

Database isolation is RCSI but I don't think that matters here because internally it's changed to snapshot based on design of readable secondaries.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler
to migrate a worker from the runnable queue of another scheduler on
the same NUMA node and immediately resume the task of the migrated
worker. This enhancement provides more balanced CPU usage and reduces
the amount of time long-running tasks spend in the runnable queue.
A long-running task that is enabled for worker migration is no longer
bound to a fixed scheduler. Instead, it will frequently move across
schedulers within the same NUMA node which naturally results in less
loaded schedulers. Together with the existing load factor mechanism,
worker migration provides SQL Server with an enriched solution for
balanced CPU usage.

Source
Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group
parallel redo tasks are enabled for worker migration to address a
commonly observed scheduler contention issue among redo tasks on
secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.
Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on
secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.
One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .
Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.


WITH RECURSIVE cte1 AS (
SELECT videoid,
       SUBSTRING_INDEX(ips, ',', 1) ip,
       SUBSTRING(ips FROM 1 + LOCATE(',', ips) FOR 255) slack
FROM test
UNION ALL
SELECT videoid,
       SUBSTRING_INDEX(slack, ',', 1) ip,
       SUBSTRING(slack FROM 1 + LOCATE(',', slack) FOR 255) slack
FROM cte1
WHERE LOCATE(',', slack)
),
cte2 AS (
SELECT videoid,
       ip
FROM cte1
UNION
SELECT videoid,
       slack
FROM cte1
WHERE !LOCATE(',', slack)
)
SELECT videoid, COUNT(ip) cnt
FROM cte2
GROUP BY videoid


Can Execute trigger in SQL Server 2008 R2 I'm trying to create a simple trigger to update a table's column after it was inserted.

But when I execute the trigger it produces an error:

  Cannot execute script. Additional information: Unable to start
  Transct-SQL debugger. The Transact-SQL debugger does not support SQL
  Server 2005 or earlier versions of SQL Server. (SQLEditors)

I'm using SQL Server 2008 R2.

Here's my simple trigger just to check if it execute.

CREATE TRIGGER tgrCheckFile
ON CheckFile
FOR INSERT
AS
        PRINT 'New Check has been issued'

I'd put this in a comment but I want to give pictures.

My guess is that you are hitting the little green arrow, or DEBUG button

Instead of the red exclamation point, or RUN button.

It's a fairly common mistake.  FYI you can also hit Ctrl-E to "execute" the script or Alt-F5 to "debug" it.  The TSQL debugger requires elevated rights (sysadmin if I remember correctly) and as you saw doesn't work in versions prior to 2008.


Is it ok to change from full recovery to simple recovery in Sql Server I have an old database - a users membership/role that was setup automatically by an ASP.Net 2 application years ago:

The Sql Server version currently running is: Sql Server 10.5.1617

The users database log file is huge (the ldf file is approx 400 times the size of the mdf file).

The recovery model is currently set to "Full". I understand what that is - and I don't need point in time restoration.

If I simply changed the recovery model to "Simple" from within Sql Server Management Studio:

...and clicked ok to save the changes - would I be risking my current database in any way? Or is Sql Server fine with making changes like this to live databases? And would the log file automatically shrink itself?

Thanks for your advice,

Mark

I would do this when the database is in low activity (end of day, overnight, first thing in the morning). I would NEVER recommend making changes like this to a live database in the middle of working hours. It's probably a good idea to change the database to read-only or make sure no users/applications are hitting it.

Run a full backup of the database/log file in question, while still in full recovery mode. This will give you a starting point in case anything gets borked and you can just restore it.

Switch to SIMPLE recovery, I don't like using the GUI, I'd script it out.

Here's a great article from MSDN about things to consider when changing your backup model:
Considerations for Switching from the Full or Bulk-Logged Recovery Model.


Azure SQL Row Level Security Full Index Scan We have implemented SQL Row-level security using SESSION_CONTEXT. From an architecture standpoint, this is a beautiful paradigm that allows our application to be clueless to the fact that only 1 tenant's data is being returned based on the logged in user.

However, we've come across some performance issues related to pre-filtering joins that result in full table scans or index scans. When I explicitly add FORCESEEK, the queries run significantly faster but SQL will never choose a seek on its own and it's my understanding that RLS is to blame for this. I verified that parameter sniffing and cached statistics are not to blame by forcing a recompute every time.

Have you had this happen before? Is there a way around it with RLS? Our backup plan is to scrap RLS completely and move to an Azure SQL elastic pool and horizontal sharding based on customer id.

Thanks so much for any guidance you can provide.

UPDATE: Below are the query execution plans for:
1) With RLS enabled on all 3 tables
2) With RLS enabled on all 3 tables + FORCESEEK option
3) With RLS disabled

We have implemented SQL Row-level ...[which] allows our application to
  be clueless to the fact that only 1 tenant's data is being returned
  based on the logged in user.  ... However, we've come across some
  performance issues

You will continue to run into performance issues with multi-tenant databases.  RLS is really neither here nor there.  If you added your own tenant filters in your application code, you would have the same issues.

To manage a multi-tenant database schema is always a challenge.  First you change every table to support tenant-based filtering.  This typically means including the TenantId in the clustered index of every table, either as the leading column, or as a traling column with a partition scheme.  Then you have to manage query plans, which can't differ between tenants in a multi-tenant database.  Then you're stuck upgrading all the tenants at the same time, which, critically, reduces your ability to quickly patch issues affecting a single tenant.  Eventually you'll have to build processes to Export and Import a single tenant. You can work through this stuff, and many companies do.  But it's fundamentally a challenging model, and one that only really makes sense when reducing your database footprint actually reduces your costs somehow.  IE not in Azure SQL Database.

So you really might be better off to "scrap RLS completely and move to an Azure SQL elastic pool and horizontal sharding based on customer id".

Now the benefits of database-per-tenant are many, and performance is just the start. The best thing is that greatly improves your data security story.  It allows you to upgrade or patch on a tenant-by-tenant basis.  It gives you tenant-level backup, restore, oops-recovery, export.  It provides tenant-level security and reporting. It gives you horizontal scalability, and the option of provisioning dedicated resources for a tenant. etc.

And while it may appear that it's more complicated to manage, and does require some additional automation, in the long run it's actually easier too.


Is there a way to have multiple error messages displayed with RAISERROR conditionally? I am thinking of something like the CASE statement for RAISERROR, but I want the remaining code to be checked too, and not skip anything after encountering the first RAISERROR. Right now I have something like this.

Now, I want a way where I can RAISERROR at the very end and do it conditionally. So if there are two errors from three checks, both should be displayed to the user. Thank you.

One method is to build an error message string and issue a single RAISERROR conditionally after all validations are done.

DECLARE @ErrorMessage varchar(2047);
IF @ForeCastYearCheck = 'false'
    SET @ErrorMessage = @ForeCastYearCheckError;
IF @ForeCastQuarterCheck = 'false'
    SET @ErrorMessage = COALESCE(@ErrorMessage + ',' + @ForeCastQuarterCheckError, @ForeCastQuarterCheckError);
IF @ForeCastComboCheck = 'false'
    SET @ErrorMessage = COALESCE(@ErrorMessage + ',' + @ForeCastComboCheckError, @ForeCastComboCheckError);
IF @ErrorMessage IS NOT NULL
    RAISERROR(@ErrorMessage, 16, 1);


Find outlier statistic in SQL server? I have a stored proc which searches "bad" data in our application database (sql server 2016 enterprise, ~500gb data). "bad" in a business/process sense.

It normally takes 10-30s to execute.
After some days it takes suddenly 5+ minutes to execute.

My current solution is to recompute all statistics and the execution time is low again:

EXECUTE dbo.IndexOptimize @Databases = 'myDB',
 @FragmentationLow = NULL,
 @FragmentationMedium = NULL,
 @FragmentationHigh = NULL,
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'

Index Optimize by Ola Hallengren

Obviously, regenerating the stats is leading into a new and better query plan. Is there a targeted way to identify the misleading statistic which introduces the slow query plan? Or how can I find the cause for this? Tables, indexes, stats and this stored proc are complex so I cannot guess it. Can I maybe programmatically compare the statistics "before" and "after" the updating?

We have many filtered indexes which are usually very tiny so the 20% rule may apply to them frequently.

The indexes are optimized weekly.

In 2016 SP2 / 2017, you can generate an actual plan, and it will tell you what statistics were used. In older versions, you could do the same, but use trace flags as Paul White describes here. But I believe you are right, updating the statistics triggers a new plan, but updating them is not what leads to better plans, its simply the new plan that's generated the first time after the stats update. you should figure out why the plan is going wrong in the first place.

Since you mention that it is a search procedure, I'm going to guess this is a parameter sensitivity issue (seeing the procedure definition and some typical calls will help confirm). There are two thoughts here:

If the problem is due to parameter A sometimes being supplied and sometimes not, consider dynamic SQL. You build the query only with the WHERE clauses that involve supplied parameters. This lets SQL Server optimize differently for each unique combination of parameters (highly recommended to combine with the instance-level optimize for ad hoc workloads setting).
If the problem is due to parameter A sometimes leading to a small number of rows and sometimes leading to a large number of rows, consider OPTION (RECOMPILE). This forces SQL Server to re-examine the parameters every time and optimize based on how many rows this set of parameters is likely to yield. There is some overhead to recompiling but it is often worth it for the stability it brings.

If both 1 and 2 are sometimes true, consider a combination - build the statement dynamically and add OPTION (RECOMPILE) to the end.

I talk about this in a lot more depth here:

#BackToBasics : An Updated "Kitchen Sink" Example

Sure, up to date stats help, but if you're simply re-using the first plan that gets compiled after stats are updated, they're worthless. In the case where the first time you search for last name like 'S%' and the second time you search for salary < 70000, the second will use the wrong index. As for filtered indexes, yes, you're definitely going to want to keep those updated manually, since they're not likely to ever fall into the 20% threshold (or the new one in place that mimics TF2371). But at least if you use dynamic SQL you will have a chance to use the filtered index plan when (and only when) it's appropriate.

Some other reading:

Why is my query suddenly slower than it was yesterday?


How does mysqldbcopy function I have a database called LIVE and I want to create a scenario were I copy the LIVE database every night at 2:30 am. I want the copied database to be called REPORT. So every night I want the REPORT database to be overwritten by the content of the LIVE database. I found a way to do this by running a cron job with mysqldbcopy.

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I need to know if the code above when executed by cron will overwrite REPORT with the LIVE database every night at 2.30 because I am confused by the copy statement. Copy seems like a one time thing. So will the code above :

Create a database called REPORT
Copy LIVE database into REPORT
Overwrite REPORT the next day with the latest copy of LIVE and continuously do so.
If my code above does not do that can someone adjust it to do that please.

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I want to do the same thing and about to test for myself in next week or so.

are you sure @LIVE:REPORT is correct?
LIVE:REPORT try remove @
try --drop-first ( old way was --force < 1.4.2 )

So:

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd  LIVE:REPORT --drop-first

This is what I am going to test.  I am admittedly a noob - fair warning.

After thinking I may tweak script a little.

0 12 * * * mysqldbcopy --source=user:pass@host:port:socket --destination=user:pass@host:port:socket orig_db:new_db --drop-first

I will most likely make cred.cnf file to call with credentials for both source and destination.

cred.cnf file like this (mine hidden and locked down)...

[source]
port=3306
user=user
password=secret
host=pathtodb

[destination]
port=3306
user=user
password=secret
host=pathtodb

This is how my testing cron job will look...

0 12 * * * mysqldbcopy --source=/path/to/cred.cnf[source] --destination=/path/to/cred.cnf[destination] orig_db:new_db --drop-first

good luck.  I will follow-up when I get time to test my system and confirm this works or not.


Will running pg_stat_reset cause performance issues? I'm doing some index analysis on our database to see if there are indexes we can remove. To do this I want to run select pg_stat_reset(); to reset all stat counters back to zero, then just wait a week and see which indexes haven't been used.
Does running pg_stat_reset() also reset row count statistics? Will this affect the query planner?
Is it recommended to rerun Analyze public.#table# on all tables after you reset stats?
Thanks!

pg_stat_reset will not affect query plans (it resets monitoring, not data-distribution, statistics), but it will cause autovacuum to think that no tables have recently been vacuumed, and so autovacuum activity will be elevated for a while. Whether this is a problem depends on the specifics of your data and how it's used.
This is a good article explaining the difference between the statistics used by the planner vs those that pg_stat_reset() affects, as well as how pg_stat_reset interacts with autovacuum:
https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
I'd like to suggest an alternative, however: Just run CREATE TABLE statistics_snapshot_TODAYS_YYYYMMDD AS SELECT * FROM pg_stat_user_indexes (replace pg_stat_user_indexes with whatever you're using if you are using a different pg_stat_* view), and then, in a week, do join it with the view and do a subtraction. This will give you week-over-week deltas, without having to reset anything.
Sketch of the reporting query (not tested, but hopefully you get the idea):
SELECT
    stats_now.schemaname, stats_now.indexrelname,
    stats_now.idx_scan - stats_weekago.idx_scan idx_scan_delta,
    stats_now.idx_tup_read - stats_weekago.idx_tup_read idx_tup_read_delta,
    stats_now.idx_tup_fetch - stats_weekago.idx_tup_fetch idx_tup_fetch_delta
FROM pg_stat_user_indexes stats_now
JOIN statistics_snapshot_20201006 stats_weekago ON
    stats_now.indexrelid=stats_weekago.indexrelid;


Increase AUTO_INCREMENT value in phpMyAdmin So a website that I've inherited had the AUTO_INCREMENT value for a particular table set to a value of 65536. The website is an online store, and this particular table is set for product variations. Well, we've finally hit 65,536 variations and cannot add anymore. I'm now getting an error of Duplicate entry '65535' for key 'PRIMARY'

I've found where to increase this value in phpMyAdmin, but I still get the same error. Is there something I'm doing wrong? Any advice would be mucho helpful.

If your auto increment column is of type UNSIGNED SMALLINT, then you'll have the 65535 limit.
Changing the column type to like UNSIGNED INT should allow you up to 4294967295.
You can use  UNSIGNED BIGINT if you need anything bigger.

If this does not solve your problem, can you post your table structure, which you can get with the statement: show create table <table name>


How to manually set a return value to a subquery with multiple results I have a subquery which can return multiple results if a customer has duplicated sales codes.

I currently LIMIT 1 to just return the most recent but ... Is there away to return NULL or "DUPLICATE" if RESULT > 1?

I am using this field to overwrite a field which already has a default - therefore, I would just prefer to use the default or raise an alert for rectification.

You could try something like this, add a select case to your subquery

select (select case when (select count(*) from subquery) >1 then 'duplicate' else 'not duplicate' end)

Though I'm not sure if I would recommend that, but there is one method.


Updating statistics, what about existing execution plans If you update the statistics on tables that have never been updated (don't ask why they never have), does SQL Server automatically refresh the execution plans based on the outdated statistics? If not, what is best approach to do so?

If you update statistics on a table that didn't have any modifications since the previous statistics update, your execution plans will not be invalidated.

If you update statistics AND data has been changed between this and previous statistics update then it will be invalidated if the database has the AUTO_UPDATE_STATISTICS database option set to ON. Note that affected plans are not immediately recompiled; each plan will recompile when it is next retrieved from cache for execution.

See this post by Kendra Little: Does Updating Statistics Cause a Recompile if No Data Has Changed?

  Finding: Statistics Update Alone Didn’t Cause a Recompile

  SQL Server was smart enough to check if the data had changed. Updating
  statistics alone doesn’t always invalidate execution plans.

And this post by Kimberly Tripp referencing a no longer available connect item:

  It’s NOT a bug, it’s BY DESIGN. And, it actually makes sense.

  If the plan should NOT be invalidated (directly due to statistics
  because the data has NOT changed) then it won’t. But… If the plan
  should be evaluated (statistics have been updated AND data changed)
  then it will.

Also see this post by Erin Stellato: Statistics and Recompilations which references this MSDN documentation page

  When the AUTO_UPDATE_STATISTICS database option is set to ON, queries
  are recompiled when they target tables or indexed views whose
  statistics have been updated or whose cardinalities have changed
  significantly since the last execution. This behavior applies to
  standard user-defined tables, temporary tables, and the inserted and
  deleted tables created by DML triggers. If query performance is
  affected by excessive recompilations, consider changing this setting
  to OFF. When the AUTO_UPDATE_STATISTICS database option is set to OFF,
  no recompilations occur based on statistics or cardinality changes,
  with the exception of the inserted and deleted tables that are created
  by DML INSTEAD OF triggers. Because these tables are created in
  tempdb, the recompilation of queries that access them depends on the
  setting of AUTO_UPDATE_STATISTICS in tempdb. Note that in SQL Server
  2000, queries continue to recompile based on cardinality changes to
  the DML trigger inserted and deleted tables, even when this setting is
  OFF.

So if you want to be absolutely sure your plans for those tables are recompiled you will have to recompile them your self using

sp_recompile 'tablename'


Can we configure Certificate-based Authentication to connect(logon) sql client? Can we configure Certificate-based Authentication to connect(logon) sql client? or any other method to connect sql client using mutual authentication(sql server and sql client)?

Answer originally provided in a comment by Kin:

The answer is no.

Only Database Mirroring and Service Broker connections can authenticate using certificates.

You might want to check Remus Rusanu 's answer on Stack Overflow and his article:

How does Certificate based Authentication work?.


mysql - best way to provide unique voucher I am designing a website that will provide vouchers (serial codes). All voucher codes generated and saved in a table like this and not generated online:

 v_id             voucher                status (used?)
-------------------------------------------------------
  1              abcdefgabcdefg1             0
  2              abcdefgabcdefg2             0
  3              abcdefgabcdefg3             0
  4              abcdefgabcdefg4             0

I should display unused (status:0) voucher to end user after successful payment but I am not sure how can I do this because this site may have high traffic so more than one people may visit site in same milisecond of time, so for example I can not just use:

SELECT * FROM table WHERE status=0 LIMIT 0,1;

and then UPDATE it:

UPDATE table SET status=1 WHERE v_id=....

because two people may visit in same time so we provide same serial code in SELECT before we UPDATE.

what would be best way to ensure I display unique serial code?

One way to do it is to update and select in the same statement; and add a condition to the statement so that (if selected, don't select again)

Here is my way:

SET @voucher_id=0;
UPDATE voucher_table SET status=1, v_id = (SELECT @voucher_id := v_id) WHERE status=0 LIMIT 1;
SELECT * FROM voucher_table WHERE v_id=@voucher_id;

It is kind of creating an artificial lock. Notice that any other transaction (or user) won't be able to pick up the same voucher ID due to the Atomicity and Isolation in ACID properties of the database systems.


A Separate Oracle Database at work I recently started working at a company (Junior Web Developer) and so far loving it. The company's all and only database is one giant Oracle database that takes care of all their data persistence requirements.

The company develops in the .NET framework (Classic ASP, VB.NET WebForms and now C# ASP.NET MVC) which I have an OK understanding of and good exposure too, but I have only ever used .NET with MS SQL Server.

Due to money and licencing the developers at the company do not each have their own local database.

The other day I noticed the Oracle does a Free Express version.

IF I install that on my local work machine, can I use it for all my .NET application needs? E.g. an ASP.NET MVC4 app with Entity Framework? WITHOUT at all affecting the main live database of the company so that I don't mess anything up whilst learning EF with Oracle.

PS. I know the current setup sucks, and they're working to get all developers their own local db.

Any help would be greatly appreciated.

In short, the answer to your questions is yes. However, I think you should bear in mind...

Try to get a version of Oracle XE that is the same as your company's main database.

You'll need to remember where your applications are configured to connect to and be careful to not inadvertently connect to a database you shouldn't, like the live or production database. Hopefully there will already be security in place to prevent you from doing this.

Also, keep in mind the limitations of XE:

  only use one CPU on a system
  use up to 1GB of RAM
  store up to 4GB of data
  11GB of data on 11.2

  The following database features are not supported:

  partitioned objects
  internal Java virtual machine (JVM)

I also wondered about possible licensing issues, but a quick Googling threw up these interesting posts...

Clarification on Oracle XE licensing - really free for any usage?

As well as the forum links in the above blog, I also found these:

oracle 10g XE in commercial enviroment

Oracle XE, is it really free for commercial use?


Can PostGIS use spatial indexes on foreign tables? I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used.

Indexes seem to be working on non-spatial queries.

Is this something that should work or is this not supported?

Assuming current Postgres 9.6, it can work.

Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.

Related answer with more details:

Postgres 9.5 foreign table inheritance not using indexes

Since PostGIS is an extension, this new feature of Postgres 9.6 is particularly relevant:

  Allow extension-provided operators and functions to be sent for remote
  execution, if the extension is whitelisted in the foreign server's
  options (Paul Ramsey)

  Users can enable this feature when the extension is known to exist in
  a compatible version in the remote database. It allows more efficient
  execution of queries involving extension operators.

Bold emphasis mine.

The use of indexes is bound to operators. Various PostGIS spatial functions are transformed to use operators internally when the query is planned. This related answer on SO has more explanation:

Can PostgreSQL index array columns?

Assuming Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick:

ALTER SERVER your_foreign_server_name OPTIONS (ADD extensions 'postgis');

Here is a related blog post by Michael Paquier.


Circular Dependency Issue Error 1451 I am having an issue with a circular dependency.  Inside my "Team Engineers" table, there is the name of the Engineer and also a Boss.  A boss is also an Engineer as well.  Whenever I want to update a Boss, I want to update to the whole entire team of engineers that are associated to that boss, but I keep getting a 1451 error every time.  I have attached my DDL as well as a diagram.

create table `Team Engineers`
(
Engineer_Name char(50) not null,
Team char(50) not null,
Role char(100) not null,
Boss char(50) null,
primary key (Engineer_Name, Team),
constraint Team Engineers_Teams_Team_Name_fk
    foreign key (Team) references Teams (Team_Name)
        on update cascade,
constraint Team Engineers_Team Engineers_Engineer_Name_fk
    foreign key (Boss) references `Team Engineers` (Engineer_Name)
        on update cascade
);

create index `Team Engineers_Teams_Team_Name_fk`
on `Team Engineers` (Team);

create index `Team Engineers_Team Engineers_Engineer_Name_fk`
on `Team Engineers` (Boss);

Specific Error Message:

  "[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`F1_db`.`Team Engineers`, CONSTRAINT `Team Engineers_Team Engineers_Engineer_Name_fk` FOREIGN KEY (`Boss`) REFERENCES `Team Engineers` (`Engineer_Name`) ON UPDATE CASCADE)"

I've seen where people say that this error is common because the MySQL DB can't multiple operations at the same time.

You haven't provided enough details but the problem is likely caused by the ill-defined foreign key. You have a foreign key (1 column) that does not reference the primary key (which is 2 columns):

primary key (Engineer_Name, Team),

foreign key (Boss) references `Team Engineers` (Engineer_Name)

This construction - while it is allowed by MySQL - it is not recommended, even by its documentation.

Solution:

Adjust the foreign key reference so it matches the primary key. I see 2 options here, depending on the business requirements. Can the Boss of an Engineer be on a different Team? Or are they always on the same Team?

If they can be on separate teams, then you need a Boss_Team column, too. Code:

-- added
Boss_Team char(50) null,

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Boss_Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

If Engineer and Boss are always on the same Team:

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

I assume that the PRIMARY KEY cannot be modified. If it is changed (say Engineer_Name becomes the primary key), then the foreign key you have will work correctly.


Cross server query using SSIS I need to issue a query against DBs on two servers:

[CRM].[CRM].[dbo].[BasicInfo] (NationalID PK, CustomerCardNo)
[ERP].[ERP].[dbo].[Order] (OrderID PK, CustomerCardNo)

And what I want to do is like this:

select *
from   [ERP].[ERP].[dbo].[Order] as O
where  O.CustomerCardNo in (select CustomerCardNo
                            from   [CRM].[CRM].[dbo].[BasicInfo]);

I think there are three ways to achieve this:

linked server: my manager would probably not authorize this because they are not familiar with linked servers;
open data source: company policies do not allow developers to have access to DB passwords;
SSIS: seems to be the only choice left, and my company adopts SSIS widely.

When I googled I had no luck finding tutorials regarding how to use SSIS to do this. The closest question is this. Please provide me with some guidance.

Since what you're doing is the equivalent of an inner join, give this a try:
SSIS Merge Join


Altering a large table to INNODB I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master.
I tried to use the pt-online-schema-change tool which says that it will take more than 1 day, and server lag is increasing because there a lot of insertions in this table,so every query has to be executed twice because of the trigger that the tool creates so that no-downtime is faced.

But I can afford a downtime since it is a slave, is it advisable to just simply run the Alter Engine command ,and how much approximate time will it take to execute?

Also I fear that the replication may break,once the alteration takes place.
I am replicating from a 5.5 Master and My Slave is on 5.6.

What am I advised to do in this case, Any help would be appreciated since I am very new to this field.

Thank You

Hmmm... puzzling that a 4GB operation of any sort should take a day.

I recently answered a question on binary compatibility between versions here. The relevant IMHO bit from that post is quoted below (from the documentation):

 - Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then
import the dump files on the other machine. Using this method,
It does not matter whether the formats differ or if your tables
contain floating-point data.

I would always advise that users, if at all possible, use plain text solutions (scripts and the like). This is in the vein of Unix philosophy and therefore, what I would strongly advise you to do is to:

Stop replication
Backup your single table as per the community wiki here, i.e. using mysqldump.
Manually edit the mysqldump script to change the engine from MyIsam to InnoDB. Check out Rolando's more detailled answer above mine - although he did say it was "short and sweet" :-). Use this command sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql as per here.
Reload table as per wiki.
Recommence replication (same reference as for stopping).

Et voilà!

I don't know your disk setup, but I'm willing to bet that it will take nothing like 24 hours (minutes maybe? :-) ).


How to back up an entire database to text files If it’s stupid and it works, it’s still stupid.

The hullabaloo about GDPR got me thinking - if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file sounds hard to me. But it’s pretty easy to open up a .txt file.

Could I back up my database to a text file instead, thereby making it easier to delete history out of the backups?

The following is not a good backup solution. Unless you’re quitting your job and feel like pranking your boss on the way out the door, you shouldn’t use it. But I learned a few things and had fun writing it, hopefully other people will, too. We are going to use some ETL concepts that might actually be useful in other circumstances. The full scripts are at the bottom.
Here’s what we are going to do:

Write the Schema to its own table
Write each table in the database to its own dynamically named text file
Re-create the Schema to a new DB from the text file
Re-create & Bulk Insert each table from its text file

Before we begin:
To do the whole DB, you will need several hundred GB of storage. If you’re too chicken, you can toss in ‘top 1000’s on the script to limit the size of the .txt files, then you won’t have to explain to your boss how you filled up C:\

Enable xp_cmdshell - explained here:

https://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/

Write the schema to a text file

We are going to check and see if the table already exists (If we’ve run the script before). It’s easier to just drop the table every time.

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

Now, if you do a quick select * from HeaderTable, you’ll see every table, every column, its data type and max_length - everything we will need to recreate it.

Write each table in the database to its own dynamically named text file

Here’s where it starts to get cool. We are going to use a cursor to loop through sys.tables and dump each one into its own .txt.

We are going to use a bunch of variables:

@table is going into the cursor. It will hold the names of each table as we go.

@Database, @filepath, @filename, @filetype are all going to be used to build a set of dynamic SQL statements.

@sql is going to hold our final SQL commands, to put to sp_executesql.

Things get a bit tricky with the delimiters and row terminators. If you use the defaults of | and /r, you’re going to have a real hard time with the Comments table. We are going to have to use something that we know isn’t used anywhere in the StackOverflow database. You could use newid(), rocket ships & googly eyes or you could use your favorite nursery rhyme. Anything, so long as it isn’t on StackOverflow already.

Here’s the script:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select name from sys.tables with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table

    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Note that I’ve put the safety on in case you just pasted it in and hit F5. Not that anyone would ever do that, right? exec sp_executesql @sql won’t run until you remove the commenters. I’ve also included a top 10000.

Go to your file path, and you should see a bunch of text files.

Go ahead and open one up and change some data. If you think opening them manually is for peasants, you can use Fart.exe to find and replace in all of the text files.

Re-Write the Header

Go ahead and create a new database.

We are going to hard code the recreation of the header table, and use it to rebuild the rest.

restore header:

    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

And now, we are going to bulk insert our Schema into HeaderTable:

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    --exec sp_executesql @sql

    We will have to tidy it up a bit, to make the next steps easier:

    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')

Re-create & Bulk Insert each table from its text file

And here’s where things get cool again. We are going to loop through HeaderTable and re-create each table, concatenating the Create statement with STUFF(). Don’t ask me how stuff() works - an old coworker (Mike Ignatoski) gave this to me years ago. Reliable sources say he originally got it from some guy named Solomon.

    declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select distinct Table_Name from HeaderTable
    where Table_Name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where Table_Name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    exec sp_executesql @sql

    --populate the table
    set @sql = 'BULK INSERT ' + @table + ' FROM ''' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c

And there you have it - your database has been restored from text files. You can consign the .bak file to the garbage pile of history! So long as you don’t have any functions, stored procedures, views, constraints or indexes.

Here are the full scripts:
Bad Idea Jeans Backup:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --create database header

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

        select * from HeaderTable

    declare c cursor local for

    select name from sys.tables so with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table

    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
     exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Bad Idea Jeans Restore:

declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --restore header
    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    exec sp_executesql @sql

    --make some changes so that we can concatenate our create tables more easily
    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')

    select * from HeaderTable

    --restore DB

    declare c cursor local for

    select distinct name from sys.columns
    where name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    --exec sp_executesql @sql

    set @sql = 'BULK INSERT ' + @table + ' FROM ' + '' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c


Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much? If I want to create 5GB database with

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'E:\2012\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

it takes 1 minute on my SSD.

But when I add SQL Server user to Perform volume maintenance tasks

it takes only 1-2 seconds.

Why is that? Can someone explain to me what are the reasons for this?

That's because of Instant File Initialization.  In short, SQL Server can take advantage of this privilege for database data files (not transaction log files).  What this means is that SQL Server does not have to zero out the data file(s) when initializing.

Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the database creation SQL Server needs to zero out "test.mdf" and "test_log.ldf".

With the "Perform volume maintenance tasks" privilege permission set, SQL Server only needs to zero out "test_log.ldf".  A significantly less overhead, therefore minimized duration for the CREATE DATABASE in your testing.

References
How and Why to Enable Instant File Initialization
Instant Initialization – What, Why and How?


Storing Japanese characters in a table I am using SQL Server 2008 R2 and I want to store Japanese characters in one of the columns of my table.

Say I want to store Japanese names, how can I achieve that? Is there an easy way?

You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below).

Technically, you can store Japanese characters in VARCHAR fields if you use a Japanese_* Collation that is associated with Code Page 932. However, that is considered a "legacy" approach and would still leave you with some issues. The appropriate way to handle this is to use a Unicode datatype as mentioned above. Please see the UPDATE section at the end for details about VARCHAR.

You will also want to specify a Japanese collation so that the data compares and sorts as expected. You can find the available Japanese collations using:

SELECT * FROM fn_helpcollations() WHERE name LIKE N'Japanese%';

And you use that value in the field specification like:

CREATE TABLE dbo.test
(
  JapaneseText NVARCHAR(3000) COLLATE Japanese_CI_AS_KS_WS
);

Please see the following section of MSDN pages for more info on using collation and what each of the CI/CS, AS/AI, KS, and WS mean, as well as BIN/BIN2 and SC (not shown above): Collation.

And depending on which characters you need to store, you might need to pay close attention to the collations ending with SC (i.e. "Supplementary Characters"). By default, NCHAR / NVARCHAR data is stored as UCS-2, which is very similar to UTF-16, but UCS-2 is always 2-bytes per character. On the other hand, UTF-16, in order to support more than 65,536 characters (max size of 2 bytes, or UInt16.MaxValue + 1) can store characters that are 4 bytes (known as "surrogate pairs"). Please see the following MSDN page on Collation and Unicode Support ("Supplementary Characters" section) for more details.

Absolutely do not use NTEXT. That has been deprecated since SQL Server 2005 came out! There is no benefit / reason for using it and, in fact, there are several drawbacks.

UPDATE

While not ideal, it is possible to store Japanese characters in CHAR / VARCHAR fields and variables. Doing so requires that the Database's default Collation be set to one that is associated with Code Page 932 (Shift-JIS). You can find that list of Collations by running the following query:

SELECT col.name
FROM   sys.fn_helpcollations() col
WHERE  COLLATIONPROPERTY(col.name, 'CodePage') = 932;

I did a simple test by creating a database with an entry from that list and ran the following statements:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'); -- Japanese_Unicode_CI_AS
SELECT COLLATIONPROPERTY(N'Japanese_Unicode_CI_AS', 'CodePage'); -- 932
SELECT CONVERT(VARCHAR(50), 0x944094B294CD985198EE9AD79AA0); -- 如抜範浪偃壅國
SELECT LEN('如抜範浪偃壅國'), DATALENGTH('如抜範浪偃壅國'); -- 7, 14

This works because Code Page 932 is a Double-Byte Character Set (DBCS), which is different than UCS-2 / UTF-16 which is also double-byte. A DBCS character set is one that is double-byte within an 8-bit encoding (like the Extended ASCII Code Pages). You can see in that last query that the DATALENGTH is twice the the character LENgth and that the data is in a VARCHAR type since there is no N prefix on the string literals and the CONVERT was to VARCHAR, not NVARCHAR. There are 4 DBCS Code Pages supported in Windows / SQL Server:

932 = Japanese (Shift-JIS)
936 = Chinese Simplified (GB2312)
949 = Korean
950 = Chinese Traditional (Big5)

Only use these if you absolutely need to, such as supporting interaction with a legacy system. Of course, the Collations are still fine to use, but store the data in NVARCHAR instead of VARCHAR.


SSIS Transaction Scope My question is in regards to the scope of a transaction when using SSIS transactions.  If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?

From BOL

If a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?

SSIS will commit the task with the sequence container.
IMHO, best is to use TSQL Transactions instead of SSIS Transactions.
Also refer to : Design Pattern: Avoiding Transactions in SSIS


SOS_Scheduler_Yield wait type I have a process that has a last_wait_type as SOS_Scheduler_Yield. It is inserting 100 records to a table. This process has been with this wait type forever with low CPU utilization %.

There are other processes executing the same query simultaneously without this wait type and executing in minutes. What could be the reason for this process hanging? It has been in runnable state with Last_wait_type as SOS_Scheduler_Yield for that long.

The simple occurrence of SOS_Scheduler_Yield don't mean very much. This can have some other causes.

Generally you can take a look at this DMV to clarify some things.

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'SOS_SCHEDULER_YIELD'

Interpretation of dm_os_wait_stats:

High counter values, low time means everything ok. This mainly just means that there are many queries which run in parallel and need to be scheduled to different threads.
High counter values, high time can cause some problems. Beware you many intensive queries which may take long time to run (e.g. Aggregation on big tables).
Low counter values, high time can indicate some intensive queries which take very long to complete. Beware this may be a CPU bottle neck which may be through bigger data loads or poor design (e.g. user defined functions).

Another good resource to look at may be the schedulers itself:

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY scheduler_id

It will provide you the status of each CPU. Basically description of this statement:

parent_node_id represents the NUMA node of the core.
scheduler_id and cpu_id are basically in many cases the same. They represent the CPU number.
status will represent if the CPU is used for processing steps in the SQL Server instance (affinity_mask)
is_idle represents the state of the cpu. 1 = it does nothing/not much and 0 = on load!
*_switches_count represent the value of the switches between some states. High context_switches_count tends to show that the CPU cycles between different operations while idle_switches_count indicates if the CPU goes often idle and back to work or not. Generally low idle_switches_count indicates that your system is under permanent pressure.
current_tasks_count shows how many tasks were assigned to this cpu.
runnable_tasks_count shows how many tasks really run on the corresponding cpu.
load_factor shows the pressure on this single core.

Hopefully this will clarify things for you.


Is a query with UNIX_TIMESTAMP using indexes in mysql? If I use

.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()

in a SELECT, does this query still make use on an index, set on some_timestamp ?

No. Up to now, the optimizer in all MySQL versions cannot use an index for such a condition. It is not sargable

If you want it to be able to use indexes, you'll have to use a function to rewrite/reverse your condition so no function is applied on the column:

WHERE some_timestamp > ReverseFunctionOf_UNIX_TIMESTAMP( NOW() )

What to use, depends on the column some_timestamp datatype.

If it is a TIMESTAMP, you can simply use

WHERE some_timestamp> NOW()

If some_timestamp is an INT that stores Unix timestamps (seconds since '1970-01-01 00:00:00'), I think you had the correct function but in the wrong place. You should use:

WHERE some_timestamp > UNIX_TIMESTAMP( NOW() )

or the equivalent:

WHERE some_timestamp > UNIX_TIMESTAMP()

Check MySQL documentation: DATE and TIME functions


How to migrate SSAS and SSRS from SQL Server 2008R2 to SQL Server 2012? We are upgrading our SQL Server 2008R2 to 2012 and separate the SQL Server engine from SSAS and SSRS. We want put on Server 1 the engine and on Server 2 SSAS and SSRS.

I did the side-by-side migration for the database. Now the SQL Server Engine and databases are on the SQL Server 2012(server 1).  On the older server we have SSAS and SSRS we want to migrate to new SQL Server 2012 server by itself.

My Question is:

  How to move the SSAS and SSRS from SQL Server 2008R2 to the new SQL
  Server 2012(Server 2) ?

Unless you have data in your SSAS cubes that no longer exists in your source (if this is the case correct that going forward!) then you can simply install a new instance of SSAS then deploy and process your SSAS solutions from SSDT (the 2012+ replacement for BIDS).

For SSRS if you have a basic install you can do the same thing - simply install the new server and deploy your reports. If you have a lot of reports, folders, security setup, schedules etc. then you should migrate.

Here are the MSDN Documents outlining the steps to migrate and the considerations prior to doing so.


Modeling a scenario in which each Music Artist is either a Group or a Solo Performer I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below.
Scenario description

An Artist has a Name, and must be either a Group or a Solo Performer (but not both).

A Group is made up of one or more Solo Performers and has a Number of Members (which should be calculated from the number of Solo Performers making up the Group).

A Solo Performer may be a Member of many Groups or of no Group and may play one or more Instruments.

Question
How to construct an ERD to represent such scenario? I’m confused with the ‘or’ part of it.

The part of the scenario that you are confused with can be modeled with a classic construct called supertype-subtype1 structure.
I will (1) introduce some pertinent preliminary ideas, (2) detail how I would delineate —at the conceptual level— the business context under consideration, and (3) provide additional related material —e.g., the corresponding logical-level representation via SQL-DDL declarations— as follows.
Introduction
A structure of this nature takes place when, in a given business environment, there is a cluster of entity types within which the supertype has one or more properties (or attributes) that are shared by the rest of the entity types in the cluster, i.e., the subtypes. Every subtype has, in turn, a particular set of properties that are applicable to itself only.
Supertype-subtype clusters can be of two kinds:

Exclusive. Comes about when an instance of the superentity type must always have one and only one subtype counterpart; therefore, the potential subtype occurrences in question are mutually exclusive. This is the kind that concerns to your scenario.
A typical case in which an exclusive supertype-subtype comes about is a business domain where both an Organization and a Person are considered Legal Parties, like in the situation deliberated in this series of posts.

Nonexclusive. Presents itself when a supertype instance may be complemented by multiple subtype occurrences, each of which is compelled to be of a different category.
An example of this kind of supertype-subtype is dealt with in these posts.

Notes: It is worth mentioning that supertype-subtype structures —being elements of a conceptual character— do not belong to a specific data management theoretical framework, be it relational, network or hierarchical —each of which offers particular structures to represent conceptual elements—.
It is also opportune to point out that although supertype-subtype clusters bear a certain resemblance to object-oriented application programming (OOP) inheritance and polymorphism, they are in fact distinct devices because they serve different purposes. In a database conceptual model —that must represent real world aspects— one deals with structural features in order to describe informational requirements, whereas in OOP polymorphism and inheritance, among other things, one (a) sketches and (b) implements computational and behavioural characteristics, aspects that decidedly belong to appplication program design and programming.
Apart from that, an individual OOP class —being an application program component—, does not necessarily have to “mirror” the structure of an individual entity type that belongs to the conceptual level of the database at hand. In this respect, an application programmer may typically create, e.g., one single class that “combines” all the properties of two (or more) different conceptual-level entity types, and such a class may as well include computed properties.
Using entity-relationship constructs to represent a conceptual model with supertype-subtype structures
You asked for an entity-relationship diagram (ERD for brevity) but, although being an extraordinary modeling platform, the original method —as introduced by Dr. Peter Pin-Shan Chen2— did not supply enough constructs to represent scenarios of the sort being discussed with the precision that a proper database conceptual model requires.
Consequently, it was necessary to make some extensions to said method, situation that yielded results in the development of an approach that assists in the creation of enhanced entity-relationship diagrams (EERDs) that, naturally, enriched the initial diagramming technique with new expressive characteristics. One of those characteristics is, precisely, the possibility of depicting supertype-subtype structures.
Modeling your context of interest
The illustration shown in Figure 1 is an EERD (using symbols similar to the ones proposed by Ramez A. Elmasri and Shamkant B. Navathe3, who refer to such structures as superclass/subclass) where I modeled the business domain you describe considering all the specifications. It is also available as a PDF that can be downloaded from Dropbox.
As you can see in the aforementioned diagram, both Group and SoloPerformer are displayed as exclusive subtypes of the Artist superentity type:

Diagram description
In order to start the description of the EERD, it is important to point out that your sentence

“an Artist must be either a Group or a SoloPerformer (but not both)”

is related to the disjointness and the completeness aspects of the supertype-subtype cluster at hand.
Disjointness
The disjointness feature is particularly important because it is right here where the “or part” that you mentioned comes into play, due to the fact that an Artist has to be either one subtype instance or the other, which I specified in the EERD through the small circle containing the letter “d”, a construct that receives the name of disjoint rule.
When a supertype may be supplemented by one or more of its possible subtypes, this point must be expressed by a small circle holding a label with the letter “o”, a symbol called overlap rule.
Discriminator property
Also within the scope of the disjointness factor of this supertype-subtype association, it is worth paying close attention to the Artist.Type property, since it carries out a very relevant task in this arrangement: it functions as the subtype discriminator. It is named in this way as it is the property that points out the exclusive kind of subtype with which a specific instance of an Artist relates to.
In the cases of nonexclusive clusters, the use of a discriminator property is needless, for a certain supertype can have multiple subtypes as complements (as brought up above).
Total specialization rule and completness
The requirement that stipulates that every Artist must always have a supplementary subtype instance has to do with the completness characteristic of this cluster. This is delineated by means of a total specialization rule, demonstrated via the double-line symbol connecting (a) the Artist supertype with (b) the disjoint rule construct.
Relating Groups with Solo Performers
Evaluating the sentences

“a Group is made up of one or more SoloPerformers”

and

“a SoloPerformer may be a member of many Groups or of no Group”,

one can recognize that both subtypes are involved in a many-to-many (M:N) association (or relationship), which I represented with the diamond-shaped box labeled as Group-SoloPerformer.
If implemented in a relational database as a base table, this component would be very useful to derive (i.e., to carry out the calculation of) the total Number of SoloPerformers that make up a concrete Group (one of the requirements that you specified).
The association between Solo Performers and Instruments
The stipulation

“a SoloPerformer […] may play one or more Instruments”

permits us inferring that, at the same time,

“an Instrument is played by zero, one or more SoloPerformers”.

Thus, this is another example of a M:N association, and I used the diamond-shaped figure designated SoloPerformer-Instrument to expose it.
Additional material
In order to expound the scope of supertype-subtype structures I am going to include two more resources, i.e.,

an IDEF1X4 diagram presented in Figure 2 (and you can download it from Dropbox as a PDF, as well) that illustrates the expressive capabilities of this sort of diagrams regarding the business domain at issue; and

the respective expository DDL logical structure that exemplifies how to manage the full scenario under discussion by virtue of a SQL database management system.

1. IDEF1X representation
The IDEF1X information modeling technique certainly offers the capability of portraying supertype-subtype structures, though with a limitation: it does not lend a visual mechanism to indicate whether a precise cluster is of an exclusive or noneclusive kind (its “native” symbols can only communicate the complete or incomplete identification of all the possible subentity types of significance). Fortunately, one can employ Information Engineering (IE) notation to show this paramount aspect more accurately while taking advantage of the descriptive power of the IDEF1X standard.
In this technique, the main feature of your question is denominated “categorization relationship”, where a supertype is referred to as “generic entity” and a subtype receives the name of “category entity”. However, I will continue applying the term supertype-subtype in this post because (1) it was used by Dr. Edgar Frank Codd, the originator of the relational model, (2) it is more widely known and (3) IE notation is used instead of the “native” one.

Foreign keys and supertype-subtype clusters
As demonstrated, IDEF1X provides a further advantage: the means to exhibit FOREIGN KEY (FK) definitions, elements of prime importance if a practitioner is going to represent a supertype-subtype association in a relational database.
In order to portray such a sort of association, the PRIMARY KEY (PK) property of the supertype, i.e. Artist.ArtistNumber, has to migrate to Group and SoloPerformer, although it has been assigned two different role names5, 6, GroupNumber and SoloPerformerNumber respectively, for the purpose of emphasizing the meaning conveyed by the property in the context of each subentity type.
Apart from being characterized as PKs, the Group.GroupNumber and SoloPerformer.SoloPerformerNumber properties are, at the same time, depicted as FOREIGN KEYs (FKs) that make a reference to Artist.ArtistNumber, the supertype PK property.
So, since every SoloPerformer and Group occurrence is existence-dependent on an exact Artist instance, these entity types are involved in an identifying association that takes effect by way of the PK property migration process delineated in the preceding paragraphs.
Foreign keys and associative entity types
The IDEF1X diagram serves as well to illustrate the FKs that compose the PKs of the two associative entity types of relevance, i.e., GroupMember and SoloPerformerInstrument; the first one connects the two subtypes, and the second one links a subtype with an independent entity type, i.e., Instrument.
2. Expository SQL-DDL logical declarations
As explained before, a supertype-subtype structure is a means to express certain kinds of business-domain-specific conceptualizations regarding informational requirements, which can in turn be represented in a database by means of distinct constructs that must correspond to those offered by the particular theoretical paradigm (be it relational, network or hierarchical) followed by the database management system being utilized by the designer.
One of the multiple advantages of the relational paradigm is that it permits representing the information in its natural structure, and the most popular approximations to the systems proposed in the relational theory are the various SQL database management systems.
So, finally, here are some sample DDL statements —including (a) base tables schemas along with (b) some of the pertinent constraints— that represent, at the logical level of abstraction, the conceptual modeling exercise treated above:
--
--
CREATE TABLE Artist ( -- Stands for the supertype.
    ArtistNumber    INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Type            CHAR(1)  NOT NULL, -- Holds the discriminator values.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Artist_PK      PRIMARY KEY (ArtistNumber),
    CONSTRAINT Artist_AK      UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT Artist_Type_CK CHECK       (Type IN ('G', 'S')) -- Enforces retaining either ‘G’, for ‘Group’, or ‘S’, for ‘SoloPerformer’, only.
);

CREATE TABLE MyGroup ( -- Represents one subtype.
    GroupNumber   INT  NOT NULL, -- To be constrained as PK and FK simultaneously.
    FormationDate DATE NOT NULL,
    --
    CONSTRAINT MyGroup_PK         PRIMARY KEY (GroupNumber),
    CONSTRAINT MyGroupToArtist_FK FOREIGN KEY (GroupNumber)
        REFERENCES Artist (ArtistNumber)
);

CREATE TABLE SoloPerformer ( -- Denotes the other subtype.
    SoloPerformerNumber INT  NOT NULL, -- To be constrained as PK and FK simultaneously.
    BirthDate           DATE NOT NULL,
    --
    CONSTRAINT SoloPerformer_PK               PRIMARY KEY (SoloPerformerNumber),
    CONSTRAINT SoloPerformerNumberToArtist_FK FOREIGN KEY (SoloPerformerNumber)
        REFERENCES Artist (ArtistNumber)
);

CREATE TABLE GroupMember ( -- Stands for a M:N association involving the two subtypes.
    MemberNumber INT  NOT NULL,
    GroupNumber  INT  NOT NULL,
    JoinedDate   DATE NOT NULL,
    --
    CONSTRAINT GroupMember_PK                PRIMARY KEY (MemberNumber, GroupNumber), -- Composite PK.
    CONSTRAINT GroupMemberToSoloPerformer_FK FOREIGN KEY (MemberNumber)
        REFERENCES SoloPerformer (SoloPerformerNumber),
    CONSTRAINT GroupMemberToMyGroup_FK       FOREIGN KEY (GroupNumber)
        REFERENCES MyGroup       (GroupNumber)
);

CREATE TABLE Instrument ( -- Represents an independent entity type.
    InstrumentNumber INT      NOT NULL,
    Name             CHAR(30) NOT NULL,
    --
    CONSTRAINT Instrument_PK PRIMARY KEY (InstrumentNumber),
    CONSTRAINT Instrument_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE SoloPerformerInstrument ( -- Denotes another M:N association, in this case between a subtype and an independent entity type.
    SoloPerformerNumber INT  NOT NULL,
    InstrumentNumber    INT  NOT NULL,
    CreatedDate         DATE NOT NULL,
    --
    CONSTRAINT SoloPerformerInstrument_PK                PRIMARY KEY (SoloPerformerNumber, InstrumentNumber), -- Composite PK.
    CONSTRAINT SoloPerformerInstrumentToSoloPerformer_FK FOREIGN KEY (SoloPerformerNumber)
        REFERENCES SoloPerformer (SoloPerformerNumber),
    CONSTRAINT SoloPerformerInstrumentToInstrument_FK    FOREIGN KEY (InstrumentNumber)
        REFERENCES Instrument    (InstrumentNumber)
);
--
--

Data integrity and consistency considerations
In agreement with all that has been previously explained, the designer must guarantee that each “supertype” row is at all times complemented by its accompanying “subtype” counterpart and, in turn, make sure that said “subtype” row is compatible with the value contained in the supertype “discriminator” column.
It would be very practical and elegant to enforce said circumstances declaratively (as the relational framework proposes) but, alas, none of the major SQL platforms has provided the suitable mechanisms to do so (as far as I know). Therefore, it is highly convenient to employ ACID TRANSACTIONS so that these conditions are always met in a database (other option would be to make use of TRIGGERS, but they tend to make things untidy).
Data derivation considerations
One of the main aspects of the relational model is that it considers data derivation as a paramount factor in data management. In accordance, it facilitates creating (a) base relations —or base tables in SQL, as shown in the DDL statements above— and (b) derived relations —derived tables in SQL, i.e., those declared by dint of SELECT operations that may be fixed as views for further exploitation—.
So, one can declare a view that gathers the “full” Group data points:
CREATE VIEW FullGroup AS
    SELECT G.GroupNumber,
           A.Name,
           A.CreatedDateTime,
           G.FormationDate
         FROM Artist A
         JOIN MyGroup G
           ON G.GroupNumber = A.ArtistNumber;

And other view that combines the “full” SoloPerformer pieces of information:
CREATE VIEW FullSoloPerformer AS
    SELECT SP.SoloPerformerNumber,
            A.Name,
            A.CreatedDateTime,
           SP.BirthDate
         FROM Artist A
         JOIN SoloPerformer SP
           ON SP.SoloPerformerNumber = A.ArtistNumber;

In this manner, it is very easy to manipulate —declaratively— all the significant data via the very same logical-level device, i.e., the relation or table (be it base or derived). Evidently, the usage of views is more effective when the conceptual entity types represented in a relational database possess more properties of interest, but it is a possibility worth illustrating with the present scenario.

References
1 Codd, E. F. (Dec. 1979). Extending the Database Relational Model to Capture More Meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.
2 Chen, P. P. (March 1976). The Entity-Relationship Model—Toward a Unified View of Data, ACM Transactions on Database Systems - Special issue: Papers from the International Conference on Very Large Data Bases: September 22–24, 1975, Framingham, MA, Volume 1 Issue 1 (pp. 9-36). New York, NY, USA.
3 Elmasri, R & Navathe, S. B. (2003). Fundamentals of Database Systems, Fourth Edition. Addison-Wesley Longman Publishing Co., Inc. Boston, MA, USA.
4 National Institute of Standards and Technology (U.S.) [NIST] (Dec. 1993). Integration Definition for Information Modeling (IDEF1X), Federal Information Processing Standards Publication, Volume 184. USA.
5 Codd, E. F. (June 1970). A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, Volume 13 Issue 6 (pp. 377-387). New York, NY, USA.
6 See reference 4


Quorum Drive needed? Do I need a quorum drive for configuring Always On on SQL Server 2014?

I am sure it is not mandatory as the 'last man standing' concept is based on node and disk majority. But I need a little more knowledge on this.

No, you do not need a quorum disk for availability groups or failover instances.  Microsoft Failover Clustering has supported the file share witness since Windows 2003 SP1.  The file share witness, in conjunction with majority node set, enables a cluster quorum to operate with no shared disk infrastructure.

Technically, the cluster can function with majority node set only, but the typical availability group configuration also uses at least one file share witness so that a node gain take ownership of quorum when one or more other nodes lose communication.

With a failover instance, a shared disk is required for the SQL Server program and data files.  A quorum disk would typically be configured for a failover instance because the shared disk infrastructure is already required.  However, it would not be required and the a majority node set / file share witness quorum could be used.

See Configure and manage quorum and  Clustering: What exactly is a File Share Witness and when should I use one? for more details.


Why is my auto-restore procedure not dropping the databases when I expect it to? I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the process, there are several databases restored at any given time, which uses too much disk space for this to work on my larger databases. When the procedure completes, all the databases are dropped and it does not leave anything behind. I have checked and double checked that I have steps inside the cursor to drop the database, but it does not help.

I have this code inside the cursor:

SET @DropCommand = 'IF DB_ID(''' + @CursorRestoreName + ''') IS NOT NULL
BEGIN
    ALTER DATABASE [' + @CursorRestoreName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [' + @CursorRestoreName + '];
END';

EXEC sp_executesql @DropCommand

I added a PRINT @DropCommand statement as well to verify this code block is being executed, and it is.

Since that wasn't working, I added another cursor inside my first cursor to go through the full list of databases to attempt the drop command, to clean up previous restores. This still has the same effect: multiple database restores exist on the server at a given time during the process, then at the end all have been dropped.

Do you have any recommendations that I could implement so that the restored database is actually dropped before moving on to the next restore? Or perhaps suggestions for troubleshooting this?

What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER.  Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user.  But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.

While setting the database OFFLINE will work, be aware that dropping an OFFLINE database does not delete the database files.  Then you won't be able to RESTORE because the database files are hanging around.  And you don't have rights to delete database files, by default, and shouldn't really need them.

RESTRICTED_USER is probably the best state to put a database ahead of dropping it.  eg

  RESTRICTED_USER

  Allows for only members of the db_owner fixed database role and
  dbcreator and sysadmin fixed server roles to connect to the database.
  RESTRICTED_USER doesn't limit their number. Disconnect all connections
  to the database using the timeframe specified by the ALTER DATABASE
  statement's termination clause. After the database has transitioned to
  the RESTRICTED_USER state, connection attempts by unqualified users
  are refused.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

use master
alter database foo set restricted_user with rollback immediate
drop database foo


Make MongoDB close open files I am currently trialing numerous database designs (to solve this problem: Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance), and my latest design in Mongo consists of using many (>20,000) collections. I am attempting a multi-collection design as I have been unable to achieve reasonable performance using a single collection, most notably when inserting any new data, which I suspect is because the index is becoming so large. From reading around design architecture, I realise a multi-collection design is not necessarily conventional, but that is not the subject of this question. However, If you have any alternative ideas, please respond to the question in the previous link.

My current design will divide my sample data set (100 million locations) into ~30,000 collections, which keeps the index small. Testing this design on a sub-set of ~150 collections (>200,000 locations), shows that my read and insert queries are running with acceptable performance, which I believe will maintain performance when scaled very large. Based on the times from this sub set, the full 100 million locations should take about 15 hours to initially insert into the database. However, when running my insert on this full sample set, after the creation of ~350 collections (in ~7 mins), I receive the error:

failed: 24: Too many open files

Upon reading other people who have run into this (often with WiredTiger), I see most people can overcome the problem by increasing the ulimit of the system:

https://stackoverflow.com/questions/31596392/wiredtiger-too-many-open-files-error-during-resync-of-a-secondary-replica-se
http://www.andrewrollins.com/2010/10/20/mongodb-open-file-limit/
https://groups.google.com/forum/#!topic/mongodb-user/vHKZODyh9cE

But for my use case I don't believe this will work, as this system will scale up to a point where I expect the collection creation routine to grow too large for any sensible upper limit, not to mention I don't want to keep all these files open! For each collection creation, 3 new files are made (1 collection, 2 indexes), and to my knowledge, there is no need to keep these files open after the collection has been made. These collections are generated with a simple loop in python to insert the data for the first time, which repeats the following tasks:

Gather data from satellite output
Determine collection name to update (upsert)
Perform update (create collection, if required)

Once a collection has been made, it will never be accessed again during this routine. So any files created/opened during the collection creation can be closed after step 3. Therefore, is it possible to make MongoDB, or the server, close any/all/specific files it currently has open? Or is there a reason I am unaware of as to why mongo keeps these files open?

If you are sure that while starting mongod instance mongo doesn't open all available db files then you should gracefully stop your mongod instance once in a while, while inserting and start it again, for example when opened files counter reaches ulimit on your system. This will close all opened files and open only the ones that you create a collection for.

Another solution is to adjust the number of collections to appropriate level.

Hope this helps.