none
Is this a bug with DELETE and table hints? RRS feed

  • Question

  • Is this a bug or what am I missing?

    thanks for your confirmation or hint on what I am missing

    use tempdb
    
    BEGIN TRAN
    
    
    CREATE TABLE dbo.Test (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT dbo.Test
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    DECLARE @b int = 2
    DECLARE @del TABLE (intID int PRIMARY KEY);
    DECLARE @t TABLE (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT @t 
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    -- this statement does not error
    DELETE TOP (@b)
    FROM dbo.Test
    WITH (UPDLOCK, READPAST)
    WHERE img IS NOT NULL
    
    -- this does error. why?
    DELETE TOP (@b)
    FROM @t
    WITH (UPDLOCK, READPAST)
    --OUTPUT deleted.intID INTO @del
    --OUTPUT deleted.intID
    WHERE img IS NULL
    
    ROLLBACK
    

    Friday, November 4, 2011 4:45 AM

Answers

  • HI Vladimir !

    Nice point, but can you go back to the defination of table variables they are still variables and you can't  create statistics / indexes on table variables. Similarly you can't provide hints to them because by defination they are just type of variable. You can instead use temporary tables to achineve this. See the code below;

     use tempdb
    
    BEGIN TRAN
    
    
    CREATE TABLE dbo.Test (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT dbo.Test
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    DECLARE @b int = 2
    DECLARE @del TABLE (intID int PRIMARY KEY);
    CREATE TABLE #t (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT #t 
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    
    -- this statement does not error
    DELETE TOP (@b)
    FROM dbo.Test
    WITH (UPDLOCK, READPAST)
    WHERE img IS NOT NULL
    
    -- this does error. why?
    DELETE TOP (@b)
    FROM #t
    WITH (UPDLOCK, READPAST)
    --OUTPUT deleted.intID INTO @del
    --OUTPUT deleted.intID
    WHERE img IS NULL
    
    ROLLBACK
    
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

     

    Friday, November 4, 2011 9:45 AM
    Answerer

All replies

  • I think you cannot specify the table hint with a table variable.

    Moreover, table variables does not participate in transactions,even you delete some rows that will not be rollback.use temp tables instead.

     

    --table variable not rollback in transaction

    declare @T table(id int)
    insert into @T
    select 1

    begin tran
    delete from @T
    rollback

    select * from @T


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, November 4, 2011 5:21 AM
  • HI Vladimir !

    Nice point, but can you go back to the defination of table variables they are still variables and you can't  create statistics / indexes on table variables. Similarly you can't provide hints to them because by defination they are just type of variable. You can instead use temporary tables to achineve this. See the code below;

     use tempdb
    
    BEGIN TRAN
    
    
    CREATE TABLE dbo.Test (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT dbo.Test
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    DECLARE @b int = 2
    DECLARE @del TABLE (intID int PRIMARY KEY);
    CREATE TABLE #t (intID int PRIMARY KEY, img varchar(10), img2 varchar(10))
    INSERT #t 
    VALUES (1, 'A', 'B')
        ,(2, 'C', 'D')
        ,(3, NULL, 'E')
        ,(4, 'F', NULL)
        ,(5, NULL, NULL)
    
    
    -- this statement does not error
    DELETE TOP (@b)
    FROM dbo.Test
    WITH (UPDLOCK, READPAST)
    WHERE img IS NOT NULL
    
    -- this does error. why?
    DELETE TOP (@b)
    FROM #t
    WITH (UPDLOCK, READPAST)
    --OUTPUT deleted.intID INTO @del
    --OUTPUT deleted.intID
    WHERE img IS NULL
    
    ROLLBACK
    
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

     

    Friday, November 4, 2011 9:45 AM
    Answerer
  • I thought the same thing that tables variables don't take hints, yet SQL BOL does not say anything about it. It probably should be documented that way.

    thank you all!

    Friday, November 4, 2011 12:55 PM
  • You may want to add a comment to this topic in BOL to clarify this point.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, November 4, 2011 2:00 PM
    Moderator
  • Yeh Naomi , i will put this at MSDN Library probably weekend.

    Thanks, Hasham

    Friday, November 4, 2011 2:12 PM
    Answerer