Fake a long running SQL statement

In one query window, execute the following:

BEGIN TRANSACTION

SELECT * from TableY with (XLOCK)

Then, in another window, execute any(*) query that attempts to access TableY. And it will run for as long as you like until you close the first window or execute a ROLLBACK or COMMIT in it.

(*) assuming you don't add a NOLOCK hint to the reference to TableY.

Just as I was writing "CTE"... it made me think. A quick search later and a variation on http://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/

--define start and end limits
Declare @start bigint, @end bigint
Select @start=1, @end=99999

;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
)

--select result
Select * From NumberSequence Option (MaxRecursion 0)

Here's a long-running SQL statement:

WAITFOR DELAY '0:05';

It will take around five minutes to execute.