One of the things I’ve noticed while performance tuning is many of the tables in my current environment are incomplete. They all have columns defined, and they all have data, but they’re still missing something.
Many are missing a primary key. You don’t always have to have a primary key, but when you’re joining against this table day in and day out, you would do well to have a primary key on the column or columns you’re joining on. If not that, then at least declare a unique constraint. That way you won’t get duplicates when you join to that table.
But then we find something else is missing. Those same tables are also missing a unique constraint. Well, other than the dupe problems this isn’t a huge problem unless you’ve written indexes on these tables and there isn’t a clustered index on the table.
…Don’t tell me. That’s missing too?
Ok, time to dig into this problem and start working some magic. Let’s see how many tables are missing primary keys.
1: SELECT soT.name
2: FROM sys.objects soT
3: LEFT JOIN sys.objects soPK
4: ON soT.object_id = soPK.parent_object_id
5: AND soPK.[type] = 'PK' -- Primary Key
6: WHERE
7: soT.is_ms_shipped = 0 -- not from Microsoft
8: AND soT.[type] = 'U' -- User Table
9: AND soPK.object_id IS NULL
10: ORDER BY
11: soT.name
This query looks in the sys.objects table. Check out the books on line article for more details. But basically this system view lets you see information about all the objects created within a database. That means you have to run this in a specific database. I suggest AdventureWorks if you’re working on a test instance of SQL 2005.
I want you to notice I’ve limited my results to only object created by users (is_ms_shipped = 0), and I only wanted to see the tables that are missing a primary key. Let’s not get into indexing views at this point.
When you run this query you could take the list of tables, and then manually inspect them in your copy of SQL Server Management Studio (SSMS), but rather than doing that, let’s use another query, to look to see how many of these tables are also missing a unique constraint.
1: SELECT soT.name
2: FROM sys.objects soT
3: LEFT JOIN sys.objects soPK
4: ON soT.object_id = soPK.parent_object_id
5: AND soPK.[type] = 'PK' -- primary key
6: LEFT JOIN sys.objects soUQ
7: ON soT.object_id = soUQ.parent_object_id
8: AND soUQ.[type] = 'UQ' -- unique constraint
9: WHERE
10: soT.is_ms_shipped = 0 -- not from microsoft
11: AND soT.[type] = 'U' -- user tables only
12: AND soPK.object_id IS NULL
13: AND soUQ.object_id IS NULL
14: ORDER BY
15: soT.name
In my results, I found that all the tables returned by the first query were also returned by the second query. That means there is no way defined to uniquely identify a single row. That’s a problem. What I would do at this point is talk to the developers using these tables, If there were a DBA, I’d also invite him or her into a conversation.
I’d want to see if there is anything about the records in these tables that would uniquely identify each row to the table. If there isn’t I’d suggest an IDENTITY column for the tables. I would then mark that as my Primary Key. Before adding it to the table, I would definitely talk through the impact of this change with the others. Everyone needs to be on board with this change. Even though it will make some things better, if the application isn’t ready for this change…bad things could happen.
But before I could add this column as a Primary Key, I’d want to make sure these tables didn’t already have a clustered index. If they did have a clustered index, I’d ask if we could make that the Primary Key. The following query would show me those tables that don’t have a Primary Key, a Unique Constraint, or a Clustered Index. The following query uses sys.indexes. If you want more information about this query, please see the books on line.
1: SELECT soT.name
2: FROM sys.objects soT
3: LEFT JOIN sys.objects soPK
4: ON soT.object_id = soPK.parent_object_id
5: AND soPK.[type] = 'PK' --primary key
6: LEFT JOIN sys.objects soUQ
7: ON soT.object_id = soUQ.parent_object_id
8: AND soUQ.[type] = 'UQ' --unique constraint
9: LEFT JOIN sys.indexes si
10: ON soT.object_ID = si.object_ID
11: AND si.[type] = 1 --clustered index
12: WHERE
13: soT.is_ms_shipped = 0 -- not from Microsoft
14: AND soT.[type] = 'U' -- a user table
15: AND soPK.object_id IS NULL
16: AND soUQ.object_id IS NULL
17: AND si.object_id IS NULL
18: ORDER BY
19: soT.name
Those in this final list would need to be treated first. Especially if they are high transaction volume tables. By completing these tables you could see tremendous gains in performance. I will put out one warning here…If these tables are written to frequently but never queried from… you could ignore them… Adding indexes could slow down the write with no real benefit.
Make sure you are reading from these tables before you go adding indexes, especially clustered indexes.
Hopefully this helps you out when you start looking at the state of your databases. If you have any questions, please let me know!