T-SQL
Transact SQL related
If you have been used to clearing up disk space on your development and test SQL server instance file systems with SQL Server 2000 or SQL Server 2005 by using the famous TRUNCATEONLY option on your transaction logs, you might be disheartened that they have removed this option from SQL Server 2008 (with good reason some might say).
If you do try to use it, you will get the infamous:
'TRUNCATEONLY' is not a recognized BACKUP option.
To achieve the same effect with SQL Server 2008, you can toggle the recovery mode for the target database and then call your DBCC SHRINKFILE to clear up...
One thing to be aware of when you have VARCHAR columns and are using ANSI PADDING: trailing whitespace is trimmed and not counted in your equality ( = / <> / LIKE) statements, so you might not get the results you are looking to be returned from your query.
In the same vein, the len() function will return the same value for two strings if one has trailing whitespace. If you need to compare those two fields including trailing whitespace values, use datalength() instead.
Here's some example T-SQL:
DECLARE @Table TABLE (Field VARCHAR(50))
INSERT @Table VALUES ('some text')
INSERT @Table VALUES ('some text ')
INSERT @Table VALUES (' ...
Ever have trouble matching unicode letter equivalents in your T-SQL queries? For example, when you want "Québec" and "Quebec" to be equivalent in your text search query?
This is where specifying Accent Insensative Collation (The "AI" in all those collation codes) comes to the rescue. If your database is already set to an AI collation, then you are all set already, but if not, change your LIKE statement to use something like the following:
SELECT *
FROM MyTable
WHERE MyColumn COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%quebec%'
Hope this helps!