Actual Chats: RIGHT OUTER joins in the wild

Colleague:
I think I just encountered my first Right join
*shocked* 

Me:
huh. weird.
a rare Right Join has appeared!

Colleague:
catch it!

Me:
You use ON. It's super effective! 

Colleague:
haha
how often do you see them? 

Me:
I've used RIGHT join maybe twice in 10 years. 

Colleague:
lol

Me:
just makes things difficult to read.

Colleague:
yea it took me a while
it was at the bottom of the query
i kept wondering what was bringing in the extra rows till I found it

And that's why, whenever possible, avoid using RIGHT OUTER. It breaks the nice top-to-bottom flow of your complicated FROM clauses, forces you to page-split your brain around data sets, and generally makes it more difficult for the next DBA to understand.

For this overly-simplistic example, these two queries on the WideWorldImporters database are identical and have the same execution plans:

SELECT  s.CustomerName
, pp.FullName
, ap.FullName 
FROM Sales.Customers AS s
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID 

SELECT  s.CustomerName
, pp.FullName
, ap.FullName
FROM [Application].People AS pp
RIGHT OUTER JOIN Sales.Customers AS s
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID

The first query allows you to do the easily relational math as you read. The second has you flipping the dataset before moving your eye back up to the top.

There are certainly legitimate albeit complicated cases for using RIGHT OUTER, but from a readability and maintainability, I'd prefer to stick with LEFT OUTER.

Columnstore Indexes: Finally Awesome in SQL 2016

Columnstore indexes are amazing for optimizing the performance of large rowcount queries that would otherwise look like a SCAN operation in an execution plan. Introduced in SQL Server 2012, they render the table read-only, however, and needed to be dropped (not even disabled) in order to insert/update/delete rows.

If you tried to update a table with a nonclustered columnstore index in SQL 2012 or SQL 2014:

Msg 35330, Level 15, State 1, Line 2
UPDATE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, and then rebuilding the columnstore index after UPDATE has completed.

In SQL 2014, you can now make a clustered columnstore index, but it has only narrow, specific uses because clustered columnstore indexes don't support traditional (B-tree) nonclustered indexes, a primary key, foreign keys, etc.

All those limitations above are going away in SQL 2016. Right now: SQL 2016 will release on June 1 2016. Release candidates are available for download but aren’t for production use.

SQL 2012 - Nonclustered columnstore indexes can be created, but aren’t updateable. 

SQL 2014 - Nonclustered columnstore indexes can be created, but still aren’t updateable. Clustered columnstore indexes can now be created, are updateable.

SQL 2016 - Nonclustered and clustered columnstore indexes can be created, and are updateable. Plus, you can mix clustered columnstore with traditional B-tree indexes. (Lots of other features too!)

More about these features as Columnstore indexes evolve here: https://msdn.microsoft.com/en-us/library/dn934994%28v=sql.130%29.aspx

Want to benefit from columnstore indexes, but not using SQL 2016 yet?

You have three options.

1) Disable/rebuild columnstore nonclustered indexes when you do updates to the table. Not a big deal for nightly-build data warehouses, but painful for frequently-updating tables. Note that the ONLINE feature to create traditional indexes isn't available here.

DROP INDEX [IDX_CS_Table_Covering] ON [dbo].[Table]
GO
<do updates/inserts>
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table] ( Column1, Column2... )
GO

(When you move to SQL 2016, these drop/create steps won’t be necessary any more.)

2) SQL 2014 only. Drop all existing clustered/nonclustered indexes and foreign keys on the table, change the table to have a single clustered columnstore index. This requires performance testing for smaller-scope queries, but should provide the big performance gains you’re looking for, at the cost of dropping the primary key and related foreign keys. This is suitable for narrow use only in reporting database designs and not in normalized environments.

<drop all existing indexes on [dbo].[Table], including the primary key and linked foreign keys>
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table]
GO

3) SQL 2014 only. Create a copy of the table that implements 2) above, so you’ll have a table with traditional indexes plus an exact copy of the table but with only a clustered columnstore index. You would then need to point big report queries at dbo.Table_CS and other queries (that use your traditional nonclustered indexes) at dbo.Table. And we would need to modify all insert/update/delete operations in the ETL on dbo.Table in order to replicate any row operations into dbo.Table_CS. This is painful, I understand, but should provide the big performance gains you're looking for. (It would be totally unnecessary to design two tables and redirect some reports in this way if you intend to move to SQL 2016 in the near term, so this step is not recommended.)

That's it!

Need proof that columnstore indexes are awesome and will improve the performance of your large queries? Get used to loving columnstore scans! 

Your Devs' Questions Answered With sys.dm_server_services

It starts with "I'm a developer...

"... with no RDP access, how can I tell if the SQL Server Agent service is running?"
"... and I don't think CDC is working. Is the SQL Agent on?"
"... how can I tell how long has the SQL Server been running? Did somebody reboot it last night?"
"... so hey I need to know if the SQL Server service set to automatic startup."

Here's an easy script that anyone can use, it can be especially helpful for developers without RDP access to the Windows Server that is hosting the SQL Server service. It'll answer these developer questions and more, in addition to a lot of other utility for you as a DBA.

SELECT  servicename -- Ex: SQL Server (SQL2K8R2)
, startup_type_desc -- Manual, Automatic
,  status_desc -- Running, Stopped, etc.
,  process_id
,  last_startup_time -- datetime
,  service_account
,  filename
,  is_clustered -- Y/N
,  cluster_nodename
FROM   sys.dm_server_services

You don't need to be a member of the sysadmin server role to run this, but you do need VIEW SERVER STATE. Developers can be given this permission, even in production, as it gives them access to many key dynamic management views (DMVs), including DMV's for diagnostics and performance tuning.

MSDN reference: https://technet.microsoft.com/en-us/library/Hh204542(v=SQL.110).aspx

Restoring Toggle Results Window as Ctrl+R in SSMS

Recent versions of SQL Server Management Studio have unmapped Control+R as the toggle for the Results window in a typical query window. Each time I work on a new SSMS environment or refresh my laptop, I find myself seeking out how to restore this setting. This blog post is as much a reference for my future self as anything.

Instead of the results pane, you may instead see at the bottom left corner of SSMS the following messages:
"(Ctrl+R) was pressed. Waiting for second key of chord..." or then "The key combination (Ctrl+R, Ctrl+R) is not a command."

Here's how to remap the Results pane to Ctrl+R, including a picture below.

1. In SSMS, go to Tools, Options.
2. Under Environment, click on Keyboard.
3. In the Show Commands containing box, you're looking for "Window.ShowResultsPane".
4. Change the "Use new shortcut in" dropdown to "SQL Query Editor." 
• Note: This step is important - mapping Ctrl+R to "Global" won't work.
5. Click in the "Press shortcut keys" field and hit Ctrl+R on your keyboard. Click the Assign button.
6. Click OK.
7. In your query windows, you can now Ctrl+R to toggle the results window all you like.

Updated: 20180105 because again I needed to this blog post as a reference myself, added the chord messages to improve SEO.

Painful or Helpful? No SSMS Multiserver Results if One Instance Errors

I've been going back and forth with this question for a few days now.

If one instance in a multiserver query presents an error, no resultsets from any of the other instances are displayed, even if n "row(s) affected)" is displayed in the Messages tab.

This is particularly painful when querying dynamic management objects across server groups, because new DMV's and new DMV columns are being introduced in every version, sometimes with service packs.

• Is this a feature preventing you from using code that won't work against the group as a whole, saving you from accidentally assuming that all instances returned data?
• Or, is this a pain that causes you to need version-specific registered server groups to get at data from some instances?

There is no right answer to this question, only the opportunity to be aware of this behavior.

There is an old MSConnect item from 2007 for a similar issue that was marked as "won't fix" in 2011, so it is not a bug. I am not sure if there is any beneficial intent from MS to prevent you from getting any data when one instance in a group has a syntax error.

Imagine if, within a large list of production SQL Servers, a handful are of a prior SQL version where some utility scripts won't work.

Should I then create another registered server group for instances that can run sys.dm_os_volume_stats, or a group for instances that support the new columns added to sys.dm_exec_query_stats? No correct answer here either, in the absence of an option to allow for partial resultsets to be returned from multiserver queries, you will need to consider what suits your needs best.

Here's the steps to reproduce for a simple scenario:

1. Add a SQL 2008 instance and a SQL 2008 R2 instance to a new Registered Servers group.
2. Create a new multiserver query.
3. Attempt to query a DMF or DMV that was introduced in SQL 2008 R2, such as sys.dm_os_volume_stats. (Sample script below.)
4. The message table includes one error and one rows returned message, like below. No Results tab is returned, so the rows are not visible even though the query worked successfully on the SQL 2008 R2 instance.

sqldemo1\sql2008(domain\user): Msg 208, Level 16, State 1, Line 2Invalid object name 'sys.dm_os_volume_stats'.sqldemo1\sql2008r2(domain\user): (2 row(s) affected)

Sample script:

select distinct
vs.volume_Mount_point,
file_system_type,
   drive_size_GB = convert(decimal(19,2), vs.total_bytes/1024./1024./1024. ) ,
   drive_free_space_GB = convert(decimal(19,2), vs.available_bytes/1024./1024./1024. ),
   drive_percent_free = CONVERT(DECIMAL(9,2), vs.available_bytes * 100.0 / vs.total_bytes)
FROM
   sys.master_files AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.database_id, f.file_id) vs

I have confirmed this behavior in Microsoft SQL Server Management Studio 12.0.2000.8, 11.0.3128.0 and 10.50.4000.0.

Challenges with Downgrading TFS 2010 from SQL Enterprise to SQL Standard Edition

