Re: More than one index on a table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 14:43:58 -0400

I know we hate this kind of answer, but it depends...

If there are many insert, update, deletes to this table and high insert
performance is required, then fewer indexes are good... If there are very
few insert,updates, or deletes or their performance is not as important as
select, then more indexes will be fine( Assuming you have the disk space)..

nothing but testing will give you the best answer... However SQL can use the
one compound index as long as the first column is known...

If one or two of the cases deci1,2,3 or 4 covers MOST of the queries you
might wish a separate index for that, and a multi-column index on the
rest... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <anonymous@discussions.microsoft.com> wrote in message
news:517701c47408$59ffe740$a601280a@phx.gbl...
> SQL 7.0/NT 4.0
>
> Will this work?
>
> I have a table with 100 million rows and have a unique
> clustered index on name, date which serves its purpose.
>
> structure:
> name, date, charfield, deci1, deci2, deci3, deci4, real
>
> I have another purpose that would be servered by finding
> where charfield = "SOMETHING" and deci1 =0 (that would be
> one type of search)
> But, i'd also like to search on where charfield
> = "SOMETHING" and deci2 =0
>
> I'm envisioning one compound non-clustered index of
> charfield+deci1+deci2+deci3+deci4 ?
>
> Or separate compound non-clustered indexes of
> charfield+deci1 and
> charfield+deci2
> charfield+deci3
> charfield+deci4
>
> OR is this even possible to consider? is the impact of
> having these additional indexes going to cause too much
> slowness?
>
> Any opinions?
>
> thanks,
> Don
>
>
>

Relevant Pages

  • Re: Connection to a MS SQL Server Table (newbie)
    ... > machines with IIS and MS SQL Server. ... The latter seems to defeat the purpose of a password. ... > What I am guessing at this point is that a SQL server account needs to be ...
    (microsoft.public.inetserver.asp.db)
  • Re: Modifying Primary Key Values
    ... Do you have an alternate key you can use for this purpose? ... > I send an UPDATE script to sql server. ... > How can I make the true correspondence between "new" and "old" rows? ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL SERVER
    ... SQL Server is manly used to store data in a relational way (but it's still ... > purpose. ...
    (microsoft.public.sqlserver.msde)
  • Re: Modifying Primary Key Values
    ... >I send an UPDATE script to sql server. ... >You understand that I cannot rely for that purpose on the A column /the ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)
  • Re: Email unkown when trying to add user in AD mode
    ... The problem is that I can't add them as a user in Active Directory mode. ... Wayne Snyder, MCDBA, SQL Server MVP ... > SharePoint Services). ... >> community of SQL Server professionals. ...
    (microsoft.public.sharepoint.windowsservices)