查找可能已更改其数据类型以减小大小的整数数据库列

以下存储过程将有助于识别这些列。它首先创建一个临时表，该表存储每个整数类型可以保存的最小值和最大值，然后运行动态查询以查找定义这些整数类型之一的每个表/列的最小值和最大值。一旦确定了最小/最大值，它就会查找可以保存当前值的最小类型并给出如下结果：

table_name      column_name         current_type    min_val max_val proposed_type   proposed_min    proposed_max    space_saved
RideLegVetting  RideNumber          int             1       21      tinyint         0               255             2025654
RideLegVetting  LegNumber           int             0       99      tinyint         0               255             2025654

对于每一列，它显示表中找到的当前类型和最小/最大值，以及新的建议类型和它可以容纳的最小/最大值。根据基数，它还指示将节省的空间量的估计值。存储过程如下：

CREATE PROCEDURE OptimizeIntSizes AS
BEGIN
    SET NOCOUNT ON
    CREATE TABLE #DataTypeRanges
    (
        name nvarchar(128),
        size tinyint,
        low bigint,
        high bigint
    )
    INSERT INTO #DataTypeRanges VALUES (N'tinyint', 1, 0, 255)
    INSERT INTO #DataTypeRanges VALUES (N'smallint', 2, -32768, 32767)
    INSERT INTO #DataTypeRanges VALUES (N'int', 4, -2147483648, 2147483647)
    INSERT INTO #DataTypeRanges VALUES (N'bigint', 8, -9223372036854775808, 9223372036854775807) 
    CREATE TABLE #Results
    (
        table_name nvarchar(128),
        column_name nvarchar(128),
        current_type nvarchar(128),
        min_val bigint,
        max_val bigint,
        proposed_type nvarchar(128),
        proposed_min bigint,
        proposed_max bigint,
        space_saved bigint
    )
    DECLARE @table_name nvarchar(128)
    DECLARE @column_name nvarchar(128)
    DECLARE @current_type nvarchar(128)
    DECLARE @proposed_type nvarchar(128)
    DECLARE @low bigint
    DECLARE @high bigint
    DECLARE @size tinyint
    DECLARE @cardinality bigint
    DECLARE @min_val bigint
    DECLARE @max_val bigint
    DECLARE @proposed_min bigint
    DECLARE @proposed_max bigint
    DECLARE @proposed_size tinyint
    DECLARE @space_saved bigint
    DECLARE @sql nvarchar(max)
    DECLARE @params nvarchar(max)
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE FindCursor CURSOR STATIC FOR
        SELECT tables.name AS table_name, cols.name AS column_name, types.name, dtr.size, dtr.low, dtr.high
        FROM sys.columns cols
            JOIN sys.tables tables ON tables.object_id = cols.object_id
            JOIN sys.types types ON types.system_type_id = cols.system_type_id
            JOIN #DataTypeRanges dtr ON dtr.name COLLATE DATABASE_DEFAULT = types.name COLLATE DATABASE_DEFAULT
    OPEN FindCursor
    FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SELECT @cardinality_out = COUNT(1), @min_val_out = MIN([' + @column_name + ']), @max_val_out = MAX([' + @column_name + ']) FROM [' + @table_name + ']'
        SET @params = N'@cardinality_out bigint OUTPUT, @min_val_out bigint OUTPUT, @max_val_out bigint OUTPUT'
        EXECUTE sp_executesql @sql, @params, @cardinality_out = @cardinality OUTPUT, @min_val_out = @min_val OUTPUT, @max_val_out = @max_val OUTPUT
        SELECT TOP 1 @proposed_type = name, @proposed_size = size, @proposed_min = low, @proposed_max = high
            FROM #DataTypeRanges
            WHERE @min_val >= low  AND @max_val <= high
            ORDER BY size
        IF @proposed_type <> @current_type AND @max_val IS NOT NULL
        BEGIN
            SET @space_saved = (@size - @proposed_size) * @cardinality
            INSERT INTO #Results VALUES (@table_name, @column_name, @current_type, @min_val, @max_val, @proposed_type, @proposed_min, @proposed_max, @space_saved)
        END
        FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    END
    CLOSE FindCursor
    DEALLOCATE FindCursor
    SELECT * FROM #Results ORDER BY space_saved DESC
END

如果您可以升级到 SQL Server 2016 SP1 或更高版本，您可能会为自己节省大量工作。即使在从该服务包开始的 Express 版本中也可以使用数据压缩。您可以简单地将行压缩应用于所有表和索引，以节省比通过更改数据类型获得的空间更多的空间。那是因为您提到的数据类型仅使用通过行压缩来拟合实际值所需的字节。文档截图：