Deleting a record and views - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting a record and views

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-31 : 14:34:19

Yesterday I deleted a record from our production database. Today, I get the following strange result when querying a view.


select right(name,1), name, description
from db.dbo.view
where right(name,1) like [^A-Z]
and right(name,1) like [^0-9]
group by name, description

The only record returned is the deleted record (which ends with the letter W). But the result does not have anything in the select right(name,1) first column. Should have "W". Strange.

Aside from anything wrong with my snippet, I'm guessing there's something 'wrong' with the view, perhaps with the metadata not being refreshed?

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-31 : 14:38:33

Should not have W in the column, I meant to say, so that part is OK, but the fact that the record is returned, still odd.

Again, returns the name and description, but not the W.

Code Name Description
--- ---- -----------
ABCW Texttextext

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 14:05:15

may be view is retrieving result from more than 1 table and other table has record still (might be a history/audit table which captures deleted info)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-09-01 : 14:24:46

Good point. That is likely the cause. Thanks!

dmilam
Posting Yak Master

185 Posts

Posted - 2010-09-22 : 17:29:34

Still seeing the strang behavior:


select len(name) as [Name length]
, [Right character domain] = 
	case 
	when right(name,1) like '[A-Z]' then 'A-Z'
	when right(name,1) like '[0-9]' then '0-9'
	when right(name,1) = ' ' then 'space'
	when right(name,1) = '	' then 'tab'
	when right(name,1) is null then 'null'
	else 'unknown'
	end
, name as [Name]
, description as [Description]
from Table

returns 'unknown' for the name in question ending in 'W'. I am baffled.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:36:28

are you sure you've deleted it from same table in the same schema. May be you're looking at same table in different schema

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources