SQL Server 2008 Thesaurus alternative

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,

Who is Participating?
lofConnect With a Mentor Commented:
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


the question is in SQL Server 2005 and SQL Server 2008 zones. Which version of SQL Server are you using?
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.