SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fixing CREATE TABLE


Fixing CREATE TABLE

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63564 Visits: 19115
Comments posted to this topic are about the item Fixing CREATE TABLE

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87328 Visits: 41113
There are two answers that I've grown accustomed to when working with any flavor of SQL. The first, of course, is "It Depends". The second one, which seems to apply in this case, is "Because". :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32741 Visits: 18559
Jeff Moden (2/26/2014)
There are two answers that I've grown accustomed to when working with any flavor of SQL. The first, of course, is "It Depends". The second one, which seems to apply in this case, is "Because". :-P


Don't forget "Fetch the high velocity pork chop launcher"Cool



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Piquet
Piquet
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 95
The inability to put inline comments in to describe the purpose of table columns I don't have a problem with - such metadata IMO should instead be directly attached to the table column as an ms_description extended property.

Although it would be really nice if the syntax to add such extended properties when creating/altering tables were as "in-line" as your comment example, rather than having to issue separate sp_addextendedproperty s-proc calls for each one...

Which leads into a 2nd point: It's not so much MS-SQL that needs to be asked "Why?" - but more so why SQL ANSI standard has this perceived inconsistency.

For me I'm OK with the different treatment of tables vs. Other database objects such as views, s-price, functions, etc. - tables have one significant difference from these other object types in that they (potentially) contain DATA whereas these other object types are essentially just "code items", thus changing the structure of a table has potential for data-loss.
Adding a column is not too risky, but altering existing columns on a table can have unintended consequences.
Having different syntax for these operations helps to protect the data and ensure existing data is handled appropriately.

Piquet



paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2583 Visits: 6232
I would assume it's because of memory issues (human ones, not SQL ones)--for instance, if I have a 60-column table that I want to add a single column to, what are the chances I'm going to retype all those column names and definitions correctly? Not so much of an issue these days with all the automated tools available to help with this, of course, but SQL as a language came into existence 40 years ago and it definitely would have been more of an issue back then!
john.riley-1111039
john.riley-1111039
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 204
This has never been a problem for me because we have generally put structure changes to databases in scripts which we retain for configuration control purposes, and which can contain comments.

If you also have a script to create the database in the first place (as we do), it is good practice to keep the script up to date when you apply structure changes.
If you don't have a permanent test database, you can set one up with the script easily.

The difference between ALTER TABLE and ALTER PROCEDURE is that ALTER TABLE changes the structure of the table, but not the data,
whereas ALTER PROCEDURE alters the code, which in a sense is data.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87328 Visits: 41113
SQLRNNR (2/26/2014)
Jeff Moden (2/26/2014)
There are two answers that I've grown accustomed to when working with any flavor of SQL. The first, of course, is "It Depends". The second one, which seems to apply in this case, is "Because". :-P


Don't forget "Fetch the high velocity pork chop launcher"Cool


BWAA-HAAA!!!! My favorite training aid. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Xavon
Xavon
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 452
Because altering a table is not as simple as altering a sproc.

Altering a sproc is basically replacing it from the ground up. Altering a table is less simple. It can add, remove, or change columns. The system would have to assume that columns with the same name are being changed to a new data type. Which would work if we were all perfect, but the chance for human error increases exponential. A simple typo could result in one column being deleted (and all its data being lost), and a new column being created. And that would not allow for a column name change.

It might be doable, with a more complex syntax, to allow for renaming of columns and prevent accidental data loss something like:

CREATE TABLE MyTable
( MyInt int
);

ALTER TABLE MyTable
( [MyInt] => MyInt int
, [] => MyChar varchar(50)
);

That explicitly maps the old columns to the new. If the old column is spelled wrong it could throw an error. And if the new column name is spelled wrong, at least the data is preserved and can be changed back at a later date.
below86
below86
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 2443
I have often said I avoid stored procedures for the most part. But this too has always seemed odd to me. You are essentially replacing the entire code when you do the ALTER PROCEDURE, so why not drop the procedure and do a create?

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10446 Visits: 13687
below86 (2/27/2014)
I have often said I avoid stored procedures for the most part. But this too has always seemed odd to me. You are essentially replacing the entire code when you do the ALTER PROCEDURE, so why not drop the procedure and do a create?


because you lose the permissions granted to the stored proc that way.

Why do you avoid stored procedures?

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search