Fixing CREATE TABLE

Question

Fixing CREATE TABLE

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
February 26, 2014 at 8:29 pm

#286191

Comments posted to this topic are about the item Fixing CREATE TABLE

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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":cool:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Piquet SSCommitted Points: 1528 More actions · Answer 3

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 SSCoach Points: 15320 More actions · Answer 4

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 SSC Eights! Points: 961 More actions · Answer 5

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 SSC Guru Points: 1003853 More actions · Answer 6

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":cool:

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Xavon SSCrazy Points: 2280 More actions · Answer 7

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 SSChampion Points: 11510 More actions · Answer 8

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
Don't fear failure, fear regret.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

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?

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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 SSCarpal Tunnel Points: 4876 More actions · Answer 11

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 SSC Guru Points: 82166 More actions · Answer 12

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 SSChampion Points: 11510 More actions · Answer 13

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
Don't fear failure, fear regret.

george sibbald SSC Guru Points: 104210 More actions · Answer 14

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.

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