12 Steps


Learn how to use tSQLt to professionally unit test T-SQL code. To enroll in the free self-paced course visit here

Unit Test Sql Server Code

  • Posted on: 3 March 2015
  • By: Ed Elliott

In part 3 of this series of blogs on "getting more agile" I am going to look at unit testing and why we need to do it.

My code is perfect, I need no stinking tests

Ah you are like me, my code is perfect as well, good point. blog end.

actually...your code may well be perfect but:

  • a) It only got perfect by you writing the code and writing a test script to make sure it worked (you did at least test it once, didn't you?)
  • b) Everyone else in the world (basically everyone other that you and me who are the only people, dear reader, who write perfect code) write horrible code and do terrible things like change table and column definitions, the blighters
  • c) Those other horrible developers and DBAs need examples of how to use the code
  • d) They also need documentation on how it works, what it returns in all cases and what side effects it has as they are too simple to work it out for themselves, the welps

ok, I admit it, I am one of those welps - what is more, I make changes to code without examining every stored procedure, every function, every piece of application code to see if it will cause any issues - I am literally a renegade throwing caution to the wind plus I write very little documentation, I certainly never explain what I have done to a stored procedure, I don't need to. I can do this because I write unit tests and make sure that every piece of code that can run is covered by a unit test.

The tests:

  • Prove that the code I write, does what it should do (the testing part)
  • Stops me breaking any other part of the database or application, without me knowing about it and giving me a chance to either fix it or reasses the change (this is a big one)
  • Helps document the code and give examples of how it works, see https://the.agilesql.club/Blogs/Ed-Elliott/Unit-Tests-Help-Document-Your...

Tests are also an enabler for much more exciting things namely continuous integration and continuous deployment. To get CI/CD working you need a number of things, one of those is unit tests - without these the whole thing is pretty much pointless.

But I have a big database without unit tests, I can't start now

Yes you can, write tests for all new code you write and all changes you make to the existing code - but before you do that, go read this: http://www.amazon.co.uk/Working-Effectively-Legacy-Robert-Martin/dp/0131...

Source control isn't just a backup

  • Posted on: 19 February 2015
  • By: Ed Elliott

In the second part of this series on getting more agile and bringing our Sql Server development practices up to date I am going to talk about why you should get your database under source control.

First a little rambling story, I was at the Redgate Sql in the city event in London last October and the speakers asked a few times who had their databases under source control, betwen 5 and 10 percent of the people there had their databases under source control. One speaker asked "who uses source control", the person sitting next to me didn't put his hand up and then the speaker asked "who has their databases under source control", the gentleman then said he thought the first question was about databases, of course he had his code under source control, he would be stupid not to!

That really made me realise how much of a struggle it is going to be to get the worlds Sql databases under source control, but with a fair wind behind us we can do it.

So...What is Source Control?

Huh? What a silly question, it is where you put your code and it gets backed up so it is safe and if you need to you can roll back your changes and get a working copy again.

So...Why bother wih Source Control?

There is no need to bother, we deploy the code to a database and that is backed up and taken offsite - it is all good in the hood so there.

So...What is Source Control?

Well, source control does give you a backup of your code - you spend time and therefore money on writing good code, it must be safe so you need a way to back it up. Backing up the database is one way to backup your code. The fact that you should already have a good system in place for backing up and testing those backups is a good thing but a backup isn't the only thing that source control is used for.


Source Control allows multiple people to work on some code, when you check it in, it is checked to make sure no one else has changed it since you last checked out, if they have you get to do a merge (or throwaway yours or their changes). Without this checking on check-in that the files haven't changed you will very easily lose fixes and features that other people have made.

Source Control allows other people to download the version of the code you are working on, without source control you would either have to work on different parts of the code or email or put on a network share different code updates which really is a pain in the backside. Doing a regular check-out in source control is super simple and normally fast so you can be sure you are always working on the lastet code.

Cleaner code

There are two aspects of cleaner code I want to talk about, the first is without source control you don't have an easy way to see what a file used to look like so you can end up with large swathes of code commented out or behind blocks that look like "IF 1=0 BEGIN ..." . When you are debugging or looking at a piece of code you haven't looked at before, this sort of thing really doesn't help and it just a waste. With source control you can happily delete parts of the code that aren't being used and check the deletions in. If anyone ever does need them back then they can find them easily.

The second aspect is a little more controversial. It has become pretty common practice to write code and to prove how good your code is, instead of making your code work and look neat and simple (you may be doing this but some people don't, trust me on this!) time is spent writing comments in the header, something like:

* Name: proc_to_end_all_procs()
* Parameters: @id int - the id of the thing you want to get
* Created Date: 1 April 2010
* Changes: Added extra widget thing - EE 1 April 2011
* Removed extra widget thing - EE 2 April 2011
* Removed extra widget thing - EE - 1 April 2011
create procedure proc_to_end_all_procs()

All this information is probably wrong and with source control, completley redundant, if we start with the name - we know the name of the procedure as we have a semi-encrypted but still human readable string at the beginning of the stored procedure that looks like "create stored procedure proc_to_end_all_procs()". Next we have the parameters, which according to the procedure header is wrong - although this is an example, this is quite common. Finally we have a list of changes, the strict process controls around this list are quite easy to get around, it takes real discipline to keep it up to date and accurate.


Why would you spend time adding when you did something when you get it for free when you check it into source control?

Why would you document what you have changed when you get it for free with source control?

Obviously you shouldn't!

To digress slightly, there are actually three valid use cases for procedure headers, the first is copyright information, if you require it can go in there. The second is if you have examples on how to run the stored procedures (although you should really have unit tests for that). Finally the only other thing that should go into the comment headers is ascii art, perferably of donkeys but you are free to experiment with whatever you like.

Source control is an enabler for CI/CD

Yes that is right, source control is an enabler for otehr technologies - you can't do those without source control and they all add business value and have a whole heap of benefits we will discuss in a future blog.

You can blame people for their rubbish code

If you have source control you can see who did what and if you use decent check-in comments, why. This should never be used for ridicule but to find out if it was you who broke everything or not, before you ridicule the person responsible. Without source control, you can never really be 100% sure who did it.


Source control gives you so much more than just a backup, it really does enable a whole new world of modern, possibly agile, Sql development - embrace it and use it and never look back.

Steps to become more agile with Sql development

  • Posted on: 12 February 2015
  • By: Ed Elliott

In this series of posts I am going to talk about what it is we need to do, as Sql developers and DBAs to improve our working practices so that we take advantage of modern development patterns and practices to improve the quality of our work, the time it takes to implement features, lower the risk of deployments and have more fun as well.

This blog is focused on becoming more agile, but even in a strict waterfall environment you can still take the methods we will talk about to improve your processes, just because you have created your design up front and had it signed off, doesn't mean that you cannot write unit tests and have an integration server, it just means you are more formal in your design, your ability to handle changes and your documentation which isn't necessarily a bad thing.

What are the steps?

Broadly speaking I think there are about 12 things we can and should so, this number might change slightly as I write the posts but we are looking at these categories:

  • Development IDE
  • Source Control
  • Unit Tests
  • Integration Tests
  • Performance Tests
  • Automatically deploying code changes
  • Automatically deploying data changes
  • Continuous Integration
  • Build notifications
  • Process for tracking work
  • Process for tracking bugs
  • Writing clean code

So here we go part 1 is already available https://the.agilesql.club/Blog/Ed-Elliott/Getting-More-Agile/Choosing-a-...

If anyone has anything that they think I have missed please let me know or share your insights with everyone via a blog or article!

Choosing a Development IDE for Sql Development

  • Posted on: 12 February 2015
  • By: Ed Elliott

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.


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).


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


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:

Redgate Sql Prompt

ApexSql Complete:

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:

dbForge intellisense

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.

Sql Manager lets you install like the Delphi UI!

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.


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 Navigation support

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.


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.


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.

Site Search with Duck Duck Go