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.

---

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;