Tag Archive varchar

/*
================================================================================
STORAGE OPTIMIZATION: FIXED-LENGTH VS VARIABLE-LENGTH
================================================================================
References (Microsoft Learn):
1. CHAR vs VARCHAR:
   https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver16#storage
   - CHAR(n): Spends 'n' bytes regardless of actual data length.
   - VARCHAR(n): Spends Actual Length + 2 bytes.

2. NCHAR vs NVARCHAR:
   https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16#storage
   - NCHAR(n): Spends 'n * 2' bytes regardless of data.
   - NVARCHAR(n): Spends (Actual Length * 2) + 2 bytes.

3. BINARY vs VARBINARY:
   https://learn.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver16#storage
   - BINARY(n): Spends 'n' bytes.
   - VARBINARY(n): Spends Actual Length + 2 bytes.
================================================================================
*/

SET NOCOUNT ON;

DECLARE @AnalysisTable TABLE (
    DatabaseName NVARCHAR(128), -- เพิ่มเพื่อใช้เรียงลำดับ
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    DataType NVARCHAR(50),
    DefinedLength INT,
    ActualMaxLength INT,
    RowCountVal INT
);

DECLARE @Db NVARCHAR(128), @Schema NVARCHAR(128), @Table NVARCHAR(128), @Col NVARCHAR(128), @Type NVARCHAR(50), @Len INT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ActualMax INT, @RowCnt INT;

-- 1. ดึง Column เป้าหมาย (เพิ่ม table_catalog สำหรับ DB Name)
DECLARE cur_all_cols CURSOR FOR
SELECT table_catalog, table_schema, table_name, column_name, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE DATA_TYPE IN ('char', 'nchar', 'binary', 'varchar', 'nvarchar', 'varbinary')
  AND CHARACTER_MAXIMUM_LENGTH > 0
  AND table_schema NOT IN ('sys', 'information_schema');

OPEN cur_all_cols;
FETCH NEXT FROM cur_all_cols INTO @Db, @Schema, @Table, @Col, @Type, @Len;

-- 2. วนลูป Scan หา Max Length ของจริง
WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR ('Scanning: %s.%s.%s [%s]', 0, 1, @Db, @Schema, @Table, @Col) WITH NOWAIT;

    IF @Type IN ('char', 'nchar', 'varchar', 'nvarchar')
        SET @SQL = N'SELECT @MaxVal = MAX(LEN(' + QUOTENAME(@Col) + ')), @Cnt = COUNT(*) FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);
    ELSE
        SET @SQL = N'SELECT @MaxVal = MAX(DATALENGTH(' + QUOTENAME(@Col) + ')), @Cnt = COUNT(*) FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);

    EXEC sp_executesql @SQL, N'@MaxVal INT OUTPUT, @Cnt INT OUTPUT', @MaxVal = @ActualMax OUTPUT, @Cnt = @RowCnt OUTPUT;

    -- Insert ลงตารางพร้อมชื่อ Database
    INSERT INTO @AnalysisTable VALUES (@Db, @Schema, @Table, @Col, @Type, @Len, ISNULL(@ActualMax, 0), @RowCnt);

    FETCH NEXT FROM cur_all_cols INTO @Db, @Schema, @Table, @Col, @Type, @Len;
END

CLOSE cur_all_cols;
DEALLOCATE cur_all_cols;

-- 3. แสดงผลและสร้าง Script
SELECT
    DatabaseName, -- แสดงชื่อ DB เป็นคอลัมน์แรก
    SchemaName,
    TableName,
    ColumnName,

    -- Current State
    UPPER(DataType) + '(' + CAST(DefinedLength AS VARCHAR) + ')' AS CurrentType,
    ActualMaxLength,

    -- Target State
    CASE
        WHEN DataType IN ('char', 'varchar') THEN 'VARCHAR'
        WHEN DataType IN ('nchar', 'nvarchar') THEN 'NVARCHAR'
        WHEN DataType IN ('binary', 'varbinary') THEN 'VARBINARY'
    END +
    '(' + CAST(CASE WHEN ActualMaxLength < 1 THEN 1 ELSE ActualMaxLength END AS VARCHAR) + ')'
    AS TargetType,

    -- Priority
    CASE
        WHEN DataType IN ('char', 'nchar', 'binary') AND ActualMaxLength < DefinedLength THEN 'HIGH (Space Saving)'
        WHEN DataType IN ('varchar', 'nvarchar', 'varbinary') AND ActualMaxLength < DefinedLength THEN 'LOW (Schema Tightening)'
        ELSE 'SKIP'
    END AS Priority,

    -- Alter Script
    'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) +
    ' ALTER COLUMN ' + QUOTENAME(ColumnName) + ' ' +
    CASE
        WHEN DataType IN ('char', 'varchar') THEN 'VARCHAR'
        WHEN DataType IN ('nchar', 'nvarchar') THEN 'NVARCHAR'
        WHEN DataType IN ('binary', 'varbinary') THEN 'VARBINARY'
    END +
    '(' + CAST(CASE WHEN ActualMaxLength < 1 THEN 1 ELSE ActualMaxLength END AS VARCHAR) + ');'
    AS AlterScript

FROM @AnalysisTable
WHERE
    RowCountVal > 0
    AND ActualMaxLength < DefinedLength
ORDER BY
    Priority,
    DatabaseName,  -- เรียงตาม Database
    SchemaName,    -- เรียงตาม Schema
    TableName,     -- เรียงตาม Table
    ColumnName;    -- เรียงตาม Column