Squared Thoughts

Think about what you are doing!

2008-09-19T10:27:00.001-07:00

Production code:

SELECT @ReturnStatus = @@ERROR
IF(@ReturnStatus <> 0)
RETURN (@ReturnStatus)

Making things as simple as possible isn't just for self-congratulatory back-patting. It's because the next time you're looking at the code it will make it easier to understand what it's doing. So let's make it simple:

RETURN @@ERROR

Some Pain Is Avoidable - So Spare Yourself

2008-05-07T12:23:00.000-07:00

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

2008-04-18T16:47:00.000-07:00

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.

Database Time Storage and Display Problems

2008-04-04T13:13:00.000-07:00

After seeing a post about UTC time in a forum, I did some more searching and reading on the web, and found an article, UTC datetime values in SQL Server 2000.

Earlier today I had the same basic idea as the one presented in that blog, though a little more simply:

SELECT UTCDateColumn + Getdate() - Getutcdate()

In the comments on the blog entry is a function someone tried to build that would automatically adjust for daylight savings time as well. This immediately struck me as problematic, and this was borne out by further comments such as that the rules are different for Arizona. And to not be overly self-centric, what about the majority of the people in the world who do not observe DST at all? Follow the link in this paragraph to see a map of the world usage of DST.

There were additional problems. One commenter said, "Say it's currently summer and I'm in daylight savings time, my difference will include DST. Now I try to convert a date from 6 months ago, I'll be an hour out."

So here are my rambling thoughts on the subject for your amusement (at me or with me, your choice).

So you want to know the time. The time where and in what time reference? Is it at the client? Is it at the server? Is it at the place the time event corresponds to like the cash machine where a withdrawal was made?

If you have web clients around the world, using the database to calculate "The Time" is pointless.

Even if your client location(s) only span one time zone (and can you guarantee this forever) are you sure that client will always be in the same time zone as the server?

The various methods to automatically calculate the UTC differential on the server are clever but with DST are exposed for being completely inadequate, because all one is answering is, what is the current UTC differential, including DST, at the server's location? One cannot reasonably expect that to apply to all clients and for all date ranges in the database.

In my mind, the correct answer is to let the user choose his UTC differential in the client, or let the client autodiscover the differential (through a server getutcdate(), or connecting to a time server, or reading the offset out of the registry, for example).

Handling older dates that were stored during a different phase of DST is an interesting problem. Are we answering the question "what was the time then in terms of our time now" or "what was the time then in terms of the time then?" And the time now/then where?

Before you answer that seemingly easy question overquickly, think about the 1 hour period just after DST adjustment makes the time jump back and the same hour is repeated. Imagine you're reviewing security logs for a crime event. Your system reports:

2:05 am - last person to exit the facility was researcher John Smith

2:20 am - critical research samples verified to be secure

2:35 am - later investigation pinpoints this as the time critical research samples were known to be missing

If the system is adjusting times for "what was the time then" then you can't know whether John Smith left the building before the samples were verified secure, or if he left after they were stolen, because there were in fact two segments of time that night covering 2 am - 2:59:59 am, and he could have cleverly left the building during the second hour in order to fool the computer systems.

Anyway, if you set a concern like this aside and still wish to adjust for the time things were when recorded, I can't see how this can be accomplished except on the client because only the client can know the actual location of the client and his desired DST rules. Unless you keep a comprehensive and carefully-updated catalog of the DST rules everywhere your clients can possibly be, this sounds like a challenge.

Maybe the answer is to store both dates in the database: the client time AND UTC time, or UTC time and client offset at that time. This data doesn't even have to be in the individual rows: it could be captured or updated in a UTC-offset history table for each client location every time a client connects. It has its own problems like: does the client know his location, UTC offset, and DST rules & current phase correctly? If so, can the client always be trusted to present this information accurately or could the user purposefully taint the data?

There is even more complication:

What if you are in some place that doesn't observe DST and you want to compare transactions from months ago that were recorded for two different places: your current location and a location that is in a different time zone and does observe DST.

What consistent method of adjustment do you plan to use to display all the times in the same list? Clearly they have to be adjusted the same for any sort of sensical ordering or comparison, and it makes sense to adjust them for the observer so he can view them in his own time. Yet now, adjusting the historical date of the time recorded for the observing-DST location has to be done in terms of the viewing location's DST rules, which can also lead to incorrect assumptions.

So yet again it seems that time data needs to either be stored in UTC and viewed in UTC with an adjustment for the CURRENT offset only and the user knowing this information and making allowances for it, or time data needs to be stored in UTC and ALSO the client's time and the client's location, in some format or other, so that true reconstruction of "the time" can be answered from every perspective needed: any location, observing any DST rules, during any DST phase. At historical review time, there is a huge list of possible dates to display for a single recorded UTC time:

The UTC time of the event (the one place where all the madness is overcome!)

The time of the event at the event location, using the DST offset of the event location then

The time of the event at the event location, using the DST offest of the event location now

The time of the event at the user's location, using the DST offset of the user location then

The time of the event at the user's location, using the DST offset of the user location now

The time of the event at the event location, using the DST offset of the user location then

The time of the event at the event location, using the DST offset of the user location now

Yikes!

In closing, the getdate() - getutcdate() idea might be useful in conjunction with the client's stated known offset to determine if the client is observing DST *now*, but by itself can never solve the problem of fully-sensible historical time display in any time zone observing any DST rules (that themselves change over time).

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

2008-01-09T09:58:00.000-08:00

% - 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

SQL Trouble

2008-01-07T16:57:00.001-08:00

The troubled code:

CASE
   WHEN theTime <=0030 THEN '0000'
   WHEN theTime >0031 AND theTime <=0130 THEN '0100'
   WHEN theTime >0131 AND theTime <=0230 THEN '0200'
   WHEN theTime >0231 AND theTime <=0330 THEN '0300'
   WHEN theTime >0331 AND theTime <=0430 THEN '0400'
   WHEN theTime >0431 AND theTime <=0530 THEN '0500'
   WHEN theTime >0531 AND theTime <=0630 THEN '0600'
   WHEN theTime >0631 AND theTime <=0730 THEN '0700'
   WHEN theTime >0731 AND theTime <=0830 THEN '0800'
   WHEN theTime >0831 AND theTime <=0930 THEN '0900'
   WHEN theTime >0931 AND theTime <=1030 THEN '1000'
   WHEN theTime >1031 AND theTime <=1130 THEN '1100'
   WHEN theTime >1131 AND theTime <=1230 THEN '1200'
   WHEN theTime >1231 AND theTime <=1330 THEN '1300'
   WHEN theTime >1331 AND theTime <=1430 THEN '1400'
   WHEN theTime >1431 AND theTime <=1530 THEN '1500'
   WHEN theTime >1531 AND theTime <=1630 THEN '1600'
   WHEN theTime >1631 AND theTime <=1730 THEN '1700'
   WHEN theTime >1731 AND theTime <=1830 THEN '1800'
   WHEN theTime >1831 AND theTime <=1930 THEN '1900'
   WHEN theTime >1931 AND theTime <=2030 THEN '2000'
   WHEN theTime >2031 AND theTime <=2130 THEN '2100'
   WHEN theTime >2131 AND theTime <=2230 THEN '2200'
   WHEN theTime >2231 AND theTime <=2330 THEN '2300'
   WHEN theTime >2331 THEN '0000'
END as GroupedTime

First of all, the logic excludes every number ending in 31. The result for those will be NULL. Oops.

So here's improvement #1:

GroupedTime =
   CASE
      WHEN theTime >= 2231 THEN '2300'
      WHEN theTime >= 2131 THEN '2200'
      WHEN theTime >= 2031 THEN '2100'
      WHEN theTime >= 1931 THEN '2000'
      WHEN theTime >= 1831 THEN '1900'
      WHEN theTime >= 1731 THEN '1800'
      WHEN theTime >= 1631 THEN '1700'
      WHEN theTime >= 1531 THEN '1600'
      WHEN theTime >= 1431 THEN '1500'
      WHEN theTime >= 1331 THEN '1400'
      WHEN theTime >= 1231 THEN '1300'
      WHEN theTime >= 1131 THEN '1200'
      WHEN theTime >= 1031 THEN '1100'
      WHEN theTime >= 0931 THEN '1000'
      WHEN theTime >= 0831 THEN '0900'
      WHEN theTime >= 0731 THEN '0800'
      WHEN theTime >= 0631 THEN '0700'
      WHEN theTime >= 0531 THEN '0600'
      WHEN theTime >= 0431 THEN '0500'
      WHEN theTime >= 0331 THEN '0400'
      WHEN theTime >= 0231 THEN '0300'
      WHEN theTime >= 0131 THEN '0200'
      WHEN theTime >= 0031 THEN '0100'
      ELSE '0000'
   END

But here's improvement #2. Now, oddly enough, theTime is a varchar column (it was extracted from a datetime column as varchar for some reason, but I'm working to try to fix that).

Groupedtime = Right('0' + Convert(varchar(2), (Left(theTime, 2) + CASE WHEN Right(theTime, 2) >= 31 THEN 1 ELSE 0 END) % 24), 2) + '00'

More improvements can be made if the theTime column comes in as datetime or a proper numeric such as minutes (instead of the display varchar number where '0100' means 1 o'clock or 60 minutes, not 100 minutes. I will be making that change since the source of the data is also able to be changed.

SQL Puzzlement

2008-01-07T16:44:00.000-08:00

The tortured SQL I find in production:

CASE WHEN ExtendedDescription is null
   THEN replace(replace(replace(MainDescription, '''', '`'), char(10), ';'), char(13), ' ')
   ELSE (replace(replace(replace(MainDescription, '''', '`'), char(10), ';'), char(13), ' ')
      + '(' + replace(replace(replace(ExtendedDescription, '''', '`') + ')', char(10), ';'), char(13), ' '))
      END as FullDescription,

A simpler way to express this:

FullDescription =
   Replace(Replace(Replace(
      MainDescription + Coalesce('(' + ExtendedDescription + ')', '')
      , '''', '`'), char(10), ';'), char(13), ' '
   ),

It's not just about the simplest way to express something, but also about how easy it is to read and see what it's doing.

Oddly enough I do put commas at the beginning of lines sometimes when expressions are very long. I'll never put commas at the beginning of a new column expression, though.

SQL Overelaboration

2007-12-27T14:18:00.000-08:00

Random forum posting, function that detects if a string is made only of numbers:

CREATE function fn_numeric
-- input is varchar string.
   (@string varchar(12) )
returns bit -- 0 = false, 1 = true
as
begin
declare @x smallint, @y smallint, @z bit
set @string = replace(@string, ' ', '@')
set @x = len(@string)
set @y = 0
set @z = 1
while @y < @x
    begin
    set @y = @y + 1
    if substring(@string, @y, 1) between '0' and '9'
        continue
    else
        begin
        set @z = 0
        break
        end
    end
return (@z)
end

My response:

WHERE ColumnToTest NOT LIKE '%[^0-9]%'

Sometimes Optional Keywords Shouldn't Be

2007-10-29T10:41:00.000-07:00

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

2007-10-25T15:52:00.000-07:00

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.

SQL Confusion

2007-09-24T16:35:00.000-07:00

It is bizarre how often I find laughable and silly code in production databases that are part of software which costs millions of dollars.

I'm sure there will be more of these to come. Here's my contribution for today.

SELECT distinct
...,
(select DATEADD(mi,-DatePart(mi, GetDate()), DATEADD(ss,-(DatePart(ss, GetDate())), GetDate()))) as orecordeddtm

WHOA! Run that statement and you'll see it's not only convoluted but it's wrong, too: it leaves the milliseconds portion of the time still on there (it's a datetime column). Here's my proposed simple and working alternative:

DateAdd(hh, DateDiff(hh, 0, GetDate()), 0)

The query is DISTINCT, which isn't a problem in this case because the expression is GetDate(). But what if the author of this used the same logic for a column? The left-in milliseconds would be messing everything up. Plus, why is the expression inside its very own select clause? Bizarre. Which of the following two statements makes more sense to YOU?

SELECT 1 + 1
SELECT (SELECT 1) + (SELECT 1)

Interesting is about the kindest description I can come up with for this.

Mixed-Up Statement Types

2007-09-06T08:28:00.001-07:00

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

2007-08-29T16:03:00.001-07:00

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.

Am I Thinking About The Right Thing?

2007-08-23T10:36:00.000-07:00

I have thought for a long time about deliberately ordering my thought processes. It's something that I do quite frequently. I'd like to share that with people and maybe spur others to do it more, but ironically I'm finding it difficult to express the concept clearly.

I think I'll start for now with an example. If I come back to this topic later, I can build on that example and maybe come up with something that helps me communicate what I'm talking about more effectively.

Sometimes I wonder if the way I think is substantially different from the way many people think. I mean, the areas where my brain works well (or doesn't work well) may not coincide with many people's. So I'm not really sure about the validity of some of my thoughts on how one might order one's own thought processes to yield better results. For example, I like to use spatial representations to figure out problems. There are many times when working on a query that I imagine little squares (representing tables or groups or some kind of data) touching each other, splitting, moving, folding, and so on. But for someone who doesn't think well spatially, that's probably not going to work. There are other modes and ways, though. So even if you can't relate directly to what I'm talking about, maybe you can find some comparable process that does work for you, that helps you be more deliberate in your thinking process.

On the other hand, maybe I'm way out in left field. Maybe my brain is stuffed full of cheese-filled rotten rutabagas! So take what I say here with a grain of salt.

When one is doing analysis of a query, one should check each thing along the way to see if it's giving the right value. It's kind of like when you have a problem with a computer peripheral, say, a printer. There is sort of a list, roughly from lowest to highest: Is it turned on? Is the power cable plugged in? Is the network/parallel cable connected to the right location? Does it have paper? Are error codes showing, is there a paper jam, are there any obvious latches or doors that need to be closed, is there a ready mode that it needs to enter? Can you generate a test page? If it's a network printer, can you ping it? Can anyone else print to it? Can you print to it with a different OS? Can you print to it with a different program? Can you print a smaller document? Can you print a document without images in it? Can you print a document with a different font?

If you find yourself troubleshooting at a certain level and it's not working, then back up and go to the more basic questions. You might even start at the beginning. Doing this helps you check your assumptions and guides you into the next correct troubleshooting step. If the extent of the description of the problem is "I can't print," you don't know much at all. But if the problem is, "I can print from Joe's computer but not from mine," then you can be directed to the level of problem-solving you ought to be at. Since now you know that it's plugged in, turned on, ready, has paper, is connectable for Joe and is responding to print requests, what level are you at? You're ready to verify your own connectivity. Only then deal with access rights. Then drivers. Then different programs. Then things affecting printer memory.

Don't waste your time with printing from different programs until you're sure you can even connect to that thing. Maybe the network cable on your computer came unplugged—you'd just be wasting your time messing around with printing from different programs. Maybe it's a private printer connected directly to his computer with a USB or parallel cable and it's not shared or you don't have access to it—you'd be wasting your time messing around with trying to ping it or using different drivers.

Going back to databases, here's a real-world example of getting stuck too high up in the chain. Several experienced SQL people were helping a poster with a query where he was trying to show summary data from several tables at once, but he was getting sums that were multiples of the correct sums. Everything was complicated because he wanted some pivoting, but was doing it by joining to the tables multiple times instead of using case statement mini-machines to calculate the correct sums as the stream of rows passed by. (Here I go with a spatial representation again. I tell you, it really helps me.)

The reason he couldn't figure it out was that he was thinking of the query in terms of its structure after grouping instead of before. When there was more than one row to be grouped by for one table, and then he joined to another table (whether or not it had multiple rows per group), everything was getting cross-joined among the groups. He needed to step back and run his query without the group by clause, and see if the rows made sense before being collapsed into their defined buckets (there's more spatial framing again). If he'd done that, he'd have seen the cross-joining behavior and realized what the problem was. All the experts, myself included, forgot about that at first and started fiddling with the summing/aggregating stuff!

That was too high of a level. We hadn't even checked to see if the data we were trying to manipulate was in the correct form. And it wasn't.

So. Deliberately ordering one's own thought processes. The next time I am about to think about a problem, I'm going to stop thinking. Then I'm going to think about what I'm thinking about for a minute. Is this the right thing to think about? Am I making assumptions? Can I test those assumptions?

I'm going to back off from solving the problem and think about whether it's even the right problem to start with.

The 3 Millisecond Datetime Trick Is Dead

2007-08-21T13:42:00.000-07:00

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

2007-08-21T11:44:00.000-07:00

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:

and even worse:

Then there are column names:

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.