Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 232 | Views in the last 30 days: 2
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones