Hot answers tagged

22

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 create multiple temp tables in the tempdb. I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see ...


20

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 FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you. The provided execution plan shows the optimizer choosing a static-like plan. Because the ...


14

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 not for "some time". Instead, the database makes a note of the change in its Transaction Log, then updates the value in memory. "Some time" later, the database ...


12

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 do with whether you are using a cursor in combination with your #temp table (though I suspect the cursor isn't needed anyway - you didn't include enough code to ...


12

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 data structure you are traversing. DECLARE st CURSOR LOCAL STATIC FOR . . .


10

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 which is not very useful. I use this technique a lot in batch scripts for say updates and deletes where I want to keep the batch size small. In the example below, ...


10

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, so choosing the right type of cursor is an important detail. Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative ...


10

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 cursor without an actual plan but some things stand out to me. (P.S.: refer to my answer here to get an actual plan). That being said, there are a couple of ...


9

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 instead-of update trigger, regardless of the number of rows, or any other considerations: That shows an example with almost 20,000 rows in the table (copied ...


9

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 operation, making it infinite unless you get -1 as a return value, since there are 4 return values for @@FETCH_STATUS. 0 The FETCH statement was successful. -1 ...


8

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc: WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits On the other hand, a refcursor opened by a plpgsql function ...


8

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 (1), (3), (5), (7), (9) ; We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half). The query: SELECT ...


7

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 equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner ...


7

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 cursor is used or not: CREATE INDEX [IX dbo.auftrag_prod_soll auftrag_produktion prod_soll_über reihenfolge] ON dbo.auftrag_prod_soll (auftrag_produktion, ...


7

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 case. But the code I'm debugging is this scenario. So I made my update to the procedure and looked at the running queries and saw the query text changed from ...


6

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 @currentId([id],[data]) SELECT @id, @data; However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why ...


6

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 CurrentID, LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, iAssetId AS CurrentAsset, LAG(iAssetId, 1) OVER (ORDER BY ...


6

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' versions of SQL Server (2005+). From Using Implicit Cursor Conversions: Fast forward cursors are never converted. I have long since forgotten what might or might ...


6

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 processing a set of rows one row at a time, whether using a cursor, or while loop, or any other iterative process, where converting to a set-based process that ...


6

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 could be that there is no workaround, and you have to do row by row processing. Below are a some examples. Not working in sets This example is the most basic ...


6

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 owner_sid, d.crdate AS create_date, d.cmptlevel AS compatibility_level, -- coll.value = null means that a collation was not specified for ...


5

While if you get this sorted out before you read my answer, making it unlikely to be useful for your particular case (unless you need to do this in multiple databases), I prefer string concatenation over cursors for this type of work. Primarily because you can print the string instead of executing it, verifying that at least the first 8K worth looks proper ...


5

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 int ); insert into @T(Eventdate, Val) values ('2012-03-23', 3965), ('2012-03-26', 3979), ('2012-03-27', 3974), ('2012-03-28', 3965), ('2012-03-29', 3967), (...


5

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 ------------ 1 2 3 Notice how the column is named column_value automatically. This is an XMLType column, that you need to convert to be able to compare it with ...


5

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 WHERE nazwa = $1; FOR _r IN SELECT imie, nazwisko, id_zespol, id_kraj FROM pilkarz LOOP IF r.id_kraj = _id_kraj THEN _nazwa_zespolu := nazwa FROM ...


5

You just need SELECT Location, Reference, Year, Int1, Int2, ROW_NUMBER() OVER (PARTITION BY Year, Reference ORDER BY Int2 DESC) AS [Id] FROM Value To give your desired results (online demo). In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering ...


5

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 program executed automatically to respond to a specific event like an insert, update, or delete. As you can see, these are very different concepts and have ...


5

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 AND uuid < y ) ) ORDER BY created_at DESC, uuid DESC This technique works for any pair of columns where the first one (created_at) ...


5

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, system_license varchar(99)); DECLARE @fixedname nvarchar(150); --Include or exclude whatever databases you want DECLARE dbCursor CURSOR FOR SELECT REPLACE(...


5

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 executing that same query over and over again, just for a different row. It should be far more efficient to store the results of the initial query into a local temp ...


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