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 |
|
dextrous
Starting Member
15 Posts |
 Posted - 2009-07-31 : 00:13:10
|
| Hello,I have been trying to educate myself about indexes as this is all quite new to me.1. Cluster index seems like it is the best way to go for most tables. Is there ever a reason not to use it?2. Does having a unique cluster increase the performance of queries even more?3. I have a table with county names and state. Within each state, the names are unique, but not within the whole column. Currently I have an index on just county name. Would it make more sense to create a "composite index" (i just ran into it on google!) for this structure?4. Once I make an index on a column, when i query it, do I need to refer to the index's name at all--I read something about this and was confused.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dextrous
Starting Member
15 Posts |
Posted - 2009-07-31 : 02:39:35
|
| Would it be a lot more efficient to have columns like these:County, States County Found inHays, TX-FL-ALthanCounty,StateHays, TXHays, FLHays, ALWould query performance improve a lot to use unique cluster index and use the first format?I'm querying the county name repeatedly in the application to get the state.The only problem is that Hays, TX has its own unique record (lets say population, etc.) attached with it...not sure about the optimum db design! |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-31 : 14:21:04
|
| probably more efficient like thisCounty,StateHays, TXHays, FLHays, ALput unique index over county/stateEven more efficient to use zip code. |
|
|
|
dextrous
Starting Member
15 Posts |
Posted - 2009-08-03 : 23:42:41
|
| One thing I cannot figure out is how do I create unique over two columns?So, Hays,TX is of course unique...I tried selecting cluster and added both column and then selected unique. But it gives an error message saying "Hays is not unique".Thanks a lot. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-04 : 17:50:46
|
| [code]Create UNIQUE CLUSTERED INDEX on tableName (county, state)[/code] |
|
|
|
dextrous
Starting Member
15 Posts |
Posted - 2009-08-04 : 22:28:53
|
| error...Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'on'. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Indexing basics