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 |
|
ssrstips.com
Starting Member
5 Posts |
 Posted - 2011-07-07 : 08:20:23
|
Today on SQL 2008 I was working on a query that seemed to run fine on production in the morning and at night; but would fail during the day. It was something like:WITH Something AS (SELECT Blah, Blah2 FROM BlahTable)SELECT *FROM Something CROSS APPLY (SELECT Count(DISTINCT Blah) AS BlahCount, Count(DISTINCT Blah2) AS Blah2Count FROM Something) SomethingCount What was happening was that BlahCount and Blah2Count would end up different to the number of actual distinct records returned. I thought that the CTE (it's a non-recursive one) constructed an in-memory table, and that as both the select and count worked on the same in-memory table, there'd work on that and there'd be no race condition. So why didn't it work in practice? (I ended up solving the problem in a different way, but I'd like to know what caused it). |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 08:35:33
|
| Not always. Often the cte is reconstructed for each reference and optimised individually - a bit like using a view.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-07 : 08:42:07
|
You can end up with two different results from Something, beause a cte is only a directive how to pick your data (much like a view).SQL Server tried to stream the data to the final resultset as it fetches the data.If you want the results to be consistent you have to use either a transaction or a snapshop isolation level.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-07 : 08:44:24
|
| And you can see what Nigel is talking about for yourself by looking at the execution plan. |
|
|
|
|
|
|
CTE race condition?