get-latest-object-version

get-latest-object-version.sql

USE tempdb
GO

/*
Generate Sample Data
*/
DROP TABLE IF EXISTS dbo.[Sample]
GO

WITH cte AS 
(
	SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY a.object_id) AS N FROM sys.all_columns a, sys.all_columns b 
)	
SELECT
	CAST(N AS INT) AS ObjectId,
	CAST(RAND(CHECKSUM(NEWID())) * 10000 AS INT) AS VersionId
INTO
	dbo.[Sample]
FROM
	cte
GO

CREATE NONCLUSTERED INDEX ixc ON dbo.[Sample](ObjectId, VersionId DESC)
GO

/*
Get Last version
*/
WITH cte AS
(
	SELECT 
		ObjectId,
		VersionId,
		ROW_NUMBER() OVER (PARTITION BY ObjectId ORDER BY VersionId DESC) AS K
	FROM 
		dbo.[Sample]
)
SELECT
	*
FROM
	cte
WHERE
	ObjectId = 1234 AND K = 1