In this series of blog posts, I am talking about how to use agile or modern development practices to help us become better Sql developers.

The first step on the list is a development IDE and it really is the first thing to think about, sure getting your database in source control is critical and if you haven't already then you should get that done (really go do it now) but how you get your schema and code into source control and how you will manage that going forwards depends a lot on which IDE you choose.

Warning

The content in this is my own, it is my own views and thoughts, it isn't unbiased and fair but what I feel is an accurate representation of the available IDEs and how they compare and help us write better, faster, more fun code. The blog is also looking at IDEs for developing T-SQL code, if you want to manage Sql Server then just use SSMS (again a biased and unfair statement but mostly true).

TLDR;

Use SSDT in Visual Studio.

What choices are there?

The choices of development IDE's are:

• Notepad
• Sql Server Management Studio (SSMS)
• Sql Server Management Studio with plugins, notably Redgate and ApexSql
• Visual Studio with Sql Server Data Tools (SSDT)
• Toad for Sql Server
• dbForge Studio
• EMS SQL Manager for SQL Server
• 0xDBE
• Anything else?

These choices basically break down further into 3 categories:

• Simple no frills, no help editors
• Editors with basic help such as intellisense
• More advanced editors with features like compilers and refactoring

No frills editors

• Notepad
• Sql Server management Studio

Notepad

Using a text editor like notepad will let you write and examine scripts which can be deployed to sql server, it is fast to open files and to save again but that is about the only good point!

What you don't get with a basic editor is any help, you don't get intellisense or information about how the database schema is made up - in short you get no feedback as to whether you have made a simple (or indeed a more complicated) mistake. There is nothing stopping you spending hours creating a schema and writing code that will never work.

Using notepad should only really be used to quickly look at a script to see what it is doing.

Sql Server Management Studio

Even though Sql Server Management Studio or SSMS has improved since it was first released with the addition of intellisense in 2008, it still isn't great.

Personally I see SSMS more as a tool to manage Sql Server, rather than as a development environment. SSMS is not all bad, there are some redeeming features such as the ability to use plug-ins or add-ins and there are some features like the ability to see query and execution plans which make it really useful but as a modern development environment by itself, it really is no frills.

Basic help such as intellisense and extra features

What you get in this category of editors is time savers and improved code quality, intellisense is a massive time saver and things like formatting T-SQL and expanding wildcards to column names help to improve the quality of the code. Each editor has its own set of features and at a minimum you should be using ones of these IDEs to develop code. When you use one of these IDEs you also need to make sure you use the features - using them as if they were SSMS or notepad is completely wasteful.

Sql Server Management Studio with add-ins

I am including two versions of this editor, SSMS with Redgate Sql Prompt and the ApexSql Complete add-ins.

They both offer similar features so choosing between them is hard, if you are inclined to use SSMS then I would trial them both out and see which you prefer. The Redgate tool is about GBP 225 the Apex tool is free (seriously!)

What you get with these add-ins are much better intellisense which makes developing faster and it means that you can use longer, more descriptive names, so no more cryptic table and column names to save the cost of a new keyboard for the Sql developers!

There are also other features that really help, both of the intellisense options also show the defintion of objects such as tables so if you want to know what type a column is, you don't have to go and look it up somewhere else, you can find the information you need without changing context and continue programming. It is the details like this which drastically improve the development time and let you focus on what is actually important, things like what the code does and how it works rather than whether you are inadvertently joining on a text column.

Redgate Sql Prompt:

ApexSql Complete:

They also both offer features such as split table which generates the T-SQL for you to take a table and split it into two without losing any data, it certainly is a good step in the right direction and if your developers are uneasy about developing outside of SSMS then why wouldn't you use, certainly the Apex tool as it is free.

One thing that both Apex and Redgate offer are source control add-ins for SSMS and these are probably the easiest way to get your databases under source control - you literally install them, choose to link a database and then point to a source control repository and then check your database in. Easy.

If you want an easy introduction into getting help with your development then these are highly recommended and will be familiar to anyone who already uses SSMS.

devart dbForge studio for Sql Server

dbForge is basically a replacement for SSMS, it includes code formatting and similar intellisense and object information, similar to the add-ins for SSMS:

I am not really sure if dbForge is any better than using SSMS with either of the add-ins, it certainly didn't feel like it when I gave it a try. One disappointing feature was the source control seems to only work with TFS and SVN where I would really expect to see Git on that list.

Toad for Sql Server

Of all the editors in this category I like Toad the most, I am not so sure about the obsession with pictures of strange amphibians but the ui looks nice and has the standard features like intellisense and some nice features like wizards to create functions and a really customizable ui.

The integration with source control works pretty well with support for SVN, TFS and file based systems you can integrate it with pretty much anything.

Although I like Toad, it feels more like an IDE for a DBA who does some development or a tool for debugging T-SQL rather than a full modern development environment, mainly because of the lack of modern features like advanced refactoring.

EMS Sql Manager for Sql Server

The last IDE in this category is the EMS Sql Manager, it has lots of features including intellisense and lots of gui tools to manage the database, again I feel it is more of a tool for DBAs and possibly occasional T-SQL developers. It is a good tool but not a great tool for modern T-SQL development with refactoring and source control support etc.

Advanced editors

As we move into the final section we really start to see some features that differentiate these IDEs from other IDEs for development. Where the previous IDEs are either pure DBA IDEs or a hybrid for DBAs and developers, these last two are really unashamedly development IDEs.

0xDBE

This is the database tool from Jetbrains based on the IntelliJ IDE and I believe if you have the paid for version of IntelliJ you also get 0xDBE IDE built in. In my mind Jetbrains get a free pass to the IDE party because of their work with resharper, writing c# code in Visual Studio with resharper is a really great experience and anything that Jetbrains can do to bring some of that love to T-SQL development is great.

The main feature of resharper I use is the navigate to symbol command which lets you quickly jump around from object to object, in a large database project it can be slow to find and move between objects, with the navigation feature it is fast and efficient:

0xDBE has the standard rename / refactoring built in and looks really nice. I recently recommended it as a solution to a development team who wanted to move towards a more agile approach but were not currently using Visual Studio. It is also written in java so you could run it on Mac or on Unix so there are plenty of options for developers who don't want to run windows.

Visual Studio with Sql Server Data Tools

This is really the game changer for me, Sql Server Data Tools (SSDT) extends Visual Studio to give support for developing T-SQL code. The added fact that you can get a free version, whether that is the community version of Visual Studio or the express versions, it is for me the obvious choice. With SSDT the general features are:

• A model of the database that is validated on build
• Refactoring log to deploy refactorings
• Built in deployment / compare system which can be automated and used in CI / CD processes
• Good intellisense and source control support built in
• A full API (DacFx API) to work with T-SQL code and the deployment units (Dacpacs)

Why are these important?

A model of the database that is validated on build

When you build your code it is checked to make sure it compiles and any references resolve, this is a great thing as it means that your code actually works. I pretty much guarantee any database that is of significant size and or age will have code in it that does not work. It is all too common to see tables or columns dropped or renamed without modifying or deleting the code that references them like stored procedures or functions and when this happens, if the code is called then it wont work. Validating the code on build means that you see any errors and can tidy up the old broken code as well as give you some confidence, not as much as with a full suite of unit tests, but some comfort that the code still works after your changes.

Refactoring log to deploy refactorings

With the other IDEs that do refactorings like renaming columns, typically they rename the table or object, then when they deploy they do a compare and then because the table is renamed but there is no history, they drop the old object and create a new one which might be a problem in some cases. The refactor log aims to fix that when deploying from SSDT projects in that refactors are stored and run as part of the deployment script, so if you rename an object in Visual Studio, when the deployment happens and a compare is made, the object is renamed first so that it isn't dropped and re-created.

Built in deployment / compare system which can be automated and used in CI / CD processes

Many of the other systems have a compare and deploy system built in but where this is different is that you can build an automated pipeline from the output of the SSDT tool without any additional cost.

Good intellisense and source control support built in

The intellisense is great and out of the box you get TFS and Git (in 2013+ it works really easily), there are also plug-ins available for pretty much any source control provider, SSDT really gets this for free as it is in Visual Studio which comes with support.

A full API (DacFx API) to work with T-SQL code and the deployment units (Dacpacs)

This is a big one as it allows us, the community to write tools to integrate with the tooling - if there is something we would like to make better then we can.

Downsides

There are a couple of obstacles to using SSDT, the first is the complexity and the learning curve; As I mentioned, if you have a database of any real size and or age you will more than likely have code that doesn't actually work, you will have changed tables and columns and views and not updated a stored procedure or a function and because they are rarely used (if ever) no one notices. When you try to get the database under SSDT, in these cases, it will not compile so you will need to fix the issues first.

The second issue is the time it takes to do things, when the code is compiled, it is validated to make sure it compiles and is valid T-SQL, the references are checked to make sure they are valid - this takes time. To publish the database it is slow, Visual Studio takes time as you get a compare and then deploy - it is much slower than working in say c# which compiles and runs pretty quickly. There are ways around the performance but if you take SSDT as it is and regularly build and publish you will find yourself waiting a lot. This may be an issue for you, personally I find ways to minimise the time I wait and like the fact that when I do have to wait, the compiler is checking things that I couldn't on every build.

The last issue is that to get SSDT you need to install Visual Studio which is quite a hefty install in terms of disk space and the memory usage when it runs, if you are already using Visual Studio it is a "no brainer", but if you are using another IDE then you might want to consider your choice.

Visual Studio with Sql Server Data Tools with Resharper

I am going to include one more IDE, that is Visual Studio and SSDT with resharper as this gives you all the features of SSDT as well as the great navigation support that resharper offers - together, for me, they really are the killer combination.

Summary

Developing T-SQL we should use all the tools available to make us more productive, if those tools also validate the code we are writing then all the better they should be embraced and extended.

Writing code for Sql Server should be like writing c# or vb or any other language, we should have strong IDEs that help us - if you are using SSMS without even any plug-ins then please look at what is available, there isn't even any cost associated with some of the IDEs that can make your coding faster and probably better.