T-SQL or SSMS… Same Thing Right??
by David Dye
While teaching a T-SQL class a few years ago I was fortunate to have our training center's Office applications instructor, Johnnie Lewis, in attendance. In the first lab, within an hour of the class starting, Johnnie discovered the query designer in SQL Server Management Studio, SSMS, and announced to the entire class, "Hey David!! Look at this! SQL has a query builder just like Access!!" In sheer panic I announced, "Well, it's a little different. We really don't cover that in this class though", but the damage was already done and the bell could not be un-rung. The class immediately converged and surrounded Johnnie's work area to find where the query designer could be found and the remainder of the three day class was spent answering, "How do you do that query in the query designer". After joking with Johnnie about his discovery and his class room announcement and informing him any such future declarations could leave me unemployed, he asked the sincere question, "Why should I learn and use T-SQL when I can do the same thing in management studio?". This question was posed many times before Johnnie's earnest inquiry and, although I knew that there distinct differences and advantages to using t-sql I failed to educate myself as to the when and why. I was taught that using t-sql over a graphical user interface assured more control and efficiency, but I had had not made an effort to verify what, if any, difference there was between my t-sql code and what SSMS was doing in the background. After I began testing the statements passed by SSMS I discovered that the disparity between efficiently written t-sql and what SSMS passed lay mostly in manipulating objects from the object explorer so that is what I will focus on in this post.
I originally ran the below examples on enterprise manager so when I began to write this I found some subtle differences between SSMS 2005 and 2008, so I will demonstrate the behavior of both. The examples require running a default trace from SQL Server profiler, either 2005 or 2008, to capture the commands that SSMS are passing to the database engine, I would also recommend filtering on the database id or name to keep overhead to a minimum. Once the trace is running create a table using t-sql that contains a single column with a data type of integer:
USE AdventureWorks
CREATE TABLE test(
col1 INT NULL);
GO
ALTER TABLE test
ALTER COLUMN col1 FLOAT;GO
but for those not familiar or used to altering tables then why not use SSMS table designer? From SSMS 2005 I can easily browse object explorer for the newly created test table, right click it and choose Design to open the table designer. In the design tab select the float data type from the drop down and choose to save the table and voila the change has been made. It would appear that SSMS passed the ALTER TABLE command, but in fact the DDL statement used is quite different when examining the profiler trace. The first action is SSMS creates a table called Tmp_test that is our test table with col1 as a float data type:
CREATE
TABLE dbo.Tmp_test(
col1 float(53) NULL
) ON [PRIMARY]
Next dynamic sql is used to insert all data from our test table into the new
Tmp_test table:
IF
EXISTS(SELECT
* FROM dbo.test)
EXEC('INSERT EXISTS(SELECT
* FROM dbo.test)
INTO dbo.Tmp_test (col1)
SELECT CONVERT(float(53), col1) FROM dbo.test WITH (HOLDLOCK TABLOCKX)')
Then our original test table is dropped and the Tmp_test table is renamed to
test:
test:
DROP
TABLE dbo.test
TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test',
N'test',
'OBJECT'
N'test',
'OBJECT'
The difference is quite obvious and the overhead that this simple alter can incur is staggering. I intimated that SSMS 2005 and 2008 differ in their behavior, so let’s look at SSMS 2008 now. Follow the same steps to the table designer, change col1’s data type and choose to save your changes and you are immediately greeted with this:
The difference is not in SSMS writing a more efficient query, but instead telling you that it will have to drop and recreate the object and not letting you proceed. This is the default behavior, but can be changed within SSMS from Tools>Options>Designers>Table and Database Designers and de-selecting Prevent saving changes that require table re-creation:
posted for such actions:
DECLARE @int INT
SET @int = 1
WHILE @int <= 250000
BEGIN
IF @int%10 <> 0
BEGIN
INSERT test
VALUES(NULL)
END
ELSE
INSERT test
VALUES(@int)
SET @int = @int + 1
END;GO
Now again browse the object explorer and attempt to change the data type of col1 and SSMS 2005 displays this warning:
You are told that the action may take considerable time and given the option to proceed with the action or exit without your changes. Consider how much time that this may take, not to mention the resources, if the table contained numerous columns, constraints, a clustered index, several non-clustered indexes, and a couple million rows. As I mentioned earlier I had originally explored the behavior of enterprise manager to find out what was occurring in the background and required using profiler. Had I waited till 2005 and SSMS I could have saved myself some time and effort. Management studio provides us with a handy Generate Change Script that will script out exactly the action(s) that SSMS would take:
I can now defend my position that Microsoft does in fact provides awesome graphical tools, but the use and knowledge of t-sql is, at times, far more efficient.
Share this post with friends
