Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Cursor Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 10:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:51 AM
Points: 6,600, Visits: 8,900
HoustonFirefox (7/6/2010)
I personally hate cursors and try not to use them if at all possible. In keeping with our TSQL roots I submit:

DECLARE @Database VARCHAR(255) 
DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))

-- Populate the in-memory table @DBList with all of the database names
insert @DBList
Select [name] , 'N' from master..sysdatabases
where [name] NOT IN( 'model','master','tempdb','msdb')

--select * from @DBList -- DEBUG: Run this to prove population was successful

-- Grab the first DB name from our in-memory table
While EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)
BEGIN
-- Get the DB Name into the @Database variable
Set @Database = (Select top 1 DBName from @DBList where Processed='N' order by DBName)
-- Do whatever awesome stuff with the database...
print @Database
-- Update the Processed flag to skip this DB on the next WHILE pass. Ever-decreasing list
Update @DBList set Processed='Y' where DBName = @Database
END

Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!

Hope this helps

This while loop can be worse than a cursor. What you should be trying to avoid is not cursors, but all looping mechanisms - you need to avoid the WHILE statement compeletely.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #948013
Posted Tuesday, July 6, 2010 11:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
j-1064772 (7/6/2010)
why not


WHILE (@@FETCH_STATUS = 0)




You could certainly do it that way... I just like to avoid the extra FETCH that requires.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #948049
Posted Wednesday, July 7, 2010 7:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Saturday, September 13, 2014 1:04 PM
Points: 4,356, Visits: 6,186
Cursors are appropriate for some classes of problems - and metadata operations can be one of them. Use the correct tool for the job.

I am with Jeff that you should not make every table have the same fill factor (although making them all something other than 0 is often an improvement). Best is to analyze fragmentation rates and adjust index fill factors appropriately based on that.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #948550
Posted Friday, November 4, 2011 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:19 PM
Points: 8, Visits: 38
This is admittedly a niggling question, but in Jeff's sample cursor, he used:

DECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY

If you want the cursor to be FORWARD_ONLY READ_ONLY, why wouldn't you just declare it FAST_FORWARD? What are the intrinsic differences that I'm missing?

Thanks,
~ J
Post #1200755
Posted Friday, November 4, 2011 11:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
You could, indeed. But way back when, someone suggested that FAST FORWARD isn't as fast as naming the options separately. I did a test back then and they were right. I don't know if it's changed with SPs, revision changes, etc, etc.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1200990
Posted Monday, May 5, 2014 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:02 PM
Points: 3, Visits: 27
Can you believe it, this is the first DRY cursor example I have ever seen. Repeating all of the parameters in the canonical dual FETCH statements was driving me nuts and I couldn't figure out why everyone was doing it that way.

(Not that I would ever use a cursor... )

Thanks Jeff!
David
Post #1567695
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse