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:
- Primary Key
- Full Text Catalog
- Full Text Index
- A stored procedure ready to utilize this
So lets get to work. We need a table.
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.
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.
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.
CREATE FULLTEXT INDEX
KEY INDEX [post_pk]
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.
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.
CREATE PROCEDURE [bad].[postsearch]
DECLARE @utcnow DATETIME2 = GETUTCDATE()
SELECT TOP (@count)
WHERE livetime <= @utcnow
AND FREETEXT(([title], [content]), @query)
ORDER BY livetime DESC
So here we told SQL to query the title and content columns for our query, using the FREETEXT command. And mission accomplished.