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

It can be challenging to find time for refactoring. These approaches will help.

- mike@mountaingoatsoftware.com

If I could make only one recommendation for an agile resource to have in 2021, this would be it…

- mike@mountaingoatsoftware.com

- mike@mountaingoatsoftware.com

I wrote 26 blog posts during 2020. In case you missed some of them, here are the most popular.

- Scott Hanselman

Late last year I blogged about the Elecrow CrowPi2 Raspberry Pi Laptop. The folks at Elecrow are gre

- Scott Hanselman

Sometimes blogging means lots of long form essays that take weeks to write and drop lots of wisdom.

- Scott Hanselman

You probably know that you can download free developer tools for Windows 10 up at the Windows Dev Ce

- Scott Hanselman

According to the Dapr open source website: "Dapr helps developers build event-driven, resilient

- Scott Hanselman

This is pretty cool. As you may know, when you type "dotnet new" from the command line, or