DDL and Transactions

When I started in database management many years ago I worked exclusively on Oracle and I got used to the fact that transactions worked with the DML statements SELECT, INSERT, UPDATE and DELETE but that things were different when it came to DDL statements such as CREATE TABLE and DROP TABLE. In short, transactions did not support DDL.

I then started working with SQL Server and for a long time I assumed that the same was true. It came as quite a surprise when I discovered that it was not true and that SQL Server supports transactions for both DML and DDL.

Consider the following T-SQL statements:

CREATE TABLE TestTable1 (ID int NOT NULL)

INSERT INTO TestTable1 VALUES (1), (2), (3)

BEGIN TRAN

DROP TABLE TestTable1

ROLLBACK

SELECT COUNT(*) FROM TestTable1

The answer is 3. After the rollback the table still exists and still has 3 rows it it. The ROLLBACK has successfully undone the DROP TABLE.

Same applies to a rollback of CREATE TABLE. Even TRUNCATE TABLE can be rolled back.

Transactions work by storing before and after images of rows in the transaction log. This log data is then used when undoing transactions in the database.

If I have a table with 10 million rows and I delete all of them using DELETE it takes quite a while as the rows are effectively copied into the transaction log file.

If on the other hand I start a transaction and delete all the rows using TRUNCATE TABLE it happens in an instant, but the roll back still works!  It clearly is not copying all the deleted rows to the log, so how does it work?

The answer is that is still logs data in the transaction log but at a much higher level of granularity. As it deletes rows, empty 8KB data pages are de-allocated and it is these page de-allocations that are logged and not the rows. A very effective and efficient solution.

Parsing Dates can be Trying

SQL Server and dates are often troublesome. In fact every database server I have ever worked on has had trouble with dates and with the parsing of dates in particular. The issue is that different countries have different date formats and that by looking at certain dates expressed as a string it is not always obvious exactly which date it represents. The classic example is that Americans start dates with the month, whereas Europeans usually start them with the day. So for example:

·         ‘01/02/2011’ to a European means 1 February 2011

·         ‘01/02/2011’ to an American means 2 January 2011

Quite often this results in either run time errors or misinterpreted dates. On my SQL Server I just tried:

·         SELECT CAST('12/16/2011' AS datetime) and it returned 16 December 2011

·         SELECT CAST('16/12/2011' AS datetime) and it returned a run-time error

Clearly my SQL Server is configured for US dates.

The SET DATEFORMAT dmy command offers an option to override the behaviour and use European dates, in which case the two SELECT statements above would behave differently.

But what if I wanted to check if a given date was a valid European date. I could run:

SET DATEFORMAT dmy
DECLARE @d datetime

SET @d = CAST('12/16/2011' AS datetime)

This would fail with a run-time error because the date is invalid. But a getting a runtime error is hardly a convenient way to process the date and so I would need to add error handling to recover from the error. One way to do is using a TRY CATCH which would look something like this

SET DATEFORMAT dmy

DECLARE @d datetime

BEGIN TRY

      SET @d = CAST('12/16/2011' AS datetime)

END TRY

BEGIN CATCH

END CATCH

IF @d IS NULL PRINT 'Date error'

This is a lot of trouble just to check a date format and so SQL Server Denali (I am using the CTP3 release) has extended T-SQL to make the parsing of dates a lot easier. In particular a new function TRY_PARSE has been added. For example to explicitly parse a date in UK format use:

DECLARE @d datetime

SET @d = TRY_PARSE ('12/16/2011' AS datetime2 USING 'en-GB')

IF @d IS NULL PRINT 'Date error'

Note that TRY_PARSE will return the date time if the format is good and NULL otherwise. TRY_PARSE is not just for dates and can be used for other country / language specific formats such as currency formats.

There is also a new function called PARSE (without the TRY). This does the same a TRY_PARSE but raises a run-time error if the format is invalid rather than returning NULL.

Finally we also have TRY_CONVERT which is a version of the standard CONVERT function. It too returns NULL if the conversion fails.

All in all we have 3 new T-SQL functions that make the parsing and conversion of data just that little bit easier.