Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Timothy A Wiseman » Finding Values With Numerous Columns
Add to briefcase
27 posts, Page 1 of 3
123»»»

Finding Values With Numerous Columns Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
timothyawiseman
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: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900
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
Anipaul
Posted Tuesday, December 02, 2008 2:13 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785, Visits: 1,334
Good one ...


Post #611883
nvanesch
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
Phil Factor
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: Today @ 5:02 AM
Points: 533, Visits: 2,284
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
Jeff Moden
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: Today @ 7:58 PM
Points: 32,893, Visits: 26,764
... 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #611978
tskelley
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: Yesterday @ 6:34 AM
Points: 33, Visits: 758
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
Paul DB
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: Tuesday, May 14, 2013 12:07 PM
Points: 60, Visits: 257
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
Phil Factor
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: Today @ 5:02 AM
Points: 533, Visits: 2,284
... 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
Madhivanan-208264
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, September 12, 2012 5:17 AM
Points: 329, Visits: 461
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
Jason Akin
Posted Tuesday, December 02, 2008 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 9:13 AM
Points: 24, Visits: 70
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
27 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.