Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

t-sql key on a table Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 9:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:51 PM
Points: 74, Visits: 391
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

Post #1527630
Posted Friday, January 3, 2014 11:34 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 4,050, Visits: 3,488
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
Post #1527671
Posted Friday, January 3, 2014 11:38 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 4,050, Visits: 3,488
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
Post #1527674
Posted Monday, January 6, 2014 3:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,121, Visits: 3,210
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1528278
Posted Monday, January 6, 2014 7:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, 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.
Post #1528301
Posted Monday, January 6, 2014 9:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1528331
Posted Monday, January 6, 2014 10:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, 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 ?
Post #1528335
Posted Monday, January 6, 2014 10:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1528336
Posted Monday, January 6, 2014 10:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1528337
Posted Monday, January 6, 2014 10:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
Thanks - good to know.
Post #1528338
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse