Hot answers tagged

25 votes
Accepted

Can two sessions create #temp tables with the same name?

SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp tables in their sessions with the same name, sql server will ...
  • 1,860
21 votes
Accepted

sp_cursoropen and parallelism

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML). When you specify ...
  • 73.8k
14 votes
Accepted

How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

A very common misconception about Databases can be dispelled with this: Database != File When you update a Row in a database, the underlying data file on disk isn't touched at all - at least ...
  • 6,983
13 votes

Can two sessions create #temp tables with the same name?

Yes, multiple applications will get their own copies of the #temp table. That is the point of using a #temp table, because each concurrent session has their own, isolated object. This has nothing to ...
12 votes
Accepted

Infinite loop in CURSOR

Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor. Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the ...
10 votes
Accepted

Keep track of time intensive stored procedure with prints

In SQL Server (and I think this should work in the ancient version you say you're using), the normal method for this is to use RAISERROR. PRINT won't display any output until the process finishes ...
  • 17.8k
10 votes
Accepted

Why choose a top query and temporary table instead of a cursor for a loop?

No, a loop is not necessarily faster than a cursor, though some people are more comfortable with them. I went through the loop writing phase myself at one time. Also cursors come in several flavors, ...
  • 13.8k
10 votes
Accepted

sp_cursorprepexec causing 53 million reads?

First of all, it surprises me that the actual number of rows for both queries from SQL Sentry isn't more or less the same. Second. It's hard to tell how correct your estimates are in the plan with a ...
  • 15.5k
10 votes

MySQL - UUID/created_at cursor based pagination?

WHERE created_at <= x AND ( created_at < x OR uuid < y ) ORDER BY created_at DESC, uuid DESC or this equivalent: WHERE ( created_at < x OR ( created_at = x ...
  • 70.4k
10 votes
Accepted

Why does CURSOR_STATUS return unexpected results for an output cursor inside the stored procedure that created it?

The cursor isn't assigned to the 'variable' @Cursor_OUT until copy-out at the end of the procedure. Before then, it isn't a 'cursor variable' and therefore not visible to CURSOR_STATUS or system ...
  • 73.8k
9 votes
Accepted

SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

This looks like an oversight. The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF and the target view (schema-bound or not) has a T-SQL ...
  • 73.8k
9 votes

Infinite loop in CURSOR

You want WHILE @@FETCH_STATUS = 0 which means continue unless something isn't right. Using <> -1 means it will continue even if the row fetched was missing, or it's not performing a fetch ...
  • 3,518
8 votes
Accepted

Return batches of rows based on one row

It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table: CREATE TABLE numbers ( i int NOT NULL PRIMARY KEY ) ; INSERT INTO numbers (i) VALUES ...
8 votes
Accepted

Cursoring over sys.databases skips databases

sys.databases is a complex view. On SQL Server 2016 it is: CREATE VIEW sys.databases AS SELECT d.name, d.id AS database_id, r.indepid AS source_database_id, d.sid AS ...
  • 73.8k
8 votes
Accepted

Changing SET options within cursor - Different behavior based on compatibility level

SQL Server 2012 introduced a breaking change to SET ROWCOUNT: The behavior of SET ROWCOUNT was changed to improve the efficiency of many common queries and can affect the query plans for ...
  • 24.7k
7 votes
Accepted

How to FETCH from a cursor into a table variable?

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert: FETCH NEXT FROM idCursor INTO @id, @data; INSERT @...
7 votes
Accepted

Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server. There is no ...
  • 431
7 votes
Accepted

sp_cursoropen chooses terrible execution plan

How can I help SQL Server find the good query plan even if cursors are used? Literally: use a plan guide or hints. But it would be much better to provide SQL Server with an optimal index, whether a ...
  • 73.8k
7 votes
Accepted

Cursor Replacement for Newbies

It depends™ The ability to work around one or multiple cursors, will depend on what is going to be executed inside of this cursor. Without knowing what is going on in it, there is no way to tell. It ...
7 votes
Accepted

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

So per @LowlyDBA's suggestion I decided to test this for myself (to a degree). I was actually in the middle of debugging a hot production issue so couldn't really stop to write out the code for a test ...
  • 26.5k
6 votes
Accepted

Improve a query using cursor to SET Based approach

Your CTE-based approach with window functions is a very good start. There's another, even more suitable window function that you could use: LAG(). Here's how: SELECT iVehicleMonitoringId AS ...
6 votes
Accepted

Is it possible to disable use of fast forward-only cursor on server side?

No, it is not possible to disable fast forward cursors on the server side, as far as I know. If your application requests a fast forward cursor, that is what it will get, at least in 'modern' ...
  • 73.8k
6 votes

Cursor Replacement for Newbies

There is no "general replacement" - you hid all the "dirty work" here so it's hard to tell if there is even a specific replacement in this case. There are certainly some specific cases where you're ...
5 votes
Accepted

T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan. declare @T table ( Eventdate date index IX_Eventdate clustered, Val ...
5 votes

Keep track of time intensive stored procedure with prints

You can also do: RAISERROR('%d', 0, 0, @cur_id) WITH NOWAIT; However depending on how many of these you have you can still overrun the buffer provided by Management Studio, and still end up in ...
5 votes
Accepted

Cursor with Begin and End Transaction

Just from looking at this code, something that jumps out as "clearly not good" is that you start a WHILE loop but then have 2 IF statements before the BEGIN: WHILE @@FETCH_STATUS = 0 --Drop Temp ...
5 votes
Accepted

Pass (comma separated) -yet single parameter to cursor

The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example: select * from xmltable('1, 2, 3') COLUMN_VALUE -...
  • 38.6k
5 votes
Accepted

Converting SQL SERVER Procedure to POSTGRESQL

It's way easier in plpgsql: CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$ DECLARE _r RECORD; _id_kraj int; _nazwa_zespolu text; BEGIN _id_kraj := id_kraj FROM kraj ...
5 votes
Accepted

Adding an ID column based on two other columns

You just need SELECT Location, Reference, Year, Int1, Int2, ROW_NUMBER() OVER (PARTITION BY Year, Reference ORDER BY Int2 DESC) ...
  • 78.6k
5 votes
Accepted

Static Cursor and Where current of

The main difference seems to be how each approach finds the row to be updated. The STATIC Cursor copies the full result set to a hidden temporary table first (hence why it is read-only), so it would ...

Only top scored, non community-wiki answers of a minimum length are eligible