By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )
INSERT dbo.JustAnotherTable WITH (TABLOCKX)
SELECT TOP 1000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
1000000 rows affected
ALTER TABLE dbo.JustAnotherTable SET (LOCK_ESCALATION = DISABLE) --maximize the impact of locking
DECLARE @trash CHAR(5)
SELECT @trash = notID
FROM dbo.JustAnotherTable
SELECT 'Make sure the cache is warm'
| (No column name) |
|---|
| Make sure the cache is warm |
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()
SELECT @trash = notID
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)
SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME()) AS [Time taken]
| Time taken |
|---|
| 157 |
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()
SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)
SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME()) AS [Time taken]
| Time taken |
|---|
| 220 |