THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Saving a Few Lines of Code by Performing Multiple Operations in a Single Command

I was in Mike Byrd's session on in-memory technologies the other day and I saw him do something I had never seen and it prompted me to write this blog today. It has nothing to do with in-memory, as the title notes, but has to do with stacking commands in a statement I had not seen before. This was SET STATISTICS. For my entire career, I have put them each on an individual line:

--create a table for the demos
CREATE TABLE dbo.Table1
(
    Table1Id    int PRIMARY KEY
);
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;
SET STATISTICS XML ON; --Not typical to use both of these, I suppose but it is possible!
GO
SELECT *
FROM   Table1;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS PROFILE OFF;
SET STATISTICS XML OFF;

But then I saw Mike use the following syntax (sans PROFILE and XML):

SET STATISTICS IO, TIME, PROFILE, XML ON;
GO
SELECT *
FROM   dbo.Table1;
GO
SET STATISTICS IO, TIME, PROFILE, XML OFF;

Wow, that is a lot easier! (And yeah, using SQL Prompt I can set a snippet to take away typing, but this saves space in any case.)

-------------------------

Another command that I often use with commas is a DROP (OR DROP IF EXISTS) command. For example, say we add a Table2 object to the example (including a FOREIGN KEY constraint to help make a point):

CREATE TABLE Table2
(
    Table2Id    int PRIMARY KEY,
    Table1Id    int REFERENCES dbo.Table1 (Table1Id)
);


Now, if we execute the following:

DROP TABLE Table2, Table1;

The tables will be dropped. However, it turns out that this method of executing DROP TABLE is not really a single executing command. If we recreate the tables and drop the objects in the wrong order (due to the reference from Table2 to Table1), we see an error:

CREATE TABLE dbo.Table1
(
    Table1Id    int PRIMARY KEY
)
CREATE TABLE dbo.Table2
(
    Table2Id    int PRIMARY KEY,
    Table1Id    int REFERENCES dbo.Table1 (Table1Id)
)
GO

DROP TABLE dbo.Table1, dbo.Table2
GO

This causes the following error message to be thrown:

Msg 3726, Level 16, State 1, Line 18
Could not drop object 'Table1' because it is referenced by a FOREIGN KEY constraint.

For almost any command, you would expect it to behave as an atomic operation. However, in this case, it does not. Table2 is actually dropped:

SELECT name
FROM   sys.tables
WHERE  name in ('Table1','Table2')

name
----------------------
Table1

So while it is an excellent tool for saving a few keystrokes, it will not save you from the mistakes of malordered objects any more than:

DROP TABLE Table1;
DROP TABLE Table2;

For this, you would need to do something with transactions, most simply something using a TRY CATCH block:

--Recreate Table2, again
CREATE TABLE dbo.Table2
(
    Table2Id    int PRIMARY KEY,
    Table1Id    int REFERENCES dbo.Table1 (Table1Id)
);
GO

BEGIN TRY
    BEGIN TRANSACTION;
    DROP TABLE dbo.Table1;
    DROP TABLE dbo.Table2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH;

Now you get the error message, and dbo.Table2 is not dropped. But realistically, most of the time when we would be using a comma delimited list of tables to drop, I expect it is in an ad-hoc manner, and something like the following is good enough:

DROP TABLE IF EXISTS dbo.Table1, dbo.Table2;

Now simply repeat until no error messages and the tables are gone! The only downside with this command is that if you spell the table incorrectly, you may go slightly mad.

Published Thursday, January 19, 2017 7:49 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

change the order to

DROP TABLE dbo.Table2, dbo.Table1

to avoid the foreign key constraint

January 23, 2017 1:17 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement