Increasing the value abnormally

Last Post 17 May 2012 11:55 AM by gunneyk. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sql-tips
New Member
New Member

--
30 Apr 2012 07:15 AM
I have table say test and it has two columns. Test(A nvarchar(10), B int) and values are like 'account',1
'Cash',2
etc..

when I do select * from test where A='Cash', the values for B jumps from 2 to 10.
Every repeated select it keep increases the value abnormally. like 12, 15 etc..

No updates on this columns, just select. I suspect something got corrupted on the table for this value. if I do select * from test where A='account' works fine, retrieves expected results.

I dropped the table and recreated, rebuild the index, sp_recompile on the table but no luck.

is the execution plan in procedure cache corrupted?

This happens only in dev database, test database is fine on the same db server.

Any help will be appreciated.. I can check restore the dev database from prod. But, what happens same thing occurs in prod.
gunneyk
New Member
New Member

--
30 Apr 2012 11:54 AM
Can you post the actual DDL for the table as well as the actual select you use.
sql-tips
New Member
New Member

--
01 May 2012 01:16 PM

CREATE TABLE dbo.Test
(
A varchar(10) NOT NULL,
B int NOT NULL)
go


ALTER TABLE dbo.Test
ADD CONSTRAINT Test_pk PRIMARY KEY (A)
go

select * from test where A='Cash'
select * from test where A='account'
gunneyk
New Member
New Member

--
01 May 2012 06:12 PM
OK, with a table that simple there is no way that can happen. I suspect that you have more going on than you think. Either the table has a trigger, computed column or something or the select is not that simple. A corrupt table would give an error or at the least return the wrong value but consistantly the wrong value. If you can reproduce it then post the DDL etc. to get those results but the code listed below does exactly what it is supposed to. You can also try running DBCC CHECKDB to ensure there is no corruption.

CREATE TABLE dbo.Test
(
A varchar(10) NOT NULL,
B int NOT NULL)
go


ALTER TABLE dbo.Test
ADD CONSTRAINT Test_pk PRIMARY KEY (A)
go


INSERT INTO dbo.test (A,B) VALUES('Account',1) ;
INSERT INTO dbo.test (A,B) VALUES('Cash',2) ;

select * from test where A='Cash'
select * from test where A='account'

A B
Cash 2

Account 1
sql-tips
New Member
New Member

--
17 May 2012 07:11 AM
Thanks gunneyk for your input. I don't have any triggers to this table. I restarted SQL Server service and it fixed the issue. But, after few days it is recurring.. I backed up this database and restored to another server and there is no issue on the new server. so, is there something related procedure cache on this server?
gunneyk
New Member
New Member

--
17 May 2012 11:55 AM
The procedure cache only holds the plan used to get the data and with such a simple query the plan is most likey not kept anyway as it would be considered a trivial plan. Again I think there must be more to this than just a simple table but assuming it's just as you say I can only suggest a few things. One is that maybe the memory on that computer is flakey but I would expect it to error. Another is maybe a bug (but never heard of this). Are the versions of the two SQL Server instances exactly the same?


Acceptable Use Policy
---