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:

CREATE TABLE Equipment
(
    EquipmentID INT,

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

CREATE TABLE Tractors
(
    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.

DECLARE @Sql NVARCHAR(4000) = 
      '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." ( http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx )
  • 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." ( http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx ). 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,
)

CREATE TABLE tblInvoice
(
   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:

BEGIN TRY
   ALTER TABLE tblInvoice
      ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID)
      REFERENCES tblCustomer (CustomerIDDDDD)
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE()
END CATCH

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

Microsoft's NaveenP explains ( http://blogs.msdn.com/sqlprogrammability/archive/2006/04/03/567550.aspx ):

"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.