Have you ever needed to order by a calculated column? You might have written it something like this:

SELECT LoginID, YEAR(HireDate) AS HireYear, 
	MONTH(HireDate) AS HireMonth
FROM HumanResources.Employee
ORDER BY YEAR(HireDate), MONTH(HireDate),
	LoginID

Did you know you don’t have to put the calculations in the ORDER BY? You don’t even… Read more

Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over the years to make sure that when writing my dynamic SQL it breaks less often. Note I don’t say “doesn’t break ever”, dynamic SQL is tricky stuff. The best you… Read more

Over the last few years I’ve learned quite a bit about different techniques in SQL Server. This particular one has been available since SQL 2005 and looks like it could be really handy.

Let’s say I want to calculate the total sales for by month and at the same the… Read more

Easy way to generate a restore script.

I was asked today if I had a canned restore script handy. I don’t. I’ve never really had a reason to. Generating a restore script, even a complicated one is really very easy if you know how.

For those of you wondering, there… Read more

I was asked today to take a table with a social security column and put all 0s if the column had any letters in it. This is the typical social security column that actually has more rows with ‘XXX-XX-XXXX’ than it does valid SSNs. There are even cases that look… Read more

A couple of months ago I talked about moving a login from one server to another without the password. The basis behind this is creating the login using the hashed version of the password. Using the same idea we can also “back up” a password.

The first thing to… Read more

I’ve had CROSS APPLY on the mind recently. You could probably tell since its been the subject of my last two posts. Since it’s been on my mind a lot I started to wonder if I could use it to solve some problems I’ve run across in the past. In… Read more

Earlier this week I posted The many uses of CROSS APPLY and I’m quite glad I did. I’ve been working on a series of audit queries for the last couple of weeks and got thrown a bit of a curve today. The original specs included several comparisons against a date.… Read more

Over the last few years of studying SQL I’ve noticed 4 different uses for the command CROSS APPLY.

In the first use I ever saw, and certainly the one I see the most commonly, CROSS APPLY is used to run a function for each row of the query. This is… Read more

So I was studying for the 70-451 and ended up reading the BOL entry for CREATE SCHEMA. I honestly can’t remember why, but what I do remember is thinking “CREATE SCHEMA can do what?!?!?” but with maybe a few more exclamation points.

Now I had always thought that CREATE SCHEMA… Read more

In the same vein as Steve Jones’ series on Customizing SSMS this is a fairly basic trick, but one that not everyone appears to know.

A fairly standard layout of SSMS looks like this.

If you drill down, to say the table list, you can left-click and drag a table… Read more

I was looking for a way to put BOL on my Kindle when I found a few good links I thought I would share. FYI If anyone knows how to put BOL on an Android device it would be great!

Here is a link to large list of Microsoft books Read more

Did you know that the GO command isn’t really part of T-SQL? It’s what’s called a batch separator. And in case you were wondering that’s why you can’t put GO inside of stored procedures, functions etc.

So what does the “batch separator do? Well, it separates batches of course. Working… Read more

I was reading through Stackoverflow today and saw an interesting question. SQL inner join vs subquery. The user was comparing 3 queries and wondered why the first took significantly longer than the other 2.

Here are the queries:

Query 1: SELECT * From TabA INNER JOIN TabB on TabA.Id=TabB.Id…

Read more

So I am now an MCITP Database Developer for SQL 2008. This is a big milestone for me. A few years ago I was terrified to take any of the exams. They are expensive, and I just knew there was no way I was going to pass. It would be… Read more

I spend a reasonable amount of time looking through various forums, answering some questions and reading the answers to others. One of the things I’ve noticed is that while I frequently see system views referenced I only very rarely see the INFORMATION_SCHEMA views mentioned. This seems rather strange to me… Read more

I’ve occasionally had the problem of trying to put “smart” logging messages into a process. You know the ones, you’re trying to set up logging for a process and you really want to create a somewhat generic piece of code that you can throw into each stored procedure. Something that… Read more

I’ve been thinking recently about who writes the best documentation. Not including a professional technical writer (although they actually do fit into my conclusion). On first thought I would have thought the SME (subject matter expert) would of course write the best documentation, because they understand the processes best. Upon… Read more

This one is basic but still something that can catch you unawares if you aren’t careful. Most DBAs and developers I know use the function LEN to calculate the length of a string. Little wonder since that is exactly what this function is for. However not everyone realizes the small… Read more

I see this question in one form or another a lot. I’ve seen this or something like it probably half a dozen times in the last couple of weeks on the forums I read. To the point where it even showed up in a dream the other night (I know,… Read more