Michael J. Swart

August 26, 2010

Ten Things I Hate to See in T-SQL

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 11:35 am

So here’s my top ten list of things that I see in written in SQL that I dislike or that irk me in some way. And yeah, in some cases, I would use the word hate.

Tweedledum's fury.


They’re not necessarily things that go against best practices. But each one gets under my skin in some way and I thought I’d share that list at the risk of giving my enemies buttons to push.

So without further ado.

10. Code that’s Been Commented Out

It’s in every set of code I’ve ever worked with and I’m sure you’ve seen it too. It’s not even SQL Server specific, but you’ll see it in any code that changes over time. The code that get’s commented out instead of deleted.

I’m guessing that the reasoning behind this is that some developers want to keep code around to make reverts easier, or to keep a sense of history of the code. But that’s what source control systems were built for!

9. The isDeleted Column

Deleted records aren’t deleted. Look, they’re right there!”

You sometimes see this. Records that are marked as deleted with a flag. It’s sometimes needed to support an undelete feature but it still bugs me.

It’s also confusing when looking at procedures called s_OBJECT_Delete(). Without looking at the definition, does this procedure delete the record, or just mark it as deleted?

8. Fluff in Object Definitions

When I create a table from scratch like:

	Value1 NVARCHAR (100) NOT NULL,
	Value2 NVARCHAR (max),

I can get SQL Server to script out the table for me later on and it will look like this:

/****** Object:  Table [dbo].[MyTest]    Script Date: 08/25/2010 19:08:29 ******/


CREATE TABLE [dbo].[MyTest](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Value1] [nvarchar](100) NOT NULL,
	[Value2] [nvarchar](max) NULL,
	[Id] ASC


All the added stuff is marked in green. This is stuff that is either optional, or that specifies an option that happens to be the default. This is okay, because SQL Server didn’t know if the table was created with options left as the default, or whether they were explicitly set. So the scripter spells it out.

And that’s totally fine. I just get peeved when some of this stuff makes it into checked-in object definitions.

7. Filter in the JOIN Clause Instead of the WHERE Clause

Look at this query:

SELECT p.name, COUNT(1)
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p
	ON sod.ProductID = p.ProductID
	AND p.Name like 'Sport%'

I’d rather see that filter on p.Name in a where clause:

SELECT p.name, COUNT(1)
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p
	ON sod.ProductID = p.ProductID
WHERE p.Name like 'Sport%'

Of course if you’re dealing with LEFT OUTER JOINS, then it matters where the filter is placed.

6. Concatenating Strings to Build SQL in C#

This is more of a C# gripe than a SQL gripe but it’s my list 🙂

Old C++ developers create strings filled with SQL to be passed to the database. But long statements would get broken up in code like:

string sql = "SELECT name, location " +
    "FROM MyTable " +
    "WHERE id = 12312 " +
    "ORDER BY name";

I hate that format because it makes things impossible to cut and paste. But C# has this nifty string prefix @ that allows newlines inside string literals:

string sql = @"SELECT name, location
    FROM MyTable
    WHERE id = 12312
    ORDER BY name";

5. Concatenating Variables Values Into SQL

Pretty self-explanatory. This practice puts a database in danger of SQL injection attacks.

(Always happy for an excuse to mention little Bobby Tables from xkcd)
Exploits of a Mom

4. Triggers that maintain data in other tables

This is a symptom of a database design that’s not normalized. Essentially, the same data is stored in two places. The development effort needed to maintain both tables properly is often more trouble than it’s worth.

Very closely related is the indexed view. But I have nothing but love for these.

3. Missing Foreign Keys

Missing foreign keys are more common than other problems because there’s no obvious symptom to react to:

  • A new table will support an application just as nicely with or without foreign keys.
  • Software testers will see no error messages coming from the database.

It’s often later on that data integrity becomes a problem, now you’ve got more things to worry about:

  • Adding the foreign key.
  • Cleaning up dirty data.
  • and Public Relations.

Here’s a post I wrote earlier on finding missing keys.

2. Identifiers That Are Also Reserved Key Words

It’s confusing to have column names, alias names or table names that are also keywords. In some cases the syntax is allowed and what’s left is merely confusing.

I often see this pet peeve when columns are named the same as a particular type. like Date or Text. I’d rather see names like CreateDate, LogDate or CommentText. The following script can identify those:

JOIN sys.types t
	ON t.name = c.COLUMN_NAME

1. Column Names That End in a Digit

Now, I don’t mind stuff like AddressLine1 and AddressLine2. They serve a purpose in their own way.

But normally when I see this it smells like unnormalized database design (not denormalized, there’s a distinction). So this is a good litmus test to check to see whether tables are at least in first normal form.

I use this script to check:

select t.name as table_name,
	c.name as column_name
from sys.tables t
join sys.columns c
	on t.[object_id] = c.[object_id]
where c.name like '%[0-9]'
order by t.name, c.name

~ Thanks to xkcd.com and victorianweb.org for sharing their images

August 17, 2010

Microsoft Connect From Our Point of View

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 12:00 pm

I first heard about Microsoft Connect in early 2008. I was at a conference and a speaker from Microsoft touted the benefits of the site. It’s a site that allows users to submit feedback on Microsoft Products (which include SQL Server of course). We were told that every issue on the site was taken very seriously and that stale or unhandled issues get escalated and are treated like a big deal. I remember feeling excited at having this direct line to Microsoft’s product teams.

Today Buck Woody (of Microsoft) posted another article pushing connect.microsoft.com. He’s had other posts on this topic before and thinks it’s a great thing.

Connect as Seen By Microsoft

And it is a great thing especially for Microsoft. They get great feedback:

  • They get bug reports which help improve quality of the released product tremendously.
  • They get suggestions which go into new features of the product. It helps get a sense of what users are really hoping to see.

Connect as Seen By Users

And it’s a great for users because we know we have a say in the product.

Except when we don’t.

There are too many issues where the submitter doesn’t know what Microsoft’s plans are with the issue.

Maybe here’s a reason. They’re hasn’t been a change to the database engine or Management Studio since 2008. And it looks like we’re about a year away from the next one. So any suggested features or fixes submitted for that time are still pending.

And that’s fine, but it means that Microsoft’s only feedback to us is inside the issue itself. Often an issue will have little or no feedback (349116, 361832) and then our perception that we have a direct line to Microsoft disappears.

But those are two isolated cases right? Yes and No. In one of my very first blog posts two years ago How useful is connect.microsoft.com, I took an arbitrary sample of issues. Today, two years later, I look at the same sample*, and I see that 41% still have an “active” status. I’ll repeat what I said in that post:

“If MS had a better track record than that, connect.microsoft.com would be seen as a real place to be heard. That would encourage even more feedback.”

It’s Not a Perfect System

Buck Woody says:

No, it’s not a perfect system, but it’s more than I’ve seen at most software vendors I deal with.

I agree with that. It is a lot better than other software vendors. Oracle is definitely behind in this respect. As far as I can tell their only feedback is their regular support desk. But all that means is that Microsoft is the leader in the race in which no one else seems to be running. And Microsoft could do even better.

What I’d Like to See Done

I don’t want to downplay the good work that Microsoft has done. When we’re told that “Microsoft really does look at these issues” I tend to believe it. I am confident that the SQL Server product teams are putting these features and fixes into the product as we speak**.

But Microsoft should tell us about it. Update the stale/active issues. Mark it as “not reproducible” or “we’ll consider it” or even “won’t fix” you won’t hurt our feelings. In fact we’ll feel better because we’ll feel listened to.

* The sample I took was issues submitted on the 1st and 2nd of May in 2008.

** Some evidence of product fixes via Todd McDermid.

Powered by WordPress