A story is told about a mother and a daughter who were cooking a roast for thanksgiving feast. The first thing the mom did was to cut the roast in half. The daughter asked her mom why she cut the roast in half. The mother answered, “I don’t know. My mother always did it that way.” So, they called the grandmother on the phone and asked her why she cut the roast in half. The grandmother replied, “I don’t know why you silly people cut your roast in half. I cut mine in half so that it will fit in my smaller roasting pan.”
This prompts me to ask the question, do you know why you do what you do? Here is an example of things I used to do in SQL Server. SQL Server used to only have locking at the page level. So, for systems requiring high performance OLTP, we would sometimes pad table definitions to consume a whole page in order to get adequate locking performance. Later, SQL Server introduced row level locking for inserts. So, we tried to work with database designs that emphasize Inserts. When row level locking was introduced for Updates, we stilll tended to emphasize inserts, because Updates often required a delete, and then an insert.
None of these behaviors are an issue since 2005, and optimizations continued to be implemented such that you don’t even have to think about those things like we did in the past. Granted, there are new issues for you to be aware. But, those original issues no longer exist.
Another cool thinig you can do in SQL Server is to write a select statement and insert it directly into a temp table, thus defining the temp table and inserting in a single statement. This technique was not a part of a transaction in the old days, and was a popular method to optimize stored procedures. The problem was, in earlier versions of SQL Server, until the query completed inserting into the new temp table, SQL Server would lock TempDB so that no other processes could create or drop tables. So, we would first create the temp table by running a query with a where clause that returned no rows. ex. WHERE 0 = 1. This would create the new temp table. Then we would insert the data into the temp table in a separate query, thus separating the data manipulation from creating the table. You don’t need to do this separation anymore; in fact, if memory serves me well, they fixed it in SQL Sever version 7 or 2000.
I still use the separation technique from time to time. But, I know I don’t have to do it for any reason other than readability, or something like that.
Are you still doing things that are no longer necessary? Why not share some that you have experienced in our comments.
Cheers,
Ben