如何拆分含350列10000行的SQL Server表并精准管理数据？

Splitting a 350-column table into smaller, logical tables is all about normalization and preserving data integrity. Here's a practical, actionable approach to get it done right:

1. Analyze & Group Columns by Logical Entities #

First, break down your wide table into meaningful groups based on business logic. Ask:

• Which columns belong to the same "thing"? (e.g., customer profile, order details, shipping info, billing data)
• What’s the primary key (PK) of the original table? This will become the foreign key (FK) linking all split tables together.

For example:

• Columns like CustomerID, Name, Email, Phone → belong to a Customers table.
• Columns like OrderDate, OrderTotal, PaymentMethod → belong to an Orders table.
• Columns like ShippingAddress, TrackingNumber → belong to a ShippingDetails table.

2. Design the New Table Schemas #

Create separate tables for each logical group, ensuring:

• The original PK is added as an FK in each new table (to maintain relationships).
• Data types match exactly the original table to avoid conversion errors.
• Add constraints (e.g., NOT NULL, UNIQUE, FOREIGN KEY) to enforce data accuracy.

Example SQL for creating new tables:

-- Parent table (uses original PK as its own PK)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY, -- Matches original table's PK data type
    FullName VARCHAR(150) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Phone VARCHAR(20)
);

-- Child table (links to Customers via FK)
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY, -- New PK for one-to-many relationships
    CustomerID INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATETIME NOT NULL,
    OrderTotal DECIMAL(18,2) NOT NULL,
    PaymentStatus VARCHAR(20) DEFAULT 'Pending'
);

3. Migrate Data Safely #

Transfer data from the original table to the new tables using INSERT INTO ... SELECT statements. Always wrap this in a transaction to ensure no partial data is saved if something fails.

Example migration code:

BEGIN TRANSACTION;

-- Insert into Customers
INSERT INTO Customers (CustomerID, FullName, Email, Phone)
SELECT CustomerID, FullName, Email, Phone FROM OriginalWideTable;

-- Insert into Orders
INSERT INTO Orders (CustomerID, OrderDate, OrderTotal, PaymentStatus)
SELECT CustomerID, OrderDate, OrderTotal, PaymentStatus FROM OriginalWideTable;

-- Commit only if all inserts succeed
COMMIT TRANSACTION;

4. Validate Migrated Data #

Double-check that all data was transferred correctly to avoid inconsistencies:

• Compare row counts between original and new tables (should match for one-to-one relationships).
• Verify sample records match across tables.
• Run checks for missing or mismatched values:

-- Check row count consistency
SELECT COUNT(*) FROM OriginalWideTable;
SELECT COUNT(*) FROM Customers; -- Should match if one-to-one

-- Check for mismatched data
SELECT o.CustomerID, o.FullName, c.FullName 
FROM OriginalWideTable o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.FullName != c.FullName; -- Should return 0 rows

5. Update Dependencies #

Don’t forget to fix any existing code that relies on the original table:

• Update stored procedures, views, triggers, and application queries to use the new tables.
• For a smooth transition, create a view that mimics the original table structure by joining the new tables:

CREATE VIEW OriginalTableView AS
SELECT c.CustomerID, c.FullName, c.Email, o.OrderDate, o.OrderTotal, s.ShippingAddress
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN ShippingDetails s ON c.CustomerID = s.CustomerID;

6. Deprecate the Original Table #

Once all dependencies are updated and validated:

• Rename the original table (e.g., OriginalWideTable_Backup) instead of dropping it immediately.
• Keep the backup for a few weeks to revert if issues arise, then drop it once you’re confident everything works.

7. Maintain Integrity Going Forward #

• Use transactions for multi-table CRUD operations to ensure all related data is updated together.
• Create stored procedures to handle inserts/updates across tables, enforcing business rules.
• Recreate indexes from the original table on the new tables to preserve query performance.

内容的提问来源于stack exchange，提问作者Manoj Deshwal