SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Monday, November 19, 2012
SQL Server: A Gotcha with Disabling/Enabling Foreign Keys and Check Constraints
When loading a large dataset, you can significantly reduce the load time by disabling foreign keys and check constraints during the load process (and ETL’s often perform data integrity checks anyway). This is commonly done with large data warehouses where entire dimensions are reloaded every day. When the load is complete, they are re-enabled, and SQL Server will ensure that the keys and constraints are honoured. That is, as long as you re-enable them correctly. And due to the syntax, it is possible to re-enable them without forcing a re-validation of the data (in which case, they have the status of ‘not-trusted’).
Not only does this mean that referential integrity is not being enforced (which is a bad thing!), but it can also have a negative performance impact on queries, because SQL Server won’t use un-trusted constraints in order to build execution plans.
Run this TSQL to check if you have any foreign keys or check constraints that are not trusted:
-- Must be run in the database being examined:
To ensure that SQL Server not only re-enables a constraint, but also rechecks all the data, you have to specify the CHECK keyword twice:
ALTER TABLE MyTable
Note: If the table is very large, you might want to perform this during scheduled maintenance.
MSN, Email: mitch døt wheat at gmail.com