Posted by Erland Sommarskog on 10/01/09 11:38

Martin R (martin80@go2.pl) writes:
> How to find first not null value in column whitout chacking whole table
> (if there is a not null value then show me it and stop searching, the
> table is quite big)?

SELECT TOP 1 col FROM tbl WHERE col IS NOT NULL

Note that "first" here is not extremly well-defined, as a table by
a defintion is a unorded set of data.

If there is no index that involves col at row, SQL Server is likely
to scan the clustered index from left to right. (But in theory it
could open parallel steams, and give you a row in the middle.)

If there is an index that involves col, SQL Serer is likely to scan
that index. And if there is an index with col as the first column,
SQL Server is likely to seek that index, and you would get the lowest
value of col. If you then change "SELECT col" to "SELECT *", you may
be back on the table scan again.

If you have any additional criteria to define this "first" value, then
you need to add an ORDER BY clause to the query.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]