Sql-server – way to copy/initialize values in a set

azure-sql-databasesql serversql-server-2008update

What I want to do (I know this does not work) is:

update stats
set BandwidthLastMonth = BandwidthThisMonth, 
    BandwidthThisMonth=0 
where myPk = 3

Is there a way to do this in a select statement? Or do I need to write a stored procedure and hit each record twice (I sure hope not).

My solution needs to work for Sql Azure and Sql Server 2008.

Best Answer

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess.

Assuming that your code involves only a table and its columns (no @variables involved) and is something like:

UPDATE
    tablename
SET
    column_a = column_b, 
    column_b = 0 
WHERE 
    (condition) ;

It is valid SQL and 100% correct. Correct, meaning that after the update column_a will have the values that column_b had before the update and column_b will have 0, for all rows that pass the (condition).

SQL-Server follows this behaviour strictly and there is no reason to assume that it will deviate from the standard in a future release.

SQL works in sets and the updates are done "simultaneously" on the whole row. (they may not be done simultaneously but the result is the same as if they were.) You could change the code to this and the result will be (guaranteed) the same:

...
SET
    column_b = 0,
    column_a = column_b
...

In fact, the following is valid and equivalent SQL as well (although not yet implemented in SQL-Server);

UPDATE
    tablename
SET
    (column_a, column_b) = (column_b, 0) 
WHERE 
    (condition) ;

I haven't tested Azure but if it deviated from this basic SQL behaviour, it would better be documented somewhere.

Related Solutions

SQL Server – Making a Stored Procedure Thread Safe

I know raising the transaction isolation level and adding a transaction can seem counter-intuitive, but sometimes, you have to make things queue up and wait their turn. Doing those two things worked for me in my simple test rig. Try it and let us know how you get on.

EDIT: Test rig OK, nice simple test rig using tempdb. It's a bit rough and ready but provided an easy repro and provable fix for me. You should enable SQLCMD mode in SSMS before you run. I basically pit the proc against itself in multiple sessions using either ostress or GO 100. Run the initial version of the proc, monitor for deadlocks; I get many. Then, reset the rig, run in the revised version of the proc and try again.

USE tempdb
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[RawData_d_DeleteObsoleteRawData]') IS NOT NULL
DROP PROC [dbo].[RawData_d_DeleteObsoleteRawData]
GO

-- Example table type
IF EXISTS ( SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IntListType' )
    DROP TYPE IntListType
GO

CREATE TYPE IntListType AS TABLE ( Item INT PRIMARY KEY ) 
GO
-- Create a large table
-- DROP TABLE dbo.rawData
IF OBJECT_ID('dbo.rawData') IS NULL
CREATE TABLE dbo.rawData ( 
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT, 
    MeasureId INT, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    DateEnergy DATETIME DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION 
    )
GO


TRUNCATE TABLE dbo.rawData

-- Populate table
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.rawData ( AreaId, MeasureId, DateEnergy )
SELECT areaId.rn, MeasureId.rn, DATEADD( day, DateEnergy.rn % 300, '1 Jan 2014' )
FROM 
    ( SELECT TOP 10 rn FROM cte  ) areaId,
    ( SELECT TOP 100 rn FROM cte  ) MeasureId,
    ( SELECT TOP 1000 rn FROM cte  ) DateEnergy,
    ( SELECT TOP 2 rn FROM cte  ) doubleUp
GO



-- Original proc
CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

         -- Delete non remote reading raw data
         DELETE TOP (10000) FROM [dbo].[RawData]
         WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList)
         AND DateEnergy < @MaxTime

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
         ;WITH cte AS
         (
                 SELECT AreaId, MeasureId, DateEnergy,
                          ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                 FROM [RawData] rd WITH (NOLOCK)
                 INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                 WHERE DateEnergy < @MaxTime
         )
         DELETE TOP (10000) rd
         FROM [RawData] rd
         INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
         WHERE rn <> 1

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         SELECT @TotalDeleted as DeletedRow

    END
GO
:exit

-- Setup END
------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------
-- Main proc call START
-- Run this section in two or more connections
-- eg using ostress
-- !!ostress.exe -E -S.\sql2008r2 -dtempdb -i"C:\temp\temp.sql" -n10 -r5 -mstress
------------------------------------------------------------------------------------------------

-- Proc call
SET NOCOUNT ON
DECLARE @tvp AS [IntListType]

INSERT INTO @tvp
VALUES ( 1 ), ( 2 ), ( 3 ), ( 5 ), ( 8 ), ( 13 )

EXEC [dbo].[RawData_d_DeleteObsoleteRawData] @tvp, 30
GO 100

-- Main proc call END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Revised proc START
------------------------------------------------------------------------------------------------

/*  Reset the rig and try with the revised proc 

ALTER PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRY

        BEGIN TRAN

            -- Delete non remote reading raw data
            DELETE TOP (10000) FROM [dbo].[RawData]
            WHERE MeasureId NOT IN ( SELECT Item FROM @RemoteReadingMeasureIdList )
            AND DateEnergy < @MaxTime

            SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

            -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
            ;WITH cte AS
            (
                    SELECT AreaId, MeasureId, DateEnergy,
                            ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                    FROM [RawData] rd
                    INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                    WHERE DateEnergy < @MaxTime
            )
            DELETE TOP (10000) rd
            FROM [RawData] rd
            INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
             WHERE rn <> 1

             SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

             SELECT @TotalDeleted as DeletedRow

             COMMIT TRAN

         END TRY

         BEGIN CATCH

            DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

            IF @@TRANCOUNT > 0 BEGIN PRINT 'Error handler rolling back...' ROLLBACK TRAN END

            RAISERROR( @errorMessage, 16, 1 )

         END CATCH

    END
GO
*/


-- Revised proc END
------------------------------------------------------------------------------------------------

SQL Server 2008 – How to Get a Local Variable List in a Stored Procedure

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN link.

You would just need to take each requirement you list in your question or that the C# is performing, and write the query to pull the correct values from your tabled-values list and create your INSERT, DELETE, and UPDATE statements based on those values.

So for

Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Something like:

INSERT INTO CompanyDomains (<column list>
SELECT <column list>
FROM @TableParameter t
WHERE NOT EXISTS ( SELECT domain FROM CompanyDomains cd WHERE t.domain = cd.domain );

Best Answer

Related Solutions

SQL Server – Making a Stored Procedure Thread Safe

SQL Server 2008 – How to Get a Local Variable List in a Stored Procedure

Related Question