Having used RedGate’s SQL Source Control for many years, I have recently started using their ReadyRoll product. I like the idea of separating the tSQLt framework and tests into a different project (entirely separate from the application objects) and I also like the hybrid approach of using proper migrations for tables whilst still treating procedures, views and functions etc. as code. I am a huge fan of TDD for databases and in this post I talk about one way of integrating ReadyRoll and tSQLt – specifically setting up your development projects.

I am a long-time user of RedGate’s SQL Source Control where unit tests and application code (tables, procedures, views, functions etc) all reside in the same SQL Compare “project”. SQL Compare does a good job of separating tSQLt framework and tests from the application code at build/release time. However, because of the sheer number of tests, when committing code and tests, I normally go through a manual process of moving tests out of the “Stored Procedures” folder of the RedGate project structure into one of a number of “Tests” folders (but still part of the same RedGate project). This is a pain point, especially when the number of tests runs in to the thousands. I have also had a few issues with the SSOC v5 implementation of migrations for which, when I last looked at it, RedGate seem unable to provide a solution or workaround. Hence my switch to ReadyRoll.

The first thing I found is that it is quite a different operational paradigm but more of that in a future post. There seems to be surprisingly little information out there about how to follow good test-first database development practices with tSQLt when working with ReadyRoll projects. So since ReadyRoll is a RedGate product, my starting point was their own documentation. I found this tutorial to be a good starting point although the focus is on integrating the very useful tSQLt Adapter for Visual Studio from Ed Elliott (b | t).

As noted in the tutorial, that requires the use of Programmable Objects, so it will not work with the ReadyRoll Core edition which is included with VS2017. In other words you’ll need a licensed version of the full ReadyRoll tool. If you don’t have it, you can get a 28 day trial of ReadyRoll in the Visual Studio Marketplace. NB: This may have changed since RedGate changed their software licensing so that ReadyRoll is only available as part of the full SQL ToolBelt.

For reference, my development environment is Visual Studio 2015, SQL Server 2016 and ReadyRoll v1.14.21. So let’s get into the detail, starting with the set-up of the first ReadyRoll project in Visual Studio that will hold the objects, code and any static data that are required to make our application work.

Creating the AppDb Project

In Visual Studio, create a new ReadyRoll project called AppDb. This project will hold all of our application code but not the test framework or any tests.

In AppDb project properties, under Programmable Objects, select “Import into separate files”. This will allow us to treat procedures, views and functions as code which can be easily overwritten, as compared with tables which must be migrated to maintain state.

In project properties, under Offline Schema Model, check “Sychronise schema objects to individual files during import”. This will ensure that a standard “CREATE TABLE” statement is maintained for every table. These are treated as read-only so cannot be used to modify the table structure but do serve as a useful mechanism for understanding a table at a glance and also how the structure may have changed over time (via version control).

Finally, under Semantic Versioning, I usually enable “Apply semantic versioning to incremental migrations”. This means that migration scripts will be grouped by version in the migrations folder which will contain sub-folders named “1.0.0”, “1.0.1”, “1.1.12” etc. This can be particularly helpful as the number of migration scripts in a project grows over time.

Our AppDb project now looks like this:

Now we need to ensure that ReadyRoll doesn’t accidentally import any tSQLt framework or test objects into the application database. Right-click the AppDb project and select “Unload Project“, then right-click the project again and select edit AppDb.sqlproj. This opens the actual project file which is xml-formatted so relatively easy to edit – if you are very careful :-). Find the setting <SyncOptionIgnoretSQLt>True</SyncOptionIgnoretSQLt> and move it from the commented out section to the uncommented section, like this:

I think ReadyRoll ignores tSQLt by default anyway but I always like to have these settings explicity declared so that the intent is clear for any developers coming along behind us.

Using ReadyRoll means, at least in theory, that you can choose to write code in Visual Studio itself or against a SQL Server instance. The latter option requires using ReadyRoll to import any schema and code changes into Visual Studio but is probably a more familiar development practice to most SQL developers. To do this, we next need to deploy our empty AppDb to a sandbox so that we can start coding. Alternatively, if the database already exists with some objects, then you can start by importing those objects. After deploying AppDb you should see output like this:

Creating the AppDb.tests Project

The next thing we need to do is create a second ReadyRoll project to hold the tSQLt framework and any tests:

In Visual Studio, create a new ReadyRoll project called AppDb.tests. This project will hold the tSQLt test framework, all unit tests and test helpers but no application code.

In AppDb.tests project properties, under Programmable Objects, select “Import into separate files”. I would also enable “Group programmable object files by schema” – if your database projects are anything like mine you will quickly get to the point where you’ll have hundreds (or even thousands) of tests and you really want to organise them properly from the start. I believe that separating them by schema (a.k.a test class) makes the most sense.

In project properties, under Offline Schema Model, check “Sychronise schema objects to individual files during import”. Because there won’t be too many tables in the tests project I usually leave “Group programmable object files by schema” unchecked.

Under Semantic Versioning, I usually leave “Apply semantic versioning to incremental migrations” disabled for test projects. Generally speaking, the tests project will consist largely of code and very few migration scripts so they don’t need to be separated out by version.

Our AppDb.tests project now looks like this:

Next we need to ensure that ReadyRoll will include all tSQLt framework and test objects. Right-click the AppDb.tests project and select “Unload Project“, then right-click the project again and select edit AppDb.sqlproj. Find the setting SyncOptionIgnoretSQLt and move it from the commented out section to the uncommented section then change the value to False, like this:

We don’t want to maintain two copies of the application code so we need to create a dependency between AppDb.tests and AppDb. In Visual Studio, select the AppDb.tests project then under the Project menu, select Project Dependencies. In the resulting dialog, check the name of the application database (AppDb in this case) to indicate that AppDb.tests now has a dependency on AppDb. This will ensure that the application project is re-built/deployed before the tests project.

Finally, we need to ensure that both projects are pointing to the same sandbox database. This will ensure that the project containing the the application code and the tests project are combined into a single target against which we can write code and create/run tests. In AppDb.tests project properties, under Debug, edit the target connection string so that it points to the same target database as as the main application database – AppDb in my case.

Writing Code and Tests

This post is about working with ReadyRoll rather than writing application code or tests so I am only providing high-level coverage of this part:

My example also includes some static data and covering tests which we are also going to manage using ReadyRoll and sp_generate_merge.

Importing application code changes into Visual Studio

As we are using semantic versioning, first create a new version folder called “1.0.0” under the Migrations folder in the AppDb project in Visual Studio.

Then, in the ReadyRoll window, if AppDb in VisualStudio doesn’t contain any objects you will see an option to “Import Database”. Check that ReadyRoll is pointing to the sandbox instance of AppDb that contains your first set of database objects and click “Import Database”. If the project already contains any code artefacts, you will instead need to click the “Refresh” or “View Changes” button to check for changes in the sandbox.

In the resulting window, note the following:

The ReadyRoll window shows new or changed objects that have been identified including schemas, tables and stored procedures. This window also details all the changes that have been identified including creates, permissions and extended properties etc.

We can see that ReadyRoll has imported three migrations scripts. These are the “001_20171119-1257_gml.sql” migration script that we can see under /Migrations/1.0.0 and which contains the CREATE SCHEMA and CREATE TABLE statements (but not code objects such as stored procedures).

We can also see that the individual schema and table scripts have been imported for reference.

There is no mention of static data because we have not defined any tables that should be included as static data within ReadyRoll.

Note also that although we also added the tSQLt framework and some tests, these objects have not been picked up. This is the expected behaviour because we configured AppDb to explicitly exclude such objects.

Click “Refresh (Verify Scripts)”. This validates the imported DDL against the SHADOW database.

It is good practice to rename any migration scripts to something more descriptive (although retaining the leading number as this governs the run order) e.g. from “001_20171119-1257_gml.sql” to “001_baseline_objects.sql”.

Importing static data into Visual Studio

There are two ways to manage static or reference data with ReadyRoll, either the ReadyRoll-native (aka “online”) approach which generates INSERT and UPDATE statements in migration scripts or using an open-source tool to generate idempotent MERGE scripts (aka “offline”). Both of these approaches are covered in detail in the ReadyRoll Documentation along with the advantages and disadvantages of each approach.

Although it is a little more work, I prefer to generate MERGE scripts using sp_generate_merge because, for smaller data sets, I like the flexibility I get from MERGE. The resulting scripts are stored in a dedicated folder in the AppDb project and there is one script per table which makes it easier to understand the expected state of the data at any point in time (as opposed to trawling through multiple migration scripts or re-building the database at a particular version from source control).

In Visual Studio, in AppDb, create a new project folder called “Static Data” then add the first script to this folder.

Ensure that “Script (Not in build)” is selected and specify a name for the script. The format I’ve used here is [schema_name].[table_name].data.sql.

Then paste the MERGE statement output from sp_generate_merge in to the new static data script

Finally, we need to tell ReadyRoll to include static data scripts in any build. Open the Properties tab for the AppDb project and under Additional Scripts, check the Static Data item. As the number of static data scripts increases, it may be come necessary to change the order in which static data scripts are run under Deployment Order.

Import tSQLt framework into Visual Studio

Assuming that all we have in our AppDb sandbox at this stage is some application objects plus the tSQLt framework, start by selecting the AppDb.tests project in the ReadyRoll window. Check that the project is pointing to the sandbox copy of AppDb and then click “Import Database” (or “Refresh” if “Import Database” is not visible). Unfortunately, without further customisation of AppDb.tests, ReadyRoll will also detect and import application-scoped changes as well as tSQLt objects.

The presence of application-scoped objects in the AppDb.tests project presents two issues: (i) there can be confusion over which objects to edit within Visual Studio and (ii) it can cause deployment errors because the AppDb.tests deployment tries to deploy application objects that already exist, for example:

This error occurs regardless of whether we try and deploy AppDb or AppDb.tests first. The solution, is to configure AppDb.tests to exclude application scoped objects before we start importing anything in to the project.

Fortunately there is a section in the documenation showing how to Exclude Database Objects. This means further editing of my sqlproj file but has the advantage of being able to exclude objects by type and schema name for example. This means that I only have to update this section if I add new schemas or new object types in the schemas I want to exclude.

In Visual Studio, right-click AppDb.tests and select Unload project, then right-click the project again and select Edit AppDb.tests.sqlproj to add a section like this (I normally leave the existing commented out <ExcludeObjectsFromImport> section in place for reference):

The above section will exclude all roles, the schemas: log4Private, log4 and log4Utils plus all log4Private tables and functions and procedures in any of those schemas will also be excluded.

Now when I click “Refresh” or “View changes” in the ReadyRoll window, only the tSQLt framework (and any tests) will be imported. This also now means that which ever project ReadyRoll deploys first, there will be no errors generated from pre-existing objects. In fact, because of the dependency between AppDb.tests and AppDb added earlier, if we only deploy AppDb.tests, we still get all the application code too.

Import Application Code and Unit Tests into Visual Studio

Finally, let’s briefly look at normal development lifecycle where we have added some application objects and associated tests in our sandbox database and we need to get those into Visual Studio so we can commit our changes to source control.

Before adding our changes to Visual Studio, we naturally check that all unit tests are passing:

In the ReadyRoll window, select the AppDb project and click “Refresh” or “View changes”. In the results we can see a number of changes including the identification of a DROP COLUMN that could result in data loss. In our hypothetical use case we no longer need the column in question so can ignore that warning and go ahead and import all these changes.

In the ReadyRoll window, select the AppDb.tests project and click “Refresh” or “View changes”. In the results we can see that only unit tests and test classes have been identified. No application-scoped objects will be imported. This is true whether we run a ReadyRoll refresh for AppDb.tests first or second.

Conclusion

That’s it! We now have a fully integrated database solution which will meet the following needs:

Will deploy to a sandbox without error regardless of which project we deploy first

Clearly delineates application objects from test framework and tests

Will maintain the separation between application-scoped and test objects (subject to any additional schema/object type filters being added to the <ExcludeObjectsFromImport> section in AppDb.tests.sqlproj)

Automatically deploy or upgrade either project, with or without tests (CI build or Production) as needed

The only challenge I still have to solve is to be able to add a call to tSQLt.RunAll; to run all unit tests in the post-deployment script of the AppDb.tests project so that I can ensure all tests are run automatically as part of any tests project deployment. However, that is a post for another day.

In the next post in this mini-series I will work through how to integrate ReadyRoll and tSQLt using dacpacs which has both advantages and disadvantages over the above method.

I would like to thank Carol D for her excellent write up on her experiences. Carol gained a lot from trying her first code kata but especially the discipline of writing tests first for small pieces of functionality at a time. Steve also made another good contribution and describes the new experience of using TDD to write SQL. The other winners were Alex, Arthur and Manoj. Thanks to all the contributors for making it such an interesting discussion.

In Part 11, we delved into mocking stored procedures and explored how to populate output parameters or add a row to a table when faking a procedure. In this post, we will look at views, including writing tests against the views themselves and also how to mock a view that another object under test depends upon.

As we’re going to focus our attention on views, let’s assume that we have the following schema in place – most of which is completely unrelated to the views we’re going to create. But that’s a lot like like the real world where the tSQLt Framework’s ability to mock the relevant objects means we can safely ignore the rest of the schema. You can download the DDL for this complete strucure here.

Testing a Simple View

There are two scenarios we are interested in, the first is where we are writing tests to validate the behaviour of the view itself, the second is where we are writing tests for an object that that references a view. The first case is pretty straightforward and for the second case, it is not so well known that tSQLt allows us to mock a view in the same way that we can fake a table. So we will start with testing views then move on to how to fake a view.

So given the schema above, let’s create a view called OrderItemView. Among other things we want this view to include a Value for each line item (i.e. Price x Quantity). So the very simplest test we can write looks like this:

In this first test, we fake the OrderItem table to isolate the dependencies and add one row to the mocke version of the table. You’ll notice that we only need to populate the Quantity and Price columns for this test. Although on the real table most of the remaining columns are defined as NOT NULL, all columns on a faked table allow NULL so we can ignore them if they’re not relevant to the test in hand. This can not only save time writing this test, it also means we don’t have to go back and refactor this test in future when other non-null columns are added to the OrderItem table. When we first run this test, as expected we get this error:

Ok, so as views go this one is of pretty limited use but we do have our first passing test and we would expect this test to continue to pass as we add more functionality. Let’s skip ahead to the next interesting feature. Our view now looks like this, returning all the columns from OrderItem including DispatchDate which, as you would expect, allows NULL. However, the application that uses this data needs the dispatch date to be some fixed point in the future as it doesn’t handle NULL date/times very well. Arguably, this isn’t the best way to address this issue but for the sake of this example, our view needs to ensure that DispatchDate is never NULL.

So, to test this we want to consider what happens when there is or is not a value for DispatchDate. Again, because we’re using FakeTable, we only need to populate the columns of interest and we just need one row with a DispatchDate and one row without. The easiest way to write this test is to put the results we expected to see into a table called #expected and select the columns of interest from the view into an #actual table then use tSQLt.AssertEqualsTable to compare the two.

You may have noticed that both of these views make use of LEFT joins – even though the join columns are NOT NULL meaning we could also use INNER joins. This is based on something I learnt from Rob Farley (blog | twitter) in an old post about query simplification and redundant joins. Basically, the principle is that if we use LEFT joins, the query optimizer can choose to leave out tables that are not required to fulfill the query. For example, in the query

SELECT SUM(OrderValue) AS [OrderValue] FROM dbo.OrderDetailView WHERE CustomerId = 1234;

Only the [OrderHeader] and [OrderItem] tables need to be accessed. Because we’re using LEFT joins, the [Customer], [Employee], [Product] and [TypeOfProduct] table are ignored – as you can see from this execution plan.

This also happens to make it much easier for us when writing tests since we can also choose to only populate the minimum number of tables – those that are actually relevant to the test in hand. So if we want to test that the [ProductDescription] column in [OrderDetailView] is properly formatted, we do not need to populate the [Employee] or [Customer] tables.

However, we will need to populate [OrderHeader] plus [OrderItem], [Product] and [TypeOfProduct] or just the [OrderHeader] table and [OrderItemView]. As it’s less work, I’m going to go with faking one table and [OrderItemView]. Let’s take a first stab at this test

In this test, we use tSQLt.FakeTable to mock both the [OrderHeader] table and [OrderItemView]. The syntax for faking a view is identical to faking a table (without the identity and constraint options etc). We then add one row to the header table and a row to the view so we have a ProductDescription to test. If [OrderItemView] only referenced a single table, this would work but as there are multiple tables and there is no BEFORE trigger, we get the following error when we try to compile the procedure.

Msg 4405, Level 16, State 1, Procedure test_OrderDetailView_formats_ProductDescription_on_all_values_present, Line 11View or function 'dbo.OrderItemView' is not updatable because the modification affects multiple base tables.

This is a SQL Server error (not tSQLt) and the solution is to use dynamic SQL to populate [OrderItemView]. This only gets compiled and validated at run time, by which point [FakeTable] will have created a table called [OrderItemView]. So our new INSERT statement might look something like this:

Interestingly, if you are testing CustomerName or OrderTaker you don’t need to fake [OrderItemView] at all, just add a row to [OrderHeader] and either [Customer] or [Employee] (depending on what you are testing). Because all our joins are redundant, the view (and indeed the tables it refers to) are not required to get the Customer or Employee name. For example:

As the SQL Developer community grows to embrace the benefits of test-driven development for databases, so the importance of learning to do it properly increases. One way of learning effective TDD is by the use of code kata – short practice sessions that encourage test-first development in baby steps. Thanks to the guys at Red-Gate, I have a limited number of licences for SQL Test to give away free – just for practicing a bit of TDD and telling me about it.

All you need to do is to try out a code kata using T-SQL, either on your own or with a colleague, then tell me about it. This could be just a paragraph or two in a comment on this post or an entry on your own blog which links back to this post. Write about which kata you chose and why, how you wrote and tested the code and what you gained from the experience. I will pick the five best write-ups (from a mixture of blogs and comments) and send each of those contributors a single-user licence key for Red-Gate’s SQL Test. You can use any test framework as long as your tests are written in T-SQL.

You could even publish your experiences as an article on Simple-Talk. They pay for original content and provided you include a link back to this post, you could still win a SQL Test licence. Although you’ll have to be quick to allow for the editorial lead time between submitting an article and it being published.

SQL Test

Regular readers will be aware of my enthusiasm for tSQLt the open-source unit testing framework for SQL2005+. I’m not the only one that rates this library since Red-Gate liked it so much they have used it as the platform on which they built SQL Test. This graphical test manager is a plug-in for SQL Server Management Studio, originally developed during one of Red-Gate’s famous down-tools weeks and now an established part of their SQL Developer product suite.

What is a Code Kata?

According to Wikipedia, “kata is a Japanese word describing detailed choreographed patterns of movements practised either solo or in pairs… Kata originally were teaching/training methods by which successful combat techniques were preserved and passed on“. I believe the term “code kata” was first coined by Dave Thomas (blog) in his book The Pragmatic Programmer who writes:

Code Kata is an attempt to bring this element of practice to software development. A kata is an exercise in karate where you repeat a form many, many times, making little improvements in each. The intent behind code kata is similar. Each is a short exercise (perhaps 30 minutes to an hour long). Some involve programming, and can be coded in many different ways. Some are open ended, and involve thinking about the issues behind programming. These are unlikely to have a single correct answer.

What this means in test-driven programming terms is a simple, often deceptively so, logic problem which developers attempt to solve using strict test first development. People will often pair program on this task redoing the same exercise every day for a week or more pairing with different developers each day and trying to improve on the solution at each attempt. Typically, they will spend no more than 30 or 60 minutes per day on these exercises.

This practice is intended to help programmers learn to write code using TDD properly – not always a luxury we have in the real world. The aim is to write code in small baby steps each supported by tests to gain an understanding of the test-first paradigm. The code kata is a safe environment in which to experiment with ideas and best practice without any real consequences or deadlines. Peter Provost has a nice write-up on the importance of kata in learning effective TDD.

Why is SQL Different?

Although often language-neutral, many kata are written with object oriented languages in mind. Looking at how some online examples are developed and refactored, more complex kata would struggle to work in T-SQL in their current form. But whilst we don’t have many of the standard OO features in SQL Server programming, we do have a set-based language along with features like DRI, views and table-valued functions. The nature of the language will lead to different solutions and such features present different opportunities for refactoring.

I noticed recently that my friends, Dennis Lloyd (blog | twitter) and Sebastian Meine (blog | twitter) are running some one day workshops on TDD and agile practices in SQL to coincide with Red-Gate’s US SQL In the City tour.

I’ve seen Dennis and Sebastian present, not to mention having had various deep technical discussions over beer or dinner and these guys really know their stuff. As the authors of tSQLt, the leading unit testing framework for SQL 2005+, they really understand both the patterns and practices of test-driven development and how to apply agile techniques, including TDD, to the database development paradigm.

The course covers test-driven development practices and how they can be applied to databases including how to isolate test dependencies and mock database objects. These last two are critical to being able to successfully implement TDD for databases without wasting time on unnecessary setup – the usual reason why people give up with TDD in the SQL space. The course also covers continuous integration and source control as they relate to database development. At the end of this one day program you should have a much better understanding of a range of agile patterns and practices and how to apply them in the real world.

Dennis and Sebastian are running this pre-con at just four of the six SQL In The City events and the price includes free or reduced-cost licences for selected products from the Red-Gate developer suite. The venues are San Francisco, Chicago, Boston and Seattle and my advice is go here as soon as possible to book your place before they fill up. I should point out that I get no payment if you book on any of these days, I just think it’s a great opportunity to learn from two of the foremost experts in this field. In fact, if I could get away from the UK I’d be going to one of them myself.

Practicing code kata is an established practice in agile shops but many kata are designed with object-oriented languages in mind and do not not always lend themselves to being reproduced in a declarative, set-based language like T-SQL. So I have created this new kata specifically for SQL. Enjoy…

User Story

My name is Milt Pixney and I think I know a bit about SQL although the DBA won’t let me read to or write from any tables or even execute any stored procedures. I want to be able to write simple queries against a summary of recent toy sales aggregated by year, quarter and category so that I can provide a range of MI reports to the business.

Suggested solution: Create a view or user-defined function to summarise the value of toy sales by year/quarter/category against which Milt can run his queries. The code for the tables is at the bottom of this post but you should create your own test data according to the tests you write.

Business Rules

Recent is defined as the last two complete years – 2010 and 2011 if you’re reading this in 2012

Show data for all Year, Quarter and Toy Category combinations – even if there were no applicable sales

Any sales of toys with no category should rollup to an “unknown” category for each year and quarter

Possible Tests

Only data for 2010 and 2011 is returned (consider what happens if there is no data for either year?)

There is a value for every quarter in 2010 and 2011 regardless of whether there is any sales data

Each quarter is listed for each year regardless of whether there are any applicable sales in that quarter

Each category is listed for each quarter regardless of whether there are any applicable sales in that category

Months are correctly translated or transposed to quarters

Totals are aggregated correctly, consider how NULL SalesValue and NULL ToyCategoryId are handled

Sales of toys with no sales category should appear as category = “Unknown”

Possible re-factoring or future enhancements

For any of the following changes, it is important that all the old functionality is still supported. You should already have sufficient unit tests to guarantee this.

Where a total is for a partial quarter this should be indicated somehow (1-3, 4-6, 7-9 and 10-12 are the standard quarters)

Make the query dynamic so that it shows data from the last two years on a rolling basis (i.e. the last 24 months including partial years)

Change the query so that only categories with at least one toy active in the quarter are visible (based on DateLaunched and DateDiscontinued)

Roll up the aggregate values to show annual totals for each category

Milt’s SQL skills aren’t as good as he thinks so you might want to include some indicator of how the totals are grouped.

There has been some discussion recently over on the Google Groups discussion forum for tSQLt about practicing code kata in SQL. One suggestion was to try the time-honored FizzBuzz game and I present here a slightly modified version adapted to work with a non-object oriented, set-based language like SQL.

What is a Code Kata?

In martial arts, where the term originates, a kata is a set of movements practiced many times over, either solo or in pairs in an attempt to improve execution a little each time. The term “code kata” was first coined by Dave Thomas – author of The Pragmatic Programmer.

The idea is that developers working alone, or more often pair programming, will spend no more than 30-60 minutes trying to solve a simple logic problem using strict TDD principles. They may work on one problem every day for a week or two, pairing with different colleagues and trying to improve on the solution each time. Code kata encourage programmers to work in small baby steps, writing unit tests at each stage. Test-first development forces developers to design and write code differently and kata are a way of practicing that approach.

Why is SQL Different?

The use of code kata is an established practice in agile application development, but as TDD is still relatively new for SQL, the notion may be less familiar to database developers. As SQL developers, we also have to deal with the fact that the set-based, declarative programming paradigm we use in the database world does not always lend itself to solving some of the katas more commonly attempted by object-oriented programmers. So with this in mind I thought I would produce a modified version of a simple, common kata making it more suitable for SQL.

About this Kata

Having played with this kata a few times, I have modified it slightly to suit how SQL Developers think. For example, I suggest returning a result set rather than printing the results, although you could write and unit test either (I have). Working with sets is what we do and as you work though this kata you will hopefully encounter some of the same challenges in writing good tests as you would encounter in testing sets in the real world.

FizzBuzz is a popular children’s game, often played in schools where the teacher works his way round the class to each pupil in turn. Starting at the number 1, each student calls out the next number in sequence except that if the number is divisible by 3 the student calls “fizz”, if divisible by 5 then “buzz” and if divisible by 3 and 5 the student shouts “FizzBuzz”.

One piece of advice I would give you for any kata is do not rush ahead, complete each section before moving on to the next. The purpose of this exercise is to learn to code in small baby steps, building up a suite of passing tests that should continue to pass as the complexity of the solution increases. This is not about writing an optimal solution for production use right at the start, it is about learning how to do test-driven development properly.

The FizzBuzz Kata

Create something in SQL that returns a result set containing the numbers from 1 to 100. But for multiples of three the value should be “Fizz” instead of the number and for the multiples of five, “Buzz”. For numbers which are multiples of both three and five the value should be “FizzBuzz”. Your results should look like this:

Steps:

Lets divide this into different steps so, we can easily write and test this.

Return a result set that contains numbers from 1 to 100

Replace any number which is divisible by 3 with “Fizz”

Replace any number which is divisible by 5 with “Buzz”

Replace any number which is divisible by both 3 and 5 with “FizzBuzz”

Stage 2 – Refactoring

If you haven’t already done so, refactor the logic that defines the string value into a separate function that accepts a single number as an input.
Create a test to determine that the correct value is returned for a given input (thinking about minimum possible use cases) and what the behaviour is if the range is outside the range 1 – 100.
Refactor the production code to remove any loops or cursors (any loops in your unit tests are OK).

Stage 3 – Changing Requirements

A number is Fizz if it is divisible by 3 or if it has a 3 in it

A number is Buzz if it is divisible by 5 or if it has a 5 in it

(Previous rules take precendence over these new requirements)

Stage 4 – New Features

Create something in SQL that will return a list of only numbers in any given range that are Fizz, Buzz or FizzBuzz
Refactor as necessary to ensure that no looping will occur during actual execution regardless of result set size

Good Luck!

I hope you enjoy playing with this SQL-friendly version of the Fizz Buzz kata. Do please add a comment on your experiences, what you learnt and how you went about designing your solution. You could also perhaps add a a link to your code. At some point I will do a follow-up post with one of my solutions.

]]>https://datacentricity.net/2012/08/02/code-kata-for-sql-fizzbuzz/feed/31161T-SQL Tuesday #032 – A Day in the Life of a freelance Development DBAhttps://datacentricity.net/2012/07/17/t-sql-tuesday-032-a-day-in-the-life-of-a-freelance-development-dba/
https://datacentricity.net/2012/07/17/t-sql-tuesday-032-a-day-in-the-life-of-a-freelance-development-dba/#commentsTue, 17 Jul 2012 09:13:11 +0000http://datacentricity.net/?p=1143

This month’s T-SQL Tuesday is hosted by Erin Stellato (blog | twitter) with a theme of “A Day in The Life”. Erin talks about the fact that our job title is often not a real reflection of everything we do, sometimes not even anything we do. So for this month’s blog party, Erin asks us to describe a typical working day.

My Job Title

My job title “Development DBA” is what I call myself, although sometimes it still doesn’t cover everything I actually do. The role description on my current contract refers to “Senior SQL Developer”. I prefer the term “Development DBA” over “SQL Developer” because, in my mind, a SQL Developer is someone who just writes code. They may well write very good code, and do some database design, documentation maybe even a bit of query tuning – but their primary focus is writing SQL. As a Development DBA, I see my role as being broader than that. Yes I do all those things but I also expect to be the go-to guy in a wide variety of performance issues or when higher level design decisions regarding data stores need to be made. This could include archiving strategies, SDLC and change management processes or coaching and mentoring other members of the team.

So the question is how does a typical day shape up when compared to my perception of what I do.

The Start of My Day

My day typically starts earlier than it needs to so I can spend an hour working on a blog post or writing some other article before I leave for work. I find I’m at my most creative first thing in the morning – whether writing or coding. I work for myself, and although I often work on long term projects with just one company, my online profile is important – especially when I need to start finding the next gig. Today (Thur 12th July) is no different and I’m currently working on another post in my tSQLt Tutorial series which I haven’t added to in a while. It’ll be nice to get this finished today as tomorrow’s SQL In The City is giving me some focus. Red-Gate’s SQL Test uses tSQLt as its unit testing framework.

Yesterday, I started working on refactoring a business-critical piece of ETL code that needed some changes. My usual process when refactoring legacy code is to start adding unit tests to prove the existing code so that when I start refactoring I can quickly identify when I’ve broken something. Then, before making any actual logic changes I also like to run my version of the code against the original using a full set of live-like data as an integration test to compare the results of both versions of the code. All was going well yesterday until I suddenly started getting some weird test failures. The corollary of being at my best in the mornings is that I’m less insightful or intuitive later in the day. Time to call it quits, I’ll look at this again tomorrow with fresh eyes (and brain).

A Tricky Problem

I’m lucky enough to live very close to the River Thames in South East London and I can get a fast catamaran service into work. It’s only a half-hour journey, much nicer than train or tube and very relaxing – although this morning I spent the time thinking about why my tests were failing!

I don’t want to go in to specific details about the code but suffice to say that it was written based on some (perfectly valid at the time) assumptions about the cardinality of links between entities. Imagine an UPDATE statement that is written in the belief that there is only ever a one-to-one relationship between entities but in a small number of perfectly valid cases the association turns out to be one-to-many. Under such circumstances, there is no guarantee of which of those one-to-many links will be the winner when updating a single destination row. I know that the results in this scenario will be somewhat unpredictable but I would have expected any clustered indexes to play a part in the order of processing.

So after grabbing my morning espresso from the best coffee shop in Canary Wharf and getting to my desk around 08:30, I started to tackle the problem left over from last night.

After running several more tests I discovered that even if I ran the old code twice (with a DROP and CREATE in between) I was still getting different results. First, this invalidated any attempt at comparing the old and new results and second, it disproved my assumption that any clustered indexes might give some kind of ordering. The only thing I could think of was that the recompile in between the two test runs was resulting in two slightly different execution plans so my next step was to compare those.

Learning Something New

A quick by-eye comparison of the graphical plans yielded no obvious differences, although as the plans were quite complex there was always the possibility that I was missing something. So the next thing I tried was to compare the plans as raw text (just by saving off the plans as xml). Again, there were no significant differences in the overall plan but what I did notice is that there were differences in the number of rows processed by each thread of the parallel elements of the plan and also that the order in which the thread row counts were reported were different.

This makes perfect sense when thinking about parallel execution, in fact one of the reasons why parallel processing can generate lots of CX_PACKET waits is because the engine often has to wait for the last thread to finish before moving on to the next step. This isn’t necessarily a bad thing, CX_PACKET waits aren’t always an indicator of a problem.

Coming back to the problem in hand, if my UPDATE statement is trying to update one row from two possible source rows, and those rows are split between two threads then the outcome is going to depend on which thread finishes first – not how the clustered index is ordered. To prove this, I modified the original version of the code to use MAXDOP 1. Several, somewhat slower runs later and I was at least getting consistent results each time. This is no real help since it isn’t how it it works in production but at least I’d managed to prove I wasn’t going insane

It was nice to find the most likely reason for the inconsistent results and it felt great to learn something new. That’s one of the things I love about what I do, I never stop learning and any day I discover something fresh is a brilliant day. Unfortunately it didn’t help my plans for creating a valid integration test, as it’s impossible to compare something that should be immutable with a moving target.

The Rest of My Day

The rest of my day was productive but typical. I continued working on that piece of code to make the required changes, supported by unit tests of course. I also spent some time working on a database change management review document. This is a topic that I am passionate about and something I end up doing for most of my clients in some form or other. They don’t always accept all my recommendations or implement everything I suggest but I like to think that I normally leave a company’s database SDLC in a better state than when I arrived. My day in the office finished around 6pm, followed by another relaxing boat ride home and an hour or so after dinner to finish off that blog post. No work tomorrow as I’m off to SQL In The City.

]]>https://datacentricity.net/2012/07/17/t-sql-tuesday-032-a-day-in-the-life-of-a-freelance-development-dba/feed/11143Unit Testing Databases with tSQLt Part 11 – using SpyProcedure to control output parameters and other outcomeshttps://datacentricity.net/2012/07/12/unit-testing-databases-with-tsqlt-part-11-using-spyprocedure-to-control-output-parameters-and-other-outcomes/
Thu, 12 Jul 2012 21:36:04 +0000http://datacentricity.net/?p=1121

In an earlier post in this series, I introduced tSQLt‘s [SpyProcedure] in a test to prove that one procedure calls another. In this article we delve deeper into mocking stored procedures and explore how to populate output parameters or add a row to a table without any of the intervening complex logic in the procedure being mocked.

[tSQLt.SpyProcedure] allows you to record that a call is made to a stored procedure and also what values were passed to that procedure without ever calling the real procedure. You can also define additional logic that will be completed when the mock procedure is called.

For example, imagine you have a procedure, [uspDoTheFirstThing] that contains a series of complex steps that ultimately result in a single row being inserted into a table called [Final]. Those steps depend on data in half a dozen other tables and you have already written tests to confirm that this procedure does everything it should. Now you want to write a new procedure [uspAndAnotherThing] that, among other things, makes a call to [uspDoTheFirstThing] then has some other logic that builds on the row added to [Final]. Without the ability to mock [uspDoTheFirstThing], the only way to write tests for [uspAndAnotherThing] is to also pre-populate the tables used by [uspDoTheFirstThing] in addition to any setup required for the [uspAndAnotherThing] tests. That gets tedious very quickly. [SpyProcedure] allows you to capture the inputs and define the outcomes of [uspDoTheFirstThing] without all the extra set up..

This feature is well documented in the Official tSQLt User Guide but judging by the some of the questions I see cropping up in forums and on internet searches, I don’t think it is so well understood.

Arguments
[@ProcedureName = ] ‘procedure name’ The name of an existing stored procedure. @ProcedureName is NVARCHAR(MAX) with no default. @ProcedureName should include the schema name of the stored procedure. For example: MySchema.MyProcedure
[@CommandToExecute = ] ‘command’ An optional command to execute when a call to Procedure Name is made. @CommandToExecute is NVARCHAR(MAX) with no default.

Let’s start by detailing the objects of interest for this post. As “copy & paste” from web pages doesn’t always work the way it should, you can download all the code samples for this post here.

The [Final] table gets populated by [uspDoTheFirstThing] using some complex but imaginary logic and the data from half a dozen or so tables. This table is then used by [uspAndAnotherThing] later in our process.

[uspDoTheFirstThing] is just a stub for the purposes of this post. Imagine that this actually looks up data from a number of other tables and then performs a number of complex logical operations before inserting a row into the [Final] table. We will assume for the purposes of this post that we already have a set of passing unit tests for this procedure.

[uspAndAnotherThing] is the procedure we are now going to write some tests for. Aside from validating inputs, this procedure calls [uspDoTheFirstThing] which supplies a Batch ID. This batch ID is then used throughout the subsequent processing (most of which has been omitted to help you stay awake).

One of the first tests we will write is to prove that [uspAndAnotherThing] does actually call [uspDoTheFirstThing] with the correct parameters. For this, we will use tSQLt.SpyProcedure as in the below example.

This test passes so we can move on to the next piece of logic. We said earlier that [uspAndAnotherThing] needs the @BatchId output from [uspDoTheFirstThing] for all its subsequent processing. And although we should already have tested that [uspDoTheFirstThing] does output a valid Batch ID, because this is such a critical value, I want to ensure that if something unforseen happens and the Batch ID is not valid, [uspAndAnotherThing] will throw an exception. So let’s add that check now…

Adding this additional validation will cause [BlogDemoTests].[Test_AndAnotherThing_calls_DoTheFirstThing] to error like this:

Notice that this is actually an error, not a failure. The test itself passed but the extra validation on @BatchId in [uspAndAnotherThing] threw an exception because Batch ID was output (by the fake version of [uspDoTheFirstThing]) as NULL

So we need to ensure that the mock version of [uspDoTheFirstThing] created by [SpyProcedure] behaves as it would if it were real. Remember, we’ve isolated any table dependencies with FakeTable so the Batch Id can be any valid number. To do this we just need to change our SpyProcedure call in [Test_AndAnotherThing_calls_DoTheFirstThing] like this:

Now when we run the same test, the additional validation of @BatchId output value we added to [uspAndAnotherThing] succeeds and the test passes.

So what is actually happening under the hood within the mock version of [uspAndAnotherThing] created by SpyProcedure? Well, after inserting a row into uspDoTheFirstThing_SpyProcedureLog with the input parameter values, it runs whatever SQL you specify in @CommandToExecute.

The ability to have mock procedures do additional SQL tasks is actually really powerful, although .Net developers will take this ability to control mock objects for granted. For the purposes of this post, we are going to skip whatever the implementation is for this procedure and jump to the final step, where the [Final] table gets updated. And, because we like to write robust code, we check that exactly one row was updated – otherwise an error will be thrown. The code in [uspAndAnotherThing] now looks like this:

This means we need to make a further modification to the SpyProcedure call in [Test_AndAnotherThing_calls_DoTheFirstThing]. This time, we need to have the mock version of [uspDoTheFirstThing] add a row to the faked [Final] table before setting the output parameter to the same value as the [Id] column (since that is what the UPDATE in [uspAndAnotherThing] is based on). This is what the call to [SpyProcedure] now looks like:

You can see that we are not actually testing any additional functionality with these progressive changes. The test we have been writing only asserts that [uspDoTheFirstThing] gets called exactly once and is passed the correct parameters. The sequence of changes we have made as part of this post replicate the way we might have to refactor [Test_AndAnotherThing_calls_DoTheFirstThing] so that the mock version of [uspDoTheFirstThing] meets the minimum behaviours to allow us to write the rest of our tests. When testing [uspAndAnotherThing], we don’t actually care about the implementation of [uspDoTheFirstThing], all we are interested in is recording the inputs and controlling the outputs. This is the essence of mock objects.

In this post we looked at [SpyProcedure] in more detail and refactored one test to allow us better control over the outputs of a mocked procedure without having to implement any complicated, intervening logic. The next couple of posts in this series will focus on writing tests against views.

]]>1121From Hairdresser to DBA – How to start a career in IThttps://datacentricity.net/2012/06/30/from-hairdresser-to-dba-how-to-start-a-career-in-it/
https://datacentricity.net/2012/06/30/from-hairdresser-to-dba-how-to-start-a-career-in-it/#commentsSat, 30 Jun 2012 09:44:34 +0000http://datacentricity.net/?p=1040

I left school more years ago than I care to count with minimal qualifications and more or less fell into a career in hairdressing. I loved what I did for most of my time in that career but by 1999 had achieved everything I wanted to within that industry. I decided it was time for a change of direction. That new direction was IT, not the most obvious choice you might think and certainly not an easy change to make. This post is about that change and tries to offer tips for others in a similar situation.

I was prompted to write this by a recent request for help on LinkedIn from a friend I haven’t met yet who is thinking of a career change similar to, although perhaps not quite as drastic to my own.

My Story

My last job in hairdressing was as a consultant travelling around the UK doing in-salon training, and presenting at seminars and hairdressing shows. For me, that was the pinnacle of my hairdressing career – I’d run my own salon previously and had no wish to go backwards so I started looking at my options. Computers had always been an interest of mine so after much research and careful thought, I decided to make the move into the IT sector.

Having decided on such a major career change, in 1999 I started studying for an MCSE juggling a long work week with evenings and weekends studying and using up all my annual leave attending courses. I knew that I would need to be able to show potential employers that I was serious about such a dramatic change so passed my first two MCP exams before starting to look for my first IT job.

My first job in IT was on helpdesk for a small software house, a job where people skills were deemed more important than technical skills. This worked well for me as they expected to have to train staff up on their own software anyway and helpdesk was very customer-facing which meant I could make the most of everything I’d learnt about dealing with people in my previous career as a hairdresser.

Helpdesk wasn’t what I wanted to do long term but I recognised that with such a drastic change of career, I would need to prove myself first and this type of role had more in common with my transferrable skills. Within 3 months of starting my first job on helpdesk, I’d moved up to helpdesk management and systems administration. I later “discovered” T-SQL and the rest, as they say, is history.

Great Oaks from Little Acorns Grow

Or, start small, think big!

If you are planning a big career change, you have to be prepared to start on a lower rung of the ladder, possibly much lower, in order to gain experience that will lead to the role you do want. The cold, harsh reality is that just because you want to be a Network Administrator and have read the books and passed a couple of exams, an employer is not going to let you loose on their network without you having proven experience.

There is no substitute for knowledge gained “in the trenches”. When it all goes horribly wrong because some new virus is infecting networks all over the world, you won’t have time to get your books out trying to remember how to configure something you remember reading about whilst you were still a chef, hairdresser or milkman. The higher up the technical ladder you aspire to, the more that real-world experience will count.

It is my experience that, the lower down that ladder you go, the less that technical experience matters and conversely, the more the right attitude and good soft skills can be enough to set you apart from the crowd. So if you are serious about changing career, you have to be prepared to make a few short term sacrifices for the long term goal.

You need to develop a career plan, even write it down if that works for you. Don’t plan further ahead than three years to begin with. You will find that your plans will change once you are actually working in IT and get a better idea of what the different types of job entail. My original plan was to complete my MCSE and become a Systems Administrator, possible specialising in Exchange. Instead, after completing my MCSE, MCDBA and MCAD; then having worked through roles as a Production DBA, here I am now doing freelance database design, development and tuning along with some coaching and .Net development.

When you start applying for jobs in IT, look for roles where the skills the employer is looking for align with what you can already do. Typically helpdesk, 1st line support or even junior development are the kind of roles where the soft skills you can pick up in other careers are more important than having a wealth of technical skills.

On the subject of development, if your chosen field is web development, have you created any websites that you can include links to in your CV? These might be for local businesses or perhaps a charity that you’ve been involved with. Words of caution here however, make sure it’s a good web site. Four static pages written in FrontPage is not a good advert for your skills!

What Have You Got to Offer?

If you are at this point in your life, you need to try and think strategically. If the only experience you have of a particular technology is passing an exam or two, this is probably not enough to get you the job you want. You have to be prepared to start with the job you can do (at least in an employer’s eyes) rather than the job you want.

If you are considering a major career change, you have to think very carefully about the skills you have now and how they might relate to a potential employer in your new field. To coin a phrase, it’s not about what the employer can do for you; it’s what you can do for them. What aspects of the job you do now would be of interest or value in the IT field? What skills do you have that would be readily transferrable? This might be the ability to build diverse relationships working in sales, people skills gained in a call centre or the ability to quickly get to grips with new technologies in a technical (but non-IT) field. You might need to think outside the box to answer these questions (see below).

In my case, I was short on IT experience but my years in front of the public meant that I was strong on soft skills. Whilst I couldn’t hide the fact that I was in hairdressing, my CV focussed on transferrable skills like interpersonal skills, coaching, influencing and man-management.

Your CV is a Billboard

When was the last time you bought a burger from a fast food restaurant that actually looked like the one on the TV ads? The burger you see in adverts or on hoardings is (allegedly) the same as the one they serve up in their restaurants just presented in the best possible way. You are that burger.

Your resume is just an advertisement. I am not suggesting you lie on your CV – that is a really bad idea – but you can still present yourself in the best possible light.

For example, my final role in hairdressing was as a technical consultant for a major hair product manufacturer. This involved planning and delivery of training plans within larger salons, working on stage or behind the scenes at big hairdressing shows, managing a team of other trainers and assisting telephone helpline staff with product queries from hairdressers. Also, as the only computer-literate member of the team I became the team’s power user, designing simple spread sheets (e.g. time and activity tracking, expenses etc.) or helping configure Lotus Notes.

So in addition to emphasising my people skills, my CV at that time included the following bullet points:

These were all aspects of the job I was doing at the time, I just chose to present them in a way that potential employers in the IT sector would be able to relate to. If I’d talked about doing hair shows and visiting lots of salons and building relationships with hairdressing wholesalers it would have been much harder for a potential employer to see that I had suitable skills and my CV would have ended up in the NO pile.

Don’t Burn Your Bridges

Don’t assume that the only way you can achieve a dramatic career change is to change employer. Getting the right job with the right company isn’t easy and if you have no experience in that role, a potential new employer is taking a bigger risk. But if you have good standing in your current company, try and build relationships with the IT team – especially any IT managers that you deal with. Managers often prefer to recruit people they or someone on their team knows but being able to get a reliable “off-the-record” reference is the next best thing. It’s much easier for a hiring manager to get that reference if you already work for the company. Obviously you wouldn’t be able to do this without the support of your current manager. Larger companies especially may be more likely to be open to such sideways career moves as they may have better staff retention policies.

There may be a number of reasons why an internal move isn’t an option. These could include lack of support from your current manager, or the company may be too small or the IT team located too far away to make the jump internally. For me it was the latter, I was field-based and lived in Southampton whilst the IT department was based on the outskirts of London, over 100 miles away.

Should you tell your manager or even colleagues of your plans before you are ready to give notice? In my experience, the answer is no. As you are changing career, it might take longer than you think to get a new position and you don’t really know what could happen in the meantime. For example, if the company need to start laying people off (not unheard of in these times) you may find that your name is at the top of the list, “as you’re planning to leave anyway”.

Another approach, depending on what you do now and what you want to do next, may be to try and get some experience in your chosen field within your current job. This typically works better if what you do now and what you want to do are more closely related. For example, I used to work with a guy who’s job was application support but he wanted to become a developer. He started trying to extend his skills by actually looking at what the code was doing, later getting to the point where he was able to recommend then actually implement bug fixes in the code. He is now doing what he want do to, development, full-time but it did take nearly a year to achieve that goal.

In a Nutshell

To summarise, these are the key points to remember:

It is never too late to change, I was 38 when I started in IT.

You can start a completely new career in IT, regardless of what you do now. But it will take hard work, patience and a little humility.

Be prepared to attend some courses and take an exam or two in your chosen field to prove to a potential employer you are serious.

If you can, try for an internal company transfer as the door is already half-open. Alternatively, can you start getting exposure to your chosen field within the job you’re doing right now.

Regardless of whether you are going for an internal move or looking externally, re-write your CV using language that a potential employer can relate to and emphasising your relevant strengths and transferrable skills. But do not lie on your resume.

Be prepared to take a lower paid, more junior position if that starts you out on your new path.

One interesting point, I thought that having all the hairdressing on my CV whilst trying to get IT jobs might cause problems but most people seemed to recognise that I had reached the top in one industry and were intrigued as to why I had made such a dramatic change to start again in another career.

Best of all, I have no regrets, in fact if I’m honest I almost wish I’d done it sooner – but then if I had I wouldn’t have had such great experiences as a hairdresser. I think it was Victor Kiam (of Remington fame) who once wrote that those people lucky enough to find a job they enjoy should stick to it. I consider myself very lucky, I’ve worked in a total of three very different careers and have absolutely loved two of them. I wish you the same luck in your careers.