0

The requirement is to return unique value for each processed row from stored procedure, which will be used like dummy primary key. One solution seems to be using ROW_NUMBER() function. Another one is given here. Perhaps, there can be solutions involving Guid. Can someone recommend me a solution which is performant and reliable?

Community
  • 1
  • 1
synergetic
  • 7,756
  • 8
  • 65
  • 106
  • please look here: http://stackoverflow.com/questions/17839420/generate-guid-for-every-row-in-a-column – swe Feb 03 '17 at 10:17
  • The answer depends on the context in which you plan to use the stored procedure. – Tim Biegeleisen Feb 03 '17 at 10:17
  • Whats happening in the SP and how are you returning values? For example if you have a select statement at the end of the SP you can take one approach, if you are returning a table variable via an output parameter you could use another approach. – Joe C Feb 03 '17 at 19:18

1 Answers1

0

  • A random number would not be an option (as you specify unique).

  • ROW_NUMBER is a bigint taking up 8 bytes of storage per row.

  • uniqueidentifier is a 16 byte structure and more costly to obtain than a ROW_NUMBER which is simply incremented (SQL's unique identifiers are GUIDs, with NEWID() being slower than NEWSEQUENTIALID() because NEWSEQUENTIALID() will increment from a seed GUID).

  • In scenarios where you INSERT into a table variable or a temporary table, you can use IDENTITY. Storage size is that of the column data type, which can be any integer type (no bit, no decimal). It will increment from a configurable offset (default 1), in configurable step (default 1).

This seems to make ROW_NUMBER your best fit, it is both fast and reliable.

I would recommend to base your design choice on more than just performance though. On any reasonably configured SQL server installation you will barely notice a difference in speed, and unless you have very constrained resources, storage should not be the bottleneck either. Some rather old benchmarks here.

Be aware that neither will help you to maintain or guarantee a particular order of the returned rows - you still need to ORDER BY your outermost SELECT if you need predicable results.

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77