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 «««56789»»»

Cursors for T-SQL Beginners Expand / Collapse
Author
Message
Posted Wednesday, January 7, 2009 9:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Again... the reason I did what I did was I couldn't come up with a way to run
DBCC CHECKPRIMARYFILE (N''' + @filename + ''',2)

Which gets the actual name of the database from the database itself.

I had found in my testing that 90% of the databases followed our naming conventions, but the other 10% (400) did not. So I needed to probe the mdf file to find out what the actual database name was. New databases all follow the naming convention. The older databases are used by our customers as well though and we had a very small time window to do this (approx 5 hours, including copying almost 2 TB of Databases to their new homes).

Tracing how SSMS 2K5 does it showed me DBCC CHECKPRIMARYFILE, which only appears to work on an mdf that isn't attached to a SQL server, it's also not documented in BOL (yet it's what MS uses).

Since the DBCC command returns a small result set for a single file at a time, I couldn't come up with a good way to do it other than to run DBCC CHECKPRIMARYFILE for each database mdf file, one at a time.

All told, the attach routine took less than 10 minutes to attach 1000 databases per instance to each instance. The rest of the time was spent copying mdf files to their new homes.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #631616
Posted Friday, May 27, 2011 2:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:51 PM
Points: 15, Visits: 331
In all my years of writing database code, the only times I have ever opted to use cursors is when executing a parameterised statement across different servers or databases.

I wouldn't like to try and count the number of times that I have stared into space for minutes at a time trying to work out the required complexities of the joins needed to accomodate a particular task instead of resorting to rbar, but isn't this the point of what DBA's and Database programmers do? We are specialised because the platform requires a specialised skill set and if we don't use it then we are just wasting what the RDBMS can do and, quite frankly, being lazy.
For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!

Rant over ;)
Post #1116067
Posted Friday, May 27, 2011 2:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
Jeff Moden (1/2/2009)
battelofhalfwits (1/1/2009)
Wow...

Two things

1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.


Not true... if you use Forward Only, Read Only (or sometimes Static), the cursor is just as "effecient" as the WHILE loop... and make no doubt about it, neither is effecient. If you use a WHILE loop, you've not done any better than using a cursor. ;)


I use a couple of cursors in my code...
One is for retrieving messages from a service broker queue (multiple messages per conversation handle, retrieved into a temp table and then the cursor processes each retrieved message one by one as they have to be - each message will take a different process path depending on what it contains):

-- Declare cursor

DECLARE MessageCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT ConversationHandle
,MessageTypeName
,MessageBody
FROM @ReceiveTable
ORDER BY QueuingOrder;

-- and a little later in the code...

WAITFOR(
RECEIVE
[queuing_order]
,[conversation_handle]
,[message_type_name]
,CAST([message_body] AS NVARCHAR(MAX))
FROM [EventDetailsTargetQueue]
INTO @ReceiveTable
), TIMEOUT 5000;

-- and process using the cursor --

The other is in a utility procedure that returns record counts and a health check (are there sufficient records) for all the tables involved in a loading process - in this case a table contains the names and minimum acceptable counts for all the loading tables, I use a very simple cursor (SELECT TableName, MinimumRecordCount FROM HealthCheckTables) to retrieve these and for each one I construct a SQL statement to retrieve the count from the table and execute the statement, storing the results in a temp table. This is then used to report the counts, and to throw an error where insufficient records are found in any of the tables (this procedure is used in the load process to halt processing before the live data is affected when we haven't loaded a healthy amount of data)

I do loop through a temp table in another component of my loading process to achieve almost the same thing (disabling/enabling all foreign keys on those same loading tables), driven by the same source table and again constructing a SQL statement for each key and executing it.

i.e. this gets all the related foreign keys:
            SELECT OBJECT_NAME(s.constid) AS ConstraintName
,'[' + sch.[name] + '].[' + OBJECT_NAME(s.fkeyid) + ']' AS TableName
INTO #Const_Table
FROM sysforeignkeys s
INNER JOIN HealthCheckTables h
ON h.TableName = OBJECT_NAME (s.rkeyid)
INNER JOIN sys.tables t
ON t.[name] = OBJECT_NAME(s.fkeyid)
INNER JOIN sys.schemas sch
ON sch.[schema_id] = t.[schema_id]

...and for each one I have to construct an "ALTER TABLE abc {NOCHECK|WITH CHECK CHECK} CONSTRAINT xyz" statement and execute it.

These last two, both could use a cursor, or both could use a loop, but essentially they both have to be REBAR processes as far as I can tell, and similarly for the service broker processing.

Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation!

Dave.
Post #1116069
Posted Friday, May 27, 2011 3:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:51 AM
Points: 328, Visits: 2,001
dave.farmer (5/27/2011)

Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation!



I think the frothing at the mouth against cursors does need some qualification.

T-SQL can fall into two general categories; Process (DDL) and Data (DML) based.

Process based could involve using a process table to determine which stored procedures to call or perhaps working through a file list stored in a table to dynamically bulk load files. Or, as you mentioned, going through a series of tables and modifying the indexes/keys.
These process based operations benefit from cursors. They could be done with out cursors but any other method method wouldn't be as elegant (IMO).

Data based operations, i.e. performing inserts, updates etc... should not be done using a cursor
Post #1116080
Posted Friday, May 27, 2011 3:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:11 AM
Points: 17, Visits: 126
Anyone who knows what happens when a phonenumber is NULL?

For 2 reasons I would add an extra filter
1) don't select what you don't need as soon as possible, less records passing your filtering will also speed up your process
2) working with NULL-values can give surprising effects, so you have to be aware and check ISNULL-function (or as I did here filter them out)

DECLARE @AllPhones VARCHAR(1000)

SET @AllPhones = ''
SELECT @AllPhones = @AllPhones
+ CASE WHEN P.ListThisNumber = 1 THEN P.PhoneNumber ELSE '***********'END
+ ' & '
FROM dbo.tblPhone P
WHERE codUser = 1
[color=#FF0000]AND P.PhoneNumber IS NOT NULL[/color]
SELECT @AllPhonesGO
Post #1116095
Posted Friday, May 27, 2011 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:38 AM
Points: 16, Visits: 122
A huge and common mistake I have seen is when a trigger is written from the perspective that only one row is affected.
For example:

Select @someVar = SomeValue
From inserted

If the statement above is in a trigger with out some sort of looping around it and the insert/update statement affects more than one row then you only select one value from one of the many rows and skipped all of the others. SQL Server won't error out if you try to jam more than one value into the variable. (Oracle would have thrown an error)
Post #1116188
Posted Friday, May 27, 2011 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
Whenever possible, I use set-based solutions. But it has occurred in the past where I simply couldn't think one.

For example, this is my answer to a question on this site (no-one was able to produce a set-based alternative) : -

CREATE TABLE #SPWithVarchar
(SPName VARCHAR(MAX), --Display the stored procedure name
LinePos INT, --Display the line number to where the varchar was found.
TextPart VARCHAR(MAX)) --Display the code to where the varchar was found

--First temporary table, required to get the names of the stored-procedures
CREATE TABLE #TestTemp (Code VARCHAR(MAX), sp_name VARCHAR(MAX))
INSERT INTO #TestTemp(Code, sp_name)
--sp_helptext "hides" an ugly while loop, so a set-based version would be
--preferred. I can't think of a way of getting the line numbers without it
--unfortunately.
SELECT 'EXEC sp_helptext ''' + o.name + '''', '''' + o.name + ''''
FROM sysobjects o
WHERE o.xtype = 'P' AND o.category = 0
GROUP BY o.name

DECLARE @code VARCHAR(MAX), @sp_name VARCHAR(MAX)
DECLARE crap CURSOR FOR
SELECT code, sp_name FROM #TestTemp

OPEN crap
FETCH NEXT FROM crap INTO @code, @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
--Second temporary table, holds the results of "sp_helptext" for each individual
--stored-procedure. Uses an identity column to determine the line number of
--the stored-procedure.
--We then search this table before inserting into your #SPWithVarchar table.
CREATE TABLE #TestTemp2 (Number INT IDENTITY,Line VARCHAR(MAX), sp_name VARCHAR(MAX))
SET @code = 'INSERT INTO #TestTemp2(Line) ' + @code
--Insert results of "sp_help" into second temp table
EXECUTE ('' + @code + '')
--Updates second temporary table with the stored-procedure name.
UPDATE #TestTemp2 SET sp_name = @sp_name
--Inserts the lines from #TestTemp2 where "varchar" appears into #SPWithVarchar.
INSERT INTO #SPWithVarchar(spname, linepos, TextPart)
SELECT sp_name, number, line FROM #TestTemp2 WHERE line LIKE '%varchar%'
--Drop #TestTemp2 so it's ready to be used for the next stored-procedure.
DROP TABLE #TestTemp2
FETCH NEXT FROM crap INTO @code, @sp_name
END

--Clean up
CLOSE crap
DEALLOCATE crap
DROP TABLE #TestTemp

--Display the results
SELECT * FROM #SPWithVarchar
--DROP TABLE #SPWithVarchar




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1116272
Posted Friday, May 27, 2011 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:09 AM
Points: 1, Visits: 6
As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners.

A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.

Post #1116290
Posted Friday, May 27, 2011 8:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:51 PM
Points: 15, Visits: 331
something like this would get you started on a set based solution:

with sp_lines(ObjectId, SPName, CRPos, [LineNo])
as (
select
o.id
,o.name
,t.n as CRPos
,ROW_NUMBER() over (partition by o.name order by t.n) as [LineNo]
from sys.syscomments c
inner join sys.sysobjects o on c.id = o.id
left join Tally t on substring(c.text, t.n, 1) = char(10)
where c.text like '%varchar%'
and o.xtype = 'P'
)

select
current_line.SPName
,substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) as LineText
--,isnull(previous_line.CRPos+1, 1) as LineStart
--,current_line.CRPos - isnull(previous_line.CRPos+1, 1) as Length
from sp_lines current_line
left join sp_lines previous_line on current_line.ObjectId = previous_line.ObjectId
and current_line.[LineNo] - 1 = previous_line.[LineNo]
inner join sys.syscomments c on current_line.ObjectId = c.id
where substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) like '%varchar%'
Post #1116303
Posted Friday, May 27, 2011 9:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 251, Visits: 537
I thought the article was well presented. I especially enjoyed the responses.
Post #1116334
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse