27

This is the query:

  1. It looks like some NULL values are appearing in the list.
  2. Some NULL values are being filtered out by the query. I have checked.
  3. If I add AND AdditionalFields = '', both these results are still returned
  4. AdditionalFields is a varchar(max)
  5. The database is SQL Server 10 with Compatibility Level = Sql Server 2005 (90)
  6. I am using Management Studio 2008

I appear to have empty strings whose length is NULL, or NULL values that are equal to an empty string. Is this a new datatype?!

EDIT: New datatype - hereby to be referred to as a "Numpty"

EDIT 2 inserting the data into a temporary table turns Numpties into NULLS. (The result from this sql is 10)

CREATE TABLE #temp(ID uniqueidentifier , Value varchar(max))

INSERT INTO #temp 
SELECT top 10 g.ID, g.AdditionalFields
FROM grants g 
WHERE g.AdditionalFields IS NOT NULL AND LEN(g.AdditionalFields) IS NULL

SELECT COUNT(*) FROM #temp WHERE Value is null

DROP TABLE #temp

EDIT 3 And I can fix the data by running an update:

UPDATE Grants SET AdditionalFields = NULL
WHERE AdditionalFields IS NOT NULL AND LEN(AdditionalFields) IS NULL

So that makes me think the fields must contain something, rather than some problem with the schema definition. But what is it? And how do I stop it ever coming back?

EDIT 4 There are 2 other fields in my database, both varchar(max) that return rows when the field IS NOT NULL AND LEN(field) IS NULL. All these fields were once TEXT and were changed to VARCHAR(MAX). The database was also moved from Sql Server 2005 to 2008. It looks like we've got ANSI_PADDING etc OFF by default.

Another example: enter image description here

Converting to varbinary enter image description here

Execution plan: Execution plan EDIT 5: removed table definition - turned out to be not relevant in the end

EDIT 6 Scripts to generate scripts for altering TEXT to VARCHAR(MAX) then update values to prevent bug and enhance performance

--Generate scripts to alter TEXT to VARCHAR(MAX)
SELECT 'ALTER TABLE [' + tab.table_schema + '].[' + tab.table_name  + '] ALTER COLUMN [' + col.column_name + '] VARCHAR(MAX)' + CASE WHEN col.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END + ' GO'
FROM INFORMATION_SCHEMA.tables tab
INNER JOIN INFORMATION_SCHEMA.COLUMNS col ON col.table_name = tab.table_name
          AND tab.table_schema = col.table_schema
          AND tab.table_catalog = col.table_catalog
WHERE tab.table_type <> 'VIEW' and col.DATA_TYPE = 'text'

--Generate scripts to set value to value in VARCHAR(MAX) fields
SELECT 'UPDATE [' + tab.table_schema + '].[' + tab.table_name  + '] SET [' + col.column_name + '] = [' + col.column_name + ']'
FROM INFORMATION_SCHEMA.tables tab
INNER JOIN INFORMATION_SCHEMA.COLUMNS col ON col.table_name = tab.table_name
          AND tab.table_schema = col.table_schema
          AND tab.table_catalog = col.table_catalog
WHERE tab.table_type <> 'VIEW' AND col.DATA_TYPE = 'varchar' and col.CHARACTER_MAXIMUM_LENGTH = -1
Colin
  • 22,328
  • 17
  • 103
  • 197
  • 2
    Is your query posted verbatim? Is there a chance you misspelled `AdditionalFields` in the `WHERE` clause but not in the `SELECT` list (which would cause filtering on another field)? Something like `WHERE 'g.AdditionalFields' IS NOT NULL` would yield similar behavior (since you're not filtering a field but a string constant). – Quassnoi Apr 24 '12 at 10:08
  • 1
    The first result *isn't* surprising. An empty string and `NULL` are two separate concepts (unless you're weird, like Oracle) – Damien_The_Unbeliever Apr 24 '12 at 10:10
  • Please check if your **AdditinaFields** contains spaces. In that case it looks like null value is returned but actually the query returned "space" character. – vpv Apr 24 '12 at 10:15
  • Is there a linked server involved in the query? – Rabid Apr 24 '12 at 10:21
  • Just one remark: you say "AdditionalFields is a varchar(max)", but you do mean that AdditionalFields is a _NULLABLE_ varchar(max), right? And I'm not sure about the compatibility level, but as I've never used that, I cvan't be sure. – Mr Lister Apr 24 '12 at 10:29
  • Yes, it's copied verbatim. Yes, the first result isn't surprising. If it contained a space then LEN(g.AdditionalFields) would not return NULL. There is no linked server. The field is NULLABLE and contains some NULL values that do get excluded by the query. – Colin Apr 24 '12 at 10:46
  • 1
    As a debug hint: Just output the PK so you see the row that generates the result. – MicSim Apr 24 '12 at 11:02
  • I have output the primary key. That's how I know that the database contains NULLS, Empty Strings and Numpties. ;-) – Colin Apr 24 '12 at 11:23
  • Not the first to get this problem. http://stackoverflow.com/q/6217131/150342. But there doesn't appear to be an answer. Can there really be a bug this basic? – Colin Apr 24 '12 at 11:31
  • 1
    @Colin - that is something completely different (and basic if I may say so). If you are determined to get to the root cause, I would 1. Copy the entire table and verify if you still get those results. If you do 2. delete half the table and verify. Repeat until you get to the data that is causing it, come back to post the results and satisfy our curiousity :) – Lieven Keersmaekers Apr 24 '12 at 11:35
  • 3
    And while we are at it, you could post a screenshot of the entire ssms window so we can verify your claim more easily. – Lieven Keersmaekers Apr 24 '12 at 11:36
  • 1
    Please post the exact table definition (right-click on the table, Script As, CREATE, Clipboard) – Quassnoi Apr 24 '12 at 11:59
  • Questions: Is there an index on that column, and if so, have you tried reindexing/rebuilding it? Also, did you perhaps manage to only select the first two lines of that query and execute those, without the WHERE clause? – Lasse V. Karlsen Apr 24 '12 at 12:53
  • No index on the column. The full query in the window was run. Not sure about posting table definition, there's 77 columns in the table. – Colin Apr 24 '12 at 13:09
  • 1
    Just another shot in the dark: Is it a database migrated from an older SQL Server version? Was the schema created using non-standard ANSI_xxx settings? Is this imported data (as an UPDATE fixes the problem)? Do you have the same behavior when querying from an application (other DB access provider)? – MicSim Apr 25 '12 at 13:38
  • Yes the database used to be on Sql Server 2005. Now it's Sql Server 2008 with Compatibility Level = Sql Server 2005 (90). When I view database properties, ANSI NULL Default, ANSI NULLS Enabled, ANSI Padding Enabled and ANSI Warnings Enabled are all false. This is the same as our development system, but if I script a table. MSSMS puts in SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON and SET ANSI_PADDING ON. I don't know what happened during the migration, but I'm willing to bet we've got a hotch potch of tables created using different settings.... – Colin Apr 25 '12 at 15:07
  • @Colin - You've posted the table definition for `GrantTypes` io `Grants` – Lieven Keersmaekers Apr 26 '12 at 13:19
  • Yes. It's a smaller table than Grants, and it's got the same problem. How do I get the execution plan? – Colin Apr 26 '12 at 13:20
  • 1
    In SSMS, press CTRL+M and I would still recommend to include the `grants` definition to avoid having to start all over again. For starters, `it's got the same problem`, *what* column? *what* query? – Lieven Keersmaekers Apr 26 '12 at 13:22
  • Query is SELECT ID, DefaultTotals FROM GrantTypes WHERE DefaultTotals IS NOT NULL AND LEN(DefaultTotals) IS NULL. Image now included at the top – Colin Apr 26 '12 at 13:27
  • 1
    Also as you say you are on 2008 I would add `sys.fn_PhysLocFormatter(%%physloc%%)` to the select list to see the `file:page:slot` of the rows returned then look at these in [SQL Internals Viewer](http://internalsviewer.codeplex.com/). I do seem to remember the role of the `NULL_BITMAP` is different for large datatypes though. – Martin Smith Apr 26 '12 at 13:29
  • Execution plan tab now included – Colin Apr 26 '12 at 13:35
  • 1
    This is wierd enough that I might open an issue directly with Microsoft. Since you converted this from text maybe there is some hidden value somewhere. – HLGEM Apr 26 '12 at 13:44
  • Do you have any kind of triggers or other hidden functions operating on these tables/fields? – Miika L. Apr 26 '12 at 13:45
  • No triggers or hidden functions – Colin Apr 26 '12 at 13:49
  • You might also try posting this question on the MS Technet SQL Server forums [ http://social.technet.microsoft.com/forums/en-US/category/sqlserver/ ]. Lots of MVPs hang out there. – Philip Kelley Apr 26 '12 at 13:50
  • Haven't managed to get SQL Internals Viewer to load properly – Colin Apr 26 '12 at 13:54
  • @Colin - You need to download the correct version for your version of SSMS. There is a 2005 version and a 2008 version. Click the "View all downloads" link to see both. – Martin Smith Apr 26 '12 at 13:55
  • @MartinSmith - it doesn't work for me either. I downloaded the 2008 version. My SSMS reports SQL Server 2008 R2. Perhaps it's the R2 that's breaking things. – Lieven Keersmaekers Apr 26 '12 at 13:58
  • @Lieven - Ah that's a shame. I haven't tried it in R2 client tools myself. There is always `DBCC PAGE` then! – Martin Smith Apr 26 '12 at 13:59
  • I got the stand-alone version to run but I don't have SysAdmin rights.... – Colin Apr 26 '12 at 14:05
  • 1
    Please, run a DBCC CHECKTABLE and let us know if it find any problem: http://msdn.microsoft.com/es-es/library/ms174338.aspx – JotaBe Apr 26 '12 at 20:02
  • "To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.". So already done. (see comment on other answer) – Colin Apr 26 '12 at 22:24
  • 3
    [I've created a connect item for this](https://connect.microsoft.com/SQLServer/feedback/details/739675/is-not-null-incorrect-results-for-column-altered-from-deprecated-large-data-types). I haven't tested to see if this issue occurs in SQL Server 2012 or not though. – Martin Smith Apr 28 '12 at 12:09
  • It looks like this issue has been addressed in SQL 2014 SP2 (not sure about older SQL servers) when you look at https://support.microsoft.com/en-us/kb/3171021 (NULL values are ignored after altering a column from text to varchar(max)). Do you maybe know, if this also happens with NVARCHAR(max)? Sadly when googling after the descritption you only get to SO and no official MS site. – rominator007 Oct 11 '16 at 09:03
  • Just checked it myself: This does not happen with a conversion to nvarchar(max) (SQL 2012 SP3) – rominator007 Oct 11 '16 at 09:10

5 Answers5

19

I got a sample code to reproduce the above behavior. The problem arises when you have a TEXT field that stores a value larger than it can fit in a row and if you set it afterwards to NULL and perform the column conversion to VARCHAR(MAX).

The large value gets stored in a separate page. Then you set the value of this field to NULL. If you now convert this column to a VARCHAR(MAX), then SQL Server seems to not get it right. Typically on a TEXT to VARCHAR(MAX) conversion the external pages stay as they are, but maybe because it was set to NULL, the column altering messes things up.

Update: It doesn't seem to have anything to do with the large values in the TEXT column. Short values show the same behavior (extended sample). So it's just the explicit setting to NULL through an UPDATE and the conversion that matters.

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [Value] [text] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO Test VALUES (1, 'test')
INSERT INTO Test VALUES (2, '')
INSERT INTO Test VALUES (3, NULL)
INSERT INTO Test VALUES (4, '012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')
INSERT INTO Test VALUES (5, 'short string')
GO

update test SET value = null where ID = 4
update test SET value = null where ID = 5
GO

ALTER TABLE test ALTER COLUMN value varchar(max)
GO

select id, value, len(value) as length
from test
where value is not null
GO

The result is:

1   test    4
2           0
4   NULL    NULL
5   NULL    NULL

An easy fix for this problem would be to reassign the values in the VARCHAR(MAX) columns.

UPDATE Test SET value = value

This seems to put the values in the rows that were previously stored in external pages. (See for reference: NTEXT vs NVARCHAR(MAX) in SQL 2005)

MicSim
  • 26,265
  • 16
  • 90
  • 133
  • The Text data type is the issue here; you need to handle them differently where datalength(Value)=0. See http://stackoverflow.com/questions/33409/how-do-i-check-if-a-sql-server-text-column-is-empty for more details – u07ch Apr 27 '12 at 09:34
  • @u07ch: Read the problem carefully. It's not about the TEXT data type, it's about VARCHAR(MAX) NULL values giving TRUE when checked with IS NOT NULL. – MicSim Apr 27 '12 at 10:58
  • 2
    +1 for providing a repo. The row where the `NULL` is inserted explicitly is the only one where the `NULL_BITMAP` is set to `1` for that column. For `text` datatypes I've noticed before that updating to `NULL` doesn't update the bitmap but seems to set some sort of flag elsewhere in the pointer or pointed to structure. If you do `select [Id], [Value] from [Test] where [Value] is not null` before the conversion to `max` then correct results are returned but the `NULL_BITMAP` still says zero so it must look somewhere else to determine that. – Martin Smith Apr 28 '12 at 10:18
  • Great troubleshooting MicSim: you reduce the problem to a specific set of steps, reproduce the unwanted behavior, and find an explanation, congratulations! – tcbrazil Apr 28 '12 at 10:54
  • Superb! Thank-you. Better go fix all the Text and Varchar(max) fields in our databases... – Colin Apr 28 '12 at 14:47
6

This is just an addition to McSim's answer using SQL Server Internals Viewer to look at the individual stages.

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL PRIMARY KEY ,
    [Value] [text] NULL)


INSERT INTO Test VALUES (1, '')

Row after initial insert

After Insert Main Row

Text value after initial insert

After Insert Text Value

update [Test] SET [Value] = null

Row after update to NULL

This is identical to the row shown earlier so I haven't repeated the screenshot. Specifically the NULL_BITMAP does not get updated to reflect the new NULL value.

Text value after update to NULL

Text value after Update

The Type bits have changed and Internals Viewer shows this as no longer containing a value for the Data column.

At this point running the following correctly returns no rows

SET STATISTICS IO ON
select [Id]
from [Test]
where [Value] is not null

So SQL Server must follow the text pointer and look at the value there to determine NULL-ability.

ALTER TABLE [Test] ALTER COLUMN [Value] varchar(max)

This is a metadata only change. Both the inrow and out of row data remain unchanged.

However at this point running the following incorrectly returns the row.

SET STATISTICS IO ON
select [Id]
from [Test]
where [Value] is not null

The output of STATISTICS IO

Scan count 1, logical reads 2, ... lob logical reads 1

shows that it still does actually follow the text pointer but presumably in the varchar(max) case there must be a different code path that incorrectly ends up taking the value from the NULL_BITMAP regardless (the value of which has never been updated since the initial insert).

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Many thanks Martin. We'd have got here sooner if I'd had rights to do this on the server. We copied a backup down, but I didn't get time to try yesterday. – Colin Apr 28 '12 at 15:03
1

Colin:

I'm pretty sure all this is happening because of the database conversion. Since you need to solve this thing ASAP, my suggestion is to guarantee that your AdditionalFields data is ok first, and try to understand why this happens after:

  1. Do a backup;

  2. Run this T-SQL:

    update grants
set AdditionalFields = ltrim(rtrim(isnull(AdditionalFields,'')))

The isnull function will transform your null values in empty strings, and the left/right trim should guarantee that even fields with more than one space will have the same value after.

Could you run this and feedback us later with the results?

Best regards

tcbrazil
  • 1,315
  • 12
  • 25
  • I already know that I can turn numpties into nulls by running an update (I ran the update above inside a transaction) so I'm not sure that this will tell us anything more. I wanted to keep the data in place to help with investigation, and the problem hasn't resulted in any significant issues yet. I don't have enough rights to view the underlying page data, so I think I will try to backup and pull the database file down onto a system where I do have admin rights. Not at work now, so tune in tomorrow... – Colin Apr 26 '12 at 18:30
0

As others have pointed out this result is utterly impossible.

  1. Please post a screenshot of the actual execution plan.
  2. Please run dbcc checkdb and post the error messages, if any.

(2) is actually my favorite right now.

usr
  • 168,620
  • 35
  • 240
  • 369
0

I suspect the word NULL is neing stored in the db, use select * from blah where mycolumn = 'NULL'

drew
  • 11
  • 1