February 21, 2008 at 3:05 am
Hi All
I am beginner ot SQL Server.
I want to know that when we delete a row or a set of Rows from a table, it'll only make the space available for subsequent inserts into it or will the Delete also free the memory used by the table.
Suppose I am inserting customer records in the details table when the customer comes into the system. If i make a logic to delete the customer record from the table and insert into the backup table when it leaves the system(As the data inserted is quite large and my application queries into this table at each transaction). Will it help in optimizing the SQL Queries or it is useless to do so.
Thanks
Harsh Dhawan
February 21, 2008 at 3:27 am
Hi Harsh,
You should not really worry too much about how data is stored in the database, at the end of the day this is the task of the database management system.
The database stores data on pages (8K), and these are associated with tables, indexes, ... . When you delete a row, it is deleted from a page. The page may contain other rows as well, so it may not become empty. Pages do not need to be full, indeed, most of them will have space left for other rows. SQL Server decides on which page to use for a row based on the clustered index on the table, the free pages available, etc. You could read about this in Books Online, see the chapter about pages in http://msdn2.microsoft.com/en-us/library/ms190969.aspx.
While it is important to understand how the database will store your data, I'd let it do it for you for now (it usually does quite a good job at it). Just store the data in your tables. You could even leave the customer in the original table, without moving it to a "backup" table (hope I understand what you mean by this), and ensure fast lookups by using indexes.
Regards,
Andras
February 21, 2008 at 4:42 am
Manual processes such as moving rows around should be considered after things such as
1. Indexing - are you indexing on the correct columns?
2. Partitioning - If you have REALLY large tables then you could be running SQL Enterprise and thus could set up table partitions that are managed for you
3. Application code - is the application coded efficiently? Does it make many round trips to the db?
Do you currently have slow performance or are you worried about it happening in the future?
February 21, 2008 at 5:20 am
Thanks
Actually I have started working on one application which is doing this way i.e. It is deleting rows from the table and putting it in backup table when the subscriber unsubscribes from the service(In Telecom application). When I asked the developer about the reason for deleting the record from the main table, he said that they dont want to maintain this data(data of Unsubscribed subscribers) in this table as the select query will take time.
As in Oracle, it stores data in segments. Segments has free and used space. The mark(a logical mark) between free and used space can be called as high water mark(HWM). When new data filled, the mark moves. On deleting records, oracle does not move back HWM. That is, you have unused data but, HWM sees as if it were filled data. When you shrink table such as alter table move command, this HWM refreshes itself
that means , the select query will take same time before or after deleting rows from table.
I want to know whether this concept is also followed in SQL Server.
On Reading from "Pages and Extents" topic i feel that it would be same. Just want to confirm that.
February 21, 2008 at 5:28 am
Thanks Andras.
From this I got that when you populate the table with 2 million rows of data and you will have many hundreds of extents. Now lets assume that you delete over half of the records in the table. SQL server still has the same number of extents but many of the pages are empty. When you run a query against the table SQl server will scan through all the pages including empty ones looking for data.
That means there is no benefit of deleting the rows from the table for making the select statement faster. Instead of this we can store the row in the same table with additional fields i.e. inactive date and status.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply