Design Errors : PK's with multiple columns on table with identity
-
Comments posted to this topic are about the item Design Errors : PK's with multiple columns on table with identity
-
This is a handy script, however you are only looking at clustered primary keys. I modified the script to remove the filter based on index_id as you can also get non-clustered primary keys. I also included unique indexes and unique constraints as these also make no sense when an identity column is a key. Lastly I added a filter to ignore included columns for the unique non-clustered indexes as we only need to count the key columns.
Thanks for submitting this, it has been added to my toolbox.
WHERE(i.is_primary_key = 1
OR i.is_unique = 1
OR i.is_unique_constraint = 1)
AND ic.is_included_column = 0
-
Actually, in testing I discovered it does not report the correct data. This will find tables that have a unique index with multiple columns as well as another index with an identity column. One needs to join to index_columns twice. The first join to identify unique indexes with an identity column and the second join to count the number of columns.
Here is my updated version.
SELECTOBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
CASE
WHEN i.is_primary_key = 1 THEN 'Primary Key'
WHEN i.is_unique_constraint = 1 THEN 'Unique Constraint'
ELSE 'Unique Index'
END AS IndexType,
id.name AS IdentityColumn,
COUNT(*) AS NumberOfKeyColumns
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN
sys.columns id
ON i.object_id = id.object_id
AND id.column_id = ic.column_id
INNER JOIN
sys.index_columns ic_cnt
ON i.object_id = ic_cnt.object_id
AND i.index_id = ic_cnt.index_id
WHERE
(i.is_primary_key = 1
OR i.is_unique = 1
OR i.is_unique_constraint = 1)
AND ic.is_included_column = 0
AND id.is_identity = 1
GROUP BY
OBJECT_NAME(i.object_id),
i.name,
id.name,
CASE
WHEN i.is_primary_key = 1 THEN 'Primary Key'
WHEN i.is_unique_constraint = 1 THEN 'Unique Constraint'
ELSE 'Unique Index'
END
HAVING
COUNT(*) > 1;
Here is the test I created to identify the issue in the original script.
CREATE TABLE MyTest(ID INT IDENTITY(1, 1),
KeyVal1 INT,
KeyVal2 INT,
CONSTRAINT pkTest PRIMARY KEY (KeyVal1, KeyVal2));
CREATE NONCLUSTERED INDEX ixtest ON MyTest (ID);
-
Sean Pearce (11/24/2015)
Actually, in testing I discovered it does not report the correct data.[/code]Thanks for the updates to the script.
-
Sean,
I created the test table but when I ran your version of Doran script the ONLY data returned was for the queue_messages_xxxxxxxx internal tables with 'Unique Index' shown for the IndexType. Running Doran's query DID return a row for the MyTest table. I tried this in SQL 2008, 2008R2, and 2012.
Lee
-
Lee Linares (11/24/2015)
Sean,I created the test table but when I ran your version of Doran script the ONLY data returned was for the queue_messages_xxxxxxxx internal tables with 'Unique Index' shown for the IndexType. Running Doran's query DID return a row for the MyTest table. I tried this in SQL 2008, 2008R2, and 2012.
Lee
That is correct. That table is not supposed to be returned. The unique index has multiple keys but not one of the keys is an identity, which is what we are trying to find.
-
Dooh! Brain Cramp on my part. My bad. Thanks.
Lee
-
I ran the original version of the script & found a table that had an identity column, but the clustered PK was actually two other columns :w00t: so thanks Doran.
-
The bigger design error is likely that you have PK identities when the table contains a valid data key. Identities have become a crutch and are vastly over-used. Yes, identities have their place, and are irreplaceable for some things -- such as customer# or order# -- but tend to be over-used and overly relied upon.
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".
-
There are legitimate cases for using identity as a later key.
For example, in an OrderItems table, I might want to key/cluster the table on ( OrderNumber, $IDENTITY ) in that order. That allows items for the same order to be grouped, and to easily be displayed in the order in which they were entered.
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".
-
Thanks Scott, Will, Lee, Sean and BornReady for the effort in testing, critiquing and optimizing.
Some good ideas here and I will adapt the script as soon as.
The specific scenario I was looking for is as per below
--example1 maintains a one to one relationship between ID and sessionID
--i.e. a value is unique in sessionid column and that value is linked to
--only one value from ID.
CREATE TABLE TestTable
(
ID INT IDENTITY(1, 1)
, sessionid UNIQUEIDENTIFIER
, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ( ID ASC ) ON [PRIMARY]
);
ALTER TABLE TestTable ADD CONSTRAINT CI_Sessionid UNIQUE( Sessionid);
GO
Is not the same as
--example2 Uniqueness of Sessionid or ID is not guaranteed just by including both columns in the primary key.
--Identity insert could result in duplicate values for ID as long as the sessionid has not
-- been used for the same ID before.
CREATE TABLE TestTable
(
ID INT IDENTITY(1, 1)
, sessionid UNIQUEIDENTIFIER
, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ( ID ASC, sessionid ASC) ON [PRIMARY]
);
And we would need to ask questions and do investigation around example2.
-
I have added the changes suggested, specifically excluding where the identity is included explicitly in indexes, as well as only returning indexes which contain the identity column.
It seems like the changes submitted are being created as a new post instead of updating the original.
Also added 8 test cases for table setups to test against.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply