Taking a look around FixedLenNullInSource is for compatibility with SQL 2000, but what does it actually mean?
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 |
| float | floatn |
| 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With