XML FeedsThis is day five of the SQL Advent 2012 series of blog posts. Today we are going to look at why you need to be able to write your own DDL statements.
Read the following two lines, have you ever answered that or has anyone every answered that when asked this question?
Question: How do you add a primary key to a table?
Answer: I click on the yellow key icon in SSMS!
Technically, yes, that will create a primary key on the table but what will happen when you do that? Let's take a look at some examples.
First create this very simple table
- CREATE TABLE TestInt(Col1 tinyint not null)
Now they users changed their mind and want to insert values that go beyond what a tinyint can hold. If you try to insert 300, you will get an error
- INSERT TestInt VALUES(300)
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 300.
The statement has been terminated.
No, problem, I will just change the data type
- ALTER TABLE TestInt ALTER COLUMN Col1 int NOT NULL
But what if you use the SSMS designer by right clicking on the table, choosing design and then changing the data type from tinyint to int? Here is what SSMS will do behind the scenes for you
- /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
- BEGIN TRANSACTION
- SET QUOTED_IDENTIFIER ON
- SET ARITHABORT ON
- SET NUMERIC_ROUNDABORT OFF
- SET CONCAT_NULL_YIELDS_NULL ON
- SET ANSI_NULLS ON
- SET ANSI_PADDING ON
- SET ANSI_WARNINGS ON
- COMMIT
- BEGIN TRANSACTION
- GO
- CREATE TABLE dbo.Tmp_TestInt
- (
- Col1 int NULL
- ) ON [PRIMARY]
- GO
- ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
- GO
- IF EXISTS(SELECT * FROM dbo.TestInt)
- EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
- SELECT CONVERT(int, Col1) FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
- GO
- DROP TABLE dbo.TestInt
- GO
- EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT'
- GO
- COMMIT
That is right, it will create a new table, dump all the rows into this table, drop the original table and then rename the table that was just created to match the orginal table. This is overkill.
What about adding some defaults to the table, if you use the SSMS table designer, it will just create those and you have no way to specify a name for the default.
Here is how to create a default with T-SQL, now you can specify a name and make sure it matches your shop's naming convention
- ALTER TABLE dbo.TestInt ADD CONSTRAINT
- DF_TestInt_Col1 DEFAULT 1 FOR Col1
About that yellow key icon, let's add a primary key to our table, I can do the following with T-SQL, I can also make it non clustered if I want to
- ALTER TABLE dbo.TestInt ADD CONSTRAINT
- PK_TestInt PRIMARY KEY CLUSTERED
- (Col1) ON [PRIMARY]
Click that yellow key icon and here is what happens behind the scenes, I have not found a way to make it non clustered from the designer
- /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
- BEGIN TRANSACTION
- SET QUOTED_IDENTIFIER ON
- SET ARITHABORT ON
- SET NUMERIC_ROUNDABORT OFF
- SET CONCAT_NULL_YIELDS_NULL ON
- SET ANSI_NULLS ON
- SET ANSI_PADDING ON
- SET ANSI_WARNINGS ON
- COMMIT
- BEGIN TRANSACTION
- GO
- ALTER TABLE dbo.TestInt
- DROP CONSTRAINT DF_TestInt_Col1
- GO
- CREATE TABLE dbo.Tmp_TestInt
- (
- Col1 int NOT NULL
- ) ON [PRIMARY]
- GO
- ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
- GO
- ALTER TABLE dbo.Tmp_TestInt ADD CONSTRAINT
- DF_TestInt_Col1 DEFAULT ((1)) FOR Col1
- GO
- IF EXISTS(SELECT * FROM dbo.TestInt)
- EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
- SELECT Col1 FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
- GO
- DROP TABLE dbo.TestInt
- GO
- EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT'
- GO
- ALTER TABLE dbo.TestInt ADD CONSTRAINT
- PK_TestInt PRIMARY KEY CLUSTERED
- (
- Col1
- ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- COMMIT
You might ask yourself why you should care, all the tables are small, this is not a big issue. This might be true now, what if you start a new job and now you have to supply alter, delete and create scripts? Now you are in trouble. I used to do the same when I started, I used the designers for everything, I didn't even know Query Analyzer existed when I started, I created and modified the stored procedures straight inside Enterprise Manager. Trying to modify a view that had a CASE statement in Enterprise Manager from the designer....yeah good luck with that one....you would get some error that it wasn't supported, I believe it also injected TOP 100 PERCENT ORDER BY in the view as well
I don't miss those days at all. Get to learn T-SQL and get to love it, you might suffer when you start but you will become a better database developer.
Aaron Bertrand also has a post that you should read about the designers: Bad habits to kick : using the visual designers
That is all for day five of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Also, changes to the database struture should be treated like all code and put under source control so they can easily be deployed. Writing a script to put into your source control system is one of the best ways to do this. 
sqlsister, I agree, this also makes it much easier to recreate the DB from scratch as well 
One thing I notice is that non-SQL folk can use T-SQL to create defaults and checks, but not give them names...
Unless I'm mistaken, you can't make changes through the designer that involve dropping and recreating a table unless you opt into doing so. Out of the box, SSMS will not allow you to do it.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.