Hi, Yesterday I went interview and asked this questions which I did not know.

1) If you have 100,000 records and you want to delete 95,000 at a time and keep only 5 thousand. But in Local memory is not having enough space for 95,000 records. What do you do on this scenario? How do you delete without any problem at a time?

2) In Ebay I need a data for customers in Chicago who can did bid on it? I dont have access to their database. How I get the information?

3) With DTS Pakaging I can store Tables in Excel and Back to SQL. But once I copied Tables to Excel I want to modify small info. How you do that without Editing the Excel File?

1) memory doesn't matter, but it could be slow. You could copy the 5,000 good records to a new/temp table, drop the old table, rename the temporary table to the original name. But this has all sorts of issues if there is referential integrity on the original table etc.

You could also BCP out the 5,000 good records, truncate the table, BCP the 5,000 good records back in - same problems if there is referential integrity involved.

2) I would write a spider that trawled the eBay site and sccraped the data form the HTML.

Actually, [a] I would work with eBay to obtain, and pay for the data, else[b] I wouldn't work for the company!

3) The DTS drive for import from Excel is completely PANTS. I'd find a different way to sovle the ACTUAL business need.

4) You do pretty much everything you can to NOT use cursors. If YOU don't know what a cursor is then you need to learn, 'coz although [for performance reasons] you are better off NOT using them, if you are a SQL Bod then you OUGHT to know all about them!

Performance is never not an issue for me. Either the DB will be "bigger one day" or the server that it is running on has more databases on it, or will have, and programming inefficiently on one DB is going to steal CPU cycles from the others.

Not to mention concurrency and a range of other issues.

"To me it's a whole lot quicker to code and quite elegant."

Doesn't work for me. OK, so I have learnt how to think "set based", and when people first start linear programming is the way they think.

But the biggest killer for me is having to describe the column list three times - that's a recipe for disaster

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = 'UT'
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
... processing ...
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

I can't see how any of the objections you guys made effects me in my situation. I agree that in most situations it is not the best thing to use. But I am writing a one-off data conversition at the moment. The code will run once and then never again.I am often running into all sorts of situations where cursors seem best. Like when I need to go through a whole table and depending on one of 7 possible values I need to: set a column value in the same table, set a column value in a different table, or insert a records in a different table.I cant see why cursors are a bad idea in this situation and I cant see a better/faster option either.Any takers?

I like to use this analogy a lot: you have a cup of coffee (or tea), and you want to put sugar in it. You can use tweezers, or a spoon. Cursors are tweezers.

Seriously. Suppose you will only drink one cup of coffee in your life, would you still use tweezers to sweeten it?

Any time you try to justify using cursors for data operations, you are, in effect, justifying tweezers over a spoon. If this sounds absurd, that's because it is, but it's still a valid point. SQL is meant to work on sets of data, not row-by-row; you actually work AGAINST the language to do so.

More than just being an ineffective way to do something, cursors are a bad habit to get into, and like drugs or smoking, best avoided by not starting in the first place. The fact that you are "running into all sorts of situations where cursors seem best" means that you're not considering those situations carefully enough or thinking them through.

quote:Like when I need to go through a whole table and depending on one of 7 possible values I need to: set a column value in the same table, set a column value in a different table, or insert a records in a different table.

This is common disconnect experienced by people who are new to set-based operations. The point is, you don't HAVE TO go through the ENTIRE table. You write a query or operation to affect ONLY the rows that operation should affect, and the rest of the data is ignored...as if it never existed. To further illustrate your example, here it is broken into 3 set-based operations:

UPDATE myTable SET col1='some value' WHERE col2 IN (1, 2, 3)

UPDATE B SET col3='some other value' FROM myTable A INNER JOIN myOtherTable B ON A.ID=B.IDWHERE A.col2 IN (4, 5, 6)

This is the second hallmark of SQL: it is a declarative language, you tell the computer WHAT you want to do, not how to do it. No need for loops, cursors, IF or WHILE tests. The WHERE clause in each of these statements limits the action to only those rows that meet that condition.

Now that is a good answer. I will have to start changing my thinking. To me, set based statements require more knowladge, understanding & confidence with SQL. So I've got some learning to do!Seeing those JOINs made me think of another reason why I thought it was a good idea to use cursors: the performance hit that is caused by joins. I assume that is not a valid concern...

No. In fact, JOINs can use indexes to their best advantage and reduce overhead substantially. Oftentimes cursors cannot or do not use indexes at all. Proper indexing could even allow you to query data without accessing the table at all. This can be accomplished with a covering index (an index that contains all of the data the query needs) or via index intersection...the optimizer uses several indexes to populate the data needed for the query. Depending on the type of cursor used, it will always hit the table, and do it only one row at a time.

An analogy would be going to the library to find out the author of a book. The card catalog would contain that information, so you don't have to browse the shelves. The card may even have the number of pages in the book, if you wanted that information. This is an example of a covering index. Having the title and author of the book from one catalog/index lets you look up the same book in another, the Subject list for example. This is an example of index intersection. At no time did you have to hit the shelves to find the book in order to get this information. A cursor, on the other hand, would repeatedly go out to the shelves for each book you wanted, and only pull one book at a time.

Whenever you start thinking about using cursors, consider these analogies (and others) that replicate a physical action to a cursor action. If the physical action does not make sense, then it's probably the wrong way to implement it in SQL.

The library is an extremely useful example of database theory and practice (libraries are the original databases) There are a lot more parallels between them than you would imagine. I remember thinking about them for a good couple of hours, and STILL didn't get them all. If you consider why libraries are set up the way they are, and how they evolved into that structure, you'll get some extraordinary insight into how databases should work, and often do work.