Stefan Krzywicki (9/17/2014)
SQLRNNR (9/17/2014)
Roy Ernest (9/17/2014)
I got someone asking if Cursor will work fast on memory based objects.It felt like the OP would upgrade to SQL 2014 so that he/she can make the cursor run faster.
It could. It could also be just as bad. Boils down to what objects are involved and how complex the cursor might be.
In the end, a set based would most likely be better for them. Sell them on the idea that 2014 has a much improved cardinality estimator that can make a lot of queries run faster by itself.:-D
I wrote a cursor this week. I needed to run sp_estimate_data_compression_savings against every table in the database. I probably could have opened the proc & figured out what tables it was querying and used that to write a set based query and I might in the future, but this works well enough for now.
I wouldn't bother to be honest. Firstly because that proc has multiple cursors in it anyway, second because it does stuff like
create table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B]([dummyDBA05385A6FF40F888204D05C7D56D2B] [int]);
It works by taking a sample of the table you specify, creating a temp table in, adding indexes, inserting the sample data and then compressing it.
Nothing wrong with cursors/loops where appropriate, and when creating and altering objects is usually such a place
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability