Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harsh.dhawan
Starting Member
10 Posts |
 Posted - 2008-02-21 : 00:19:15
|
| Hi AllI 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 DhawanHarsh Dhawan |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-02-21 : 01:03:25
|
| Harsh, There is really 2 answers here:1) When you delete a row it will free up a space in the .mdf, there will be a period of time that it will be in your log until that is backed up.2) In the example that you provided do you really want to delete Customer info. In many cases I would not recommend deletes unless it is being archived to another database. Info like this in my opinion and I am not sure who shares this opinion, should be kept for a long time unless you are sure you will never need it. Maybe done the road you may want to look at why that person is not a customer any more. Or you may want to share with them a new product or something along those lines. I normally recommend either a datefield where the customer when inactive. Along with Date field they were active. Just my opinion.ChrisChris Shawwww.SQLonCall.com |
 |
|
|
harsh.dhawan
Starting Member
10 Posts |
Posted - 2008-02-21 : 01:27:33
|
| Thanks Chris for your prompt reply.Actually I am working on to change the design in the application. This design is currently implemented in one of the applications. I am suggesting to maintain the status(as there are many intermediate statuses required) and inactive date in the tableActually my main purpose is to know that by just deleting the rows from the table help in better output i.e. less time consumption of the select statement on that table or it will take the same time as it was taking before doing the delete.ThanksHarshHarsh Dhawan |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-21 : 01:59:35
|
| As Shaw had mentioned, deleting the rows in not the right way to go if you want to improve the performance. If you want to increase the performance of your select queries, try using indexes. SQL 2005 supports partitions ( not sure if SQL 2000 does, can anyone help?) and if you think that you dont need the records entered in the last year them you can partition the table on year which will drastically improve performance. |
|
|
|
harsh.dhawan
Starting Member
10 Posts |
Posted - 2008-02-21 : 02:19:06
|
| Thanks PravinBut my purpose is not fulfilled yet. I know indexes can improve the performance. But my question is will only deleting the rows can improve the performance.Like in Oracle there is a high watermark point assigned to the table internally which specifies the highest rows inserted into that table. When i delete rows from table the high watermark point doesn't change. Then means my select query will take the same time before deleting and after deleting the data.Is the concept is same in SQL Server?ThanksHarsh Dhawan |
|
|
|
|
|
|
Query Regarding Delete