Every year I am disappointed.
As an example here is a table create statement that I saw the other day (table and column names have been changed to protect the innocent)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE [dbo].BestestTableEVAR( Col1 [int] IDENTITY(1,1) NOT NULL, Col2 [uniqueidentifier] NULL, Col3 [uniqueidentifier] NOT NULL, Col4 [smallint] NULL, Col5 [smallint] NOT NULL, Col6 [bit] NOT NULL, Col7 NULL, Col8 NULL, ColA NULL, ColB NULL, ColC [datetime2](2) NULL, ColD [datetime2](2) NULL, COlE [datetime2](2) NULL, ColF [datetime2](2) NULL, CONSTRAINT [PK_BestestTableEVAR] PRIMARY KEY CLUSTERED ( Col3 ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] |
So what’s wrong with this?
The clustered primary key on this table is a GUID. Is that bad? That’s an unequivocal YES! Read all that Kimberly Tripp (blog|twitter) has to say about GUIDs in database design.
What makes this all the more crazy is that the table has an identity column. That’s a natural clustering key ready and waiting to be used and yet for some reason it’s not.
This table is going to fragment like crazy, it won’t scale and performance will be hideous. Additionally, thanks to the XML columns this table can’t even be rebuilt online meaning there’s no way to help the fragmentation or performance without actually taking the table offline to do it, meaning it can’t handle any transactions. This is a problem in a table on an OLTP system.
I would go back and change some things. Let’s say you wanted to keep the table structure the same, that’s fine, but let’s be smart about the keys and indexes.
It would make sense to change the identity column to be clustered (I would also make this the primary key) and then, to ensure uniqueness on Col2 which is the current primary key a unique index is warranted.
Continue reading on SirSQL.net.