Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql server - What does FixedLenNullInSource in sp_help mean?

Taking a look around FixedLenNullInSource is for compatibility with SQL 2000, but what does it actually mean?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

In SQL Server 2008 the definition of sp_help shows this is hardcoded to return "yes" if the column is nullable and one of varbinary, varchar, binary, char

   'FixedLenNullInSource' = 
   CASE
        WHEN Type_name(system_type_id) NOT IN ( 'varbinary', 'varchar', 
                                                'binary', 'char' ) THEN '(n/a)'
        WHEN is_nullable = 0 THEN @no
        ELSE @yes
    END 

In SQL Server 2000 it is defined differently as

   'FixedLenNullInSource' = case  
      when type_name(xtype) not in ('varbinary','varchar','binary','char')  
       Then '(n/a)'  
      When status & 0x20 = 0 Then @no  
      Else @yes END
      /* ... */
      from syscolumns

The meaning of the stats bits in syscolumns in SQL Server 2000 is not fully documented but I found a SQL Server 7.0 SP4 upgrade script that sets the column values as follows (0x20 = 32 in decimal)

+ CASE WHEN (type_name(xtype) IN ('text', 'image')
                                AND (colstat & 0x2000)!=0)
                            OR (type_name(xtype) IN ('binary', 'char', 'timestamp')
                                AND (typestat&1)=0 )
                            THEN 32 ELSE 0 END      -- COL_FIXEDNULL, COL_NONSQLSUB

I couldn't find much additional information when googling for either COL_FIXEDNULL or COL_NONSQLSUB but did find out that the storage of NULL values for fixed length datatypes changed in SQL Server 7. In previous versions nullable fixed length datatypes were silently converted to variable as per the following table.

+----------------------------+-----------+
| char                       | varchar   |
| nchar                      | nvarchar  |
| binary                     | varbinary |
| datetime                   | datetimn  |
| ?oat                       | ?oatn     |
| int, smallint, and tinyint | intn      |
| decimal                    | decimaln  |
| numeric                    | numericn  |
| money and smallmoney       | moneyn    |
+----------------------------+-----------+

This is discussed for SQL Server in KB 463166 (only available in French) and from looking at the Sybase documentation it appears that is still the case in that product.

From SQL Server 7.0 onwards a NULL CHAR(100) column took up the whole of the declared fixed column length in the fixed length data section of the row (until sparse columns were introduced in 2008 - which change the behaviour again).

I presume that this bit in syscolumns.status differentiated between the two different storage formats.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...