SQL DDL Scripting for Automation

• Build objects in dependency order: database, schema, parent tables, child tables, constraints, indexes and views.
• Validate existing rows before adding stricter constraints or changing data types.
• Keep DDL out of cyclic SCADA Runtime code; deploy it through a controlled migration.
• Verify results from SQL Server catalog views, not only from a “Command completed” message.

1. What Is DDL?

DDL means Data Definition Language. It defines or changes the structure and metadata of database objects. The most familiar DDL verbs are CREATE, ALTER, DROP and TRUNCATE.

DDL is broader than tables. It also creates or changes schemas, constraints, indexes, views, procedures, functions, triggers, sequences, users and many other SQL Server objects.

2. DDL vs DML, DCL and TCL

3. Understand Objects and Deployment Order

SQL Server cannot create a child relationship before the referenced parent object exists. A predictable automation deployment normally follows this sequence:

1. Create or select the database.
2. Create custom schemas if required.
3. Create reference tables such as Equipment, Shift and AlarmPriority.
4. Create transactional tables such as tblEvent.
5. Clean or seed required data.
6. Add foreign keys and business constraints.
7. Create workload-driven indexes.
8. Create views, procedures and reporting objects.
9. Grant the minimum application permissions.
10. Run verification queries and record the migration version.

Use two-part names such as dbo.tblEvent. An unqualified table name can resolve differently for users with different default schemas.

4. Create a Database and Schema

For a new lab, create the database from the master context. Keep production file sizing, recovery model, backup and high-availability decisions under DBA control.

USE [master];
GO

IF DB_ID(N'SQF_DB') IS NULL
BEGIN
    CREATE DATABASE [SQF_DB];
END;
GO

USE [SQF_DB];
GO

IF SCHEMA_ID(N'automation') IS NULL
    EXEC(N'CREATE SCHEMA automation AUTHORIZATION dbo;');
GO

A custom schema can separate application objects from dbo, but this tutorial keeps dbo.tblEvent to match the supplied table reference.

5. CREATE TABLE: Build tblEvent

The attached DDL uses tblEvent as a furnace or process event table. The following beginner version preserves that model while using datetime2(3), fixed-precision engineering values and a binary status flag.

USE [SQF_DB];
GO

CREATE TABLE [dbo].[tblEvent]
(
    [ID]             int IDENTITY(1,1) NOT NULL,
    [DT]             datetime2(3) NOT NULL,
    [TM]             varchar(10) NULL, -- legacy display value; DT remains authoritative
    [SQF_No]         int NULL,
    [ChargeNo]       varchar(50) NULL,
    [Event_From]     varchar(100) NULL,
    [Event_To]       varchar(100) NULL,
    [Temp_Set]       decimal(9,3) NULL,
    [Temp_Act]       decimal(9,3) NULL,
    [Cp_Set]         decimal(9,3) NULL,
    [Cp_Act]         decimal(9,3) NULL,
    [Oil_Set]        decimal(9,3) NULL,
    [Oil_Act]        decimal(9,3) NULL,
    [Jacket_Set]     decimal(9,3) NULL,
    [Jacket_Act]     decimal(9,3) NULL,
    [Fan_Status]     bit NULL,
    CONSTRAINT [PK_tblEvent] PRIMARY KEY CLUSTERED ([ID])
);
GO

6. ALTER TABLE: Add, Change and Drop Columns

Add a column

ALTER TABLE [dbo].[tblEvent]
ADD [OperatorName] nvarchar(100) NULL;
GO

Change a type or length

-- First confirm that existing values fit the new definition.
SELECT MAX(LEN([Event_From])) AS MaxEventFromLength
FROM [dbo].[tblEvent];

ALTER TABLE [dbo].[tblEvent]
ALTER COLUMN [Event_From] varchar(150) NULL;
GO

Drop a column

-- Check dependencies before removal.
ALTER TABLE [dbo].[tblEvent]
DROP COLUMN [OperatorName];
GO

7. Add Rules with Constraints

Data types define representation; constraints enforce additional rules. Give constraints explicit, predictable names so future migrations can reference them reliably.

ALTER TABLE [dbo].[tblEvent]
ADD CONSTRAINT [DF_tblEvent_DT]
    DEFAULT (SYSUTCDATETIME()) FOR [DT];

ALTER TABLE [dbo].[tblEvent]
ADD CONSTRAINT [CK_tblEvent_TempAct]
    CHECK ([Temp_Act] IS NULL OR [Temp_Act] BETWEEN -100.000 AND 1800.000);

-- Add only if the process truly guarantees one event at this grain.
ALTER TABLE [dbo].[tblEvent]
ADD CONSTRAINT [UQ_tblEvent_Charge_DT]
    UNIQUE ([ChargeNo], [DT]);
GO

A default acts only when an insert omits the column; it does not repair existing NULL values. A unique constraint is a business rule, not merely a performance setting.

8. Create the Equipment Foreign Key

Create the parent table first, seed it and check for orphaned child values before adding the relationship.

CREATE TABLE [dbo].[Equipment]
(
    [EquipmentID]   int NOT NULL,
    [EquipmentName] nvarchar(100) NOT NULL,
    [IsActive]      bit NOT NULL
        CONSTRAINT [DF_Equipment_IsActive] DEFAULT (1),
    CONSTRAINT [PK_Equipment] PRIMARY KEY ([EquipmentID]),
    CONSTRAINT [UQ_Equipment_Name] UNIQUE ([EquipmentName])
);
GO

-- Find orphaned values before adding the foreign key.
SELECT DISTINCT e.[SQF_No]
FROM [dbo].[tblEvent] AS e
LEFT JOIN [dbo].[Equipment] AS q
    ON q.[EquipmentID] = e.[SQF_No]
WHERE e.[SQF_No] IS NOT NULL
  AND q.[EquipmentID] IS NULL;

ALTER TABLE [dbo].[tblEvent] WITH CHECK
ADD CONSTRAINT [FK_tblEvent_Equipment]
    FOREIGN KEY ([SQF_No])
    REFERENCES [dbo].[Equipment] ([EquipmentID]);

ALTER TABLE [dbo].[tblEvent]
CHECK CONSTRAINT [FK_tblEvent_Equipment];
GO

A foreign key does not automatically create an index on the child column. Add one when join and filter workload justifies it.

9. Add a Computed Column

A computed column derives its value from other columns. PERSISTED stores the result physically and updates it when dependent values change.

ALTER TABLE [dbo].[tblEvent]
ADD [TempDeviation] AS
    (CONVERT(decimal(10,3), [Temp_Act] - [Temp_Set])) PERSISTED;
GO

SELECT [ID], [Temp_Set], [Temp_Act], [TempDeviation]
FROM [dbo].[tblEvent];

SELECT
    COLUMNPROPERTY(OBJECT_ID(N'dbo.tblEvent'), N'TempDeviation', 'IsDeterministic') AS IsDeterministic,
    COLUMNPROPERTY(OBJECT_ID(N'dbo.tblEvent'), N'TempDeviation', 'IsPrecise')       AS IsPrecise;
GO

10. Create Indexes for Real Queries

CREATE INDEX is DDL, but indexing is a workload decision. Every index consumes storage and adds work to inserts and updates. Begin with the actual SCADA/report queries.

-- Useful for charge lookups.
CREATE INDEX [IX_tblEvent_ChargeNo]
ON [dbo].[tblEvent] ([ChargeNo]);

-- Useful for equipment time-range queries.
CREATE INDEX [IX_tblEvent_SQFNo_DT]
ON [dbo].[tblEvent] ([SQF_No], [DT])
INCLUDE ([ChargeNo], [Temp_Set], [Temp_Act], [Fan_Status]);
GO

Column order matters. The index (SQF_No, DT) supports an equality filter on equipment followed by a timestamp range. Test execution plans and write overhead before adding more indexes.

11. CREATE OR ALTER VIEW for Reporting

A view gives reporting clients a stable, permission-friendly interface. In supported SQL Server versions, CREATE OR ALTER VIEW removes separate create-versus-alter branching.

CREATE OR ALTER VIEW [dbo].[vw_ChargeSummary]
AS
SELECT
    [ChargeNo],
    [SQF_No],
    MIN([DT]) AS [StartTime],
    MAX([DT]) AS [EndTime],
    DATEDIFF(MINUTE, MIN([DT]), MAX([DT])) AS [DurationMinutes],
    MAX(CASE WHEN [Event_To] = 'Complete' THEN 1 ELSE 0 END) AS [IsComplete]
FROM [dbo].[tblEvent]
GROUP BY [ChargeNo], [SQF_No];
GO

SELECT TOP (20) *
FROM [dbo].[vw_ChargeSummary]
ORDER BY [StartTime] DESC;

A regular view stores its definition, not a separate copy of the result. Avoid SELECT * in long-lived interfaces because new or reordered base-table columns can surprise consumers.

12. Enable System-Versioned Temporal History

A system-versioned temporal table maintains a current table plus a history table. SQL Server requires a primary key and exactly one period made from two datetime2 columns.

ALTER TABLE [dbo].[tblEvent]
ADD
    [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT [DF_tblEvent_ValidFrom] DEFAULT (SYSUTCDATETIME()),
    [ValidTo] datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT [DF_tblEvent_ValidTo]
        DEFAULT (CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999')),
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]);
GO

ALTER TABLE [dbo].[tblEvent]
SET (SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = [dbo].[tblEvent_History], DATA_CONSISTENCY_CHECK = ON));
GO

History begins when system versioning is enabled; it cannot reconstruct changes that occurred before that time. Period values use UTC transaction begin times.

DECLARE @PointInTime datetime2(7) = DATEADD(HOUR, -1, SYSUTCDATETIME());

SELECT [ID], [DT], [SQF_No], [Temp_Act], [ValidFrom], [ValidTo]
FROM [dbo].[tblEvent]
FOR SYSTEM_TIME AS OF @PointInTime
ORDER BY [ID];
GO

13. Rename Objects Carefully

SQL Server does not provide a general ALTER ... RENAME statement for tables and columns. sp_rename can rename them, but it does not rewrite every dependent module or external SCADA reference.

-- Examples only: inventory dependencies first.
EXEC sys.sp_rename
    @objname = N'dbo.tblEvent.Fan_Status',
    @newname = N'FanStatus',
    @objtype = N'COLUMN';

EXEC sys.sp_rename
    @objname = N'dbo.tblEvent',
    @newname = N'tblFurnaceEvent';
GO

Search views, procedures, functions, jobs, reports, WinCC scripts, ODBC queries and application code before renaming. A safer compatibility migration often adds the new interface, moves consumers, monitors usage and removes the old interface later.

14. DELETE vs TRUNCATE vs DROP

-- DML: remove an approved time range.
DELETE FROM [dbo].[tblEvent]
WHERE [DT] < DATEADD(MONTH, -12, SYSUTCDATETIME());

-- DDL: all rows, no WHERE. Use only on an eligible disposable table.
TRUNCATE TABLE [dbo].[tblEvent_Stage];

-- DDL: remove the object itself.
DROP TABLE IF EXISTS [dbo].[tblEvent_Stage];
GO

15. Use Transactions and Test Rollback

Many SQL Server DDL statements participate in transactions. That does not make every migration low-risk: long-running schema changes can hold locks and consume log space. Test the exact script and rollback path.

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    ALTER TABLE [dbo].[tblEvent]
    ADD [MigrationTest] int NULL;

    -- Verification can run before COMMIT.
    IF COL_LENGTH(N'dbo.tblEvent', N'MigrationTest') IS NULL
        THROW 50001, 'MigrationTest column was not created.', 1;

    -- In a real approved deployment, use COMMIT here.
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
GO

16. Write Repeatable, Idempotent Migrations

An idempotent migration can be re-run without duplicating an object. Guard clauses help, but they are not a substitute for a migration history: if an object exists with the wrong definition, a simple existence check can hide drift.

IF COL_LENGTH(N'dbo.tblEvent', N'OperatorName') IS NULL
BEGIN
    ALTER TABLE [dbo].[tblEvent]
    ADD [OperatorName] nvarchar(100) NULL;
END;

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE [object_id] = OBJECT_ID(N'dbo.tblEvent')
      AND [name] = N'IX_tblEvent_SQFNo_DT'
)
BEGIN
    CREATE INDEX [IX_tblEvent_SQFNo_DT]
    ON [dbo].[tblEvent] ([SQF_No], [DT]);
END;
GO

Store each migration under source control with a version, description, author, test evidence and deployment status. Apply migrations once in sequence and compare the actual schema with the expected definition.

17. DDL Safety for PLC, SCADA and Historian Systems

18. Verify the Final Schema

Use SQL Server catalog views to prove that columns, data types, constraints, indexes and temporal settings match the intended design.

-- Columns and types
SELECT
    c.[column_id], c.[name] AS [ColumnName],
    t.[name] AS [DataType], c.[max_length],
    c.[precision], c.[scale], c.[is_nullable], c.[is_identity],
    c.[is_computed], c.[generated_always_type_desc]
FROM sys.columns AS c
JOIN sys.types AS t ON t.[user_type_id] = c.[user_type_id]
WHERE c.[object_id] = OBJECT_ID(N'dbo.tblEvent')
ORDER BY c.[column_id];

-- Constraints
SELECT [name], [type_desc], [is_disabled], [is_not_trusted]
FROM sys.objects
WHERE [parent_object_id] = OBJECT_ID(N'dbo.tblEvent')
  AND [type] IN ('PK','F','UQ','C','D');

-- Indexes
SELECT [name], [type_desc], [is_unique], [is_disabled]
FROM sys.indexes
WHERE [object_id] = OBJECT_ID(N'dbo.tblEvent')
  AND [index_id] > 0;

-- Temporal status: 0 = none, 2 = system-versioned temporal table
SELECT [name], [temporal_type_desc],
       OBJECT_SCHEMA_NAME([history_table_id]) AS [HistorySchema],
       OBJECT_NAME([history_table_id]) AS [HistoryTable]
FROM sys.tables
WHERE [object_id] = OBJECT_ID(N'dbo.tblEvent');
GO

19. Common DDL Errors and Fixes

CREATE TABLE [dbo].[Shift]
(
    [ShiftID]   tinyint NOT NULL,
    [ShiftName] varchar(30) NOT NULL,
    [StartTime] time(0) NOT NULL,
    [EndTime]   time(0) NOT NULL,
    CONSTRAINT [PK_Shift] PRIMARY KEY ([ShiftID]),
    CONSTRAINT [UQ_Shift_Name] UNIQUE ([ShiftName]),
    CONSTRAINT [CK_Shift_TimeRange] CHECK ([StartTime] <> [EndTime])
);
GO

CREATE OR ALTER VIEW [dbo].[vw_SuspiciousReadings]
AS
SELECT [ID], [DT], [SQF_No], [ChargeNo], [Temp_Set], [Temp_Act]
FROM [dbo].[tblEvent]
WHERE [Temp_Act] IS NULL
  AND [Temp_Set] IS NOT NULL;
GO

SELECT COL_LENGTH(N'dbo.tblEvent', N'MigrationTest') AS MigrationTestLength;
-- Expected after ROLLBACK: NULL