How to create a slow view?

Question

I want to test the timeout-behavior of my MS-Access application (with linked tables). It would be super convenient to have a slow database query. If possible, even with a predefined query duration.

I tried this:

CREATE VIEW slow_view AS
WAITFOR DELAY '00:01:05'
SELECT 1 AS one

but WAITFOR is not allowed inside a tsql view definition.

How can I create a slow database view in SQL Server, that provokes a timeout?

Answer 1

One option would be to lock a table. In SSMS or a different tool, execute a query with a begin transaction, but don't include the commit or rollback.

BEGIN TRAN

SELECT *
FROM MyTable with(tablockx)

Then try to access the same table from a different session, it will wait until the transaction is completed / rollbacked.

In action in SSMS, left side is the locking query, right side is the blocked query.

To stop the blocking query, you can execute COMMIT in the locking query session, by selecting the COMMIT and executing it F5.