Finally, Create or Alter

Question

Post reply

Finally, Create or Alter

Viewing 15 posts - 16 through 30 (of 48 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

t.franz (11/29/2016)

I doubt, that an ALTER OR CREATE <table> would be used commonly, particularly since a CREATE table will usually need some indexes, FKs, CHECK CONSTRAINTs and some other stuff too.

Furthermore an ALTER TABLE ADD <newcol> is short and simple, while an ALTER OR CREATE with e.g. 50 columns will hide the real change.

If you are not sure which "version" a target database has, go and use tools as RedGate SourceControl / Compare.

I think developers need to understand where tables are changing. Certainly version control systems help us determine what is changing, but the implementation for ALTER shouldn't be hard. Each time we alter the table, I'd like to see the full definition of the table.

Like I do for other code.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Thomas Franz Hall of Fame Points: 3760 More actions · Answer 2

djackson 22568 (11/29/2016)

I never have an issue with this. A simple "if exists" tells me if I need to delete something before running create.

Yes, and doing this you drop any extended property (MS_Description), permission (GRANT for some roles) and some more stuff, that you forgot to think above ...

Better do a dummy-create for the procedure / trigger / whatever, if it does not exists than dropping an exisiting object.

God is real, unless declared integer.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 728159

November 29, 2016 at 8:20 am

#1914967

djackson 22568 (11/29/2016)

I never have an issue with this. A simple "if exists" tells me if I need to delete something before running create.

Not for tables.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Thomas Franz Hall of Fame Points: 3760 More actions · Answer 4

@steve-2: What would you do regarding the column order, when you are facing a CREATE OR ALTER <table> statement and the column order differs from the real. Recreating could be terrible when it is a very large table or a table that has tons of indexes / FKs or - worst case - (persisted) computed column that calls a scalar function that does some lookups / joins / calculations in 5 other tables.

God is real, unless declared integer.

Chris Harshman SSC-Forever Points: 42179 More actions · Answer 5

paul s-306273 (11/29/2016)

Maybe SQL Server is catching up on Oracle.

😀 just what I was thinking. I've worked with SQL Server since version 2000, but it wasn't until the redesign and new features in 2005 that I took it seriously as a challenger to Oracle. Don't get me wrong, I'm glad I choose to move my career towards SQL Server instead of staying with Oracle, but there are some things that I still miss. This is one less item on my list.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 6

t.franz (11/29/2016)

@Steve: What would you do regarding the column order, when you are facing a CREATE OR ALTER <table> statement and the column order differs from the real. Recreating could be terrible when it is a very large table or a table that has tons of indexes / FKs or - worst case - (persisted) computed column that calls a scalar function that does some lookups / joins / calculations in 5 other tables.

I think this is up to the developer to understand. Inserting columns in the middle is always problematic, but people do it anyway at times. I think this can be dealt with in the engine.

Don't forget, Microsoft can determine how changes are made. They could always add to the end, using meta data to display purposes with select *. They can get around that.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Nelson Petersen SSC Enthusiast Points: 195 More actions · Answer 7

Nelson Petersen

SSC Enthusiast

Points: 195

November 29, 2016 at 11:33 am

#1915017

No need to specify all the columns when you only want to change one of them:

create table test9 (c1 number, c2 number, c3 varchar2(26), c4 varchar2(14) );

Table created.

alter table test9 modify ( c3 varchar2(28) );

Table altered.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 8

Nelson Petersen (11/29/2016)

No need to specify all the columns when you only want to change one of them:

create table test9 (c1 number, c2 number, c3 varchar2(26), c4 varchar2(14) );

Table created.

alter table test9 modify ( c3 varchar2(28) );

Table altered.

This is inconsistent with other code. When I ALTER PROCEDURE, I have all code. When I submit a new class to the C# compiler, I send all code. Why not for tables? I know how it works, but it should be:

create table test9 (c1 number, c2 number, c3 varchar2(28), c4 varchar2(14) );

And have the engine know this means a change in col 3 from 26->28.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

ZZartin SSC-Dedicated Points: 30758 More actions · Answer 9

Steve Jones - SSC Editor (11/29/2016)

Nelson Petersen (11/29/2016)

No need to specify all the columns when you only want to change one of them:

create table test9 (c1 number, c2 number, c3 varchar2(26), c4 varchar2(14) );

Table created.

alter table test9 modify ( c3 varchar2(28) );

Table altered.

This is inconsistent with other code. When I ALTER PROCEDURE, I have all code. When I submit a new class to the C# compiler, I send all code. Why not for tables? I know how it works, but it should be:

create table test9 (c1 number, c2 number, c3 varchar2(28), c4 varchar2(14) );

And have the engine know this means a change in col 3 from 26->28.

What about cases where you don't necessarily want to sync up the tables exactly, there's advantages to having a simple make tables match kind of command but unlike stored procedures or views I often do want to manipulate individual columns without having to potentially affect the entire table.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

KenpoDBA (11/29/2016)

If we could get something like the following we could also avoid most of the dynamic-sql out there:

select * from @DBName.dbo.T1;

update @DBName.dbo.T1;

select * from @DBName.sys.database_files;

etc.

They force us to use dynamic-sql for ridiculous reasons when a simple replacement like that would not only take most of that away, but would also make the code so much more readable.

+1 Gazzillion!

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Steve Jones - SSC Editor (11/29/2016)

Nelson Petersen (11/29/2016)

No need to specify all the columns when you only want to change one of them:

create table test9 (c1 number, c2 number, c3 varchar2(26), c4 varchar2(14) );

Table created.

alter table test9 modify ( c3 varchar2(28) );

Table altered.

This is inconsistent with other code. When I ALTER PROCEDURE, I have all code. When I submit a new class to the C# compiler, I send all code. Why not for tables? I know how it works, but it should be:

create table test9 (c1 number, c2 number, c3 varchar2(28), c4 varchar2(14) );

And have the engine know this means a change in col 3 from 26->28.

Heh... yeeaaahhh... that's what I want to do for a 140 column table. 😉

--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)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 12