I recently had the challenge of migrating an installation of Microsoft Team Foundation Server 2010 from a shared SQL Server 2008 R2 Enterprise instance to its own SQL Server 2008 R2 Standard instance on a new server.

Because of some Enterprise edition-specific feature usage, this turned out to have two problems during the restoration:

• Use of database compression on some TFS 2010 tables 
• Use of perspectives in the TFS 2010 SSAS database "TFS_Analytics" 

Neither feature is available in Standard edition and conveniently the error message when restoring the SQL or SSAS databases from Enterprise to Standard clearly indicated this. After making the client aware, the decision made was to try and remove these features from their TFS installation.

After removing these Enterprise features from copies of the databases, I was able to back up and restore the copied databases (during the migration outage) to the new server without any more edition failures.

Here's how:

Remove Compressed Indexes

You may encounter this error if you attempt to restore any database that uses data compression from Enterprise to Standard edition:

cannot be started in this edition of SQL Server because part or all of object 'foo' is enabled with data compression or vardecimal storage format

Here is a script to look through all tables for compressed partitions (either heaps or indexes) and REBUILD them with DATA_COMPRESSION = NONE. This obviously only works on Enterprise Edition of SQL 2008 or higher.

--Enable WITH (ONLINE = ON) if possible

Declare @sqltext nvarchar(1000), @tbname sysname, @index_id int, @index_name nvarchar(100)

Declare tbl_csr cursor
FOR
select name = '['+s.name+'].['+o.name+']', p.index_id, i.name
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.indexes i on i.index_id = p.index_id and i.object_id = o.object_id 
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Open tbl_csr

Fetch Next from tbl_csr into @tbname, @index_id, @index_name

 While (@@FETCH_STATUS=0)
 Begin

   If @index_id =0 
    begin
     --catches heaps
     set @sqltext=N'ALTER Table '+@tbname + N' REBUILD WITH (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild heap ' + @tbname 
    end
   else
    begin
     set @sqltext=N'ALTER INDEX ' + @index_name + ' ON '+@tbname + N' REBUILD WITH  (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild index ' + @tbname 
    end

   Fetch next from tbl_csr into @tbname, @index_id, @index_name

 End

Close tbl_csr
Deallocate tbl_csr

Below is a demo you can use to simulate the script as it finds clustered indexes, nonclustered indexes and heaps to rebuild appropriately, while also ignoring XML indexes (which could present a problem if you take a blanket ALTER INDEX ALL ... REBUILD.)

use adventureworks
go

--test lab
if not exists (select 1 from sys.schemas where name = 'testschema')
exec (N'create schema testschema')

go
if exists (select 1 from sys.objects where name = 'testfeature_index') 
drop table testschema.testfeature_index
go
create table testschema.testfeature_index (id int not null identity(1,1) primary key , bigint1 bigint not null, xml1 xml null)
insert into testschema.testfeature_index (bigint1) values (123456789123456789),(1234567891234567891),(1234567891234567892),(1234567891234567893)

go
set nocount on 
insert into testschema.testfeature_index (bigint1)
select bigint1+5 from testschema.testfeature_index 
go 10
set nocount off
alter index all on testschema.testfeature_index rebuild with (data_compression = page)
create nonclustered index idx_nc_testfeature1 on testschema.testfeature_index (bigint1) with (data_compression = page)
create primary xml index idx_nc_testfeaturexml1 on testschema.testfeature_index (xml1) 
create xml index idx_nc_testfeaturexml2 on testschema.testfeature_index (xml1)  USING XML INDEX idx_nc_testfeaturexml1 FOR PATH
go
if exists (select 1 from sys.objects where name = 'testfeature_heap') 
drop table testschema.testfeature_heap
go
create table testschema.testfeature_heap (id int not null identity(1,1)  , bigint1 bigint not null)
insert into testschema.testfeature_heap (bigint1) values (123456789123456789),(1234567891234567891),(1234567891234567892),(1234567891234567893)
go
set nocount on 
insert into testschema.testfeature_heap (bigint1)
select bigint1+5 from testschema.testfeature_heap 
go 10
set nocount off
go
alter table testschema.testfeature_heap rebuild  with (data_compression = PAGE)
create nonclustered index idx_nc_testfeature1 on testschema.testfeature_heap (bigint1) with (data_compression = page)

go

--Enable WITH (ONLINE = ON) if possible

select name = '['+s.name+'].['+o.name+']', case p.index_id when 0 then 'Heap' when 1 then 'Clustered Index' else 'Index' end
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'
go
Declare @sqltext nvarchar(1000), @tbname sysname, @index_id int, @index_name nvarchar(100)

Declare tbl_csr cursor
FOR
select name = '['+s.name+'].['+o.name+']', p.index_id, i.name
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.indexes i on i.index_id = p.index_id and i.object_id = o.object_id 
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Open tbl_csr

Fetch Next from tbl_csr into @tbname, @index_id, @index_name

 While (@@FETCH_STATUS=0)
 Begin

   If @index_id =0 
    begin
     --catches heaps
     set @sqltext=N'ALTER Table '+@tbname + N' REBUILD WITH (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild heap ' + @tbname 
    end
   else
    begin
     set @sqltext=N'ALTER INDEX ' + @index_name + ' ON '+@tbname + N' REBUILD WITH  (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild index ' + @tbname 
    end

   Fetch next from tbl_csr into @tbname, @index_id, @index_name

 End

Close tbl_csr
Deallocate tbl_csr


go

select name = '['+s.name+'].['+o.name+']', case p.index_id when 0 then 'Heap' else 'Index' end
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Remove Perspectives from SSAS Database

You may encounter this issue when restoring any SSAS database from Enteprise to Standard editions, not just in TFS.

Errors related to feature availability and configuration: The 'Perspectives' feature is not included in the '64 Bit Standard Edition' SKU.

The solution is multi-step but straightforward.

Here's a breakdown of the steps. The XMLA code to accomplish this will follow:

1. Backup the SSAS database (TFS_Analytics) on the Enterprise SSAS instance.
2. Restore the SSAS database with a new name (TFS_Analytics_std) to a new DbStorageLocation on the Standard SSAS instance.
3. In Management Studio Object Explorer, script out the database as an ALTER statement. Find the <Perspectives> section of the code. (Note - "Perspectives" is plural.) Drag and select to the </Perspectives> tag. Be sure to capture all the <Perspective>...</Perspective> sections. Delete.
• Easier way? Collapse the box to the left of the <Perspectives> tag. Select the collapsed line for the <Perspectives> tag. Delete.
4. Execute the XMLA script. (Hit F5.) This will remove the perspectives from the database.
5. Backup the TFS_Analytics_std database to a new location.
6. Restore the backup of the TFS_Analytics_std database to the Standard Edition server, renaming the database back to TFS_Analytics.

Code examples below. Be aware that you may need to apply the <AllowOverwrite>true</AllowOverwrite> element to overwrite any .abf files during a backup, or databases during a restore. For safety reasons, this option has been set to false for these code examples.

1. On the old Enteprise server, backup the SSAS database (TFS_Analytics).
   

   <backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <object>
     <databaseid>Tfs_Analysis</DatabaseID>
    </object>
    <file>M:\MigrationBackups\TFS_analysis_migrationbackup_2014.abf</file> <allowoverwrite>false</allowoverwrite>
   </backup> 

2. On the old Enteprise server, restore the SSAS database with a new name (TFS_Analytics_std) to a new DbStorageLocation.

   <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <File>M:\migrationbackups\TFS_analysis_migrationbackup_2014.abf</File>
    <DatabaseName>TFS_Analysis_std</DatabaseName>
   <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">m:\migrationbackups\</DbStorageLocation
   >
   

3. In Management Studio Object Explorer, script out the database as an ALTER statement.
   
4. Find the <Perspectives> section of the code and remove it.
   

5. Execute the XMLA script. (Hit F5.) This will remove the perspectives from the database.
6. On the old Enteprise server, backup the TFS_Analytics_std database to a new location.
   

   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <Object>
       <DatabaseID>Tfs_Analysis_std</DatabaseID>
    </Object>
     <File>M:\MigrationBackups\TFS_analysis_migrationbackup_2014_std.abf</File>
   </Backup>
   

7. Create a new XMLA script on the target Standard Edition server. Restore the backup of the TFS_Analytics_std database to the Standard Edition server, renaming the database back to "TFS_Analytics".
   
   If there are no other Enterprise-only features in use in the SSAS database, this backup should restore successfully.
   
   Note also that the restore here occurs across the wire, using the UNC path to a temporary folder share. The SSAS service account on the new Standard edition server must have permissions to view this folder share.

   <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <File>\\serverpathnamehere\MigrationBackups\TFS_analysis_migrationbackup_2014_std.abf</File>
   <DatabaseName>TFS_Analysis</DatabaseName>
    <AllowOverwrite>false</AllowOverwrite>
   

Easy Backup History Review Script

I often use these scripts to check on the backups of all databases in an unfamiliar SQL Server instance, regardless of the platform that is taking the backups (SQL maintenance plans, T-SQL scripts in an agent job, and third-party software).

The query sorts databases by the backup type and then the recovery model configured. Because the backups are grouped by database name, it shows the most recent full backup for each database, most recent differential if it exists, and then the most recent transaction log backup (but only for databases in FULL or BULK_LOGGED recovery models).

Most importantly, it will show the most recent transaction log backup for all databases in FULL or BULK_LOGGED recovery models, even if a transaction log backup has never been taken.

If any of the dates look out of place, too old or NULL, they will stand out right away.

• The first two scripts are functionally equivalent. The first uses SQL 2005 system views (sys.databases) that are only appropriate if all databases are running SQL 2005 compatibility mode or higher. 
• The second script is using SQL 2000 system views (sysdatabases), which are now deprecated.
• The third script provides a granular list of all backup events by database, and is valid for SQL 2000 and up.

--sql2005 and above
select 
 database_Name
 , backuptype 
 , d.recovery_model_desc
 , BackupDate = MAX(BackupDate)
 from sys.databases d
 inner join 
 (
select distinct 
 database_name
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = MAX(backup_start_date)  
 from msdb.dbo.backupset bs       
 group by Database_name, type
 UNION 
 select distinct
 db_name(d.database_id)
 , backuptype = 'Database'
 , null
 FROM master.sys.databases d
 UNION
 select distinct
 db_name(d.database_id)
 , backuptype = 'Transaction Log'
 , null
  FROM master.sys.databases d
  where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')
  
 ) a
 on db_name(d.database_id) = a.database_name
 group by database_name, backuptype, d.recovery_model_desc
order by backuptype, recovery_model_desc, BackupDate asc
 
 

 --sql 2000 and above
select distinct 
 database_name = d.name 
 , a.backuptype 
 , RecoveryModel = databasepropertyex(d.name, 'Recovery')  
 , BackupDate = Max(a.backup_start_date)  
 from master.dbo.sysdatabases d
 left outer join 
 (  select distinct 
   database_name
   , backuptype = case type WHEN 'D' then 'Database'
         WHEN 'I' then 'Differential database'
         WHEN 'L' then 'Transaction Log'
         WHEN 'F' then 'File or filegroup'
         WHEN 'G' then 'Differential file'
         WHEN 'P' then 'Partial'
         WHEN 'Q' then 'Differential partial' END
   , backup_start_date = MAX(backup_start_date)   
   from msdb.dbo.backupset bs       
   group by Database_name, type
   UNION 
   select distinct
     d.name
   , backuptype = 'Database'
   , null
   FROM master.dbo.sysdatabases d
   UNION
   select distinct
     d.name
   , backuptype = 'Transaction Log'
   , null
    FROM master.dbo.sysdatabases d
    where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')
  
 ) a
 on d.name = a.database_name
 group by d.name , backuptype , databasepropertyex(d.name, 'Recovery')
order by backuptype, RecoveryModel, BackupDate asc
 

--granular backup history
select distinct 
 database_name
 , type
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = backup_start_date
 , database_backup_lsn
 , bf.physical_device_name
 from msdb.dbo.backupset bs 
 left outer join msdb.dbo.[backupmediafamily] bf
 on bs.[media_set_id] = bf.[media_set_id]
 --where database_name = 'databasenamehere'
 --and type in ('d', 'i')
 
  order by database_name asc, backupdate desc
  

YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY.  Why?  For one, it sorts correctly.  And the biggest reason - no need to worry about leading zeroes, and therefore the data can be stored as NUMERIC, not VARCHAR data.

I needed to do month math on this data. For example, give me the previous month, six months ago, one year ago, nine months in the future, etc.  This gets tricky because though it is tempting to do -1, -6, -12, +9, the rollover of years does not work at all.

The following function solves the problem.

ALTER FUNCTION dbo.[YearMonth_Math]
(
@Yearmonth int, @months smallint
)
RETURNS INT
AS
BEGIN
RETURN
 CASE WHEN @months THEN CASE --This case handles flipping January to the previous years December.
 WHEN convert(int, Right(@Yearmonth,2)) <= abs(@months) --will we need to flip a year?
 THEN convert(int,
  convert(char(4), Left(@Yearmonth,4) + (((@Months)) / 12)  - case when right(@YearMonth, 2) + (@months % 12) < 1 Then 1 else 0 end)
  + convert(char(2), right('0' +  convert(varchar(3), (right(@yearmonth, 2) + (@months % 12) 
  + case when right(@YearMonth, 2) + (@months % 12) < 1 Then 12 else 0 end)),2))
  )
  --Otherwise, this previous month calculation is very simple.
  ELSE @Yearmonth + @months
 END
 WHEN @months >0
 THEN CASE --This case handles flipping December to the next years January.
  WHEN 12 - convert(int, Right(@Yearmonth,2)) <= @months --will we need to flip a year?
  THEN convert(int,
   convert(char(4), left(@YearMonth,4) + ((@months + right(@yearMonth,2) -1) / 12) ) +   
   convert(char(2), right('0' + convert(varchar(3), (right(@YearMonth, 2) + (@months % 12)  
   - case when right(@YearMonth, 2) + (@months % 12) > 12 THen 12 else 0 end)),2))
   )
   --Otherwise, this previous month calculation is very simple.
   ELSE @Yearmonth + @months
  END
 ELSE @YearMonth
 END
END

Here's some testing to verify all the different possibilities.

select  dbo.[YearMonth_Math] (201212,1), 201301
select  dbo.[YearMonth_Math] (201212,2), 201302
select  dbo.[YearMonth_Math] (201212,7), 201307
select  dbo.[YearMonth_Math] (201212,12), 201312
select  dbo.[YearMonth_Math] (201212,13), 201401
select  dbo.[YearMonth_Math] (201212,24), 201412
select  dbo.[YearMonth_Math] (201212,25), 201501
select  dbo.[YearMonth_Math] (201212,36), 201512
select  dbo.[YearMonth_Math] (201201,-1), 201112
select  dbo.[YearMonth_Math] (201201,-2), 201111
select  dbo.[YearMonth_Math] (201201,-7), 201106
select  dbo.[YearMonth_Math] (201201,-12), 201101
select  dbo.[YearMonth_Math] (201201,-13), 201012
select  dbo.[YearMonth_Math] (201201,-24), 201001
select  dbo.[YearMonth_Math] (201201,-25), 200912
select  dbo.[YearMonth_Math] (201212,-1), 201211
select  dbo.[YearMonth_Math] (201212,-2), 201210
select  dbo.[YearMonth_Math] (201212,-7), 201205
select  dbo.[YearMonth_Math] (201212,-12), 201112
select  dbo.[YearMonth_Math] (201212,-13), 201111
select  dbo.[YearMonth_Math] (201212,-24), 201012
select  dbo.[YearMonth_Math] (201212,-25), 201011
select  dbo.[YearMonth_Math] (201212,-36), 200912
select  dbo.[YearMonth_Math] (201206,1), 201207
select  dbo.[YearMonth_Math] (201206,-1), 201205
select  dbo.[YearMonth_Math] (201206,2), 201208
select  dbo.[YearMonth_Math] (201206,-2), 201204
select  dbo.[YearMonth_Math] (201201,1), 201202
select  dbo.[YearMonth_Math] (201201,13), 201302
select  dbo.[YearMonth_Math] (201201,25), 201402

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

      with cteFK (pktable, fktable) as ( 
       select             
            pktable = s1.name + '.' + o1.name 
       ,    fktable = isnull(s2.name + '.' + o2.name, '')        
       from sys.objects o1       
       left outer join sys.sysforeignkeys fk on o1.object_id = fk.fkeyid        
       left outer join sys.objects o2 on o2.object_id = fk.rkeyid        
    left outer join sys.schemas s1 on o1.schema_id = s1.schema_id
    left outer join sys.schemas s2 on o2.schema_id = s2.schema_id
       where o1.type_desc = 'user_table'       
       and o1.name not in ('dtproperties','sysdiagrams')        
       group by s1.name + '.' + o1.name 
      , isnull(s2.name + '.' + o2.name, '')       
), cteRec (tablename, fkcount) as  ( 
       select tablename = pktable 
       ,    fkcount = 0
       from cteFK    
       
       UNION ALL       
       
    select tablename = pktable 
       , fkcount = 1
       from cteFK  
       cross apply cteRec        
       where cteFK.fktable = cteRec.tablename    
       and cteFK.pktable <> cteRec.tablename
) 
select
 TableName
, InsertOrder = dense_rank() OVER ( ORDER BY max(fkcount) asc )
from (       
       select
              tablename = fktable
       ,      fkcount = 0 
       from cteFK 
       group by fktable    
        
       UNION ALL    
 
       select tablename = tablename, fkcount = sum(ISNULL(fkcount,0))   
       from cteRec      
       group by tablename
     ) x 
where x.tablename <> ''
group by tablename 
order by InsertOrder asc, TableName asc

Use the sample script from the previous post on how to "Script Out Foreign Keys With Multiple Keys" for an example of building a complicated set of foreign key relationships to test this script out.

Here's the results from that example:

Similarly, this script would generate an order for you to unravel the data - the numbers descending would allow you to delete in the proper order,

delete from fktable11
delete from fktable10
delete from fktable9
delete from fktable8
delete from fktable6
delete from fktable4
delete from fktable2
delete from fktable7
delete from fktable5
delete from fktable3
delete from fktable1

... or drop the tables in the proper order.

drop table fktable11
drop table fktable10
drop table fktable9
drop table fktable8
drop table fktable6
drop table fktable4
drop table fktable2
drop table fktable7
drop table fktable5
drop table fktable3
drop table fktable1

UPDATED 20140507: changed old system reference objects (sysobjects) to new system reference objects (sys.objects) UPDATED 20140624: added "and cteFK.pktable <> cteRec.tablename", see comments for explanation.

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,

--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3))

create table dbo.fktable2(
  id int not null identity(1,1) primary key
, id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY (id1, id2, id3) 
  REFERENCES dbo.fktable1 (id1, id2, id3))

Combining those multiple records in the sys.foreign_key_columns into a concatenated string in order to get this is tricky:

--Script 2
ALTER TABLE [dbo].[fktable2]  WITH CHECK 
  ADD  CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY([id1], [id2], [id3])
  REFERENCES [dbo].[fktable1] ([id1], [id2], [id3])

Here's how I recently did this.   It actually turned out to be more complicated than I thought, certainly more complicated that your standard throw-strings-together-based-on-system-tables.  This is because we need to build a recurse of the multi-key values that are both referenced and referencing in foreign keys. 

Adding the Clustered Key To Your Nonclustered Index? Part 2

Of course, a rather obvious answer presents itself the next day.  After reviewing the actual environment that my colleague was working in, it popped quickly into mind.

What if the table has a compound primary key?  See comments for a play-by-play.

drop table dbo.testclusteredinclude

go

create table dbo.testclusteredinclude

(             id1 int not null

,             id2 int not null

,             id3 int not null

,             text1 varchar(30) Not null

,             constraint pk_testclusteredinclude primary key  (id1, id2, id3)

)

go

insert into dbo.testclusteredinclude (id1, id2, id3, text1) values (1,2,3,'test1'); --put in our seed row

--filler data of 10000 rows

with cte3pk (id1, id2, id3) as

(select id1=2,id2=3,id3=4

union all

select id1+1, id2+1, id3+1

from cte3pk

where id1 <= 10000

)

insert into dbo.testclusteredinclude (id1, id2, id3, text1)

select id1, id2, id3, 'test2' from cte3pk

OPTION (MAXRECURSION 10000);

go

alter index all on dbo.testclusteredinclude  rebuild

go

--turn on show actual exec plan

--Second key of the Clustered Key can benefit, this easy to understand.

-- We're simply giving SQL a narrower set of data, via the nonclustered index.

select id2, text1 from dbo.testclusteredinclude where

id2 = 1001

go

create nonclustered index idx_nc_testclusteredinclude_id2_text1

on dbo.testclusteredinclude (id2, text1)

go

select id2, text1 from dbo.testclusteredinclude where

id2 = 1001

go

drop index idx_nc_testclusteredinclude_id2_text1 on dbo.testclusteredinclude

go

--Still, putting a subsequent key of a compound clustered key in the include column doesn't help.

-- SQL can still do an index seek on id2, even when the index doesn't contain it (idx_nc_testclusteredinclude_text1).

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

create nonclustered index idx_nc_testclusteredinclude_text1

on dbo.testclusteredinclude (text1)

go

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

create nonclustered index idx_nc_testclusteredinclude_text1_inc_id2

on dbo.testclusteredinclude (text1) include (id2)

go

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

drop index idx_nc_testclusteredinclude_text1 on dbo.testclusteredinclude

drop index idx_nc_testclusteredinclude_text1_inc_id2 on dbo.testclusteredinclude

go

One final note - none of the SELECT statements above generate any missing index suggestions in SQL 2012 SP1 or SQL 2008 SP2, even though without any nonclustered indexes they all generated Clustered Index Scans.

Adding the Clustered Key To Your Nonclustered Index?

A colleague of mine was understandably confused when using the almost-perfect missing index engine in SQL 2008. The engine recommended he include the clustered index key in a new nonclustered index. Read that again - you're right, it doesn't make a lot of sense.

From my colleague:

Why would you ever need to have your primary key or clustered index key as a key field on a non-clustered index? That don't make any sense to me. If the SQL engine needs to make a lookup on the clustered index have the primary key as an include on your non-clustered index. ...I researched it and the only difference is that my current nonclustered has the primary key as a key field and now it's suggesting making it a include column.

I've got a wild hunch that the clustered key might make sense to have as a key in the nonclustered index as long as it isn't the first key, and the query is ordering on a nonclustered key. However, I can't think of a representative situation to prove that, so I might be wrong. I'm open to suggestions for a reproducible lab sample, perhaps your starting point would be the sample script below.

That said, I don't think it would make any sense to put the clustered key in the INCLUDE of a nonclustered index.  I'm puzzled as to why the missing index feature would suggest this.

Here's a lab for proof. In this scenario, the index was 19.320312 MB without the include, and 19.320312 with the include. SQL doesn't store an extra copy of the clustered key in the nonclustered index just because you asked for it in the INCLUDE or in the key. That's because the clustered key is already a part of any nonclustered index key.

This reinforces the point that the clustered key should always been as unique (so it's helpful) and narrow (so it doesn't waste space) as possible. That's why for almost all tables, an integer IDENTITY(1,1) key is a perfect clustered index and primary key. 4 bytes per row. And that's why for all tables, a GUID is a less efficient choice for your clustered key. 16 burdensome bytes per row...which is then lugged around by all your nonclustered keys...  </GUIDrant>

use somedatabasenotinproduction 

go

create table dbo.testclusteredinclude 

(      id int identity(1,1) not null primary key 

,      text1 varchar(30) Not null) 

go

insert into dbo.testclusteredinclude (text1) values ('test1'), ('test2') 

go 

insert into dbo.testclusteredinclude (text1) 

select text1 from dbo.testclusteredinclude where text1 = 'test2' 

go 20 

--turn on show actual exec plan 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1' 

go 

create nonclustered index idx_nc_testclusteredinclude_text1  

on dbo.testclusteredinclude (text1) 

go 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1' 

go 

drop index idx_nc_testclusteredinclude_text1 

on dbo.testclusteredinclude 

go 

create nonclustered index idx_nc_testclusteredinclude_text1 

on dbo.testclusteredinclude (text1)  include (id) 

go 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1'
 

Adding the Clustered Key To Your Nonclustered Index?  Part 2

On the Advantages of DateTime2(n) over DateTime

Starting with SQL 2008, we database developers started becoming more familiar with datetime2.  Sometimes folks need convincing though, so here goes.

Here's a brief review of how the precision of the datetime2 data type converts from a varchar representing a time value out to one ten-millionths of a second.  Run this script yourself or view the results in the image below:

declare @datetime varchar(50) = '01/01/2012 11:11:11.1111111'

select        @datetime

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

select        convert(datetime2(4), @datetime)

select        convert(datetime2(7), @datetime)

select        convert(datetime2, @datetime) --default is 7

Want to do the same conversion with datetime or smalldatetime?  Can't.

Msg 241, Level 16, State 1, Line 10

Conversion failed when converting date and/or time from character string.

The old data types can't handle that much precision.  Gotta dial it down for the old datetime and smalldatetime types.  How quaint.

declare @datetime varchar(50) = '01/01/2012 11:11:11.111'

select        convert(datetime, @datetime)

select        convert(smalldatetime, @datetime)

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

Note how the old data types are incapable of storing precision out to one one-thousandth of a second.

How about date ranges?

datetime: 1753-01-01 through 9999-12-31
smalldatetime: 1900-01-01 through 2079-06-06
datetime2: 0001-01-01 through 9999-12-31


Now the kicker. What's the cost to storing all that extra precision in datetime2? None. You can get more precision than datetime and fewer bytes per row per field by specifying a precision value for columns declared as datetime2(n).

For example, datetime(2) stores one hundreds of a second - realistically the same precision as datetime, which rounds the third place to the right of the decimal. And datetime(2) is two bytes smaller than datetime, making it ideal.

Don't need seconds, just hours and minutes? Stick with smalldatetime, 4 bytes, as opposed to datetime2(0) at 6 bytes.

Storage requirements 

smalldatetime:
4 bytes - precision to the minute (seconds are always :00)

datetime2(n):
6 bytes for precisions less than 3 - precision up to one hundredth of a second
7 bytes for precisions 3 and 4 - precision up to one ten thousandth of a second
8 bytes for precisions > 4 - precision up to one ten millionth of a second (within 100 nanoseconds)

datetime:
8 bytes - precision to one hundredth of a second, rounded precision to three thousands of a second

Clearly, datetime2 is an upgrade in range of values, precision (no rounding!) and storage size over datetime.

And that's only if you need to store date and time info. Since SQL 2008, we've also been able to store mm/dd/yyyy data in the date data type (3 bytes), and discrete hh:mm:ss in the time data type (5 bytes).

Oh yeah, and even though datetime is not deprecated, this friendly yellow box might make you think so.

Note
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

.net developers?  Datetime and Datetime2(n) both map to System.DateTime.  No worries there.  More info here: http://msdn.microsoft.com/en-us/library/bb675168.aspx

HOWEVER, you should probably be using datetimeoffset, not just datetime2. DateTimeOffset works similarly to datetime2, but adds 2 bytes to store a +-hh:mm data. While this doesn't store the time zone (CDT, EST, etc.) it does store the offset. I've given a joint presentation on this topic with a colleague that goes into more detail. (update 7/31/2017)

Management Studio Database Diagram Owners

If you're working in an environment as a developer without sysadmin privileges, and you are creating database diagrams using Management Studio underrated diagram tool, but not the database owner or a sysadmin, you'll see your created diagrams look like this

where the diagram is owned by the developer using the sql login 'jdoe'.  With many diagrams created by multiple developers, this can be ugly, confusing and just nonsensical.  Sysadmins don't have this problem, which is why like me you may have used Database Diagrams for years without encountering this issue.

There is no way to change this or rename it from the Management Studio GUI, but a simple script can fix the problem.

Find the diagram you want to rename, and the new principal you want to be the "owner".

select * from dbo.sysdiagrams

select * from sys.database_principals

(abbreviated results shown below)

name principal_id diagram_id

Diagram_0 1 1

name principal_id type type_desc

dbo 1 S SQL_USER

jdoe 6 S SQL_USER

This script can be executed by the developer to change the owner of the diagram from jdoe to dbo.

  update dbo.sysdiagrams

  set  principal_id  = 1 --dbo

where  principal_id  = 6 --jdoe

and    diagram_id    = 1

And now the diagram isn't owned by one of your developers.