Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I wrote a previous post on how to set up full-text searching (FTS) and indexes in SSMS. This post looks at the T-SQL equivalent.
Everything in SSMS uses T-SQL under the covers. Often, you can get the code from a dialog in SSMS and use that for repeatable operations. For FTS, that’s not the case. When we get to the end of the wizard, there is no “Script” button.
there isn’t one on other screens, either. This is an omission (and bug in) from SSMS, IMHO.
In any case, we need to do these things:
- create a catalog
- create a full text index
- populate the index
That’s what we’ll do.
Create a Catalog
A FTS catalog is a logical group of FTS indexes. That’s it. This is used to be a way to decide where indexes are stored, as you can choose the filegroup for these. However, after SQL Server 2008, the storage decisions (path or filegroup) have no effect.
Now we really run this:
CREATE FULLTEXT CATALOG name
We can add accent sensitivity or a default setting (or an owner), but really we’re just picking a name here for the most part.I’ll run this:
CREATE FULLTEXT CATALOG FTSCat
That gives me a place to put indexes. I can create multiple catalogs, if needed.
Creating FTS Indexes
The next step is to actually create an index. The basic syntax uses the CREATE FULLTEXT INDEX DDL, with the table and column. We need a PK on the table for this to work, so make sure your table has one.
We can add some options for language, statistical semantics to be gathered, and population parameters. All of those are documented in the Docs. To create a basic index you can use, this is what I’ll do. First, here’s my table.
CREATE TABLE dbo.FTS2
( myid INT NOT NULL CONSTRAINT FTS2PK PRIMARY KEY
, Val VARCHAR(2000));
GO
Next, let’s create the index. I want to index the val column in this table. I’ll use this statement:
CREATE FULLTEXT INDEX ON dbo.FTS2(Val) KEY INDEX FTS2PK ON FTSCat
This statement lists the table and the column(s) in parenthesis, much like any other index. I need to provide the unique index used to track rows, which in this case is the PK. I also give the catalog on which I store this index.
Once this is done, the index is created. This auto populates by default, which means I can query using the CONTAINS() function to search.
Populating Indexes
If I had specified manual population for the index, then I’d need to populate it myself. Plenty of people want to choose the time to populate indexes, as this can be resource intensive.
To do this, the ALTER INDEX can be used to start this. This is simple with the START FULL POPULATION (or UPDATE POPULATION) command.
ALTER FULLTEXT INDEX ON dbo.FTS2 START FULL POPULATION
That’s it.
Summary
This post essentially duplicates what I did in the previous post, but with direct T-SQL instead of using the GUI. It’s always good to know both ways to accomplish something, especially as the GUI might not contain an option you want to use.
SQLNewBlogger
This was easy to do after the previous post. I essentially repeated everything, except I had to look up the T-SQL for each step to check syntax.
This is a great example of showing some learning, and adding depth to a previous post. Easy to do, another item that can impress an interviewer.