Wednesday, March 24, 2010

Fix for 'USE dbname' Failing Randomly

Have you ever gotten an error message that you just couldn't figure out? A while back, and for about a year, a certain T-SQL script was giving me this error message:

Cannot find database 'SALES' in system tables.

This error message occured randomly every month or so, and with no cause-and-effect pattern that I could see. I couldn't reproduce it. It didn't fail when I ran it manually - but every time it happenned, it usually happened two or three times in a row. This gave me a series of tiny "windows of opportunity" in which to debug the problem.

After about a year (on and off), I had it narrowed down to one line of code - if I removed this line, the script ran correctly every time:

USE [Sales]

Now, the 'Sales' database was quite demonstrably there. Googling for the error message turned up one (unhelpful) hit. So, I started by dropping and re-creating the database. No joy. Next, I re-installed SQL Server. No joy. I re-imaged the machine, to re-install the operating system. I re-imaged the machine using a different operating system. I tried all of this again on a different machine. No joy. I was stymied.

I had long ago removed all the T-SQL code from this script that wasn't absolutely necessary, so now I started to re-write the script, line by line, using different keywords, constant values, and variable names. (Yes, by now I was on a mission.) Finally, I discovered the cause: it only happened when the script contained the SYSNAME datatype: changing it to NVARCHAR(128) fixed it! Hurray!

But wait: that doesn't make any sense. As BOL points out:

SQL Server 2005 Books Online (November 2008)
... sysname is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers; therefore, it can vary between instances of SQL Server. sysname is functionally equivalent to nvarchar(128). (Emphasis added.)

If sysname and nvarchar(128) are "functionally equivalent", why would changing from one to the other fix the error message? Well, a little farther down in BOL, there's this:

In databases that are case-sensitive, or that have a binary collation, sysname is recognized as a SQL Server system data type only if it appears in lowercase.

So, that was the problem: I had used SYSNAME instead of sysname.

Actually, the root cause was having collation specifiers spread all higgledy-piggledy on our databases, tables, and columns, sometime specified, sometimes not. Somewhere, one of them had a case-sensitive collation, and that's what caused the whole problem.

Tuesday, March 23, 2010

Rant: Table Names Should be Singular!

There's two main schools of thought on how tables should be named: as singluar, or as plural. For me, the deciding factor is clear: English pluralizes nouns inconsistently!

Today, for example, I had two tables to create that were identical in all respects (don't ask). So, I created a fair amount of code to create the first table, with its assorted indexes, constraints, triggers, extended properties, etc. So far, so good.

Next, it's just a simple copy-and-paste, followed by a find-and-replace, to create the second table, right? No - not if the first table is named "Equipment", and the second is named "Tractors". Here's the problem.

This is a snippet of the "Equipment" table's code:

    EquipmentID INT,

So let's copy-and-paste and find-and-replace it, to create the "Tractors" table:

    TractorsID INT,

See the problem? "TractorsID"?? That ain't right - "TractorID" is what we want, obviously. But to get it, we have to edit each mistake by hand. So what should have been a five-second task is now an aggravating five-minute task.

The culprit in this example is the noun "equipment": what's the singular of "equipment"? "Equipment"? "Piece of equipment"? "Equip"? Although it's linguistically grotesque, this table would be better named "Equipments", just for the sake of clarity and consistency. Or, another noun, which pluralizes the same way as "tractor" does, should be used: "machine", maybe?

The root of this problem is that there are so many different ways to pluralize English nouns. According to this article, the most common way is to append an "s": one duck, two ducks. Or sometimes it's an "es": one potato, two potatoes. Or maybe an "ies": one pony, two ponies. Possibly a "ves": one calf, two calves.

And we haven't even gotten to the irregular plurals. Some nouns are the same when pluralized: one fish, two fish. Some add an "en": one ox, two oxen. Some add an "ren": one child, two children, or one brother, and some brethren.

Sometimes there's a mid-vowel change: one goose, two geese, one louse, two lice. Also, one crisis, but two crises.

And English has borrowed a lot of words from Latin and Greek, so we have: alumna/alumnae; matrix/matrices; criterion/criteria; phalanx/phalanges; addendum/addenda; hippopotamus/hippopotami; and schema/schemata.

Not to mention loan-words from: Hebrew (seraph/seraphim); French (château/châteaux); and Italian (graffito/graffiti).

Also, my personal favorites, nouns that don't even have a singular form: equipment, clothes, amends, etc.

There's much more, but the point is, name your tables as singular, not plural. Then the whole issue goes away!

Monday, March 22, 2010

Using sp_MsForEachDb and sp_MsForEachTable Together

Ever wanted to run a T-SQL command on every user table in every user database? Here's how to nest the undocumented sp_MsForEachTable system stored procedure inside the undocumented sp_MsForEachDB system stored procedure.

      'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''''#'''' AND owner_sid = 0x01) RETURN '
    + 'RAISERROR(''''Reclaiming space on table ' + QUOTENAME('#') + '.?'''', 10, 1) WITH NOWAIT '
    + 'USE ' + QUOTENAME('#') + ' '
    + 'DBCC CLEANTABLE(''''#'''', ''''?'''', 10000) WITH NO_INFOMSGS '

SET @Sql = 'USE ' + QUOTENAME('#') + ' EXEC sp_MsForEachTable ''' + @Sql + ''''

EXEC sp_MsForEachDb @Sql, @replacechar = '#'

The trick is the @replacechar parameter to the sp_MsForEachDb stored procedure: it's how we keep the placeholders for the database and the table separate. If there's an easier way, I'd love to see it.

Sunday, March 21, 2010

Do MDF Files Get Defragged?

When you run a disk defragmenter, such as the built-in Windows one, or a third-party product, do your MDF and LDF files get defragmented if SQL Server is running? I get asked this question fairly often by "reluctant DBAs".

(Be mindful that we're talking about disk-level fragmentation here, not database-level, such as "logical" or "external" index fragmentation - that's another thing altogether.)

First, defragmentation of the mdf and ldf files should – in a ideal world - rarely be an issue. If you remembered to defragment the disk first, and this gave you a large enough empty space to hold the entire ?df file, and if you created the database files big enough to handle all the data you'll ever need to store, and you made sure that "AutoShrink" is turned off... then you wouldn’t need to worry about disk fragmentation. That's a lot of if's, and anyway, the database may grow bigger than you predicted, and be forced to auto-expand; that’s when disk fragmentation begins. So, we can't really avoid the issue.

Second, will the disk defragmenter work on "open" files, like our ldf and mdf files? Yes. A disk defragger works at a lower level than the file system: it knows nothing of file locks, streams, "open" files, etc., so the fact that the MDF file is open is immaterial.

Third, is it safe? Well, Microsoft hardy ever uses words like "perfectly safe" and "CANNOT" - but in this case, they do:

  • "Given that file defragmentation is integrated in the operating system, defragmenting open files is perfectly safe." ( )
  • Microsoft's documentation for NtFsControlFile( ) states, "[NtFsControlFile( ) internals] are implemented such that you CANNOT corrupt data on your drive by using them."

Of course, defragmenting can slow any disk-intensive application, including SQL Server. Microsoft says, "I/O activity generated must be considered if continuous high performance is mandatory." ( ). Some defraggers may detect disk I/O activity and "get out of the way". Generally, though, you’d want to defrag the disk during maintenance hours or low usage times.

So, defragment your hard drive (to create a big empty space), and then expand your database (hopefully forcing the OS to move the mdf and ldf files to that big, empty space). Make sure "AutoShrink" is off. And remember that a big empty database will normally be faster than a small full one.

Saturday, March 20, 2010

Is SQL Server's 2005's Exception Handling an Improvement?

At first, I was excited to read about the new TRY...CATCH exception handling in SQL Server 2005, but it feels like it has a serious flaw: only the last error message is returned in the Exception object!

This is really annoying, both during development and in production. For example, if you make a mistake while trying to create a CONSTRAINT:

-- Setup.
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblInvoice')
   DROP TABLE tblInvoice

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblCustomer')
   DROP TABLE tblCustomer

CREATE TABLE tblCustomer
   CustomerID INT,

   InvoiceID INT,
   CustomerID INT,

Under SQL Server 2000, we get complete error information:

ALTER TABLE tblInvoice
   ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID)
   REFERENCES dbo.tblCustomer(CustomerIDDDDD)    -- Error!

Msg 1770, Level 16, State 0, Line 18
Foreign key 'FK_tblInvoice_tblCustomer' references invalid column 'CustomerIDDDDD' in referenced table 'dbo.tblCustomer'.

Msg 1750, Level 16, State 0, Line 18
Could not create constraint. See previous errors.

But under SQL Server 2005's exception mechanism, the error message for this code is almost useless:

   ALTER TABLE tblInvoice
      ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID)
      REFERENCES tblCustomer (CustomerIDDDDD)

Msg 50000, Level 16, State 42, Line 24
Could not create constraint. See previous errors.

Microsoft's NaveenP explains ( ):

"When an error is raised in a some special context, the first message has information about the error, while subsequent messages provides information about the context. This becomes an issue inside tsql try-catch. In the absence of any notion about error collection, the catch block is activated with one of the error messages. As Sql Server 2000 would have set @@error to the last error message, we decided to set error intrinsics (error_message(), etc.) to the last error message inside the catch block. In other words Sql Server 2005 would ignore all but the last error message inside [the] tsql try-catch... Backward compatibility prevents us from mergeing old error messages."

Well, shoot.

Thursday, March 18, 2010

A Clustered Index Does Not Guarantee Order

Learned something the hard way recently: just because a table has a clustered index doesn't mean a SELECT without an ORDER BY will return the rows in order. For example, given this table:


... if you say:


... then the rows will be returned in undefined (that is, random) order.

"Clustering", I've learned, is a physical construct, not a logical one. The rows are laid out in physical order on the hard drive, but SQL Server is free (in the abscence of an ORDER BY clause) to return them in whatever order it thinks most efficient.

As developers, we don't usually see this behavior, because we tend to have fresh, well-maintained tables. In fact, I only discovered it in a customer's database that hadn't had any maintenance done to it in years. So, the bottom line is, as always, "no ORDER BY, no ordering."

Wednesday, March 17, 2010

One TRUNCATE TABLE Can Use Up All Your Locks (in SQL Server 2000)

Here's an example of how you can learn more from bad schemas than good schemas. This happened to me under SQL Server 2000, and I'm wondering if anyone has seen it on later versions. (post hoc - Sankar has demonstrated that this behavior has changed as of SQL Server 2005.)

I was attempting to truncate a table with maybe 100 million rows (don't ask - that's the bad schema part). When I issued the TRUNCATE TABLE command, however, I got:

Error: 1204, Severity: 19, State: The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

What I've learned from is that "almost all of the locks acquired for a TRUNCATE statement are for extent deallocations". I didn't even know TRUNCATE TABLE used locks, but it makes sense now, of course. I ended up dropping the table and re-creating it.

Tuesday, March 16, 2010

Transactions Don't Work the Way You Think

(Just a little note for myself; sometimes I want to explain XACT_ABORT, but I never have the example fresh in my mind.)

We were all trained that a transaction is a "all-or-nothing" thing: either all the statements succeed and are all committed, or none are committed. It doesn't work that way by default in SQL Server, though:

SET XACT_ABORT OFF     -- This is the default.
--SET XACT_ABORT ON    -- This is NOT the default.

CREATE TABLE DatabaseProperty

INSERT INTO DatabaseProperty
 (sName, sValue)
 ('Fred', 'This is tooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo long!')

INSERT INTO DatabaseProperty
 (sName, sValue)
 ('Barney', 'Will be added if XACT_ABORT is OFF, which is the default!')


This is why XACT_ABORT is usally set to ON, instead the default ("OFF").

Monday, March 15, 2010

Using SSMS Templates for Fun and Profit!

The "best practices" concept is truly a great thing, especially when applied to T-SQL code. But does your best practices process amount to (a) carefully writing down the things you know you should do, and then (b) not having time to do them? If so, my next few posts will describe an easy way to capture and re-use your T-SQL best practices, saving time, improving code, and reducing bugs. No, really.

In this post, I'll walk through how to use one of the least-appreciated features of SQL Server Management Studio: the "Template Library." Future posts will teach you how to leverage SSMS Templates to create a flexible, personal "toolbox" of your debugged, error-checked, optimized, commented, and otherwise perfected T-SQL code snippets. (Post hoc: You can now download my library of templates from )

What's a Template?

Templates are simply Notepad-editable text files that have an "sql" extension: "SQL scripts", in other words. What makes them special is that they contain zero or more parameters, and reside in certain "well-known" folders.

Ok, What's a Parameter?

This is one of those things that's easier to demonstrate than explain. Here's a very simple line of code with a parameter in it:

DROP TABLE <table_name, sysname, your_table_name>

As you can see, a parameter is just a parameter_name, a data_type, and a default_value, surrounded by angle brackets. Actually, it's even easier than that: the parameter_name can be made readable by using upper- and lower-case letters, spaces, and (most) punctuation; the data_type doesn't actually do anything; and the default_value is optional. So this is also a legal parameter:

DROP TABLE <Table Name,,>

Of course, including a default_value is usually a good idea, if only to jog your memory. Even better, since data_type doesn't do anything (I call this "meekly"-typed), we could conceivably use it for documentation:

DROP TABLE <Table Name, Must begin with "tbl"!, tbl>

So far, using Templates might look like more work than it's worth. And it might be, except... Microsoft has written a bunch of them for you!

So How do I Use a Template?

To see the available Microsoft-authored Templates, we use the "Template Explorer" window, via the "View, Template Explorer" menu item. The familiar tree-of-folders-and-files control appears, with the Templates arranged into folders based on database object type. For example, here's the Create Unique Nonclustered Index Template from the Index folder:

-- ===================================
-- Create Unique Nonclustered Index
-- ===================================
USE <database_name, sysname, AdventureWorks>

CREATE UNIQUE NONCLUSTERED INDEX <index_name,sysname,AK_EmployeeAddress_rowguid> 
ON <schema_name,sysname,HumanResources>.<table_name,sysname,EmployeeAddress> 
 <column_name,sysname,rowguid> ASC
ON <file_group,,[PRIMARY]>

EXEC sys.sp_addextendedproperty 
 @value=N'<description_index,string,Description of index>' ,

As you can see, there are several parameters in this Template; if there's a limit, I've never run into it. It's important to understand that the parameters describe simple text-replacement: they know nothing of T-SQL. This means they can be used to parameterize anything: database object names, text inside quotes, text in comments, portions of object names, fragments of T-SQL code, etc.

Let's see how you use this Template to create a unique nonclustered index. First, open a new (or existing) SQL file. Now drag-and-drop the Create Unique Nonclustered Index Template from the Index folder to the desired location in the editor window; this pastes the Template's contents at the drop location.

Now comes the fun part: press the Ctrl-Shift-M chord, or use the Query, Specify Values for Template Parameters menu item. This will cause SSMS to pop this dialog:

This dialog is what makes Templates so useful. There's one row for each parameter, and the columns are the variable name, data_type, and default_value for each one. To replace all the parameters in your SQL document, you enter values (or accept the defaults) for each row. (Naturally, the left and center columns in this dialog are read-only.) Once you're happy with the values you've entered, click the "OK" button, and they will be substituted for the Template parameters, resulting in T-SQL code like:

-- ===================================
-- Create Unique Nonclustered Index
-- ===================================
USE AdventureWorks

ON HumanResources.EmployeeAddress 
 rowguid ASC

EXEC sys.sp_addextendedproperty 
 @value=N'Supports the Employee Address Report for Marketing - see Pam for details' ,

Some things to note about this dialog box:

  • Each variable-name appears once, no matter how many times it's used in the Template's T-SQL code.
  • Don't allow two parameters to have the same variable-name but different data-types, because it won't work: only the first parameter for a variable-name appears in the dialog.
  • You can use the tab key to navigate, but it's faster to use the down arrow after entering each value.
  • Drag-and-dropping copies the contents of the Template to the editor window.
  • Double-clicking opens a new window, and copies the contents of the Template to the editor window. However, this is not how you edit a Template.
  • To edit a Template, right-click on it, and select the "Edit" menu item.
  • This is, for some reason, a modal dialog, so be sure to have whatever text you need available to you (on the clipboard, in an open text file, etc.) before popping it.
  • Be sure not to use extra commas or angle brackets in a parameter, as this usually confuses the parser.

Where are the Templates?

This can be a little confusing because of the way SSMS behaves. There are two locations for the Microsoft-authored Templates: a "factory-installed" one for SSMS to use, and a user-specific one:

  • The original, "factory-installed" copies can be found in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql for SQL Server 2008, or in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql for SQL Server 2005. The Templates in these folders cannot be changed via SSMS; they will always be preserved in their original state.
  • The location of the local, user-specific copies depends on your version of SQL Server and your operating system. Using the %APPDATA% Windows environment variable (which makes it operating system independent), and knowing that SQL Server 2005 is "version 9", and "SQL Server 2008" is "version 10", we can represent the local Template folder's name compactly as:
    %APPDATA%\Microsoft\Microsoft SQL Server\{SQL Version}0\Tools\Shell\Templates\Sql

The difficult thing to remember (for me, at least) is that what you're seeing in the "Template Explorer" window is your local files, not the original ones. So, edits to the these Templates will be saved to your local Template folder.

Every time it runs, SSMS makes sure that each user has a copy of some kind of all the Microsoft-authored Templates. It does this by comparing the files and folders in the original folder to each user-specific folder. If it finds any missing in the user-specific folder, it copies them from the original folder.

The trick is that the comparison is on existence only: the actual contents of the files are not examined. This allows SSMS to install (and repair) from the original Templates, but also lets the use modify their local copies without SSMS overwriting them with original files at the next program run.

So, if you edit a Template, your changes will remain forever. But, if you delete a Template, you'll find it's re-appeared at the next program run. (If you delete both the original and local Templates - which you should not do - SSMS won't be able to perform this copy, because it won't be able to find the file.)


Using SSMS Templates is less error prone than typing code by hand, faster than using the GUI, and makes it possible to add documentation while it's still fresh in your mind. The only thing better would be if you could create your own Templates. That will be the subject of a future post.