This will be the first of a few posts covering topics from
my SQL Saturday presentation on June 1st
of 2013. If you have not checked out SSDT since they
were called Database Projects, it is well worth another look. Besides an improved interface and other new
features, it just works seamlessly.
With database projects, one would generate a deployment
script that might be 20 or 30 thousand lines of code. Besides, hardly being able to effectively
review it, it was always white knuckles
hoping that it would not error out 15,000 lines or so into it. Some object changes were tough to trust that
they would work right and, occasionally, some tinkering was required to make
the scripts work. With SSDT, database
refactoring can now largely be an automated process, which makes it possible to
have continuous integration on your data warehouse projects. All of this shortens up the development cycle
and reduces the deployment effort.
Refactoring (renaming objects or changing schemas) was part
of database projects as far as deployments were concerned, but now it is
integrated into schema comparisons in SSDT. I will walk through the basics of below.
Renaming an object or changing schemas is often a time
consuming and difficult task depending upon how many references there are to
it. For example, suppose we have a
column with a typo, “Customer_Numbr” (not that I have ever done that before). Not only, do we need to rename the column,
but this column might be part of a PK, index, foreign key, stored procedure,
view or extended property. All of these
references need to be discovered and updated as well.
SSDT makes this simple.
You can access the refactor menu by right-clicking on an object in SSOX
(SQL Server Object Explorer) – this would be a table, view, column or function,
for example. Or you can right click on
an object within a script. In our case,
we would click on the column name and then Go to Refactor> Rename. Note that you can also change the schema from
here as well.
Once we select “rename” the dialogue will give the current
name, prompting you to change it. In the
case of this example, we would make our new name “Customer_Number.” By default, “Preview Changes” is checked and
so, when we hit “ok,” get the following window which will allow us to see all
of the scripts that reference the object and what will be changed in this
action. This takes away all the tedium
of finding all the references that require a change in reference and, really,
it’s a lifesaver because it is easy to overlook some type of objects like
extended properties. If the column is
referenced in a proc in another database in the project which has a reference
to it, this script will be changed as well.
When we click “Apply” all of the files mentioned in the menu
are changed and saved. More than this,
SSDT will keep track of these changes and how they are applied so that when it
comes time for deployment, no data loss will occur. For example, if we changed the name of a column,
when SSDT compares the new name to the old, it would have no sure way of
knowing that this was a rename and it would simply rebuild the table, dropping
the old column and fitting in the new one.
With refactoring, the change is saved so that when a
comparison is done, the renaming can be applied. There are two places which store this
information – first, the refactorlog and second, a system table in the target
database called dbo.__refactorlog. The
refactorlog file will be added to the root of the project when the first refactoring
change is made. The file will be name
<DatabaseName>.refactorlog and the contents will look something like
<?xml version="1.0" encoding="utf-8"?>
<Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
<Operation Name="Rename Refactor" Key="2ed039e3-c6ba-4985-9abf-d82a33c9baad" ChangeDateTime="06/26/2013 18:41:53">
<Property Name="ElementName" Value="[SalesLT].[Customer].[Customer_Numbr]" />
<Property Name="ElementType" Value="SqlSimpleColumn" />
<Property Name="ParentElementName" Value="[SalesLT].[Customer]" />
<Property Name="ParentElementType" Value="SqlTable" />
<Property Name="NewName" Value="[Customer_Number]" />
As you can see, the old and new names are tracked in a
generic xml format, allowing it to be reused against any object that can be
renamed or have the schema transferred.
When you publish the change, the rename operation will be
put into the script unless the column does not exist. As well, it will create the __refactorlog
table and add the entry with the guid from the project, noting that the change
has been applied.
Refactoring step to update target server with deployed transaction logs
IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '2ed039e3-c6ba-4985-9abf-d82a33c9baad')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('2ed039e3-c6ba-4985-9abf-d82a33c9baad')
Note that the table will appear under the system tables in
SSMS. The beauty of this design is that
a refactoring change can be applied to any target database in any state and take
the correct action given the condition it is in.
Hope you find this tip helpful,