Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save mbourgon/d90864fd354ead2ac549dfc5b2aa0e94 to your computer and use it in GitHub Desktop.
Save mbourgon/d90864fd354ead2ac549dfc5b2aa0e94 to your computer and use it in GitHub Desktop.
An example proving that WAIT_AT_LOW_PRIORITY works in Standard Edition.
USE mycrappydatabase
GO
CREATE PARTITION FUNCTION pf(INT) AS RANGE LEFT FOR VALUES (5,10,15);
GO
CREATE PARTITION SCHEME ps AS PARTITION pf ALL TO ([PRIMARY]);
GO
CREATE TABLE partitionexample(id INT PRIMARY KEY) ON ps(id);
GO
INSERT dbo.partitionexample
(
id
)
VALUES
(3), (4),(7),(12),(13)
GO
CREATE TABLE staging_partitionexample(id INT PRIMARY KEY) ON ps(id);
--now, in other window:
--BEGIN TRANSACTION
-- UPDATE dbo.partitionexample SET id = 8 WHERE id = 7
ALTER TABLE partitionexample
SWITCH PARTITION 2 TO  [staging_partitionexample] PARTITION 2
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = blockers));
ALTER TABLE [staging_partitionexample]
SWITCH PARTITION 2 TO  partitionexample PARTITION 2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment