Inline view ordering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inline view ordering

Author

Topic

MrMoosehead
Starting Member

2 Posts

Posted - 2003-04-10 : 07:00:02

Hi.
We have a query that unions three queries (dummy code below)
This union is part of an inline view to select out one of the columns of the union.
This inline view is a VIEW on the DB.

The problem is that we have had a couple of instances of the order of the rows returned from the view being mixed up - this is a problem as the view is used to create a text file via BCP and the first and last parts of the union are queries to create a header and footer line

The answer as to why it's happening? Well, we're not sure - but I think it's to do with storage of tempDB data, CAN ANYONE HELP!?

This is a simplified version of the code:

*****************************************
CREATE VIEW vwOutput AS
SELECT vw1.ROW FROM
(
SELECT npos=0, 'ROW' = 'This is the header'
UNION
SELECT npos=1, 'ROW' = datafield
FROM tblSomeTable
UNION
SELECT npos=2, 'ROW' = 'This is the footer'
)
AS vw1
****************************

The reason for the npos is that the order was coming out mixed up, so the developer added this and it worked - this is due to the fact that it is a UNION, not UNION ALL, so the concatenation is then ordered as part of the 'distincting' process.

I reckon that the fact that there is an inline view means that the data has to be written to tempdb in a temp table, and that this is sometimes changing the order of the row storage (as any insert statement on any table may insert the data in the middle), and when the select is done on the view and no ordering is specified, it just returns what it has in the same order that it is stored.

Can anyone clarify this?

TIA,
Moosey.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-04-10 : 09:12:38

Moosey
Ive just tried this in Query Analyser and it works fine

SELECT vw1.ROW FROM
(
SELECT TOP 100 PERCENT npos=1, 'ROW' = 'A'
UNION
SELECT TOP 100 PERCENT npos=2, 'ROW' = 'B'
UNION
SELECT TOP 100 PERCENT npos=0, 'ROW' = 'C'
ORDER BY npos
)
AS vw1

Returns:

ROW
----
C
A
B

HTH

Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-10 : 10:14:29

Mr Moose --

In a relational database, since there is no concept of "row order", SQL Server is not obligated to return rows in any order, UNLESS you specifically tell it how to sort it. Subqueries cannot be ordered becuase there is no point -- the only time ordering can be important is for the final results of your query.

So, if you have 3 tables and you wish to perform a UNION with them, and you wish for the order of the results to be returned 1 table after another, then the solution you have proposes is exactly the way to do it -- assign the rows from each table with a unique number and sort your results by that number.

So, the one thing you need to add is:

CREATE VIEW vwOutput AS
SELECT vw1.ROW FROM
(
SELECT npos=0, 'ROW' = 'This is the header'
UNION
SELECT npos=1, 'ROW' = datafield
FROM tblSomeTable
UNION
SELECT npos=2, 'ROW' = 'This is the footer'
)
AS vw1
ORDER BY nPos

The ORDER BY is necessary, even though it may appear that without it SQL orders the results the way you want. There is no guarantee that as statistics change in your database that SQL Server will not alter the way rows are returned from one call to another without specifying an ORDER BY.

So, I think you had the right idea and the way you went about solving the problem was right on, you just needed the ORDER BY.

AndyB -- even your solution has no guarantee of ordering the results properly: only the final resultset needs or should have an ORDER BY clause. Putting it in the middle (unless you are using a TOP n or <100%) just forces SQL to needlessly order the intermediate rows when evaluating the query, and in the end it may still return the rows in whichever order it chooses.

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-04-10 : 10:29:58

Thanks Jeff
You live and learn - thats why i come on here

Now i look at your explanation it has become clear

Bloody marvellous this forum
Cheers

Andy

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 10:31:32

I do the same type of thing when I want to generate stuff from the catalog. In this case I'm worryed about a group of sql statements taht I'm generating (In this Case Inserts), and the the row orders with in that grouping. The following will gen an insert statement for a particular table in a database (just because I'm lazy and don't like to type):

USE NorthWind

DECLARE @TBName sysname, @TBName2 sysname

SELECT @TBName = 'Orders', @TBName2 = 'Orders2'

SELECT SQL FROM (

SELECT 'INSERT INTO ' + @TBName2 + ' ( ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ') ' As SQL, TABLE_NAME, 2 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
UNION ALL
SELECT 'SELECT ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

Brett

8-)

MrMoosehead
Starting Member

2 Posts

Posted - 2003-04-10 : 10:48:37

Thanks all.

Esp. Jeff. Top quality answer, thanks for taking the time.

The particular thing that I was interested in was whether my analysis of the situation was correct regarding the inline view in tempdb and the ordering of this.

Anyway, anyone want to consider 2 billion row tables now?

}:8)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-10 : 10:53:35

I think what you mentioned about UNION vs UNION ALL is totally correct ... in order to remove duplications, UNION has to do some ordering and the end result may be truly "random", whereas for a UNION ALL it can just return rows without needed to do any shuffling or comparisons. Look at the execution plans, as well, you'll see the difference.

That's why UNION ALL's are more efficient than UNION's ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources