Fox Tricks

Going beyond sp_helpindex

2009-06-28T20:43:00.000+01:00

Every SQL Server developer and DBA has to use the sp_helpindex from time to time to get some information on the indexes on a table. I have 2 major problems with this stored procedure: All information is concatenated in 1 string Not much info on partitioned indexes If I run the following statement: exec sp_helpindex '

How to determine the default database path in all SQL Server Versions

2009-06-12T17:04:00.017+01:00

When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations. Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right

How to create SQL Server temp tables without collation problems

2008-07-08T08:35:00.016+01:00

In SQL Server it is possible to create temporary tables which are special tables that are automatically cleaned up when they go out of scope. There are two types of temporary tables, global and local temporary tables. Global Temporary Tables are visible in all sessions currently connected to the SQL Server instance and only go out of scope when the last session disconnects from the server. It is

How to strip trailing zeros

2008-05-09T13:28:00.004+01:00

A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero. 1.0501needs to show1.0501 1.0500needs to show1.05 1.0000needs to show1 This is easy to do by

How to make a thread safe insert/update procedure on SQL Server

2007-11-23T10:17:00.000+01:00

When developing an application that does a lot of inserts and updates, it is sometimes easier to wrap both statements in one stored procedure. This results in a simple procedure that will check if a certain record exists. When no record is found a new one is inserted, when a record is found the values are updated. We will use the following table script as example to explain the problem: SELECT

Zero Padding a numeric value in Transact SQL

2007-07-02T15:51:00.000+01:00

A quick one before I go home (and just because I used it 10 minutes ago)... I zero pad a numeric value like this: DECLARE @mynumber INT, @padding INT SELECT @mynumber = 123, @padding = 8 SELECT REPLACE(STR(@mynumber, @padding), ' ', '0') The result of this example will be 00000123 I admit, it is not very

High Performance writing to XML Log files.

2007-07-02T09:29:00.000+01:00

A characteristic of logfiles is the "append" nature. We append new content at the back of the file. When writing to a normal text file this is very easy and fast. 2007-07-01 13:56:04.313 start process 2007-07-01 13:56:25.837 do something 2007-07-01 13:56:25.853 the end But when we want to use XML files to write log information things become a little bit more complicated because the xml needs

Recognising the SQL Server Version

2007-06-29T14:41:00.000+01:00

Something I often need to do is recognise which SQL Server version I am working on. For example to separate SQL 2000 compatible code from SQL Server 2005 compatible code. I'll show you some of the options to get the version number... Starting from the ugliest to the most elegant... The first option is calling the extended procedure xp_msver select name, sid from