/* ================================================================================ Auto-shipping stream : schema (v2 - SENDCARTON aware) Target DB : ShipLinkDB Date : 2026-08-11 Tables only. No stored procedures, no sequences, no triggers - everything that decides anything lives in the formulas, where it can be stepped through in a debugger. WHAT CHANGED FROM v1 -------------------- v1 had one table, ShipmentData, holding one row per line off the wire, and that row was simultaneously the shipment, the parcel and the print unit. The eWarehouse SENDCARTON feed does not work that way: one record = one CARTON -> one Parcel records sharing ORDER -> one Order (+ one OrderDetail per record) records up to LASTTRAN = 'Y' -> one SHIPMENT, which may span many Orders So the three levels are now three tables and ShipmentData becomes the shipment header: ShipmentData one row per transaction (the unit of shipping) ShipmentOrder one row per ORDER -> Order ShipmentOrderDetail one row per part line -> OrderDetail ShipmentParcel one row per CARTON -> Parcel, and the unit of printing ShipmentOrderDetail also points at the ShipmentParcel it was reported on, which is what makes "what is in this carton" answerable - needed for international paperwork and for a commercial invoice. THE ORDERING GUARANTEE (unchanged in substance, now at two levels) ------------------------------------------------------------------- Labels must come off the printer in the order the records arrived. 1. ONE WRITER. IDENTITY is allocated at INSERT, not at COMMIT, so two concurrent inserters can commit out of Id order and a batch claimed in that window would split them. The listener formula funnels every insert through a single thread. Formulas must never INSERT into these tables directly. 2. A WHOLE SHIPMENT IS INSERTED IN ONE TRANSACTION, with its parcels in arrival order inside it. So a shipment's parcels get a consecutive block of identities before the next shipment gets any, which makes ShipmentParcel.Id on its own a monotonic sequence over every carton that ever arrives. PRINT ORDER IS ShipmentParcel.Id. It agrees with ShipmentData.Id order because of (1) and (2), so the printing loop needs only the one key. 3. THE PRINTING LOOP NEVER SKIPS. It refuses to print anything while a lower ShipmentParcel.Id is still unfinished, so a lost or interfered-with record halts the line instead of quietly reordering it. There is deliberately NO batch-level timeout. Passing over a slow batch to reach the next one is exactly what breaks the guarantee. The deadline lives on the individual shipment instead (ShipStartedAt + the carrier deadline), so every shipment reaches a terminal state in bounded time and therefore every batch drains. RAW DATA lives on ShipmentParcel.Data - one record, one parcel, so that is where it belongs. Every parsed column is derived from it and it is kept so a mis-parse can be diagnosed and replayed. Script is idempotent: guarded with IF NOT EXISTS. A v1 ShipmentData is renamed out of the way rather than dropped - see the first block. ================================================================================ */ -- QUOTED_IDENTIFIER and ANSI_NULLS are not decoration: every filtered index -- below fails with "CREATE INDEX failed because the following SET options have -- incorrect settings" without them. SSMS turns them on for you, sqlcmd and osql -- do not - so a script that relies on the session default works when a human -- runs it and fails when the installer does. SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; SET XACT_ABORT ON; GO -- --------------------------------------------------------------------------- -- v1 -> v2. The old ShipmentData held the raw record in a column called Data -- and had no OrderCount; that is how it is recognised. It is renamed, not -- dropped: it may still hold unshipped records and it is the only evidence of -- what arrived. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentData', 'U') IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ShipmentData') AND name = 'Data') AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ShipmentData') AND name = 'OrderCount') BEGIN IF OBJECT_ID('dbo.ShipmentData_v1', 'U') IS NOT NULL BEGIN THROW 50200, 'dbo.ShipmentData_v1 already exists. Deal with the previous rename before re-running this script.', 1; END -- The FK to ShipmentBatch travels with the table and would then constrain -- the archive against a live table: deleting a finished batch would be -- blocked by rows nobody is going to look at again. Drop it first. IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ShipmentData_ShipmentBatch') ALTER TABLE dbo.ShipmentData DROP CONSTRAINT FK_ShipmentData_ShipmentBatch; EXEC sp_rename 'dbo.ShipmentData', 'ShipmentData_v1'; -- sp_rename renames the TABLE and nothing else. Its primary key, check -- constraints and defaults keep their old names, and those names are unique -- per schema - so creating the v2 table below fails with "There is already -- an object named 'PK_ShipmentData'". Every one of them has to move out of -- the way as well. -- -- Driven off the catalog rather than a hand-written list because the v1 -- names are whatever the v1 script happened to create, and a list would -- silently miss anything added since. Indexes are deliberately not renamed: -- index names are scoped to their table, so they cannot collide. DECLARE @oldTableId INT = OBJECT_ID('dbo.ShipmentData_v1'); DECLARE @constraintName SYSNAME; DECLARE @renameSql NVARCHAR(500); DECLARE constraint_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT o.name FROM sys.objects o WHERE o.parent_object_id = @oldTableId AND o.type IN ('PK','UQ','C','D','F') AND o.name NOT LIKE '%[_]v1'; OPEN constraint_cursor; FETCH NEXT FROM constraint_cursor INTO @constraintName; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = @constraintName + '_v1') BEGIN SET @renameSql = N'EXEC sp_rename N''dbo.' + REPLACE(@constraintName, '''', '''''') + N''', N''' + REPLACE(@constraintName, '''', '''''') + N'_v1'', ''OBJECT'''; EXEC sp_executesql @renameSql; END FETCH NEXT FROM constraint_cursor INTO @constraintName; END CLOSE constraint_cursor; DEALLOCATE constraint_cursor; PRINT 'Renamed the v1 dbo.ShipmentData to dbo.ShipmentData_v1, and its constraints with it.'; END GO -- --------------------------------------------------------------------------- -- ShipmentBatch - unchanged from v1. A batch is a set of shipments claimed -- together by the batching formula and printed together. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentBatch', 'U') IS NULL BEGIN CREATE TABLE dbo.ShipmentBatch ( Id BIGINT IDENTITY(1,1) NOT NULL, -- Processing : shipments are with the carrier, nothing printed yet -- Printing : the printing loop owns this batch -- LabelPrinted : every parcel reached a terminal print state -- Failed : printing halted and needs a human Status VARCHAR(20) NOT NULL CONSTRAINT DF_ShipmentBatch_Status DEFAULT ('Processing'), -- Stamped once at claim time. A batch is sealed - nothing is ever added -- to it afterwards - so this can be trusted without recounting. RecordCount INT NOT NULL CONSTRAINT DF_ShipmentBatch_RecordCount DEFAULT (0), PCId INT NULL, CreatedDate DATETIME NOT NULL CONSTRAINT DF_ShipmentBatch_CreatedDate DEFAULT (GETUTCDATE()), PrintStartedAt DATETIME2(3) NULL, PrintCompletedAt DATETIME2(3) NULL, ErrorMessage NVARCHAR(4000) NULL, CONSTRAINT PK_ShipmentBatch PRIMARY KEY CLUSTERED (Id), CONSTRAINT CK_ShipmentBatch_Status CHECK (Status IN ('Processing','Printing','LabelPrinted','Failed')) ); END GO -- The printing loop's only batch lookup. Filtered so it stays small forever, -- however many finished batches pile up behind it. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentBatch_Open' AND object_id = OBJECT_ID('dbo.ShipmentBatch')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentBatch_Open ON dbo.ShipmentBatch (Id) INCLUDE (Status, RecordCount, PrintStartedAt) WHERE Status IN ('Processing','Printing'); END GO -- --------------------------------------------------------------------------- -- ShipmentData - the SHIPMENT header. One row per eWarehouse transaction. -- -- A transaction is the run of records the sender closes with LASTTRAN = 'Y'. -- It may contain one order or many; a many-order transaction is exactly the -- multi-order shipment case, and needs no separate representation. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentData', 'U') IS NULL BEGIN CREATE TABLE dbo.ShipmentData ( -- The arrival order, and therefore the print order. See the header. Id BIGINT IDENTITY(1,1) NOT NULL, -- The first order number in the transaction. Denormalised from -- ShipmentOrder so the common single-order lookup does not need a join; -- on a multi-order shipment the others are in ShipmentOrder and this is -- NOT sufficient to identify the shipment. Null only when not one -- record in the transaction parsed. OrderNumber VARCHAR(15) NULL, OrderCount INT NOT NULL CONSTRAINT DF_ShipmentData_OrderCount DEFAULT (0), ParcelCount INT NOT NULL CONSTRAINT DF_ShipmentData_ParcelCount DEFAULT (0), RecordCount INT NOT NULL CONSTRAINT DF_ShipmentData_RecordCount DEFAULT (0), -- Why the listener decided the transaction was finished. -- LastTran : LASTTRAN = 'Y'. The only clean close. -- ConnectionClosed : the sender hung up mid-transaction. -- IdleTimeout : nothing arrived for GroupIdleFlushSeconds. -- SizeCap : MaxRecordsPerShipment reached. -- Anything other than LastTran means the sender may still send more -- records for this transaction, which would then arrive as a second -- shipment. Treat those as suspect before shipping them. CloseReason VARCHAR(20) NOT NULL CONSTRAINT DF_ShipmentData_CloseReason DEFAULT ('LastTran'), -- Shipping lifecycle, owned by the carrier-facing code. -- Ready -> Processing -> Shipped | Failed ShipStatus VARCHAR(20) NOT NULL CONSTRAINT DF_ShipmentData_ShipStatus DEFAULT ('Ready'), -- Roll-up of the parcels' print states, owned by the printing loop, so -- the loop can skip a finished shipment without reading its parcels. -- Separate from ShipStatus so printing an error slip does not erase the -- fact that the shipment failed. -- Pending -> Sending -> LabelPrinted | NoLabel | PrintFailed PrintStatus VARCHAR(20) NOT NULL CONSTRAINT DF_ShipmentData_PrintStatus DEFAULT ('Pending'), -- Distinguishes "the carrier said no" from "the carrier never answered" -- once ShipStatus has settled on Failed. IsTimeOut BIT NOT NULL CONSTRAINT DF_ShipmentData_IsTimeOut DEFAULT (0), ShipmentBatch_Id BIGINT NULL, -- Diagnostic only - never sort by this. Its resolution is coarser than -- the arrival rate, so a burst off one connection shares a timestamp, -- and a backward NTP step would invert the order outright. Id is the -- ordering key. PCId INT NULL, CreatedDate DATETIME NOT NULL CONSTRAINT DF_ShipmentData_CreatedDate DEFAULT (GETUTCDATE()), BatchedAt DATETIME2(3) NULL, -- Starts the carrier deadline clock. The sweep measures against this. ShipStartedAt DATETIME2(3) NULL, ShippedAt DATETIME2(3) NULL, AttemptCount INT NOT NULL CONSTRAINT DF_ShipmentData_AttemptCount DEFAULT (0), ErrorMessage NVARCHAR(4000) NULL, -- Client ip:port, for tracing a bad transaction back to the sender. SourceEndpoint VARCHAR(64) NULL, CONSTRAINT PK_ShipmentData PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_ShipmentData_ShipmentBatch FOREIGN KEY (ShipmentBatch_Id) REFERENCES dbo.ShipmentBatch (Id), CONSTRAINT CK_ShipmentData_ShipStatus CHECK (ShipStatus IN ('Ready','Processing','Shipped','Failed')), CONSTRAINT CK_ShipmentData_PrintStatus CHECK (PrintStatus IN ('Pending','Sending','LabelPrinted','NoLabel','PrintFailed')), CONSTRAINT CK_ShipmentData_CloseReason CHECK (CloseReason IN ('LastTran','ConnectionClosed','IdleTimeout','SizeCap')) ); END GO -- --------------------------------------------------------------------------- -- ShipmentOrder - one row per distinct ORDER inside a shipment. Maps to the -- ShipLink Order entity. -- -- Column widths are the SENDCARTON field widths from -- e-warehouse_Clippership_Fields_Mapping.xlsx. Values are stored trimmed, so -- they always fit; a wider column here would only hide a layout change. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentOrder', 'U') IS NULL BEGIN CREATE TABLE dbo.ShipmentOrder ( Id BIGINT IDENTITY(1,1) NOT NULL, ShipmentData_Id BIGINT NOT NULL, -- 1-based position within the shipment, in arrival order. Id would say -- the same thing today, but only because one writer inserts them in one -- transaction; SeqNo says it because it was written to say it. SeqNo INT NOT NULL, OrderNumber VARCHAR(15) NOT NULL, -- ORDER OrderExt VARCHAR(10) NULL, -- ORDER_EXT AltOrder VARCHAR(16) NULL, -- ALTORDER StationId VARCHAR(3) NULL, -- STATION_ID OperatorId VARCHAR(10) NULL, -- OPERATOR -- Consignee. ShipToId VARCHAR(10) NULL, ShipToName VARCHAR(35) NULL, ShipToAddr1 VARCHAR(35) NULL, ShipToAddr2 VARCHAR(35) NULL, ShipToAddr3 VARCHAR(35) NULL, ShipToAttn VARCHAR(35) NULL, ShipToCity VARCHAR(25) NULL, ShipToState VARCHAR(10) NULL, ShipToZip VARCHAR(12) NULL, ShipToCountry VARCHAR(16) NULL, Phone VARCHAR(30) NULL, -- Sold-to / bill-to. Blank on a drop-ship, as in the sample record. CustName VARCHAR(35) NULL, CustAddr1 VARCHAR(35) NULL, CustAddr2 VARCHAR(35) NULL, CustAttn VARCHAR(35) NULL, CustCity VARCHAR(25) NULL, CustState VARCHAR(10) NULL, CustZip VARCHAR(12) NULL, CustCountry VARCHAR(16) NULL, CustomerNr VARCHAR(10) NULL, Shipper VARCHAR(9) NULL, -- Service selection and billing. Carrier VARCHAR(10) NULL, -- CARRIER, e.g. U11P Mode VARCHAR(2) NULL, ChargeCode VARCHAR(1) NULL, InvoiceCode VARCHAR(1) NULL, FreightType VARCHAR(10) NULL, DestZone VARCHAR(50) NULL, ThirdPartyBillAcct VARCHAR(50) NULL, -- 3PB_ACCOUNT CustPo VARCHAR(15) NULL, -- CUST_PO CustPoNo VARCHAR(20) NULL, -- CUST_PONO Custom1 VARCHAR(50) NULL, Custom2 VARCHAR(50) NULL, Custom3 VARCHAR(50) NULL, Custom4 VARCHAR(250) NULL, -- TOTORDVAL parsed, plus the source text. The text is kept because a -- value that would not parse is a layout problem, and the number alone -- cannot tell you that. TotOrdVal DECIMAL(18,4) NULL, TotOrdValRaw VARCHAR(13) NULL, -- CARTON_TOTAL as the sender declared it, against which the parcels -- actually received can be checked. DeclaredCartonTotal INT NULL, -- A record with LASTCART = 'Y' was seen for this order. False means the -- transaction closed mid-order: the order is short some cartons. SawLastCarton BIT NOT NULL CONSTRAINT DF_ShipmentOrder_SawLastCarton DEFAULT (0), ErrorMessage NVARCHAR(1000) NULL, CONSTRAINT PK_ShipmentOrder PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_ShipmentOrder_ShipmentData FOREIGN KEY (ShipmentData_Id) REFERENCES dbo.ShipmentData (Id), CONSTRAINT UQ_ShipmentOrder_Seq UNIQUE (ShipmentData_Id, SeqNo) ); END GO -- --------------------------------------------------------------------------- -- ShipmentParcel - one row per CARTON record. Maps to the ShipLink Parcel -- entity, and is the unit the printing loop prints. -- -- ShipmentOrder_Id is nullable: a record that failed to parse still lands here, -- with Data and ErrorMessage populated and no order to attach it to. Dropping -- it at the socket would lose it permanently - the sender will not send it -- again - and leave nothing to diagnose. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentParcel', 'U') IS NULL BEGIN CREATE TABLE dbo.ShipmentParcel ( -- Print order within the shipment. See the header. Id BIGINT IDENTITY(1,1) NOT NULL, ShipmentData_Id BIGINT NOT NULL, ShipmentOrder_Id BIGINT NULL, -- 1-based arrival position within the shipment, across all its orders. SeqNo INT NOT NULL, CartonId VARCHAR(10) NULL, -- CARTON -> Parcel.ERPPackageId CartonSeq INT NULL, -- CARTON_SEQ ("carton n" CartonTotal INT NULL, -- CARTON_TOTAL "of m") Weight DECIMAL(18,7) NULL, WeightRaw VARCHAR(9) NULL, Length DECIMAL(18,4) NULL, Width DECIMAL(18,4) NULL, Height DECIMAL(18,4) NULL, DimUnit VARCHAR(2) NULL, Cost DECIMAL(18,4) NULL, TotVal DECIMAL(18,4) NULL, IsLastCarton BIT NOT NULL -- LASTCART = 'Y' CONSTRAINT DF_ShipmentParcel_IsLastCarton DEFAULT (0), IsLastTran BIT NOT NULL -- LASTTRAN = 'Y' CONSTRAINT DF_ShipmentParcel_IsLastTran DEFAULT (0), -- Printing lifecycle for this label. PrintStatus VARCHAR(20) NOT NULL CONSTRAINT DF_ShipmentParcel_PrintStatus DEFAULT ('Pending'), ZPL VARCHAR(MAX) NULL, TrackingNumber VARCHAR(50) NULL, CarrierCode VARCHAR(20) NULL, -- Stamped BEFORE the write to the printer, so a row with this set and -- PrintedAt null after a restart is identifiable as the ambiguous case: -- the label may or may not have physically come out. PrintSentAt DATETIME2(3) NULL, PrintedAt DATETIME2(3) NULL, -- The record exactly as it came off the wire, line wraps removed. -- Single-byte on purpose: fixed offsets only mean anything in a -- single-byte encoding. Every parsed column above is derived from this. Data VARCHAR(2000) NOT NULL, ErrorMessage NVARCHAR(1000) NULL, CONSTRAINT PK_ShipmentParcel PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_ShipmentParcel_ShipmentData FOREIGN KEY (ShipmentData_Id) REFERENCES dbo.ShipmentData (Id), CONSTRAINT FK_ShipmentParcel_ShipmentOrder FOREIGN KEY (ShipmentOrder_Id) REFERENCES dbo.ShipmentOrder (Id), CONSTRAINT UQ_ShipmentParcel_Seq UNIQUE (ShipmentData_Id, SeqNo), CONSTRAINT CK_ShipmentParcel_PrintStatus CHECK (PrintStatus IN ('Pending','Sending','LabelPrinted','NoLabel','PrintFailed')) ); END GO -- --------------------------------------------------------------------------- -- ShipmentOrderDetail - one row per part line. Maps to the ShipLink -- OrderDetail entity. -- -- SENDCARTON carries exactly one part per record, so there is one detail per -- record, and ShipmentParcel_Id says which carton it was reported on. Two -- cartons of the same part therefore produce two rows, which is the carton -- contents rather than a duplicate - do not fold them together without -- deciding what the carrier should be told. -- --------------------------------------------------------------------------- IF OBJECT_ID('dbo.ShipmentOrderDetail', 'U') IS NULL BEGIN CREATE TABLE dbo.ShipmentOrderDetail ( Id BIGINT IDENTITY(1,1) NOT NULL, ShipmentData_Id BIGINT NOT NULL, ShipmentOrder_Id BIGINT NOT NULL, ShipmentParcel_Id BIGINT NULL, -- 1-based position within the order, in arrival order. SeqNo INT NOT NULL, PartNumber VARCHAR(15) NULL, -- PART_NR PartDesc VARCHAR(35) NULL, -- PART_DESC Upc VARCHAR(11) NULL, -- UNIT_TOTAL. Whether this is units in this carton or units on the -- whole order is not settled by the sample - see the note in -- 01b_AutoShipStreamSendCarton.cs. Confirm before it drives a customs -- value. UnitTotal INT NULL, UnitTotalRaw VARCHAR(5) NULL, TotVal DECIMAL(18,4) NULL, TotValRaw VARCHAR(13) NULL, CONSTRAINT PK_ShipmentOrderDetail PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_ShipmentOrderDetail_ShipmentData FOREIGN KEY (ShipmentData_Id) REFERENCES dbo.ShipmentData (Id), CONSTRAINT FK_ShipmentOrderDetail_ShipmentOrder FOREIGN KEY (ShipmentOrder_Id) REFERENCES dbo.ShipmentOrder (Id), CONSTRAINT FK_ShipmentOrderDetail_ShipmentParcel FOREIGN KEY (ShipmentParcel_Id) REFERENCES dbo.ShipmentParcel (Id), CONSTRAINT UQ_ShipmentOrderDetail_Seq UNIQUE (ShipmentOrder_Id, SeqNo) ); END GO -- --------------------------------------------------------------------------- -- Indexes -- --------------------------------------------------------------------------- -- The batching formula's claim: TOP (n) ... WHERE ShipStatus = 'Ready' ORDER BY Id. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentData_Ready' AND object_id = OBJECT_ID('dbo.ShipmentData')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentData_Ready ON dbo.ShipmentData (Id) WHERE ShipStatus = 'Ready'; END GO -- The printing loop's "is anything earlier still unfinished?" check, which runs -- once per batch pass and must not scan the table. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentData_Unprinted' AND object_id = OBJECT_ID('dbo.ShipmentData')) BEGIN -- Two <> rather than NOT IN: a filtered index predicate accepts IN but not -- NOT IN, so the obvious spelling fails outright with "Incorrect syntax -- near 'NOT'". PrintStatus is NOT NULL, so these are equivalent. CREATE NONCLUSTERED INDEX IX_ShipmentData_Unprinted ON dbo.ShipmentData (Id) INCLUDE (ShipmentBatch_Id, ShipStatus) WHERE PrintStatus <> 'LabelPrinted' AND PrintStatus <> 'NoLabel'; END GO -- The carrier deadline sweep: in-flight shipments ordered by when they started. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentData_InFlight' AND object_id = OBJECT_ID('dbo.ShipmentData')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentData_InFlight ON dbo.ShipmentData (ShipStartedAt) INCLUDE (ShipmentBatch_Id, BatchedAt) WHERE ShipStatus = 'Processing'; END GO -- The per-batch read, in print order. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentData_Batch' AND object_id = OBJECT_ID('dbo.ShipmentData')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentData_Batch ON dbo.ShipmentData (ShipmentBatch_Id, Id) INCLUDE (ShipStatus, PrintStatus); END GO IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentData_OrderNumber' AND object_id = OBJECT_ID('dbo.ShipmentData')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentData_OrderNumber ON dbo.ShipmentData (OrderNumber, Id); END GO -- Load a shipment's orders, in order. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentOrder_Shipment' AND object_id = OBJECT_ID('dbo.ShipmentOrder')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentOrder_Shipment ON dbo.ShipmentOrder (ShipmentData_Id, SeqNo); END GO -- Find a shipment from an order number on a multi-order shipment, where -- ShipmentData.OrderNumber only carries the first one. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentOrder_OrderNumber' AND object_id = OBJECT_ID('dbo.ShipmentOrder')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentOrder_OrderNumber ON dbo.ShipmentOrder (OrderNumber) INCLUDE (ShipmentData_Id); END GO -- The printing loop's per-shipment parcel read, in print order. Excludes ZPL: -- a large batch would otherwise pull every label into memory at once. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentParcel_Shipment' AND object_id = OBJECT_ID('dbo.ShipmentParcel')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentParcel_Shipment ON dbo.ShipmentParcel (ShipmentData_Id, Id) INCLUDE (ShipmentOrder_Id, PrintStatus, TrackingNumber); END GO IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentParcel_Order' AND object_id = OBJECT_ID('dbo.ShipmentParcel')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentParcel_Order ON dbo.ShipmentParcel (ShipmentOrder_Id, Id); END GO -- CARTONRESPONSE and UNFREIGHT both key off the carton number, so it has to be -- findable on its own. Not unique: nothing in the feed guarantees the sender -- will not reuse one, and a duplicate must be diagnosable rather than rejected -- at 3am. IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentParcel_CartonId' AND object_id = OBJECT_ID('dbo.ShipmentParcel')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentParcel_CartonId ON dbo.ShipmentParcel (CartonId) INCLUDE (ShipmentData_Id, ShipmentOrder_Id); END GO IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ShipmentOrderDetail_Parcel' AND object_id = OBJECT_ID('dbo.ShipmentOrderDetail')) BEGIN CREATE NONCLUSTERED INDEX IX_ShipmentOrderDetail_Parcel ON dbo.ShipmentOrderDetail (ShipmentParcel_Id); END GO /* -------------------------------------------------------------------------------- OPTIONAL: write-once guard on ShipmentBatch_Id. -------------------------------------------------------------------------------- Formulas are customer-authored and have direct table access. Moving a shipment between batches after the fact would reorder printed labels. Left commented out because a trigger is invisible at the call site and is exactly the kind of hidden database logic this design is trying to avoid - and because the printing loop already HALTS when it detects the harm. Enable it if you would rather the write be rejected than detected. CREATE TRIGGER dbo.trg_ShipmentData_ProtectBatch ON dbo.ShipmentData AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF NOT UPDATE(ShipmentBatch_Id) RETURN; IF EXISTS ( SELECT 1 FROM inserted i JOIN deleted d ON d.Id = i.Id WHERE d.ShipmentBatch_Id IS NOT NULL AND (i.ShipmentBatch_Id IS NULL OR i.ShipmentBatch_Id <> d.ShipmentBatch_Id) ) BEGIN ROLLBACK TRANSACTION; THROW 50100, 'ShipmentData.ShipmentBatch_Id is write-once. Changing it would reorder printed labels.', 1; END END */