question

How about making use of a full text index (and a little hacking)? Step 1: Concatenate your column values to a single column in the table. Step 2: Create a full text index (FTI) on this column. Step 3: Use a search term and the FTI to return a set of keys for the rows that contain the term. Step 4: Pass the keys to an UNPIVOT query to isolate the matches. You should probably have a think about what to do with rows with multiple matches. Example code below. Note that I'm working on a server without FTI installed, so didn't physically test the FTI bits, but they're probably there or thereabouts. Links at bottom might be of interest. ----------------- -- create a table ----------------- create table dbo.MyTable ( MyId int identity , Col1 varchar(20) , Col2 varchar(20) , Col3 varchar(20) , Col4 varchar(20) , Col5 varchar(20) , Col6 varchar(20) , MyConcat varchar(250) ) go alter table dbo.MyTable add constraint pk_MyTable primary key clustered (MyId) go -- add some data insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('Mary','Had','A','Little','Lamb',null) insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('It''s','Fleece','Was','White','As','Snow') insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('And','Everywhere','That','Mary','Went',null) insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('That','Lamb','Was','Sure','To','Follow') --------------------------- -- concatenate data columns --------------------------- -- null values might spoil the party set concat_null_yields_null off update dbo.MyTable set MyConcat = Col1 + ' ' + Col2 + ' ' + Col3 + ' ' + Col4 + ' ' + Col5 + ' ' + Col6 -- show data select * from dbo.MyTable ------------- -- create FTI ------------- -- note - i'm working on a server without FTI installed, so we'll just have to spoof the results later -- we need a catalog first create fulltext catalog ft as default; go -- create the index create fulltext index on dbo.MyTable(MyConcat) key index pk_MyTable go ------------ -- query FTI ------------ -- pass the keys returned to a temp table to use later create table #MyKeys (MyId int) -- always use nvarchar types with FTI for performance reasons declare @MySearchTerm nvarchar(20) set @MySearchTerm = 'Mary' insert #MyKeys select MyId from dbo.MyTable where contains(MyConcat,@MySearchTerm) -- this is the bit where i fake a resultset... insert #MyKeys values (1) insert #MyKeys values (3) -- index up create clustered index ix_tmp on #MyKeys(MyId) --------------------------- -- return items of interest --------------------------- declare @MySearchTerm nvarchar(20) set @MySearchTerm = 'Mary' select * from ( select mytb.MyId, Col1, Col2, Col3, Col4, Col5, Col6 from dbo.MyTable mytb join #MyKeys myks on mytb.MyId = myks.MyId ) src unpivot ( TermToSearch for TermFoundIn in ( Col1, Col2, Col3, Col4, Col5, Col6 ) ) unpvt where TermToSearch = @MySearchTerm http://msdn.microsoft.com/en-us/library/ms142571.aspx http://msdn.microsoft.com/en-us/library/ms142583.aspx Edit: Carriage returns and stuff...