|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
an index creation question...i'm using sql2k
i have a big table with client names in it. first and last names are seperate fields. users would like to search on either first or last name, or both at the same time. what's the best way to create indexes for the best search performance? one on first name, one on last name, or a composite index on both? what the syntax would look like for creating a composite index? thank you! "=== Steve L ===" <steve.***@powells.com> wrote in message You could get away with the following two indexes:news:1121368080.759126.243620@z14g2000cwz.googlegroups.com... > i'm using sql2k > > i have a big table with client names in it. first and last names are > seperate fields. users would like to search on either first or last > name, or both at the same time. what's the best way to create indexes > for the best search performance? > one on first name, one on last name, or a composite index on both? what > the syntax would look like for creating a composite index? > > thank you! > -- Create an index on FirstName for searching on Firstname only. CREATE NONCLUSTERED INDEX IX_CustomerTable_FirstName ON CustomerTable(FirstName) -- Create a composite index for LastName, FirstName for searching on LastName only, or LastName, FirstName CREATE NONCLUSTERED INDEX IX_CustomerTable_LastName_FirstName ON CustomerTable(LastName, FirstName) HTH Rick Sawtell MCT, MCSD, MCDBA thanks a bunch!!
but how about this case: if in a book table, there are author1, author2, author3 fields (a book can have a primary author and a few 2ndary authors), when users search by last name 'smith', what would be the best indexing to search all three author fileds? I would not have the 3 seperate author columns in the book table. This is
imposing a limit on the number of authors you can handle as well as making life harder for this specific issue. Alternatively you would have a "book" table with a defined primary key (probably ISBN) , a seperate Author table, and a table to resolve the many to many relationship between them that contains required columns from both. Mike John Show quote "=== Steve L ===" <steve.***@powells.com> wrote in message news:1121369196.616583.288720@f14g2000cwb.googlegroups.com... > thanks a bunch!! > > but how about this case: > > if in a book table, there are author1, author2, author3 fields (a book > can have a primary author and a few 2ndary authors), when users search > by last name 'smith', what would be the best indexing to search all > three author fileds? > thanks for the advice about the table design. i do understand that and
the table was desinged that way for a very specific business reason, however, my question is about indexing under such circumstance, not about table design. "=== Steve L ===" <steve.***@powells.com> wrote in message If you insist on breaking normalization rules, then I would suggest you takenews:1121369196.616583.288720@f14g2000cwb.googlegroups.com... > thanks a bunch!! > > but how about this case: > > if in a book table, there are author1, author2, author3 fields (a book > can have a primary author and a few 2ndary authors), when users search > by last name 'smith', what would be the best indexing to search all > three author fileds? > a look at Full-Text-Indexes for this kind of thing. Read up on them in the Books Online. Depending on what else is in the table, it could help with queries on authors as well as book titles and ISBN numbers. (If they are all stored in that same table.). Rick Sawtell MCT, MCSD, MCDBA  
Other interesting topics
|
|||||||||||||||||||||||
