Sql Server Data Tools (SSDT)

Home » SQL and Databases » Sql Server Data Tools (SSDT)
Photograph of a SSD Drive Array

Quite a few years ago stored procedures were very common. With the advent of ORM (object relational mappers) and TDD (test driven design), they are less common. Well what might be termed “misuse” of views and stored procedures is. I have worked on some systems where the majority of the data access logic and business logic are partly in stored procedures, partly in the app. These systems are very difficult to test, no matter what methods are used (and can be difficult if for example a requirement appears to store some of the data on a different database for example). I could go on but I wont…

However, even though a thinner database layer is both common and usually a good thing, it is still a good idea in my opinion to keep the database scripts used to create the database under source control. This way, the code can be versioned with the app using the app’s source control system and a history of changes and comments, along with all the benefits of the app’s source control system can be used for the database files as well. Even though we don’t have big stored procedures as much any more, many systems can still contain a lot of views, and of course tables.

Prior to using the tools I am talking about in this blog post, keeping full track of changes was hard. I few customers I worked for have had systems where object create (table, view, stored procedure) and also change scripts were maintained in a set of folders, so that when you changed the database, you changed the folder structure/contents as well to match. A little time consuming but these systems tended to work very well.

The first tool I came across a few years ago was SQL Compare and SQL Data Compare from Redgate. These are powerful, useful tools, that saved me time because when I made a change, I updated the release notes but didn’t have to make a change script. Both these tools can very easily make change scripts at release time, provided you have a copy, or have access to the test database at release time (you are using test and dev databases aren’t you, please don’t tell me you develop on live…).

However what really got my attention was Sql Server Data Tools (SSDT) from Microsoft, which is now an integral part of Visual Studio. Installing this add on, makes available a new project type, the SQL Server Database Project as shown below.

Shows screen print of new project dialog in Visual Studio

Sql Server Data Tools (SSDT) New Project

Once the project is created, the following page appears, upon which you can import an existing database, or you can create the SQL files to create tables, views etc. using full Visual Studio Intellisense.
Screen print of Empty SSDT Project in Visual Studio

Sql Server Data Tools (SSDT) Empty Project

The immediate thing that I like about Sql Server Data Tools (SSDT) is that it automatically creates and saves CREATE TABLE scripts, but when working with it, it uses CREATE or ALTER as appropriate. Just a detail that is surprisingly useful.Sql Server Data Tools (SSDT) compiles the Visual Studio solution just like other solutions, and has compare tools built in (I still like Redgate’s tools in production environments but SSDT are good for dev’s). It keeps an empty copy of your dev database in LocalDb storage, so you can either update the dev database and compare with SSDT, or you can update SSDT and publish your change to your dev database. It can also be used for updating test and live database although I tend to use Redgate’s tools for that at the moment.

The only problem I have found personally with Sql Server Data Tools (SSDT) is that because it compiles the solution, some complex production databases, particularly ones that link to other databases inside of views, stored procedures etc., do not properly compile. In these situations I have used RedGate’s SQL Source Control which is easier to set up in these situations. With SQL Source Control, you have a development database as before, which is linked in SSMS to the source system and no solution compilation is involved, so it works fine in even the most complex of situations. This is slightly different to Microsoft because they use Visual Studio for dev’s, SSMS for admins, but as a dev I have used SSMS as well so this is no problem at all for me.
A good tutorial on Sql Server Data Tools (SSDT) can be found in this Mssql Tips article on Sql Server Data Tools (SSDT). Hope this helps. I am surprised that a lot of developers haven’t heard about these tools. Well worth checking out.

About Phil

I have been working as a software developer since 1983. This blog could have been called "From Fortran 77, C and Cobol to C# in 20 (not so) easy years", but it doesn't sound quite right somehow. Besides I'm talking about what's happened since 2003, not before!

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Top Posts & Pages


Recent Posts

Recent Comments



  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

Too often, agile teams are expected to finish everything every iteration. This expectation is harmfu [...]

- mike@mountaingoatsoftware.com

When a product backlog becomes too big, it hinders agility. Discover four steps your team can take t [...]

- mike@mountaingoatsoftware.com

Pervasive myths about agile get in the way of success. It’s time to bust six of those myths. [...]

- mike@mountaingoatsoftware.com

Agile teams strive to finish work in the same iteration in which its begun. Here’s why that is so im [...]

- mike@mountaingoatsoftware.com

I wrote 25 blog posts during 2018. In case you missed some of them, here are the most popular. [...]

- Scott Hanselman

My Xbox user name is Glucose for a reason. This is a passion project of mine. You've likely see [...]

- Scott Hanselman

I've been really enjoying my Xbox lately (when the family is asleep) as well as some fun Retrog [...]

- Scott Hanselman

So you've been asked to parse some dates, except the years are two digit years. For example, da [...]

- Scott Hanselman

I've been working on a little idea where I'd have an app (maybe a mobile app with Xamarin [...]

- Scott Hanselman

"EditorConfig helps maintain consistent coding styles for multiple developers working on the sa [...]