Finally, Create or Alter

There are lots of reasons to upgrade to SQL Server 2016, but this is the one for me. We finally get a CREATE OR ALTER statement in T-SQL. This not only makes lots of code easier to write, it means that the ways in which you might script and schedule your future deployments will be cleaner. This is an exciting change for implementing a simpler and easier Continuous Integration/Continuous Deployment system in your organization.

It’s not perfect news for a few reasons. First, this is a SQL Server 2016 addition to T-SQL only. That means until you have most of your applications have moved to SQL Server 2016 SP1+, you won’t be able to use this construct. That’s OK, because it will mean that at some point most of our instances will be on SQL Server 2016 SP1 or later, and much of our code will be cleaner. We won’t resort to including IF statements in our deployment scripts. We won’t need to create stubs of procedures and functions so our code is embedded in an ALTER script. In essence, you won’t need to maintain two separate code constructs to make a change.

This isn’t perfect, nor is it complete. We still don’t have CREATE OR ALTER for tables. That’s the place where I’d really like to get a consistent way of coding items. What I really want is a complete view of the table each time I change it. By this I mean that if I create a table like this:

CREATE TABLE Students
(
    studentname VARCHAR( 200),
    status TINYINT
);

Then I want to be able to add a column like this:

ALTER TABLE Students
(
    studentname VARCHAR( 200),
    status TINYINT,
    DOB DATE
);

Or alter a column like this:

ALTER TABLE Students
(
    studentname VARCHAR( 200),
    status BIT,
    DOB DATE
);

Or better yet, have a CREATE OR ALTER for tables.

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. I’m sure this may require substantial engineering, not to mention a great deal of understanding of how this would actually affect our systems when run, but it would certainly make our code cleaner.

I doubt we’ll see these kinds of changes, at least not until we have an ANSI standard that encompasses them, but I would hope that as an industry we would mature and improve the way we work with databases, not remain bound by tradition and history.

Steve Jones

 

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

2 Responses to Finally, Create or Alter

  1. I think create or alter for tables might well be potentially quite dangerous. Table schema changes are currently the biggest bugbear I have with most CI implementations, especially when the testing is carried out on small datasets. One of the most annoying things being when a developer switches the column order in a table or adds a new column to the middle of the table; behind the scenes this is achieved by creating an entirely new table, loading the data from the old table and then renaming the tables all within a single transaction. Fine with a few hundred test records, not so cool when you’re dealing with hundreds of millions of rows or more in a db that’s part of an AG, for example!

    • way0utwest says:

      I suppose, but the issues with schema changes has nothing to do with CI. If these issues aren’t caught, then that’s a CI/testing design issue, not a problem with CI. CI is supposed to catch problems or issues and flag them before they get deployed. If you don’t have realistic test datasets, fix that issue. Don’t say that CI doesn’t work or help.

      However, a CREATE OR ALTER for tables doesn’t have to be an issue. Microsoft would need to engineer a better process that won’t create a performance issue, or exposes how the change will be made in a different way. That’s their issue. I wouldn’t expect that every CREATE OR ALTER for a table would work in the same way it does today.

Comments are closed.