SQL Server

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]

You may be surprised to know that not all keyboard layouts include the backtick key, and if you happen to live in a country with such a layout and want to do some PowerShell coding, you’re in big trouble. For many years all major programming languages took this layout mismatch into consideration and avoided the use of US-only keys in the language definition. Now, with PowerShell, serious issues arise for those that want to wrap their code on multiple lines and reach for the backtick key, staring hopelessly at an Italian keyboard. [Read More]

The contest is over and the winner is Jeff Moden! Champagne! I won’t repeat all the reasons why I think that Jeff deserves the award, I just want to say that I could not agree more with this result. To say it with Grant Fritchey’s (blog|twitter) words: “Of the year? I think I’d put it down to of the decade, but that’s not what the contest was.” The exceptional DBA 2011 Jeff Moden will receive: [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]

Some days ago I blogged about a weird behaviour of “table-level” CHECK constraints. You can find that post here. Somehow, I did not buy the idea that a CHECK with a scalar UDF or a trigger were the only possible solutions. Scalar UDFs are dog-slow and also triggers are evil. I also read this interesting article by Alexander Kuznetsov (blog) and some ideas started to flow. Scalar UDFs are dog-slow because the function gets invoked RBAR (Row-By-Agonizing-Row, for those that don’t know this “Modenism”). [Read More]

Time has come for the annual Exceptional DBA Awards contest, sponsored by Red Gate and judged by four really exceptional DBAs: Steve Jones (blog|twitter) Rodney Landrum (blog|twitter) Brad McGehee (blog|twitter) Brent Ozar (blog|twitter) The judges picked their finalists and it would really be hard to choose the winner if I didn’t happen to know one of them. I won’t talk around it: please vote for Jeff Moden! I don’t know the other three finalists and I am sure that they really are very good DBAs, probably exceptional DBAs, otherwise they would not have made it to the final showdown. [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]

Looks like a sneaky bug made its way from SQL Server 2005 CTP to SQL Server 2008 R2 SP1 almost unnoticed, or, at least, ignored by Microsoft. Imagine that you installed a new SQL Server instance (let’s call it “TEST”) and you want Database Mail configured in the same way as your other instances. No problem: you navigate the object explorer to Database Mail, start the wizard and then realize that you don’t remember the parameters to enter. [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]

Stanislav Kamaletdin (twitter) today asked on #sqlhelp how to backup all user databases with TDP for SQL Server: My first thought was to use the “*” wildcard, but this actually means all databases, not just user databases. I ended up adapting a small batch file I’ve been using for a long time to take backups of all user databases with full recovery model: @ECHO OFF SQLCMD -E -Q "SET NOCOUNT ON; SELECT name FROM sys. [Read More]