Table Delete

• Bruin

  SSCrazy

  Points: 2051

  More actions

  November 15, 2024 at 12:24 pm

  #4482205

  I have a very large table and I'm looking to do a cleanup based upon a non-keyed field. This table many concurrent insert transactions running against it, and I can't afford downtime to create an Index this field. Looking for some suggestions using the PK(ID) to help speed up the deletes and minimize Locking on the table.

  Right now driver for cleanup is something like this:

  delete from Image_Classification_Master

  where

  convert(date,SpoolStartDt) = '02/22/2022'

  Thanks.

  CREATE TABLE [dbo].[Image_Classification_Master](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [ImageName] [nvarchar](500) NULL,
  [ImageType] [nvarchar](200) NULL,
  [ImageSource] [image] NULL,
  [ReceivedDateTime] [datetime] NULL,
  [ImagePath] [nvarchar](500) NULL,
  [Site] [varchar](3) NULL,
  [MachineNbr] [int] NULL,
  [LineNbr] [int] NULL,
  [TakeUpNbr] [int] NULL,
  [SpoolNbr] [int] NULL,
  [ImageIndex] [int] NULL,
  [CameraNbr] [varchar](2) NULL,
  [SpoolStartDt] [datetime] NULL,
  [SpoolStartTime] [time](7) NULL,
  [DefectDate] [datetime] NULL,
  [DefectTime] [time](7) NULL,
  [DefectNbr] [int] NULL,
  [DefectClass] [varchar](100) NULL,
  [Reviewer] [nvarchar](50) NULL,
  [UserDefectInput] [nvarchar](10) NULL,
   CONSTRAINT [PK_Image_Classification_Master] PRIMARY KEY CLUSTERED 
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO
  
  ALTER TABLE [dbo].[Image_Classification_Master] ADD  CONSTRAINT [DF_Image_Classification_Master_ReceivedDateTime]  DEFAULT (getdate()) FOR [ReceivedDateTime]
  GO