Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

"IS NULL" check applied to a LOB field causes "lob logical..

 
   Database Help (Home) -> Programming RSS
Next:  Upgrading From SQL 2000 A few questions.  
Author Message
SVA

External


Since: Dec 04, 2006
Posts: 1



(Msg. 1) Posted: Mon Dec 04, 2006 4:55 am
Post subject: "IS NULL" check applied to a LOB field causes "lob logical reads"
Archived from groups: microsoft>public>sqlserver>programming (more info?)

It looks like an "IS NULL" predicate, when applied to a LOB field,
causes that LOB's pages to be read.

This results in a very slow processing of queries like:

SELECT
(case when [myColumn] is not null then 1 else 0 end) as test1
FROM myTable

Specifically, this behaviour can be observed on SQL2005 and IMAGE
datatype.

Theoretically, I would assume that SQL Server should simply check a
NULL bitmap in the cases above, but it performs "lob logical reads"
instead.
The above can be observed in "set statistics io on" mode.

So I wonder if there is a "fast" way to check if the LOB value contains
NULL without the need to read LOB pages.


Thanks for comments/ideas.

--
Vladislav

 >> Stay informed about: ""IS NULL"" check applied to a LOB field causes ""lob logical.. 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
What happens when a "NULL" condition is applied to the - Hi, I have below two tables "TAB1" & "TAB2": TAB1 ---- T1C1 T1C2 ---- ---- 1 A 2 B 3 C TAB2 ---- T2C1 T2C2 ---- ---- 2 D 3 E 4 F When I executed the below sql statement, it displayed the Cartesian product ...

IS NULL on field is not using index placed on that field - Hi All, I have the table structure as follows ->Deliveries (table) |__OrderID (Guid) |__DeliverDate (DateTime) (ALLOW NULLS) Index is on DeliverDate (ASC) 60000 records look at the query plan for the following and is using the pro...

Check for constraints on a field - Hi, I want to remove a field from a table by using 'ALTER TABLE tblTest DROP [TST_Available]'. I got an error message returned that there is a constraint on the field (the field has a default value). How do I programmaticaly check for constraints on...

can pivot be applied to group by ? - For this table Id bigint identity(1,1) Attribute nvarchar(50) not null PeriodNumber bigint not null DataDateTime DateTime not null Value decimal(18,5) not null I need to retrieve each period as a row with its columns as a set of attributes' values. ...

error adding user when audit trigger applied - Hi I have a trigger that will audit all ddl events. When adding a user through SSMS and give the user permission to certain databases only not all databases I get the error below. If I grant access using the code below CREATE USER [Titan\john.dow] FOR...
   Database Help (Home) -> Programming All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]