Re: Delete in SQL Server
From: Prabhat (not_a_mail_at_hotmail.com)
Date: 05/06/04
- Next message: Hugo Kornelis: "Re: Simple Removal of Duplicate Rows"
- Previous message: x452: "Query Attempt"
- In reply to: Tibor Karaszi: "Re: Delete in SQL Server"
- Next in thread: Tibor Karaszi: "Re: Delete in SQL Server"
- Reply: Tibor Karaszi: "Re: Delete in SQL Server"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 6 May 2004 13:17:56 +0530
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: Hugo Kornelis: "Re: Simple Removal of Duplicate Rows"
- Previous message: x452: "Query Attempt"
- In reply to: Tibor Karaszi: "Re: Delete in SQL Server"
- Next in thread: Tibor Karaszi: "Re: Delete in SQL Server"
- Reply: Tibor Karaszi: "Re: Delete in SQL Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
