CODEWITHSUNDEEP
t-sql
kumar
kumar

Reputation: 107

T-SQL table variable data order

I have a UDF which returns table variable like

--
--
RETURNS @ElementTable TABLE
(
    ElementID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    ElementValue VARCHAR(MAX)
)
AS
--
--

Is the order of data in this table variable guaranteed to be same as the order data is inserted into it. e.g. if I issue

INSERT INTO @ElementTable(ElementValue) VALUES ('1')
INSERT INTO @ElementTable(ElementValue) VALUES ('2')
INSERT INTO @ElementTable(ElementValue) VALUES ('3')

I expect data will always be returned in that order when I say 

select ElementValue from @ElementTable  --Here I don't use order by

EDIT:

If order by is not guaranteed then the following query

SELECT T1.ElementValue,T2.ElementValue FROM dbo.MyFunc() T1
Cross Apply dbo.MyFunc T2
order by t1.elementid

will not produce 9x9 matrix as 

1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

consistently.

Is there any possibility that it could be like

1 2
1 1
1 3
2 3
2 2
2 1
3 1
3 2
3 3

How to do it using my above function?

Upvotes: 1

Views: 3611

Answers (4)

Tom Studee
Tom Studee

Reputation: 10452

No, the order is not guaranteed to be the same.

Unless, of course you are using ORDER BY. Then it is guaranteed to be the same.

Upvotes: 4

Ben Tennen
Ben Tennen

Reputation: 445

...or use a deterministic function

SELECT TOP 9 
  M1 = (ROW_NUMBER() OVER(ORDER BY id) + 2) / 3,
  M2 = (ROW_NUMBER() OVER(ORDER BY id) + 2) % 3 + 1
FROM 
  sysobjects

M1  M2
1   1
1   2
1   3
2   1
2   2
2   3
3   1
3   2
3   3

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Given your update, you obtain it in the obvious way - you ask the system to give you the results in the order you want:

SELECT T1.ElementValue,T2.ElementValue FROM dbo.MyFunc() T1
Cross join dbo.MyFunc() T2
order by t1.elementid, t2.elementid

You are guaranteed that if you're using inefficient single row inserts within your UDF, that the IDENTITY values will match the order in which the individual INSERT statements were specified.

Upvotes: 2

Chains
Chains

Reputation: 13167

Order is not guaranteed.

But if all you want is just simply to get your records back in the same order you inserted them, then just order by your primary key. Since you already have that field setup as an auto-increment, it should suffice.

Upvotes: 0

Related Questions