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?
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.
answered Nov 18, 2009 at 11:43 AM
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.
answered Nov 18, 2009 at 12:08 PM
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!
answered Nov 18, 2009 at 04:32 PM
Kev Riley ♦♦
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....
answered Feb 02, 2010 at 03:01 AM