Closed answer technical tests and when ‘it depends’ isn’t an option

Many months ago when I was working as a SQL Server contractor, I was approached by a recruiter who was looking to fill a contract role, and as part of the initial screening, the client had asked all potential candidates to answer a simple 4-question, true-or-false SQL quiz. When I read the question, and the Read more about Closed answer technical tests and when ‘it depends’ isn’t an option[…]

Building a string of values in T-SQL

It is often far too easy to experience some behaviour in SQL Server, and then extrapolate your belief from that. One example that is seen frequently on forums is the simplistic case of concatenating values into a scalar variable, however the logic that is commonly assumed is false. Take the example USE tempdbGOIF OBJECT_ID(‘CharsTable’,’U’) IS Read more about Building a string of values in T-SQL[…]

Helping the Query Optimizer with Constraints

The Query Optimizer in SQL Server is an amazing piece of code. It takes any query we choose to throw at it, evaluates many different ways of executing that query and, generally speaking, comes up with an execution plan that is going to be good enough. And it does all that in milliseconds. Sometimes it Read more about Helping the Query Optimizer with Constraints[…]

Automate DACPAC refresh with PowerShell

I’m working on a project where we have recently started to use SSDT more and more. One feature I make use of, is Database References [http://schottsql.blogspot.co.uk/2012/10/ssdt-external-database-references.html], and use DACPACs as the source of these references. To keep this simple for the distributed development team and have one version of the truth, I keep the DACPACs Read more about Automate DACPAC refresh with PowerShell[…]

SSDT error SQL71501: Procedure: contains an unresolved reference to an object

I’ve recently started to use SQL Server Data Tools (SSDT) a lot more in database development. And I must say that I’m quite impressed. Having previously used database projects in VS, and fought with msbuild, SSDT is much easier. If you are starting out with SSDT, then I can’t recommend enough that you read Jamie Read more about SSDT error SQL71501: Procedure: contains an unresolved reference to an object[…]

SQL – rounding to nearest 5

Had a requirement to round some price values to the nearest £5 in a SQL stored proc. It’s easy to round to the nearest 10 using a negative value for the length parameter of the ROUND function i.e. [sql]select round(12, -1)[/sql] returns 10 [sql]select round(16, -1)[/sql] returns 20 but length must be of type tinyint, Read more about SQL – rounding to nearest 5[…]

Power and Deception of CTEs

This is a reprint of an article of mine that appeared on SQLServerCentral.com in December 2008 Introduction A customer of mine had a performance problem with a stored procedure that uses Common Table Expressions (CTEs). The performance had gone from sub second to 10-12 secs, and the approach that was being taken to diagnose and Read more about Power and Deception of CTEs[…]