April 2, 2014 at 10:08 am
Hi All,
I came across a curious problem that I've never encountered before and thought I'd ask the SQLServerCentral Brain Trust for some thoughts.
I have a table named Transmissions as follows:
TransmissionId UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_Transmissions PRIMARY KEY CLUSTERED,
SubmitDate DATETIME NOT NULL
<bunch of other columns>
There is another unique index defined on this table on the TransmissionId column only. Let's call this index IX_TransmissionId.
There are also several other tables that have a TransmissionID column as foreign key referencing the Transmissions.TransmissionId column.
This table has heavy inserts and deletes and we've been having performance problems due to clustered index being on a GUID (fragmentation is always 99%).
My goal is to reorganize the indexes and make the PK on the TransmissionId column non-clustered and put the clustered index on the SubmitDate column. However, when I tried to drop the IX_TransmissionId, I got the following error:
"An explicit DROP INDEX is not allowed on index 'IX_TransmissionId'. It is being used for FOREIGN KEY constraint enforcement."
I thought that the PK is what enforces the uniqueness and would be used for FK enforcement. I'm a bit confused as to what's happening here. Why is the DB engine preferring to use the IX_TransmissionId index for FK enforement versus the PK?
Any thoughts would be appreciated.
Note: This is a third party DB, so any major schema changes are not an option. The only avenue of performance improvement I have is through better indexing (and trying to reduce the page splits & fragmentation).
Thanks,
Peter
April 2, 2014 at 10:33 am
like the error is telling you, because there are foreign keys in place, in this case you'll need to drop and recreate the foreign keys, i think; you can find them pretty easily with exec sp_fkeys TableName
i would suggest changing the data type from GUID to use the newsequentialid function:
CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())
you'd avoid the fragmentation issue that plain old guids suffer from.
Lowell
April 2, 2014 at 11:03 am
You can use a unique index as a foreign key too. It doesn't have to only be the primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 11:38 am
Thank you for the motivation. I have been meaning to finish my article about using guids as primary keys. I have been swamped at work lately but I have also seen a large amount of posts lately where people are doing this. You have inspired me to get this article completed to hopefully help other people not make the same design mistake of having a guid as the clustered index on a large and busy table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2014 at 12:12 pm
Thanks folks for the replies. However, my question is *why* did SQL Server elect to use the unique index defined on the TransmissionId column versus using the Primary Key (defined on the TransmissionId)?
When a foreign key is defined in a child table against some column in the parent table, SQL Server must look at the data in the parent table to ensure that the column in question is unique. In the event that there is more than one unique index on the referenced column, how does SQL server determine which index to use to enforce this?
Thanks,
Peter
April 2, 2014 at 12:33 pm
It wouldn't "elect" to. Some script at some point had to declare that it should.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 1:27 pm
AZ Pete (4/2/2014)
Thanks folks for the replies. However, my question is *why* did SQL Server elect to use the unique index defined on the TransmissionId column versus using the Primary Key (defined on the TransmissionId)?When a foreign key is defined in a child table against some column in the parent table, SQL Server must look at the data in the parent table to ensure that the column in question is unique. In the event that there is more than one unique index on the referenced column, how does SQL server determine which index to use to enforce this?
Thanks,
Peter
All SQL needs to enforce the FK constraint is a unique index. Since a non-clustered index would be vastly more efficient for that process, I would expect SQL to use it if one exists.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply