We are back. Ok. CRAZY new improvements today in the world of badflyer.com. Its not actually all that exciting, but it's a feature of SQL server which I have always wanted to try out. It's Full Text search! So instead of essentially doing some thing "post.content LIKE '%' + @query + '%'", SQL Server can do much more advanced/and faster full text searches.

To get a basic basic full text search going, you need to get a few things ready. So, super simple tutorial:
  • Table
  • Primary Key
  • Full Text Catalog
  • Full Text Index
  • A stored procedure ready to utilize this

So lets get to work. We need a table.

Here is my table.

raw

CREATE TABLE [bad].[post]
(
    [slug]     NVARCHAR(256) NOT NULL
   ,[title]    NVARCHAR(256) NOT NULL
   ,[livetime] DATETIME2     NOT NULL
   ,[content]  NVARCHAR(MAX) NOT NULL
)

Ok, so of course the table needs a primary key, so lets make one of those too. Not here, the reason that we need to make the primary key outside of the table like this, is that we need the name of the primary key when we make a full text index.

raw

ALTER TABLE [bad].[post]
    ADD CONSTRAINT [post_pk]
    PRIMARY KEY ([slug])

So, pretty standard table ready to go. We have a table and a primary key. Lets make a catalog to utilize this. All full text indicies need to go into a catalog.

raw

CREATE FULLTEXT CATALOG [post_full_text_catalog]

BOOM. Catalog accomplished. INDEX TIME. Ok, so for the full text index, the syntax is pretty strait foreward. I'll describe right after the code.

raw

CREATE FULLTEXT INDEX
    ON [bad].[post]
        ([title], [content])
    KEY INDEX [post_pk]
    ON [post_full_text_catalog]
    WITH CHANGE_TRACKING AUTO

So what did this mean? Here's pretty much what we are trying to explain to SQL Server.

So first things first, make me a FULL TEXT INDEX on my table '[bad].[post]' on the columns ([title], [content]). The primary key on my table is named '[post_pk]'. That needs to be the name of the constraint (which is why we actually made a named constraint earlier, instead of just inlining it into the table definition). Also, the name of the catalog we created earlier is named '[post_full_text_catalog]', so put the full text index in that one.


Sweet. Now all we need is a stored procedure that actually utilizes this thing. Interesting to note here. To utilize your full text index, you cant just use 'LIKE'. You will need to use some specific search functions built into TSQL. Here is a list of the four big ones.

  • FREETEXT
  • FREETEXTTABLE
  • CONTAINS
  • CONTAINSTABLE

In this example, we are going to use the crazy overkill option. 'FREETEXT'. 'FREETEXT' is kind of like google for your SQL server. It does a bunch of crazy stuff and tries to extract the meaning from your query. 'CONTAINS' is more restrictive. It's what you get when you use something like SOLR. You can query using things like "cat AND dog". So here is out stored procedure.

raw

CREATE PROCEDURE [bad].[postsearch]
    @query NVARCHAR(256),
    @count INT
AS
    DECLARE @utcnow DATETIME2 = GETUTCDATE()

    SELECT TOP (@count)
        slug
       ,title
       ,livetime
       ,content
    FROM bad.post
    WHERE livetime <= @utcnow
      AND FREETEXT(([title], [content]), @query)
    ORDER BY livetime DESC
RETURN 0

So here we told SQL to query the title and content columns for our query, using the FREETEXT command. And mission accomplished.