June 23, 2009 at 6:42 am
declare @c table(datasetid int)
;with cte as (
--there are a complex sql query here, so I ignore it
)
insert into @c -- # 1
select DataSetId
from cte where datasetid = 56614
select * from @c -- # 2
The problem is that if I comment two lines: # 1 and # 2, then there is only one row, but if I don't comment them, there is no row at last. However, if I change temperary table @c to a real table in database, it has one row at last.
The version of my SQL Server is 9.0.3077, any tips are appreciated.
June 26, 2009 at 12:05 am
Curious, can you recreate the problem using a simple cte instead of a complex one? Perhaps selecting an ID from a base table?
June 26, 2009 at 12:36 am
I can reproduce this problem in my database, since the database is larger than 50 GB and the complex query involed so many table, so I have no idea to let you test. But I can do anything you would like.
The last problem is that at last, if I select from @c, there is about 468 rows. But if I comment those two lines: # 1 and # 2, it will have 469 rows. So I think it may because @c is in memory, and other rows are ok, but for this line: where datasetid = 56614. I mean, where datasetid = 56611 is ok, it's only for one specified row: 56614
We can use real created table (dbo.mytable) or temp table (##A or #A in temperary database) to replace @c, then it's ok.
So there must be something wrong with memory or bug with @c.
Today the problem is changed because we add more rows in. It can show this row now, but still has a problem with the number 56614, details as below:
select DataSetId,
case flag
when -1 then ''
when 0 then 'False'
when 1 then 'True'
end as f
from cte
where datasetid = 56614
------------------------
Output: DataSetId f
56614 True
//////////////////////////////////
insert into @c
select DataSetId,
case flag
when -1 then ''
when 0 then 'False'
when 1 then 'True'
end as f
from cte
where datasetid = 56614
select * from @c
------------------------
Output: DataSetId f
56614 -- Here is '', not 'True'
Who can tell me why? Thanks.
June 26, 2009 at 7:37 am
Table variables are just like temporary tables, just light weight. Both are created in tempdb, table variable just have a few more restrictions. They do not have statistics kept, they are not involved in transactions are just a couple of the restrictions.
I asked if you can recreate the problem using a simple cte in place of the complex cte, or is this the only time you are having this problem.
You may need to provide the code in the cte so that we can see what is going on.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply