Post reply

Constraints for temp tables

  • February 1, 2022 at 8:59 am

    #3979466

    Hi

    I've been asked to do a data purge on a huge database.

    There is an existing script to do this but it is taking forever to delete the records so i thought i'd run a few jobs concurrently.

    However the code below prevents that because the constraint is named:

    CREATE TABLE #tempTable
    			(
    				RecordID numeric,
    				CONSTRAINT [PK_tempTable] PRIMARY KEY CLUSTERED
    				(
    					RecordID ASC
    				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    			) ON [PRIMARY]

     

    I've changed it to this so that SQL can give the constraint a temp name and avoid any conflicts:

    CREATE TABLE #tempTable
    			(
    				RecordID numeric NOT NULL PRIMARY KEY
    			) ON [PRIMARY]

     

    All the options set in the first example appear to be the defaults and when i look at the details of both versions using this:

    EXEC tempdb.dbo.sp_help @objname = N'#tempTable';

    All the info appears to be identical except the name of the constraint which in the second example is generated by SQL.

    Just wanted to check on here to see if anyone can see any issues with making the changes I've made because this is a little bit beyond what I would normally do.

    Thanks

  • February 1, 2022 at 9:53 am

    #3979492

    I don't see an issue in using randomly generated PK names for temp tables

  • February 1, 2022 at 10:08 am

    #3979504

    There is no issue with letting SQL generate the PK name for the temp table.

    That said, there is no guarantee that your script will run any faster when running 2 or more concurrent versions of the same script.  In fact, you may even find that it runs slower as the scripts may be accessing the same data pages, causing blocking.

    How to post data/code on a forum to get the best help.

  • February 1, 2022 at 10:42 am

    #3979509

    Hi Guys

    Thanks for the quick replies!

    I did have some issues with page locks when i started trying to set it up but I've set each job to run on a separate date range which seems to have resolved that. And i did some index work that has helped to make it run faster. Still gong to take a while though just because it has been left to accumulate such a huge amount of data over the course of about ten years!

    Thanks again for the help. Very much appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply