:::: MENU ::::

Posts Categorized / Cursors

  • Dec 20 / 2012
  • 0
Cursors, dbDigger, T-SQL Scripts, User Defined Stored Procedures

Drop specific tables through a stored procedure by using Cursor

Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo

/*   
 Demo would drop the required tables  
 by using a cursor in following steps  
 1. Create tables for drop demo  
 2. Verify the created tables  
 3. Create USP  
 4. Create cursor in USP to pull and execute the drop commands  
 5. Execute the procedure  
 6. Verify that tables do not exist now  
 */  
 -- Create Tables  
 create table rep1 (id smallint)  
 GO  
 create table rep2 (id smallint)  
 GO  
 create table rep3 (id smallint)  
 GO  
 create table rep4 (id smallint)  
 GO  
 -- verify the created tables   
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

 -------------------------------------------------------------  
 -- Create USP and a cursor in USP  
 If exists (select name from sys.objects   
 where type = 'P' and name = 'USP_DropRepTables')  
 DROP PROCEDURE USP_DropRepTables  
 GO  
 CREATE PROCEDURE USP_DropRepTables  
 AS  
 -- Declare variable for SQL command  
 DECLARE @dropQuery NVARCHAR(400)  
 -- Declare and populate cursor with required tables  
 DECLARE DropLoopCursor CURSOR FOR  
 SELECT 'DROP Table '+name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 -- Open cursor  
 OPEN DropLoopCursor  
 -- Fetch record from cursor  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 -- Configure while loop in cursor  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 -- Execute Dynamical SQL  
 execute (@dropQuery)  
 -- Fetch next recod  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 END  
 -- Close and deallocate the cursor  
 CLOSE DropLoopCursor  
 DEALLOCATE DropLoopCursor  
 ------------------------------------------------------------  
 -- End the procedure  
 GO  

 -- Execute the procedure  
 EXECUTE USP_DropRepTables  
 GO  

 -- Verify that tables do not exist now  
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.

  • Jan 07 / 2010
  • 0
Cursors, dbDigger, T-SQL Interview Questions

Get count of the number of rows in the cursor

Question:

If you open a cursor, which of these will allow you to get a count of the number of rows in the cursor? (select all that apply)

Answer:

  • sp_cursor_list
  • sp_decribe_cursor
  • sp_describe_cursor_columns
  • @@cursor_rows

Explanation: All of these items will allow you to determine the number of rows in some cursors, subject to restrictions. The @@Cursor_rows requires a non-dynamic cursor and the sp_describe_cursor_columns requires you to count the number of rows returned.

The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Aug 06 / 2008
  • 1
Cursors, DBA Interview questions, dbDigger, Performance Tunning and Optimization

Some thing good about cursors performance

Mostly cursors are notorious for performance degradation and resource consumption. And are used as last option always. But there is situation when cursors may help you to boost the performance. If your application or procedure requires the repeated use of a set of records,it is faster to create a cursor once and reuse it several times than to repeatedly query the database. But do not forget to properly close the cursor at end and also deallocate the resources.
Follow these steps to create, use, and close a database cursor:

  • Create the cursor
  • Open the cursor for use within the procedure or application
  • Fetch a record’s data one row at a time until you have reached the end of the cursor’s records
  • Close the cursor when you are finished with it
  • Deallocate the cursor to completely discard it

Consider the following example of cursor created for pubs..authors

 
DECLARE @fName VARCHAR(40)  
 DECLARE @lName VARCHAR(40)  
 DECLARE @city VARCHAR(40)  
 DECLARE OAKLAND CURSOR FOR  
 SELECT au_lname, au_fname, city  
 FROM pubs..authors  
 OPEN OAKLAND  
 FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city  
 IF (@city = 'OAKLAND')  
 BEGIN  
 PRINT @city  
 END  
 END  
 GO  
 CLOSE OAKLAND  
 DEALLOCATE OAKLAND  
 GO  

You may find more detail use of cursors in online books.

  • Jul 29 / 2008
  • 0
Cursors, dbDigger, T-SQL Interview Questions

Server-side cursors

Question: Of course, we all know that server-side cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.
So, suppose you find yourself in a situation where you do need a cursor, and you also need to change the data retrieved by the cursor – how can you assure maximum performance?

Answer: Do not use FOR UPDATE in the cursor declaration, and use WHERE keycolumn = @keyvalue in the update statement

Explanation: Though not documented by Microosoft, extensive testing has shown that reading a STATIC cursor with no FOR UPDATE option and using the primary key to update the row just read is faster than specifying a FOR UPDATE option (either with or without a column list) and using the WHERE CURRENT OF clause in the UPDATE statement.

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

  • Jul 09 / 2008
  • 0
Cursors, DBA Interview questions, dbDigger, Performance Tunning and Optimization

Cursor performance

Question: Of course, we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor – which cursor option must be specified in order to assure maximum performance?

Answer: STATIC

Explanation: The default options are actually the slowest, since they create a dynamic cursor. In spite of its name, FAST_FORWARD does not result in the fastest performance; STATIC has been shown to be faster. Specifying FORWARD_ONLY or READ_ONLY on a static cursor will not affect performance.

Ref: DECLARE CURSOR

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Consult us to explore the Databases. Contact us