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 Wednesday, February 26, 2014 8:29 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 3:05 PM
Points: 31,284, Visits: 15,750
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
Post #1545676
Posted Wednesday, February 26, 2014 8:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
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".

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545682
Posted Wednesday, February 26, 2014 11:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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".


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




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1545712
Posted Thursday, February 27, 2014 12:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 30, 2014 5:29 PM
Points: 1,214, Visits: 75
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



Post #1545736
Posted Thursday, February 27, 2014 2:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 1,632, Visits: 5,707
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!
Post #1545762
Posted Thursday, February 27, 2014 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:55 AM
Points: 34, Visits: 163
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.
Post #1545763
Posted Thursday, February 27, 2014 6:39 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
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".


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


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


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545849
Posted Thursday, February 27, 2014 7:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:35 AM
Points: 359, Visits: 169
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.
Post #1545860
Posted Thursday, February 27, 2014 7:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 308, Visits: 1,142
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?
Post #1545866
Posted Thursday, February 27, 2014 7:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 5,886, Visits: 13,047
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?


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

Post #1545876
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse