February 7, 2008 at 1:00 pm
Hi,
I need a help from Gurus, in the below table there is a column SW_ID is an Int and IDENTITY column also which is participated in PRIMARY KEY Columns list with CLUSTERED index. Here my question is "Do we need to add an IDENTITY column (sw_id) in a PK constraint group?" If so will it hit any performance issue?
Please gimme your advise on this...
Table Description
==================
CREATE TABLE [dbo].[TestTest](
[sw_id] [int] IDENTITY(1,1) NOT NULL,
[app_id] [numeric](18, 0) NOT NULL,
[msn] [tinyint] NOT NULL,
[dash_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [bit] NULL,
[received_date] [datetime] NULL,
[received_from_dashboard] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_TestTest_received_from_dashboard] DEFAULT ('AP'),
[received_from] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[received_from_usertype] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned_to] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned_to_usertype] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_id] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_type] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [datetime] NULL CONSTRAINT [DF_TestTest_last_update_date] DEFAULT (getdate()),
CONSTRAINT [PK_TestTest] PRIMARY KEY CLUSTERED
(
[sw_id] ASC,
[app_id] ASC,
[msn] ASC,
[dash_id] ASC,
[received_from_dashboard] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
Thanks
Jay
http://www.sqldbops.com
February 7, 2008 at 1:23 pm
It seems odd to me to have an identity column be part of a primary key, as opposed to being the whole primary key by itself.
The way the table is written, all you're really doing is storing extra data in the primary key. Since the identity is the first column in the index, all data will be stored in that sequence, and all rows in the PK are unique because of the identity. That makes including the extra columns a little redundant.
It will make covering indexes a little easier to write, because they will automatically include the columns from the PK. It will also make them bigger on the drive, for the same reason.
I think the question to ask is: Do the other columns need to be in the primary key? If so, do you really need an identity column?
A surrogate key, like an identity column, is only needed if there isn't a valid natural key.
I'd either just have the identity in the PK, or not have the identity at all. One or the other.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2008 at 1:28 pm
Really thanks for your thoughts, I'll review the same and correct accordingly.
Thanks
Jay
http://www.sqldbops.com
February 7, 2008 at 2:16 pm
Usually I use the identity field in your case sw_id as primary key and build an index for the other columns - app_id, msn, dash_id, received_from_dashboard.
Also even sw_id as the primary key, it is not necessary to be cluster if it is not reference in too many tables. You can build it as non-cluster while you can build a cluster index using the other columns.
February 7, 2008 at 2:21 pm
Hi Loner,
Here i need to specify one more details, dash_id is a less selectivity column (28 rows) and total rows of the table is 98886.
Here can we add the dash_id column in PK constraint?
Advise me.
Thanks
Jay
http://www.sqldbops.com
February 7, 2008 at 2:38 pm
Actualy best practise still is using Dr. Codd's rules fo Normalization of the Data Base Relational Model. www.sqlservercentral.com/articles/Advanced/coddsrules/1208/
An identity is just an incrementing number (altough you can increment with a negative number) that would be handed out only once.
So by concept adding an identity column to other columns to make the primary key doesn't make sence !
Making it the primary key by itself on the other hand would make sence because that can be an implementation for surogate key.
You would then also need a unique constraint on one or more other columns to form the "symanic primary key" for the row.
You could then use the small (identiry) primary key to relate to other columns.
You would gain e.g. flexibility for e.g. extending/altering columns of the "symantic primary key" without affecting any of the related tables.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2008 at 6:06 pm
I agreed, you can just make dash_id as unique cluster index and leave the identity field as the PK.
Remember you can only have one cluster index in one table.
February 7, 2008 at 11:47 pm
I'll second Loner.
Clustered PK on the identity (Custered indexes should be narrow and unique)
Add a unique nonclustered index on app_idz, msn, dash_id, received_from_dashboard
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2008 at 12:36 pm
Thanks lot Loner and Gila,
After the changes done as per your suggestions, it works well.
Again thanks...
Warmest
Jai...
Thanks
Jay
http://www.sqldbops.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply