October 28, 2016 at 8:22 am
Hi,
I'm currently trying delete data from a large database with the below code... But its taking quite a long time to delete 1000 rows the table holds over 1mil records
Any ideas on how I can improve the performance ?
The column is indexed and i have tired using rowlock but still no improvement
DECLARE @r INT;
DECLARE @loopCount int;
DECLARE @maxLoops int;
SET @maxLoops = 5
SET @loopCount = 0
SET @r = 1;
WHILE @r > 0 AND @loopCount < @maxLoops
BEGIN
DELETE TOP (1000)
FROM [u_od] WITH (ROWLOCK)
WHERE
odid IN (SELECT id FROM contacts)
SET @r = @@ROWCOUNT;
SET @loopCount = @loopCount + 1;
WAITFOR DELAY '00:00:01'
END
GO
October 28, 2016 at 8:27 am
Suth (10/28/2016)
Hi,I'm currently trying delete data from a large database with the below code... But its taking quite a long time to delete 1000 rows the table holds over 1mil records
Any ideas on how I can improve the performance ?
The column is indexed and i have tired using rowlock but still no improvement
DECLARE @r INT;
DECLARE @loopCount int;
DECLARE @maxLoops int;
SET @maxLoops = 5
SET @loopCount = 0
SET @r = 1;
WHILE @r > 0 AND @loopCount < @maxLoops
BEGIN
DELETE TOP (1000)
FROM [u_od] WITH (ROWLOCK)
WHERE
odid IN (SELECT id FROM contacts)
SET @r = @@ROWCOUNT;
SET @loopCount = @loopCount + 1;
WAITFOR DELAY '00:00:01'
END
GO
Quick question, can you post the full DDL of the table, including all indices?
😎
October 28, 2016 at 8:33 am
Also, how many rows of data are you trying to delete versus how many rows of data you are keeping?
October 28, 2016 at 8:34 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[u_od](
[id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[created] [DATETIME] NOT NULL,
[createdBy] [INT] NOT NULL,
[updated] [DATETIME] NOT NULL,
[updatedBy] [INT] NOT NULL,
[orderId] [INT] NOT NULL,
[title] [VARCHAR](30) NULL,
[qty] [NUMERIC](18, 0) NOT NULL,
[startDate] [DATETIME] NULL,
[endDate] [DATETIME] NULL,
[startTime] [VARCHAR](8) NULL,
[endTime] [VARCHAR](8) NULL,
[notes] [NTEXT] NULL,
[deletedFlag] [SMALLINT] NOT NULL,
[recordType] [VARCHAR](30) NULL,
[recycledFlag] [BIT] NOT NULL,
[hardDeletedFlag] [BIT] NOT NULL,
[importId] [INT] NULL,
[attTXT01] [NVARCHAR](30) NULL,
[attTXT02] [NVARCHAR](30) NULL,
[attTXT03] [NVARCHAR](30) NULL,
[attTXT04] [NVARCHAR](30) NULL,
[attTXT05] [NVARCHAR](30) NULL,
[attTXT06] [NVARCHAR](30) NULL,
[attTXT07] [NVARCHAR](30) NULL,
[attTXT08] [NVARCHAR](30) NULL,
[attTXT09] [NVARCHAR](30) NULL,
[attTXT10] [NVARCHAR](30) NULL,
[attTXT11] [NVARCHAR](30) NULL,
[attTXT12] [NVARCHAR](30) NULL,
[attTXT13] [NVARCHAR](30) NULL,
[attTXT14] [NVARCHAR](30) NULL,
[attTXT15] [NVARCHAR](30) NULL,
[attTXT16] [NVARCHAR](30) NULL,
[attTXT17] [NVARCHAR](30) NULL,
[attTXT18] [NVARCHAR](30) NULL,
[attTXT19] [NVARCHAR](30) NULL,
[attTXT20] [NVARCHAR](30) NULL,
[attTXT21] [NVARCHAR](30) NULL,
[attTXT22] [NVARCHAR](30) NULL,
[attTXT23] [NVARCHAR](30) NULL,
[attTXT24] [NVARCHAR](30) NULL,
[attTXT25] [NVARCHAR](30) NULL,
[attTXT26] [NVARCHAR](30) NULL,
[attTXT27] [NVARCHAR](30) NULL,
[attTXT28] [NVARCHAR](30) NULL,
[attTXT29] [NVARCHAR](240) NULL,
[attTXT30] [NVARCHAR](240) NULL,
[attTXT31] [NVARCHAR](240) NULL,
[attTXT32] [NVARCHAR](240) NULL,
[attTXT33] [NVARCHAR](240) NULL,
[attTXT34] [NVARCHAR](240) NULL,
[attTXT35] [NVARCHAR](240) NULL,
[attTXT36] [NVARCHAR](240) NULL,
[attTXT37] [NVARCHAR](240) NULL,
[attTXT38] [NVARCHAR](240) NULL,
[attTXT39] [NVARCHAR](240) NULL,
[attTXT40] [NVARCHAR](50) NULL,
[attTXT41] [NVARCHAR](50) NULL,
[attTXT42] [NVARCHAR](50) NULL,
[attTXT43] [NVARCHAR](50) NULL,
[attTXT44] [NVARCHAR](50) NULL,
[attTXT45] [NVARCHAR](50) NULL,
[attTXT46] [NVARCHAR](50) NULL,
[attTXT47] [NVARCHAR](50) NULL,
[attTXT48] [NVARCHAR](50) NULL,
[attTXT49] [NVARCHAR](50) NULL,
[attTXT50] [NVARCHAR](50) NULL,
[attTXT51] [NVARCHAR](50) NULL,
[attTXT52] [NVARCHAR](50) NULL,
[attTXT53] [NVARCHAR](50) NULL,
[attTXT54] [NVARCHAR](50) NULL,
[attTXT55] [NVARCHAR](50) NULL,
[attTXT56] [NVARCHAR](50) NULL,
[attTXT57] [NVARCHAR](50) NULL,
[attTXT58] [NVARCHAR](50) NULL,
[attTXT59] [NVARCHAR](50) NULL,
[attTXT60] [NVARCHAR](50) NULL,
[attTXT61] [NVARCHAR](100) NULL,
[attTXT62] [NVARCHAR](100) NULL,
[attTXT63] [NVARCHAR](100) NULL,
[attTXT64] [NVARCHAR](100) NULL,
[attTXT65] [NVARCHAR](100) NULL,
[attTXT66] [NTEXT] NULL,
[attTXT67] [NTEXT] NULL,
[attTXT68] [NTEXT] NULL,
[attTXT69] [NTEXT] NULL,
[attTXT70] [NTEXT] NULL,
[attDT01] [SMALLDATETIME] NULL,
[attDT02] [SMALLDATETIME] NULL,
[attDT03] [SMALLDATETIME] NULL,
[attDT04] [SMALLDATETIME] NULL,
[attDT05] [SMALLDATETIME] NULL,
[attDT06] [SMALLDATETIME] NULL,
[attDT07] [SMALLDATETIME] NULL,
[attDT08] [SMALLDATETIME] NULL,
[attDT09] [SMALLDATETIME] NULL,
[attDT10] [SMALLDATETIME] NULL,
[attDT11] [SMALLDATETIME] NULL,
[attDT12] [SMALLDATETIME] NULL,
[attDT13] [SMALLDATETIME] NULL,
[attDT14] [SMALLDATETIME] NULL,
[attDT15] [SMALLDATETIME] NULL,
[attDT16] [SMALLDATETIME] NULL,
[attDT17] [SMALLDATETIME] NULL,
[attDT18] [SMALLDATETIME] NULL,
[attDT19] [SMALLDATETIME] NULL,
[attDT20] [SMALLDATETIME] NULL,
[attNum01] [REAL] NULL,
[attNum02] [REAL] NULL,
[attNum03] [REAL] NULL,
[attNum04] [REAL] NULL,
[attNum05] [REAL] NULL,
[attNum06] [REAL] NULL,
[attNum07] [REAL] NULL,
[attNum08] [REAL] NULL,
[attNum09] [REAL] NULL,
[attNum10] [REAL] NULL,
[attNum11] [REAL] NULL,
[attNum12] [REAL] NULL,
[attNum13] [REAL] NULL,
[attNum14] [REAL] NULL,
[attNum15] [REAL] NULL,
[attNum16] [REAL] NULL,
[attNum17] [REAL] NULL,
[attNum18] [REAL] NULL,
[attNum19] [REAL] NULL,
[attNum20] [REAL] NULL,
[attNum21] [REAL] NULL,
[attNum22] [REAL] NULL,
[attNum23] [REAL] NULL,
[attNum24] [REAL] NULL,
[attNum25] [REAL] NULL,
[attNum26] [REAL] NULL,
[attNum27] [REAL] NULL,
[attNum28] [REAL] NULL,
[attNum29] [REAL] NULL,
[attNum30] [REAL] NULL,
[attBIT01] [BIT] NULL,
[attBIT02] [BIT] NULL,
[attBIT03] [BIT] NULL,
[attBIT04] [BIT] NULL,
[attBIT05] [BIT] NULL,
[attBIT06] [BIT] NULL,
[attBIT07] [BIT] NULL,
[attBIT08] [BIT] NULL,
[attBIT09] [BIT] NULL,
[attBIT10] [BIT] NULL,
CONSTRAINT [PK_u_orderItem] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_created] DEFAULT (GETDATE()) FOR [created]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_createdBy] DEFAULT (0) FOR [createdBy]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_updated] DEFAULT (GETDATE()) FOR [updated]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_updatedBy] DEFAULT (0) FOR [updatedBy]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_orderId] DEFAULT (0) FOR [orderId]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_qty] DEFAULT (0) FOR [qty]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_deletedFlag] DEFAULT (0) FOR [deletedFlag]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_recycledFlag] DEFAULT (0) FOR [recycledFlag]
GO
ALTER TABLE [dbo].[u_od] ADD CONSTRAINT [DF_u_orderItem_hardDeletedFlag] DEFAULT (0) FOR [hardDeletedFlag]
GO
October 28, 2016 at 8:37 am
well ideally im getting rid of everything older than year , which is around 1 million rows. And ill be left with 2mil rows
October 28, 2016 at 9:01 am
Are there no indices on the table?
😎
October 28, 2016 at 9:03 am
indicies ? do you mean indexes ?
October 28, 2016 at 9:16 am
Suth (10/28/2016)
indicies ? do you mean indexes ?
It seems to me that the columns [odid] needs to be indexed; otherwise it's going to do a table scan for each batch. Also, a batch size of 1000 rows is way too small, because if your goal is to delete 1 million rows, that results in 1000 iterations / table scans, so of course it's taking a very long time. I'd suggest at least changing the batch size to something like 100,000, so there are only 10 iterations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 28, 2016 at 9:26 am
odid has a non clustered index on it..
October 28, 2016 at 9:33 am
Suth (10/28/2016)
Hi,I'm currently trying delete data from a large database with the below code... But its taking quite a long time to delete 1000 rows the table holds over 1mil records
Any ideas on how I can improve the performance ?
The column is indexed and i have tired using rowlock but still no improvement
Are there any tables with a foreign key to this table? A delete operation requires SQL Server to check to make sure this won't break referential integrity. If there are foreign keyed tables, then one or more of them may not have an index on the foreign keyed columns. You can look at the estimated execution plan of the DELETE to see.
October 29, 2016 at 6:55 am
Suth (10/28/2016)
Hi,I'm currently trying delete data from a large database with the below code... But its taking quite a long time to delete 1000 rows the table holds over 1mil records
Any ideas on how I can improve the performance ?
The column is indexed and i have tired using rowlock but still no improvement
DECLARE @r INT;
DECLARE @loopCount int;
DECLARE @maxLoops int;
SET @maxLoops = 5
SET @loopCount = 0
SET @r = 1;
WHILE @r > 0 AND @loopCount < @maxLoops
BEGIN
DELETE TOP (1000)
FROM [u_od] WITH (ROWLOCK)
WHERE
odid IN (SELECT id FROM contacts)
SET @r = @@ROWCOUNT;
SET @loopCount = @loopCount + 1;
WAITFOR DELAY '00:00:01'
END
GO
Suth (10/28/2016)
well ideally im getting rid of everything older than year , which is around 1 million rows. And ill be left with 2mil rows
There is NOTHING in your code concerning any kind of temporal limit. It relies solely on the supposed order of an index in an execution plan, which could change in a heartbeat. It's dangerous code and I wouldn't run it as it is ever.
Further, you've added a ROWLOCK hint and that only serves to cripple it for performance.
I'll be back but wanted to let you know that you MUST NOT run this code because it could delete things that you don't want it to.
--Jeff Moden
October 29, 2016 at 11:14 am
Ok... here we go. First, let's make the understanding that if you only want to keep 1 year of data, you should do deletes every day instead of having a large number of deletes on occasion. With that in mind and the fact that even this first large set of deletes needs to look at the table at least once for every iteration in a temporal fashion, you need to create an index on the column that will be used to decide the age of a row. I'm assuming that will be the "Created" column.
--===== Create an index to support the deletes.
-- Note the two places where you need to change the database name before running.
[font="Arial Black"]-- IMPORTANT! TAKE A LOG FILE BACKUP JUST BEFORE AND JUST AFTER RUNNINNG THIS CODE![/font]
-- This builds a new index using MINIMAL LOGGING so that we don't blow out the log file.
ALTER DATABASE [PutYourDatabaseNameHere] SET RECOVERY BULK_LOGGED WITH NO_WAIT
;
CREATE INDEX IX_By_Created_OdID_ID
ON dbo.U_Od (Created, OdID, ID)
;
ALTER DATABASE [PutYourDatabaseNameHere] SET RECOVERY FULL WITH NO_WAIT
;
GO
[font="Arial Black"]Also understand that none of the code has been tested because I don't have your tables. You need to do a syntax, safety, and functional review before you try it.[/font] Again, if you want to keep things in check instead of doing monster deletes on occasion, you could run the following code every day as a scheduled job.
See the notes in the following code. It should run fairly quickly because it should run in mostly Clustered Index Order. There's no need for any ROWLOCK hints or other stuff, either because it will be deleted from the logical beginning of the table across adjacent mostly whole pages instead of in a haphazard fashion. The index the we added might net need any maintenance because it is also temporally ever-increasing.
--===== Suppress the auto-display of row counts just to make the output pretty.
SET NOCOUNT ON
;
--===== If you only want to keep a year of data, the following should be executed
-- every day. Doesn't matter what time of day it is.
CREATE TABLE #ToBeDeleted
(ID INT)
;
--===== Local variables and presets
DECLARE @Counter INT = 1
,@BatchSize INT = 10000
,@CutoffDate DATETIME = DATEADD(yy,-1,CONVERT(DATE,GETDATE())) --1 Year ago today
,@RowCount INT
,@StartTime DATETIME
,@DurationMS INT
;
--===== This is the "delete loop". It will execute at least once.
-- If you had, say, 20768 rows that were to be deleted, it will run 3 times.
-- Two for (in this case), 10000 rows and one for 768 rows. It won't waste time
-- or reesources on a final pass looking for 0 rows.
WHILE @Counter = 1 --This allow the first iteration to occur
OR @RowCount = @BatchSize --This skips the useless final pass on zero rows
BEGIN --but still does the last batch of less than @BatchSize.
--===== Start the timer and identify the pass.
SELECT @StartTime = GETDATE();
RAISERROR ('Working on pass %u...',0,0,@Counter) WITH NOWAIT
;
--===== Find the IDs to be deleted.
INSERT INTO #ToBeDeleted WITH (TABLOCK)
(ID)
SELECT TOP (@BatchSize)
uod.ID
FROM dbo.U_Od uod
JOIN dbo.Contacts con
ON uod.ID = con.ID
WHERE uod.Created < @CutoffDate
ORDER BY uod.Created
;
--===== Delete the previously identified rows.
DELETE FROM dbo.U_Od uod
WHERE uod.ID IN (SELECT del.ID FROM #ToBeDeleted)
;
--===== Capture the number of rows deleted and bump the counter.
SELECT @RowCount = @@ROWCOUNT
,@Counter = @Counter + 1
;
--===== Get ready for the next pass, if there is one
TRUNCATE TABLE #ToBeDeleted
;
--===== Display the stats for this pass.
SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE());
RAISERROR ('%u rows deleted in %uMS.',0,0,@RowCount,@DurationMS) WITH NOWAIT;
RAISERROR ('------------------------------------------------',0,0) WITH NOWAIT
;
--===== Delay if you want but I don't believe you'll need it.
WAITFOR DELAY '00:00:01'
;
END --End of "Delete Loop"
;
PRINT 'RUN COMPLETE'
;
GO
No... it's not the shortest code that it could be. It's some of the easiest to maintain/troubleshoot, safest, and will have good for performance over the lifetime of the code.
--Jeff Moden
October 31, 2016 at 8:26 am
Suth (10/28/2016)
indicies ? do you mean indexes ?
Indices is the original Latin plural of index. Indexes is the Anglicized version of the plural. Both are acceptable in English. There are a small number of such words in English. Vertex (vertices and vertexes) is another example.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 31, 2016 at 8:30 am
no foreign keys are being used
October 31, 2016 at 9:32 am
Quick suggestion, reading the data to be deleted into a "bucket" variables can drastically speed up the delete, here is an example based on the DDL previously posted.
😎
DECLARE @id INT ;
DECLARE @created DATETIME ;
DECLARE @createdby INT ;
DECLARE @updated DATETIME ;
DECLARE @updatedBy INT ;
DECLARE @orderId INT ;
DECLARE @title VARCHAR(30) ;
DECLARE @qty NUMERIC(18, 0) ;
DECLARE @startDate DATETIME ;
DECLARE @endDate DATETIME ;
DECLARE @startTime VARCHAR(8) ;
DECLARE @endTime VARCHAR(8) ;
DECLARE @notes NTEXT ;
DECLARE @deletedFlag SMALLINT ;
DECLARE @recordType VARCHAR(30) ;
DECLARE @recycledFlag BIT ;
DECLARE @hardDeletedFlag BIT ;
DECLARE @importId INT ;
DECLARE @attTXT01 NVARCHAR(30) ;
DECLARE @attTXT02 NVARCHAR(30) ;
DECLARE @attTXT03 NVARCHAR(30) ;
DECLARE @attTXT04 NVARCHAR(30) ;
DECLARE @attTXT05 NVARCHAR(30) ;
DECLARE @attTXT06 NVARCHAR(30) ;
DECLARE @attTXT07 NVARCHAR(30) ;
DECLARE @attTXT08 NVARCHAR(30) ;
DECLARE @attTXT09 NVARCHAR(30) ;
DECLARE @attTXT10 NVARCHAR(30) ;
DECLARE @attTXT11 NVARCHAR(30) ;
DECLARE @attTXT12 NVARCHAR(30) ;
DECLARE @attTXT13 NVARCHAR(30) ;
DECLARE @attTXT14 NVARCHAR(30) ;
DECLARE @attTXT15 NVARCHAR(30) ;
DECLARE @attTXT16 NVARCHAR(30) ;
DECLARE @attTXT17 NVARCHAR(30) ;
DECLARE @attTXT18 NVARCHAR(30) ;
DECLARE @attTXT19 NVARCHAR(30) ;
DECLARE @attTXT20 NVARCHAR(30) ;
DECLARE @attTXT21 NVARCHAR(30) ;
DECLARE @attTXT22 NVARCHAR(30) ;
DECLARE @attTXT23 NVARCHAR(30) ;
DECLARE @attTXT24 NVARCHAR(30) ;
DECLARE @attTXT25 NVARCHAR(30) ;
DECLARE @attTXT26 NVARCHAR(30) ;
DECLARE @attTXT27 NVARCHAR(30) ;
DECLARE @attTXT28 NVARCHAR(30) ;
DECLARE @attTXT29 NVARCHAR(240) ;
DECLARE @attTXT30 NVARCHAR(240) ;
DECLARE @attTXT31 NVARCHAR(240) ;
DECLARE @attTXT32 NVARCHAR(240) ;
DECLARE @attTXT33 NVARCHAR(240) ;
DECLARE @attTXT34 NVARCHAR(240) ;
DECLARE @attTXT35 NVARCHAR(240) ;
DECLARE @attTXT36 NVARCHAR(240) ;
DECLARE @attTXT37 NVARCHAR(240) ;
DECLARE @attTXT38 NVARCHAR(240) ;
DECLARE @attTXT39 NVARCHAR(240) ;
DECLARE @attTXT40 NVARCHAR(50) ;
DECLARE @attTXT41 NVARCHAR(50) ;
DECLARE @attTXT42 NVARCHAR(50) ;
DECLARE @attTXT43 NVARCHAR(50) ;
DECLARE @attTXT44 NVARCHAR(50) ;
DECLARE @attTXT45 NVARCHAR(50) ;
DECLARE @attTXT46 NVARCHAR(50) ;
DECLARE @attTXT47 NVARCHAR(50) ;
DECLARE @attTXT48 NVARCHAR(50) ;
DECLARE @attTXT49 NVARCHAR(50) ;
DECLARE @attTXT50 NVARCHAR(50) ;
DECLARE @attTXT51 NVARCHAR(50) ;
DECLARE @attTXT52 NVARCHAR(50) ;
DECLARE @attTXT53 NVARCHAR(50) ;
DECLARE @attTXT54 NVARCHAR(50) ;
DECLARE @attTXT55 NVARCHAR(50) ;
DECLARE @attTXT56 NVARCHAR(50) ;
DECLARE @attTXT57 NVARCHAR(50) ;
DECLARE @attTXT58 NVARCHAR(50) ;
DECLARE @attTXT59 NVARCHAR(50) ;
DECLARE @attTXT60 NVARCHAR(50) ;
DECLARE @attTXT61 NVARCHAR(100) ;
DECLARE @attTXT62 NVARCHAR(100) ;
DECLARE @attTXT63 NVARCHAR(100) ;
DECLARE @attTXT64 NVARCHAR(100) ;
DECLARE @attTXT65 NVARCHAR(100) ;
DECLARE @attTXT66 NTEXT ;
DECLARE @attTXT67 NTEXT ;
DECLARE @attTXT68 NTEXT ;
DECLARE @attTXT69 NTEXT ;
DECLARE @attTXT70 NTEXT ;
DECLARE @attDT01 SMALLDATETIME ;
DECLARE @attDT02 SMALLDATETIME ;
DECLARE @attDT03 SMALLDATETIME ;
DECLARE @attDT04 SMALLDATETIME ;
DECLARE @attDT05 SMALLDATETIME ;
DECLARE @attDT06 SMALLDATETIME ;
DECLARE @attDT07 SMALLDATETIME ;
DECLARE @attDT08 SMALLDATETIME ;
DECLARE @attDT09 SMALLDATETIME ;
DECLARE @attDT10 SMALLDATETIME ;
DECLARE @attDT11 SMALLDATETIME ;
DECLARE @attDT12 SMALLDATETIME ;
DECLARE @attDT13 SMALLDATETIME ;
DECLARE @attDT14 SMALLDATETIME ;
DECLARE @attDT15 SMALLDATETIME ;
DECLARE @attDT16 SMALLDATETIME ;
DECLARE @attDT17 SMALLDATETIME ;
DECLARE @attDT18 SMALLDATETIME ;
DECLARE @attDT19 SMALLDATETIME ;
DECLARE @attDT20 SMALLDATETIME ;
DECLARE @attNum01 REAL ;
DECLARE @attNum02 REAL ;
DECLARE @attNum03 REAL ;
DECLARE @attNum04 REAL ;
DECLARE @attNum05 REAL ;
DECLARE @attNum06 REAL ;
DECLARE @attNum07 REAL ;
DECLARE @attNum08 REAL ;
DECLARE @attNum09 REAL ;
DECLARE @attNum10 REAL ;
DECLARE @attNum11 REAL ;
DECLARE @attNum12 REAL ;
DECLARE @attNum13 REAL ;
DECLARE @attNum14 REAL ;
DECLARE @attNum15 REAL ;
DECLARE @attNum16 REAL ;
DECLARE @attNum17 REAL ;
DECLARE @attNum18 REAL ;
DECLARE @attNum19 REAL ;
DECLARE @attNum20 REAL ;
DECLARE @attNum21 REAL ;
DECLARE @attNum22 REAL ;
DECLARE @attNum23 REAL ;
DECLARE @attNum24 REAL ;
DECLARE @attNum25 REAL ;
DECLARE @attNum26 REAL ;
DECLARE @attNum27 REAL ;
DECLARE @attNum28 REAL ;
DECLARE @attNum29 REAL ;
DECLARE @attNum30 REAL ;
DECLARE @attBIT01 BIT ;
DECLARE @attBIT02 BIT ;
DECLARE @attBIT03 BIT ;
DECLARE @attBIT04 BIT ;
DECLARE @attBIT05 BIT ;
DECLARE @attBIT06 BIT ;
DECLARE @attBIT07 BIT ;
DECLARE @attBIT08 BIT ;
DECLARE @attBIT09 BIT ;
DECLARE @attBIT10 BIT ;
SELECT
@id = OD.id
,@created = OD.created
,@createdBy = OD.createdBy
,@updated = OD.updated
,@updatedBy = OD.updatedBy
,@orderId = OD.orderId
,@title = OD.title
,@qty = OD.qty
,@startDate = OD.startDate
,@endDate = OD.endDate
,@startTime = OD.startTime
,@endTime = OD.endTime
,@notes = OD.notes
,@deletedFlag = OD.deletedFlag
,@recordType = OD.recordType
,@recycledFlag = OD.recycledFlag
,@hardDeletedFlag = OD.hardDeletedFlag
,@importId = OD.importId
,@attTXT01 = OD.attTXT01
,@attTXT02 = OD.attTXT02
,@attTXT03 = OD.attTXT03
,@attTXT04 = OD.attTXT04
,@attTXT05 = OD.attTXT05
,@attTXT06 = OD.attTXT06
,@attTXT07 = OD.attTXT07
,@attTXT08 = OD.attTXT08
,@attTXT09 = OD.attTXT09
,@attTXT10 = OD.attTXT10
,@attTXT11 = OD.attTXT11
,@attTXT12 = OD.attTXT12
,@attTXT13 = OD.attTXT13
,@attTXT14 = OD.attTXT14
,@attTXT15 = OD.attTXT15
,@attTXT16 = OD.attTXT16
,@attTXT17 = OD.attTXT17
,@attTXT18 = OD.attTXT18
,@attTXT19 = OD.attTXT19
,@attTXT20 = OD.attTXT20
,@attTXT21 = OD.attTXT21
,@attTXT22 = OD.attTXT22
,@attTXT23 = OD.attTXT23
,@attTXT24 = OD.attTXT24
,@attTXT25 = OD.attTXT25
,@attTXT26 = OD.attTXT26
,@attTXT27 = OD.attTXT27
,@attTXT28 = OD.attTXT28
,@attTXT29 = OD.attTXT29
,@attTXT30 = OD.attTXT30
,@attTXT31 = OD.attTXT31
,@attTXT32 = OD.attTXT32
,@attTXT33 = OD.attTXT33
,@attTXT34 = OD.attTXT34
,@attTXT35 = OD.attTXT35
,@attTXT36 = OD.attTXT36
,@attTXT37 = OD.attTXT37
,@attTXT38 = OD.attTXT38
,@attTXT39 = OD.attTXT39
,@attTXT40 = OD.attTXT40
,@attTXT41 = OD.attTXT41
,@attTXT42 = OD.attTXT42
,@attTXT43 = OD.attTXT43
,@attTXT44 = OD.attTXT44
,@attTXT45 = OD.attTXT45
,@attTXT46 = OD.attTXT46
,@attTXT47 = OD.attTXT47
,@attTXT48 = OD.attTXT48
,@attTXT49 = OD.attTXT49
,@attTXT50 = OD.attTXT50
,@attTXT51 = OD.attTXT51
,@attTXT52 = OD.attTXT52
,@attTXT53 = OD.attTXT53
,@attTXT54 = OD.attTXT54
,@attTXT55 = OD.attTXT55
,@attTXT56 = OD.attTXT56
,@attTXT57 = OD.attTXT57
,@attTXT58 = OD.attTXT58
,@attTXT59 = OD.attTXT59
,@attTXT60 = OD.attTXT60
,@attTXT61 = OD.attTXT61
,@attTXT62 = OD.attTXT62
,@attTXT63 = OD.attTXT63
,@attTXT64 = OD.attTXT64
,@attTXT65 = OD.attTXT65
,@attTXT66 = OD.attTXT66
,@attTXT67 = OD.attTXT67
,@attTXT68 = OD.attTXT68
,@attTXT69 = OD.attTXT69
,@attTXT70 = OD.attTXT70
,@attDT01 = OD.attDT01
,@attDT02 = OD.attDT02
,@attDT03 = OD.attDT03
,@attDT04 = OD.attDT04
,@attDT05 = OD.attDT05
,@attDT06 = OD.attDT06
,@attDT07 = OD.attDT07
,@attDT08 = OD.attDT08
,@attDT09 = OD.attDT09
,@attDT10 = OD.attDT10
,@attDT11 = OD.attDT11
,@attDT12 = OD.attDT12
,@attDT13 = OD.attDT13
,@attDT14 = OD.attDT14
,@attDT15 = OD.attDT15
,@attDT16 = OD.attDT16
,@attDT17 = OD.attDT17
,@attDT18 = OD.attDT18
,@attDT19 = OD.attDT19
,@attDT20 = OD.attDT20
,@attNum01 = OD.attNum01
,@attNum02 = OD.attNum02
,@attNum03 = OD.attNum03
,@attNum04 = OD.attNum04
,@attNum05 = OD.attNum05
,@attNum06 = OD.attNum06
,@attNum07 = OD.attNum07
,@attNum08 = OD.attNum08
,@attNum09 = OD.attNum09
,@attNum10 = OD.attNum10
,@attNum11 = OD.attNum11
,@attNum12 = OD.attNum12
,@attNum13 = OD.attNum13
,@attNum14 = OD.attNum14
,@attNum15 = OD.attNum15
,@attNum16 = OD.attNum16
,@attNum17 = OD.attNum17
,@attNum18 = OD.attNum18
,@attNum19 = OD.attNum19
,@attNum20 = OD.attNum20
,@attNum21 = OD.attNum21
,@attNum22 = OD.attNum22
,@attNum23 = OD.attNum23
,@attNum24 = OD.attNum24
,@attNum25 = OD.attNum25
,@attNum26 = OD.attNum26
,@attNum27 = OD.attNum27
,@attNum28 = OD.attNum28
,@attNum29 = OD.attNum29
,@attNum30 = OD.attNum30
,@attBIT01 = OD.attBIT01
,@attBIT02 = OD.attBIT02
,@attBIT03 = OD.attBIT03
,@attBIT04 = OD.attBIT04
,@attBIT05 = OD.attBIT05
,@attBIT06 = OD.attBIT06
,@attBIT07 = OD.attBIT07
,@attBIT08 = OD.attBIT08
,@attBIT09 = OD.attBIT09
,@attBIT10 = OD.attBIT10
FROM dbo.u_od OD WITH (NOLOCK)
-- Add a where clause if applicable;
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply