Emil’s Blog

Templates for SQL Server Management Studio 2008 are stored in this folder on WIndows XP:

C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

On my new WIndows 7 machine, the location is:

C:\Users\[user]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

Create sub-folders and SQL files there and they show up in the Template Explorer. Really useful…

/Emil

This will list stored procedures, views, etc. that uses a given table column:

Credits for this tip to SQLServerCentral.

/Emil

You won't need them very often but it might be good to know that they're there. Here's an example of how to use a bitwise AND in a where clause:

In this example, 'applikationer' is a column containing several combined flags and we want to check the setting on the flag in the lowest bit.

The bitwise operators are:

• & - bitwise AND
• | - bitwise OR
• ^ - bitwise EXCLUSIVE OR

Have you ever had a need to create temporary database tables in a SQL Server session or procedure? Are you used to creating them by hand like this:

Depending on what you need the table for, then there might be an easier way. Have a look at this short session:

The first line creates a new temporary table based on the result set of a SELECT query, and stores the result set in the table. The query can be more complex than in the example, with JOINs, WHERE clauses etc. Incredibly useful when mangling and transforming data!

This is how to generate a new GUID in SQL Server T-SQL:

Result:

I'm back now from my parental leave, so here's the first posting in a while...

Ever installed SQL Server forgetting to install the client tools such as Management Studio, Profiler, etc? Here's how to install them the easiest way.

Instead of running the main installation program, which may or may not work, run [drive]:\SQL Server x86\Tools\Setup\SqlRun_Tools.msi to install the tools directly. Much quicker and we're not depending on updates made to SQL Server since the installation.

Tip found here.

/Emil

It can sometimes be useful to know the id of a given database, e.g. for filtering events when profiling. Here are a few ways to do that:

Here's an easy way of extracting ISO dates from a datetime column:

Result:

When doing an UPDATE and there are two occurrances of the table to update, then use the alias as table name. Example