Do i need here any index ???

Question

Do i need here any index ???

Bhuvnesh

SSC Guru

Points: 59351
More actions
March 26, 2010 at 6:00 am

#219825

Hi
CREATE TABLE CombineProducts
(
id int ,
dscnt_stub uniqueidentifier,
prod_name nvarchar(max),
prod_type_id smallint
)
GO
insert into CombineProducts
select 1,'C1D42B73-A457-4E8B-8AB9-7B1B90A05F04','llA,B,ddC',10
union select 2,'C1D42B73-A457-4E8B-8AB9-7B1B90A05F04','dvdA,B,C',20
union select 3,'733EB687-9AEF-45D4-9AC0-9C2BF5640258','vdB,B,dvC',30
union select 4,'733EB687-9AEF-45D4-9AC0-9C2BF5640258','Avmgv,Bvdvd,Cvdvd',40
GO
Select
ID,
dscnt_stub,
prod_type_id ,
l.item
from CombineProducts t CROSS APPLY
dbo.uf_utl_SplitNString(t.prod_name, ',') l
Above query will not contain any Where clause and function dbo.uf_utl_SplitNString gives comma separated values of prod_name column.
So can any index on CombineProducts give here any benefit ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

I don't think any index is going to help the query, but, I'd strongly suggest you identify the primary key on the table and make that a clustered index. This is more for the benefits of storage than for anything it would do for this query, which is going to result in a table scan no matter what you do.

You do know, as that table scales, this query will become slower and slower.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Grant Fritchey (3/26/2010)
You do know, as that table scales, this query will become slower and slower.

Frankly tell you this would be a intermediate/ working table ( prefebly a temp table) in Stored proc which will average contain 50 records with every execution.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

Bhuvnesh (3/26/2010)
Grant Fritchey (3/26/2010)
You do know, as that table scales, this query will become slower and slower.
Frankly tell you this would be a intermediate/ working table ( prefebly a temp table) in Stored proc which will average contain 50 records with every execution.

If it's only ever going to have 50 rows, then no, an index won't help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Thanks a lot 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150468 More actions · Answer 5

Bhuvnesh (3/26/2010)
...function dbo.uf_utl_SplitNString gives comma separated values of prod_name column.

Is that a multi-statement table-valued function, or in-line?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi