September 9, 2009 at 1:02 pm
In this specific case OP was relying on rownum to get "ordered" rows from a table which would never happen.
Here is one way to make it happen in SQL Server using the identity column and a table variable (or temprary table) to emulate the rownum of Oracle
CREATE TABLE Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
INSERT INTO Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
DECLARE @Employees TABLE
(
[rownum] [int] IDENTITY(1,1),
Emp_No INT,
EName VARCHAR(5),
Sal INT
)
INSERT INTO @Employees
SELECT Emp_No , EName, Sal from Employees
SELECT * FROM @Employees
rownum Emp_No EName Sal
1 111 AAAA 9999
2 222 bbbb 9890
3 777 vvvvv 7879
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 9, 2009 at 1:45 pm
Mazharuddin Ehsan (9/9/2009)
In this specific case OP was relying on rownum to get "ordered" rows from a table which would never happen.
Here is one way to make it happen in SQL Server using the identity column and a table variable (or temprary table) to emulate the rownum of Oracle
CREATE TABLE Employees (Emp_No INT, EName VARCHAR(5), Sal INT)INSERT INTO Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
DECLARE @Employees TABLE
(
[rownum] [int] IDENTITY(1,1),
Emp_No INT,
EName VARCHAR(5),
Sal INT
)
INSERT INTO @Employees
SELECT Emp_No , EName, Sal from Employees
SELECT * FROM @Employees
rownum Emp_No EName Sal
1 111 AAAA 9999
2 222 bbbb 9890
3 777 vvvvv 7879
I have to agree this is very creative - you certainly qualify as part of the "smart people" I was referring before but... unfortunately, it looks like it works but it doesn't work.
Please note that it doesn't work as OP expected to work in Oracle because of a very simple reason which is... "it doesn't work on Oracle" so by definition you cannot replicate in SQL Server something that doesn't actually happens in the other vendor's rdbms.
Oracle's rownum is a pseudo-column, it represents nothing but the sequence in which a row is delivered by a query - this number is assigned at the time the row is made available to the user and as you can see it can't be used to order nothing coming from such table because it is not in the table, it's not a column, it's a pseudo-column 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 3:54 pm
PaulB (9/9/2009)
Please note that it doesn't work as OP expected to work in Oracle
It is for the OP to tell us what he intends to do with the rownum. It may be useful if he wants to generate a serial# for a requirement like pagination in a web page, without having to specify an order by.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 9, 2009 at 4:56 pm
[font="Verdana"]In versions of SQL Server prior to 2005, I emulated it using an identity field. Since then, I use row_number(). Neither is exactly equivalent, but for what I've used it for (usually numbering consecutive child rows belonging to the same parent or for numbering lines in a file) it's all been fine.
What you are using it for is the key. If you give us some idea of what you use ROWNUM for, we can tell you how best to accomplish the same task in SQL Server.
[/font]
September 10, 2009 at 7:27 am
Mazharuddin Ehsan (9/9/2009)
PaulB (9/9/2009)
Please note that it doesn't work as OP expected to work in Oracle
It is for the OP to tell us what he intends to do with the rownum. It may be useful if he wants to generate a serial# for a requirement like pagination in a web page, without having to specify an order by.
Why you just read OP's initial post? 😀
As you can see OP "expects" to order rows by rownum which is an imposibility because rownum does not exist in the real world. OMG! ... how many times do I have to say it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 10, 2009 at 11:03 am
Bruce W Cassidy (9/9/2009)
[font="Verdana"]In versions of SQL Server prior to 2005, I emulated it using an identity field. Since then, I use row_number(). Neither is exactly equivalent, but for what I've used it for (usually numbering consecutive child rows belonging to the same parent or for numbering lines in a file) it's all been fine.What you are using it for is the key. If you give us some idea of what you use ROWNUM for, we can tell you how best to accomplish the same task in SQL Server.
[/font]
For a requirement like 'for numbering lines in a file' the row_number() will not be suitable because it needs an OVER and Order by Clause. This task can be accomplished best by the rownum (or its emulation in SQL Server).
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 10, 2009 at 11:26 am
.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 10, 2009 at 11:37 am
PaulB (9/10/2009)
Mazharuddin Ehsan (9/9/2009)
PaulB (9/9/2009)
Please note that it doesn't work as OP expected to work in Oracle
It is for the OP to tell us what he intends to do with the rownum. It may be useful if he wants to generate a serial# for a requirement like pagination in a web page, without having to specify an order by.
Why you just read OP's initial post? 😀
As you can see OP "expects" to order rows by rownum which is an imposibility because rownum does not exist in the real world. OMG! ... how many times do I have to say it?
Notwithstanding what the OP expects, we cannot deny the existance of the ROWNUM itself :w00t:
Apparently he did a mistake in wording his requirement properly. However, clearly he is interested in getting an euivalent for the rownum in SQL Server.
As also seconded by Bruce, one of the valuable usage of rownum is 'for numbering lines in a file'
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 10, 2009 at 12:33 pm
I'm giving up :doze:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 10, 2009 at 1:17 pm
This info rocks! Awesome!
Eddie Wuerch
MCM: SQL
September 10, 2009 at 6:33 pm
Mazharuddin Ehsan (9/10/2009)
For a requirement like 'for numbering lines in a file' the row_number() will not be suitable because it needs an OVER and Order by Clause. This task can be accomplished best by the rownum (or its emulation in SQL Server).
Eh? :blink: :blink: :blink:
That was posted before your temp-table with an identity column 'solution'.
Your solution there relies on IDENTITY values being assigned in the same 'order' as the query processor inserts the rows. Before SQL 2005, the assignment order was not defined even when an ORDER BY clause was present. Your query takes this one stage further and assume identity-column numbering now follows a guaranteed order even without an ORDER BY clause!
So, using ROW_NUMBER with an ORDER BY (SELECT NULL) is at least up-front about the lack of definition to the ordering. It also avoids using a temporary table or variable. So how is the IDENTITY solution better, and how is ROW_NUMBER() not suitable?!
You might like to also read my posts in this thread concerning %%LockRes%% and %%PhysRes%%
And please, for the love of small cute fluffy bunnies, take the point that you can't return results ordered by ROWNUM in Oracle. ROWNUM is defined by the return order - so you can't base the return order on it...!!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 5:01 am
Your solution there relies on IDENTITY values being assigned in the same 'order' as the query processor inserts the rows. Before SQL 2005, the assignment order was not defined even when an ORDER BY clause was present. Your query takes this one stage further and assume identity-column numbering now follows a guaranteed order even without an ORDER BY clause!
My solution was based on a scenario like as follows. Let us say there a table [SomeTable] and you want to number the rows returned by the statement SELECT * FROM [SomeTable] in the exactly same order as they are returned by the select stament. The behaviour defined in the aricle
not defined
does not focus on this.
So, using ROW_NUMBER with an ORDER BY (SELECT NULL) is at least up-front about the lack of definition to the ordering. It also avoids using a temporary table or variable. So how is the IDENTITY solution better, and how is ROW_NUMBER() not suitable?!
I agree. I missed the ORDER BY (SELECT NULL) trick that you gave. Both this and the IDENTITY solution give the same result.
And please, for the love of small cute fluffy bunnies, take the point that you can't return results ordered by ROWNUM in Oracle. ROWNUM is defined by the return order - so you can't base the return order on it...!!
No one disagrees with this for sure in this thread I think.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 11, 2009 at 5:42 am
Cool. While you are correct that the article I referened talks about SELECT INTO, the situation with INSERT INTO is similar.
The code INSERT INTO @Employees SELECT Emp_No , EName, Sal from Employees assumes that the order of rows returned by the SELECT will be mirrored by the assignment order of new IDENTITY values (as calculated by the internal GetIdentity() function in a Compute Scalar query plan iterator).
This is not, and has never been documented as guaranteed behaviour. The QP is free to complete the request (an INSERT from the result of a SELECT) in any correct way. Even if an ORDER BY clause has been specified, the scope of that is the SELECT - the INSERT statement is not required to respect it.
Now, it turns out (as privately confirmed by people with access to the SQL Server code) that an INSERT to a table with an IDENTITY column is guaranteed to follow the ORDER BY attached to an accompanying SELECT. This is, however, an implementation side-effect present only in 2005 and later. I can't stress enough that the behaviour could change at any time - in the next QFE, Cumulative Update, Service Pack or version.
Given that there is a better alternative (ROW_NUMBER OVER) - the 'trick' is no longer required. I can see no advantage to using it, only extra overhead (the temporary table or variable), and reliance on undocumented and unintended behaviour.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 10:27 am
Paul White (9/11/2009)
Cool. While you are correct that the article I referened talks about SELECT INTO, the situation with INSERT INTO is similar.The code
INSERT INTO @Employees SELECT Emp_No , EName, Sal from Employeesassumes that the order of rows returned by the SELECT will be mirrored by the assignment order of new IDENTITY values (as calculated by the internal GetIdentity() function in a Compute Scalar query plan iterator).This is not, and has never been documented as guaranteed behaviour. The QP is free to complete the request (an INSERT from the result of a SELECT) in any correct way. Even if an ORDER BY clause has been specified, the scope of that is the SELECT - the INSERT statement is not required to respect it.
Now, it turns out (as privately confirmed by people with access to the SQL Server code) that an INSERT to a table with an IDENTITY column is guaranteed to follow the ORDER BY attached to an accompanying SELECT. This is, however, an implementation side-effect present only in 2005 and later. I can't stress enough that the behaviour could change at any time - in the next QFE, Cumulative Update, Service Pack or version.
Given that there is a better alternative (ROW_NUMBER OVER) - the 'trick' is no longer required. I can see no advantage to using it, only extra overhead (the temporary table or variable), and reliance on undocumented and unintended behaviour.
Paul
Thanks Paul for the analysis. Even though the article is informing the flaw in behaviour with the 'function GetIdentity()' and is advising to
create a table that contains a column with the IDENTITY property and then run an INSERT .. SELECT … ORDER BY query to populate this table
, note that I have used the same approach sans the ORDER BY. I use this approach for the following requirement:
Let us say I have a flat file containg rows of data and I want to uplaod the file to a database table with an additional column comprising of the line number as it is appearing in the text file.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 11, 2009 at 10:59 am
Indeed. (Don't forget that the article specifically targets SQL Server 7.0 and 2000 though).
Let me put it one last way. There are always alternatives - you may choose an ordered INSERT with IDENTITY...I don't share your enthusiasm for it. Just be aware of it's shortcomings (performance and questionable behavioural stability).
For numbering lines in an imported file that guarantees correctness? Number the lines before they get to SQL Server - or use an SSIS transform.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply