Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
 Posted - 2013-04-30 : 03:20:05
|
| Hi All,i query the "orders" table from northwinds database The query isSELECT * 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
5072 Posts |
Posted - 2013-04-30 : 08:00:30
|
| 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' |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-04-30 : 08:46:47
|
| 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 |
|
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-04-30 : 10:05:52
|
| Thanks for responce,Jackv:yes we have nonclustered indexstats 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 |
|
|
|
|
|
|
Estimated Number of Rows issue?