I’ve had several metadata posts recently, all about SQL Server 2005. But yesterday, a colleague asked me a metadata question about SQL Server 2000. He wanted to know how to tell what constraints had been created with the NOCHECK option. This option tells SQL Server not to verify the constraint for existing data when a constraint is added using ALTER TABLE. SQL Server will only verify the constraint for new data added after the constraint is added. Only Foreign Key and Check constraints can be created with NOCHECK. Since Primary Key and Unique constraints build a unique index, we can’t tell SQL Server to only verify uniqueness for new data. And Default constraints don’t mean anything once the data is added.
It turns out there isn’t anyplace that tells you specifically that a constraint was added to the table with NOCHECK. But the status column in sysobjects has a bit which indicates if a constraint is untrustworthy.
Let me digress a bit. If there is a constraint on the table that says a column must be greater than 0, any query you write that asks for all rows where the column is less than 0 are really fast to execute. But only if SQL Server is absolutely sure that all the rows obey the constraint. If you added the constraint with NOCHECK and there was already data in the table, there might be data in the column with negative values. In addition, SQL Server allows you to DISABLE a constraint temporarily, and then re-enable it. So during the time a constraint was disabled, negative values could have been inserted. So if there is any chance that the data is not guaranteed to conform to the constraint’s requirement, SQL Server needs to know about it when optimizing queries referencing the column with the constraint. Any constraint which may have been violated is considered untrustworthy, and if there are any untrustworthy constraints, the optimizer can’t make any assumptions about the values in the column.
Sysobjects has a row for every constraint, as well as for every table, view, function, etc. For constraints, which really have no existence apart from the table they are part of, the sysobjects.parent_id column contains the object id of the table. In the sysobjects.status column, the 2048 bit indicates that at some point, the constraint was not valid, so there is a chance there is bad data.
Here’s an example. Create a table with 3 columns, and then add a constraint to each column, checking to see if the column is positive. Constraint c2 will be added with NOCHECK, so existing data will not be verified.
use tempdb
GO
create table test_bad_data (a int, b int, c int)
GO
alter table test_bad_data add constraint c1 check(a >0)
GO
alter table test_bad_data with nocheck
add constraint c2 check(b >0)
GO
alter table test_bad_data add constraint c3 check(a >0)
GO
This query shows all the constraints from the table:
SELECT name, status
FROM sysobjects
WHERE parent_obj = object_id('test_bad_data')
This query shows that only constraint c2 is untrustworthy:
SELECT name AS [Constraint Name],
object_name(parent_obj) as [Table Name]
FROM sysobjects
WHERE status & 2048 = 2048
Now alter the table to disable constraint c3, and then re-enable it. Because it has at one point been disabled, it is now considered untrustworthy.
ALTER TABLE test_bad_data NOCHECK constraint c3
GO
ALTER TABLE test_bad_data check constraint c3
GO
SELECT name AS [Constraint Name],
object_name(parent_obj) AS [Table Name]
FROM sysobjects
WHERE status & 2048 = 2048
SQL Server 2005 makes it easier to check for untrustworthy constraints, but not nearly so much fun. The sys.check_constraints view has a column called ‘is_not_trusted’, so if you created the above table on SQL Server 2005, you could run the following:
SELECT name, is_not_trusted,
object_name(parent_object_id) AS [Table Name],
FROM sys.check_constraints
WHERE parent_object_id = object_id('test_bad_data')
In addition, although SQL Server 2005 lets you query the sysobjects compatability view, the status column is not populated, so the only way to query the metadata for possibly untrustworthy data is to use the new catalog view.
Finally, in both SQL Server 2000 and 2005, you can actually see what data violates a constraint using a DBCC command:
DBCC CHECKCONSTRAINTS (test_bad_data)
-- Kalen