Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fixing CREATE TABLE

By Steve Jones,

I've always wondered about this. When I create a stored procedure I do this:

CREATE PROCEDURE MyProc
  @param1 int
 as
 BEGIN
   -- add code here
 END;
  

As is often the case, I realize that I've made some mistake and need to change the code later. So I'll do this: 

ALTER PROCEDURE MyProc
  @param1 int
 as
 BEGIN
   -- add better code here
 END;
  

In both cases, I've repeated lots of the code that I used the first time, though hopefully less of the bugs. If I create a function or view, I do something similar. However when I build a table, I do this:

CREATE TABLE MyTable
 ( MyInt int
 );
  

If I decide that's not enough data storage, and it's likely not, I would do this:

ALTER TABLE MyTable
  ADD MyChar varchar(50);
  

We're used to this, but why do we do this? Why not this?

ALTER TABLE MyTable
 ( MyInt int
 , MyChar varchar(50)
 );
  

It's almost as though DDL mixes the idea of code submission with architectural scaffolding. It's inconsistent, and it's the big reason why we can't use comments in our table code like this:

ALTER TABLE MyTable
 ( MyInt int  -- integer to store a pointer to this row, requires unique index for integrity
 , MyChar varchar(50) -- random value of some data I need to store for this example.
 );

I don't have any hopes that things will change, but it does make me wonder why SQL, which is often simple and highly versatile with a few consistent structures, would create this strange inconsistency.

 
Total article views: 238 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Stairway to SQL Server Indexes: Level 12, Create Alter Drop

Options and impacts when creating, altering and dropping an index

FORUM

alter table alter column with default value of getdate

Why is this alter table not parsing

FORUM

create a role for "alter trace" permission?

I want to create a role and grant it alter trace

BLOG

ALTER SCHEMA

Use this select statement to create a list of ALTER SCHEMA statements for all stored procedures in a...

FORUM

Prohibition of users to Create/Alter tables from UI Designer.

They can alter/create table from running query in query analyzer but not from UI.

Tags
editorial    
t-sql    
 
Contribute