Need help to define index - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need help to define index

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-13 : 23:41:27

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-14 : 00:13:28

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
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-14 : 06:52:02

Sir,

how about SQL as following,

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-14 : 07:33:59

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-14 : 07:49:47

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

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-14 : 21:47:00

tq guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources