Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Finding Values With Numerous Columns Expand / Collapse
Author
Message
Posted Monday, December 01, 2008 11:07 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
Comments posted to this topic are about the item Finding Values With Numerous Columns

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #611821
Posted Tuesday, December 02, 2008 2:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,191, Visits: 1,368
Good one ...


Post #611883
Posted Tuesday, December 02, 2008 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 08, 2012 1:26 AM
Points: 3, Visits: 17
If you are happy to exclude index performance benefits or doing a like comparison you can create a computed column of the concatenation of other columns and then search the computed column.

ALTER TABLE phonebook ADD
phoneall AS ',' + isnull(phone1, '') + ',' +
isnull(phone2, '') + ',' +
isnull(workphone, '') + ',' +
isnull(cellphone, '') + ','

SELECT 
*
FROM
phonebook
WHERE
phoneall like '%,234-5678,%'

Post #611891
Posted Tuesday, December 02, 2008 2:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 561, Visits: 2,415
This is an interesting article. What is most important is that it explains WHY there can be a problem, for anyone who has never been in a position of trying unsuccessfully to curb developers who possess just a little knowledge of Database Design but who are completely unaware of just how little.

As far as I can see, the solution that Timothy shows will work well in cases where the database has a lot of small spreadsheet-like tables, (Timothy seems to have suffered from this) but it will not work for monster tables unless it has some very clever indexing.

There is a different solution that works very well, which I've had to use myself many times, though it is not a good idea for a rapidly changing table. It is, however, very fast where there is lots of text to search in several columns, which would otherwise need a '%xxxx%' wWHERE-clause. (i.e. unindexable!) This is to use an 'inversion' table.

this technique is usually called the ‘Inverted’ or ‘Inversion’ index technique. (see http://en.wikipedia.org/wiki/Index_(search_engine) for a full discussion.

Basically, you produce table that contains, uniquely, a list of every word in the columns you want to index. You maintain a many-to-many linking table that links the row in the denormalized table with the words in the unique table that it contains. This gives you, instantly, the rows containing the words in the 'search string' that you want to search for, even in tables that are several million rows long. You can refine your search from that subset, and Timothy's method should work fine for that. I've never tried to make this into a generic solution, as I'm not that brave, nor unfortunate enough to have more than one travesty of a denormalized table like the ones Timothy describes, within a single database!

If anyone is interested, I'll pop the solution into a blog post, but it is too long for a forum post, I reckon..



Best wishes,

Phil Factor
Simple Talk
Post #611909
Posted Tuesday, December 02, 2008 5:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
... or a nice article on SSC, Phil. ;)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #611978
Posted Tuesday, December 02, 2008 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:38 AM
Points: 39, Visits: 875
Thanks for the article as this does come up, especially when dealing with others' legacy databases. I would be interested in seeing this expanded to include text fields, as so often a varchar is used to capture field data, then it overspills into a catch-all notes field.



Post #611995
Posted Tuesday, December 02, 2008 6:31 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 09, 2013 1:26 PM
Points: 60, Visits: 258
Jeff Moden (12/2/2008)
... or a nice article on SSC, Phil. ;)


Speaking for the vast silent majority, I'd like to read Phil's article. :D


Paul DB
Post #612007
Posted Tuesday, December 02, 2008 6:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 561, Visits: 2,415
... or a nice article on SSC, Phil. [Wink]

Speaking for the vast silent majority, I'd like to read Phil's article. [BigGrin]


OK. It's a deal. I needed a good excuse to publish it! :)



Best wishes,

Phil Factor
Simple Talk
Post #612016
Posted Tuesday, December 02, 2008 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
Just want to point out that ISNUMERIC and ISDATE is not reliable

SELECT ISNUMERIC('12d5'),ISDATE('2000')




Madhivanan

Failing to plan is Planning to fail
Post #612043
Posted Tuesday, December 02, 2008 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 12, 2014 8:12 AM
Points: 24, Visits: 73
This doesn't work for a LIKE condition, but for equalities in a small number of fields, you can use

WHERE '411-555-1212' IN (HomePhone, WorkPhone, CellPhone, AltPhone)

I've found it useful when looking for a foreign key of a Person Id in several fields like fkLoanOfficerId, fkApprovingOfficerId, fkVerifiedBy etc.

You can't let the perfect be the enemy of the good. In some cases it's more expedient to work with the errors of the past, than to attempt a total rewrite of the structure.
Post #612077
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse