Aug 092011
 

T-SQL Wednesday!Howdy folks and Happy T-SQL Tuesday Wednesday! Contrary to popular belief, both myself and my blog are not dead, it’s just that preparations for my upcoming wedding have taken up nearly all of my spare time. Once I return from my honeymoon I hope to be back to blogging as usual!

This month’s topic comes from none other than the founder of the T-SQL Tuesday movement, Adam Machanic (blog | @AdamMachanic). Adam’s asking us to write about what he so eloquently describes as “crap code”. We’ve all made poor coding and/or design choices at one pont or another, and even if it seemed like a good idea at the time it might still look bad in hindsight because we’ve all learned better ways to do things as we have progressed in our careers. This topic left me faced with a tough decision as I’ve definitely made my share of bad decisions whether I knew better at the time or not. I decided to go with an oldie-but-goodie that still haunts me to this day.

A while back I wrote about how my first job working for a city is what got me started working with databases. I worked for the building department and designed a database for storing information about building permits, contractor licenses and inspections. The decision to learn how to build such a database was a great one, but a few of the other decisions I made shortly thereafter were much less than great when I look back on them now.

I chose the wrong RDBMS
When I was getting started back in 2002 I was trying to decide between using Microsoft Access and MSDE 2000. I chose Access since it had a GUI, and of course it had to be better since it had a GUI, right? Since this application is still in use and I still maintain it, it goes without saying that I’m now kicking myself. MSDE may have caused me a bit more pain up front, but the upgrade path to SQL Server would have been much simpler than it is from Access, which is why I haven’t bothered to convert it yet.

I tried to be fancy
In Access if you want to create a table with an identity column, you use a datatype called “AutoNumber”. AutoNumber columns have 3 options:

– an incrementing value (like an identity)
– a completely random long integer value
– a replication ID (GUID)

Which did I pick? Surprisingly not the GUID, but I did pick the completely random value, which isn’t much better. Fortunately all of my AutoNumber columns contain key values that are never seen in the application, but every time I open up a table I’m horrified at the randomness I see. Why’d I pick it? I really don’t know. I have a strange feeling my high school brain thought random values would be way cooler than incrementing ones. Obviously I hadn’t learned the concept of index fragmentation yet.

So there you have it – 2 decisions I made that seemed like good ones at the time yet turned out to be anything but. If I were re-writing this system from scratch it would be way better than it is now. Thanks, Adam, for the most excellent topic!

Jul 272010
 

Do you like it when your schemata are easy to understand and maintain?  If so, keep the following in mind when choosing names for tables and columns.  If you’re feeling evil and want to inflict some frustration on others, this might give you some good ideas too…

1.  Data types make horrible names

There’s no rule that says you can’t name a column after a datatype – it will just be awfully confusing.  The following code works perfectly:

create table INT (
   char datetime not null,
   foo int null,
   bit bit null,
   timestamp datetime null
);

The birth of the DATE datatype in SQL 2008 definitely throws a monkey wrench in the works as well.  How many columns do you know of that are named “Date”? (More on that in a bit.)  Again, it’s not going to break anything, I just find it rather confusing when a column of type “datetime” is named “timestamp”.

2.  So do reserved keywords

Interestingly, SQL Server datatypes are not found on the list of T-SQL Reserved Keywords.  The rules for T-SQL identifiers state that a regular identifier cannot be a T-SQL reserved keyword.  “Regular identifiers” are those which do not require brackets or double quotes around them.

-- This will fail
CREATE TABLE FOO (
  add CHAR(5),
  between INT,
  restore SMALLDATETIME
);

-- Add brackets and it works just fine!
CREATE TABLE FOO2 (
  [add] CHAR(5),
  [between] INT,
  [restore] SMALLDATETIME,
  dump VARCHAR(10) -- DUMP is a reserved keyword but doesn't need brackets
     -- probably because it is discontinued in SQL 2008.
     -- maybe it should be removed from the list.
);

3.  Pick a good name length

Bad NameA good name should be long enough to be descriptive, but short enough that it’s not a pain to type.  I hate columns or tables with names like “Date” or “Name”.  Chances for confusion can easily be lowered by adding another word to make it more descriptive, such as “PurchaseDate” or “FamilyName”.

Table and column information is stored in the sys.tables and sys.columns tables.  The name values are stored in columns of the data type sysname, which since SQL Server 7 has been equivalent to nvarchar(128). This is one of the cases where adding quotes or brackets can’t help you break the rules. Names cannot exceed 128 characters in any case, and temporary tables are a special case as they can’t exceed 116.

4.  Avoid spaces and special characters

They’re allowed, but I consider it a bad practice to use them.  Most special characters are not included in the rules for regular identifiers, meaning that you’ll need to enclose the name in double quotes or brackets.

-- To be really tricky, you can start a name with a leading space!
CREATE TABLE [test^one](
   foo INT NOT NULL,
   [ bar] varchar(10) NULL  -- This is evil
);

5.  They can be case-sensitive depending on collation

Case sensitivity in object names depends on the database’s collation settings.  If it is case-sensitive, then object names will be unique based on case sensitivity as well.

-- This will fail in a case-insensitive database
-- but runs fine in a case-sensitive one
create table testing1 (
   char datetime not null,
   foo int null,
   bit bit null,
   FOO int null,
   timestamp datetime null
);

create table Testing1 (
   char datetime not null,
   foo int null,
   bit bit null,
   FOO int null,
   timestamp datetime null
);

6.  Don’t pick names that will change meaning

The concept of a name that changes meaning might not make a whole lot of sense, so I’ll elaborate with a short story.  In a previous job, we had to maintain a table that stored historical information for the previous 10 years.  Said table had 11 columns:  Key, Year0, Year1, Year2,…,Year9.

Year0 was always the current year, Year1 was last year, etc, so each year the columns changed meaning as far as what calendar year they really referred to.  There was also a special job that had to be run once a year to shift all the data one column to the right.  This is more than just bad naming, it’s a horrible design to begin with!  We knew there was a much better way, but were stuck with this schema due to legacy application support.

In conclusion, a little thought when choosing table and column names can go a long way. I hope this is helpful!