Blog

Untrusted Foreign Keys

  What is a Foreign Key?

Do you trust your foreign keys?

Do you trust your foreign keys?

A foreign key is a link between 2 tables that is used to enforce referential integrity in the database.

For example, if you have an order and a customer table, there is probably a logical relationship between the 2 tables. An order can’t exist without being linked to a customer record.

When trusted, these keys help ensure that the data in your database stays ‘clean’ and logical.

By establishing trusted foreign keys in SQL Server between your tables, the optimizer is able to make some assumptions about the data and therefore make more efficient execution plans for your queries.

Foreign keys are trusted by default when created since checks are done to ensure the data all lines up. If the data does not match as expected, errors are relayed and the key is not created. Once established they keys will prevent ‘bad’ data from being entered into your database so long as they remain trusted.

 

What is an Untrusted Foreign Key?

An untrusted foreign key is one that has had the referential integrity of the relationship removed. SQL Server is unable to ‘trust’ that the data is clean in both tables and therefore isn’t exactly sure of the best way to proceed. This can start to show itself by queries getting slower as the optimizer starts to perform extra checks to make sure the data it is getting is good.

 

  How Do They Become Untrusted?

We have established that foreign keys are important in relational databases since they check & help enforce the referential integrity of the data. Sometimes though, when doing large bulk loads and similar operations, SQL Server can slow down due to the large number of checks being done.

One of the ways to improve the performance of these bulk loads is to disable the foreign keys on the tables being loaded. This is usually a safer & faster process than just dropping and recreating the keys.

Foreign keys then become untrusted when they are not re-enabled correctly after the bulk load or similar operation is completed.

Usually the foreign key is re-enabled with the CHECK CONSTRAINT option in the ALTER TABLE statement. This is fine to re-enable the foreign key, but it does not tell SQL Server to re-verify the integrity of the data.

Because of this, SQL will not know if the relationship can still be trusted, that the data is ‘clean’ between the 2 tables. This results in the SQL Server optimizer ignoring the foreign key restraint and checking the data integrity itself with extra processes added to your query execution plan.

 

  How Do I Trust a Foreign Key Again?

Before you are able to trust foreign keys again, you have to identify the ones that are no longer trusted first.

This first snippet of code goes through your database and identify the foreign keys & constraints that are enabled yet not trusted. (We don’t have to worry about disabled keys)

This will identify the schema, object and name of the keys that are not trusted in your database.

Once you have that result set you can use the following statement to re-establish the trust for each result in the list:

ALTER TABLE <s.name from results>.<o.name from results> WITH CHECK CHECK CONSTRAINT <i.name from results>

The CHECK CHECK in the syntax seems odd, but it is just due to the alignment of 2 different options with the alter table statement.

The first is the WITH CHECK statement. This tell the ALTER statement to validate the table contents against the foreign key. By default when re-enabling an existing foreign key or constraint this is set to WITH NOCHECK. You have to explicitly define it when re-enabling a foreign key or constraint.

The second is the CHECK CONSTRAINT statement. This is used to configure if the constraint is enabled or disabled. CHECK CONSTRAINT is enabled, while NOCHECK CONSTRAINT disables the foreign key.

If you have a lot of results from the previous script, you can use the following script which will output the ALTER TABLE statements so you can copy & paste the ones you like.

This script could be easily modified to also apply the alter, but I personally prefer to look at the results and changes to be made prior to having it run, just in case.

The script can also be modified to enable all foreign keys & constraints by using ALL rather than the [i.name] but be sure that you want all keys and constraints enabled or disabled first.

Download it!

Any way you do it, you want to make sure your foreign keys and constraints are trusted so the SQL Server optimizer can create efficient plans for your queries.

 

P.S. – Don’t forget to sign up for our new SQL newsletter and your chance to win a $25 Amazon gift card at the end of November, 2014!

You can sign up here!

 

 

 

I Must Be Doing Something Right – MVP Award

I am extremely excited to announce that I have just received the Microsoft MVP award for SQL Server for 2014.

MVP_Horizontal_FullColor

For those that know what this whole MVP thing is there isn’t much more to say other than the headline:

I must be doing something right!

and

Yay!

 

For others that may not know what this is all about, here is a little more info of why I am excited and honored to receive this recognition:

Read more →

Video: Walk & Talk #003 – RPO

What is the RPO?

And why does it matter?

Join me as I walk through Jefferson Patterson Park’s Indian village and discuss the Recovery Point Objective and what it means is respect to SQL Server.

Location: Jefferson Patterson Park

More info: http://www.jefpat.org/

Video: Walk & Talk #002 – RTO

Recovery Time Objective (RTO)

What is it and why does it matter?

I am actually walking this time! Join me to walk & talk through what the RTO (Recovery Time Objective) is with regards to SQL Server.

Location: American Chestnut Land Trust – South Side Trail: Gravatt Lane (0.72 miles long)
more info @ http://www.acltweb.org/

Export a SSMS Query Result Set to CSV

Once you have a query that is returning the data you like in SSMS you have a few options to move the result set into a format that you can use in other tools.

The simplest way is to select all results and then copy & paste them into whichever other tool you are using. This is great for smaller sets of data, and works very well when using tools such as MS Excel.

There are some cases though where the quantity of data returned does not fit into the excel spreadsheet, or you may want to be able to import data into a different tool. To do this you need to export the data from the query set to a format that can be referenced by the other tools.

There are 2 options that can be done. The first is to execute the query with the results being output to a file. The second is to configure a data export based on a query.

 

Execute query results to a file

  1. Build your Query

    Build your Query

    Open SSMS (SQL Server Management Service) and open / create the query for the data you are looking for.

  2. Next we have to configure the output to file options.


  3. On the menu bar select: Tools then Options

    Select Tools then Options

    Select Tools then Options

  4. On the left select Query Results, SQL Server, Results to Text.

    Set the Results options

    Set the Results options

  5. Set output format to CSV. You can also mark the Display Results in a separate tab and the switch to results tab after the query executes if you like. Click OK to save the changes.

  6. Now when you go to export your query results you have a few options to enable to results to file feature. Press Ctrl+Shift+F to set the query to file destination (Ctrl+D will revert back to the grid output). You can also select Query, Results To & Results to File. There is also a quick button located on the SSMS Standard Toolbar.

    Set the Results to go to Text

    Set the Results to go to Text

    Or use the button on the toolbar

    Or use the button on the toolbar

  7. After having selected the output format, you can Execute the query. You will be prompted to enter a file name and location for the output file. The file will be created with a .rpt extension, but it is a just a plain text file.

    Set the File Name

    Set the File Name

  8. After the export you can browse to the directory and change the extension of the output file to .CSV if you like, and then open the file to verify the contents.

 

 

Export query results to a file


  1. Open SSMS (SQL Server management Studio) and open / create the query for the data you are looking for.

    Write your Query

    Write your Query

  2. Select all of your query text and Copy it to the clipboard (Ctrl+C).

  3. In the Object explorer, right click on the database you wish to export data from and select Tasks, Export Data

    Select Tasks then Export Data

    Select Tasks then Export Data

  4. The SQL Server Import and Export Wizard will open with the data source defaulted to the database you right-clicked on. Leave this as it is and click Next.

    Choose your Data Source

    Choose your Data Source

  5. Select Flat File Destination for the destination and provide the file name and other options. Once done, click Next to continue.

    Set the Destination

    Set the Destination

    image020Set the File Name

    Set the File Name

  6. On the next screen you are asked if you want to Specify Table Copy or Query. Select the Write a query to specify the data to transfer radio button and then click Next.

    Get ready to put in your own query!

    Get ready to put in your own query!

  7. Paste the Query

    Paste the Query

    Paste your query code from step #2 into the SQL Statement box. Click Parse to ensure syntax is correct. Once it is all good, click OK to close dialog box and then click Next to continue.

    Valid SQL is good!

    Valid SQL is good!

  8. You can click Edit mappings if you like to map data types and transformations during the export for more accurate data. When ready click Next.

    Change mappings if you need to

    Change mappings if you need to

  9. Leave Run immediately marked and click Next

    Yes! Run it now!

    Yes! Run it now!

  10.  

  11. Review actions to be performed. When ready click Finish

    All Done!

    All Done!

  12. You will see process complete indicating success (if there are errors you will need to determine why, it may just be a lack of permissions to save files on the server, etc.). You will also see a count of records that were transferred. Click Close when done.

  13. Now you can use the new file in whichever tool you prefer.

Live 360 Discount Code & Helping Digby!

Join me at SQL Live 360 this November!Live 360 discount code: LSPK05

I am hugely excited to have been selected to present at Live 360, November 17 – 21 in Orlando, FL.

Live! 360 brings together five conferences, and the brightest minds in IT and Dev, to explore leading edge technologies and conquer current ones.

More info is here: http://bit.ly/LSPK05HOME

I’ll be presenting the following session during the SQL Live 360 portion:

 Prevent Recovery Amnesia Forget the Backups

Through this introductory session we will review the various aspects & myths of backups in SQL Server.

We will pin down how developing a recovery strategy to meet business requirements is, in fact, more important than just a good backup strategy.

 

SPECIAL OFFER:

As a speaker, I am excited to be able to extend a $600 savings on the 5-day package for Live 360 this year!

Register here: http://bit.ly/LSPK05REG and use code LSPK05

Extra Bonus!

Digby! will get a new pool if you use our Live 360 discount code!

For every time this discount codes used, we will put the $50 we get in return towards paying for Digby’s second TPLO surgery he just had (And maybe towards a bigger pool for the dog park!)

You can learn more about what happened with Digby! and if you want to donate and not go to Live 360 you can do that as well here!

Get better at what’s available, and get ready for what’s coming.

We delve deep into the topics that matter most in your world today, covering practical knowledge, fundamentals, new technologies, and futures.

Register now!!

Take advantage of the additional $500 Summer Savings on top of our discount code before the price goes up  tomorrow! (You can still use our code after tomorrow too! Remember it’s LSPK05)

http://bit.ly/LSPK05REG

See you there!