Sql Server : INT to BIGINT

[DRAFT - Work in progress]

In SQL Server, changing a column data type from INT to BIGINT is quite simple:

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] BIGINT

But it is so simple only when the field does not have any index or constraints on it. In such a case, we have to remove the indexes and constraints, change the type and then recreate them. Warning changing the type, creating indexes, rebuilding indexes, increase the size of the transaction log file (LDF). Check to have enough space and, after activity, consider to reclaim unused space. Also check the status of Primary Key (Clustered) fragmentation of involved related table.

Below a simple but realistic example: we change the data type of field [CustomerKey] in the database created by ContosoDatabaseGeneratorV2. [CustomerKey] is primary key of table [Customer] and ForeignKey in table [Sales]. We have to remove and recreate indexes and constraints.

-- Dropping involved indexes and relationships
DROP INDEX [IX_Sales_CustomerKey] ON [Data].[Sales]
ALTER TABLE [Data].[Sales] DROP CONSTRAINT [FK_Sales_Customers]
ALTER TABLE [Data].[Customer] DROP CONSTRAINT [PK_Customer] WITH ( ONLINE = OFF )

-- INT to BIGINT
ALTER TABLE [Data].[Customer] ALTER COLUMN [CustomerKey] BIGINT NOT NULL
ALTER TABLE [Data].[Sales]    ALTER COLUMN [CustomerKey] BIGINT NOT NULL

-- Recreating indexes and relationships
ALTER TABLE [Data].[Customer] ADD  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerKey] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
ALTER TABLE [Data].[Sales]  WITH NOCHECK ADD  CONSTRAINT [FK_Sales_Customers] FOREIGN KEY([CustomerKey])  REFERENCES [Data].[Customer] ([CustomerKey])
ALTER TABLE [Data].[Sales] CHECK CONSTRAINT [FK_Sales_Customers]
CREATE NONCLUSTERED INDEX [IX_Sales_CustomerKey] ON [Data].[Sales] ( [CustomerKey] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

If required, rebuild/reorganize PartitionKey index of child table(s).

ALTER INDEX [PK_Sales] ON [Data].[Sales] REBUILD PARTITION = ALL 

If you use Entity Framework or other ORM, remember to update the model and related code.

Script for checking the last values of identity columns in a database

SELECT
    SchemaName = OBJECT_SCHEMA_NAME(tabs.object_id, DB_ID()),
    tabs.name AS TableName,
    cols.name AS ColumnName,
    icols.last_value AS LastValue,
    tps.name AS TypeName    
FROM
    sys.tables AS tabs
    INNER JOIN sys.columns AS cols            ON tabs.object_id = cols.object_id
    INNER JOIN sys.identity_columns AS icols  ON icols.column_id = cols.column_id AND icols.object_id = cols.object_id
    INNER JOIN sys.types AS tps               ON cols.user_type_id = tps.user_type_id
WHERE
    cols.is_identity = 1
ORDER BY
    icols.last_value DESC

Output example:

| SchemaName   | TableName          | ColumnName         | LastValue | TypeName |
|--------------|--------------------|--------------------|-----------|----------|
| Production   | TransactionHistory | TransactionID      | 213442    | int      |
| Sales        | SalesOrderDetail   | SalesOrderDetailID | 121317    | bigint   |
| Production   | WorkOrder          | WorkOrderID        | 72591     | int      |
| Person       | EmailAddress       | EmailAddressID     | 259       | smallint |

Script for checking the size of indexes in a database

SELECT   
    sc.name AS SchemaName,
    t.name AS TableName,	
    i.name AS IndexName,
	i.type_desc AS IndexType,
    SUM(s.used_page_count) * 8 / 1024.0 AS IndexSizeMB
FROM sys.dm_db_partition_stats  AS s
    JOIN sys.indexes  AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    JOIN sys.tables AS t ON s.object_id=t.object_id
	JOIN sys.schemas AS sc ON sc.schema_id = t.schema_id
GROUP BY 
    sc.name,t.name,i.name,i.type_desc
ORDER BY 
    sc.name,t.name,i.name,i.type_desc

Output example:

| SchemaName | TableName | IndexName            | IndexType    | IndexSizeMB |
|------------|-----------|----------------------|--------------|-------------|
| Data       | Customer  | PK_Customer          | CLUSTERED    | 610.906250  |
| Data       | Product   | PK_TestProduct       | CLUSTERED    | 0.726562    |
| Data       | Sales     | NULL                 | HEAP         | 1759.625000 |
| Data       | Sales     | IX_Sales_CustomerKey | NONCLUSTERED | 390.765625  |
| Data       | Sales     | IX_Sales_ProductKey  | NONCLUSTERED | 656.757812  |

Script for checking the size of database files

SELECT
    DB_NAME() AS DbName, 
    name AS FileLogicalName, 
	physical_name AS FileNameOnDisk,
    type_desc as Type,
    size/128.0 AS TotalSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS BIGINT)/128.0 AS FreeSpaceMB 
FROM sys.database_files

Output:

| DbName          | FileLogicalName     | FileNameOnDisk                | Type | TotalSizeMB | FreeSpaceMB |
|-----------------|---------------------|-------------------------------|------|-------------|-------------|
| ContosoSales10M | ContosoSales10M     | X:\db\ContosoSales10M.mdf     | ROWS | 5270.937500 | 1273.625000 |
| ContosoSales10M | ContosoSales10M_log | X:\db\ContosoSales10M_log.ldf | LOG  | 14.937500   | 9.664063    |