There is a 8KB limit as input to the HASHBYTES function. Make sure your columns don't exceed that (not necessarily a character limit).

WITH CTE

AS

(

SELECT t.x

FROM

( VALUES

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)

)t (x)

),

CTE2

AS

(

SELECT ROW_NUMBER() OVER(ORDER BY c.x) AS RowNum

FROM CTE c

CROSS JOIN CTE c2

CROSS JOIN CTE c3

CROSS JOIN CTE c4

CROSS JOIN CTE c5

),

CTE3

AS

(

SELECT CAST(c.RowNum AS NUMERIC(15,2)) AS...

The answer to that questions depends on "the what". Some implementations are better when you have disparate data sources (flat files, spreadsheets, xml files, etc) as

DEK46656 suggests. One of...

Great article. The idea behind using the hash to determine data change is a great. Determining which hashing algorithm to use and where to use it (ETL, update...

The reason I had the index sorting in the opposite direction was to demonstrate the structure of table. The query itself represents a requirement that was needed and the...

First, please note the subtitle of the article, An Efficient Alternative to Subqueries. There were no claims about it being the best, just an alternative to subqueries. From...

FreeHansje (6/2/2009)

---

Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their...

FreeHansje (6/2/2009)

---

Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their...

mbergstrom (5/29/2009)

---

How does the ROW_NUMBER() approach compare with using self-joins as follows?

SELECT Previous.Category,

Previous.Date,

Next.Date

FROM Dates.CategoryDate Previous

LEFT JOIN Dates.CategoryDate Next

ON Previous.Category = Next.Category

AND Previous.Date < Next.Date

LEFT JOIN Dates.CategoryDate Middle

ON Previous.Category = Middle.Category

AND...

Pritesh Keniya (5/21/2009)

---

Thanks for introducing the concept of Row_Number(). A good example to quickly understand the concept.

Just out of curiosity I thought: "What if we use CTE?"

------------------------------------------------------------------------------------------------

With MaxVer as

(

SELECT...

wbrianwhite (5/12/2009)

---

William Hutton (5/12/2009)

---

I think it's just a matter of ...elegance? To me using Row_Number() statements (and CTEs in general) are easier to write and easier to follow in specific...

JJ B (5/12/2009)

---

Well done article.

Question: I hate to open a can of worms on a different topic, but I'm so curious as to why you specified the nolock...

There are not any issues to my knowledge that require the NOLOCK hint for ROW_NUMBER(). I just used it for performance reasons.