wBob (7/21/2014)

---

I would approach that using TOP and CROSS APPLY, eg:

SELECT c2.*

FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x

CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

If there is an index on CoverageCode, that will really help too, or if the CoverageCodes are stored in their own table ( so we don't have to do the DISTINCT on dbo.Contract ) then that was the most efficient in my simple test rig.

I found this marginally more efficient than the CTE at a scale of 1 million rows, and the CTE appears unable to get an accurate estimated rowcount. This is unlikely to matter at small volumes. but it's always worth considering alternatives : )

My results :

(see attachment)

My simple test rig:

USE tempdb

GO

-- !!WARNING - this is not to be run on a production server

IF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]

IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodes

GO

CREATE TABLE dbo.[Contract]

(

rowIdINT IDENTITY PRIMARY KEY,

CoverageCodeINT NOT NULL

)

GO

CREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )

GO

;WITH cte AS (

SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn

FROM master.sys.columns c1

CROSS JOIN master.sys.columns c2

CROSS JOIN master.sys.columns c3

)

INSERT INTO dbo.[Contract] ( CoverageCode )

SELECT rn % 101

FROM cte

GO

SELECT DISTINCT CoverageCode

INTO dbo.CoverageCodes

FROM dbo.[Contract]

GO

CREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- 1)

SELECT c2.*

FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x

CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

ORDER BY 2, 1

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- 2)

;WITH PRE_SEL AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY CoverageCode ORDER BY (SELECT NULL)) AS PRT_RID

,*

FROM Contract

)

SELECT * FROM PRE_SEL WHERE PRT_RID <=5

ORDER BY CoverageCode, rowId

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- 3)

SELECT c2.*

FROM dbo.CoverageCodes x

CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

ORDER BY 2, 1