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

I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already been answered if they look interesting and answer questions where I can. This is a great way to not only keep up my basic skills but to collect… Read more

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to be moved from a development server to a test server, test to prod etc. Or maybe a lateral move to a new server. And frequently the DBA doesn’t and/or shouldn’t… Read more

Templates are one of those really handy tools that most DBAs I know of have either never heard of or just don’t use much. I have to admit I fall into the second category myself. I will break out templates occasionally when I’m working with something I don’t do much.… Read more

While I was at the 2011 Pass Summit I was given the following piece of code to create a comma delimited list.

 SELECT files.database_id, db.name AS DatabaseName,
		STUFF((SELECT ', ' + names.name
				FROM sys.master_files names
				WHERE names.database_id = files.database_id
				FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
			, 1, 2, '') AS NameList
FROM sys.master_files…

Read more

If you are lucky you’ve never seen an error along the lines of:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. 

But let’s face it, luck runs out. So you may want to know what on earth this mysterious fifth system database is. As of… Read more

I was scanning http://dba.stackexchange.com and ran across the following question:

http://dba.stackexchange.com/questions/31478/sql-server-script-to-delete-accounts-no-longer-in-active-directory

Basically the OP wanted to know how to get rid of “Windows” or AD logins. Mike Fal answered with a very cool script I just had to blog about. Basically it scan’s through the Windows logins in sys.server_principals and… Read more

The INSERT command has two distinct ways to load data into a table.

INSERT INTO Table (field1, field2) VALUES ('string1','string2')

And

INSERT INTO Table (field1, field2)
SELECT field1, field2 FROM Table2

Both of these are very handy and have been around as far back as I can remember. Historically you… Read more

When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options.  I’ll start at the outset by saying I use all 3 and that they each have their pluses and minuses.  I’m not trying to advocate one or another, just… Read more