SQL Server Tips

The blog has moved to www.sqlblogcasts.com

2007-09-06T08:15:00.000-07:00

I will continue posting over there at www.sqlblogcasts.com.
Simon Sabon has moved the blog over to www.sqlblogcasts.com.
Thank you Simon!

Selecting a random number for each row

2007-08-24T09:57:00.000-07:00

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f
end

select --your columns--, dbo.wrapped_rand() wrapped_rand
from YourTable

How to select a second page faster

2007-08-22T10:59:00.000-07:00

I used to think that using TOP clause to select a second page is faster than using ROW_NUMBER(). Apparently this is no longer true (although the plans are somewhat different):

-- this populates IDs with 3M rows, and the CAST strips IDENTITY property
SELECT CAST(my_ID*1 AS BIGINT) as ID INTO IDs FROM MyTable
GO
CREATE UNIQUE CLUSTERED INDEX PK_IDs ON IDs(ID)
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT TOP 20 ID FROM(
SELECT TOP 40 ID FROM IDs ORDER BY ID) t
ORDER BY ID DESC
GO
/*
Table 'IDs'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

A simple execution plan. Correct cardinality estimate:
Estimated Number of Rows: 40
*/
SELECT ID FROM(
SELECT ID, ROW_NUMBER() OVER(ORDER BY ID) rn FROM IDs) t
WHERE rn BETWEEN 21 AND 40
GO
/*
Same real execution costs:
Table 'IDs'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

A different, more complex plan.
I was surprized to see cost of one step exceed 100%: Segment Cost: 1771%
Also this estimate was somewhat askew: Estimated Number of Rows: 100
*/

next article on simple-talk.com

2007-08-21T10:25:00.000-07:00

"Close those Loopholes - Testing Stored Procedures"

Testing stored procedures which use dynamic SQL

2007-08-19T12:29:00.000-07:00

Consider the following stored procedure which uses dynamic SQL:

CREATE PROCEDURE Readers.SelectEmployeesByName

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6)

IF @Status IS NOT NULL AND @Status NOT IN('Active', 'Quit') BEGIN

RAISERROR('Readers.SelectEmployeesByName: Invalid Status %s', 16, 1, @Status)

RETURN -1

END

IF @FirstName IS NULL AND @LastName IS NULL AND @Status IS NULL BEGIN

RAISERROR('Readers.SelectEmployeesByName: MustProvide At Least One NOT NULL Parameter', 16, 1)

RETURN -1

END

DECLARE @SQL NVARCHAR(3000), @SQLSeparator VARCHAR(5), @params NVARCHAR(1000), @comma VARCHAR(1)

SELECT @SQLSeparator = 'WHERE',

@params = '@FirstName VARCHAR(20), @LastName VARCHAR(20), @Status VARCHAR(6)',

-- @comma = ''

@SQL = 'SELECT FirstName, LastName, Status, HireDate FROM data.Employees '

IF @FirstName IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' FirstName = @FirstName '

--SET @params = @params + @comma + '@FirstName VARCHAR(20)' + @comma

SET @SQLSeparator = 'AND'

--SET @comma = ','

END

IF @LastName IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' LastName = @LastName '

--SET @params = @params + @comma + '@LastName VARCHAR(20)' + @comma

SET @SQLSeparator = 'AND'

--SET @comma = ','

END

IF @Status IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' Status = @Status'

END

-- PRINT for debugging purposes only

PRINT @SQL

EXEC sp_executeSQL @SQL, @params, @FirstName, @LastName, @Status

It is very easy to make sure that the procedure always generates valid SQL which compiles:

DECLARE params CURSOR LOCAL STATIC

FOR SELECT FirstName, LastName, Status

FROM (SELECT 'Jeff' FirstName UNION ALL SELECT NULL) t1 CROSS JOIN

(SELECT 'Jones' LastName UNION ALL SELECT NULL) t2 CROSS JOIN

(SELECT 'Active' Status UNION ALL SELECT 'Retired' UNION ALL SELECT NULL) t3

DECLARE @FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6),

@keepFetching INT

SET @keepFetching = 1

OPEN params

WHILE @keepFetching = 1 BEGIN

FETCH params INTO @FirstName, @LastName, @Status

IF @@FETCH_STATUS <> 0 BEGIN

SET @keepFetching = 0

END ELSE BEGIN

EXEC Readers.SelectEmployeesByName @FirstName, @LastName, @Status

END

CLOSE params

DEALLOCATE params

Even though the procedure takes only three parameters, there are twelve different combinations that need to be considered. Five combinations are invalid and are rejected without running dynamic SQL. Seven combinations out of twelve are valid and as such need to be tested. Running seven unit tests is quite reasonable. Suppose, however, that more parameters are added to the procedure:

CREATE PROCEDURE Readers.SelectEmployees

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6),

@HireDateFrom DATETIME,

@HireDateTo DATETIME

Now you need to test at least forty eight combinations. It is still easy to make sure that dynamic SQL always compiles - you just need to add two more cross joins to your cursor. However, adding more and more unit tests to test results for all possible combinations becomes less and less feasible. Usually you want to test only the combinations that you actually need, and you do not want to expose untested combinations. For example, you could create the following two procedures:

CREATE PROCEDURE Readers.SelectEmployeesByNameAndStatus

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6)

-- twelve combinations exposed by this procedure

DECLARE @ret INT

EXEC @ret = Readers.SelectEmployees @FirstName, @LastName, @Status, NULL, NULL

RETURN @ret

CREATE PROCEDURE Readers.SelectEmployeesByHireDate

@HireDateFrom DATETIME,

@HireDateTo DATETIME

-- four combinations exposed by this procedure

DECLARE @ret INT

EXEC @ret = Readers.SelectEmployees NULL, NULL, NULL, @HireDateFrom, @HireDateTo

RETURN @ret

That done, you could revoke permissions to execute the original procedure, Readers.SelectEmployees - that would reduce the number of combinations which you need to test fully to only sixteen. Clearly there are other ways to deal with this problem, but in any case avoid exposing untested code.

My first article on simple-talk.com

2007-08-14T10:17:00.000-07:00

"Close These Loopholes in Your Database Testing"
[http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes-in-your-database-testing/]

Making Sure All The Objects Are Qualified

2007-08-01T10:20:00.001-07:00

is quite simple: I do not have any objects in dbo schema at all. Tables are in created data. or staging. schemas.
Procedures are created in Writers and Readers schemas.

All Permutations For A String

2007-07-23T14:13:00.000-07:00

Suppose you have a auxiliary Numbers table with integer numbers.

DECLARE @s VARCHAR(5);
SET @s = 'ABCDE';

WITH Subsets AS (
SELECT CAST(SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST('.'+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM dbo.Numbers WHERE Number BETWEEN 1 AND 5
UNION ALL
SELECT CAST(Token+SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST(Permutation+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM Subsets s JOIN dbo.Numbers n ON s.Permutation NOT LIKE
'%.'+CAST(Number AS CHAR(1))+'.%' AND s.Iteration < 5 AND Number
BETWEEN 1 AND 5
--AND s.Iteration = (SELECT MAX(Iteration) FROM Subsets)
)
SELECT * FROM Subsets
WHERE Iteration = 5
ORDER BY Permutation

Token Permutation Iteration
----- ----------- -----------
ABCDE .1.2.3.4.5. 5
ABCED .1.2.3.5.4. 5
ABDCE .1.2.4.3.5. 5
(snip)
EDBCA .5.4.2.3.1. 5
EDCAB .5.4.3.1.2. 5
EDCBA .5.4.3.2.1. 5

(120 row(s) affected)

Which Roles Does The User Belong To?

2007-07-23T06:19:00.000-07:00

SELECT r.[name] Role_Name FROM sys.database_principals u
JOIN sys.database_role_members rm ON u.principal_id = rm.member_principal_id
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE u.[name] = USER_NAME()

I got a SQL Server MVP award.

2007-07-03T11:50:00.000-07:00

I got a SQL Server MVP award.

One More Article on Devx.com

2007-06-22T11:35:00.000-07:00

"Enforce Business Rules for All Your Data with Constraints"
http://www.devx.com/dbzone/Article/34479

This article is a reply to Paul Nielsen's post on sqlblog.com

http://sqlblog.com/blogs/paul_nielsen/archive/2006/12/18/430.aspx#comments

Materialized Path: Making Sure a Tree has No Cycles

2007-06-21T15:53:00.000-07:00

The following DDL guarantees no cycles for Materialized Path:

CREATE TABLE dbo.OrgChart(GroupName VARCHAR(20) NOT NULL,
ParentPath VARCHAR(900) NULL,
FullPath VARCHAR(900) NOT NULL,
CONSTRAINT PK_OrgChart PRIMARY KEY(GroupName),
CONSTRAINT OrgChart_NoBackslashInGroupName CHECK(GroupName =
REPLACE(GroupName, '\', '')),
CONSTRAINT OrgChart_CorrectFullPath CHECK(FullPath = CASE WHEN
ParentPath IS NULL THEN '' ELSE ParentPath + '\' END
+ GroupName),
CONSTRAINT OrgChart_ParentPath_FK FOREIGN KEY(ParentPath) REFERENCES
OrgChart(FullPath),
CONSTRAINT OrgChart_NoCycles CHECK (ParentPath NOT LIKE GroupName +
'\%' AND ParentPath NOT LIKE '%\'+ GroupName + '\%')
)
go
SET NOCOUNT ON
-- adding some valid data
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath) VALUES('US', NULL,
'US')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Chicagoland', 'US', 'US\Chicagoland')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath) VALUES('Loop
Office', 'US\Chicagoland', 'US\Chicagoland\Loop Office')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Naperville Lab', 'US\Chicagoland', 'US\Chicagoland\Naperville
Lab')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Lisle Datacenter', 'US\Chicagoland', 'US\Chicagoland\Lisle
Datacenter')

Getting All Subsets of a Set

2007-06-20T14:30:00.000-07:00

For example, let us generate all subsets of a set with 10 different elements.

-- you will need two auxiliary tables
SELECT * INTO dbo.Numbers FROM AnotherDB.dbo.Numbers WHERE Number BETWEEN 1 AND 1024
GO
CREATE UNIQUE CLUSTERED INDEX abcd ON Numbers(Number)
GO
-- another auxiliary table
CREATE TABLE NumbersIn2Power(Number INT NOT NULL, Power2 INT NOT NULL)
INSERT NumbersIn2Power VALUES(1,1)
INSERT NumbersIn2Power VALUES(2,2)
INSERT NumbersIn2Power VALUES(3,4)
INSERT NumbersIn2Power VALUES(4,8)
INSERT NumbersIn2Power VALUES(5,16)
INSERT NumbersIn2Power VALUES(6,32)
INSERT NumbersIn2Power VALUES(7,64)
INSERT NumbersIn2Power VALUES(8,128)
INSERT NumbersIn2Power VALUES(9,256)
INSERT NumbersIn2Power VALUES(10,512)
GO
CREATE UNIQUE CLUSTERED INDEX abcdef ON NumbersIn2Power(Number)
GO

-- the set itself has 10 elements

CREATE TABLE #t(itemNum INT, val INT)
INSERT #t VALUES(1,11)
INSERT #t VALUES(2,12)
INSERT #t VALUES(3,13)
INSERT #t VALUES(4,14)
INSERT #t VALUES(5,15)
INSERT #t VALUES(6,16)
INSERT #t VALUES(7,17)
INSERT #t VALUES(8,18)
INSERT #t VALUES(9,19)
INSERT #t VALUES(10,20)
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

-- approach 1, using a cross join
SELECT n.number AS SetNumber, #t.val FROM NumbersIn2Power n2
JOIN dbo.Numbers n ON n2.Power2 & n.number > 0
JOIN #t ON n2.number = #t.itemNum
ORDER BY n.number, #t.itemNum

/*
Table 'Numbers'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NumbersIn2Power'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________000000000031'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 71 ms.

*/

-- approach two, using recursion

WITH Subsets AS (
SELECT itemNum, CAST(',' + CAST(itemNum AS VARCHAR(10)) + ',' AS
CHAR(50)) AS LinesList, CAST(1 AS INT) AS Iteration, val
FROM #t
UNION ALL
SELECT t.itemNum, CAST(CAST(RTRIM(s.LinesList) AS VARCHAR(200)) +
CAST(t.itemNum AS VARCHAR(10)) + ',' AS CHAR(50)) AS LinesList,
s.Iteration + 1 AS Iteration, s.val + t.val AS val
FROM Subsets s JOIN #t t ON s.itemNum >= t.itemNum
WHERE s.Iteration <> t.itemNum
)
SELECT * FROM Subsets ORDER BY LinesList

/*
Table 'Worktable'. Scan count 2, logical reads 6121, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________000000000031'. Scan count 2, logical reads 1023, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 87 ms.
*/

As you have seen, elapsed time is similar, but the first approach uses less CPU. Note that I ran each script more than once, so that parse/compile time is not included in the costs.

Calculating Pi Using Only Pure SQL

2007-06-19T15:28:00.000-07:00

Suppose table Numbers has numbers from 1 to 1000.
The problem: using pure SQL find the closest approximation to Pi number as a ratio of two integers. The integers should not exceed 999.
The solution is:

SELECT TOP 100 m, n, c FROM(

SELECT n1.Number m, n2.Number n, ABS(SIN(1.0*n1.Number/n2.Number)) c

FROM Numbers n1, Numbers n2

WHERE n1.Number BETWEEN 100 AND 999

AND n2.Number BETWEEN 100 AND 999) t

ORDER BY c

The answer is 355/113.

Maximum selectivity for bookmark lookups depends on row size.

2006-12-09T14:30:00.000-08:00

Consider a range query on a column that has a non clusterd index. For narrow ranges the optimizer chooses to access the table via bookmark lookups. For wider ranges it just scans the clustered index, as it is cheaper than bookmark lookups. There is no hardcoded threshold - it depeneds on row size and bookmark size. A simple example - in all three cases the optimizer chooses bookmark lookups, but if you increase the range just a little bit, it scans the whole clustered index.

-- Assuming Numbers table with 10000 rows

CREATE TABLE WideRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(1000))
go

INSERT WideRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf' FROM
dbo.Numbers
go

CREATE INDEX WideRows_j ON WideRows(j)

---- break even point close to 4%
SELECT i, j, c FROM WideRows WHERE j BETWEEN 1000 AND 1400
go

DROP TABLE WideRows
go

CREATE TABLE HugeRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(3500))
go

INSERT HugeRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf' FROM
dbo.Numbers
go

CREATE INDEX HugeRows_j ON HugeRows(j)

---- break even point almost 20%
SELECT i, j, c FROM HugeRows WHERE j BETWEEN 1000 AND 2900
go

DROP TABLE HugeRows
go

CREATE TABLE NarrowRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(10))
go

INSERT NarrowRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf'
FROM dbo.Numbers
go

CREATE INDEX NarrowRows_j ON NarrowRows(j)

---- break even point less than 1%
SELECT i, j, c FROM NarrowRows WHERE j BETWEEN 1000 AND 1040
go

DROP TABLE NarrowRows
go

The price of BEGIN TRANSACTION

2006-12-09T14:23:00.000-08:00

A question came up in a newsgroup:
Is there any performance penalty for explicitly wrapping a single DML statement in BEGIN TRANSACTION / COMMIT brackets?

I did a quick benchmark:

CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
IF @@ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go

I ran the tests 3 times and I did not notice any significant differences in
neither of 4 counters. I would not worry about performance penalties of BEGIN TRAN in my environment.

Bypassing A Trigger

2006-12-04T18:00:00.000-08:00

Several ways to bypass a trigger can be found in an excellent book "Inside MQ SQL Server 2005: T_SQl Querying". Here is yet another one.

Suppose you have a trigger that records all the changes against a table, as follows:

CREATE TABLE Party(PartyID INT NOT NULL PRIMARY KEY, LastName VARCHAR(20),
FirstName VARCHAR(20), Salutation VARCHAR(20))
go

CREATE TABLE PartyHistory(PartyID INT NOT NULL, LastName VARCHAR(20), FirstName
VARCHAR(20), Salutation VARCHAR(20),
ModifiedBy VARCHAR(100), ModifiedAt DATETIME)
go

CREATE TRIGGER PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger PartyUpd'
INSERT INTO [dbo].[PartyHistory]([PartyID], [LastName], [FirstName],
[Salutation], [ModifiedBy], [ModifiedAt])
SELECT [PartyID], [LastName], [FirstName], [Salutation], SYSTEM_USER,
CURRENT_TIMESTAMP
FROM Deleted
go

INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(1, 'Doe', 'John', 'Junior')
INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(2, 'Doe', 'John', 'Junior')
go
UPDATE dbo.[Party] SET [LastName] = 'Cooper', [FirstName] = 'Stanley'
WHERE partyID = 1
go
SELECT * FROM PartyHistory
/*
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:42:12.480

(1 row(s) affected)
*/

Suppose you want to modify Salutation column, as follows

UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'

And you do not want the trigger to fire. Make sure that RECURSIVE_TRIGGERS is disabled (which is the default):

SELECT DATABASEPROPERTYEX('SANDBOX','IsRecursiveTriggersEnabled')
----
0

Retrieve your spid:

SELECT @@spid
/*
------
58

(1 row(s) affected)

*/

And wrap your update in a trigger:

CREATE TRIGGER Bypass_PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger Bypass_PartyUpd'
PRINT @@SPID
IF @@spid = 58
UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'
go

Now all you need to do is run an update which does not modify any rows

UPDATE Party Set LastName ='asfdsdf' WHERE PartyID<0
go

The new trigger will fire and update Salutation. Because RESURSIVE_TRIGGERS is turned off, the old trigger will not fire:

SELECT * FROM Party
/*
PartyID LastName FirstName Salutation
----------- -------------------- -------------------- --------------------
1 Cooper Stanley Jr.
2 Doe John Jr.
*/
go
SELECT * FROM PartyHistory
/*
--- still only one row in PartyHistory
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:51:49.257
*/
go

If you try an update which clearly will not update any rows no matter what:

UPDATE Party Set LastName ='asfdsdf' WHERE (1=0)

The optimizer will be smart enough to not execute the update, so the trigger will not run at all.

The advantage of this approach is that you do not need to touch the original trigger at all. The disadvantage is that you must not forget to drop the new trigger.

Running Concurrency Tests

2006-11-03T14:56:00.000-08:00

Starting concurrency tests simultaneously could be tricky, especially they need to start at the same time from different workstations.

One way to do it:

CREATE TABLE WhistleSound(i INT)
go
CREATE PROCEDURE WaitForWhistle
AS
DECLARE @cnt INT
SET @cnt = 0
WHILE (@cnt = 0) BEGIN
SELECT @cnt = COUNT(*) FROM WhistleSound WITH(NOLOCK)
IF @cnt = 0
WAITFOR DELAY '00:00:01'
END
go
CREATE PROCEDURE BlowWhistle
AS
INSERT WhistleSound(i) VALUES(1)
go

From one connection run

DELETE FROM WhistleSound
SELECT Getdate()
EXEC WaitForWhistle
SELECT Getdate()

From another connection run

SELECT Getdate()
EXEC WaitForWhistle
SELECT Getdate()

From third connection run

EXEC dbo.BlowWhistle

When Snapshot Isolation Helps and When It Hurts

2006-11-03T07:06:00.000-08:00

My next article on devx.com:

You know SQL Server 2005's new snapshot isolation feature can help you reduce lock contention and deadlocks, but did you know that it also can help you fix intermittent errors in reports and detect lost updates? You know that snapshot isolation requires some effort from your DBA, but did you know it may introduce some breaking changes into applications? Its benefits are well known, but SQL Server developers need to understand the downstream ramifications of snapshot isolation. This article discusses a couple of common real-world scenarios that illustrate the often-unanticipated consequences of using this feature.

http://www.devx.com/dbzone/Article/32957

Child Intervals Of Time Inside Parent Ones - Enforced By RI

2006-10-27T18:55:00.000-07:00

I needed to enforce a business rule that appointments fit into shifts.
The only one watertight way of doing it that i know about is to use RI, as follows:

CREATE Table Shifts(ShiftID INT NOT NULL PRIMARY KEY,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
CONSTRAINT UK_Shifts UNIQUE(ShiftID, StartedAt, FinishedAt))
go
SET NOCOUNT ON
INSERT Shifts VALUES(1, '20061025 08:00AM', '20061025 05:00PM')
INSERT Shifts VALUES(2, '20061026 08:30AM', '20061026 05:00PM')
INSERT Shifts VALUES(3, '20061027 08:30AM', '20061027 05:00PM')
go
CREATE Table Appointments ( ShiftID INT NOT NULL,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
ShiftStartedAt DATETIME NOT NULL,
ShiftFinishedAt DATETIME NOT NULL,
CONSTRAINT FK_Appointments FOREIGN KEY(ShiftID, ShiftStartedAt, ShiftFinishedAt)
REFERENCES Shifts(ShiftID, StartedAt, FinishedAt) ON UPDATE CASCADE,
CONSTRAINT CHK_TimesInRange CHECK((StartedAt BETWEEN ShiftStartedAt AND
ShiftFinishedAt) AND (FinishedAt BETWEEN ShiftStartedAt AND ShiftFinishedAt)))
go
INSERT Appointments VALUES(1, '20061025 09:00AM', '20061025 05:00PM', '20061025
08:00AM', '20061025 05:00PM')
INSERT Appointments VALUES(2, '20061026 08:30AM', '20061026 01:00PM', '20061026
08:30AM', '20061026 05:00PM')
INSERT Appointments VALUES(3, '20061027 10:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
---- these updates succeed: appointments still fit into their shifts
UPDATE Shifts SET StartedAt = '20061027 09:31AM' WHERE ShiftID = 3
UPDATE Shifts SET FinishedAt = '20061027 04:31PM' WHERE ShiftID = 2
go
-- all fail: range not completely inside the parent range
INSERT Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
INSERT Appointments VALUES(3, '20061027 10:30AM', '20061027 08:00PM', '20061027
08:30AM', '20061027 05:00PM')
-- fails because there is an appoinment starting at 10:30
UPDATE Shifts SET StartedAt = '20061027 10:31AM' WHERE ShiftID = 3

Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.

2006-10-23T16:09:00.000-07:00

My next article went live on devx.com:

Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.

The same ANSI Standard SQL query can run against identical data on SQL Server and Oracle and return different results. So just because your application issues ANSI standard SQL, don't assume it's portable.

http://www.devx.com/dbzone/Article/32852

Implementing One-To-One Relationship.

2006-10-16T17:37:00.000-07:00

Suppose you have two tables, as follows:

CREATE TABLE LeftHalf(PairID INT NOT NULL PRIMARY KEY,
SomeData VARCHAR(20))
go
CREATE TABLE RightHalf(PairID INT NOT NULL PRIMARY KEY,
SomeData VARCHAR(20))
go
INSERT LeftHalf(PairID, SomeData) VALUES(1, 'Left Half 1')
INSERT RightHalf(PairID, SomeData) VALUES(1, 'Right Half 1')
go

Assume you want to enforse one-to-one relationship between them:

ALTER TABLE LeftHalf ADD CONSTRAINT LeftHalfHasMatchingRight FOREIGN KEY(PairID) REFERENCES RightHalf
go
ALTER TABLE RightHalf ADD CONSTRAINT RightHalfHasMatchingLeft FOREIGN KEY(PairID) REFERENCES LeftHalf
go

Unfortunately, this straightforward approach does not allow you to add new rows:

INSERT LeftHalf(PairID, SomeData) VALUES(2, 'Some Data')
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.
*/

Let us implement deferrable constraints instead:

ALTER TABLE LeftHalf DROP CONSTRAINT LeftHalfHasMatchingRight
go
ALTER TABLE RightHalf DROP CONSTRAINT RightHalfHasMatchingLeft
go

ALTER TABLE LeftHalf ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE RightHalf ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE LeftHalf ADD MatchingRight AS CASE WHEN InTransientState = 'N' THEN PairID END PERSISTED
go
ALTER TABLE RightHalf ADD MatchingLeft AS CASE WHEN InTransientState = 'N' THEN PairID END PERSISTED
go
ALTER TABLE LeftHalf ADD CONSTRAINT LeftHalfHasMatchingRight FOREIGN KEY(MatchingRight) REFERENCES RightHalf
go
ALTER TABLE RightHalf ADD CONSTRAINT RightHalfHasMatchingLeft FOREIGN KEY(MatchingLeft) REFERENCES LeftHalf
go
UPDATE LeftHalf SET InTransientState = 'N'
UPDATE RightHalf SET InTransientState = 'N'
go

Now you can add rows all right as long as you defer RI checking until both rows in a pair are added, as follows:

BEGIN TRAN
INSERT LeftHalf(PairID, SomeData, InTransientState) VALUES(2, 'Left Half 2', 'Y')
INSERT RightHalf(PairID, SomeData, InTransientState) VALUES(2, 'Right Half 2', 'Y')
UPDATE LeftHalf SET InTransientState = 'N' WHERE PairID = 2
UPDATE RightHalf SET InTransientState = 'N' WHERE PairID = 2
COMMIT
go

Note that straightforward insert will not work, as before:

INSERT LeftHalf(PairID, SomeData, InTransientState) VALUES(3, 'Left Half 3', 'N')
INSERT RightHalf(PairID, SomeData, InTransientState) VALUES(3, 'Right Half 3', 'N')

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "RightHalfHasMatchingLeft". The conflict occurred in database "Sandbox", table "dbo.LeftHalf", column 'PairID'.
The statement has been terminated.

But the data not in transient state is guaranteed to be consistent:

SELECT * FROM LeftHalf WHERE InTransientState = 'N'
SELECT * FROM RightHalf WHERE InTransientState = 'N'
/*
PairID SomeData InTransientState MatchingRight
----------- -------------------- ---------------- -------------
1 Left Half 1 N 1
2 Left Half 2 N 2

(2 row(s) affected)

PairID SomeData InTransientState MatchingLeft
----------- -------------------- ---------------- ------------
1 Right Half 1 N 1
2 Right Half 2 N 2

Mimicking Deferrable Constraints With Persisted Computed Columns.

2006-10-16T17:34:00.000-07:00

In Oracle you can utilize deferrable constraints (when RI checks are delayed until commit time) - this is very convenient in some cases.
In SQL Server deferrable constraints are not supported, but you can mimick this behaviour using persisted computed columns.
For instance, suppose two columns in your child table reference one and the same primary key as follows:

CREATE TABLE Employee(EmployeeName VARCHAR(30) NOT NULL PRIMARY KEY, Extension CHAR(4))
go
CREATE TABLE Task(CompletedBy VARCHAR(30) NOT NULL, ReviewedBy VARCHAR(30) NULL, Description VARCHAR(30))
go
ALTER TABLE Task ADD CONSTRAINT TaskCompletedFK FOREIGN KEY(CompletedBy) REFERENCES Employee(EmployeeName)
go
ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedBy) REFERENCES Employee(EmployeeName)
go

Also suppose that your primary key is not immutable and you need to cascade the changes in the parent's primary key down to the child table

ALTER TABLE Task DROP CONSTRAINT TaskCompletedFK
go
ALTER TABLE Task DROP CONSTRAINT TaskReviewedByFK
go

You can create one foreign key with cascading updates all right:

ALTER TABLE Task ADD CONSTRAINT TaskCompletedFK FOREIGN KEY(CompletedBy) REFERENCES Employee(EmployeeID)
ON UPDATE CASCADE
go

But the second constraint cannot be not created with ON UPDATE CASCADE ON DELETE CASCADE option:

ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedBy) REFERENCES Employee(EmployeeID)
ON UPDATE CASCADE
go

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'TaskReviewedByFK' on table 'Task' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

You have no choice but implement cascading updates manually.
Note that dealing with mutating primary keys in triggers is very inconvenient to put it mildly. However, you can add a couple of columns to your table and easily cascade the changes without troubling yourself too terribly much. Add two columns and a constraint as follows:

ALTER TABLE Task ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE Task ADD ReviewedByNotTransient AS CASE WHEN InTransientState = 'N' THEN ReviewedBy END PERSISTED
go
ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedByNotTransient) REFERENCES Employee(EmployeeName)
go

Let us populate the tables

INSERT Employee VALUES('Doe, John', '1234')
INSERT Employee VALUES('Doe, Jane', '1235')
INSERT Employee VALUES('Jill, Ruben', '1236')
go
INSERT Task(CompletedBy, ReviewedBy, InTransientState) VALUES('Doe, John', 'Doe, Jane', 'N')
INSERT Task(CompletedBy, ReviewedBy, InTransientState) VALUES('Doe, Jane', 'Jill, Ruben', 'N')
go

Note that a straightforward UPDATE will not work, because it is not cascaded down to ReviewedBy column:

UPDATE Employee SET EmployeeName = 'Doe, Janette' WHERE EmployeeName = 'Doe, Jane'
/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "TaskReviewedByFK". The conflict occurred in database "Sandbox", table "dbo.Task", column 'ReviewedByNotTransient'.
The statement has been terminated.
*/

But a slightly more complex script will work all right (error handling skipped for brevity):

BEGIN TRAN
--- Turn off TaskReviewedByFK temporarily for Doe, Jane only
UPDATE Task SET InTransientState = 'Y' WHERE ReviewedBy = 'Doe, Jane'
--- update the PK
UPDATE Employee SET EmployeeName = 'Doe, Janette' WHERE EmployeeName = 'Doe, Jane'
--- change the FK accordingly and get the rows back into RI scope
UPDATE Task SET ReviewedBy = 'Doe, Janette', InTransientState = 'N' WHERE ReviewedBy = 'Doe, Jane'
COMMIT
-- verify that it worked. Note that changes to CompletedBy cascaded automatically.
SELECT * FROM Task

As you have seen, a persisted computed column allowed you to postpone RI verification.

Loading Legacy Data Into parent/Child Tables With Identities

2006-09-25T19:42:00.000-07:00

It is one of those cases when OUTPUT clause comes very handy.

CREATE TABLE Staging(EmployeeCode CHAR(30), FullName VARCHAR(30),
PhoneNumber VARCHAR(30))
INSERT INTO Staging VALUES('ABC1234567890XYZ0987654321', 'Doe, John',
'(123)456-7890 home')
INSERT INTO Staging VALUES('ABC1234567890XYZ0987654321', 'Doe, John',
'(312)456-7890 cell')
INSERT INTO Staging VALUES('ABD123FHSDJKFH367FHASJ4321', 'Doe, Jane',
'(567)456-7891 home')
go

CREATE TABLE Party(PartyID INT IDENTITY PRIMARY KEY, EmployeeCode
CHAR(30), FullName VARCHAR(30))
CREATE TABLE Phone(PartyID INT NOT NULL, PhoneNumber VARCHAR(30))
go

DECLARE @InsertedIds TABLE(PartyID INT, EmployeeCode CHAR(30))

INSERT Party(EmployeeCode, FullName)
OUTPUT INSERTED.PartyID, INSERTED.EmployeeCode INTO @InsertedIds
SELECT DISTINCT EmployeeCode, FullName
FROM Staging

SELECT * FROM Party

PartyID EmployeeCode FullName
----------- ------------------------------
------------------------------
1 ABC1234567890XYZ0987654321 Doe, John
2 ABD123FHSDJKFH367FHASJ4321 Doe, Jane

SELECT PartyID, EmployeeCode FROM @InsertedIds

PartyID EmployeeCode
----------- ------------------------------
1 ABC1234567890XYZ0987654321
2 ABD123FHSDJKFH367FHASJ4321

INSERT Phone(PartyID, PhoneNumber)
SELECT
--(SELECT PartyID FROM @InsertedIds i WHERE Staging.EmployeeCode =
i.EmployeeCode)
PartyID,
PhoneNumber
FROM Staging JOIN @InsertedIds i ON Staging.EmployeeCode =
i.EmployeeCode

SELECT * FROM Phone

PartyID PhoneNumber
----------- ------------------------------
1 (123)456-7890 home
1 (312)456-7890 cell
2 (567)456-7891 home

When Eliminataing Cursors Hurts Performance

2006-09-19T19:17:00.000-07:00

Not every non-cursor so9lution is performant. I think there are at least two different situations:

1. You open a cursor and use it to modify the underlying table.
2. You open a cursor, do something, but you do not touch the underlying
at all.

I think that we need to distinguish between these two cases. I think
the popular rule of thumb that "cursors should be avoided whatever it
takes" not always applies in case 2.
Have a look at this simple script (Table Numbers stored numbers from 1
to 10000):

DROP PROCEDURE CursorTest
go
CREATE PROCEDURE CursorTest
AS
DECLARE @KeepLooping INT, @n INT, @d1 DATETIME, @d2 DATETIME
DECLARE Test1 CURSOR LOCAL FORWARD_ONLY
FOR SELECT Number
FROM dbo.Numbers
ORDER BY Number

SET @KeepLooping = 1

SET @d1 = GETDATE()

OPEN Test1

WHILE @KeepLooping = 1 BEGIN
FETCH Test1 INTO @n
IF @@FETCH_STATUS <> 0 BEGIN
SET @KeepLooping = 0
END
END

SET @d2 = GETDATE()
SELECT 'CursorTest' testname, DATEDIFF(ms, @d1, @d2) duration

CLOSE Test1
DEALLOCATE Test1
go
DROP PROCEDURE LoopTest
go
CREATE PROCEDURE LoopTest
AS
DECLARE @n INT, @d1 DATETIME, @d2 DATETIME

SET @n = -1
SET @d1 = GETDATE()

WHILE @n IS NOT NULL BEGIN
SELECT @n = MIN(Number)
FROM dbo.Numbers WHERE Number > @n
END
SET @d2 = GETDATE()
SELECT 'LoopTest' testname, DATEDIFF(ms, @d1, @d2) duration
go
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest

testname duration
-------- -----------
LoopTest 30000

(1 row(s) affected)

testname duration
---------- -----------
CursorTest 106

(1 row(s) affected)

testname duration
-------- -----------
LoopTest 32190

(1 row(s) affected)

testname duration
---------- -----------
CursorTest 106

(1 row(s) affected)

testname duration
-------- -----------
LoopTest 29736

(1 row(s) affected)

testname duration
---------- -----------
CursorTest 106

(1 row(s) affected)

How To Quickly Verify That A Date Has No Time Part

2006-09-19T18:55:00.000-07:00

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster (at least 7 times faster):

DECLARE @d1 DATETIME, @d2 DATETIME, @i INT, @cnt INT
DECLARE @d TABLE(ddd DATETIME)
SET NOCOUNT ON

SET @i = 0
WHILE @i<100000 BEGIN
INSERT @d VALUES('20060101')
SET @i = @i + 1
END
SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE (convert(char(8), ddd, 112) =
ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'char'

SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE
(dateadd(d,datediff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'datediff'

----------- ----
346 char
----------- --------
46 datediff

Yet Another Index Covering Tip

2006-09-18T18:51:00.000-07:00

Suppose you have a table:

create table a(email_alias varchar(20), full_name varchar(20), /*many other columns*/)

Suppose you need to make sure email_alias are unique. Obviously you will create a unique index on email_alias. Suppose you also need an index on (email_alias, full_name) to cover several frequently run queries. In SQL Server 2005 you can have one and the same index accomplish both goals. Use a new INCLUDE option in CREATE INDEX statement:

create unique index a1 on a(email_alias) include(full_name)

Both columns will be stored in the index, which will guarantee index covering, but the uniqueness of email_alias will be also preserved:

--- succeeds
insert into a values('jsmith', 'Jack Smith')
go
--- fails
insert into a values('jsmith', 'Jared Smith')

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.a' with unique index 'a1'.
The statement has been terminated.

It is very important to keep the number of indexes as low as possible, and the new INCLUDE option comes very handy in accomplishing that goal.

Mimicking MERGE Statement in SQL Server 2005

2006-09-18T18:50:00.000-07:00

Suppose you have a table

create table permanent(id int, d float, comment varchar(15))
go
insert into permanent values(1, 10., 'Original Row')
insert into permanent values(2, 10., 'Original Row')
insert into permanent values(3, 10., 'Original Row')
go

And you want to modify it with incoming values from a staging table

create table staging(id int, d float)
go
insert into staging values(2, 15.)
insert into staging values(3, 15.)
insert into staging values(4, 15.)
go

You want to modify rows 2 and 3 and to add a new row 4. In DB2 and oracle there is a very convenient MERGE statement which does precisely this. In SQL Server 2005 you can use OUTPUT clause of an UPDATE statement:

go
declare @updated_ids table(id int)
update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id

insert into permanent
select id, d, 'New Row' from staging where id not in(select id from @updated_ids)
go
select * from permanent
go
id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 15 Modified Row
3 15 Modified Row
4 15 New Row

Populating a Column with Random Values

2006-09-18T18:48:00.000-07:00

Suppose you have a table:

create table t(i int, d float)
insert into t values(1, 0.0)
insert into t values(2, 0.0)
insert into t values(3, 0.0)
insert into t values(4, 0.0)

Suppose you want to populate d with random values:

update t set d = rand()

Unfortunately it will assign one and the same value to all the rows:

select * from t

i d----------- ----------------------
1 0.430774201738828
2 0.430774201738828
3 0.430774201738828
4 0.430774201738828
(4 row(s) affected)

Here is the right way to do it:

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f
end
go
update t set d = dbo.wrapped_rand()

select * from t

i d
----------- ----------------------
1 0.665934939858962
2 0.99919527046644
3 0.844039650356367
4 0.146401486624627
(4 row(s) affected)

My DB2 UDB articles

2006-09-17T16:58:00.000-07:00

1. When We Think That the Optimizer Doesn't Get It Right
Some simple tips to make sure the optimizer makes good decisions for your queries.

http://www.devx.com/ibm/Article/11179/0

2. Essential Facts about Index Covering in DB2 Universal Database
xamples to illustrate cases in which adding a column or two to an index can boost a query's performance by "covering" the columns used in a SELECT statement. The DB2 optimizer is very good in recognizing such situations and can choose a very efficient execution plan.

http://www-128.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html

3. Using DB2 UDB OLAP functions
Online analytical processing (OLAP) functions are very flexible and powerful. Using them, you may come up with simple solutions to some problems that would otherwise require either iteration through one or several cursors or recursion. In some other cases it is much easier to write a query using OLAP functions or auxiliary tables than to write an equivalent query not using them.

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/index.html

4. Using Materialized QueryTables to Speed up Queries in DB2 UDB
Sometimes a simple change in physical database structure dramatically improves query performance. In addition to indexes, DB2 UDB provides you with materialized query tables, which in some cases are more efficient than indexes. This article provides ...

http://www-128.ibm.com/developerworks/db2/library/techarticle/0208kuznetsov/0208kuznetsov.html

My MS SQL Server Articles

2006-09-17T16:53:00.000-07:00

My MS SQL Server Articles

1. Indexes on Computed Columns: Speed Up Queries, Add Business Rules
Using an index on a computed column, you can implement complex business rules or just give your SQL Server queries a performance boost.

http://www.devx.com/dbzone/Article/30786/0

2. Avoid These Common Business Rule Implementation Mistakes
Some solutions for implementing business rules can allow invalid data to get into the database. Find out where the loopholes lie in these all-too-common approaches.

http://www.devx.com/dbzone/Article/31985/0

3.Index Covering Boosts SQL Server Query Performance
Index covering is a quick and easy solution to many query performance problems. By just adding a column or two to an index you can significantly boost a query's performance.

http://www.devx.com/dbzone/Article/29530/0

4. The Less SQL Server Sorts, the Faster It Responds (with Rajiv Rajak)
Learn coding techniques to tweak your SQL queries so that SQL Server sorts less data and returns much faster.

http://www.devx.com/dbzone/Article/30149/0