What does FixedLenNullInSource mean in sp_help?

Question

A look around FixedLenNullInSource is compatible with SQL 2000, but what does that mean?

Answer 1

In SQL Server 2008, the definition of sp_help shows that it is hard-coded to return yes if the column is NULL 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 value of the stats bit in syscolumns in SQL Server 2000 is not fully documented, but I found an update to SQL Server 7.0 SP4 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 could not find much additional information when searching with keywords for COL_FIXEDNULL or COL_NONSQLSUB , but found that the NULL store for fixed-length data types changed in SQL Server 7. In previous versions, fixed-length, zero-value data types were silently converted to variables in accordance with the table below.

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

This is discussed for SQL Server in KB 463166 ( available only in French ), and looking at the Sybase documentation, it looks like it is still in this product .

From SQL Server 7.0 onwards, the NULL CHAR(100) column occupied the entire declared fixed column length in the fixed-row data section (until sparse columns were introduced in 2008 that change behavior again).

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