Posts

Today I ran into an interesting question on the forums at SQLServerCentral and I decided to share the solution I provided, because it was fun to code and, hopefully, useful for some of you. Many experienced T-SQL coders make use of FOR XML PATH(‘’) to build concatenated strings from multiple rows. It’s a nice technique and pretty simple to use. For instance, if you want to create a list of databases in a single concatenated string, you can run this statement: [Read More]

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]

Understanding Oracle Parallel Execution in 11.2 is a pain, not because the topic itself is overly complex, rather because Oracle made it much more complicated than it needed to be. Basically, the main initialization parameter that controls the parallel execution is PARALLEL_DEGREE_POLICY. According to Oracle online documentation, this parameter can be set to: MANUAL: Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behaviour of parallel execution to what it was prior to Oracle Database 11g Release 2 (11. [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]