T-SQL

First of all, let me say it: I don’t think this should ever be done on the database side. Formatting dates is a task that belongs to the application side and procedural languages are already featured with lots of functions to deal with dates and regional formats. However, since the question keeps coming up on the forums at SQLServerCentral, I decided to code a simple scalar UDF to format dates. [Read More]

Though undocumented and unsupported, I’m sure that at least once you happened to use Microsoft’s built-in stored procedure to execute a statement against all databases. Let’s face it: it comes handy very often, especially for maintenance tasks. Some months ago, Aaron Bertand (blog|twitter) came up with a nice replacement and I thought it would be fun to code my own. The main difference with his (and Microsoft’s) implementation is the absence of a cursor. [Read More]

EDITED 2011-08-05: This post is NOT about the "correct" way to implement table-level check constraints. If that is what you're looking for, see this post instead. Today on SQL Server Central I stumbled upon an apparently simple question on CHECK constraints. The question can be found here. The OP wanted to know how to implement a CHECK constraint based on data from another table. In particular, he wanted to prohibit modifications to records in a detail table based on a datetime column on the master table. [Read More]

Do you use code templates in SSMS? I am sure that at least once you happened to click “New stored procedure” in the object explorer context menu. The default template for this action is a bit disappointing and the only valuable line is “SET NOCOUNT ON”. The rest of the code has to be heavily rewritten or deleted. Even if you use the handy keyboard shortcut for “Specify values for template parameters” (CTRL+SHIFT+M), you end up entering a lot of useless values. [Read More]

Bart Duncan (blog) found a very strange edge case for short-circuiting and commented on my article on sqlservercentral. In my opinion it should be considered a bug. BOL says it clearly: Searched CASE expression: Evaluates, in the order specified, Boolean_expression for each WHEN clause. Returns result_expression of the first Boolean_expression that evaluates to TRUE. If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. [Read More]