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


Create Clustered index on composite Key- script difference


Create Clustered index on composite Key- script difference

Author
Message
Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 590
I have a primary users lookup table. I have userID and Domain used as composit key. I have following two different ways got from two different DBAs.

Script - 1
ALTER TABLE [dbo].[tbl_cv_user] ADD CONSTRAINT [PK_tbl_cv_user] PRIMARY KEY CLUSTERED
(
[CV_User_Name] ASC,
[Domain_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]

Script -2
ALTER TABLE dbo.tbl_cv_user ADD CONSTRAINT
PK_tbl_cv_user PRIMARY KEY CLUSTERED
(
CV_User_Name,
Domain_Name
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

both are doing same thing overall, but still something not clear to me why they both have difference in script. Could you plz let me know which one to follow and which one will be best in terms of performance? we have almost 500000 records into it.

Shamshad Ali



GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231866 Visits: 46354
Edit: Misread the question. Nevermind......

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 590
I was just asking about the OPTIONS, why they are different and what options are better - I mean they should be added or NOT? does they make sence and how they help in terms of performance, good practice etc ...

Shamshad Ali.



Ronald H
Ronald H
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1678 Visits: 630
All of the changes are already default or pure textual, except for the fillfactor. And that is only used on creation of the constraint, the fillfactor won't be evaluated dynamically. Only on the fillfactor you need to decide what's the best here, depending if this table is update-intensive or not.

In general, the first statement is slightly better if many updates are made in this table. As a result, you need more storage space for this option.

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231866 Visits: 46354
50% fill factor is low. It means that half of each page is empty which means your table will take twice the space it needs and queries will do twice the IOs that would otherwise be necessary.
Fill factor that low is very seldom necessary. Can you ask the DBA who suggested that why he goes for such a low fill factor?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 590
Yes we are doing daily updates on this table, if user changes his location, Region, or any other personal information then we take that data changes and update that user profile in this table. This update would be based on change only, not whole table.

I am now confused about both of your replies. one is saying script 1 is fine, other is saying fillfactor 50 is low. what would you suggest now?


Shamshad Ali.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231866 Visits: 46354
Shamshad Ali (3/6/2009)
what would you suggest now?

That you ask the DBA who recommended that why he recommends such a low fill factor.
The main reason for something like that would be a table with a lot of inserts at random locations in the table or updates that increase the size of the row.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


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