Someone Has a Sense of Humor

Found this in Microsoft's sql.h file - line 535:

/* SQL_OJ_CAPABILITIES bitmasks */    /* NB: this means 'outer join', not what you may be thinking */

DELETE FROM FROM ?

I ran across this today:

DELETE Customer
  FROM Customer, Invoice
 WHERE Customer.CustomerIndex = Invoice.CustomerIndex

Despite how odd it looks at first glance, it is legal SQL (at least, extended T-SQL). The oddness is because the DELETE statement can have two optional FROM clauses: in the code above, we haven't used the first one, but have used the second.

The first FROM clause "is an optional keyword that can be used between the DELETE keyword and the target table_name..." (BOL). The second FROM allows you to specify data from another, JOINed table, and delete corresponding rows from the table in the first FROM clause.

Personally I think writing this statement as:

DELETE FROM Customer
  FROM Invoice
 WHERE Customer.CustomerIndex = Invoice.CustomerIndex

... would probably be a lot clearer.

Average Size of Data In A Column

A script to return the average size of the values in a column. It also provides a simple histogram of the data distribution.

SELECT AVG(DATALENGTH(MyColumn)) AS 'Avg Size'
  FROM MyDB.dbo.MyTable

SELECT AVG(DATALENGTH(MyColumn))           AS 'Avg Size',
       FLOOR(LOG10(DATALENGTH(MyColumn)))  AS 'Log',
       COUNT(*)                            AS 'Count in Log' 
  FROM MyDB.dbo.MyTable
 GROUP BY FLOOR(LOG10(DATALENGTH(MyColumn)))
 ORDER BY FLOOR(LOG10(DATALENGTH(MyColumn))) DESC

A Good Habit

Most of us have a standard set of commands we place at the start of any script we write. Common ones are SET NOCOUNT ON and SET ANSI_NULLS ON. Of course, there are countless settings you could employ "just in case", but knowing your database's default setup can usually guide you.

I've just learned of another setting, though, that could be used to program defensively in certain environments:

SET ROWCOUNT 0

This statement resets the number of returned rows back to "all of them", and protects your script from being called from somewhere that had ROWCOUNT set to a value, say, 100 rows, but never got reset. This is overkill in most cases, but in other cases, remember that just because you're paranoid doesn't mean they're not out to get you.