SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Estimated Number of Rows issue?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 04/30/2013 :  03:20:05  Show Profile  Reply with Quote
Hi All,

i query the "orders" table from northwinds database

The query is
SELECT * FROM Orders WHERE OrderDate > '2000-01-01'

The table has the orders data from 1996 to 1998 so ideally the result set of above query is zero records.

But when i look at query execution plan(Ctrl+L)it is showing estimated number of rows as "1"

we have non clustered index on this orderdate column.

Even table has upto date statistics on this "orderdate" column why optimiser is showing estimated number of rows as "1" instead of zero?

Thanks,



M.MURALI kRISHNA

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/30/2013 :  08:00:30  Show Profile  Visit russell's Homepage  Reply with Quote
Is there an index on order date?

1 is pretty close to 0.

Try updating statistics on the table.

Should pass in dates in unambiguous format '20010101'
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 04/30/2013 :  08:46:47  Show Profile  Visit jackv's Homepage  Reply with Quote
That's a fairly good estimate i.e 1 . There are a few circumstances , such as table variable and recurseive CTEs , where it estimates as 1, as SQL Server doesn't hold statistics for these containers.


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 04/30/2013 :  10:05:52  Show Profile  Reply with Quote
Thanks for responce,

Jackv:yes we have nonclustered index
stats are uptodate
And tested with date format '20010101' Still showing estimated number of rows as 1

But i tested with some other cases(queries) which will return Empty result set by supplying false where caluse in all those cases i am getting estimated number of rows as 1.

if result set will return zero rows then optimiser is showing estimated number of rows as 1,Is this predefined behaviour?

And is there any scenarion we will get Estimated number of rows as Zero?

Thanks,

M.MURALI kRISHNA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000