September 28, 2011 at 4:08 am
Hi,
can any one explain me how the delete query is working on here..? i don't know what type of query it is? some one brief me please..
DECLARE @t TABLE
(nDex INT IDENTITY(1,1)
,value VARCHAR(9)
,keey UNIQUEIDENTIFIER)
INSERT @t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
DELETE t
OUTPUT DELETED.*
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM @t
ORDER BY NEWID()) AS b
ON b.ndex = t.nDex
GO
Thanks,
Charmer
September 28, 2011 at 4:35 am
This sql statment deletes X records and those records deleted were show in the result window. Other than that this is a pretty straight forward self join delete statement.
select *
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM @t
ORDER BY NEWID()) AS b
ON b.ndex = t.nDex
Order by NEWID() is the culprit. When you order by newid() every time the resultant set varies. Which is where you got confused.
If you remove that and join using Ndex the results will not change.
September 28, 2011 at 4:39 am
ok..i understand the select query..but what is that delete statement?
DELETE t
OUTPUT DELETED.*
im not aware of this type of declaration...what actually it is?
Thanks,
Charmer
September 28, 2011 at 4:41 am
What ever is deleted will be show as the output.
After the delete statement.
Please use
select * from @t
Just to see what are the records that are deleted. The records deleted will be exactly the same as output shown using the delete statement.
September 28, 2011 at 4:46 am
so it deletes based on newid()..?
Thanks,
Charmer
September 28, 2011 at 5:30 am
...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 5:52 am
What it looks like to me: You delete 9 random rows from the query, this is my testing script:create TABLE #t
(nDex INT IDENTITY(1,1)
,value VARCHAR(9)
,keey UNIQUEIDENTIFIER)
create table #count
(id int identity(1,1), Minimum int, Maximum int, Counter int)
GO
INSERT #t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
DELETE t
OUTPUT DELETED.*
FROM #t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM #t
ORDER BY NEWID()) AS b
ON b.ndex = t.nDex
insert into #count
select MIN(nDex), MAX(ndex), COUNT(*) from #t
GO 100
SELECT * FROM #count
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 28, 2011 at 7:06 am
yeh random manner...but not deleting 9 rows always for all the execution....it differs...i don't know how it deletes...sometimes i get 7 rows deleted, sometimes it is 8 and sometimes it is 10....and so on...im still wondering on what based it deletes?
Thanks,
Charmer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply