SQL Yesterday vs. SQL Today

Quick Tip: Use the ANSI Standard Join syntax in all of your queries.

I've been writing SQL for abotu 20 years now, and have been forcing my hands to type the new code. It's not just a good idea; it's now critical to performance. I've talked to hosts of people who claim that their performance has improved by changing format from the old syntax (where joincol = joincol) to the new syntax (ON joincol = joincol) ... I'm sure the "Why" has been documented / blogged about, so I'll just leave the top & a sample correction here...

Reproduced here with the client's permission:

SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities, dbo.Trips Trips, dbo.TripNotes Tripnotes, dbo.Notes Notes
WHERE
Trips.dfac = Facilities.code AND
Tripnotes.job = Trips.job AND
Notes.code = Tripnotes.note AND
Trips.tdate = Tripnotes.tdate AND
(
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1
)

And recommend it be rewritten like this:

SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities join dbo.Trips Trips on
Trips.dfac = Facilities.code JOIN
dbo.TripNotes Tripnotes on
Trips.job = Tripnotes.job AND
Trips.tdate = Tripnotes.tdate JOIN
dbo.Notes Notes ON
Notes.code = Tripnotes.note
WHERE
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1

Common Table Expressions (CTE) in SQL Server 2005 & 2008

From 2008 Books Online:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

The really cool part of this is the self-referencing part.

I'm not going to try to teach you CTE here. Honestly, it's been done too well already. Here's a link to a favorite:

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

It does reference a few other documents (like I did with books online, above), again so as not to reinvent the wheel.

I wanted to share a couple of limitations in CTEs that I encountered this past week.

Requirement: Unlimited self-referencing / recursion in object definitions.

With the self-referencing objects, the problem that you have is the circular reference. With a CTE, you can limit this by specifying a recursion limit (i.e. "circle no more than X times").

Is this a good idea? Does it make sense to circle multiple passes through a set of objects once you've already collected all of the objects?

We ended up (in SQL 2005) using a temp table implementation (well, really, memory table not temp table). The reason: we needed to be able to say, "grab the next set of child relationships, but ONLY if we haven't already grabbed those relationships." The CTE limitation in sql 2005 doesn't allow mulitple recursive references.

Imagine my delight when I read the above: "...can be referenced multiple times in the same query,,," ... this in and of itself is enough to talk this particular client into going to SQL Server 2008.

Happy CTEs...

SQL 2008 new features for developers

I was recently asked to outline a "New Features for SQL 2008 for Developers" course... this is not strictly perormance related, and you can certainly get this list from other sources... and if you want detail, go to microsoft.com... and in fact, if you want detail, you'll have to go there... but if you're interested in a concise list, here it is:

SQL Server 2008 New features for developers
3 days
Topics:

- Auditing
- Change tracking
- Resource governor
- Compressed data and indexes
- Filestream storage
- Partition switching
- Sparse columns
- Spatial data storage
- Wide tables
- New data types
- Full text search enhancements
- Compound operators
- Grouping sets
- Merge statement
- SQL Dependency reporting
- Table-values parameters
- T-SQL Row Constructors
- Filtered indexes
- Data encryption enhancements
- LINQ

(For more detail, check out our training web site later, www.soaringeagle.com/training)