The blog has moved to www.sqlblogcasts.com

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

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

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

"Close those Loopholes - Testing Stored Procedures"

Testing stored procedures which use dynamic SQL

Consider the following stored procedure which uses dynamic SQL:

CREATE PROCEDURE Readers.SelectEmployeesByName

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6)

AS

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

GO

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

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)

AS

-- twelve combinations exposed by this procedure

DECLARE @ret INT

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

RETURN @ret

GO

CREATE PROCEDURE Readers.SelectEmployeesByHireDate

@HireDateFrom DATETIME,

@HireDateTo DATETIME

AS

-- four combinations exposed by this procedure

DECLARE @ret INT

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

RETURN @ret

GO

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

"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

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.