Re: Delete in SQL Server
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/07/04
- Next message: Michael Cheng [MSFT]: "RE: Domain Name"
- Previous message: Michael Cheng [MSFT]: "RE: Stopping multiple connections"
- In reply to: Prabhat: "Re: Delete in SQL Server"
- Next in thread: Prabhat: "Re: Delete in SQL Server"
- Reply: Prabhat: "Re: Delete in SQL Server"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 7 May 2004 11:20:46 +0200
> If I will Insert The Row in 2nd table and then delete from 1st table then
> will sql server will maintain 2 copies of the same row in 2 different place
> (As You wrote)?
Yes.
> You have wrote that cleanup process can be done before insert to the free
> space. How we can do that all? Can you please elaborate that.
I only mentioned this to describe what goes on under the covers. This isn't anything you can control. And it
doesn't affect anything either, as the page is compacted as needed.
If you find that inserting this amount of rows and then deleting them (and doing that time period you both
original and "copy" in the database) is a constraint for you, I suggest you don't do all rows in one
statement.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp "Prabhat" <not_a_mail@hotmail.com> wrote in message news:Ow2Xw5zMEHA.3472@TK2MSFTNGP10.phx.gbl... > Thanks Karaszi, > > But in my situatuation: > > If I will Insert The Row in 2nd table and then delete from 1st table then > will sql server will maintain 2 copies of the same row in 2 different place > (As You wrote)? then that may lead to performace problem. > > I am worried because Both of the table in my case are VERY LARGE (10000000 > records in 1st table and 400000 records in 2nd table ) and here i want to > delete some rows from 1st table and insert the rows in second table. So Will > be causing any Performance problem? > > You have wrote that cleanup process can be done before insert to the free > space. How we can do that all? Can you please elaborate that. > > My Requirement is that: My customer don't want all INACTIVE records in the > main tbale after every 1 or 2 years. The Inactive data will nver be used in > the Application in any of the form. But He want the data to be kept in > another table. Please suggest the best way to do that. > > I prefer to move the Data to another table (those are not used) Instead of > filtering the data in each and every menu item. But as per you SQL Server > will maintain 2 identical copies then it may be causeing prob. > > Please Suggest. > > Thanks > Prabhat > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:eo#BM6sMEHA.3712@TK2MSFTNGP10.phx.gbl... > > Just a bit of nit.picking... :-) > > Not that in any effect changes the validity of your statement from a user > perspective: > > > > SQL Server does not remove the row from the page when a row is deleted. > However, the row offset table on the > > page marks the slot as available. You can see this by taking a hex dump of > the page immediately after the > > delete, using DBCC PAGE, the row is still there, physically. The space > made available (logically) on the page > > is also immediately reflected in the PSF page. If the page in question is > targeted for an insert, the page > > will be compacted when the insert occurs. But a clean up process can do > the compaction earlier then that (spid > > number 6). > > > > However, the row is by no means an entity which can live on its own or be > "moved" (or rather referred to) by > > different tables. In Prabhat's scenario, the row is first deleted and > another row (with possibly identical > > values) will be inserted into the other table. > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > > > > > "Jeff Duncan" <jduncan@gtefcu.org> wrote in message > news:OqoEcusMEHA.1348@TK2MSFTNGP12.phx.gbl... > > > if it is deleted it is gone. No proverbial recycle bin. You can wrap > your > > > delete in a transaction and if it is wrong roll it back though. If your > > > data is gone well then that is what restores are for. > > > > > > -- > > > Jeff Duncan > > > MCDBA, MCSE+I > > > "Prabhat" <not_a_mail@hotmail.com> wrote in message > > > news:uLqLBlsMEHA.4016@TK2MSFTNGP10.phx.gbl... > > > > Hi All, > > > > > > > > I have one doubt in SQL Server, Regarding how DELETE Works? > > > > > > > > What is the Actual Thing that SQL Server Do when we delete any record > from > > > > the Database Table? > > > > Does it maintain any Falg Internally or actually Delete the Record > from > > > > Database? > > > > (Like In FoxPro It store some flag internally so that we can Undelete > that > > > > records). > > > > > > > > Example, Suppose I have 2 tables of same structure, and I delete one > > > record > > > > from 1st table and put that record into 2nd table then how actually > SQL > > > > Server handles that. Does it really move that record or Does it > changes > > > the > > > > pointer the record for 2nd table or mark the record for delete in 1st > > > table > > > > and create one record for 2nd table? > > > > > > > > > > > > Can Any Body Help me in these DOUBTS? > > > > > > > > Thanks > > > > Prabhat > > > > > > > > > > > > > > > > > > > >
- Next message: Michael Cheng [MSFT]: "RE: Domain Name"
- Previous message: Michael Cheng [MSFT]: "RE: Stopping multiple connections"
- In reply to: Prabhat: "Re: Delete in SQL Server"
- Next in thread: Prabhat: "Re: Delete in SQL Server"
- Reply: Prabhat: "Re: Delete in SQL Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
