Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
103

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Google Ads

Suggestion: Check for 'not trusted' check constraints

Please wait...

Suggestion: Check for 'not trusted' check constraints

Postby PhilFactor on Tue Jan 31, 2012 7:29 pm

If you've done some bulk imports, the chances are you'll have some check constraints that are left as 'not trusted' when they were re-enabled, or even worse are still disabled. These can't be used by the optimiser so you'll be getting performance hits or perplexing query plans that seem stubbornly poor.

SELECT sys.objects.name AS [Table Name],
sys.check_constraints.name AS [Constraint],
is_not_trusted,
is_disabled
FROM sys.objects
INNER JOIN sys.check_constraints
ON sys.check_constraints.parent_object_ID = sys.objects.object_ID
WHERE sys.check_constraints.is_not_trusted = 1
OR sys.check_constraints.is_disabled = 1
ORDER BY sys.objects.name, sys.check_constraints.name

…or on old versions of SQL Server…

SELECT name,
OBJECTPROPERTY(id,'CnstIsNotTrusted') as is_not_trusted
FROM sysobjects
WHERE COALESCE(OBJECTPROPERTY(id,'CnstIsNotTrusted'),0)<>0
PhilFactor
Apprentice
Apprentice
LTD Bronze - Rating: 4
 
Posts: 5
Joined: Thu Jan 15, 2009 10:28 am
Unrated