There are several ways to make sure you do not corrupt your data in SQL Server.
Here are six tips on how to avoid committing the seven deadly sins of T-SQL coding.
Please, keep in mind that there might be many more sins you should never commit — consult The Pragmatic Programmer for a list… and while you’re at it, check out our own: How to Be an Awesome DBA (in No Time). And if after reading this article or any other article by us or anyone else on SQLite, you still manages to accidentally corrupt your data, make sure you have a good DBA to fix it for you.
You can find this and other great articles from Eric at his blog:
A Solid DBA’s Guide to Data Corruption in SQL Server. It is definitely one of the best blogs on SQL Server that I read. Enjoy!
Don’t Use SELECT INTO, INSERT … SELECT, UPDATE … WHERE CURRENT OF, TRUNCATE TABLE, SELECT INTO Statements
Did you know there are seven ways to write a SELECT INTO statement?
There are, and unfortunately, you’ve probably been writing a few of them wrong. It’s easy to fall into the “SELECT INTO is just like a table” trap. This trap has snared many an intrepid developer who thought they were simply creating a memory-optimized temporary table. In reality, however, the process that creates the temp table is evaluating one or more statements for each row in your source data set – whether that data comes from a view, another query, a stored procedure, etc. The number of queries running behind the scenes can be staggering when you have deep hierarchies and complex views nesting within other views with aggregate functions. An example might help illustrate this point: Let’s say your boss has asked you to write a query that calculates the total amount of sales revenue for each region in your company: SELECT region, SUM (sales) FROM Sales. SalesOrderHeader GROUP BY region; the statement looks innocent enough, and when we run it we see the expected results:
Region Name
Sum of Sales
Western $2,923,487.21 Now let’s adds in some views and other statements like our original view: Select * from (Select |LS|region|RS|, Sum (sales) as |LS|sum_of_sales|RS| From Sales. SalesOrderHeader Group By |LS|region|RS|) As Region Results Order by Region Result. |LS|Region|RS|; Running this statement generates a set of execution plans for all queries involved in the previous statement. Each execution plan shows what is executed by each object (in this case, one or more T-SQL statements for each row). If you look at the execution plans for both queries above, you will notice that they are subtly different. The difference is a SELECT INTO versus a regular table’s clustered index being created behind the scenes while performing a regular INSERT…SELECT/UPDATE… WHERE CURRENT OF/TRUNCATE TABLE statement. In a traditional insert into a table example, SQL Server would create an empty table and copy each column from your result set to the appropriate index of the new table. In contrast, when using SELECT INTO, SQL Server builds indexes on top of your results but it does not actually populate your new table. Depending on your data, this could result in some major performance differences when generating larger results sets. For example, let’s look at what happens to the execution plans with a few more products included in our sales report:
SELECT region, SUM (sales)
FROM Sales. SalesOrderHeader
GROUP BY region; as you can see from the execution plan below, all three queries are very similar but one of them creates an index on top of the resulting temporary table for us automatically using SELECT INTO instead of INSERT…SELECT or UPDATE… WHERE CURRENT OF: The actual plan generated by each is subtly different too.
The second example changes only slightly when we add in the additional ORDER BY clause:
SELECT region, SUM (sales)
FROM Sales. SalesOrderHeader
GROUP BY region
ORDER by Region Result. |LS|Region|RS|; Again, the execution plan is slightly different yet again; this time not because of indexes but due to an optimized GROUP BY statement. This performance difference gets even more interesting when you have larger data sets and/or complex views nesting within other views:
SELECT region, SUM (sales) AS |LS|Region Total|RS|
FROM (SELECT |LS|region|RS|, Sum (sales) As |LS|Sum of Sales|RS|
FROM Sales. SalesOrderHeader GROUP BY |LS|region|RS|) As Region Results In this case we have two additional queries being executed behind the scenes so we not only have to worry about SELECT INTO vs INSERT…SELECT or UPDATE… WHERE CURRENT OF but also the second SELECT statement. Keep this in mind when you are writing your queries and try to rewrite them so that they use SELECT INTO whenever possible, especially if you plan on running them multiple times with different result sets.
zaključak:
Using SELECT INTO is a quick way to generate new, temporary tables with indexes on top of them.
Be careful when using sub queries because they might be rewritten behind the scenes to use INSERT…SELECT or UPDATE… WHERE CURRENT OF; this can cause unexpected results depending on the result set size. When you are creating views, consider using SELECT INTO whenever possible instead of INSERT…SELECT/UPDATE… WHERE CURRENT OF/TRUNCATE TABLE. If your boss asks you why these options exist at all, just tell them it’s how Microsoft implements “partial views” (see Books online for more information about partial views).
