Clustered index equivalent for a table variable

Question

Hi,

I trying to (re)create, using only table variables, a certain query that originally used a clustered index to sort data in a specific order to ensure that two UPDATE statements that were run on it returned correct results.
The original query is as follows:

CREATE CLUSTERED INDEX xi_xt_stockagepl
  ON dbo.xt_stockagepl_rec (item, trdate DESC, id DESC);

go

DECLARE @TotalQTY      FLOAT,
        @TotalQTYReset FLOAT;
DECLARE @ITEM VARCHAR(30);

SET @TotalQTYReset = 0.0;
SET @TotalQTY=@TotalQTYReset;

UPDATE dbo.xt_stockagepl_rec
SET    @TotalQTY = qty_incr = qty + CASE WHEN item=@ITEM THEN @TotalQTY ELSE
                                     @TotalQTYReset END,
       @ITEM = item

go

UPDATE s1
SET    s1.qty_age = CASE
                      WHEN s1.qty_incr <= s1.qty_stock THEN s1.qty
                      ELSE s1.qty - ( s1.qty_incr - s1.qty_stock )
                    END,
       s1.rng = CASE
                  WHEN s1.age <= '360' THEN 'R1'
                  WHEN s1.age > '360'
                       AND s1.age <= '720' THEN 'R2'
                  WHEN s1.age > '720'
                       AND s1.age <= '1080' THEN 'R3'
                  WHEN s1.age > '1080' THEN 'R4'
                  ELSE 'R0'
                END
FROM   dbo.xt_stockagepl_rec s1;

go

The table definition is as follows:

CREATE TABLE [dbo].[xt_StockAgePL_REC](
    [ID] [bigint] NULL,
    [ITEM] [varchar](30) NULL,
    [RNG] [char](2) NULL,
    [AGE] [int] NULL,
    [QTY] [float] NOT NULL,
    [QTY_AGE] [float] NULL,
    [QTY_INCR] [float] NULL,
    [QTY_Stock] [float] NULL,
    [VALUE] [float] NOT NULL,
    [PriceAVG] [float] NULL,
    [TrID] [int] NOT NULL,
    [TrOurRef] [char](8) NULL,
    [TrDate] [datetime] NULL
) ON [PRIMARY]
GO

Below are 10 rows prior to running the update statement:

INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (3, N'LOZY-00240177', N'R0', 150, 6, 0, 0, 9, 2154.9, 381.19222222222209, 5192010, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (2, N'LOZY-00240177', N'R0', 156, 15, 0, 0, 9, 5850.15, 381.19222222222209, 5150850, N'10297049', CAST(N'2020-09-25T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (1, N'LOZY-00240177', N'R0', 455, 6, 0, 0, 9, 2154.9, 381.19222222222209, 4735253, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (6, N'LOZY-00240231', N'R0', 150, 2, 0, 0, 9, 890.26, 466.46444444444438, 5192009, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (5, N'LOZY-00240231', N'R0', 156, 12, 0, 0, 9, 5640.24, 466.46444444444438, 5150848, N'10297049', CAST(N'2020-09-25T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (4, N'LOZY-00240231', N'R0', 455, 2, 0, 0, 9, 890.26, 466.46444444444438, 4735252, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (8, N'LOZY-028283500', N'R0', 150, 2, 0, 0, 2, 729.06, 364.53, 5192008, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (7, N'LOZY-028283500', N'R0', 455, 2, 0, 0, 2, 729.06, 364.53, 4735255, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (10, N'LOZY-23246700', N'R0', 150, 4, 0, 0, 4, 898.28, 224.57, 5192007, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (9, N'LOZY-23246700', N'R0', 455, 4, 0, 0, 4, 898.28, 224.57, 4735254, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))

And here are the same rows after the update statements have been run:

INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (3, N'LOZY-00240177', N'R1', 150, 6, 6, 6, 9, 2154.9, 381.19222222222231, 5192010, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (2, N'LOZY-00240177', N'R1', 156, 15, 3, 21, 9, 5850.15, 381.19222222222231, 5150850, N'10297049', CAST(N'2020-09-25T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (1, N'LOZY-00240177', N'R2', 455, 6, -12, 27, 9, 2154.9, 381.19222222222231, 4735253, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (6, N'LOZY-00240231', N'R1', 150, 2, 2, 2, 9, 890.26, 466.46444444444438, 5192009, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (5, N'LOZY-00240231', N'R1', 156, 12, 7, 14, 9, 5640.24, 466.46444444444438, 5150848, N'10297049', CAST(N'2020-09-25T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (4, N'LOZY-00240231', N'R2', 455, 2, -5, 16, 9, 890.26, 466.46444444444438, 4735252, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (8, N'LOZY-028283500', N'R1', 150, 2, 2, 2, 2, 729.06, 364.53, 5192008, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (7, N'LOZY-028283500', N'R2', 455, 2, 0, 4, 2, 729.06, 364.53, 4735255, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (10, N'LOZY-23246700', N'R1', 150, 4, 4, 4, 4, 898.28, 224.57, 5192007, N'10299053', CAST(N'2020-10-01T00:00:00.000' AS DateTime))
INSERT [dbo].[xt_StockAgePL_REC] ([ID], [ITEM], [RNG], [AGE], [QTY], [QTY_AGE], [QTY_INCR], [QTY_Stock], [VALUE], [PriceAVG], [TrID], [TrOurRef], [TrDate]) VALUES (9, N'LOZY-23246700', N'R2', 455, 4, 0, 8, 4, 898.28, 224.57, 4735254, N'10276118', CAST(N'2019-12-01T00:00:00.000' AS DateTime))

My question therefore is how can I achieve the same results but using only table variables, i.e. without using clustered index.

Accepted Answer

Hi anonymous userMuryjas-7562,

Welcome to Microsoft Q&A!

Actually you could also use SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) to calculate a running total instead of creating an index and using variables.

So you could put the 'item, trdate DESC, id DESC' part inside the SUM (order by ) part.

A little modification based on Erland's query as below:

; WITH CTE AS (  
   SELECT RNG, ID, AGE, QTY, QTY_AGE, QTY_INCR, QTY_Stock,  
          new_incr = SUM(QTY) OVER(PARTITION BY ITEM ORDER BY item, trdate DESC, id DESC)  
   FROM  xt_StockAgePL_REC  
)  
UPDATE CTE   
SET    QTY_INCR = new_incr,  
       QTY_AGE   = CASE WHEN new_incr <= QTY_Stock THEN QTY  
                        ELSE QTY - (new_incr - QTY_Stock)  
                   END,  
       RNG       = CASE WHEN AGE <= 360  THEN 'R1'  
                        WHEN AGE <= 720  THEN 'R2'  
                        WHEN AGE <= 1080 THEN 'R3'  
                        WHEN AGE > 1080  THEN 'R4'  
                        ELSE 'R0'  
                  END

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

You have clustered indexes in table variables too.

However, unless you are on a very ancient version of SQL Server, you can do this, which is easier and safer:

; WITH CTE AS (
   SELECT RNG, ID, AGE, QTY, QTY_AGE, QTY_INCR, QTY_Stock,
          new_incr = SUM(QTY) OVER(PARTITION BY ITEM ORDER BY TrDate DESC
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
   FROM  xt_StockAgePL_REC
)
UPDATE CTE 
SET    QTY_INCR = new_incr,
       QTY_AGE   = CASE WHEN new_incr <= QTY_Stock THEN QTY
                        ELSE QTY - (new_incr - QTY_Stock)
                   END,
       RNG       = CASE WHEN AGE <= 360  THEN 'R1'
                        WHEN AGE <= 720  THEN 'R2'
                        WHEN AGE <= 1080 THEN 'R3'
                        WHEN AGE > 1080  THEN 'R4'
                        ELSE 'R0'
                  END

What you have above only works by chance - there are no guarantees.

Answer

Hi Melisa (and Erland),

thank you very much for your help. The code suggested by you worked just fine. My issue has been solved.

Best regards,
Igor

Share via

Clustered index equivalent for a table variable

2 additional answers