how actually this query executes?

Question

how actually this query executes?

Charmer

SSChampion

Points: 14903
More actions
September 28, 2011 at 4:08 am

#245603

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

baabhu SSCertifiable Points: 6205 More actions · Answer 1

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.

Charmer SSChampion Points: 14903 More actions · Answer 2

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

baabhu SSCertifiable Points: 6205 More actions · Answer 3

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.

Charmer SSChampion Points: 14903 More actions · Answer 4

Charmer

SSChampion

Points: 14903

September 28, 2011 at 4:46 am

#1387659

so it deletes based on newid()..?

Thanks,
Charmer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

...

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 6

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

Charmer SSChampion Points: 14903 More actions · Answer 7

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