Printable View
I went to a company for an interview and to my surprise they were still using cursors in sql server.
Now i'm not the best sql programmer around and i think i have skipped the cursors part(due to younger age) but i think some of you guys can give some info on this one.
Are cursors needed nowadays and what is a job that only a cursor can do(as i was told when i asked why they use them(they didn't elaborate).
Thanks.
Cursors are typically used for processing rowsets, i.e. a set of records. For e.g. if I wanted to write code to update the salary of all employees of department X by 10%, I would use a cursor. The cursor would initialize a rowset containing records of all employees of department X and in each iteration the salary field would be incremented by 10%.
This is a very simple example of updating a batch of records. You can do the above by using a simple UPDATE query. However there are more complex operations for which cursors was an 'easy' way out.
.
Sometimes I need to emulate what a user is doing on-line for hundreds of claims at once. The on-line system does most of the work in stored procedures. I’ll take a table of claim numbers and run them through cursor logic executing four or five stored procedures. I suppose I could set up a loop instead but that is one case I use them.
I use a cursor in a sp that creates a trigger on a specific table. If loops through the columns of a table building the trigger code. I don't know of another way to do it besides using VB or C#.
Code:OPEN @ColumnList
FETCH NEXT FROM @ColumnList
INTO @ColumnName, @DataType, @PrimaryKey
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DataType <> 'timestamp' AND @DataType <> 'ntext' AND @DataType <> 'text' AND @DataType <> 'image'
SELECT @Fields = @Fields + @ColumnName + N', ' + CHAR(13)
IF @PrimaryKey = 1
SELECT @PKWhere = @PKWhere + N' CT.' + @ColumnName + N' = D.' + @ColumnName + N' AND '
FETCH NEXT FROM @ColumnList
INTO @ColumnName, @DataType, @PrimaryKey
END
Yes I still do if the situation requires it.
I use one duiring a database migration I'm doing to drop all SPs in the database.
There is no harm in using cursors where it is really required.
You should use cursors only where you need to traverse row by row performing some operation and you can't think of any set based way to accomplish the task.
So if i understand correctly u use cursors as a replacement of a loop (CASE ) situation or some complex stuff that has to do with more core t-sql (Garrys's example).
Also can't CTE be used for fetching some amount of rows and manipulate them.Or i'm confusing things?
Thanks.
Yes you are right.Quote:
Originally Posted by sapator
CTE is just like a temporary table on the fly. If you can get your task done in a set-based way, then that should always be the preferred way as opposed to cursors (or while loops), whether you use CTE or not.
Personally I avoid cursors like the plague and can think of almost nothing that requires them. The query you write to create the cursor can always be expressed as a where clause so the deciding factor is not the nature of the data set but rather the operation you wish to carry out.
The obvious operation I can think of that cannot be applied directly to a dataset is a stored procedure call. If I want to call an SP based on a bunch of different records I have to build a cursor that returns those records and then traverse it, passing in the relevant values to the SP as I go. I'm not aware of a way of achieving this without some sort of loop.
I'll also sometimes use a cursor if I'm updating data manually because it can be easier to follow what I'm doing.
I'd sooner chew my own arm off than write a cursor into an application, though, if I can possibly use a set based aproach.
Thanks for the valuable info everyone.
So should i tell them (called for a second interview on Monday) that there are other better was to handle common situations or keep my mouth shut...Mmmm.
I think i better ask them when they use cursors and inform you guys.
If you really want to impress, I guess you should discuss cursors objectively, i.e. explain what a cursor is and how it works, and then explain the pros and cons of it. Avoid taking a judgemental view such as "Cursors are bad!". There are many constraints when you are working on a real life project, and sometimes you have no choice (at least for the immediate future) than to use something which is not necessarily the best option.
If you are given a situation and then asked if you would use cursors, you could then tell whether you would use cursors or not, justifying the answer you give with the merits.
If this is a good company, they wouldn't be interested to know if you like cursors or hate them. They would like to know if you 'understand' them.
.
Ok got you.
Sometimes i can't keep my mouth shut if i see something completely wrong but i'll try :D
It's an insurance company i think i've worked with a couple of them before and i don't see any real inventions or rigid thinking on them.It's (they, insurance companies in Greece in general are) a good company with poor IT understanding and usage, as most of them.I'll inform if i see something out of the ordinary.
Always remember, as a general philosophy as well as a sure way of showing off your maturity: There is no such thing as right or wrong. You choose the best you can under the circumstances. Sometimes circumstances allow you to make a different choice, sometimes not. So if they say they tried something and it failed, don't ever say it was the wrong thing to try.Quote:
Originally Posted by sapator
Over the years as I have been exposed to how decisions are made at the managerial levels, I have come to understand that there is a reason a particular decision is taken. Without understanding the reason someone would call the decision a mistake. But it is subjective criticism, not objective.
If you do want to call something as wrong, a more polite way of expressing it could be, "Yes, that's a good choice. But are there any alternatives?" or "Can we improve something in this approach?"
.
Here are a few situations where I normally use cursors:
1. Situations where using a set based query is not possible. I usually use cursors where there is a big logic to process some records. Normally database queries are not fit for these situations because they are inherently not designed for such things. A more specialized language like vb.net etc. is apt for these things. Yet you will have to code in database to keep things simple, usually inside stored procedures or functions.
2. Situations where performance is immaterial and you have no easy set based way to work on records. Usually when I make changes to an application and need to send out a one time database update query to update all databases on various platforms like test, intermediate and production. There might be better ways, but you don't have time to think much since it is meant to be executed only once on each database. So performance of the query is no issue.
Here is one example where I used it recently
3. Situations where using a set based query is possible but will be very complex, while using a cursor will result in easy to understand code. And you know that that stored procedure/function will be used in rarest cases. So in such situations we give preference to code maintenance rather than performance (unless the performance is really pathetic).
Pradeep as a show your code i view all the go statements.
This is another thing that puzzles me.If they are necessary or he stall down the execution.
Should i open another topic or this is a simple answer?
Are you talking about what GO statement does in t-sql query?
GO statement causes all the statements above it to be executed as one batch of tasks. Statements below it will behave as if starting a new sql file. So if you want to execute a series of queries, you don't need to put your queries in different files and execute them one by one. You can put them all in one file and put that GO in between.
Usually not putting GO will also have the same effect.
Yes what i'm afraid of is if some GO statement backfire then the others will execute and binding this with a use of transactions you may have a problem.
But i see your point on "storing" statements in one file.I think thought, it must be handled with caution and no task interacts with the next one.Unless transactions can surround all the go's(I'm not aware of that.) or use triggers but it gets complex.
Hmm.
It appears i was wrong and go's cannot be used in sp's below the initial statements.
I think my mind is off today.