x

I'm using a cursor in production code - does that make me a bad person?

Just wanted to get your points of view.

I need to disable foreign keys that point to a particular set of tables while those tables are modified (dropped & recreated) and then have their data re-inserted (it is stored in a separate table first).

I am using a cursor to iterate through the foreign keys which point to any of the tables in question, and disable them. I then use another cursor to re-enable them after the operation is complete.

I need to do this at run-time, because the code can run on anyone's database, so I can't avoid having to deal with foreign keys which could potentially be system-named constraints.

In this instance, i chose to use a cursor because I think the performance differences will be very minimal (if noticeable at all), and it actually makes the code more readable for generating and executing the DDL that I need.

What do you think?

more ▼

asked Nov 18, 2009 at 11:25 AM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 for the title. Beautiful!
Nov 18, 2009 at 12:00 PM David Wimbush
I am tempted to say yes you are, but i don't. :) Sometimes a cursor i faster or doesn't have any negative effect on performance and is easier to implement.
Nov 18, 2009 at 12:07 PM Håkan Winther
That is the funniest title I have seen on this site yet.
Nov 18, 2009 at 01:49 PM TimothyAWiseman
Yes, you are evil and must be destroyed !! (J/K)
Nov 18, 2009 at 11:07 PM Bob Hovious
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

This is one of those instances where I would think a cursor would be appropriate. Another option is to create the script(s) to drop the FK's and reapply them after and use sqlcmd to call the scripts that you have saved off in a file.

more ▼

answered Nov 18, 2009 at 11:43 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

I'm not sure the file one would fly for me - basically this is in a schema comparison tool - and one of the output modes is in script - so it has to be a 100% T-SQL approach...
Nov 18, 2009 at 12:12 PM Matt Whitfield ♦♦
I tend to find while loops with table variables to be more readable, but that is largely a matter of taste. I think using something like a cursor in this case is certainly understandable and would be very hard to avoid.
Nov 18, 2009 at 01:50 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

I second Jack's answer. There are a few things where there just doesn't seem to be a set-based way. More on the DDL side than DML. I just checked and there's definitely no WHERE clause in the ALTER TABLE ... DROP CONSTRAINT syntax.

Hmmm. It would be nice if you could do DDL on objects by INNER JOINing to a SELECT that returns the names or IDs of the objects to act on. It would make my nightly data warehouse rebuild job a lot less brittle.

more ▼

answered Nov 18, 2009 at 12:08 PM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

I certainly could have done it that way, by selecting into a variable and building up the statement as needed - but I didn't for two reasons - 1) The limits for strings in SQL Server 2000 and 2) The readability of the cursor...
Nov 18, 2009 at 12:10 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I think a lot of the 'bad' things about cursors is the way that some people use them incorrectly, when a more set-based solution is both 'better' and faster. Often procedural thinking programmers fall into the trap that they must deal with data one row at a time, rather than taking the whole data set as one. It's no-ones fault, just a different mindset.

So seconding and thirding (is that a word?) what has already been said here, there's nothing wrong with a cursor based solution if it's right for the job, and you go into it with eyes wide open. Have you ever looked at some of the system procs that come with SQL Server - riddled with cursors!

more ▼

answered Nov 18, 2009 at 04:32 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

cursor is most expensive as we know already. in most case we use cursor for looping only...

we can do the same looping using table variable.

let me describe.

-use table -insert those records to table ( SELECT statement of Cusrsor) -count the number or row. -take while loop till count -same process logic in while loop.

i am using this as an alternative solution to cursor.

hope this will help....

more ▼

answered Feb 02, 2010 at 03:01 AM

pxt_ce gravatar image

pxt_ce
27 1 1 1

You might want to re-look at that, because a fast forward read only cursor isn't any good for large scale data manipulation, but it will be faster than a while loop
Feb 02, 2010 at 05:41 AM Matt Whitfield ♦♦
this is new thing to me.. need to verify on screen.. thanks for info. in that case what should be prefered? FastFwd Cur OR While loop?
Feb 03, 2010 at 09:20 AM pxt_ce
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x246
x58
x33

asked: Nov 18, 2009 at 11:25 AM

Seen: 2330 times

Last Updated: Nov 18, 2009 at 01:48 PM