Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flanz_g
Starting Member
22 Posts |
 Posted - 2006-12-03 : 16:24:48
|
Hi,Very new to this technology I have a small question: Say I create a Clustered Index Column on a table e.g.:"CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndexON DummyTable2 (EmpID)GO"..if I sunbsequently append k's more rows of data every week, is there any additional comand I have to run to make the C.Index "work" on the new Data rows, or will it work on ALL Data (old and new) automatically?Thank you for your help,Gezza  |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-03 : 16:58:51
|
| It will continue to work.Eventually it will get fragmented and less efficient.You may run DBCC DBREINDEX or DBCC INDEXDEFRAG to clear up the fragmentation.To see if the index might need som refreshing run DBCC SHOWCONTIG.But SQL Server really does a good job of maintining index statistics (not really the same as fragmentation.. but still),and it is not something you need to loose too much sleep on.Even a badly fragmented index is likely to be much better than none at all.rockmoose |
 |
|
|
flanz_g
Starting Member
22 Posts |
Posted - 2006-12-04 : 15:43:55
|
| Thank you for this rockmoose,And also is it likley to be an improvement if I create 2 Cluster Indexes instead of 1 on the same Table, if Each Key Name has 24 rows of stats for each Day, any many Days will be queried on several Names at once in each query? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-04 : 16:15:18
|
| You can only create one clustered index per table. Index design is very important, in most cases you need to have indexes on columns that appear in the WHERE clause of any query, but you can of course have multiple columns in one index so how many indexes to create will depend on how columns are used together. I'd recommend the Inside SQL Server book series as good reading on the topic.You should start by running the Index Tuning Wizard (SS 2000) or the Database Engine Tuning Advisor (SS 2005) as they will recommend most of the indexes you need, then you can possibly do some fine tuning later. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-04 : 16:18:02
|
| You can only have one CLUSTERED index on a table.But you may have several NONCLUSTERED indexes.In general it is good if the clustered index is also UNIQUE.Also an index (clustered or nonclustered) can include several columns.Try some! Google some!www.sqlserverperformance.com should have some good articles on index guidelines.or: http://msdn2.microsoft.com/en-us/library/ms190804.aspxPost the table structure, and a sample query, that way we can advice more.rockmoose |
|
|
|
flanz_g
Starting Member
22 Posts |
Posted - 2006-12-05 : 06:41:13
|
| Mighty Thanks! - off to digest your answers! |
|
|
|
|
|
|
|
|
Question on Clustered Index