Source Control for Database Development with SSDT

I had never had the change of using source control for database development before I started working on my current project. I have used TFS and Subversion for SSAS, SSRS and SSIS projects in the past. Since it is very difficult to edit manually the rdl, dtsx and SSAS-related files, TFS/Subversion took the role of a glorified storage location with some version control capabilities.

With SSDT we can now enjoy the full set of capabilities of a source control system. This is by far the most compelling reason for me to use SSDT. Sure, there are other very very nice features which make SSDT way better IDE than the plain old Sql Server Management Studio so many SQL Server developers use: top 3 would be dependency/reference checking, better IntelliSense and schema compare (which I used to do with RedGate’s SQL Schema Compare in the past). But, source control is not just about convenience – it makes you and your team more productive and reduces stress levels (yeah, it does!). T-SQL is much like application code – it’s easy to modify by a developer, it’s readable and merge-able. So, it is a prime candidate for being managed using a source control system.

SSDT is free. There are absolutely no drawbacks to using it in a team db development environment and I would strongly encourage anyone who has not tried it to do so. The initial investment in learning how to use it will pay off handsomely in the longer term. Things like _old suffixes to tables and stored procedures become unnecessary, we don’t have to worry about deleting a piece of code and then losing it forever, daily backups for the purpose of not losing code, and in fact – we stop worrying about losing code in general. It will be there forever and we can track its progress (we can also see who breaks it). I’m sure most db developers would be well aware of the benefits of source control, but looking back, my projects always went ahead without using SSDT because the perceived complexity of using SSDT outweighed the potential benefits.

It is not hard. All you do is create a db project and start adding code. That’s in the form of .sql CREATE scripts. And that’s about it. Your project is configured to deploy to a SQL Server instance. When you build the solution, the output is a dacpac. When you deploy it to an instance the dacpac gets compared to what is already there and an incremental update is applied to the target environment. If you add a non-nullable column to a table which already has some data in it you will get a deployment failure. But try doing it on a live db – you’ll get the same issue, right? And since you are in SSDT if you change the name of a table you’ll get errors on all objects referencing it (like stored procedures) and your solution won’t build. Therefore, using SSDT prevents you from breaking your solution’s consistency. You can also refactor code – a right click on a function/stored proc reference allows you to change its name across the whole solution. All this plus source control. Still not convinced? Well, you may need to go through a few more db development projects (especially in a team environment), get pissed off with your teammates, lose some code, spend a few hours chasing code references to realise how important and empowering SSDT can be.

If your whole team is not on the same page, you can use SSDT solo. Import the db in SSDT and off you go – now you can tell everyone how they break your project all the time with changes which are not properly propagated across the solution. You can also import all new changes applied to the live db through the handy schema compare (SQL toolbar item). Just compare the live db to your project and it will show you all new objects, as well as all changes to existing objects in your solution. Applying these will effectively synchronise your solution with the new stuff other developers apply to the live db.

But don’t just take my word for it – download SSDT for free from: and give it a good go before you decide that you are too old for it!

Oh, and if you do think it’s too much to handle, or if you would like to get your SSDT techniques honed a little and you live in London I’d recommend either coming to work on my current project, or letting you boss know of how great SSDT is, and how he/she can empower your team and splash a little on:

Number of Weekdays Between Two Dates

There was an old post here describing some T-SQL code for finding the number of weekdays between two dates, which I wrote. It was working fine, so if you have implemented it you have not done anything wrong. However, Jeff Moden from SQL Server Central has written a post a while ago about this same problem and his implementation is a bit cleaner, and thus I would consider it better than mine. So, here is the link:

Do Business Analysts make good dimensional modellers??

Recently I had the (dis)pleasure of working with Business Analysts, who also thought that they are good in dimensional modelling. so, I had to implement BI solutions (including cubes) on top of their database design. I will show an example (about 95% the same as the actual design), where the idea of letting BAs go into dev territory does not yield the best results:


This “dimensional model” was created by an experienced BA. Some “features” are missing here:
1. The fact table had EffectiveFrom and EffectiveTo dates
2. The relationships between some Dim Tables were 1-1 ?!
3. The Time dim (the only one properly implemented on its own – on the bottom of my example) had columns like: DateTimeName nvarchar(100), DateTimeKey nvarchar(100), YearName nvarchar(100), etc..
4. The Some Tables on the top had nothing to do with the rest (in fact a colleague of mine reckons they are there to fill in the white space on the top of the A3 printout)

Another design, which is better, but still pretty bad showed up after my training on Dimensional Modelling (1hr to go through EVERYTHING, including M2M relationships, Parent-Child hierarchies, Type 2 dimensions, etc):

Obviously, the designer (a developer actually) did grasp some concepts. However, my explanation of a star schema must have been not too clear..

Hope that you had some fun with these two diagrams..and I am sure many developers get in a similar situation, especially when someone else designs their databases. But two points:

1. Ask the BAs to analyse the business and their requirements – not to design the database
2. 1 hour of training on dimensional modelling will not make you an expert

Star-Join Optimisation – Prerequisites

A colleague of mine asked recently: Do we need to have foreign keys between our fact tables and dim tables in order to take advantage of the new SQL Server 2008 star-join optimisation. I decided to ask the question at the MSDN Forums. Just now I got a reply and I thought it may be good to share with everyone:

Charles Wang – MSFT

To use star join, indexes are required on tables. FK constraints are not necessary, but it is recommended that you have FK constraints defined since without it SQL Server must depend on heuristics to detect star schema query patterns. There are some restrictions for heuristics to determine a start join query. And it may not pick up a correct start join query plan under some cases. You can find the detailed information in the section “Star Join Heuristics” of this article, Data Warehouse Query Performance. The following is an extraction of the content:

Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns. The following heuristics are applied to detect star join query patterns

1. The largest of the tables participating in the n-ary join is considered the fact table. There are additional restrictions on the minimum size of the fact table. For instance, if even the largest table is not beyond a specific size, the n-ary join is not considered a star join.

2.All join conditions of the binary joins in a star join query have to be single column equality predicates. The joins have to be inner joins. While this might sound restrictive, it covers the vast majority of joins between the fact table and dimension tables on the surrogate key in typical star schemas. If a join has a more complex join condition that doesn’t fit the pattern described above, the join is excluded from the star join. A five-way join, for example, can lead to a three-way star join (with two additional joins later on), if two of the joins have more complex join predicates.

There we go – so we need indexes, while FK constraints would be good to have but not necessary. I also wrote to the CSS SQL Server Team, so if they come back with more information, I will update this post.

SQL Server DBMS Top 1 Wish List

As an addition to Teo Lachev’s Top 10 Wishlists (SSAS and SSRS), I would like to contribute only 1 item to a possible SQL Server DBMS wishlist:

1. Source Control.

Not SourceSafe source control, but rather an automated version out-of-the-box, not relying on developers to check in/out. Rather, it should track the changes to the code as they are made, and a full version history should be available directly in the DBMS. It should not be too hard. After all, there is a nice database available, which can store code with its version numbers just like anything else.

This would make a lot of developers’ lives a bit less frustrating.

OK, a SQL code “beautifier” would also be nice, but it is not all that important…

Vote on Connect

Changes in SQL Server 2008 sysadmin group

There are some noteworthy changes in the way SQL Server 2008 handles security; apart from the single major improvement – the replacement of the Surface Area Configuration tool by Policy-Based Management.

One thing that surprised me today was that even though I was a Domain Admin and a member of the local Administrators group, SQL Server 2008 refused to let me log in. A login had to be explicitly created so I could access the instance. After some research, I found out that in SQL Server 2008 the local Windows administrators do not get mapped to the sysadmin role. Therefore, it is possible to get locked out of a server instance if there are no sysadmins on it. This is a feature, which separates more clearly SQL Server admins and Windows admins.

A further note on this topic. I would have not lost a small SQL Server war on a Dev environment recently if we were using SQL Server 2008 instead of SQL Server 2005. Now, being a Domain Admin does not necessarily win the battle for SQL Server permissions.

There is a TechNet page describing SQL Server 2008 Security Changes for further reference.

And another one, helping in case all system administrators are locked out.

Spreading Non-Transactional Data Along Time

In some cases we need to be able to analyse non-transactional data for discrete periods along a time dimension. An example of such a case is a collection of invoices, which have start and end dates for a period, but are not otherwise connected to a time axis. We may have such invoices with these properties:

Invoice Id
Start Date
End Date

One of the invoices may be:

Invoice Id: 34821432
Start Date: 2008-10-15
End Date: 2009-03-14
Amount: 15,000.00

and another one:

Invoice Id: 34934221
Start Date: 2008-12-01
End Date: 2009-05-30
Amount: 6,500.00

If the company we are building this for is daily deducting a fee for its services (e.g. funds management, software maintenance, etc.), we may have to be able to spread the amount in smaller periods, like months or days and then aggregate the smaller amounts along a time dimension.

To do this we have to first store the data in a relational table and then write some SQL to do the trick for us.

First, we should create a table valued function which returns all the dates at a specified granularity, such as days, from the Start to the End dates and the count of all the periods in between (in our case is is a count of days):

CREATE FUNCTION udf_Create_Daily_Date_Spread
      @Start_Date datetime
    , @End_Date datetime
RETURNS @Daily_Spread TABLE (
      Date_Id datetime
    , Count_Of_Days int
    DECLARE @Count int
    SET @Count = 0

    IF @Start_Date >= @End_Date

    WHILE @Start_Date <= @End_Date
        INSERT INTO @Daily_Spread(Date_Id)
        SELECT @Start_Date

        SET @Start_Date = DATEADD(d, 1,@Start_Date)
        SET @Count = @Count + 1

    UPDATE @Daily_Spread
    SET   Count_Of_Days = @Count


After having created these functions, we can use the CROSS APPLY statement to create the even spread:

SELECT             Invoice_Id
FROM Invoice_Source inv
CROSS APPLY udf_Create_Daily_Date_Spread(inv.Start_Date, inv.End_Date) cdds

After running the sample data through this code, we will get an even spread for both invoices and we will be able to attach a time dimension to them.

Even though the data size may explode after such a manipulation, Analysis Services provides an excellent way of handling even the largest sets of data. If storage is a problem, we can always choose to break down our data in less periods – instead of days, weeks or months.

Ordering Dimensions: Recursion vs Loops in T-SQL

Recently I had to build a function, which returned dimension members from a parent-child relational table. I needed to be able to order the members hierarchically and I decided to use a recursive CTE call.
Usually, we have a parent-child dimension table with structure similar to this:
Business_Skey int
Parent_Business_Skey int
with a root node with Skey of -3 and Parent_Business_Skey of -3.
Let’s assume we have a hierarchy like this:
All (Id = -3, P_ID = -3)
-GLOBAL (Id = 1, P_ID = -3)
–Europe (Id = 2, P_ID = 1)
—UK (Id = 3, P_ID = 2)
—France (Id = 4, P_ID = 2)
—Spain (Id = 5, P_ID = 2)
–North America (Id = 6, P_ID = 1)
—USA (Id = 7, P_ID = 6)
—Canada (Id = 8, P_ID = 6)
—Mexico (Id = 9, P_ID = 6)
If we do something like:
SELECT Business_Skey
FROM Dim_Business
ORDER BY Parent_Business_Skey ASC, Business_Skey ASC
We will get:
-3 (All)
2 (Europe)
6 (North America)
3 (UK)
4 (France)
5 (Spain)
7 (USA)
8 (Canada)
9 (Mexico)
Obviously, this hierarchy is incorrect, because we want to see the leaf nodes under their respective parents.
We can recursively create order, which concatenates the parent ids:
WITH b_ord(bid, bord)
SELECT  Business_Skey AS bid
 , CONVERT(nvarchar(1000), Business_Skey) AS bord
FROM Dim_Business
WHERE Business_Skey = -3


SELECT  Business_Skey AS bid
 , CONVERT(nvarchar(1000), bord + ‘|’ + CONVERT(nvarchar, Business_Skey))
FROM Dim_Business db
 INNER JOIN b_ord bo
 ON db.Parent_Business_Skey =
WHERE db.Business_Skey <>
FROM b_ord
The result of the CTE query is:
-3 -3 (All)
1 -3|1 (GLOBAL)
2 -3|1|2 (Europe)
3 -3|1|2|3 (UK)
4 -3|1|2|4 (France)
5 -3|1|2|5 (Spain)
6 -3|1|6 (North America)
7 -3|1|6|7 (USA)
8 -3|1|6|8 (Canada)
9 -3|1|6|9 (Mexico)
and the order is correct.
Because the code needed to go in a function, invoked by a number of stored procedures, .NET application and various reports, I needed the code to be quick and 
light. As some dimensions had a large number of members (50000+), which could grow with time, the code needed to implemented in a careful way. So, I decided 
to compare the recursive CTE function to a WHILE loop and a temporary table implementation:
DECLARE @c int
DECLARE @num_of_nodes int

SET @num_of_nodes = (SELECT  COUNT(*) FROM Dim_Business)

  skey int
 , ord nvarchar(1000)
 , lvl int

SELECT  Business_Skey
 , CONVERT(nvarchar(1000), Business_Skey)
 , 1
FROM Dim_Business
WHERE Business_Skey = -3

SET @c = 2

WHILE @c > 0
 SELECT  Business_Skey
 , CONVERT(nvarchar(1000), ord + ‘|’ + CONVERT(nvarchar, Business_Skey))
 , @c
 FROM Dim_Business db
 INNER JOIN #order o
 ON db.Parent_Business_Skey = o.skey
 AND o.lvl = @c – 1
 WHERE db.Business_Skey <> o.skey

 SET @c = @c + 1
 IF (SELECT COUNT(*) FROM #order) = @num_of_nodes
 SET @c = 0

SELECT  skey AS bid
 , ord AS bord
FROM #order

After comparing the results in Client Statistics and the IO reads, the 1st recursive query performs more than 20% worse than the WHILE loop query. It also trails the non-recursive query in the count of logical reads,read-ahead reads and scan counts.
It seems like in SQL Server 2005 calling WITH recursively does not work as good as coding set-based operations through WHILE loops.

Fun with T-SQL

As a tribute to some fairly restrictive .NET design I once fell victim to, I wrote this piece of code:

Use [Database]
Select [Select]
From [From]
Where [Where] = ‘Where’
And [nvarchar(50)] = ‘nvarchar(50)’
And [int] = 1
The CREATE and INSERT statements for the above command:
CREATE TABLE [dbo].[From](
 [Id] [int] NOT NULL,
 [Where] [nvarchar](50) NOT NULL,
 [On] [nvarchar](50) NOT NULL,
 [Select] [nvarchar](50) NOT NULL,
 [From] [nvarchar](50) NOT NULL,
 [int] [int] NOT NULL,
 [nvarchar(50)] [nvarchar](50) NOT NULL

INSERT INTO [From] (  [Id]
, [Where]
, [On]
, [Select]
, [From]
, [int]
, [nvarchar(50)])
SELECT 1, ‘Where’, ‘On’, ‘:-)’, ‘From’, 1, ‘nvarchar(50)’



EXECUTE AS someone else – impersonation in stored procedures

Recently, I had to implement a stored procedure, which is executed under a service account context. It had to be invoked by an Informatica workflow and its purpose was to clean a cache database table and to update all statistics on a database through invoking the sp_updatestats. As the Informatica user did not have enough permissions to perform these tasks, I had to use the EXECUTE AS in the stored procedure. The EXECUTE AS statement can also effectively be used to limit the execution scope through allowing limited permissions to the user account executing the stored procedure:
CREATE PROCEDURE [dbo].[usp_Update_Statistics] 
 clear cache
 EXEC sp_UpdateStats
Immediately I encountered various problems with the execution of the stored procedure. After some research it turned out that a few preconditions need to be satisfied before we can execute it:
1. TRUSTWORTHY setting on the database needs to be turned on:
2. The owner of both the context database and the master database must be the same. To set this property we need to:
   2.1 Find who is the owner of master:
           exec sp_helpdb
   2.2 Set the context database owner to the same owner:
           ALTER AUTHORIZATION ON DATABASE::database TO user
3. The account under which we EXECUTE AS needs to be a database/server principal. The database and server principals can be found in the sys.database_principals and the sys.server_principals tables.
4. The account needs to be granted impersonate permissions:
     GRANT IMPERSONATE ON USER::user in EXECUTE AS TO user executing the sproc
5. If a server login is specified (instead of a database user), it needs to be mapped to a database user.
Also, if SQL Server is running under a local account/service it is not possible to use the EXECUTE AS statement.
The same statement can be used to execute SQL Server Agent jobs under different context – something particularly useful when trying to run them through Integration Services.