SQL Server临时表列查询疑问：ProviderName长度为何为200而非100？

Hey Carlosm, let's break down why your ProviderName column is reporting a max length of 200 instead of the 100 you expected when querying tempdb.sys.columns. This is usually tied to how SQL Server stores metadata for string types, so here are the most likely causes to check:

1. You're Using NVARCHAR Instead of VARCHAR #

This is the #1 reason for this discrepancy. SQL Server stores max_length in bytes, not characters, for string types:

• For VARCHAR(100), max_length will show 100 (1 byte per character)
• For NVARCHAR(100), max_length will show 200 (2 bytes per character, since NVARCHAR uses Unicode)

To confirm this, run this query to check the actual data type:

SELECT 
    name AS column_name,
    max_length,
    CASE system_type_id
        WHEN 167 THEN 'VARCHAR'
        WHEN 231 THEN 'NVARCHAR'
        WHEN 175 THEN 'CHAR'
        WHEN 239 THEN 'NCHAR'
        ELSE 'Non-string type'
    END AS data_type
FROM tempdb.sys.columns
WHERE 
    object_id = OBJECT_ID('tempdb..#YourSourceTempTable')
    AND name = 'ProviderName';

If the result shows NVARCHAR, the 200 value is completely expected—you just need to account for the byte vs character difference when creating your new temp table.

2. Your Temp Table Was Modified After Creation #

If you initially created the temp table with ProviderName as length 200, then later altered it to 100, there's a small chance the metadata view hasn't refreshed properly (though SQL Server usually handles this immediately). To verify the current actual length, check INFORMATION_SCHEMA.COLUMNS which returns character count directly:

SELECT 
    COLUMN_NAME,
    CHARACTER_MAXIMUM_LENGTH AS character_length,
    DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME LIKE '#YourSourceTempTable%' -- Temp tables have unique suffixes
    AND COLUMN_NAME = 'ProviderName';

This view will show you the actual character limit you set, regardless of byte count.

3. Implicit Type Expansion During Temp Table Creation #

If you created the source temp table using SELECT INTO instead of explicit CREATE TABLE, SQL Server might have inherited the length from the source data. For example:

• If your SELECT pulled from a table where ProviderName is NVARCHAR(200)
• Or if you used a function like CAST(some_column AS NVARCHAR) without specifying a length, which can sometimes lead to unexpected default lengths

Double-check the original creation script for your source temp table. If it's using SELECT INTO, replace it with an explicit CREATE TABLE where you define ProviderName as VARCHAR(100) or NVARCHAR(100) explicitly.

4. Rare: Metadata Cache Glitch #

In extremely rare cases, especially on older SQL Server versions, you might hit a metadata cache issue. If you've ruled out the above, try dropping and recreating the source temp table, then re-query tempdb.sys.columns to see if the value updates.

Start with checking the data type (VARCHAR vs NVARCHAR)—that's almost certainly the issue here. Once you confirm that, you can adjust your script to use the correct byte count or reference INFORMATION_SCHEMA.COLUMNS for character-based length when creating your new temp table.

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