INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision--seemingly purely aesthetic--can bring to the fore.

To illustrate the first of these examples I will be using the following table:

USE tempdb
GO

CREATE TABLE dbo.MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
GO

Consider the following queries, disregarding the fact that this isn't a good dynamic SQL example (I'm trying to keep it simple), and take a moment to think about the differences between them from a behavioral point of view:

EXEC
('
    INSERT dbo.MyTable
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')
GO

INSERT dbo.MyTable
EXEC 
('
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')
GO

It's quite common to insert some data into a table from dynamic SQL, and for as long as I can remember, I've believed the choice between these two forms of the query to be primarily an issue of style. My preference has been for the latter style, as I feel that it's a bit more readable. It also has the benefit of better supporting ownership chains, but that's something that, in most cases, we don't have to worry about any longer in SQL Server 2005 or 2008. But let's move beyond style and potential security issues and get to the substance. What actually happens when we run these two queries?

In the first case--insert done inside of the EXEC--the first step is that the outer statement--the EXEC itself--is parsed and validated. Now we jump down one level of context, into the string that was passed to EXEC, and that string is parsed and validated. The referenced objects are resolved, and the plan cache is checked. If there is no plan, the query is compiled. And then it's executed. Data is streamed from SalesOrderDetail into MyTable and when the query inside of the EXEC is done control returns to the calling context--EXEC--and assuming there were no errors, the EXEC is now completed.

In the second case, something quite a bit different occurs. The insert is parsed and validated, and an INSERT EXEC plan is generated. This plan does not involve SalesOrderDetail, as the query optimizer doesn't know at this point in the process where the data will be inserted from, since that step is dynamic. So the plan references something called the "Parameter Table" (plan truncated for simplicity):

|--Table Insert(OBJECT:([tempdb].[dbo].[MyTable]))
   |--Top(ROWCOUNT est 0)
        |--Parameter Table Scan

Once this plan has been generated, we again jump down one level of context, and the inner string is parsed and validated, the referenced object resolved, the cache checked, and the compiled plan executed. But what is this Parameter Table thing?

Here's where things start really diverging. Data is not, at this point, streamed from SalesOrderDetail directly into MyTable. Rather, it is streamed from SalesOrderDetail into the Parameter Table. And the Parameter Table, as it turns out, is in actuality a hidden temporary table. And not until all of the data has streamed into that hidden temporary table is control resumed by the EXEC context, and only then does the data start moving into its final home.

The natural question you might be asking yourself at this point is, just how much overhead does this Parameter Table introduce into the equation? The answer might just send you racing to check your stored procedures: the additional cost for the Parameter Table is well over 100% as compared with doing the insert inside of the EXEC. The fact that all of the data must be spooled to the Parameter Table before the insert can even begin tells us that this must be true, and it can be verified using a simple check against the sys.dm_exec_requests DMV, as in the following example:

EXEC
('
    INSERT dbo.MyTable
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')

SELECT
    r.writes AS [INSERT inside EXEC writes]
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID
GO

INSERT dbo.MyTable
EXEC
('
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')

SELECT
    r.writes AS [INSERT EXEC writes]
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID
GO

So that's that. We should avoid INSERT EXEC and try to do our inserts in the same context in which the SELECT is running--right?

Well, yes and no. There is another element at play here which I haven't yet mentioned. What if we were only inserting a few rows, and the table we were inserting into looked something like the following:

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
GO

The only difference between this table and the previous one is that this is a temporary table and the other is not. But temporary tables have their own interesting little twists, especially when it comes down to one of the key enemies in a highly-transactional system: recompilation. As it turns out, doing the insert inside the EXEC will cause the internal statement to recompile every time a new temp table is encountered. This means that if you have a stored procedure that creates a temp table, puts together a bit of dynamic SQL, and does an insert inside of that dynamic SQL, you'll now have yourself a recompilation problem.

To see this illustrated, try the following script. Here only a single row is inserted as a result of the dynamic query, but it's complex enough that the compile time more than overshadows the overhead of the Parameter Table:

USE tempdb
GO

DBCC FREEPROCCACHE
GO

CREATE TABLE #AvgTimes
(
    CPU_time DECIMAL(19,4) NOT NULL,
    insert_type VARCHAR(25) NOT NULL
)
GO

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

EXEC
('
    INSERT #MyTable
    SELECT TOP(1)
        sod.*
    FROM AdventureWorks.Sales.SalesOrderDetail sod
    WHERE 
        sod.UnitPrice > 10
        AND sod.LineTotal > 100
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Person.Contact c ON 
                c.ContactID = soh.CustomerID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND c.LastName LIKE ''Ab%''
        )
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Production.Product p
            WHERE
                p.ProductID = sod.ProductID
                AND p.Color IS NULL
        )
        AND NOT EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Sales.SalesPerson sp ON
                soh.SalesPersonID = sp.SalesPersonID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND sp.CommissionPct > 50
        )
')

INSERT #AvgTimes
SELECT
    r.cpu_time,
    'INSERT inside EXEC'
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID

DROP TABLE #MyTable
GO 5

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

INSERT #MyTable
EXEC
('
    SELECT TOP(1)
        sod.*
    FROM AdventureWorks.Sales.SalesOrderDetail sod
    WHERE 
        sod.UnitPrice > 10
        AND sod.LineTotal > 100
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Person.Contact c ON 
                c.ContactID = soh.CustomerID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND c.LastName LIKE ''Ab%''
        )
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Production.Product p
            WHERE
                p.ProductID = sod.ProductID
                AND p.Color IS NULL
        )
        AND NOT EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Sales.SalesPerson sp ON
                soh.SalesPersonID = sp.SalesPersonID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND sp.CommissionPct > 50
        )
')

INSERT #AvgTimes
SELECT
    r.cpu_time,
    'INSERT EXEC'
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID

DROP TABLE #MyTable
GO 5

SELECT 
    AVG
    (
        CASE a.insert_type 
            WHEN 'INSERT inside EXEC' THEN a.CPU_time
            ELSE NULL
        END
    ) AS [CPU time - INSERT inside EXEC],
    AVG
    (
        CASE a.insert_type 
            WHEN 'INSERT EXEC' THEN a.CPU_time
            ELSE NULL
        END
    ) AS [CPU time - INSERT EXEC]
FROM #AvgTimes a
GO

DROP TABLE #AvgTimes
GO

So what have we learned today? The choice between INSERT EXEC and INSERT inside of EXEC is not purely stylistic and has definite performance implications. Here are the rules I'll be following from now on:

• When working with permanent tables, always avoid INSERT EXEC if possible. There are some cases where it won't be possible to avoid. I only showed dynamic SQL in this post, but INSERT EXEC also applies to stored procedures. Can you safely rip apart all of the stored procedures in your system in order to avoid this issue? Maybe not quite as easily as you can rip apart the dynamic SQL within stored procedures.
• When working with temporary tables, evaluate the complexity of the operations, the amount of data to be inserted, and most importantly, test every solution! The more rows that are inserted as a result of the INSERT EXEC, the more the overhead you'll get from the Parameter Table. On the flip side, the more complex the dynamic statement, the more overhead you'll get from recompilations. Every scenario is bound to be different and you may just learn something about your processes by doing this extra bit of analysis.

On Tuesday night I was honored to present a new talk to the Cape Cod .NET Users Group that meets in Plymouth, MA. The talk is called "T-SQL Power! Learning to Harness the Under-Used OVER Clause". Following is the abstract for the talk:

First introduced in SQL Server 2005, the OVER clause is an ANSI SQL enhancement that gives you tremendous control when dealing with aggregations. By using the OVER clause, query writers can simultaneously aggregate columns based on multiple groups. The feature also enables the query engine to provide windowing mechanisms for ranking and row numbering. Leveraging these powerful language enhancements allows you to solve a surprisingly large number of difficult query problems—including custom paging schemes, data de-duplication, "top-N" problems, and complex statistical calculations. Even better, this feature can be creatively applied to help with performance optimization of certain tough queries. In this session, you will learn all of these techniques and see why, after applying the OVER clause in dozens of projects since the release of SQL Server 2005, I consider it to be one of the most powerful T-SQL features available.

I always enjoy trying out a new talk for the first time, especially when the talk is on a topic that I really enjoy. Based on some of the feedback and reactions I got, I have a few tweaks to make, but I think the talk is on the right track. My next scheduled delivery of this talk is in November, at the SQL Server Connections conference. Should be a great time!

As promised on Tuesday night, demos are attached to this blog post. Enjoy!

... or something like that.

We're featured on this week's .NET Rocks! show, discussing some of the intricacies of database design with Richard and Carl. Enjoy!

A huge thank you to everyone who attended today's session on exception handling! I had a great time and got some very good, on-point questions from the audience.  As promised, the demos are attached to this blog post.  Please let me know in the comments if you have any questions (whether or not you attended--everyone is allowed to join the party).

Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method, I received a comment telling me about a big thread on SQLServerCentral dedicated to the question of how best to split strings. So I jumped in, and went back and forth, and back and forth, and back... and forth...

Many, many messages and several revisions and re-revisions later, I am happy to present the newer, better, more bug-free SQLCLR string splitting function. Not only is it faster than the previous one I posted, but it also handles multi-character delimiters, ignores consecutive delimiters as well as pre and post delimiters, and best of all, it won't get itself stuck in an infinite loop in certain cases (always a really great stability feature).

This version walks the SqlChars character array rather than using the IndexOf method on the string, which we found to be a somewhat faster technique--and it's certainly the most scalable and memory efficient method I can imagine.

Special thanks to SQLServerCentral member Florian Reischl, who was the main person keeping the thread going with me during the last couple of days, and who managed to re-write my versions and eke out even better performance by modifying the algorithms.  Great stuff--it was definitely the most fun I've had on a technical forum in quite a long time.

But without further ado, the code:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
       )
    ]
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_Multi(object obj, out SqlString item)
    {
        item = new SqlString((string)obj);
    }

    public class SplitStringMulti : IEnumerator
    {
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                return new string(theString, lastPos, nextPos - lastPos);
            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i < stringLen; i++)
                {
                    bool matches = true;

                    //Optimize for single-character delimiters
                    if (isSingleCharDelim)
                    {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else
                    {
                        for (byte j = 0; j < delimiterLen; j++)
                        {
                            if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j]))
                            {
                                matches = false;
                                break;
                            }
                        }
                    }

                    if (matches)
                    {
                        nextPos = i;

                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

Enjoy!

Update: Please respond here instead.  Thanks for the great idea, Alex!

Please respond and let me know:

A) Whether you change it at all

and

B) If so, whether you use a fixed width font (the default font is non-fixed width)

Background, in case you're curious: I modified a newer version of Who is Active?, based on some feedback I received from Aaron Bertrand asking for right-padding of the numbers in the result.  I did this modification based on my assumption that most people won't change the default grid font, and even if they do change it they'll still use a non-fixed width font.  I tested my modification on my end and sent it over to Aaron to see what he thought, and low and behold he has changed his own SSMS settings to use a fixed width font.  Oops!  I would be very interested in finding out how many of you do the same.  If the vast majority of people do indeed leave the font unchanged, or at least in the same font family, perhaps I'll leave the right-padding as-is (I happen to like it, personally -- sorry, Aaron!).  If I'm mistaken in my assumption, I'll rip it out.

Thanks for letting me know, either way.