A rotating message feels stale when the same line keeps returning. A quote of the day table can track usage and finish the collection before starting again.
Define What Counts as a Repeat
A rotating picker and a daily picker solve different problems. The rotating picker advances whenever it is called. The daily picker returns one stable choice throughout the same calendar day.
I decide which behavior the application needs before writing the ordering expression. I also define the time zone for the day boundary. Otherwise, two correct implementations produce an argument at midnight.
For rotation, a cycle means using every eligible line once. Repeats become allowed after that complete cycle. Adding or removing lines changes the collection and therefore changes the cycle's practical meaning.
A random sort alone provides no memory of previous selections. It can choose the same line on consecutive requests. Randomness has never promised to read yesterday's application screen.
Keep the examples in a disposable user database. The text values below are synthetic database tips rather than attributed quotations. Use only content the application is authorized to display.
Store the Quote of the Day Rotation State
The table keeps an identifier, the display text, and the date last shown. A null date means the line remains unused in the current cycle. An index supports finding those unused candidates.
CREATE TABLE dbo.DailyLineDemo
(
LineID int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_DailyLineDemo PRIMARY KEY,
LineText nvarchar(400) NOT NULL,
last_shown date NULL
);
CREATE INDEX IX_DailyLineDemo_LastShown
ON dbo.DailyLineDemo(last_shown, LineID);
INSERT dbo.DailyLineDemo(LineText)
VALUES (N'Check the transaction before closing the window.'),
(N'Read the actual plan before changing an index.'),
(N'Test the restore before trusting the backup.');The date records usage without needing an execution timestamp. It does not preserve every historical display. If display history matters, write each selection to a separate history table.
Resetting dates loses their previous values. That is acceptable for this small rotation demonstration because null represents cycle eligibility. A production history requirement needs a separate cycle number or retained usage records.
Validate empty text and disabled lines at the application boundary. If you add an enabled flag, apply it consistently to picking and cycle completion. A disabled line must not prevent the eligible collection from finishing.
Pick and Mark the Quote of the Day in One Statement
Selecting an identifier and updating it in a later statement creates a race. Two sessions can read the same unused row before either marks it. Update an ordered candidate and return its text through OUTPUT instead.
The complete transaction below also serializes cycle reset with an application lock. Every rotation caller must use the same lock resource in the same database. This prevents another caller from resetting while a selection remains unfinished.
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 50001, 'Run this picker without an existing transaction.', 1;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @LockResult int;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'DailyLineDemoCycle',
@LockMode = N'Exclusive',
@LockOwner = N'Transaction',
@LockTimeout = 5000;
IF @LockResult < 0
THROW 50002, 'The rotation lock was not acquired.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.DailyLineDemo)
THROW 50003, 'The collection is empty.', 1;
IF NOT EXISTS
(SELECT 1 FROM dbo.DailyLineDemo WHERE last_shown IS NULL)
UPDATE dbo.DailyLineDemo SET last_shown = NULL;
;WITH NextLine AS
(
SELECT TOP (1) LineID, LineText, last_shown
FROM dbo.DailyLineDemo WITH (UPDLOCK, READPAST, READCOMMITTEDLOCK)
WHERE last_shown IS NULL
ORDER BY last_shown, NEWID()
)
UPDATE NextLine
SET last_shown = CONVERT(date, SYSDATETIME())
OUTPUT inserted.LineID, inserted.LineText, inserted.last_shown;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;UPDLOCK protects candidates during selection, and READPAST skips incompatible row locks. READCOMMITTEDLOCK supports this pattern when read committed snapshot is enabled. Run it under READ COMMITTED isolation, not an arbitrary inherited isolation setting.
READPAST does not skip page locks. It also cannot tell whether a skipped row means cycle completion. The shared application lock therefore coordinates that decision instead of treating an empty candidate result as a complete cycle.
The quote of the day selection becomes consumed when its transaction commits. OUTPUT can reach a caller before the commit finishes. Display the returned value only after confirming the command completed successfully.
Check the Cycle in Two Windows
Run the picker repeatedly in two SSMS windows against the same database. Keep each successful returned identifier until the cycle completes. No identifier should appear twice inside that completed cycle.
Then inspect the table to see which dates remain null. A null date identifies an unused entry in the current cycle. It does not mean that entry has never appeared in any earlier cycle.
SELECT LineID, LineText, last_shown
FROM dbo.DailyLineDemo
ORDER BY last_shown, LineID;A one-line collection necessarily repeats when the next cycle begins. Larger collections can also repeat across a cycle boundary by chance. If adjacent repeats are forbidden, retain the previous identifier and exclude it from the next cycle's first pick.
That rule needs a special case for a one-line collection. Reject the requirement or accept the unavoidable repeat explicitly. No locking hint can manufacture a second line that does not exist.
Keep One Choice All Day
For a stable daily choice, order the identifiers and map a date checksum to an ordinal. Convert the checksum to bigint before ABS to avoid the minimum integer overflow. This query leaves rotation dates unchanged.
DECLARE @Day date = CONVERT(date, SYSDATETIME());
WITH Numbered AS
(
SELECT LineID, LineText,
ROW_NUMBER() OVER (ORDER BY LineID) AS PositionNumber,
COUNT_BIG(*) OVER () AS CollectionCount
FROM dbo.DailyLineDemo
)
SELECT LineID, LineText
FROM Numbered
WHERE PositionNumber =
1 + ABS(CONVERT(bigint, CHECKSUM(@Day))) % NULLIF(CollectionCount, 0);Which behavior should a second visitor receive on the same day? Choose the stable query when everyone should see the same line. Choose the rotating transaction when each accepted request should advance the collection.
The daily result stays stable only while collection membership and ordering remain unchanged. Adding a line can change today's selection. Persist the chosen identifier by date when that stronger guarantee matters.
A checksum does not guarantee different choices on consecutive dates. It produces a deterministic mapping rather than a schedule without repeats. Combining both guarantees requires storing each day's selection as part of the rotation transaction.
Give that daily selection table a unique date key. Read the existing date first, and create a new selection only when it is missing. Coordinate creation with the same transaction lock so concurrent visitors receive the committed identifier.
If the command loses its connection after committing, retrying the rotating picker consumes another line. A daily selection record avoids that ambiguity for a date-based display. Decide whether requests or calendar dates own consumption before adding retries.
Keep the Collection Small
ORDER BY NEWID evaluates random values across candidates and sorts them. That is reasonable for thousands of short lines. A large message catalog needs a different design and workload measurements.
The quote of the day table also needs a clear update policy. Coordinate administrative edits with the cycle lock when they affect membership. Keep rotation state and retained history separate so each remains understandable.
Finally, verify behavior at both concurrency and calendar boundaries. A usable picker needs more than an interesting ordering expression. Its cycle rule, daily rule, and transaction boundary must agree with the application.
Related reading on this blog: Techniques for Retrieving Random Rows and Simple Example of READPAST Query Hint.
A daily message is not a random accident, it is a selection rule with memory.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
