InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:56:39 GMT20http://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxYou 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.Fri, 04 Nov 2011 23:47:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxThis 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,~ JFri, 04 Nov 2011 10:30:53 GMTJPHKhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxCursors 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.Wed, 07 Jul 2010 07:57:40 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspx[quote][b]j-1064772 (7/6/2010)[/b][hr]why not [code="sql"]WHILE (@@FETCH_STATUS = 0)[/code][/quote]You could certainly do it that way... I just like to avoid the extra FETCH that requires.Tue, 06 Jul 2010 11:03:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspx[quote][b]HoustonFirefox (7/6/2010)[/b][hr][b]I personally hate cursors and try not to use them[/b] if at all possible. In keeping with our TSQL roots I submit:[code="sql"]DECLARE @Database VARCHAR(255) DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))-- Populate the in-memory table @DBList with all of the database namesinsert @DBListSelect [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 tableWhile EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)BEGIN-- Get the DB Name into the @Database variableSet @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 listUpdate @DBList set Processed='Y' where DBName = @DatabaseEND [/code]Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!Hope this helps ;-)[/quote]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.Tue, 06 Jul 2010 10:15:20 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxI personally hate cursors and try not to use them if at all possible. In keeping with our TSQL roots I submit:[code="sql"]DECLARE @Database VARCHAR(255) DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))-- Populate the in-memory table @DBList with all of the database namesinsert @DBListSelect [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 tableWhile EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)BEGIN-- Get the DB Name into the @Database variableSet @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 listUpdate @DBList set Processed='Y' where DBName = @DatabaseEND [/code]Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!Hope this helps ;-)Tue, 06 Jul 2010 09:50:07 GMTHoustonFirefoxhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspx[quote][b]Jeff Moden (7/5/2010)[/b][hr]... In the meantime, here's a simple cursor that reads a couple of values from a table and displays them. If you're in the GRID mode, it will end with an error. I've commented the code so you can see what each piece does.[code="sql"]USE AdventureWorks;GO--===== Declare some cursor related variablesDECLARE @EmployeeID INT, @Title NVARCHAR(50)--===== Declare the cursor using a SELECTDECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT EmployeeID, Title FROM HumanResources.Employee;--===== Open the cursor to begin using it. -- This is where a static cursor gets loaded -- into a temp table OPEN Employee_Cursor;--===== Start an infinite loop. We'll break out later... WHILE 1 = 1 BEGIN[/code][/quote]why not [code="sql"]WHILE (@@FETCH_STATUS = 0)[/code]Tue, 06 Jul 2010 08:55:01 GMTj-1064772http://www.sqlservercentral.com/Forums/Topic436805-65-1.aspx[quote][b]vedpsoni (7/5/2010)[/b][hr]Hello Sir I just wanna know that what is cursor is it datatype or object and how and what is use of cursor when we have its alternate and how a cursor can give us maltiple value likeempId salary101 10000105 5000[/quote]My recommendation is to lookup "cursors [SQL Server]" in Books Online (the help system that comes with SQL Server) because CURSORs are a big subject. In the meantime, here's a simple cursor that reads a couple of values from a table and displays them. If you're in the GRID mode, it will end with an error. I've commented the code so you can see what each piece does.[code="sql"]USE AdventureWorks;GO--===== Declare some cursor related variablesDECLARE @EmployeeID INT, @Title NVARCHAR(50)--===== Declare the cursor using a SELECTDECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT EmployeeID, Title FROM HumanResources.Employee;--===== Open the cursor to begin using it. -- This is where a static cursor gets loaded -- into a temp table OPEN Employee_Cursor;--===== Start an infinite loop. We'll break out later... WHILE 1 = 1 BEGIN --===== Read a row from the cursor FETCH NEXT FROM Employee_Cursor INTO @EmployeeID, @Title; --===== If the read above returns NO row, exit the loop -- because we're done IF @@FETCH_STATUS <> 0 BREAK; --===== If we're still here, then there was a row to be read. -- We can now process that row. SELECT @EmployeeID, @Title;--===== This marks the end of the While Loop which automatically continues -- up to here until we hit the "BREAK" in the code above. END;--======== Release any locks held open by the cursor and then drop the -- cursor structure. CLOSE Employee_Cursor;DEALLOCATE Employee_Cursor;GO[/code]So far as what to use a CURSOR for goes, the answer is almost always that you shouldn't use a CURSOR. I'll also tell you that writing a Temp Table or Table Variable to step through using a While Loop is nothing more than a poor man's CURSOR and should also be avoided 99.99% of the time.The only time I'll condone (never mind allow in my shop) is when you're trying to do something to all tables in a database and other object control related things. Even then, you can get away with concatenated commands rather than using a CURSOR.Don't be fooled by recursive CTEs or the use of things like sp_MSForEachTable... recursive CTEs are generally a form of hidden RBAR and sp_MSForEachTable is nothing more than a huge, very ugly CURSOR in the background.[quote]what is use of cursor when we have its alternate [/quote]The answer is, it allows people who don't know how to do high performance, set-based code to still get to their data and do some processing. CURSORs were originally meant to make it a bit easier to do something to, say, all tables in a database but others have used them to overcome their lack of set-based knowledge. Generally speaking, cursors should be avoided no matter what the cost because they are usually terrible for performance and resource usage. I've never put a CURSOR into production code and about the only time I use a While Loop is to step through file names during T-SQL imports of files. Even that isn't RBAR processing... it's a control loop to load thousands/millions of rows for each file in a set-based manner.Just to summarize and emphasize... if you use a CURSOR, While Loop, or any form of recursion to affect just one row at a time (RBAR), there's a very, very, high probability that you're doing it the wrong way whether you can think of a set-based method or not.Mon, 05 Jul 2010 09:13:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxHello Sir I just wanna know that what is cursor is it datatype or object and how and what is use of cursor when we have its alternate and how a cursor can give us maltiple value likeempId salary101 10000105 5000Mon, 05 Jul 2010 03:38:15 GMTvedpsonihttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxno worries - I find myself often trying to explain why a fill factor of 80% is such a waste of space on etl import tables, or even read only filegroups!Sat, 29 Dec 2007 08:09:41 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspx[quote]sorry Jeff I have to disagree, even if the clustered index is on an indentity column then the clustered index is actually the entire table, regardless of the index being defined on an int or bigint, so if the next couple of columns are varchars and the values( length of these) change during editing you may well get page splits if you cannot perform an inplace update, in this case a fill factor may help avoid splits.[/quote]Crud... you're right, Colin... I forgot about the eventuality of Updates like this because I'm so bloody entrenched in ETL processes where the data is imported and never changes... thank you for the correction.Fri, 28 Dec 2007 11:48:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxsorry Jeff I have to disagree, even if the clustered index is on an indentity column then the clustered index is actually the entire table, regardless of the index being defined on an int or bigint, so if the next couple of columns are varchars and the values( length of these) change during editing you may well get page splits if you cannot perform an inplace update, in this case a fill factor may help avoid splits.That said I find most applications of fill factors and padding are missguided at best - I usually challenge implementers of such code to prove and justify the action - they usually can't, which isn't too uncommon for many diverse changes/actions which affect sql server ( how urban myths arise ? )As to the original post you could change the cursor to a while ( which is just a cursor in disguise ) but the way sql handles transactions is different for a while and a cursor so you may find yourself digging yourself into a hole.Fri, 28 Dec 2007 02:56:35 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxI can't think of a way to do this without loops of some sort, either cursors or a while loop. Since changing the existing nested cursors to nested while loops, would just be an exercise in writing code for the sake of writing code, with no real benefit, I can't suggest anything on this.On the other hand, the reason this exact task isn't something that's easy to set up in a maintenance plan (for example), is because what this code does is pretty much a bad idea in the first place, as already pointed out in another comment.What I would suggest is find out why this code is in use in the first place, and then maybe we can help with a solution to the underlying problem. It exists to solve something from someone's point of view - and that something probably has a better, standard solution.Thu, 27 Dec 2007 13:13:07 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxMy first inclination about this job is to abandon it... no, I didn't say rewrite it... I do mean "abandon it". This will change the Fill Factor on all tables and that's an absolute Bozo-no-no. There are (should be) a large number of static definition tables that never/rarely change that should have a fill factor of 100 just for performance reasons. Also, any table that has a Primary Key on an IDENTITY column should also have a Fill Factor of 100 on that Primary Key because there's [i]no [/i]chance for interceding rows, so no interference on INSERTs/UPDATEs by the 100 fill factor but will increase the speed of SELECT's and decrease the storage requirements on the IDENTITY Primary Key... clustered or not.The basis of the code is a really bad idea...Thu, 27 Dec 2007 12:34:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic436805-65-1.aspxi have a job which is failing my lead wants me to Remove the cursors from the job and modify it any suggestion would be of great help please--DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT database_name FROM WHERE status='y'AND database_name NOT IN ('master','model','msdb','tempdb') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd)FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursorthanks Thu, 27 Dec 2007 12:15:55 GMTDreamsz