ZZartin (11/29/2016)

What about cases where you don't necessarily want to sync up the tables exactly, there's advantages to having a simple make tables match kind of command but unlike stored procedures or views I often do want to manipulate individual columns without having to potentially affect the entire table.

What cases do you mean? How would a CREATE OR ALTER where you specify the definition cause you to be unable to manipulate a single column? You could use multiple CREATE OR ALTER statements if needed. At the end of the day, this means my code and my table evolution looks like every other type of source code over time.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 13

Jeff Moden (11/29/2016)

Heh... yeeaaahhh... that's what I want to do for a 140 column table. 😉

If you're treating code like code, then you'd always have the definition of the table. How is this hard to manage?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Steve Jones - SSC Editor (11/29/2016)

Jeff Moden (11/29/2016)

Heh... yeeaaahhh... that's what I want to do for a 140 column table. 😉

If you're treating code like code, then you'd always have the definition of the table. How is this hard to manage?

It's a lot more difficult than just altering a table to alter a single column. The reason why tables are handled differently than store procedures, functions, and views is that tables have easily identifiable "sub-entities" (attributes) known as columns. There isn't an easy way to identify the parts of a programmable object that have been changed.

Treating code like code is one thing. Treating a table like code makes no sense.

--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)
Intro to Tally Tables and Functions

Ed Wagner SSC Guru Points: 287015 More actions · Answer 15

KenpoDBA (11/29/2016)

If we could get something like the following we could also avoid most of the dynamic-sql out there:

select * from @DBName.dbo.T1;

update @DBName.dbo.T1;

select * from @DBName.sys.database_files;

etc.

They force us to use dynamic-sql for ridiculous reasons when a simple replacement like that would not only take most of that away, but would also make the code so much more readable.

I have to agree with you 100% on that one. While CREATE OR ALTER is nice, your suggestion would make a far more tangible difference. Further, I'd extend it to:

SELECT @ColumnList FROM @DBName.dbo.@TableName;

I have no idea how this would be implemented in the plan cache, unless they treated each expanded version of the query independently rather than the version as written. If not, it could give a whole new meaning to parameter sniffing. It would certainly be an interesting trip under the hood.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions