May 19, 2020 at 11:48 pm
I came across this by chance when I was testing the assignment of variables. This SELECT query returns a single column with 12 rows. The last value is assigned. The result of this is the number 70013 displaying on the Messages tab in the results pane window. This makes sense.
DECLARE @placeHolder int
SELECT @placeHolder = ord_no
FROM dbo.orders;
PRINT @placeholder;
Then I flipped around the assignment in the SELECT statement to see what would happen.
DECLARE @placeHolder int
SELECT ord_no = @placeHolder
FROM dbo.orders;
PRINT @placeholder;
The result of this was a column called ord_no with 12 rows. All rows were NULL. This displayed on the Results tab inside the results window pane. There was also a Message tab next to this.
In both cases I am using a Print statement. So why is it that in one case the result appears on the Messages tab and in the other case the result appears on the Results tab?
I have two pictures attached.
May 20, 2020 at 6:41 am
Since you didn't use a variable on the left side of the "=", you've changed it to a column assignment. Since the variable had no assigned value, it went ripping through the table (which is the basic principle of a "Pseudo-Cursor") and returned the value of the variable (a NULL) once for each row in the table. This will appear on the grid tab if you're in the grid mode. It will return on the messages tab if you're in the text mode.
Finally, the print will also display a NULL on the messages tab.
The "Pseudo-Cursor" thing where you're not actually using anything from the table other than the "presence of rows" is an incredibly powerful tool that can replace a great many explicit loops. For example... see what happens when you replace the variable with a row number and a date function.
SELECT ord_no = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '20 May 2020')
FROM dbo.orders;
Of course... that's not even close to being an order number from dbo.orders. It's a calculated date. We used the "loop" or "Pseudo-cursor" of a SELECT to return 12 calculated rows, which is the number of rows in the dbo.orders table.
Guess what we can do with that? Let's CROSS JOIN our "row source" table (which we only use the "presence of rows" from to form a "Pseudo-Cursor") using a slightly larger table and, Poof!... we have the makings for a million row (for example) Tally Table.
SELECT TOP 1000000
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
The CROSS JOIN is a method of "Relational Multiplication" which is a fancy name for a "Cartesian Product" which is a fancy name for "Very quickly creates a row source with a shedload of rows to replace Cursors and While Loops".
You probably won't find anything about that in any "official" Microsoft documentation. It's how I make "Million Row" test tables so quickly.
If you look at the link below in my signature line about the fnTally function, you'll see a function that uses a different type of row source / Pseudo Cursor that I call a cCTE or "Cascading CTE". First I ever saw of such a thing was by Itzik Ben-Gan. It produces absolutely zero reads and runs as fast as the wind.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2020 at 6:06 pm
Since you didn't use a variable on the left side of the "=", you've changed it to a column assignment. Since the variable had no assigned value, it went ripping through the table (which is the basic principle of a "Pseudo-Cursor") and returned the value of the variable (a NULL) once for each row in the table. This will appear on the grid tab if you're in the grid mode. It will return on the messages tab if you're in the text mode.
Finally, the print will also display a NULL on the messages tab.
The "Pseudo-Cursor" thing where you're not actually using anything from the table other than the "presence of rows" is an incredibly powerful tool that can replace a great many explicit loops. For example... see what happens when you replace the variable with a row number and a date function.
SELECT ord_no = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '20 May 2020')
FROM dbo.orders;Of course... that's not even close to being an order number from dbo.orders. It's a calculated date. We used the "loop" or "Pseudo-cursor" of a SELECT to return 12 calculated rows, which is the number of rows in the dbo.orders table.
Guess what we can do with that? Let's CROSS JOIN our "row source" table (which we only use the "presence of rows" from to form a "Pseudo-Cursor") using a slightly larger table and, Poof!... we have the makings for a million row (for example) Tally Table.
SELECT TOP 1000000
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;The CROSS JOIN is a method of "Relational Multiplication" which is a fancy name for a "Cartesian Product" which is a fancy name for "Very quickly creates a row source with a shedload of rows to replace Cursors and While Loops".
You probably won't find anything about that in any "official" Microsoft documentation. It's how I make "Million Row" test tables so quickly.
If you look at the link below in my signature line about the fnTally function, you'll see a function that uses a different type of row source / Pseudo Cursor that I call a cCTE or "Cascading CTE". First I ever saw of such a thing was by Itzik Ben-Gan. It produces absolutely zero reads and runs as fast as the wind.
This makes sense. For your example with the DATEADD ( ) and the ROW_NUMBER( ), that was way over my head. I'll have to read it again later, but for now I think that might be beyond my level. However I do appreciate that you took the time to post that.
I believe I have an idea of what you are saying about a pseudo cursor. I'm familiar with CROSS JOINS and cartesian products.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply