Looking at SSDT upgrade scripts

来源:转载

I have been using SSDT seriously for a couple of years now and have really been impressed with the code that is generated to upgrade schemas and over that time have learnt to trust that SSDT generates good upgrade scripts.

It is however a concern that a tool generates the deployment script rather than a person and to move to a continuous deployment environment we need to be sure that the tooling is better than a person. To be clear I am already in that camp but wanted to spend some time exploring what upgrade scripts get generated in which scenarios.

Scenario 1 - Making a column smaller

In this first example I will take an existing table and change one of the columns so that it is smaller we will go from:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> )

to:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(1024) not null<br /> )

The Result

In this example we get a table rebuild:

Scenario 2 - Changing a column from a varchar to an nvarchar

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> )

to:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname nvarchar(max) not null<br /> )

The Result

Scenario 3 - Changing Non-Null columns to Nullable

In the next scenario what happens when we remove a non-null constraint? I would like to see a alter table rather than a table rebuild:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> )

to:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) null<br /> )

The Results:

Scenario 4 - Add a non null column

This one is a little more interesting, what happens when we add a column with a constraint and no default value so if there is data in the table this should fail:

from:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> )

to:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> lastname varchar(200) not null<br /> )

The Results:

The interesting this about this is firstly the script will stop if the tabel contains any data as the deploy would fail.

Secondly there is a comment that gives you multiple ways to fix it. NICE.

Scenario 5 - Add a null column

If we do the same thing but add a nullable column to the end of the table what do we get?

from:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> )

to:

<br /> create table employees(<br /> id int not null primary key,<br /> firstname varchar(max) not null<br /> lastname varchar(200) null<br /> )

The Results:

Done

I think that is enough for now, the generated code is basically what I would generate except that when adding scenario 5 I would have added it as a nullable column, then update the value and set to null after that but I can live with that.

I will likely re-visit this topic with some more interesting scenarios but that is enough for now :)

ed



分享给朋友:
您可能感兴趣的文章:
随机阅读: