Skip to main content
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 ...
Phill  W.'s user avatar
  • 8,850
12 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 ...
Rick James's user avatar
  • 78.5k
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 ...
David Browne - Microsoft's user avatar
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 ...
Tom V's user avatar
  • 15.7k
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 ...
Paul White's user avatar
  • 86.3k
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 ...
S3S's user avatar
  • 3,578
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Paul White's user avatar
  • 86.3k
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 ...
Dan Guzman's user avatar
  • 28.3k
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 ...
Paul White's user avatar
  • 86.3k
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 ...
Randi Vertongen's user avatar
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 ...
J.D.'s user avatar
  • 38k
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' ...
Paul White's user avatar
  • 86.3k
6 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 ...
Ezequiel Tolnay's user avatar
6 votes
Accepted

Are PostgreSQL cursor bound to connection?

Yes, cursors are not shared across sessions, and their namespace is private to their session as well. Moreover, if the cursor is not declared "holdable" trough the WITH HOLD clause, it's ...
Daniel Vérité's user avatar
6 votes
Accepted

MySQL - UUID/created_at cursor based pagination?

I will answer what you asked, but first let me tell you that I don't understand why you want to do that. An autoincremental id is very good for this task. But it is correct to also use a timestamp ...
Federico Razzoli's user avatar
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 ...
Aaron Bertrand's user avatar
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 ...
Solomon Rutzky's user avatar
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) ...
Martin Smith's user avatar
  • 84.9k
5 votes

What's the difference between a cursor and a trigger?

Generally, a cursor is used to iterate through a result set that was returned by a SELECT statement. Cursors can be used in stored procedures, stored functions, and triggers. A trigger is a stored ...
630's user avatar
  • 305
5 votes
Accepted

Need to run select statement across all databases

Here's the "do something on each db" template I use, slightly adapted to your example: --Create a table to hold the query results CREATE TABLE #licenselist (dbid smallint, dbname sysname, ...
BradC's user avatar
  • 9,994
5 votes
Accepted

Cursor causes SSMS to crash

First, it looks like you are doing a ton of extra work here. The query in the cursor is a subset of the query in the WHILE loop, and the data does not change at any point in time. So it is just ...
Solomon Rutzky's user avatar
5 votes

Cursor causes SSMS to crash

To get past the SSMS issue, SET NOCOUNT ON and don't send results to the client. ServerXMLHTTP.Send, when called from the deprecated sp_OAxxx COM interop stored procedures has an 8000 character limit,...
David Browne - Microsoft's user avatar
5 votes
Accepted

Using cursor to update if exists and insert if not

I think that error is being generated because of this line If (Select Login from [dbo].[LoginsForDBUserList]) = @log Multiple rows are being returned from the left side of the equal sign and is ...
Scott Hodgin - Retired's user avatar
5 votes

Cursor never stops

The cursor will loop infinitely unless and until @@fetchstatus = 0. In order to reach that state, you need to proceed through the dataset. In order to do that, you should add fetch next from ...
Peter Vandivier's user avatar
5 votes
Accepted

How to drop a procedure across a lot of databases (SQL Server 2016)

You were very close, but forgot a small thing : execute sp_msforeachdb 'USE [?] drop procedure Usages' The use [?] will actually go to each db, and execute it there, this should solve your issue. ...
Yannick Liekens's user avatar
4 votes

Stored Procedure return unwanted cursor result

Instead of using a cursor*, write the update and insert operations explicitly as set-based operations: CREATE OR ALTER PROCEDURE cache.MoveInverterData ( @sourceInverterID bigint, @from ...
Paul White's user avatar
  • 86.3k
4 votes

Pass data from a table row into a create login statement using dynamicSQL

I understand that the exercise is to use a cursor and dynamic SQL, but just to give you another view (and something to take beyond this one class I suppose), you can do a lot of administrative tasks ...
Aaron Bertrand's user avatar
4 votes
Accepted

Insert top 253 values where value in select distinct tradernames

In general if you have a cursor that's your first place to look for performance issues. It probably works great if you have 10 traders, not so well if you have 10,000. Try this. INSERT INTO #...
Kenneth Fisher's user avatar
4 votes
Accepted

Safeguard against infinite looping in case of missing FETCH NEXT

To avoid problems like this I'm using different pattern for fetch: OPEN Cursor; WHILE 1=1 BEGIN FETCH NEXT FROM Cursor INTO [...]; IF @@FETCH_STATUS <> 0 BREAK; [...] END; ...
Piotr Palka's user avatar
  • 1,591

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