SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamic Concat ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ZMike
Posting Yak Master

110 Posts

Posted - 03/11/2013 :  16:03:08  Show Profile  Reply with Quote
I wanted to see if there is a way to dynamically concatenate all of the fields in my table so i can use it for a full text search.

I have a table that is quite large column wise,

SELECT
ReportID, ReportServer, ReportName
, SQLQuery1 Thru SQLQuery49
, SQLFields1 Thru SQLFields379

FROM A

I wanted to get the data down to the following

ReportID, ReportServer, ReportName , Search (Which is all of the queries and fields)

Since depending on what is active, this number could fluctuate a lot and obviously I don't want to Copy Paste that many times hard coded


robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 03/11/2013 :  17:24:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
Two questions:

1. Why are you storing SQLQuery data as multiple columns (denormalized), rather than 1 column with 1 row for each entry (normalized)?
2. If you can't change that structure, why not just add all the columns to the fulltext index? You can specify a full column list in CONTAINS & FREETEXT predicates.
Go to Top of Page

ZMike
Posting Yak Master

110 Posts

Posted - 03/11/2013 :  21:11:06  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Two questions:

1. Why are you storing SQLQuery data as multiple columns (denormalized), rather than 1 column with 1 row for each entry (normalized)?
2. If you can't change that structure, why not just add all the columns to the fulltext index? You can specify a full column list in CONTAINS & FREETEXT predicates.



1. I have a more normalized table currently with a full text search on. I am still very new to full text search so if you have something in mind could you elaborate a bit more ?

The situation on the normalized table is I'm running data against 6 SSRS databases (and counting) and pulling all of the queries behind them. I have found that we have several extremely large reports (49 queries is the largest). That's within a whole separate issue that I'll have to check into.

So on that 1 report if I did a full text search I would get 49 record hits for that 1 report. I only want to get 1 hit per Report if at all possible. I figured if I could get them in more of a pivoted format then maybe I could roll up the search that way and then I would end up with 1 record for that full text search.

2. How do you add all the columns to the fulltext via tsql ? Some of the examples that I found online were taking each field and doing something like this :

SELECT DISTINCT
ReportID, ReportServer, ReportName
, Field1 + Field2 + Field3 AS Search

FROM A


and then referencing that in the search. If there is a better way for my situation would you be so kind as to elaborate a bit more ? I'm sure I'm probably making this harder than it really should be.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 03/12/2013 :  11:23:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Proof of concept to support option 2:
CREATE TABLE FTS(ID INT NOT NULL IDENTITY(1,1), CONSTRAINT PK_FTS PRIMARY KEY(ID),
col1 VARCHAR(256),
col2 VARCHAR(256),
col3 VARCHAR(256),
col4 VARCHAR(256),
col5 VARCHAR(256),
col6 VARCHAR(256),
col7 VARCHAR(256),
col8 VARCHAR(256),
col9 VARCHAR(256));

INSERT FTS(col1) VALUES('To');
INSERT FTS(col2) VALUES('Be');
INSERT FTS(col3) VALUES('Or');
INSERT FTS(col4) VALUES('Not');
INSERT FTS(col5) VALUES('To');
INSERT FTS(col6) VALUES('Be');
INSERT FTS(col7) VALUES('That');
INSERT FTS(col8) VALUES('Is');
INSERT FTS(col9) VALUES('The Question');
INSERT FTS(col5) VALUES('To Be Or Not To Be That Is The Question');

CREATE FULLTEXT CATALOG FTS_cat AS DEFAULT;

CREATE FULLTEXT INDEX ON FTS(col1,col2,col3,col4,col5,col6,col7,col8,col9)
KEY INDEX PK_FTS WITH STOPLIST = OFF;

SELECT * FROM FTS WHERE CONTAINS(col1,'to')  -- one row
SELECT * FROM FTS WHERE CONTAINS(col2,'to')  -- no rows
SELECT * FROM FTS WHERE CONTAINS(*,'to')     -- 3 rows

-- clean up
DROP FULLTEXT INDEX ON FTS;
DROP TABLE FTS;
DROP FULLTEXT CATALOG FTS_cat;
If you need to add new columns to the FT index you can use ALTER FULLTEXT INDEX.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000