Some Pain Is Avoidable - So Spare Yourself

Ever heard of a runaway F5? This is when you intend to highlight only a small section of SQL code but you miss for some reason and when you hit F5, the entire script you have loaded runs instead of just the small part you tried to highlight.

It's not enough to put a syntax error at the top of your script because there might be some batch separating GOs in there. So there are a few things that I routinely do to protect against this because the results of such a mistake can be, shall we say, painful.

1. Put this at the top:

create database trash
GO
use trash
GO

This will make most of your code run in the wrong context. The GO statements are important as you need these to run without errors.

If your code uses three-part names to point to specific databases, this will not help, so keep reading.

2. Don't use the database name in dev-only code, and think carefully about your use of database context in your production code. If you put a USE statement in your manually-attended script, hide it inside comments:

/*
USE Somedatabase
*/

-- USE Somedatabase

The block comment way makes highlighting the whole line easier if and when you need to run it.

3. Comment out the modification part of your statements and turn them into selects:

Instead of the following query:

UPDATE MyTable
SET Salesrep = 42
WHERE CustID = 16

This is a much safer query to leave lying around in your script:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *
-- UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16

The highlighted part is what you have to select in order to run your update (note that you're skipping the commenting characters at the beginning of the line). You can still make a mistake by leaving off the WHERE clause, but at least you know no updates will be made unless you do some careful selecting.

Here's one rough idea for a way to protect against forgetting the where clause:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *
-- BEGIN TRAN UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16
ROLLBACK TRAN
-- COMMIT TRAN

You can still blow it but it's harder. Any full-line end selection is protected.

And another, simpler and possibly better:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *,
-- UPDATE M SET Salesrep = 42
FROM MyTable M /*
*/ WHERE CustID = 16

A full-line end selection is again protected.

4. Be paranoid. Don't let yourself get comfortable. THINK about each update you do to real, live, data. Make an extra "unnecessary" backup. Convert your modification statements to selects and check it. Select the results into a new table and do some joins to make sure they were right. Run the update in a transaction and do some queries to verify them. Keep in mind potential locking & blocking, though, so if people are accessing the table you're modifying, create the check statements and do the update and the check all at once:

BEGIN TRAN

UPDATE Blah -- My potentially dangerous modification

SELECT * FROM Blah B INNER JOIN Gorp G ON Splat = Bonk

ROLLBACK TRAN

Only run the whole block. If you don't like what you got, fix it, and run it again.

Be wary of leaving open any transaction, even one you don't think will affect other people. A stray SQL editor session holding some kind of lock is annoying and can cause problems.

I hope these ideas are useful to you and save you some pain! I had to learn this the hard way. I am simply fortunate that I never did anything REALLY bad before acquiring these habits.

Update 2008-05-16

5. If you must have a USE statement, check the server:

IF @@Server = 'DevServerName' USE DevDatabase

Depending on what you're doing in your code and how potentially destructive it is, you can do some pretty clever things to help prevent problems. Use the tools at your disposal.

SQL Waste Land

Welcome to Waste Land, the place where your server is full of char and nchar columns instead of varchar and nvarchar columns. In case you're not familiar with those, the difference between the two sets of data types is that the ones that aren't varying (don't have the letters "var" in the data type name) always consume a fixed amount of space. So if you define a column in a table as char(2) and then insert the value 'a', the database actually stores 'a ' with an extra space.

In SQL Server 2008, when you turn on rowlevel and page compression, it will treat char as varchar and will also compress values. But until then, char columns waste space.

Inspired by the wastefulness of a large database I work with, I wrote some code to find out: just how much of the database is wasted space?

First, some prerequisite functions:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TokenItem]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[TokenItem]
GO
/* Function TokenItem
Author: Erik E
Returns the [TokenNum]th item from a string
For example
PRINT dbo.TokenItem('The quick brown fox jumped over the lazy dog', 4, ' ')
will return the fourth token of the string, with tokens identified as delimited by spaces: fox
*/
CREATE FUNCTION TokenItem (
   @String varchar(8000),
   @TokenNum int,
   @Delimiter varchar(8000) = ','
)
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @DelimLen int
   SET @DelimLen = Datalength(@Delimiter)
   IF @TokenNum < 1 OR @TokenNum > 1 + (Datalength(@String) - Datalength(Replace(@String, @Delimiter, ''))) / @DelimLen
      RETURN ''

   DECLARE @Pos int
   SET @Pos = 1

   WHILE @TokenNum > 1 BEGIN
      SET @Pos = CharIndex(@Delimiter, @String, @Pos) + @DelimLen
      SET @TokenNum = @TokenNum - 1
   END

   RETURN SubString(@String, @Pos, CharIndex(@Delimiter, @String + @Delimiter, @Pos) - @Pos)
END

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TokenItemRev]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[TokenItemRev]
GO
CREATE FUNCTION TokenItemRev (
   @String varchar(8000),
   @TokenNum int,
   @Delimiter varchar(1) = ','
)
RETURNS varchar(8000)
/*
Returns the @TokenNum-th token as separated by @Delimiter, counting from the last token
Returns an empty string if no token by that number exists (< 1 or > tokencount)
Example:
PRINT dbo.TokenItemRev('a,b,c,d,e', 2, ',')
returns 'd'
*/
AS
BEGIN
   RETURN Reverse(dbo.TokenItem(Reverse(@String), @TokenNum, @Delimiter))
END

Now the real meat of the tool.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpaceCharInfoCollectBackup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpaceCharInfoCollect]
GO
/*************************************************************************************
Procedure:   SpaceCharInfoCollect
Version:      1.2
Date:         8/11/2006
Author:      Erik E

Description:
   Find out how much wasted space there is in a database,
   because of the use of char or nchar columns instead of varchar or nvarchar.
   Doesn't support object names containing periods.

Parameters:
   TableName      Table to analyze for space information. Since it defaults to the
                  database the stored procedure is in, include the database name
                  if the stored procedure is being run from a different database,
                  as in database.owner.table. Can also specify owner.
   DestTableName  Table to receive the result information, can be in another database
                  on the same server. Use database.owner.table if desiring a table
                  in a different database than the stored procedure is located in or
                  if you want a different owner.
   DateStamp      Time to store as the data collection time. Defaults to GetDate().

Planned Updates:
   A version that runs against all databases on a server, without using undocumented
   stored procedures (See SP SpaceInfoCharCollectAllDB).

Update History:
   1.0 <8/11/2006>:   Original version
   1.1 <10/13/2006>:  Updated to accept destination table, and database name for
                      table being reported on. Added database name to results table.
                      Added object owner logic and column to results table.
   1.2 <4/18/2007>:   Used numbers instead of names for column selection to reduce
                      script size, to handle more char columns in a table without errors

Examples:
USE ThisDatabase
EXECUTE SpaceInfoCharCollectAllDB

USE DesiredDatabase
EXECUTE sp_msforeachtable 'EXECUTE DatabaseOfThisSP.dbo.SpaceCharInfoCollect ''DesiredDatabase.?'', ''DesiredDatabase.dbo.SpaceCharInfo'''
*************************************************************************************/

CREATE PROCEDURE SpaceCharInfoCollect
   @TableName varchar(8000), -- table to analyze space information on, include databasename, use brackets if name has weird characters
   @DestTableName varchar(8000), -- table to receive the result information, can be in another database on the same server
   @DateStamp datetime = NULL, -- defaults to GetDate(),
   @Debug bit = 0
AS
DECLARE
   @NSQL nvarchar(4000),
   @PreSQL varchar(8000),
   @SQL1 varchar(8000),
   @MidSQL1 varchar(8000),
   @SQL2 varchar(8000),
   @MidSQL2 varchar(8000),
   @SQL3 varchar(8000),
   @PostSQL varchar(8000),

   @DB varchar(8000),
   @Owner varchar(8000),
   @Table varchar(8000),

   @DestDB varchar(8000),
   @DestOwner varchar(8000),
   @DestTable varchar(8000)

SET NOCOUNT ON

PRINT @TableName

IF @DateStamp IS NULL SET @DateStamp = GetDate()

SET @TableName = Replace(@TableName, '''', '''''')
SET @Table = WhatDatabase.dbo.TokenItemRev(@TableName, 1, '.')
SET @Owner = WhatDatabase.dbo.TokenItemRev(@TableName, 2, '.')
IF @Owner = '' SET @Owner = user
IF @Owner LIKE '[[]%]' SET @Owner = SubString(@Owner, 2, Len(@Owner) - 2)
SET @DB = WhatDatabase.dbo.TokenItemRev(@TableName, 3, '.')
IF @DB = '' SET @DB = Replace(DB_Name(), '''', '''''')
IF @DB LIKE '[[]%]' SET @DB = SubString(@DB, 2, Len(@DB) - 2)

IF @Table LIKE '[[]%]' SET @Table = SubString(@Table, 2, Len(@Table) - 2)

SET @DestTableName = Replace(@DestTableName, '''', '''''')
SET @DestTable = WhatDatabase.dbo.TokenItemRev(@DestTableName, 1, '.')
SET @DestOwner = WhatDatabase.dbo.TokenItemRev(@DestTableName, 2, '.')
IF @DestOwner = '' SET @DestOwner = user
SET @DestDB = WhatDatabase.dbo.TokenItemRev(@DestTableName, 3, '.')

SET @SQL1 = 'IF NOT EXISTS (SELECT * FROM '
   + @DestDB
   + '.Information_Schema.Tables WHERE Table_Name = ''' + @DestTable + ''' AND Table_Schema = ''' + @DestOwner + ''')
   SELECT
      SpaceDate = convert(datetime, NULL),
      DatabaseName = convert(varchar(128), NULL),
      OwnerName = convert(varchar(128), NULL),
      TableName = convert(varchar(128), NULL),
      ColumnName = convert(varchar(128), NULL),
      ExtraSpaces = convert(bigint, 0),
      RowCnt = convert(bigint, 0)
   INTO ' + @DestTableName + '
   WHERE 1 = 0'

EXECUTE (@SQL1)

SET @PreSQL = 'INSERT ' + @DestTableName + ' (SpaceDate, DatabaseName, OwnerName, TableName, ColumnName, ExtraSpaces, RowCnt)
SELECT
   SpaceDate = ''' + Convert(varchar(50), @DateStamp, 121) + ''',
   DatabaseName = ''' + @DB + ''',
   OwnerName = ''' + @Owner + ''',
   TableName = ''' + Replace(@Table, '''', '''''') + ''',
   ColumnName,
   ExtraSpaces = CASE ColumnNum'
SET @MidSQL1 = '
      END,
   RowCnt
FROM
   (
      SELECT'
SET @MidSQL2 = ',
         RowCnt = Count(*)
      FROM ' + Replace(@TableName, '''', '''''') + ' WITH (NOLOCK)
   ) X CROSS JOIN ('
SET @PostSQL = '
   ) Y (ColumnNum, ColumnName)'

SET @SQL1 = ''

SET @NSQL =
N'SELECT
   @SQL1 = @SQL1 + ''
      WHEN '' + Convert(varchar(11), Ordinal_Position) + '' THEN ['' + Replace(Column_Name, '''''''', '''''''''''') + '']''
      + CASE WHEN Data_type = ''nchar'' THEN ''* 2'' ELSE '''' END,
   @SQL2 = IsNull(@SQL2 + '','', '''') + ''
         ['' + Replace(Column_Name, '''''''', '''''''''''') + ''] = Sum(IsNull('' + LTrim(Str(Character_Maximum_Length)) + '' - Len(['' + Replace(Column_Name, '''''''', '''''''''''') + '']), 0))'',
   @SQL3 = IsNull(@SQL3, '''') + ''
      '' + CASE WHEN @SQL3 IS NULL THEN '''' ELSE ''UNION ALL '' END + ''SELECT ''
      + Convert(varchar(11), Ordinal_Position) + '', ''
      + '''''''' + Replace(Column_Name, '''''''', '''''''''''') + ''''''''
FROM
   ' + QuoteName(@DB) + '.Information_Schema.[Columns] C
   INNER JOIN ' + QuoteName(@DB) + '.Information_Schema.Tables T ON C.Table_Name = T.Table_Name and Table_Type = ''base table''
WHERE
   Data_Type in (''char'', ''nchar'')
   AND T.Table_Name = ''' + @Table + '''
   AND T.Table_Schema = ''' + @Owner + ''''

IF @Debug = 1 PRINT @NSQL

EXECUTE sp_executesql @NSQL, N'@SQL1 varchar(8000) OUTPUT, @SQL2 varchar(8000) OUTPUT, @SQL3 varchar(8000) OUTPUT', @SQL1 OUTPUT, @SQL2 OUTPUT, @SQL3 OUTPUT
IF @SQL3 IS NULL RETURN 0 --no columns found

IF @Debug = 1
   PRINT (@PreSQL + @SQL1 + @MidSQL1 + @SQL2 + @MidSQL2 + @SQL3 + @PostSQL)
   ELSE EXECUTE (@PreSQL + @SQL1 + @MidSQL1 + @SQL2 + @MidSQL2 + @SQL3 + @PostSQL)

RETURN 0

/*
--Compare before and after values from most recent two run dates:
SELECT
   DatabaseName = IsNull(x.databasename, y.databasename),
   OwnerName = IsNull(x.ownername, y.ownername),
   TableName = IsNull(x.tablename, y.ownername),
   ColumnName = IsNull(x.columnname, y.columnname),
   ExtraSpaces1 = Sum(x.extraspaces),
   ExtraSpaces2 = Sum(y.extraspaces),
   RowCnt1 = Min(x.rowcnt), -- / count(distinct IsNull(x.databasename, y.databasename) + IsNull(x.ownername, y.ownername) + IsNull(x.tablename, y.ownername)),
   RowCnt2 = Min(y.rowcnt), --/ count(distinct IsNull(x.databasename, y.databasename) + IsNull(x.ownername, y.ownername) + IsNull(x.tablename, y.ownername))
   CollectionTime1 = Min(x.Spacedate),
   CollectionTime2 = Min(y.Spacedate)
FROM
   (
      SELECT * FROM dbo.spacecharinfo d1 WHERE spacedate = (select max(spacedate) from dbo.spacecharinfo where spacedate < (select max(spacedate) from dbo.spacecharinfo))
   ) x
   FULL JOIN (
      SELECT * FROM dbo.spacecharinfo d1 WHERE spacedate = (select max(spacedate) from dbo.spacecharinfo)
   ) y ON x.databasename = y.databasename AND x.ownername = y.ownername AND x.tablename = y.tablename AND x.columnname = y.columnname
GROUP BY
   IsNull(x.databasename, y.databasename),
   IsNull(x.ownername, y.ownername),
   IsNull(x.tablename, y.ownername),
   IsNull(x.columnname, y.columnname)
WITH ROLLUP
ORDER BY
   IsNull(x.databasename, y.databasename),
   IsNull(x.ownername, y.ownername),
   IsNull(x.tablename, y.ownername),
   IsNull(x.columnname, y.columnname)

select
   spacedate,
   databasename,
   ExtraSpaces = sum(extraspaces),
   [Rows] = sum(rowcnt),
   [Average spaces per row] = convert(float, sum(extraspaces)) / convert(float, sum(rowcnt)),
   [Average spaces per column] = convert(float, avg(extraspaces))
from spacecharinfo
group by spacedate, databasename
with rollup
having grouping(spacedate) = 0
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpaceInfoCollectAllDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpaceInfoCollectAllDB]
GO
-- runs SpaceInfoCollect against all databases and tables, see that SP for more information
CREATE PROCEDURE SpaceInfoCollectAllDB(@DestTableName nvarchar(4000) = NULL)
AS
SET NOCOUNT ON
IF @DestTableName IS NULL SET @DestTableName = QuoteName(DB_Name()) + '.' + QuoteName(User) + '.SpaceCharInfo'
DECLARE
   @SQL nvarchar(4000),
   @DBName nvarchar(128),
   @Dt datetime
SET @Dt = GetDate()
SET @SQL =
'SELECT ''USE '' + [name] + '' EXECUTE sp_msforeachtable ''''EXECUTE ''
   + QuoteName(DB_Name()) + ''.dbo.SpaceCharInfoCollect '''''''''' + QuoteName(Replace([name], '''''''', '''''''''''')) + ''.''
   + Char(63) + '''''''''', ''''''''YourDesiredDefaultDatabase.dbo.SpaceCharInfo'''''''', ''''''''' + Convert(varchar(50), @Dt, 121) + '''''''''''''''
FROM master.dbo.sysdatabases
WHERE [name] NOT IN (''master'', ''model'', ''tempdb'', ''pubs'', ''msdb'')'

SET @DBName = DB_Name()

EXECUTE sp_executesql N'EXECUTE master.dbo.xp_execresultset @SQL, @DBName', N'@SQL nvarchar(4000), @DBName nvarchar(128)', @SQL, @DBName
SET @SQL = 'SELECT * FROM ' + @DestTableName + ' WHERE SpaceDate = ''' + Convert(varchar(50), @Dt, 121) + ''''
SET NOCOUNT OFF
EXECUTE (@SQL)

Whew! Now how do you use all that? Easy.

EXECUTE SpaceInfoCollectAllDB

When you're done, take a look at the code at the end of the SpaceCharInfoCollect to see some final analysis tools for the output table. The database I ran this on had a whopping 10 GB of extra spaces, which is something like 14% of the entire database size.

Yes, the dynamic SQL is really difficult to work with. Making major changes directly in the script is nearly impossible: it's much easier to grab the section of script you want to change, paste it into a new window, and replace doubled single-quotes with single single-quotes. Which of course doesn't work for embedded values from the full outer script, but it helps immensely. Then when you have what you need, you double them back up and paste in just the parts you changed. At times you have to go two levels of dynamic SQL deep (or more) and undouble/double your quotes at each step. Use the power of search and replace to help you.

In some cases for small changes it does work to use the debug mode to output the final script and then examine or try to run THAT, and iteratively modify the dynamic SQL and see what you get.

If you find any bugs, please let me know. I haven't tested this on nchar columns, but it should multiply the space used by 2 since we care about bytes rather than characters.

P.S. There are 15 single quotes in a row in this script. Wheeeeeeeee.

What's the Percent Character (%) in SQL?

% - the percent character in SQL is the modulo (aka modulus) operator, which means give the remainder of a division. For example, 11 divided by 4 is 2 with a remainder of 3. It's the 3 that we're interested in.

I think about the operation in a few different ways. Depending on what form your numbers are in and what functions are available to you, or depending on which strikes you as the most natural, one of these might be more helpful to you than the others.

Form 1 - modulo is to subtract from n the highest multiple of x that is less than n:

n % x = n – floor(n / x)

Form 2 - modulo is to divide n by x, remove the whole number portion, then multiply by x again:

n % x = (n / x - floor(n / x)) * x

Form 3 - modulo is, using a clock with x numbers (from 0 to x - 1), to start on 0 and take n steps, then look at what number we end up on.

Operation: 11 % 4
Okay. Here's a 4-numbered clock to count on:

  0
3   1
  2

Starting at 0 we take 11 clockwise steps and see that we end up on 3.

So 11 mod 4 = 3.

Here are some syntax notes for other programming environments:

n mod x <- how it’s written in VB
mod(n, x) <- how it’s written in Excel

It’s a nice way to get the remainder without having to use n twice in an expression.

Examples:

10 % 10 = 0
10 % 9 = 1
10 % 8 = 2
10 % 7 = 3
10 % 6 = 4
10 % 5 = 0

It helps to keep in mind that the result will always be in the range 0 to (x – 1). Anything mod 10 will always end up in the range 0 to 9. Modulo has the same precedence as multiply and divide (at least in SQL): lower than negation and bitwise not but higher than everything else.

If you want to add 1 to an hour value of 0 to 23 and not just end up with 1 – 24, modulo is a great way to do that.

For an hour value that goes from 0 to 23, to add one hour:

(Hr + 1) % 24 -> 0 to 23 -> 1 to 24 -> 1 to 23, 0 (0 to 23 again, rotated up one)

For an hour value that goes from 1 to 24, to add one hour:

Hr % 24 + 1 -> 1 to 24 -> 1 to 23, 0 -> 2 to 24, 1 (1 to 24 again, rotated up one)

Since the modulo of negative numbers can give unexpected results depending on the programming language (-1 % x could be 1 or it could be x – 1, e.g., -1 mod 5 could be 1 or 4) you just add a multiple of x (since that won't change the remainder but will get us out of negative territory), then subtract what you need. Usually you only need x.

Here’s an example of finding last month’s month number from this month’s month number. Note that when you have ranges that start with 1 instead of 0, sometimes you have to subtract 1 before the modulo and then add 1 after the modulo.

Note: we’re subtracting one from the month, then adding 12 so we don’t get negative numbers. We also subtract 1 before the modulo and add 1 after the modulo (to convert the 1-based range to a 0-based range and back).

(Month – 1 + 12 - 1) % 12 + 1
which simplified is:
(Month + 10) % 12 + 1 -> 1 to 12 -> 11 to 22 -> 11, 0 to 10 -> 12, 1 to 11

Sometimes Optional Keywords Shouldn't Be

I always use the CLUSTERED or NONCLUSTERED keywords in my constraint scripting. I used to not do it. But then I realized the following problem:

You have a table with some stuff in it.

CREATE TABLE Test(a int NOT NULL, b int NOT NULL)
INSERT Test VALUES (1, 2)
INSERT Test VALUES (2, 3)
INSERT Test VALUES (3, 4)

You go and add a primary key to it:

ALTER TABLE Test ADD CONSTRAINT PK_Test PRIMARY KEY (a) --<-- Clustered, right? Perhaps...

And now you have a clustered index, right? Yes. But let's say that this table had different needs than most and its primary key wasn't the clustered index and there was already a non-primary-key clustered index! So drop the constraint you just made, then re-add it after making our test table correspond with your "real" table we're imagining:

ALTER TABLE Test DROP CONSTRAINT PK_Test
CREATE CLUSTERED INDEX IX_Test_b ON Test (b)
ALTER TABLE Test ADD CONSTRAINT PK_Test PRIMARY KEY (a) --<-- Not clustered this time.

So now your primary key constraint is NOT clustered, even though the default for primary keys IS clustered. And it was the same DDL script each time, performing something very different. So I concluded that it's best to always include the keyword CLUSTERED or NONCLUSTERED so that you and other developers know that's what was intended. Plus now you won't go your merry way thinking that you did something you didn't. Let's try to re-add that PK but using the CLUSTERED keyword (which we are expecting to happen by default without using it):

ALTER TABLE Test DROP CONSTRAINT PK_Test
ALTER TABLE Test ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (a)

And now you get the information you needed to do your job properly:

Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'Test'. Drop the existing clustered index 'IX_Test_b' before creating another.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

UNIQUE is another word that I've seen people forget to include in their NONCLUSTERED index creation (not primary keys of course) so don't forget to put that in when you are expecting uniqueness. On the one hand, you might think that checking for uniqueness on every update or insert would be a performance hit. But that is not a problem: the real performance hit comes from the extra bytes required on that non-unique index as a unique-ifier bloating the index's size and reducing its speed.

I should add that having extra unused indexes is a performance hit all by itself. Here's a good discussion about that. (This guy has a WAY better blog than I will ever have. If you've gotten anything from reading my blog, then you should forget about mine and go read his. When you're done reading all 50,000 of his posts, then come back here, where there's still a teensy tiny chance remaining you'll learn something from me that you didn't from him.)

Table Variables Use Tempdb

create table #t(a int)
select * from tempdb.dbo.sysobjects where name not like 'sys%'
drop table #t
GO
declare @t table(a int)
select * from tempdb.dbo.sysobjects where name not like 'sys%'

Now remove the GO and run it again. Notice that the table variable exists in tempdb during the first select! This is generically in computer programming called hoisting (and see more interesting related stuff).

The query engine decides how much to put in tempdb and how much to keep in memory. Some reports I've read say that the data always lives in memory, but others say that tempdb can be used for some of the data. What I'm taking away is that, while there is always the possibility that your table variable will live only in memory, it is not guaranteed. But using a table at all will always incur a performance hit. And consuming too much RAM can be as much a detriment to a server as consuming too much tempdb space.

Create a correlated subquery and put it in the SELECT list of a simple query against a many-row table that has a clustered index, and put a literal derived table in the subquery that UNION ALL SELECTS the numbers 1 through 10. It doesn't matter what the subquery does as long as it logically runs once for each row in the main query's table (it has a reference to a column from the main table).

Now create a table variable and insert the same numbers 1 through 10 and modify the correlated subquery to use the table variable instead of the UNION list. Compare execution plans and traces. Note that the first query has 10 constant scans in it, repeated as many times as there are rows, but the number of reads are close to the number of pages in the physical table.

The second query has no constant scans in it, but the number of reads is now closer to the number of rows in the physical table.

You cannot think of table variables as no-cost, memory only, super-fast objects with no side effects. They are not. They have side effects that can very nearly be main effects.

Mixed-Up Statement Types

I am going to start trying to put together the basic "What you need to know to get started with SQL Server." I see the same exact problems over and over again in SQL help forums. FAQs are good, but there are dozens and dozens of them, each generally solving a specific problem. I see a need for something before that. People are getting so confused they don't even know what to ask, let alone know what kind of FAQ is going to help them with their problem.

So I'll one at a time make some posts about different conceptual issues I see and then finally I'll pull it all together as the "Top 10 Concepts" for beginners. Or whatever name seems right at the time. :)

So today I'm going to start with the different types of statements in T-SQL, and particularly the difference between expressive, procedural, and manipulative statements. Other kinds of statements we won't concern ourselves with now are data definition, data control, and transaction control. People generally don't get as confused about those.

Expressive Statements

An expression consists of one or more literal values or functions tied together with operators, which when evaluated in the correct order result in a value or collection of values.

In most cases an expression returns only a single value. In computing there is a special term for "a thing that is only a single value" called a scalar. A composite or many-valued expression may be a list, array, or record. In SQL Server there are some cases where a rowset can be treated as a list and used in an expression.

Here are some example expressions in T-SQL:

Power(2, 10)
-- The literal values 2 and 10 are operated on by the built-in function power which returns a single value.

(18 + 23) * 7 % 5
— The literal values 18, 23, 7, and 5 are combined with operators: addition, grouping (parentheses), multiplication, and modulus, to return a single value.

CASE ... WHEN ... <WHEN ...> ELSE ... END

These are all scalar expressions. I'll give an example of a multi-valued expression later because to use it one has to mix some of the statement types.

Let me call attention specifically to the fact that with only one exception, all parts of an expression are evaluated. CASE statements do support "short-circuit" logic where the evaluation stops with the last WHEN condition that is true. But in general, you should think about expressions as an all-or-nothing proposition. Nothing can be done with all the parts of an expression until the whole thing is evaluated into a final and single scalar.

Procedural Statements

Procedural statements are called that because there is some procedure that must be followed. It isn't a simple case of order-of-operations resulting in a single value. There is in fact no value expressed at all. Here are some examples:

IF <true-or-false expression>
WHILE <true-or-false expression>
RETURN <integer expression>
THEN
ELSE
BEGIN
END
GOTO
RAISERROR(...)
PRINT

These all tell the query parser what statement it should proceed to next. Note that some of these procedural statements expect expressions immediately following. The server, while executing the statements, has to evaluate the expression in order to know what it is supposed to do. Let me call attention specifically to the fact that not every statement may be executed, and they may be executed repeatedly or out of order.

Manipulative statements

These are normally called queries (though some of them do things instead of just asking questions as the word query implies).

SELECT
INSERT
UPDATE
DELETE
UNION

The Point Of All This

Now that you know the three main kinds of statements (and I won't rule out the possibility of there being more or of there being subclassifications of these) the key concept you must know to get along well with SQL Server is that when a certain kind of statement is expected, you can't use a different one in its place.

Here are some examples of confusing one kind of statement with another.

-- Example 1
IF (IF @b = 2 THEN @i ELSE @j) = 1 PRINT 'True' ELSE PRINT 'False'

IF expects a true-false expression after it. You can't put another procedural statement there.

-- Example 2
EXECUTE CASE WHEN @Action = 'Kill' THEN MyKillingSP ELSE MyNonKillingSP END

EXECUTE isn't looking for an expression. It wants an SP name. Instead, do

IF @Action = 'Kill' EXECUTE MyKillingSP ELSE EXECUTE MyNonKillingSP.

There. EXECUTE is happy now because it has an SP name immediately following in each case.

-- Example 3 - An actual example from online
RETURN
   (
      IF @LINEID = '123'
         BEGIN
            select * FROM TABLE1
         END
      ELSE
         BEGIN
            SELECT * FROM TABLE2
         END
      END
   )

In a stored procedure, RETURN expects an integer and in a user-defined function it expects an expression or a rowset or nothing, depending on its type. You can't put procedural statements in. This is obviously a rowset-returning user-defined function. So it will have to be a table-variable returning one. Use procedural logic to insert the right rows into the table variable, and then simply RETURN.

-- Example 4
SELECT *
FROM MyTable
WHERE
   Status = (IF @Status IS NULL THEN '' ELSE @Status)

IF is a procedural statement. You can't just embed it in the middle of an expression. And the IF statement switches back to expressive again: the first keyword after THEN can't be an expression. What's supposed to be done with that? It has to be procedural or manipulative: SET or INSERT or GOTO or PRINT. If you ran the following all by itself as its own batch, you'd get an error:

''

So '' can't be the first thing after THEN.

The way to do an expressive if-then-else is with a CASE statement. I think it's worth spending a moment to explain that it has two forms:

--Simple equivalency
CASE CaseExpression WHEN TestEquivalentExpression THEN ResultExpression <WHEN ... THEN ...> ELSE ... END

CASE @i
   WHEN 1 THEN 'One'
   WHEN 2 THEN 'Two'
   WHEN 3 THEN 'Three'
   WHEN NULL THEN 'NULL'
 -- This doesn't work because "@i = NULL" will always fail.
 -- Use the next construction to test for this.
   ELSE 'Invalid'
END

-- fully expressive
CASE WHEN TrueOrFalseExpression THEN ResultExpression <WHEN ... THEN ...> ELSE ... END

CASE
   WHEN @i = 1 THEN 'One'
   WHEN @i = 2 THEN 'Two'
   WHEN @i = 3 THEN 'Three'
   WHEN @i IS NULL THEN 'NULL'
   ELSE 'Invalid'
END

But it resolves to a single final value. I believe (though I need confirmation on this) that the query engine expands the first syntax into the second. So be wary of using nondeterministic functions like Rand() in CASE statements. (Nullif() definitely gets expanded to a CASE statement so it has the same issue.)

SELECT *
FROM MyTable
WHERE
   Status = CASE WHEN @Status IS NULL THEN '' ELSE @Status END

This is just an example and may not be best coding practice.

Manipulative Statements As Expressions

Finally, there are some special cases where manipulative statements can be used in expressions or as expressions.

1. A manipulative statement that returns a single column and a single row can be used in most any place that an expression can be used. This is done by placing it in parentheses:

IF (
   SELECT OrderGroup FROM OrderStatuses WHERE OrderStatus = @OrderStatus
) = 2 ...

PRINT (SELECT Count(*) FROM MyTable)

2. A manipulative statement that returns a single column and many rows can be used in an expressive statement immediately following certain operators, again in parentheses. Those keywords are IN, EXISTS, and in conjunction with inequality operators, ANY, SOME, and ALL:

SELECT *
FROM Orders
WHERE OrderStatus IN (SELECT OrderStatus FROM OrderStatuses WHERE OrderGroup = 2)
-- Note this is better done with an INNER JOIN.

IF EXISTS (
   SELECT *
   FROM Orders
   WHERE
      ExpectedShipDate >= GetDate()
      AND OrderStatus = 1
) ...
-- note that because of EXISTS, no columns are returned, and the query engine stops as soon as it finds one row meeting the criteria.

SELECT *
FROM Orders
WHERE
   OrderStatus > ALL (
      SELECT OrderStatus
      FROM OrderStatuses
      WHERE OrderGroup = 2
   )
-- can use ANY and SOME as well as ALL

Note that some other SQL languages may allow multiple columns in some of these kinds of expressions.

That should be enough to get people started on understanding

Note: I might have mistakes or be missing some details in this blog entry. If I am, I'd appreciate you pointing it out so I can make the final version accurate and complete.

SQL Server 2005

I've only recently really started to get into some of the new T-SQL stuff in SQL Server 2005. I am really digging them. And of course, at the same time, I am still wanting yet even more!

Today I used ROW_NUMBER() for the first time, and it was easy. It will take a few tries to remember the syntax exactly, but it makes perfect sense. You have to be able to say when to begin counting at 1 again (PARTITION OVER) and what order to count in (ORDER BY). RANK, DENSE_RANK, and TILE_N (if I'm remembering that right) all make sense, too.

I'm looking forward to getting into the OUTPUT clause. Before I had even learned about it in SQL 2005 I wanted this functionality for INSERT, UPDATE, and DELETE. I imagined its syntax slightly differently. First, any of these statement could be made to have a rowset. I actually overlooked this part when I was originally thinking about it, but it's fairly obvious once you realize its necessity. Here's one possible way:

RETURN_ROWSET INSERT Stuff SELECT Blah FROM Gorp

Then this query could be used any place a SELECT could be used:

UPDATE H
SET Column = NewValue
FROM
   StuffHistory H
   INNER JOIN (
      RETURN_ROWSET INSERT Stuff SELECT Blah FROM Gorp
   ) S ON H.StuffID = S.StuffID

This would allow you to chain together any number of statements, causing each INSERT, UPDATE, or DELETE to return a rowset, which would either go to a client or could be used again in the next statement. I realize now that there are some issues with the syntax I envisioned, but I think it's interesting that I "invented" this on my own before learning about it from SQL 2005.

(The issues are that for an update there are two potential rowsets: the Inserted and Deleted meta-tables, so the way Microsoft implemented it makes a certain sort of sense, though I find the syntax a little weird, and I don't think you can nest these over and over again.)

Perhaps instead of nesting things this way it could be more like a common table expression, with many queries using the thing just like a table:

;WITH_ROWSET S AS (INSERT Stuff SELECT Blah FROM Gorp)
BEGIN
   UPDATE H
   SET Column = NewValue
   FROM
      StuffHistory H
      INNER JOIN S ON H.StuffID = S.StuffID

   SELECT NewValue FROM H WHERE OtherColumn = 1
END

There are also a bunch of gotchas surrounding views, triggers, remote tables, and limitations on the target of an OUTPUT INTO clause (target table can't have triggers, foreign keys, CHECK constraints, or enabled rules).

Here's one example from BOL 2005. Do you understand it on the first try? I didn't.

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

Anyway, that's enough for now. I'll talk another time about alternate syntax I wish one could use for UPDATEs and INSERTs.

P.S. If you are trying to insert HTML in your own blog on blogspot.com (blogger.com) stay away from the "Compose" mode and stay in "Edit Html" mode. They try to parse the HTML and fail and end up eating your stuff.

The 3 Millisecond Datetime Trick Is Dead

At times I've used my knowledge that SQL Server 2000 stores datetimes to a precision of 1/300th of a millisecond (I won't go into full details here). Here are some examples:

If @StartDate and @EndDate have already had their time portions removed (or guaranteed to have none) I might do this:

WHERE DateCol BETWEEN @StartDate AND @EndDate - '0:00:00.003'

or perhaps

WHERE DateCol BETWEEN @TheDate AND @TheDate + '23:59:59.997'

But I realized today that these tricks, though cool and interesting, and while perhaps reducing the amount of code or increasing readability, assume that the data type of DateCol will never change. That doesn't sound so bad since the only alternative is smalldatetime, right? Not for long.

In SQL Server 2008, the TIME, datetimeoffset, and datetime2 data types will have 100 nanosecond precision. It's not a bad bet that somewhere, some time, someone's going to be converting one of the datetime columns I was querying against to one of those. And then all my code written in this sort of style is going to break big-time.

And this is the kind of nasty break that doesn't bring all systems to a screeching halt (which has the benefit of notifying you that something is wrong) but lets you keep running your processes for days or weeks or months, until eventually something slips through that 3 millisecond hole or accumulates enough to be noticeable. Something critical, something important, something I should have thought about before using this kind of technique.

From now on I will be a good camper and deal with the pain of

WHERE DateCol >= @StartDate AND DateCol < @EndDate
--AND
WHERE DateCol >= @TheDate AND DateCol < (@TheDate + 1)

since they work no matter what the data type of DateCol is.

Naming Objects In Your Database

This is something I posted first in a SQL Server forum. Here it is again, touched up a bit.

---

So you want to name the objects in your database: your tables, your views, your functions, and anything else. And there are a lot of people out there who name them with what the object type is. Maybe even with more information. For example:

tblOrders
Table_Orders
sp_OrderSend (NEVER prefix SPs with "sp_" because the server will look for a system SP first!)
Proc_OrderSend
OrderStatusFunc
fn_OrderStatus
fn_t_OrderStatus

and even worse:

joe_sp_OrderSend
mike_sp_OrderCancel
david_fn_OrderStatus

Then there are column names:

ID
ProductID
keyProductID
pkeyProductID
pkProductID
pkProductIDCode
frnkeyProductID
fkProductID

So does this stuff help at all? Is it useful to put the type or relationship in the name of objects? Can you leave the name off the ID column in your tables? Should you put more information in there? What does it matter, anyway, as these are mere naming conventions, right? Just pick something, and stick with it!

I agree that consistency is important. But consistently helpful is a far improvement over consistently something-less-than-that. Consistently unnecessary is extra work and frustration. Consistently interfering impairs your ability and speed. Consistently mediocre is not the greatest.

Overall, I think it is best to take a minimalistic approach. I hope my reasons make sense to you, but whatever you do pick, you should have clear and compelling reasons for what you do. Notice the compelling part. Your default should be to avoid clutter and only add things if there is a materially demonstrable reason to violate that rule.

First of all, much of my advice is meaningless if you're using a GUI where one performs the less taxing task of recognition instead of the recall required to write queries in text. If you are writing queries in text, more power to you, and if you keep doing it I predict you'll eventually see the sense of my recommendations.

But if you're using a GUI to write queries you'll only reach a certain level of ability, never stepping into the next level of professional skill and seasoned experience in the SQL realm. I haven't used a GUI to write a serious query in a very long time (I might have started a couple in an Access database but even then switched to SQL view). I recently helped someone who posted his query that was obviously built in a GUI and it had about 10 left joins. I seriously doubt they all needed to be left joins, which means he was killing performance for no reason.

So if you are writing queries in a text editor, here are some thoughts for you:

• Lose the underscores. They clutter up the screen. They take the shift key to type and also your pinky finger which has to move two rows up from the home row. And using CamelCase is just as clear and saves space and that pinky traversing. And if you're really feeling lazy you don't even have to hit the shift key.

order_id
customer_name
device_code_reason

OrderID
CustomerName
DeviceCodeReason

Look at it this way: if you can train your eyes to scan either kind of naming convention just as efficiently, why not use the one that involves less typing and takes less space on the screen?

• The prefix/suffix naming convention is not always understood or followed. In one shop I worked in they named tables with prefixes: tbl for base tables, tlk for lookup tables, and tin for "intersect" tables. But no one really knew what they were for sure. "tin" tables were supposed to be transactional tables that had constantly changing data such as orders and transactions. "tbl" tables were supposed to be things like customers and products. But the problem was that many tables didn't fit a clear definition, and people were careless, or came along years after the creator of the database (who yes used a GUI database creation program), and they got named wrong. So now after all that silliness and extra typing, there was a "tin" table that was really a "tbl" table and a "tlk" table that was really a "tin" table after some changes, and ... so on and so forth. Just leave off that junk.

• You'll eventually become completely sick of typing the extra characters over and over again for NO GOOD REASON. That is, if you care about speed. If you don't care about speed, type your prefixes merrily. If you do get tired of them, though, you'll have these prefixes so wrapped up in your code and database that ripping them out becomes impossible. All those extra letters will come to be so much useless froofroo to you. At the same shop as tbl/tin/tlk they really named columns like keyOrderID and frnkeyOrderID. They added nine characters of typing to EVERY SINGLE JOIN I ever had to write. After a year of working there, the pain was no less. It took me about 2 weeks to learn the primary and foreign keys of that database. The other fifty weeks I wished the database designer had left well enough alone.

• Naming is something that is so much more important than most people seem to think. What does it matter what we call things, as long as we're "consistent," right? I disagree. My experience is that the naming in a database the single most important thing that structures how programmers relate to the data and that determines their final speed and facility working with it in the long run.

Mislabeling things is like putting up signs at the cliff edge that say "keep going" and "this is the right way" when it's not. Label things appropriately.

• Scanning and comprehension speed suffer when extra things are tacked on. When you look at a stored procedure or query, if you yourself weren't just working on it yesterday, then you have to digest it to understand it. The more prefix_blob_description_gorp you have in there, the harder it is to see what's going on. The assumption here is, you know your database like the back of your hand. Will that extra stuff speed you up now or slow you down? Who should you code for, the least common denominator or a reasonable level of facility? In fact, this brings up another important point:

• You do not want to give that new developer, who is unfamiliar with your database, a prematurely quick sense of competence and ability in your database. By doing this, you increase the chances that he or she will make some serious mistake. This is the opposite mistake of mislabeling. Now the path over the edge of the cliff is littered with so many friendly and distracting signals that the poor new person gets the same wrong signal to keep going when the correct response ought to be, "wait, I'm not sure I am doing the right thing." I'm all for helping new developers get going as fast as possible. But they ought to do this by studying the schema and practicing with it. Don't make it so apparently easy (but not necessarily materially easy) that even an ignorant person will believe he knows all about your database.

• The information is unnecessary and ultimately makes developers remember more stuff, not less. It sounds at first like a great way to expose information about the tables and objects. But the fact is that the period of time where a new developer isn't familiar with the database is actually very short. So for the benefit of two months of instant answers to the beginner, everyone else in the shop is suffering through unnecessary baggage.

• Don't label things with each developer's name. Why would you do that? Do all the developers write different versions of the same SPs? Are they not production SPs but simply a sort of learning environment where each can write whatever he wants so it's important to keep the names straight? In that case, why do the objects have the same owners? Just use a different owner name and don't put your name in the object. But wait, why are they even in the same database? Why does it possibly matter who wrote something in the function of the database as a whole? Things should be named according to what they are or do, not who worked on it first or last.

If I was offered a consulting job on a database that had every stored procedure and object using different names based on who had written them, I would refuse the work for fear of going absolutely nuts. If I was forced by my situation to take the work, I would every day curse the people who decided that putting their names on their stuff would somehow have any kind of long-term value and I would quit the moment I found something else. Long after Shannon and Victor and Felix are gone from the company, poor developers are toiling away trying to remember if that was a felix function or a victor view or perhaps it was a shannon function and a felix view! GAHHH!

• Spend careful and deliberate time naming things. I often use a thesaurus when designing a database. I have at times spent (cumulatively over the project) hours working out the correct naming of things. But when I was done, WOW how things flowed. I've worked with databases where the detail table wasn't, and the parent table wasn't, and the order table wasn't, and the Determination column wasn't (it should have been called DeterminationMethod, the actual determination was called something else entirely). Every single one of those is a roadblock to proper usage, even to the developer who's been working with that database for years. Make it intuitive instead of notated.

The way I do it is to never add anything to table names, and to use carefully chosen words for my functions and SPs that make it clear what they are. For example, all my SPs are either SubjectVerb or VerbSubject, consistently throughout the database (still experimenting with which I like better--I'm leaning toward SubjectVerb). Functions have words that make it clear what they do. NumberToHex is a function. OrderNumberToOrderID is a function. ProductDetailCount is a scalar function, ProductDetail is a table, and ProductCreate is a stored procedure. Develop your own pattern. I can go through my own code so fast when I've been careful like this. The meaning nearly leaps out at me.

• If you absolutely must use a prefix or a suffix (I lean toward suffixes myself because I like things to group by subject instead of function) why not use a single character instead of three? Tables can still do without any fluff at all. Then your other objects are still distinguishable:

OrderNumber_OrderIDF

(Before you criticize why one might have order numbers and order IDs, consider a system that has to handle orders from disparate sources which use overlapping order number schemes. So you either have to have a composite key--source,ordernumber--or use a global and internal order ID that can be unique per order.)

And think about that suffix part. In what possible situation would you see all your objects mixed together, and you need to ordered them by type but don't have a column that specifies type? This is the only way I can think of that a prefix with the type might be helpful. If it was actually needed at all because the objects aren't named intuitively.

• The object type can change. What if you decide to change the structure of one of your tables, and to support your older versions of software, you build the new table with a new name and convert your old table to a view? Now you have tbl_Whatever that's a view. Whoops. And what if you need to use that really important sp, and you name SPs with their creator (shudder), only you can't remember who wrote it? Now you have to look it up. What a time waster. What if you can't remember if it's an inline table function or a table-variable function? More wheels spinning. If these seem farfetched, they are not. I have worked with databases that had "tbl" objects that were views and believe me it was a nightmare remembering which was which. Who cares what it is! Views and tables are used in exactly the same way so why call them something different? If you're worried about the execution plan and query performance (and you ought to be) then you're already at a level that demands familiarity with the type of objects you're working with. If you absolutely need to notate that something is a view, put it in comments.

• Think of it this way: you're trying to build a race car--a sleek, elegant, efficient thing that can go as fast as possible and be interfaced with well. What good is a racecar that can theoretically go 200mph if the steering wheel has sharp spikes all over it and the driver has to spend extra time avoiding them? He won't be spending as much time at top speed as he ought to be.

• Name a column the same thing everywhere. Don't call it different things in different tables. Especially don't just call it "ID." Call it BlahID everywhere, including the Blah table that it originates from.

Calling all identity columns ID is certainly a kind of standardization. But it's a standardization of seriously doubtful value that makes different columns in different tables have the same name! This is now the opposite error: conflating things that one ought to be able to tell apart. If you've been trained to differentiate each thing by putting the type of it in the name, why now depart radically from that philosophy by calling all the IDs the same thing? From the beginner/new developer perspective, it seems to make things easier in some ways--no looking up column names when selecting, right? But it's not easier, it's harder. If you ever get the experience of doing it both ways in a large and busy production database (that is, ID vs. TableNameID), I suspect you'll eventually agree (unless you've become dogmatic about it by then and don't really care about the actual function or efficency any more).

I prefer to standardize things in this way: If there is an ID column, it shares the same name as the table, + ID. Now there's no confusion about what the name is. Now if you have a query with many joins and you need to use ID you're not getting confused.

• Another reason to avoid columns named "ID" is that you will eventually put the wrong alias on some join. You may not even notice because the query parses fine and you save your SP and off you go, but whoops, you got an effective cartesian product because your join condition referenced the wrong ID. Or just the wrong resultset. What a confusing mess. Much better, when you accidentally put D.ProductID instead of P.ProductID, for the compiler to complain that the table aliased by D has no such column. The alternate "D.ID" though would have compiled just fine and given the wrong results farther down the path, maybe even after release. It's much better to catch errors early.

• Selecting is not where the problem is. Yes, it's shorter to say SELECT ID FROM Table. Fewer characters. But the real problem is in JOINs. The problem is in old resultsets you saved in Excel that you don't know what table they came from because they so unhelpfully say ID. You don't know what some error message means that a user reported to you because all he remembered was the main column that the constraint violated and he says "there was a foreign key constraint in column ID!" Great. That's every table in your database.