Dynamic SQL and Late Binding

Mike C — Wed, 23 Jun 2010 16:54:00 GMT

We all know that dynamic SQL runs in its own scope (see here for details), and that if used improperly it can have serious security implications. But it's also important to realize that dynamic SQL evaluates your SQL statements more like a stored procedure than a regular script. In stored procedures, if the tables you reference exist the stored proc must reference the table structures as they exist at stored proc creation time. That is to say stored procs will late bind to referenced tables only if the tables do not exist at stored proc creation time; dynamic SQL does the same. If the table already exists, SQL Server validates the structure at parse time.

Consider the following SQL code. In this code we create a table named xyz in one batch. In the subsequent batch we check for the existence of table xyz, drop it if it already exists, and then recreate it. In this scenario table xyz starts with two columns named i and c; when I rebuild the table in the second batch I will change the table structure to 3 columns--i, c and x:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

CREATE TABLE dbo.xyz
(
i int,
c varchar(100)
);
GO

IF OBJECT_ID('dbo.xyz') IS NOT NULL
DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
i int,
c varchar(100),
x varchar(100)
);

INSERT INTO dbo.xyz
(
i,
c,
x
)
VALUES
(
1,
'hello',
'good bye'
);
GO

DROP TABLE dbo.xyz;
GO

This code runs successfully. Now let's convert the second batch to dynamic SQL and execute the sample again:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

CREATE TABLE dbo.xyz
(
i int,
c varchar(100)
);
GO

DECLARE @sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
i int,
c varchar(100),
x varchar(100)
);

INSERT INTO dbo.xyz
(
i,
c,
x
)
VALUES
(
1,
''hello'',
''good bye''
);';

EXEC (@sql);
GO

DROP TABLE dbo.xyz;
GO

The dynamic SQL example returns the following error message:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'x'.

Why is this? Well the xyz table exists, but the column x referenced in the dynamic SQL INSERT does not exist at parse time. It will exist after the CREATE TABLE statement is executed, but the INSERT statement parse errors out before the dynamic SQL is executed. If you comment out the final DROP TABLE statement you can see that the DROP TABLE and CREATE TABLE statements in the dynamic SQL were not executed. If you're doing something like this with dynamic SQL you can ensure that the DROP TABLE and CREATE TABLE are executed before the INSERT statement is parsed by breaking up your dynamic SQL like below:

DECLARE @sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
i int,
c varchar(100),
x varchar(100)
);';

EXEC (@sql);

SET @sql = N'
INSERT INTO dbo.xyz
(
i,
c,
x
)
VALUES
(
1,
''hello'',
''good bye''
);';

EXEC (@sql);
GO

As an interesting (if not entirely useful) aside, if you create the three column table above, insert data into it, then drop it and recreate with two columns, the dynamic SQL succeeds. It appears to remember the old table structure and runs just fine.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

CREATE TABLE dbo.xyz
(
i int,
c varchar(100),
x varchar(100)
);
GO

INSERT INTO dbo.xyz
(
i,
c,
x
)
VALUES
(
1,
'a',
'b'
);
GO

IF OBJECT_ID('dbo.xyz') IS NOT NULL
DROP TABLE dbo.xyz;
GO

CREATE TABLE dbo.xyz
(
i int,
c varchar(100)
);
GO

DECLARE @sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
i int,
c varchar(100),
x varchar(100)
);

INSERT INTO dbo.xyz
(
i,
c,
x
)
VALUES
(
1,
''hello'',
''good bye''
);';

EXEC (@sql);
GO

DROP TABLE dbo.xyz;
GO

The trick is to remember that if the table already exists, dynamic SQL is looking at your table structure at parse time not at run time. So your target table structure has to fit the dynamic SQL statements that are being parsed.

Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

Search results matching tags 'T-SQL', 'sql 2005', and 'programming'

Dynamic SQL and Late Binding

Please Vote: Windowing Enhancements in SQL Server