RANGE VS ROW in SQL Server 2012

Understanding the difference

ROWS is managed by the physical position of the row, independent whether the values of the columns specified in the
order by clause repeat or not.

If RANGE is specified and the data in the rows duplicate it is considered as ONE range, like a distinct of the rows.

Example:

USE tempdb
GO
IF OBJECT_ID(‘tempdb.dbo.#TMP’)  IS NOT NULL
  DROP TABLE #TMP
GO
CREATE TABLE #TMP (ID Int, Col1 Char(1), Col2 Int)
GO

INSERT INTO #TMP VALUES(1,’A’, 5), (2, ‘A’, 5), (3, ‘B’, 5), (4, ‘C’, 5), (5, ‘D’, 5)
GO
SELECT * FROM #TMP

 

SELECT *,
       SUM(Col2) OVER(ORDER BY Col1 RANGE UNBOUNDED PRECEDING) “Range”,
       SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) “Rows”
  FROM #TMP

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s