6

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

Alex KeySmith
  • 16,657
  • 11
  • 74
  • 152
  • 1
    Even in 2000 BOL this is listed as ["For backward compatibility only."](http://msdn.microsoft.com/en-us/library/aa933429%28v=sql.80%29.aspx). You'd probably need to find the docs for an even earlier version. – Martin Smith Sep 07 '11 at 14:55
  • Thanks Martin Smith, been tryign to track down the meaning in the SQL server 7.0 docs, but looks like the meaning has been lost in time! I wonder if any old-schoolers know what it means? – Alex KeySmith Sep 07 '11 at 15:01
  • 1
    [Looking at the definition of `sp_help`](http://stackoverflow.com/questions/13508162/is-there-a-way-to-change-the-values-for-fixedlennullinsource-and-trimtrailingbla/13508747#13508747) it actually doesn't appear to mean much these days. It is hardcoded to return "yes" if the column is nullable and one of `'varbinary', 'varchar', 'binary', 'char'` – Martin Smith Nov 23 '12 at 09:00
  • Thanks @MartinSmith you should pop your comment in as an answer. That's good enough for me, I'll mark it as the answer. – Alex KeySmith Nov 23 '12 at 11:13

1 Answers1

8

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845