Finally, Create or Alter

Question

Finally, Create or Alter

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
November 28, 2016 at 10:34 pm

#332274

Comments posted to this topic are about the item Finally, Create or Alter

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

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

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 1

I know this might be asking for a lot, but I really think that we ought to get a consistent way of coding databases so that we can reduce the mistakes and make our systems easier to understand.

Now won't that be just fantastic but this Alter or Create is a step in the right way. I want the company I work for to upgrade to SQL Server 2016 but have to wait till the new servers they want to implement are here and installed. Frustrating really when I read about all these cool new features.

Manie Verster
Developer
Johannesburg
South Africa

I am happy because I choose to be happy.
I just love my job!!!

Gary Varga SSC Guru Points: 82166 More actions · Answer 2

I think that sometimes we have to understand that a feature will only be available in the latest and subsequent releases.

Realising that by the time that they completed the effort to add the feature to previous versions it is likely that there would be a significant reduction in installations on the older versions and the numbers will continue to decline. This means that the effort may be wasted.

Given the above, I feel that it would be better to spend the effort refining the new feature in the original release and subsequent versions. Of course, there may be exceptions.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 3

Gary Varga (11/29/2016)
I think that sometimes we have to understand that a feature will only be available in the latest and subsequent releases.
Realising that by the time that they completed the effort to add the feature to previous versions it is likely that there would be a significant reduction in installations on the older versions and the numbers will continue to decline. This means that the effort may be wasted.
Given the above, I feel that it would be better to spend the effort refining the new feature in the original release and subsequent versions. Of course, there may be exceptions.

You are right Gary. I can see a positive change in SQL through the years and it feels to me like it is happening to slow. But then, I suppose to develop SQL Server is much more complicated than for me to develop my business system or application. I do get the feeling sometimes that Microsoft is working on a trial and error basis. Like, "we will just patch it when things go wrong". Am I guilty of doing the same on a much smaller scale? Only I can answer that.

Manie Verster
Developer
Johannesburg
South Africa

I am happy because I choose to be happy.
I just love my job!!!

paul s-306273 SSChampion Points: 10730 More actions · Answer 4

paul s-306273

SSChampion

Points: 10730

November 29, 2016 at 1:48 am

#1914881

Maybe SQL Server is catching up on Oracle.

Henrik Staun Poulsen SSCertifiable Points: 6574 More actions · Answer 5

Steve,

I'm perfectly happy about the enhancement that we got with

DROP TABLE IF EXISTS myTable

CREATE TABLE myTable(...)

This ensures that the table is dropped, so that my Create table works.

I mostly use it for #Temp tables, as it is not that often I create permanent tables.

Best regards,

Henrik

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 6

henrik staun poulsen (11/29/2016)
Steve,
I'm perfectly happy about the enhancement that we got with
DROP TABLE IF EXISTS myTable
CREATE TABLE myTable(...)
This ensures that the table is dropped, so that my Create table works.
I mostly use it for #Temp tables, as it is not that often I create permanent tables.
Best regards,
Henrik

Henrik,

I am so glad you mentioned this. This is another feature in SQL 2016 that I loved when I read about it. Steve, I am sure you can live with this for now?

Manie Verster
Developer
Johannesburg
South Africa

I am happy because I choose to be happy.
I just love my job!!!

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 7

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.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Thomas Franz Hall of Fame Points: 3888 More actions · Answer 8

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.

God is real, unless declared integer.

mjh 45389 SSCertifiable Points: 5706 More actions · Answer 9

paul s-306273 (11/29/2016)
Maybe SQL Server is catching up on Oracle.

Or maybe MySQL. We run MySQL on a Linux server and MS-SQL on a Windows one as the second server has to have the Linux OS. I am hoping we can go to MS-SQL under Linux in 2017. These additional features are a plus point!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

henrik staun poulsen (11/29/2016)
Steve,
I'm perfectly happy about the enhancement that we got with
DROP TABLE IF EXISTS myTable
CREATE TABLE myTable(...)
This ensures that the table is dropped, so that my Create table works.
I mostly use it for #Temp tables, as it is not that often I create permanent tables.
Best regards,
Henrik

The same for us now that we have our system in place and running. A table change is rare. New tables the same.

djackson 22568 SSChampion Points: 11733 More actions · Answer 11

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

Dave

hakim.ali SSCarpal Tunnel Points: 4239 More actions · Answer 12

IMHO, even "CREATE or ALTER objectname" can/should be reduced to just "CREATE objectname". Visual Studio database projects / SSDT has had this for years. Just issue the create statement, and let the software figure out specific implementation behind the scenes. Let me specify the *what*, and let the engine work out the *how* and perhaps allow for a manual override if I want it done a specific way.

Hakim Ali
www.sqlzen.com

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

hakim.ali (11/29/2016)
IMHO, even "CREATE or ALTER objectname" can/should be reduced to just "CREATE objectname". Visual Studio database projects / SSDT has had this for years. Just issue the create statement, and let the software figure out specific implementation behind the scenes. Let me specify the *what*, and let the engine work out the *how* and perhaps allow for a manual override if I want it done a specific way.

I'd agree with that. This should be simple, and figuring out if a new column needs to be added should be the job of the engine.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 14

henrik staun poulsen (11/29/2016)
Steve,
I'm perfectly happy about the enhancement that we got with
DROP TABLE IF EXISTS myTable
CREATE TABLE myTable(...)
This ensures that the table is dropped, so that my Create table works.
I mostly use it for #Temp tables, as it is not that often I create permanent tables.
Best regards,
Henrik

That works well for temps, not for perm tables.

well, it works well for perm tables for me. Not so much for clients.