Untrusted FK's demo + ADS notebooks

Back in 2012 at SQLSaturday Pensacola, I did a lunch-time lightning talk on indexing, which was supposed to have a big finale on how FK's could silently help with performance, and it didn't work. Didn't show the performance boost. It was because, thanks to poor scripting by SSMS, FK's are enabled but not trusted. This blog post serves as a long-belated knowledge share of a problem I didn't figure out live, as well as a successful recreation of that big finale.

Fast forward to a couple weeks ago at the May 2019 Baton Rouge SQL Server User Group, and local fellow SQL pro John Wells gave a presentation to a mixed audience of dev's and DBA's on performance tips, and one of them was to check up on untrusted FK's. Thought occurred to me to create some lab's to demonstrate not only how to untrusted FK's get accidentally created by well-intentioned dev's and DBA's, but how to fix, and also a demonstration on how they can be silent performance drags. So with all respect to John, Chris Bell who John mentioned in his presentation, and many others who have spoken and written well on the topic already, below is a lab demonstrating the issue, resolution, and performance impact.

To further one more goal, I've built the lab into a tsql script but also a new Azure Data Studio SQL notebook. Either offer a step-by-step introduction and demonstration. New to SQL Notebooks? No worries, they were released generally in March 2019 as a new feature of Azure Data Studio (which can be launched from SSMS 18+), which itself was made GA in September 2018.

A SQL Notebook is another way to combine text instructions with step-by-step, modular executed SQL statements in a single file. Notebooks are a popular form of mixed media, combining text, proofs, queries, and their live interactive output. They are already quite popular tools for data scientists to package methodology, analysis and live output. SQL Notebooks include kernels for querying SQL, PySpark, Spark, Python with multiple languages, and now in preview for SQL 2019 CTP 3.0, working with big data clusters. SQL Notebooks could also be used by DBA's and developers as rich, well-documented runbooks for complex exercises deployment or disaster recovery exercises.

SQL Notebooks are easy to learn and use, consider a SQL Notebook for your next detailed query breakdown or summary data analysis. Maybe you'll learn two things at once!

Github links to the SparkhoundSQL toolbox for the Untrusted FK's lab: .sql .ipynb

PS Check it out, Github even parses the notebook JSON correctly to display a (non-interactive) view of the notebook outside of ADS with the saved resultsets. Neat!

UPDATE: Refined my terminology a little bit to favor "SQL Notebooks" not "ADS Notebooks"

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.