Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Need help to define index
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

96 Posts

Posted - 01/13/2012 :  23:41:27  Show Profile  Reply with Quote
I've table and data as following,

declare @t1 table
(idx int primary key, nme1 varchar(200), nme2 varchar(200))

insert into @t1 values(1, 'brad pit', 'angelina jolie');
insert into @t1 values(2, 'keanu reeves', 'jennifer lopez');
insert into @t1 values(3, 'brad pit', 'jennifer aniston');
insert into @t1 values(4, 'orlando bloom', 'kiera nighley');
insert into @t1 values(5, 'keanu reeves', 'heidi klum');
insert into @t1 values(6, 'ed harris', 'maryl streep');
insert into @t1 values(7, 'keanu reeves', 'jennifer lopez');
insert into @t1 values(8, 'brad pit', 'angelina jolie');

--Let's say my SQL as following,

select distinct left(nme1,2) as nme1, nme2
from @t1

How my index looks like to make it above select statement run efficiently?

Almighty SQL Goddess

38200 Posts

Posted - 01/14/2012 :  00:13:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your query would require a scan, so it would use the clustered index or just the table.

Do you have a better example with a where clause?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server

Subscribe to my blog
Go to Top of Page

Yak Posting Veteran

96 Posts

Posted - 01/14/2012 :  06:52:02  Show Profile  Reply with Quote

how about SQL as following,

select distinct left(nme1,2) as nme1, nme2
from @t1 where nme1 like 'br%'

Edited by - Idyana on 01/14/2012 06:52:26
Go to Top of Page

Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/14/2012 :  07:33:59  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
This is SARGable and covering, but it's as large as the table is, so in a real situation I'd consider carefully before creating this.

Index on nme1, include nme2.

btw, Tara's not 'sir'.

Gail Shaw
SQL Server MVP
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/14/2012 :  07:49:47  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Consider Normalisation

You have many instances of the same person in mne1 and 2 and stored as strings. This isn't generally a good thing unless you have a very specifc purpose for the table and you need to eek out every last drop of performance ( even then it's pretty debatable)

If you had an Actor table and kept one copy of each person in there with a surrogate key (identity or whatever), then you facts table (the one you posted) would only need to contain foreign keys to the actor table. Tis would mean that of you wanted to correct a misspelling or such then you only need to change 1 row and not hundreds or thousands.

THen if you needed to do such a search then you would have an index on the actor table which can be searched (sargeable) by your like query and the index would be much smaller and have a higher level of cardinality

You'd need indices on the foreign key columns also

Msg 3903, Level 16, State 1, Line 1736

Edited by - Transact Charlie on 01/14/2012 07:55:07
Go to Top of Page

Yak Posting Veteran

96 Posts

Posted - 01/14/2012 :  21:47:00  Show Profile  Reply with Quote
tq guys
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000