SQL Tricks Insights from Microsoft IT (Ward Pond)
General Trick 1 GO n :
This script will demonstrate that the contents of a batch with SQL Server Management Studio can be executed a given number of times by appending an integer to the GO directive.
This technique supported in SQL Server 2005 and later.
-- GO_n.sql
-- Shows impact of "GO n" syntax.
USE tempdb
GO
CREATE TABLE TechEdTest (
TestId INT IDENTITY (1,1),
TestData NVARCHAR(400)
)
GO
INSERT TechEdTest (TestData) VALUES (N'Here is some test data.')
GO 10 -- note the "10" here..
SELECT TestId,
TestData
FROM TechEdTest
GO
DROP TABLE TechEdTest
GO
General Trick 2 Order of Operations
This script will demonstrate that, in certain scenarios, SQL Server CONVERT functions will execute before the WHERE clause. This scenario can have undesirable side effects when data is of mixed base type in a column (e.g., character and numeric data housed in the same column).
This technique supported in SQL Server 2000 and later.
-- Order_of_Operations.sql
-- demo of order of operations and workaround
SET NOCOUNT ON
USE tempdb GO
CREATE TABLE OOO_Demo (
OOO_Demo_Id INT IDENTITY(1,1) PRIMARY KEY,
HeaderType NVARCHAR(100) DEFAULT N'Header',
MetricName NVARCHAR(100),
MetricValue NVARCHAR(100)
)
GO
INSERT OOO_Demo (MetricName, MetricValue) VALUES (N'CharMetric', N'CharValue')
GO 2500
INSERT OOO_Demo (MetricName, HeaderType, MetricValue) VALUES (N'FloatMetric', N'Footer', N'123.4567')
GO 5
INSERT OOO_Demo (MetricName, MetricValue) VALUES (N'CharMetric', N'CharValue')
GO 500
INSERT OOO_Demo (MetricName, HeaderType, MetricValue) VALUES (N'FloatMetric', N'Footer', N'123.4567')
GO
INSERT OOO_Demo (MetricName, MetricValue) VALUES (N'CharMetric', N'CharValue')
GO 2500
CREATE INDEX q1 ON OOO_Demo (MetricName, HeaderType)
GO
-- Here's a SELECT that SOMETIMES won't work, even with the WHERE clause,
-- because we can't guarantee whether the CONVERT will be evaluated after the WHERE clause.
-- (comment out the WHERE clause on lines 31 and 32 to be certain to see the error)
-- Msg 8114, Level 16, State 5, Line 18
-- Error converting data type nvarchar to float.
SELECT OOO_Demo_Id,
MetricName,
CONVERT(float, MetricValue) AS ConvertedValue
FROM OOO_Demo
WHERE MetricName=N'FloatMetric'
AND HeaderType=N'Footer'
ORDER BY MetricName, ConvertedValue DESC
-- Here's a SELECT that will ALWAYS WORK,
-- because we can't guarantee when the CONVERT will fire
-- (although this one will also fail if you comment out the WHERE clause)
SELECT OOO_Demo_Id,
MetricName,
CASE
WHEN MetricName = N'CharMetric' THEN N'CharMetricFound'
WHEN MetricName = N'FloatMetric' THEN CONVERT(float, MetricValue)
END AS ConvertedValue
FROM OOO_Demo outside
WHERE MetricName=N'FloatMetric'
AND HeaderType=N'Footer'
ORDER BY MetricName, ConvertedValue DESC
GO
DROP TABLE OOO_Demo
GO
General Trick 3 Limitations of COLUMNS_UPDATED() and Workaround
COLUMNS_UPDATED() is a trigger function which “returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated.” (SQL 2005 BOL) This demonstration will show that COLUMNS_UPDATED() reacts if a column is named in an UPDATE statement, regardless of whether the value has changed. This limitation can have profound implications if one’s intent is to avoid running expensive code against an updated column. A workaround is then demonstrated.
This technique supported in SQL Server 2000 and later.
-- COLUMNS_UPDATED_limitations_demo.sql
-- demonstrate the COLUMNS_UPDATED() doesn't check to see if data has changed;
-- show workaround
USE tempdb
GO
CREATE TABLE ColumnsUpdated_Test (
ColumnsUpdated_TestId INT IDENTITY(1,1), --Column_ID 1
TestValue NVARCHAR(100), -- Column_ID 2
LastModifiedDate DATETIME DEFAULT (GETDATE()) -- Column_ID 3
)
GO
CREATE TABLE AuditTable (
AuditTableId INT IDENTITY (1,1),
ColumnsUpdated_TestId INT,
TestValueBeforeUpdate NVARCHAR(100),
TestValueAfterUpdate NVARCHAR(100),
TriggerType NVARCHAR(40),
EventTime DATETIME
)
GO
-- now build a trigger on ColumnsUpdated_Test that will
-- populate AuditTable based on COLUMNS_UPDATED()
CREATE TRIGGER AU_ColumnsUpdated_Test1
ON ColumnsUpdated_Test
AFTER UPDATE AS
IF (COLUMNS_UPDATED() & 2) > 0 -- if TestValue column has been updated
BEGIN
INSERT AuditTable (ColumnsUpdated_TestId,
TestValueBeforeUpdate,
TestValueAfterUpdate,
TriggerType,
EventTime
)
SELECT
i.ColumnsUpdated_TestId,
d.TestValue,
i.TestValue,
N'COLUMNS_UPDATED()',
i.LastModifiedDate
FROM inserted i
JOIN deleted d
ON i.ColumnsUpdated_TestId = d.ColumnsUpdated_TestId
END
GO
DECLARE @ColumnsUpdated_TestId INT
-- seed the table with a value..
INSERT ColumnsUpdated_Test (TestValue)
VALUES (N'This string has not changed but COLUMNS_UPDATE() doesn''t notice.')
SELECT @ColumnsUpdated_TestId = SCOPE_IDENTITY()
SELECT * FROM AuditTable
UPDATE ColumnsUpdated_Test
SET TestValue = N'This string has not changed but COLUMNS_UPDATE() doesn''t notice.'
WHERE ColumnsUpdated_TestId = @ColumnsUpdated_TestId
SELECT * FROM AuditTable
GO
-- now build a second trigger on ColumnsUpdated_Test that will
-- populate AuditTable based on a comparison of the values
-- in inserted and deleted
CREATE TRIGGER AU_ColumnsUpdated_Test2
ON ColumnsUpdated_Test
AFTER UPDATE AS
IF EXISTS ( SELECT 1
FROM inserted i
JOIN deleted d
ON i.ColumnsUpdated_TestId = d.ColumnsUpdated_TestId
AND i.TestValue <> d.TestValue
)
BEGIN
INSERT AuditTable (
ColumnsUpdated_TestId,
TestValueBeforeUpdate,
TestValueAfterUpdate,
TriggerType,
EventTime
)
SELECT
i.ColumnsUpdated_TestId,
d.TestValue,
i.TestValue,
N'TABLE TEST',
i.LastModifiedDate
FROM inserted i
JOIN deleted d
ON i.ColumnsUpdated_TestId = d.ColumnsUpdated_TestId
AND i.TestValue <> d.TestValue
END
GO
DECLARE @ColumnsUpdated_TestId INT
-- seed the table with another value..
-- notice that only one trigger picks up the change for this record
INSERT ColumnsUpdated_Test (TestValue)
VALUES (N'This string has not changed but COLUMNS_UPDATE() doesn''t notice.')
SELECT @ColumnsUpdated_TestId = SCOPE_IDENTITY()
SELECT * FROM AuditTable
UPDATE ColumnsUpdated_Test
SET TestValue = N'This string has not changed but COLUMNS_UPDATE() doesn''t notice.'
WHERE ColumnsUpdated_TestId = @ColumnsUpdated_TestId
SELECT * FROM AuditTable
-- seed the table with a final value..
-- notice that both triggers pick up the change for this record (the only real change in the batch)
INSERT ColumnsUpdated_Test (TestValue)
VALUES (N'This string has not changed but COLUMNS_UPDATE() doesn''t notice.')
SELECT @ColumnsUpdated_TestId = SCOPE_IDENTITY()
SELECT * FROM AuditTable
UPDATE ColumnsUpdated_Test
SET TestValue = N'This string has changed; both triggers should load a record into AuditTable.'
WHERE ColumnsUpdated_TestId = @ColumnsUpdated_TestId
SELECT * FROM AuditTable
GO
DROP TABLE ColumnsUpdated_Test
DROP TABLE AuditTable