| Author |
Topic |
|
programmer76
Starting Member
6 Posts |
 Posted - 2011-03-15 : 12:16:46
|
| Gurus,I am working at a client site and all tables in their database do not have a clustered index. Instead of clustered index all the tables have a unique nonclustered index.So to explain in detail they have a table called company with only one field company_name varchar(100) and it has only one record inside it representing name of the company. company_name is the part of primary key for all the remaining tables inside the database. So for example contact table will have a unique nonclustered index on contact_id and company_name column.I believe this design is wrong and every table should have a clustered index. I am planning to change all the table design to put clustered index in it. I have three questions.(1) I want the confirmation that i'm on the right track and I should definitely change the table designs to put clustered indexes on all the tables.(2)Going ahead with the same example for the contact table I will create a clustered index on the contact_id column only. All the queries inside the database are always refering to company_name whenever there is a where clause associated with it. Tempting up on this fact I could have created clustered index on composite columns contact_id and company_name but I am not doing it because company_name is always one value and as far as i believe you are supposed to create index only on columns which have enough selectivity. Please confirm that My approach is right and I should be creating a clustered index this way.(3) After creating the clustered index in order to reduce the overhead of maintaining the index I was planning to get rid of unique nonclustered index. I believe that we don't need unique nonclustered index because clustered index will be taking care of whaterver part it was doing and there will be more overhead to maintain the not needed nonclustered index.Am i doing this right?Pleaset let me know.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-15 : 13:04:45
|
| 1. Yes you should add clustered indexes to all/most of the tables.2. I'm not clear if contact_id should be the clustered index on all of the tables. We can't answer that given the information you provided. We'd need to see the DDL for the tables as well as some sample queries. A common clustered index that the experts recommend is composite on DateTimeColumn, IdColumn. Then you'd have a non-clustered on just IdColumn, which is typically the PK.3. You won't need the non-clustered unique index on contact_id, companyname if you cluster contact_id, however your clustered index is still yet to be determined (by us at least).3.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-15 : 08:15:13
|
| Best practice or you can say a rule is , every table should have a clustered index,if you cant create the clustered index in a table that this table is a heap table and you cant take any benefit of non-clustered index too,1.Do you have primary key on this column ,if it is then it will create the Clustered Index automatically2.We cant craete index on seleted columns,Index build on the column which one is referring in the where clause and Selected columns include with this index in the non-clustered index,you cant include with clustered Index.Select C1,C2,C3 from Tab1where C2 = 3now you will create the index on C2 and C1,C3 will be include with this index3.Clustered Index does not mean Unique Index,if you create the PRIMARY KEY on a particular column then Clustered Index create automatically and uniqueness of a column is a responsibility of a Key not a clustered index,You can create a Clustered Index without uniqueness.MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-15 : 08:39:01
|
| Depends on what you aer going to do with the clustered index.I would change the company table to be company id and name, clusterd on id and use that id in the other tables rather than name.It sounds like the dtabase is using the company name to partition into several vitual databases. It might be better to have the company first in the index but that depends on the distribution of the data. Clustering on id + company name could be a bad idea as the database stands as this index will be included in all other indexes - as company name is a varchar it could make all those indexes wide and so inefficient.If you don't change to using a company id rather than name I would leave things alone or find a smaller unique value to cluster on if oyu can.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Serrano88
Starting Member
4 Posts |
Posted - 2011-05-12 : 15:12:24
|
| I certainly agree with nigelrivett that the company table should have a company_id (int) column added and that numeric id should be referenced by the child tables rather than using the company name. There are many reasons for this as nigelrivett pointed out.I would also like to add that if any of the tables in question are relatively large (millions of rows), and are updated frequently a non-clustered index might be more appropriate. When a new row is added to a clustered index the CPU essentially performs two operations: an index and a sort. A non-clustered index does not sequence the indexed data which can be desireable if a large table is updated frequently either by a daily stored procedure or by end users via a web-based application.Clustered indexes are great for tables where the data is viewed often and updated infrequently.That's my two cents anyway.__________________________________________________For SQL Contractor and Consulting Support:http://www.thedatasolutioncenter.com |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-12 : 16:12:23
|
quote:
Originally posted by Jahanzaib
Best practice or you can say a rule is , every table should have a clustered index,if you cant create the clustered index in a table that this table is a heap table and you cant take any benefit of non-clustered index too,
This is surprising to me. Recently I was reading this article http://technet.microsoft.com/en-gb/library/cc917672.aspx#EBAA. They go to great lengths to compare non-clustered index on a heap to table with clustered index. So I implicitly assumed that a non-clustered index on a heap had some benefits. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-12 : 17:43:28
|
quote:
Originally posted by sunitabeck
So I implicitly assumed that a non-clustered index on a heap had some benefits.
It does. The same benefit as on a table with a cluster.SQL is optimised for tables having clustered indexes, but that's more on the insert side. On the select side, a noncluster on a heap can actually be slightly (very, very, very slightly) more efficient than a noncluster on a table with a cluster, when it comes to key/RID lookups. It's not a good reason to leave the table a heap though. Some of the downsides (forwarding pointers among others) have a far greater negative effect on performance--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-12 : 17:44:53
|
quote:
Originally posted by Jahanzaib
if you cant create the clustered index in a table that this table is a heap table and you cant take any benefit of non-clustered index too,
That's total and complete garbage, and it's trivial to prove that you've completely wrong in this case.Please, please stop posting misinformation. I'm beginning to think you're intentionally posting garbage just to see how other people react to it.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-12 : 17:46:12
|
quote:
Originally posted by Serrano88
A non-clustered index does not sequence the indexed data which can be desireable...
All indexes are logically sorted by their keys, clustered and nonclustered alike.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-05-12 : 17:56:47
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by Jahanzaib
if you cant create the clustered index in a table that this table is a heap table and you cant take any benefit of non-clustered index too,
That's total and complete garbage, and it's trivial to prove that you've completely wrong in this case.Please, please stop posting misinformation. I'm beginning to think you're intentionally posting garbage just to see how other people react to it.--Gail ShawSQL Server MVP
I'm considering locking his account.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
clustered index