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


Fixing CREATE TABLE


Fixing CREATE TABLE

Author
Message
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
Piquet (2/27/2014)
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
What he said. Saves the necessary checks if you're changing datatypes to truncate data/make data incompatible with the new type. Procedures are just code, no checks necessary. You can even ALTER PROCEDURE to something that just plain doesn't exist, the system doesn't care until you try to execute it.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Tobar
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
Think of "create table" as legacy code, and create procedure as the newer fangled thingy. That is really what is going on. Create table was one of the first SQL commands, create procedure came later, in some cases much later. How often do we really go back and re-engineer when we realize "it can be better"? Not saying I wouldn't like to, just saying. The Standards organization could retrofit a better interface but what really would be the benefit? Sure our job would make more sense, but that would be a hidden benefit to anyone who pays the bills. Return on invest is the only thing most of the world sees. Beauty, is not quantifiable. sigh.

<><
Livin' down on the cube farm. Left, left, then a right.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 6147
george sibbald (2/27/2014)
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.

...


I always thought that the two ALTERs were semantically different and, therefore, shouldn't share the command.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2121
george sibbald (2/27/2014)
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?


OK, that makes sense, didn't think about it like that.

Almost everything we run is batch driven, nightly cycles, so no real time need. When a SP fails I don't know where in it it failed or why. Running the SQL in batch processing I get all that info dumped to a log file when it executes. Yes I know you can set up error handling within a SP to do somewhat the same thing but it clutters up the code. Then you have to rely on any future changes, adding more steps, that the programmer adds the needed steps to capture any potential errors. I know I seem to be in the minority here, but it works for us. Why change just for changing? We do you some SP's it's just not very much. And it is only for things that a lot of SQL would use, like taking a date and determining if it is a holiday or a weekend date.

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

Group: General Forum Members
Points: 6324 Visits: 13687
below86 (2/27/2014)
george sibbald (2/27/2014)
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?


OK, that makes sense, didn't think about it like that.

Almost everything we run is batch driven, nightly cycles, so no real time need. When a SP fails I don't know where in it it failed or why. Running the SQL in batch processing I get all that info dumped to a log file when it executes. Yes I know you can set up error handling within a SP to do somewhat the same thing but it clutters up the code. Then you have to rely on any future changes, adding more steps, that the programmer adds the needed steps to capture any potential errors. I know I seem to be in the minority here, but it works for us. Why change just for changing? We do you some SP's it's just not very much. And it is only for things that a lot of SQL would use, like taking a date and determining if it is a holiday or a weekend date.


understandable if the recompile time is insignificant compared to overall elapsed time.

---------------------------------------------------------------------
DEK46656
DEK46656
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 563
IMHO, it’s all about the roots of where relational database designs come from: set theory and relational algebra.

In the case of CREATE TABLE or ALTER TABLE, you are defining the set. The set definition (DDL) is probably so different from the other things we think about doing, that we forget just how strict the rules are for stating “what is the set?”

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 1017
I think the difference is really simple.

ALTER TABLE: you can specify whether you are adding a column or changing a column. If adding one, it adds it to the end of the table, if changing a column it finds the column and changes it.

ALTER PROCEDURE: if you are just changing part of a procedure, how do you tell it where the change goes?

CREATE PROCEDURE myproc
AS
SELECT lname, fname, mi
FROM mytable
WHERE lname = 'smith'
GO


now I want to change the order of that select and get the first name, mi, and last name. How do I tell SQL Server to change JUST the select line? The code isn't written with line numbers, so I can't say something like ALTER PROCEDURE ALTER LINE 1 SELECT fname, mi, lname.

Since I can't tell SQL Server the specific part I need to change, I have to resubmit the entire thing.

-SQLBill



GBimberg
GBimberg
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 281
If you could alter tables the way you suggest, it would appear that you could also change the column order and type.

Accidently changing the column order and type would cause nightmares.

If you couldn't change the column order and type, altering tables this way would just be a huge pain in the a$$.
GoofyGuy
GoofyGuy
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 971
One of the other RDBMSs has a CREATE OR ALTER statement. Can't remember which. Oracle? MySQL?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36099 Visits: 18738
paul.knibbs (2/27/2014)
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!


Not if you're used VCS, which was around early.

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.


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
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