Viewing entries tagged


Implementing SQL Source Control

Every developer working with databases and backend magic needs DB source control. If you think you don’t, then you are crazy, or will become crazy soon after you realize it. But not every developer has the money to pay for the awesome RedGate SQL Source Control tool (~$1500 per developer, or ~$6000 for a team of 5). Sometimes, the ramen noodles expression must be interpreted literally. So how do you resolve/implement this problem? You may want to follow K. Scott Allen’s database version control blog series (links below).

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

These posts are great for the conceptual background and practices when doing the change scripts and such. The problem is there is no much implementation in the series to help speed up the process. To complement the series, you can DOWNLOAD my implementation of SQL Source Control. It consist of roughly of 4 tables and 4 sprocs. If you want to implement it on your SQL Server Database, download this update script and run it against your database (make a backup first, just in case).

Here is the ERD of the version control entities:

To use it, just create your change script (maybe follow some of the advice from Scott Allen), and follow this template to add the final touches to the script. Essentially all you have to do is call the Database_Update sproc and inspect the DatabaseErrorLog table whenever you encounter a problem to find more details.

[sourcecode language="sql"] BEGIN TRY


DECLARE @DatabaseVersionLogId INT , --This is for later, you may needed if you want to report to a CI Server. @UpdateFromDatabaseVersion VARCHAR(32) = '1.2.3' ,--The database version you want to update. This is your starting point. @UpdateToDatabaseVersion VARCHAR(32) = '1.2.4' ,--The new version of the database after successfuly updated @UpdateScriptName VARCHAR(64) = N'''1.2.3 to 1.2.4''' ,--A friendly name for this script @UpdateScriptFileName VARCHAR(1024)= N'''123 to 124.sql''' ,--The file name containing this script. @CreateScriptName VARCHAR(64) = N'1.2.4' ,--A friendly name for ANOTHER script (not this one) that generates the new schema from scratch as opposed to from the @UpdateFromDatabaseVersion database @CreateScriptFileName VARCHAR(1024)= N'124.sql' ,--The file name containing the @CreateScriptName script @Tags VARCHAR(MAX) = N'1.2.4, 1.2.3, super release'--A CSV for tagging and searching your releases later on.


EXEC @return_value = [dbo].[Database_Update] @UpdateFromDatabaseVersion, @UpdateToDatabaseVersion, @UpdateScriptName, @UpdateScriptFileName, @CreateScriptName, @CreateScriptFileName, @Tags, @DatabaseVersionLogId = @DatabaseVersionLogId OUTPUT

SELECT @DatabaseVersionLogId AS N'@DatabaseVersionLogId'

PRINT ( 'All good. Update finished... now go open a beer' )




PRINT ( 'Michael Jackson''s moonwalk. The script is a piece of shit. Fix it and try again. Your database is safe.' )


END CATCH [/sourcecode]

You can download the same script HERE

Enjoy and Happy coding!



Letter from a MS Access hater!

I hate MS AccessI am a very frustrated man right now. I happen to be working on a project to “fix” a MS Access database some genius business user created with a ton of crappy forms, redundant data, thousands of data anomalies, zero relationships, unimaginable naming conventions, and the craziest data structures I have ever seen in my life. Somehow these people have been using this Access database to run their business for YEARS now, and the only reason they needed someone to look at it, is because the geniuses that created it made a giant table with columns that represented a combination of Month and Year where they would add some data as time went by. Every month they will come to this table and add a couple of columns to continue adding crap for the next month and so on. Well, they ran out of columns in the Access table (up to 255) and now they are stuck with it.

I just wanted to shoot myself in the head when I saw the db for the first time. My first suggestion was “Let’s do it right. Let’s port this to SQL Server Express (FREE), normalize it, create a couple of views, and you can still use your forms with the correct data model” but off course, they do not want me to do that, even after I explained that trying to fix this database will take more time and money because of how flawed it is, they still wanted their old stuff, full of errors and inconsistencies. They want me to fix this piece of shit. Unbelievable!

