4 ms·

I'd be happy with simple syntax for things like OFFSET/LIMIT and GROUP_CONCAT at this point.

by NDizzle 15y ago

mgkimsal 15y ago
if they gave you that, it'd be too easy to port a lot of basic stuff away from sql server. I worked someplace that was primarily a SQL Server shop, although was still largely Java and some PHP at the time. I was tasked with building something to paginate through records. There were several hundred thousand to paginate through, and I had to resort to getting the DBA to build some weird sproc with cursors and junk in it. "I just need some of the rows" "Why would you need that?" (honest to goodness question from the DBA) "Well, because I only need to show 30 items on the screen, not 400,000. Showing 400,000 might take a while." "Just use TOP. SQL Server lets you do SELECT TOP(30) already!" "Umm... but I might need to see the second 30, or the 99th 30." "Why? No one does that!" Insane. They experimented with some monstrosity of nested TOP() queries. They refused to allow MySQL to be used ("it's just a toy"), when it was perfectly capable for the requirement, and had developer-useful stuff like LIMIT in it. I got some overly engineered sproc that had to be updated whenever I needed a change, and I ended up leaving a few months later. Not specifically because of that, but that culture was one I couldn't fit in to. I've heard it's gotten better, but I don't really believe it's better so much as the people who've stayed there have adapted and worked around the cognitive dissonance it triggered.
- saryant 15y ago
  I might be wrong, but I'm pretty sure I've implemented pagination in SQL Server and it wasn't as complicated as that. Maybe I produced the world's worst pagination technique but I don't think I ever resorted to TOP. The ROW_NUMBER() function takes care of this.
  - steverb 15y ago
    Yes. http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging http://stackoverflow.com/questions/548475/efficient-way-to-i...
- tbone47 15y ago
  It's 2012. There's almost no need to ever use cursors. I'm pretty sure pagination can easily be achieved with ROW_NUMBER()
- scriby 15y ago
  This problem was solved in SQL 2005. The solution in SQL 2000 was pretty terrible. You had to select from the start of the table to the end of the current page, reverse it, take a top of the page size, then reverse again.
  - mgkimsal 15y ago
    Yes, that was one of the proposed solutions. Again, as I said in one of my replies, this was in mid 2004 - we were using SQL Server 2000, not anything higher, because it wasn't available to us yet.
- yread 15y ago
  I am also angry that stuff like pagination isn't done in some standard way (pg, oracle, mysql sql server are doing it differently, sybase doesn't even have it). BUT! Dude, saying that sql server can't do something when a simple search gives you the right solution? The SQL standard is missing couple more features I think, like identity/sequences (I even had to use different code in .NET to get the newly inserted id), upserts, ... Writing cross-database software isn't even worth it. And I say that as a developer of a medium sized project supporting 4 databases. It would have been much better if we just decided to use one and use all the features properly
  - manub22 15y ago
    You can do pagination by using the standard ROW_NUMBER() function and using CTEs, check my blog post for the same: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/ http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored... Also in SQL 2012 now you can use the offset-fetch clause to implement paging, check this: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/ http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...
- deleted 15y ago
  [deleted]
- mgkimsal 15y ago
  ACK - I can't edit. FWIW, this was in the year 2004, not 2012. And... to the extent that people will say "it's got it now"... this the same situation people have with mysql - spouting off about stuff that it didn't do 10 years ago, and don't use it (or postgres from X year ago, etc). Once you hit some rather stupid/useless limitation, it tends to stick with you and influence your choices in the future. I've used SQL Server on projects since then, but it's never been my choice to start a project with it. Clients have used it, and I've used it because that's what they had opted for, but it wasn't my choice or suggestion. As someone else noted, connecting to it from outside the world of Windows/.Net is a bit of a pain. The PHP connector situation is weird - it's changed in 5.3, and you seem to have to download a separate connector from MS directly. It'd be super swell if they'd just open source that connector so it could be distributed around (or provide a binary and license terms so at least WAMP-stacks could distribute it more easily).
redstripe 15y ago
OFFSET/LIMIT is coming: http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110%29.aspx http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110... GROUP_CONCAT still needs to be done with the FOR XML PATH hack
josephcooney 15y ago
You'll be happy then. I think they added OFFSET in SQL 2012. http://sqlwithmanoj.wordpress.com/2011/07/28/tsql-denali-new-feature-offset-fetch-clause-for-pagingpagination/ http://sqlwithmanoj.wordpress.com/2011/07/28/tsql-denali-new...

I'd be happy with simple syntax for things like OFFSET/LIMIT and GROUP_CONCAT at this point.

by NDizzle 15y ago

I'd be happy with simple syntax for things like OFFSET/LIMIT and GROUP_CONCAT at this point.

if they gave you that, it'd be too easy to port a lot of basic stuff away from sql server. I worked someplace that was primarily a SQL Server shop, although was still largely Java and some PHP at the time. I was tasked with building something to paginate through records. There were several hundred thousand to paginate through, and I had to resort to getting the DBA to build some weird sproc with cursors and junk in it. "I just need some of the rows" "Why would you need that?" (honest to goodness question from the DBA) "Well, because I only need to show 30 items on the screen, not 400,000. Showing 400,000 might take a while." "Just use TOP. SQL Server lets you do SELECT TOP(30) already!" "Umm... but I might need to see the second 30, or the 99th 30." "Why? No one does that!" Insane. They experimented with some monstrosity of nested TOP() queries. They refused to allow MySQL to be used ("it's just a toy"), when it was perfectly capable for the requirement, and had developer-useful stuff like LIMIT in it. I got some overly engineered sproc that had to be updated whenever I needed a change, and I ended up leaving a few months later. Not specifically because of that, but that culture was one I couldn't fit in to. I've heard it's gotten better, but I don't really believe it's better so much as the people who've stayed there have adapted and worked around the cognitive dissonance it triggered.

saryant 15y ago
I might be wrong, but I'm pretty sure I've implemented pagination in SQL Server and it wasn't as complicated as that. Maybe I produced the world's worst pagination technique but I don't think I ever resorted to TOP. The ROW_NUMBER() function takes care of this.
- steverb 15y ago
  Yes. http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging http://stackoverflow.com/questions/548475/efficient-way-to-i...
tbone47 15y ago
It's 2012. There's almost no need to ever use cursors. I'm pretty sure pagination can easily be achieved with ROW_NUMBER()
scriby 15y ago
This problem was solved in SQL 2005. The solution in SQL 2000 was pretty terrible. You had to select from the start of the table to the end of the current page, reverse it, take a top of the page size, then reverse again.
- mgkimsal 15y ago
  Yes, that was one of the proposed solutions. Again, as I said in one of my replies, this was in mid 2004 - we were using SQL Server 2000, not anything higher, because it wasn't available to us yet.
yread 15y ago
I am also angry that stuff like pagination isn't done in some standard way (pg, oracle, mysql sql server are doing it differently, sybase doesn't even have it). BUT! Dude, saying that sql server can't do something when a simple search gives you the right solution? The SQL standard is missing couple more features I think, like identity/sequences (I even had to use different code in .NET to get the newly inserted id), upserts, ... Writing cross-database software isn't even worth it. And I say that as a developer of a medium sized project supporting 4 databases. It would have been much better if we just decided to use one and use all the features properly
- manub22 15y ago
  You can do pagination by using the standard ROW_NUMBER() function and using CTEs, check my blog post for the same: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/ http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored... Also in SQL 2012 now you can use the offset-fetch clause to implement paging, check this: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/ http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...
deleted 15y ago
[deleted]
mgkimsal 15y ago
ACK - I can't edit. FWIW, this was in the year 2004, not 2012. And... to the extent that people will say "it's got it now"... this the same situation people have with mysql - spouting off about stuff that it didn't do 10 years ago, and don't use it (or postgres from X year ago, etc). Once you hit some rather stupid/useless limitation, it tends to stick with you and influence your choices in the future. I've used SQL Server on projects since then, but it's never been my choice to start a project with it. Clients have used it, and I've used it because that's what they had opted for, but it wasn't my choice or suggestion. As someone else noted, connecting to it from outside the world of Windows/.Net is a bit of a pain. The PHP connector situation is weird - it's changed in 5.3, and you seem to have to download a separate connector from MS directly. It'd be super swell if they'd just open source that connector so it could be distributed around (or provide a binary and license terms so at least WAMP-stacks could distribute it more easily).

redstripe 15y ago

OFFSET/LIMIT is coming: http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110%29.aspx http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110... GROUP_CONCAT still needs to be done with the FOR XML PATH hack

josephcooney 15y ago

You'll be happy then. I think they added OFFSET in SQL 2012. http://sqlwithmanoj.wordpress.com/2011/07/28/tsql-denali-new-feature-offset-fetch-clause-for-pagingpagination/ http://sqlwithmanoj.wordpress.com/2011/07/28/tsql-denali-new...