What is the percentage of null values in a table for a column? This question comes up every now and then and it is pretty easy to answer this question

We will start by creating the following table

Someone was writing some queries that brought back a lot of data (and I mean a LOT!!) and after a while he got the following message

Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or or allowing file growth

That is not good, fortunately this wasn't one of our production machines but a dev/test box.

So what causes this? You might think that tempdb is o...

In this blog post I will show you a couple of ways of getting the top 2 values from a set in SQL Server. The dense_rank queries will not work if you have SQL server 2000 but the other ones should.

Let's say you have a table that has this data

100

100

100

99

99

95

95

90

You want to get the 2 highest amounts in that table the values 100 and 99, how would you do this?

Let's take a look at some posibillities, first create this table and populate it with data

If you use the FOR XML syntax in SQL Server it can be a little tricky to get the XML formatted the way you really want it.

For every table that you have in a join it becomes a child of the table before. What if you have a 3 table join and you want one table to be the parent and the two tables that you are joining to be on the same level, I will show you how you can accomplish that.

First let's create some tables and insert some data

Yesterday in the Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly post I showed you that sp_executesql is better than exec because you get plan reuse and the procedure cache doesn't get bloated.

Today I will show you that sp_executesql is better than exec or ad hoc queries when you deal with conversions in execution plans

First create this table and populate it with some data