Select TOP N returning N+ rows with OpenQuery to Snowflake

Question

Select TOP N returning N+ rows with OpenQuery to Snowflake

AVB

SSChampion

Points: 11418
More actions
December 10, 2021 at 2:37 pm

#3962405

I have a basic query using openquery and a linkedserver pointing to a Snowflake DB. When I select Top 1000 (e.g. Select Top 1000 * From OpenQuery(...)) it results 1035 rows.
I've tried it with multiple TOP N values and most times it returns a higher row count but never less. Setting the Rowcount was successful in limiting the rows to the correct amount though and provided consistent results.
Anyone have any ideas as to why Top N doesn't work correctly with this particular setup?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 1

Total SWAG on my part but NOT using parentheses for the "N" number is only allowed for SELECTs and they did that only for backward compatibility. The correct syntax is to include the parentheses as in SELECT TOP (N). Like I said, total SWAG on my part but try that.

I've also seen the optimizer put TOP in the wrong place depending on the number of rows (specifically in things like cascading CTEs to create a Tally or GetNums function) and it's an intermittent fault.

Try adding the parentheses and see if that corrects the issue. If it doesn't, then you may have discovered a bug in SQL Server.

As a bit of a sidebar, I gave up on reporting bugs and highly desired functionality to MS years ago because they keep changing the system and don't forward upvotes and the people at MS don't seem to grasp the nature of things (like a machine language function to generate sequences, which has been an open item with lots of misunderstanding by MS as to what people would use it for) unless they seem to be "marketable" to THEM and they appear to not always "get it".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 2

Jeff I appreciate the suggestion! I was hoping it was that simple but it didn't work.

I'll have to ask other SQL and Snowflake users and see if they are experiencing it too.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

I have a few thoughts on this.

First - is this consistently returning the same number of rows? Like if you run the query 10 times do you always get 1035 rows or does it jump around a bit? On top of that, is it always the same number of additional rows, in this case, 35? If so, how many rows do you get if you try to do a TOP (965)? I am wondering if it ALWAYS is adding 35 rows in which case you may be able to just have a comment in there saying why you are doing a TOP that is less than the number of rows you actually want.

Second, what about if you tossed the "TOP 1000" into the openquery part? what I mean is instead of "SELECT TOP(1000) * FROM OPENQUERY(...)" you do "SELECT * FROM OPENQUERY(<linked server>,"SELECT TOP(1000) FROM ...")"? or is that not an option? It might not be an option with Snowflake; I am not sure on the syntax with their stuff.

Third - If you look at the actual execution plan, do you see it doing a TOP operation? I am wondering if the execution plan MAY have more information on why it isn't filtering out the rows like you expect.

Lastly - are you fully patched on SQL Server and SSMS? It COULD be that this issue has already been addressed. If you are not using SSMS 18, does the problem go away if you use SSMS 18? If not, what about using an older SSMS such as 2014? It is the version of SQL you posted this under, so I expect you have SSMS 2014 installed.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.