Category: SQL

Posted on

Removing MS SQL Full Text Search

This week I had to move one of my sites from web host to another. Full Text Search, though useful, turned out to be a nuance because the new host doesn’t not allow it and the old host wanted to charge me $20 to remove it.

Why didn’t I just remove it myself? Well, I assumed I didn’t have the privileges because it was grayed out in Enterprise Manager. But hope was not lost, I fired up Query Analyzer and found that I was able to manage full text search functionality without any issues. Here’s the code if someone needs it…

Step 1, Drop the table (or tables if you have multiple)

USE DB_NAME;
GO
EXEC sp_fulltext_table 'TABLE_NAME', 'drop';
GO

Step 2,  Drop the Full Text Search Catalog

USE DB_NAME;
GO
EXEC sp_fulltext_catalog 'CATALOG_NAME', 'drop';
GO

In summary, working on a shared hosting environment can have its disadvantages but with a little Transact-SQL know-how it isn’t all that bad.

BTW, here’s a good reference for MS SQL Full Text Search Stored Procedures… linky.

Posted on

The Subtract Join

Subtract Join

I needed a way of selecting all primary key(s) from a certain table (Table_1) that was not used as a foreign key in a sub-table (Table_2). So I went at it with my SQL know-how to no end. It was obvious that I needed to do a little catching up. 🙂

I googled the following:

• all instances of unique id not found in sub-table
• primary key not found as foreign key in sub-table
• select all rows if not exist in sub-table
• sql tutorial
• sql join
• sql reference
• sql relational databases

…and found nothing useful. However, my research did lead me to one conclusion… what I needed was the opposite of a join. I googled for it, it as in “opposite of a join”. What I found was a very useful MS KB, http://support.microsoft.com/kb/136699.

The described “Subtract Join” was exactly what I was looking for and it works! What’s odd is of all the SQL tutorials I ran into, none of them mentioned the SUBTRACT JOIN. That makes me think there might be a better way of doing this query. Is there a better way? Am I simply not using the right keywords when I search? Anyways, if there is I couldn’t find it…