THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Connected development (10 days of SSDT – Day 8)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

In Day 1 – Online and Offline I explained how SSDT could be used to query and affect existing databases:

I can browse through the objects on those servers and interrogate them just as I would in SSMS. Within SSDT/SSOX however there is one important difference, when I make changes to the database I am not affecting the database directly, I am affecting an in-memory model of the database. This approach has two important advantages,

  • I can view the impact of a change (e.g. if I change a column name will it break any views that reference it) without actually making the change
  • I can make many changes to the model and then push them into my actual database en masse and SSDT will work out the best way of doing that. I don’t have to write the code that deploys my code.

These capabilities are worth exploring in more detail which I’ll do by comparing the schema editing experience in SSMS & SSDT. Here I show that I have a copy of venerable old Northwind which I’m going to use to demo this:

image

Let’s say, hypothetically, I want to change the name of the CustomerID field; SSMS has a UI that enables me to do this and when it does so it will alter the definition of all the affected objects such as foreign keys that reference that column. That’s quite useful however it does somewhat shield you, the developer, from knowing the intricacies of what SSMS is doing under the covers. In SSDT this scenario is a little different, we browse to the table in SQL Server Object Explorer (SSOX – its one of the integral components to SSDT) and select ‘View Designer’:

image

This launches us into SSDT’s Table Designer (which we have seen before in Day 4 – Table Designer) where we can go ahead and make our change:

image

Let’s explain what’s going on here. We’ve changed the name of a column and before we’ve actually saved our change SSDT shows us which objects will be impacted by that change. For example, the first error in that list is:

Computed Column: [dbo].[Quarterly Orders].[CustomerID] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Customers].[CustomerID], [dbo].[Customers].[Customers]::[CustomerID] or [dbo].[Orders].[Customers]::[CustomerID].

Double clicking on that error opens up the definition of the affected object and gives us a red squiggly indicating exactly where the error is:

image

That’s two reasons why I like the SSDT way, (1) it shows you the implications of your change as you type rather than after you try and commit the change and (2) you can jump straight to affected objects so you can change them, plus you get nice red squigglies and intellisense too:

image

The third, and what I think of as the best, benefit though can’t easily be demonstrated with screenshots; that is, when I make any changes I’m not actually making changes to the physical database. In actuality SSDT has built an offline model of the database underneath the covers and it is that model to which I am making changes. I can go on making changes (probably by double clicking on all the errors in the error list) and each one of those changes is made to the offline model, not to the physical database.

Eventually I will reach a state where all the errors have been solved and I am ready to push all the model changes back to the actual database. To do that I simply hit the Update button that will appear at the top of each edited DDL script:

image

and when I do so a dialog appears with a pseudo code description of all the changes that I have made:

image

(Note that this is the same pseudo code that we talked about previously in Day 7 - Data Tools Operations Window)

I can hit the Generate Script button to build a SQL script that will make all those changes for me or simply hit Update Database to push all the changes up.

In summary, SSDT allows you to queue up a series of changes to a database by affecting an offline model rather than the database itself. If you’re making a simple change that doesn’t affect anything else then the benefit here is negligible but if your changes are more substantial than that then this can be a really useful feature.

The last note on this feature is that when SSDT was first released this feature was known as PowerBuffer although I haven’t heard that word mentioned much (if ever) since then. The only reason I mention it here is so that if you hear the word you will know what is being referred to. If you are interested there is useful PowerBuffer documentation on MSDN: How to: Update a Connected Database with Power Buffer.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Published Friday, September 26, 2014 8:40 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 13, 2014 2:57 AM
 

Colin Johnston said:

Just want to say thank you for this blog series. We've been using SSDT for about a year now and I mostly like it very much. However, I've never used the Object Explorer, as I couldn't see any advantages over SSMS for ad-hoc changes/connected development.

Thanks to this post, I know see how powerful it is as an adjunct to the project-based approach, all within Visual Studio, and without having to do schema compares all the time. I still wish however, that there were designers for other objects than tables, and that the table designer loaded faster than it does.

February 27, 2015 9:44 AM
 

jamiet said:

Thanks Colin. Its getting comments like that that make it all worth it.

February 27, 2015 10:56 AM
 

Enders said:

If you go to the T-SQL in the table, select a column right, click refactor and then rename you can change the column name.

Check preview changes to see what is being changed too (indexes, views, stored procedures and so on)

When you deploy your the column will not be dropped and added but renamed instead. No data will be lost

March 12, 2015 8:13 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement