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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-03-14 : 18:02:03
|
| I have a couple of columns that are frequently used, but contain narrow information:Inactive BIT -- Is this an active user or not?ClientID INT -- generally only 1 or 2 clients / databaseWhat's the impact of creating an index on a bit value, or an INT value with only 1 or 2 possible INT values?Sam |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-14 : 18:04:34
|
| From BOL:Columns that contain a large number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is usually more efficient. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-14 : 18:33:48
|
| To further extend this, you could run DBCC SHOWCONTIG on all tables to determine the scan density of the indexes. If the scan density is less than 5% or 10%, then SQL Server will most likely use the index because there are a sufficient amount of distinct values.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-14 : 20:35:57
|
| Another little story from the past.I was working for a bank (one of the 4) on a very important system. Actually my first sql server contract (6.0). They had a central database which contained all the data. This was replicated to servers at 5 regions. There was a region id field on each table and only the data for the region in question was transferred.All of the indexes had the region id as the first field and the sme indexes were used on all copies of the database.I wasn't allowed to tell anyone that this might not be optimal due to political issues (project leader felt threatened).Of course none of the indexes were used at the regional sites and the system ran like a dog - but was ok at the central site.Impressive that they could get a system with maybe 200k recs accross all tables to run that slowly.The dba team (oh yes) was due to index the tables after the development was complete by looking at the queries - anyone spot a problem with that? Anyway they said that the queries were too complex to do anything with so at least they didn't make it worse.Also had an overnight process with 3 nested cursors which would have taken about 2 weeks to run. Rewrote to take under 5 mins.Went back again 6 months later to find someone had added a cursor to the process - doh!==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
And Yet Another Index Question