Home Forums SQL Server 2014 Development - SQL Server 2014 Windowing functions vs Non-deterministic ROW-NUMBER() RE: Windowing functions vs Non-deterministic ROW-NUMBER()

  • April 19, 2016 at 7:48 am

    #1873071

    drew.allen (4/18/2016)

    [snip]

    ROW_NUMBER() is deterministic as long as the ORDER BY clause is deterministic.

    Alan.B (4/18/2016)

    [snip]

    In other words, when the columns referenced in your the ORDER BY portion of your OVER clause are unique then then your all four "ranking" functions will produce a deterministic result set; when they aren't then only RANK and DENSE_RANK produce a deterministic result set, ROW_NUMBER and NTILE do not.

    Now, this makes sense. Books on line (msdn Microsoft) simply states: "The following built-in functions from other categories are always nondeterministic." and that includes ROW_NUMBER.