SQL Server
T-SQL, SQL Server 2005 encounters
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 (' ...
The December 2009 TechNet Magazine is on the website. Check out my IT Toolbox column here:
December 2009 TechNet Magazine IT Toolbox
In this issue I covered:
Eraser: Eradicate Sensitive Information
SQL Server Backup Pro from Red Gate: Streamline SQL Server Backups
MindManager: Map Out Ideas, Notes and Projects
Check it out and let me know what you think!
And if you have a tool you want to see me review, please suggest it to me here: tntools@microsoft.com.
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!
Ever want to turn multiple rows into a comma seperated list when you are querying your database?
Here's a quick way to get it done using "FOR XML PATH('')":
DECLARE @comma_delimited_list VARCHAR(6000)
SELECT @comma_delimited_list =
(
SELECT productName + ','
FROM Products
WHERE productName LIKE 'a%'
FOR XML PATH('')
)
IF LEN(@comma_delimited_list) > 0
SELECT @comma_delimited_list = STUFF(@comma_delimited_list ,LEN(@comma_delimited_list), 1, '')
A couple things to remember: 1) you have set your path to '' and 2) you can't name your selection or it will put the name into your xml adding more information than you want.
If you are looking for a quick way to verify the service pack and version applied to your SQL server instance, try the following T-SQL:
SELECT
SERVERPROPERTY('productversion') as productversion,
SERVERPROPERTY('productlevel') as productlevel,
SERVERPROPERTY('edition') as edition
Hope this helps!
You can easily put a database into or take it out of single user mode with the following T-SQL:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH NO_WAIT
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER WITH NO_WAIT
GO
Here's a quick way to get some timing statistics on your T-SQL:
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON
GO
-- Insert your query here
SET STATISTICS TIME OFF
GO
After you run the statements in query analyzer, check the messages tab to see how it went.
How to Shrink Your Database Transaction Log
If you have noticed that your transaction log on your SQL Server database has grown incredibly large after a long transactional process, you can try this to shrink it back down:
DBCC SHRINKFILE(mydatabase_log, 2)
GO
BACKUP LOG mydatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(mydatabase_log, 2)
GO
Of course, you'll want to replace "mydatabase" with the name of the database you wish to shrink and "mydatabase_log" with the name of the log for that database.
To force a named pipes connection to your database, prepend the name in your connection string with "np:".
E.g.
<add key="my_conn_string" value="Data Source=np:(local)\SQL1; ....
Full SQL Server Archive