The situation made me think again about how businesses that are not in the IT space, see their IT and software development efforts as a liability instead of an asset. Why is it so hard for some companies to embrace technology? Unless your business is that of fighting against IT (and I can’t think of a business model based on that premise), they should embrace technology as a tool to improve business processes and enable collaboration between your clients, partners and your workforce. Every time I come across a corporation closing the doors of innovation, creativity and the enablement of IT to support and improve business processes, I see a dying mammoth. Such companies are doomed for failure, no matter the size or industry they are in.

Anyways… let me get back to my grinding Access DB. Ufff… I just needed to let out some steam.



SQL Convert CSV to Table. The Better Way.

A very common database operation is to transform a simple CSV string into a table so it can be used in more complex query operations. There is an overabundance of SQL functions online that perform such transformations. Here is one: [sourcecode language="sql"] CREATE FUNCTION [dbo].[fn_ParseCsvString] ( @CSVString VARCHAR(max) , @Delimiter VARCHAR(1) ) RETURNS @tbl TABLE ( s VARCHAR(128) ) AS BEGIN DECLARE @i INT , @j INT SELECT @i = 1 WHILE @i BEGIN SELECT @j = CHARINDEX(@Delimiter, @CSVString, @i) IF @j = 0 BEGIN SELECT @j = LEN(@CSVString) + 1 END INSERT @tbl SELECT SUBSTRING(@CSVString, @i, @j - @i) SELECT @i = @j + LEN(@Delimiter) END RETURN END [/sourcecode]

And you’ll use it like this:

[sourcecode language="sql"] select * from dbo.fn_ParseCSVString ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',') [/sourcecode]

The problem is the way we use those functions and the fact that their performance order is linear to the number of comma separated values in the string. That works out ok for a small number of items, but not when you want to JOIN on a very large number of CSV. When you are using the CSV functions in a sproc or a query, you'd be writing JOIN statements to the result set from the function in order to filter a real table in your database.

Let's look at a case scenario: Suppose you have an Address table in your Geo schema. Your address table has a primary key 'Id' column and other columns that are irrelevant for the purpose of this article. If you wanted to query your Address based on CSV values using your function, you'd do something like this:

[sourcecode language="sql"]

DECLARE @csv VARCHAR(MAX) = '1,2,3,4,5,6,7'

SELECT a.Id FROM Geo.[Address] a JOIN dbo.fn_ParseCsvString(@csv, ',') tbl ON a.Id = tbl.s [/sourcecode]

Here is some benchmarking on the previous query (all in microseconds): 1 second = 1000 milliseconds = 1'000'000 microseconds

1000 CSV  ~ 0.5 secs – T1: 557'272 – T2: 408'185 – T3: 446'485

5000 CSV  ~ 2.3 secs  – T1: 2'376'159 – T2: 2'307'394 – T3: 2'370'191

10000 CSV ~ 4.6 secs  – T1: 4'699'332 – T2: 4'737'877 – T3: 4'641'049

You get the idea and it gets even worse when you do left joins on CSV. Even if your tables are indexed on the actual columns you are joining to your function, sequentially calculating those values and inserting them into a table will take some unnecessary time. So here is a solution that requires not a function but instead a store procedure and the reason for the store procedure is the need to run Dynamic SQL.

[sourcecode language="sql"]

PROCEDURE [dbo].[CsvToTable] @Csv VARCHAR(MAX) , @SchemaName VARCHAR(128) , @TableName VARCHAR(128) , @KeyColumnName VARCHAR(32) = 'Id' , @Debug BIT = 0 AS SET NOCOUNT ON ; BEGIN TRY DECLARE @mySql NVARCHAR(MAX)= 'select distinct ' + @KeyColumnName + ' from ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)

IF ( @Csv IS NULL OR @Csv = '' ) BEGIN SET @mySql = @mySql + ' where 1=0' END ELSE BEGIN

SET @mySql = @mySql + ' where ' + @KeyColumnName + ' in (' + @Csv + ')' END IF ( @Debug = 1 ) PRINT @mySql EXEC sp_executesql @mySql END TRY BEGIN CATCH --Your catch logic here --EXEC dbo.Error_Rethrow END CATCH


And this is how you’d use it:

[sourcecode language="sql"] DECLARE @csv VARCHAR(MAX) = '1,2,3,4,5,6,7'

DECLARE @tbl TABLE ( Id INT ) INSERT INTO @tbl EXEC dbo.CsvToTable @csv, N'Geo', N'Address', N'Id'

SELECT a.Id FROM Geo.Address a JOIN @tbl tbl ON tbl.Id = a.Id [/sourcecode]

Here is some benchmarking on the new solution (all in microseconds): 1 second = 1000 milliseconds = 1'000'000 microseconds

10000 CSV ~ 0.2 secs – T1: 212'289 – T2: 183'635 – T3: 204'688

WOW! That's  1/5 of a second on average to query 10K records. MUCH MUCH better ah?!

Let’s check pros and cons of this solution.


  1. There is nothing faster than this solution to convert CSV into a table. Period.
  2. You can configure additional indexes on the columns you want to lookup using a CSV approach and the indexed will have an impact on the speed of the CSV look-up; versus always having a linear execution order because the function is unaware of indexes.
  3. Simple to use.


  1. It’s using dynamic SQL, and that brings all the negative things writing dynamic SQL has. I will recommend having a dedicated role to runners of this sproc so that users do not have permissions to run it. Try to always use this sproc through another sproc.
  2. The fact that is a sproc “feels” wrong, since by their nature sprocs can have side effects, although this one has none.  This is precisely the reason why a function does not allow for execution of dynamic SQL.

I think I can live with the feeling in trade of the unmatched performance this solution offers. A couple of things to note that were done to prevent SQL injection on the Dynamic SQL:

  1. Both schema and table names are provides as separate parameters of the sproc and they are put together in the code with the '.' separator.
  2. Both schema and table name are wrapped using quotename()
  3. When executing the Dynamic SQL, I'm using sp_executesql rather than just the plain EXEC() function.
  4. There is a @Debug parameter that prints the generated Dynamic SQL when debugging. This will safe you a couple of headaches.



SQL - Select rows in chaotic order

Ok, another SQL trick. How to get a row set where the rows are ordered by a specific value sequence of a particular column?

This is not a very common problem, but if you happen to need something like this, you may scratch your head for a couple of minutes before you figure it out.

So, the challenge is the following, you have a table like the one below:


If you want to return all rows with ids 4, 5, 7 and 8  in order by any column, you can do so easily using order by [the_column] asc or desc; but if you want to return those same rows in a chaotic desired order is no so trivial.

Say we want the rows ids 4, 5, 7 and 8 and we want them such that the ids appear in the following order: 7, 4, 8 and then 5

Before looking at how to do it, challenge yourself and try to come up with that query.

Ok now let’s look at the solution. It involves the use of the CHARINDEX string function. CHARINDEX returns the starting index of parameter1 in the string given on parameter2, if parameter1 is a substring of parameter2. For example:

[sourcecode language="sql"] SELECT CHARINDEX ( '23', 'AA23BB') --returns 3 SELECT CHARINDEX ( 'abc', 'aapppabcbc') --returns 6 [/sourcecode]

That means we can do something similar with a set of numbers when treated as a strings, like so:

[sourcecode language="sql"] SELECT CHARINDEX ( '7', '7,4,8,5') --returns 1 SELECT CHARINDEX ( '4', '7,4,8,5') --returns 3 SELECT CHARINDEX ( '8', '7,4,8,5') --returns 5 SELECT CHARINDEX ( '5', '7,4,8,5') --returns 7 [/sourcecode]

Now you see how easy is toget order range using this style. Finally, we can create our query using this technique and return our rows in the order we want.

[sourcecode language="sql"] select * from Person where PersonID in (4,5,7,8) order by CHARINDEX(CONVERT(varchar, Person.PersonID), '7,4,8,5') [/sourcecode]

Happy coding!



SQL MaxInt and MinInt functions

Is kind of frustrating when you are trying to write a complex query and there is that one function not provided by SQL Server out of the box to use it right away. This is the case with a function to compare 2 integers to get the Max or Min between them, similar to the Math.Max and Math.Min methods in .NET. You could create your own function and re-use it everywhere, but the first implementation that comes to your mind is a very inconvenient way to deal with this, since you cannot use it (easily) within an inline Sql query. Let's take a look at the easiest way to implement our function:

[sourcecode language="sql"] create function dbo.MaxInt(@int1 int, @int2 int) returns int as begin if @int1 > @int2 return @int1 return @int2 end [/sourcecode]

As I mentioned, there is no easy way to have this query as a subquery in a complex sql statement. And if you make it possible, the performance implications can be kinda bad. But the world doesn't end here! There is a convenient solution that will allow you to write an efficient function for it, or even better, have it inline right within your query. The trick is to double the desired value and then divide it by 2. So if you want to find the max value, find the value that is 2 times the max and then divide it by 2; if you want to find the min value, find the value that is 2 times the min and then divide it by 2. Let's take a look:

Max Function

[sourcecode language="sql"] create function dbo.MaxInt(@int1 int, @int2 int) returns int as begin return ((@int1 + @int2) + ABS(@int1 - @int2))/2 end [/sourcecode]

Min Function

[sourcecode language="sql"] create function dbo.MinInt(@int1 int, @int2 int) returns int as begin return ((@int1 + @int2) - ABS(@int1 - @int2))/2 end [/sourcecode]


And using this doubling notion with the absolute value tricks, we can elegantly write min and max comparisons right inside the Sql statement without the need of a function. Here is an example of an inline Sql statement:

[sourcecode language="sql"] select a, b, ((a + b) + ABS(a - b))/2 as maxValue from myTable [/sourcecode]

Sweet ahh?! Happy coding!


1 Comment

SharePoint Site Architecture

In this post:

  • WSS vs MOSS
  • SharePoint site architecture

SharePoint is an ecosystem of software and server products as it was described in this previous post. To understand it better let's look at the architecture and object model that it is offered with SharePoint. The basic component of SharePoint is Windows SharePoint Services (WSS from now on). WSS is a free server component that must be installed in a Windows Server OS and contains the basic framework and infrastructure that supports any other SharePoint offerings like the different Microsoft Office SharePoint Server (MOSS from now on) versions.

You can think of it the same way selling cars work. You get the basic model with a chassis, 4 tires and a steering wheel (that will be WSS) and then you can add stuff to it like a great sound system, a dynamic design, GPS and Cruise Control, etc (those nifty things being the analogous to MOSS).

So now that we have an idea of how WSS relates to MOSS, let's see exactly how their offerings stand apart.

As we can see all MOSS trims are nothing more than a very useful set of extensions and additions to the WSS platform, offering users a more powerful and pleasant experience out of the box. These extensions are supported thanks to a very complex architecture and framework that has evolved quite a bit over time.

In a nutshell the industry refers to a SharePoint installation (be it WSS only or MOSS) as a SharePoint FARM. A Farm is a collection of physical SharePoint servers  that serve the content to users. A Farm can be formed using different topologies that we'll explore later, before we get to that, let's actually see what is going to be served, how does SharePoint structures its web sites and content.

Farm:  A collection of SharePoint Web Applications.

SP Web Application:  A collection of Site Collections.

SP Site Collection: Formed by a single root site (aka Top-Level site) and a collection of sub-sites.

SP Sub-Site: A collection of pages, lists, libraries and content that are displayed through the browser.

Each of these components in the SharePoint site architecture have different rules and security settings and restrictions. As you can see, this is a tree structure and very successful way to isolate users and content to ensure confidentiality, availability and integrity in the data security. This model is very user friendly since everyone can understand a tree structure and most people are already used to it.

I always like to compare a SharePoint Farm with a physical hard drive. If the Farm is the hard drive, then we can think of the Web Applications as partitions of the Farm. Likewise Site Collections will be Folders directly on the root of the partition. Sub-Sites are also represented by folders that only go inside of the Site Collections and so on, you get the idea.

Because is easy to understand the site architecture SharePoint offers, users can quickly provision (a fancy word for 'create') web sites with the click of a button. Because of this, planning for a successful deployment and IT infrastructure to support all the greatness SharePoint offers does not come at ease. Planning the right topology that will hold all of this together is very important for a successful SharePoint deployment. More on SharePoint topology and planning in upcoming posts.

1 Comment


Crystal and SQL Reporting Services

MSSAP Why talking about reports? Reporting is one of the key elements of Business Intelligence technologies.

- The reason for this article is basically that there is a sort of dislike and panic involved in approaching a Reporting problem.

- Nobody (that I know of) likes working in reports projects.

- Reports are a different beast than OOP, with a complete different nature, programming experience and solution approach.

This is intended to illustrate and discuss the development of reports using Crystal Reports (CR) and SQL Server Reporting Services (SSRS).

CR and SSRS are 2 COMPLETELY DIFFERENT technologies that produce the same result: A Report.

Neither Crystal nor SSRS have a full object-oriented report writer. Both are object-based with a rich object model, and feature a certain amount of cascading. Additionally, Crystal supports CSS themes. However, OOP enthusiasts will need to lower their expectations and adjust to the fact that neither product supports true object-oriented features

In general we can say that SSRS contains roughly 70–75 percent of the functionality of Crystal Reports. Some companies have decided to switch from Crystal to SSRS, and others are contemplating the switch: therefore, it's very important to know what functionality may be lost during the process.


As of today Crystal Reports is the most used general purpose reporting tool in the market (about 55%) Crystal reports is owned by SAP AG (a German Company), Business Objects was acquired by SAP.

It’s been in the market since 1980, when the first Crystal Report version I. Today the product has evolved and has had 11 versions spanning over 20 years.


Reporting Services is much easier to use and has a more rich experience since it supports things like having a Chart AND a table in the same report section. Today it has about 20% of the reporting market share. SQL Server Reporting Services is owned by Microsoft, as part of subsystem that belongs to their SQL Server Enterprise Solutions branch.

It’s been in the market since 2004 and it has 3 versions so far (SRS2000, SRS2005 and SRS2008)


  • Report creation:

Both offer report wizard that helps to define a data source and build general report layout. They also support manual definition where you’ll start with an empty report and modify it from there.

  • Report data sources:
Crystal Reports SQL Reporting Services
Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, etc.Pull Model: External Data Source like a store procedure. Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, or XML.

You can specify an XML web service as the data source for an SSRS report!!! To do this you must follow the next steps:

1- You define the data source in the connection string like this:

       data source=http://localhost/test.aspx

2- Use the code below for the query syntax (in the report dataset query).

       // Syntax to query XML web service for an SSRS DataSet
         <Method Namespace = "http://localhost/TestSSRSSite/"
           Name = "GetData" >
         <ElementPath IgnoreNamespaces="True">
  • Linking Tables:

If you use stored procedures (or in-line SQL queries) directly as your data source, be aware both report products will only allow you to access the first table in the result set.

Crystal Reports SQL Reporting Services
Supports multiple tables when pushing data through datasets/XML into the report by using its tables collection database object. It allows put to place columns from != tables on the same report body respecting relationships. Supports only one result set. To use multiple result sets, multiple datasets must be created and use sub-reports to link the data from the datasets
  • Report sections and groups: Both offer similar approach to groups and report sections like header, footer, sections, groups and details area.
  • Formatting and formula options: Here Crystal offers a more powerful approach since it supports a larger variety of formatting options than SSRS.
Crystal Reports SQL Reporting Services

  1. CSS
  2. Rotate Text
  3. Watermarks
  4. Tooltips
  5. Suppressing Duplicates
  6. Date formatting
  7. Currency formatting
  8. RTF/HTML Rendering (Rich Text Format)
  9. Datatype conversion.
  10. Expressions/Calculated fields
  11. Dynamic formatting
  12. Hyperlinks

  1. Watermarks
  2. Tooltips
  3. Suppressing Duplicates (Hide)
  4. Date formatting
  5. Currency formatting
  6. Datatype conversion
  7. Expressions/Calculated fields
  8. Dynamic formatting (+ SWITCH statement for multiple condition evaluation)
  9. Hyperlinks

- No CSS, Text rotation or RTF (Rich Text Format) rendering

- Allows you to add custom code to reports, either by embedding Visual Basic code directly into reports, or by adding externally created and compiled .NET assemblies

  • Page control options:

Crystal generally provides better support for page control through the Keep Together options for both individual report sections as well as entire report groups.

SSRS allows you to define a page break at the end of a group; it will not conditionally allow you to essentially implement "widow-orphan" protection to ensure that a group or section will fit on a page. SSRS does implement a Keep Together property for the rectangle control that is very buggy at this moment and is consider an uncooked feature.

  • Report parameters and global report variables:

Both reporting products support parameters and report variables.

  • Sub-Reports:

Typically you build a sub-report when you need to display multiple levels of detail data, and/or when you need to display data from multiple data sources. In most instances you'll need to establish a link between the sub-report and the parent report.

Crystal Reports SQL Reporting Services
You can “Insert” sub-reports as reusable sections for parent reports (like a reusable header or footer)

Different charts for shared datasets can be linked to their own sub-report and be reuse across multiple parent reports.

You cannot insert a sub-report into sections of other reports. This makes difficult to build common header/footer templates.

Different charts for shared datasets MUST have their own sub-report since they cannot be used with the same result set, if the set is different than the one in the parent.

  • Charts:
Crystal Reports SQL Reporting Services
  1. Bars (horizontal & vertical)
  2. Lines
  3. Area
  4. Pie
  5. Doughnut
  6. 3D Raiser
  7. 3D Surface
  8. XY Scatter
  9. Radar
  10. Bubble
  11. Stock
  12. Numeric Axis
  13. Gauge
  14. Gantt (timeline)
  15. Funnel
  1. Horizontal Bar
  2. Vertical Column
  3. Area
  4. Line
  5. Pie
  6. Doughnut
  7. Scatter
  8. Bubble
  9. Stock
  • Drill-down capabilities:

Both reporting products support drill-down features. Crystal though its Hide property in the Section Expert and SSRS though its Hidden and ToggleItem properties.

  • Exporting reports:
Crystal Reports SQL Reporting Services
PDF, Excel, Word and RTF.

Note: No XML, TIFF or CSV support.

XML, CSV, TIFF, PDF, Web Archive (MHTML) and Excel.

Note: No Word support.

  • Programmatic access:
Crystal Reports SQL Reporting Services
  1. Extensive and complex API with a lot of 3rd party wrapper tools.
  2. Does not directly support web service report definition control (3rd party tools will)
  1. Great open architecture for implementing any function, but many CR features are in developing stage yet.
  2. Built in support to generate reports from a report URL or a Web Service.
  • Previewing in Windows Forms or in the browser:

Both reporting products support previewing reports in Windows Form or in the browser. Note that SSRS intrinsically support for the web is superior to Crystal because SSRS is essentially an ASP.NET application

  • Matrix and Cross-Tab objects:

Sometimes you may want to create reports with a variable number of columns based on the amount of data in a result set. For instance, you might have a result set of orders by year where the year is a row. You want to display a column for each year, but you may not know at design time how many years the result set will contain.

Both reporting products offer decent solutions for this situation through Crystal’s Cross-Tab Report and SSRS’s Matrix report.


Crystal Reports (Beta Release Now Available) SQL Reporting Services (CTP Now Available “Katmai”)
  1. A reduced footprint for the report designer.
  2. Interactive sorting of report columns
  3. Support for web pagination and an improved web report viewer
  4. Cross-Tab enhancements (ability to add Cross-Tab custom summaries, either repeating or one-time)
  5. Built-in barcode font support (Crystal now includes Code39 barcode fonts)
  6. Interactive report parameters (customers can change report parameters on the fly, and the developer can decide which parameters can be modified)
  7. Ability to define XML export format options.
  8. Flash integration—you can embed a Shockwave Flash (SWF) object inside reports.
  9. Improvements to the report engine for performance and resource utilization
  1. A new report control called Tablix, which combines the flexibility of the report table with the cross-tab capabilities of the report matrix.
  2. Improvements to the report engine for performance and resource utilization.
  3. A new hosting model independent of IIS.
  4. New charting tool capabilities (Microsoft has purchased the rights to the Dundas charting capabilities)
  5. SharePoint integration to access and manage reports and data sources from a SharePoint site


Crystal Reports has the benefit of being the veteran in the field and supports almost every conceivable reporting requirement there is. It has versions for developers as well as business users. SSRS is the new kid on the block and it is focused only on the developer market. SSRS is a 1.0 release and still has some growing pains to go through