June 30, 2004 at 9:40 am
Hi folks, i am a bit confused about implementing indexes: I've just gone through the article:
http://www.sqlservercentral.com/columnists/lPeysakhovich/indexcreationguidelines.asp
It's said of that an indexes shall not be created on FREE-FORM text columns (varchar usually). Some bad examples in this context are Flag column and SSN column.
OK. I am in agreement of Flag column not to use indexes for it because optimizer would ignore the index if corresponding column doesn't contain 80-85% unique values. What about SSN. It shall be unique with in the column and isn't it a good candidate for index? Why VARCHAR columns are not recommended for indexes.
Another question: If i have a composite index on LAST_NAME and FIRST_NAME column. When i query the table specify only FIRST_NAME in WHERE clause, shall that index be used by the optimizer.
Thanx in advance.
__________________________________
A DBA works only when the users can't, so i scarcely work!
June 30, 2004 at 12:02 pm
Actually SSN will be fine but should be CHAR not VARCHAR as it is fixed in length and format.
It will in many cases be but will be an index scan instead of seek for sure.
July 1, 2004 at 8:48 am
My rules of thumb are
- make sure your primary key, unique indexes are in place. That of course is automatic.
- create indexes for all foreign key columns.
- have the developers give you the most frequently used Select statements and make sure you have indexes on the Where predicate columns.
- when the application is in testing (and again when it's in production) capture a "workload" with Profiler and run the Index Tuning wizard against the workload to see if it recommends any additional indexes.
July 1, 2004 at 9:07 am
Thanx!
What about my 2nd question:
"If i have a composite index on LAST_NAME and FIRST_NAME column. When i query the table specify only FIRST_NAME in WHERE clause, shall that index be used by the optimizer.
"
Howdy!
__________________________________
A DBA works only when the users can't, so i scarcely work!
July 1, 2004 at 9:35 am
The optimizer will almost surely not use the last_name, first_name index, if you query on first_name only. If you will frequently query on first_name only, you should create another index where first_name is the first column.
You can confirm that by running the query in Query Analyzer and looking at the 'Display Estimated Execution Plan', to see which, if any, index is used.
July 2, 2004 at 7:35 am
I have a general question to add to the mix.
I have worked on 10+ database systems in the past and for all the general rule is never build composite keys using variable length fields.
For instance, if we have FirstName varchar(30) and LastName varchar(30) and build an index on FirstName+LastName, you get the following problem: The two names
'ANN EPSON' and 'ANNE ROBERTS'
would get stored in the index as keys: 'ANNEPSON' and 'ANNEROBERTS'
when searching for a firstname of 'ANNE' the index would find both records. The solution is to convert the fields to fixed length in the index build statement. In other words you build the index key on CONVERT(CHAR(30), Firstname) + CONVERT(CHAR(30), Lastname) though it isn't strictly necessary to convert the last field in the composite key to be fixed length.
In SQLServer the list of fields is passed as a comma separated list to the CREATE Index command. So...the question is:
is SQLServer automatically converting variable length fields to fixed length fields in composite indexes? ie two varchar (250) fields create an index key that is 500 characters long, where the first field is padded out with spaces to 250 characters. If so, the answer to 'thebeginer's original question is it doesn't matter if the fields are varchar, as SQLServer is treating them as fixed length, except that whenever it searches the index it has to convert the value being searched for to a fixed length value by padding it with spaces(which is a small overhead).
Sorry about the rambling. Just want to be sure I understand what is going on.
Peter
July 2, 2004 at 7:49 am
Interesting; Thanx for ur participation, Peter.
Guys!!!?
__________________________________
A DBA works only when the users can't, so i scarcely work!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply