SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


t-sql key on a table


t-sql key on a table

Author
Message
etirem
etirem
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 517
Below is the sample sql and what is the best way to create a primary key on it?

Is it ok if a column is added as ...... [ID] [int] IDENTITY(1,1) NOT NULL and/or create a composite key?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserTable1]') AND type in (N'U'))
DROP TABLE [dbo].[UserTable1]
GO

CREATE TABLE UserTable1(Item VARCHAR(5) NOT NULL,
ItemNumber INT NOT NULL)
INSERT INTO UserTable1(Item, ItemNumber)
VALUES ('ABC11',58),
('ABC11',20),
('ABC11',58),
('ABC11',43),
('ABC11',16),
('ABC22',08),
('ABC22',53),
('ABC22',90),
('ABC22',43),
('ABC22',101),
('ABC33',43),
('ABC33',58),
('ABC33',105),
('ABC33',32),
('ABC33',63),
('ABC44',54),
('ABC44',58),
('ABC44',76),
('ABC44',99),
('ABC44',25),
('ABC55',54),
('ABC55',65),
('ABC55',90),
('ABC55',42),
('ABC55',100)

SELECT * FROM UserTable1


Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47873 Visits: 10844
For this table, it is fine if you add a column as IDENTITY (1, 1) and define it as a primary key. It'll be a 4-byte key.

It appears that the combination of the two columns is unique, so the other possibility is to define a natural key using the combination of the two columns. This would be called a composite key because it contains more than one column. There is great debate between using artificial keys and natural keys and I'm not trying to ignite a debate. Ami Levin has a chapter on the debate in "SQL Server Deep Dives Volume 2" and it covers both sides to the argument.

What matters in the decision here is the overall design of the table, the uniqueness of the columns and if the values are ever-increasing. If you add more columns to this table and add nonclustered indexes to them, all nonclustered indexes inherit the values of the clustered index, which increases the size, so you'd want the size of the clustered index to be as narrow as possible. If you add an artificial primary key and the two columns are unique, you should also consider adding a unique constraint to the two columns.

I know I've thrown out a lot of theory here, but as with so very many things, "it depends" strikes again.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47873 Visits: 10844
Better yet, Gail has written a great place to start with indexes.

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19361 Visits: 7410
Identity is a terrible idea on that table. Use the existing columns, in whichever order best matches your use of the table: either ( Item, ItemNumber ) or ( ItemNumber, Item ).

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 97
*grin* Ed did not want to ignite debate, but it was inevitable. I agree with Scott, creating a third column of meaningless data makes no sense, if a natural, meaningful key exists. If you didn't do that, you'd want to index your two columns anyhow, you're clearly going to filter or search on them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212081 Visits: 41977
Actually, it's not a terrible idea to add an IDENTITY column to this table especially if it suffers a lot of inserts. If you make the clustered index (the default for PKs) on the column pairs given, you will constantly be fighting massive page splits that could actually cause timeouts for a GUI. I agree that the PK should not be on the IDENTITY column, but the clustered index should not be on the column pair... it should be on an IDENTITY column.

[EDIT] Please see my next post below for an exception to what I've stated above.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 97
I did not know that a clustered index is the default for a PK. Doesn't a clustered index mean the entire row is written in the index ?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212081 Visits: 41977
Just to add to that, "It Depends".

If this table is a lookup table that is mostly static, leaving out an IDENTITY column and using the the two columns as both the PK and the Clustered Index would be the way to go especially for performance.

Like I said previously, if this table suffers a large number of INSERTs, then add the IDENTITY column and make it the UNIQUE Clustered Index and make the PK Non-Clustered on the other two columns.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212081 Visits: 41977
Christian Graus (1/6/2014)
I did not know that a clustered index is the default for a PK. Doesn't a clustered index mean the entire row is written in the index ?


To be semantically correct, it means that the rows of the table form the leaf level of the Clustered Index and a B-Tree is built over the top of it. That's why a Clustered Index Scan is actually the same as a Table Scan except that a Table Scan can only happen on HEAPs because there is no Clustered Index on those.

And, yeah... if you assign a Primary Key without explicitly assigning it as a Non-Clustered index and no Clustered Index has yet been assigned, the PK will be created as a UNIQUE Clustered Index along with the special PK meaning of "NOT NULL".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 97
Thanks - good to know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search