Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fixing CREATE TABLE Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 7:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:33 AM
Points: 2,670, Visits: 19,244
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."
Post #1545880
Posted Thursday, February 27, 2014 7:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1545883
Posted Thursday, February 27, 2014 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 PM
Points: 5,356, Visits: 3,054
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!!!
Post #1545895
Posted Thursday, February 27, 2014 7:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 281, Visits: 1,058
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.
Post #1545896
Posted Thursday, February 27, 2014 7:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


---------------------------------------------------------------------

Post #1545902
Posted Thursday, February 27, 2014 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 376, Visits: 432
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"
Post #1545914
Posted Thursday, February 27, 2014 8:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:48 PM
Points: 3,244, Visits: 537
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



Post #1545926
Posted Thursday, February 27, 2014 8:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:56 PM
Points: 1,367, Visits: 256
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$$.
Post #1545932
Posted Thursday, February 27, 2014 8:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:50 PM
Points: 212, Visits: 609
One of the other RDBMSs has a CREATE OR ALTER statement. Can't remember which. Oracle? MySQL?
Post #1545937
Posted Thursday, February 27, 2014 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
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
Post #1545983
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse