Using Full Text Indexing

In this page I will explain how to create a full text index on large character columns, Filestream or XML.
Step 1: create a catalog
USE [my_db]

CREATE FULLTEXT CATALOG [proverbs]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]

You can check this with:
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

Here I have created a full text catalog called proverbs which will be stored in the database. If you are using SQL 2005 you can use the IN PATH N'd:\somepath' to specify it's location, this tag is depricated in SQL 2008. ACCENT_SENSITIVITY means that there will be a difference between café and cafe. The last line sets the dbo as owner.

Step 2: create an index
CREATE FULLTEXT INDEX ON [dbo].[proverbs]
KEY INDEX PK_proverbs ON proverbs
WITH CHANGE_TRACKING AUTO

This creates an index on the table [dbo].[proverbs] using the cluster index name PK_proverbs in the catalog proverbs created in step 1. Basically adds the table with the primary key to the catalog

Then add the column on which you want to search:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ADD ([proverb])

This enables fulltext indexing on the column proverb of the table [dbo].[proverbs].


Then enable the index which will do a rebuild of the index:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ENABLE

Step 3: test with a select
SELECT * FROM [dbo].[proverbs] WHERE CONTAINS ([proverb], 'something')

With the CONTAINS statement you can use keywords AND, OR, NEAR, and use wildcards like '*'

SQL timesaver: MERGE

Let's say you want to transfer an amount of data from one database or table to another and you can't just transfer all the records without checking. You can do several statements after each other and do an INSERT IF NOT EXISTS, or you can do everything in one statement with MERGE.
Here is an example:

MERGE users  AS t_U
USING (
SELECT [username], [password], [email], [fullname]
FROM
[someothertable]
WHERE
[isDeleted] = 'N'
) AS U 
ON t_U.[username] = U.[username]
AND t_U.[email] = U.[email]
WHEN MATCHED THEN UPDATE
SET [is_duplicate] = 'Y'
WHEN NOT MATCHED THEN
INSERT(
[username], [password], [email], [fullname]
)
VALUES (
U.[username], U.[password], U.[email], U.[fullname]
);
GO

In this code I merge the users from someothertable to the users table. If the username and email are the same then I set the column is_duplicate on the target tabel to Y.
You can expand the WHEN MATCHED statement with extra criteria using AND X = Y or something like that, or you can add an extra WHEN MATCHED statement. The order of the WHEN MATCHED statements are important.

Used in Microsoft SQL server 2005 and higher