Tunning
-
Hi All,
I have a table tab1 with col1 as varchar(100) having non clustered index on it.
Col1 just has these kind of values in it:
Col1
------------------------------------------
Employee2
Internet_proxy
Ent Std Users - Caravan2
ESP_GENERALUSERS
GAKDST3
Developers_G
SMS_WORKSTATION_P
MSDN_Developers
IRLANSEC
APPS_INF
ATION1_CG
INFORES
DL MSDN Developers
Please let me know what is the best way to make use of the index in the following sitiuation :
declare @col1 varchar(100)
Set @col1 = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'
select * from tab1
where @group_name_list like '%,' + col1 + ',%'
Thanks.
-
This was removed by the editor as SPAM
-
I don't think the above query should work ok.
Try checking the query first.
-
I agree. Check the query and then repost and we can help. One thing that you do need to understand, if you want to check for any of those values in the column you need ORs.
where col1 like '%mygroup1%'
OR col1 like '%mygroup2%',
OR ...
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks All,
The actuall query is very big, I just wanted to summarize it in this post and get some suggestions. I am sorry if I have confused anyone.
But I did find a answer to it. For that I had to use a function called ParseString which I got it from a friend of mine. Here's the function :
================================================
CREATE function dbo.ParseString (@string varchar(8000),@sep char(1) = ',')
returns @stringlist table(string varchar(100))--, Row_Cnt INT IDENTITY (1,1) primary key clustered(String, Row_Cnt) ) -- primary key clustered)
as
/*
Purpose: parse character values passed in a comma delimited string
into a table variable
Input: comma delimited string of character values
Output: result set of individual string values
*/
begin
declare @position smallint
declare @sepString varchar(10)
set @sepString = '%' + @sep + '%'
--separate individual strings using the comma delimiter and
--insert the values into the @stringlist table variable
set @position = 1
if patindex(@sepString, @string) > 0 --mulitple values in list
begin
while (@position > 0)
begin
insert into @stringlist values (rtrim(ltrim((substring(@string, 1, patindex(@sepString, @string) - 1)))))
set @string= stuff(@string, 1, patindex(@sepString, @string), '')
set @position = patindex(@sepString, @string)
end
--insert the last value from the input parameter to the table variable
insert into @stringlist values (rtrim(ltrim(@string)))
end
else
--only one value in input list
insert into @stringlist values (rtrim(ltrim(@string)))
return
end
================================================
This is what I did :
declare @col1 varchar(100)
Set @col1 = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'
select * from tab1
where col1 in (Select String From dbo.ParseString(@col1,',')
=======================================
This fixed my problem.
Thanks for your help and sorry for the confusion.
-
The other option is to use dynamic SQL
declare @col1 varchar(100)
set @sql nvarchar(1000)
Set @col1 = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'
set @sql= 'select * from tab1
where col1 IN ''' + replace(@group_name_list,',',''',''') + ''''
execute (@sql)
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply