CODEWITHSUNDEEP
sqlsql-servert-sqltable-valued-parameters
FirstByte
FirstByte

Reputation: 631

Table Value parameter cursor order

I'm passing a single string column table parameter (no other option, must be one column). I've read that there's no guarantee of order on a TVP, but all the articles I've read are referring to a select statement. If I run a cursor on a TVP, will it always read from first record to last?

Upvotes: 0

Views: 491

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

Table-valued parameter is a table, a temporary table.

Any table, including temporary tables in SQL Server is an unordered set of rows.

When you write

SELECT Column1 FROM Table1

the server can return rows in any order it chooses. It will return all rows of a table, of course, you just can't predict in which order.

If you need some specific order, then you have to specify ORDER BY clause, like this:

SELECT Column1 FROM Table1 ORDER BY Column2

All this applies to TVP and cursors.

If you define a cursor without ORDER BY, it will read through all rows, but in some unspecified order, which may vary with each run. If you need the cursor to process rows of TVP in some specific order, then specify the ORDER BY clause in the SELECT statement of the cursor definition.

In other words, the cursor will always read from the "first" record to the "last". If it is important to the logic of the cursor to have defined what is "first" and what is "last" you need to add ORDER BY clause.

Upvotes: 2

Related Questions