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

Create Clustered index on composite Key- script difference Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 6:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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



Post #669157
Posted Thursday, March 5, 2009 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
Edit: Misread the question. Nevermind......


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #669174
Posted Thursday, March 5, 2009 7:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.



Post #669219
Posted Thursday, March 5, 2009 7:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,123, Visits: 603
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
Post #669222
Posted Thursday, March 5, 2009 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #669251
Posted Friday, March 6, 2009 2:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.



Post #670030
Posted Friday, March 6, 2009 3:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #670032
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse