SQL Data Types and Keys Explained

• Data types encode process rules, not only storage size.
• Foreign keys reject equipment identifiers that do not exist.
• A binary bit is correct only for a permanently two-state domain.
• A candidate key is a business guarantee, not a guess based on sample data.

1. What Is a SQL Data Type?

A SQL data type defines the kind of value a column, variable or parameter can hold. It determines valid range, storage representation, precision, supported operations, comparison behavior and conversion rules.

For automation data, select a type from the engineering meaning:

• What is the minimum and maximum possible value?
• Is the number exact or an approximate measurement?
• How many decimal places are meaningful?
• Can the value be unknown or unavailable?
• Does text require multiple languages?
• Is the timestamp UTC, local time or offset-aware?
• How many rows will be stored and how will they be queried?

2. Major SQL Server Data-Type Families

3. Integer Types and Ranges

Choose the smallest type that safely covers the approved lifetime range, not merely today's sample values. For an identity key in a high-frequency event table, estimate row growth before choosing int or bigint.

4. Exact and Approximate Numeric Types

decimal and numeric

decimal(p,s) and numeric(p,s) are synonyms. Precision p is total digits; scale s is digits to the right of the decimal point. SQL Server supports precision up to 38.

TemperatureSetpoint decimal(8,3), -- five integral + three fractional digits
ProductionTotal     decimal(18,3)

real and float

These are approximate binary floating-point types. They are suitable for many sensor values, but decimal fractions may not compare exactly. Do not use floating-point equality as a business rule.

5. Character, Unicode and Length

• char(n): fixed-length non-Unicode text.
• varchar(n): variable-length non-Unicode text.
• nchar(n): fixed-length Unicode text.
• nvarchar(n): variable-length Unicode text.

Use nvarchar for multilingual HMI messages and operator comments. Define a realistic maximum length; do not choose max by default. Length is part of the interface contract and must match application parameter definitions.

ChargeNo     varchar(50)  NOT NULL,
AlarmText   nvarchar(500) NOT NULL,
OperatorNote nvarchar(1000) NULL

6. Date and Time Types for Automation

For SCADA events, a clear design is EventTimeUtc datetime2(3) plus a separate receive timestamp and source sequence when event ordering matters.

7. NULL and Column Constraints

NULL means missing, unknown or not applicable. It is not equal to zero, false or an empty string. Query it using IS NULL and IS NOT NULL.

Data types define representation; constraints define additional rules:

8. What Is a Database Key?

A key is one column or a combination of columns used to identify rows or relate tables. Key theory separates possible identities from the one selected for implementation.

9. Primary, Candidate and Alternate Keys

A primary key must be unique and NOT NULL. SQL Server creates a unique index to enforce it. A table has one primary-key constraint, although that key can contain multiple columns.

Candidate keys are a logical design concept. To enforce alternate keys in SQL Server, use UNIQUE constraints after confirming minimality, mandatory columns and the real business rule. Existing sample uniqueness alone does not prove a candidate key.

CONSTRAINT PK_tblEvent PRIMARY KEY (ID),
CONSTRAINT UQ_tblEvent_Charge_DT UNIQUE (ChargeNo, DT)

10. Foreign Keys and Referential Integrity

A foreign key prevents a child row from referencing a parent that does not exist. It does not automatically create an index on the child column, and it does not decide what should happen on delete/update; those behaviors must be designed explicitly.

For production history, avoid cascade delete unless the retention and audit policy explicitly permits deleting dependent events.

11. Use tblEvent as the Running Example

The reference table demonstrates how data types, constraints and keys work together:

USE [SQF_DB];
GO

SELECT
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable
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;

12. Practice 1: Change DT from DATETIME to DATETIME2(3)

USE [SQF_DB];
GO

ALTER TABLE [dbo].[tblEvent]
ALTER COLUMN [DT] DATETIME2(3) NOT NULL;
GO

Existing datetime values can be represented by datetime2(3). The operational risk is the table alteration itself: a large history table can be locked and generate substantial log activity. Benchmark the migration on a restored copy and schedule an approved maintenance window.

13. Practice 2: Add an Equipment Foreign Key

Create a small equipment master table, then relate tblEvent.SQF_No to its primary key.

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

INSERT INTO dbo.Equipment (EquipmentName)
VALUES ('SQF-1'), ('SQF-2'), ('SQF-3');
GO

Before adding the foreign key, find orphaned values already present:

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;

The result must be empty, or the orphan rows must be corrected through an approved data-cleaning rule.

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

Prove the constraint without retaining test data

BEGIN TRANSACTION;

BEGIN TRY
    INSERT INTO dbo.tblEvent (DT, SQF_No, ChargeNo, Fan_Status)
    VALUES ('2026-08-04T12:00:00.000', 999, 'CHG-3001', 'ON');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

ROLLBACK TRANSACTION;

SQL Server should return error 547 because equipment 999 does not exist. Repeat in a rolled-back transaction with SQF_No = 1; it should pass the foreign-key check if all other required columns and constraints are satisfied.

14. Practice 3: Decide Between VARCHAR(10) and BIT for Fan_Status

First inventory the real data:

SELECT Fan_Status, COUNT(*) AS RowCount
FROM dbo.tblEvent
GROUP BY Fan_Status
ORDER BY Fan_Status;

If the approved domain is strictly ON/OFF, migrate through a new column:

ALTER TABLE dbo.tblEvent
ADD FanStatusBit bit NULL;
GO

UPDATE dbo.tblEvent
SET FanStatusBit = CASE
    WHEN UPPER(LTRIM(RTRIM(Fan_Status))) = 'ON'  THEN 1
    WHEN UPPER(LTRIM(RTRIM(Fan_Status))) = 'OFF' THEN 0
    ELSE NULL
END;
GO

-- Stop if any non-NULL source value failed conversion
IF EXISTS
(
    SELECT 1
    FROM dbo.tblEvent
    WHERE Fan_Status IS NOT NULL
      AND FanStatusBit IS NULL
)
    THROW 50001, 'Unexpected Fan_Status value; migration stopped.', 1;
GO

ALTER TABLE dbo.tblEvent DROP COLUMN Fan_Status;
EXEC sys.sp_rename
    'dbo.tblEvent.FanStatusBit',
    'Fan_Status',
    'COLUMN';
GO

15. Practice 4: Primary, Candidate and Alternate Keys

The reference identifies ID as the chosen surrogate primary key and proposes two natural combinations:

• (ChargeNo, DT): one event per charge per timestamp.
• (SQF_No, DT): one event per furnace per timestamp.

First prove that the current data is unique:

SELECT ChargeNo, DT, COUNT(*) AS DuplicateCount
FROM dbo.tblEvent
GROUP BY ChargeNo, DT
HAVING COUNT(*) > 1;

SELECT SQF_No, DT, COUNT(*) AS DuplicateCount
FROM dbo.tblEvent
GROUP BY SQF_No, DT
HAVING COUNT(*) > 1;

An empty result proves only that today's data has no duplicates. The process owner must confirm the future business rule. With millisecond timestamps, two legitimate events can still share the same instant.

ALTER TABLE dbo.tblEvent
ADD CONSTRAINT UQ_tblEvent_Charge_DT
    UNIQUE (ChargeNo, DT);
GO

-- Optional only when the plant rule is confirmed
ALTER TABLE dbo.tblEvent
ADD CONSTRAINT UQ_tblEvent_SQFNo_DT
    UNIQUE (SQF_No, DT);
GO

16. Review the Remaining tblEvent Data Types

Do not change these columns merely for uniformity. Capture actual values, client parameter types, report calculations and process rules before selecting new types.

17. Use a Safe Migration Sequence

1. Back up and prove restore on a non-production copy.
2. Record row count, table size, constraints, indexes and dependent objects.
3. Profile NULLs, duplicates, orphan equipment and status values.
4. Create/reference master data before the foreign key.
5. Run alterations in a tested maintenance window.
6. Validate row counts, constraints, query results and application writes.
7. Retain a reviewed rollback or restore decision point.

Large table alterations can be fully logged and blocking. Measure duration and log growth using a production-sized restored copy.

18. Verify the Final Data Types and Keys

SELECT
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable
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')
  AND c.name IN (N'ID', N'DT', N'SQF_No', N'ChargeNo', N'Fan_Status');

SELECT
    kc.name AS KeyName,
    kc.type_desc
FROM sys.key_constraints AS kc
WHERE kc.parent_object_id = OBJECT_ID(N'dbo.tblEvent');

SELECT fk.name AS ForeignKeyName
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = OBJECT_ID(N'dbo.tblEvent');

19. Migration Troubleshooting