Monthly Archives: September 2012

GOs and Semicolons

In every programming language there are phrases or elements that appear frequently and yet seem innocuous. Two such elements in T-SQL are the GO command and the semicolon.

The GO command tells the SQL server to execute/commit any preceding T-SQL statements. When there is only one statement in a batch, the GO command has no distinct effect. But, in multi-statement batches, the placement of GO could not only be important, it could, in fact, be required. Let’s take a look by walking through an example.

The following three statements, when executed as a batch (all at once), will fail with error message 111, stating that “CREATE VIEW” must be the first statement in a batch. In our example, it is not; it is the second statement, right after we specify the database context.

/* This fails because CREATE VIEW must be the first statement in a batch transaction */
USE [master];
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

To address this error, let’s put a GO after we specify the database context. While that resolves error message 111, running the statements again as a batch will result in error message 156, stating incorrect syntax near the keyword “DROP”.

/* This fails because CREATE VIEW must be committed before continuing in a batch transaction */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

And so, to resolve the second error, let’s put another GO after the CREATE VIEW statement to ensure it is committed before continuing on to the DROP statement. The following statements executed as a batch will succeed.

/* This completes successfully */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
GO
DROP VIEW vwTest;

As the preceding examples demonstrate, the GO command is useful in batch operations to specify points in the batch where prior statements should be completed before continuing to later statements. There are other use-cases, mostly related to ensuring that newly-created objects are in place before subsequently referencing them the batch.

Let’s now shift to a more subtle issue: that of the semicolon. In T-SQL, a semicolon is designated as the statement terminator (similar to many other programming languages). In all SQL Server versions through 2012 (Denali), terminating a statement with a semicolon is optional in almost every instance. However, be aware that it will not be optional in a future release (or, at least so-says Microsoft at this point). Take a look at this TechNet article and see for yourself. On the other hand, the lack of semicolons in the wild is so pervasive that it will be difficult to enforce their usage anytime soon.

Aside from the potential that terminating T-SQL statements with a semicolon might be required in the nearish future, when else might it be needed? There are a few, specific instances, such as in the definition of a common table expression (CTE). A CTE is a T-SQL statement that resolves to a record set which, once defined, can be referenced multiple times, but only within the same scope. Additional information on CTEs is available here, and perhaps will be the subject of a future post within this forum.

Regardless, CTEs require that preceding T-SQL statements are terminated with a semicolon. Let’s take a look.

The following batch will fail with error message 319, stating incorrect syntax near the keyword “with”. The full message is actually uncharacteristically helpful and suggests that it might be caused by a missing semicolon.

/* This fails because common table expression definitions require prior statements to be semicolon terminated */
PRINT 'Start'
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

Now that we’ve seen the error message and confirmed the batch does not execute, let’s add the semicolon and see what happens.

/* This completes sucessfully */
PRINT 'Start'; -- add a semicolon here
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

As you will see, the previous batch executes successfully. While it’s not a good example of using a CTE, it does demonstrate that the semicolon was necessary for the CTE construct. At this point, let’s shift perspective and look at the use of semicolons a little bit differently: what if we provide too many semicolons? Let’s add one to the batch after defining the CTE and see what happens.

/* This fails because common table expressions are only valid within a statement (and a semicolon terminates a statement) */
PRINT 'Start';
WITH cte AS (SELECT 'Col1' = 1); -- add a semicolon here
SELECT * FROM cte
;

In this case, the batch fails with error message 1o2, incorrect syntax near “;”. That error is due to SQL not being able to resolve the “FROM cte” part of the statement. As mentioned previously, a CTE is only valid within the T-SQL statement in which it was defined. Since the semicolon ended the CTE definition statement, the following FROM reference to the CTE is invalid.

So, bottom line regarding semicolons: 1) get in the habit of using them, because they are eventually going to be required, and 2) by using them frequently you’ll develop a natural sense of where statements begin and end, and thus be better able to plan and manage transactions, batches, and, in general, good coding practice.

Advertisements

Find Databases w/o Backup

Imagine you have multiple databases on a server (maybe even hundreds or thousands?), each in full recovery mode, and a scheduled job that performs a log backup every fifteen minutes. All of a sudden the job starts failing. What could be the cause?

Very likely, a database was added to the server and has not yet had a full database backup performed. A log backup can only be performed after a full backup has been taken, since log backups are by definition incremental from a full backup.

How are we going to figure out which of our thousands of databases is missing a backup? Since it must have been recently created, we could simply sort sys.databases by created_date in descending order. However, there is no guarantee that the most recently created database (or any of the top X, for that matter) is, in fact, the one missing the full backup.

The following view will help us find the databases missing full backups. They will be identified by having a NULL value for backup_finish_date. This view extends that logic further to identify any database with an “old” backup as well. In this case, old is defined as over one day ago. Obviously that threshold should be adjusted to suit your environment.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());

Also of note, in this use-case we are excluding databases with a “simple” recovery model since they would not be eligible for a log backup.

UPDATE 08 May 2015: There is a flaw in the query originally presented above. This flaw is exposed through the following use case. If a database is backed up and then deleted, without removing the backup history, the record of it’s back remains. If a new database is created with the same name as the deleted database, the query above will indicate that it has a backup when, in fact, there is none. This is due to the join between databases and backupsets having been solely on the database name field.

To correct this flaw, we can add a second join criterion for the database creation date, thereby ensuring we only consider backupsets that were created after the database itself was created. See updated query below.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND DATEADD(SECOND
            , DATEDIFF(SECOND, CONVERT(DATE, db.create_date), db.create_date)
            , CONVERT(DATETIME2(0), CONVERT(DATE, db.create_date))
            ) <= bak.database_creation_date
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());