Defaults hurt. They take a 3 inch splinter and try to bury it as far up and under your nail as far as they possibly can. Every database server has a task. A mission if you will. That mission is to serve the data and secure the data. Some need 32GB of RAM; some need 3GB of RAM. This one might need 32 spindles on RAID 3 billion.

You see this kind of question all the time in newsgroups/forums, someone wants to return all the rows if nothing is passed in or just the rows that match the variable when something is passed in. Usually someone will reply with a suggestion to do something like this

WHERE (SomeColumn=@col OR @col IS NULL)

The problem with that approach is that it doesn't perform well, let's take a look, first create this table

Lee Everest created a post named MongoDB vs. SQL Server - INSERT comparison where he compared inserting 50001 rows with MongoDB vs. SQL Server. So he claims that MongoDB inserts 50001 rows in 30 seconds while the SQL Server one takes 1.5 minutes. Okay so I looked at this SQL Script and can make 2 improvements

First create this table

Index DMV/DMF goodness! SQL Server 2005 and up has given us the ability to truly be more efficient in gathering information in which we can be more proactive. With everything these objects give us, a price has to be paid. We will discuss that price but first, we’ll go over a few major features that DMV/DMF has provided to us in regards to indexes to help us in our daily tasks.

Yesterday I made a post about using a queue instead of a list because the code was so much cleaner. I think it is very important that code is clean and easier to read.

But then Denis asked about the performance differences between the 2 methods. I hadn't worried about the performance, because in my case the performance was good in both cases.

But I aim to please my fellow LTDers, so I did a little performance test. And let me remind you that like most performance tests, this one ...