SQL Server 2008 Thesaurus alternative

Posted on 2009-12-29
Last Modified: 2012-05-08
I am currently facing a dilemma based on our SQL Server host and our business requirements.

The company would like to establish a hierachy of words in which a person can search.


If tier one was "web developer" and tier 2 was "ASP.NET" and tier three was "VB.NET" - any searches with web developer will bring results containing ASP.NET and VB.NET.  Conversely if you were to search on ASP.NET you would not get web developers but you would get VB.NET programmers.

Our host doesn't allow us to have a XML file of the Thesaurus entries so we cannot use that and the other part of this is that there are many clients in our database.  They have to only be able to search their results (which I have covered with a client ID).

Please give me any input on how we can search these tables for skillsets and have the results returned quickly (excellent execution plan) and follow the above rules.

Thanks in advance,

Question by:cyimxtck
    LVL 10

    Expert Comment


    the question is in SQL Server 2005 and SQL Server 2008 zones. Which version of SQL Server are you using?
    LVL 10

    Expert Comment

    You may use Common Table Expression for example.
    Have a look at my sample code where I am searching for any parents of a given node.

    If you are using SQL 2008 you could use new hierarchical type or if you really want something working as fast as possible, you could have additional table where you would store all the children for a given parent in a flattened way that is regardless the level.
    Such table would have two fields:

    ParentId, ChildId

    with values from my very short example you would have it populated with the following values

    ParentId, ChildId

    create table keyWords (keyWordId int identity, keyWordParent int, keyWordWord varchar(32))
    insert into keyWords values (0, 'web developer')
    insert into keyWords values (1, 'ASP.NET')
    insert into keyWords values (2, 'VB.NET')
    declare @word varchar(32) 
    set @word = 'ASP.NET';
    with hierarchy as 
    	select * from keyWords where keyWordWord = @word
    	union all 
    	select children.* from keyWords children
    	inner join hierarchy on children.keyWordParent = hierarchy.keyWordId 
    select * from hierarchy

    Open in new window

    LVL 10

    Accepted Solution

    Just in case you would like to try the approach with additional table flattening the hierarchy you could cross apply a function returning a table, containing the CTE I posted earlier to pre-populate the flat table. The attached code will work with the previous snippet

    create function GetAllChildren (@parentId int) 
    returns table
    return (
    	with hierarchy as 
    		select * from keyWords where keyWordParent = @parentId
    		union all 
    		select children.* from keyWords children
    		inner join hierarchy on children.keyWordParent = hierarchy.keyWordId 
    	select keyWordId 
    	from hierarchy
    	where keyWordId<>@parentId
    select P.keyWordId, C.keyWordId from keyWords P
    cross apply GetAllchildren(keyWordId) C

    Open in new window


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now