Intro:
Reading Time: ~1 minutes
Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.
select name, is_not_trusted from sys.check_constraints; select name, is_not_trusted from sys.foreign_keys;
In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.
However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.
All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.
This, in turn, got me wondering; how well do you really know your sys tables?
If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?
Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.
Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?
Test yourself:
The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.
In the dialog box, Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:
- Auto list members
- Parameter information
If you uncheck these two checkboxes, your intellisense is gone!
Try it out, even for 5-10 minutes.
Hopefully, with your Intellisense gone, your sense will remain.