ColdFusion queries and SQL comment

Here's one problem I experienced using a query in ColdFusion which was working perfectly in SQL server manager, but refused to work in ColdFusion.
The query was something like:

SELECT * --some comment
FROM users
WHERE user_id = 1

The query would only work if the comment is removed. The source of the problem is that ColdFusion removes all the line breaks in a query before executing. In this case only "SELECT *" would be executed which results (luckily) in a SQL error.
Good to know this before I tried the following query:

DELETE
FROM users --some comment
WHERE user_id = 1

Note: Normally you would use a SQL param and replace it with queryService.addParam(), this would result in an error because the param could not be found.

To work around this problem use /* and */ for SQL comment, or strip all your comments in SQL if you execute it through ColdFusion.

Modifying a column in SQL manager, the right way

If you have a column of a table that needs to be changed, don't always go to the modify table page in SQL manager to do your changes. I tried something simple as increasing the column size of a nvarchar field, but the update kept on giving timeouts in the SQL manager. Then I tried the generate SQL script button to see what SQL manager was trying to do. The contents gave me an idea why I was getting timeouts:
-alter the table:
-drop constraints
-drop triggers
-create temporary table with new column sizes/fields
-add defaults from table to temp table
-add constraints from table to temp table
-add index to temp table
-insert all values from original table to temp table
-drop relationships from original table
-drop original table
-rename temp table name to original table name
-add relationships to temp table

Which it needed to do is:
ALTER TABLE sometablename
ALTER COLUMN somecolumnname
nvarchar(100) NULL;

Next time if I modify a table with much data, or a lot of relationships and/or constraints I will stay clear of the SQL manager "modify table" dialog.

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