How To Delete Rows In SQL

Does the SQL DELETE command actually delete records or just mark them for deletion.

And if so, how do you force a physical deletion? (code required). Thanks.

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("DELETE FROM Table WHERE Day=" & "Monday", connection)

connection.Open()

command.ExecuteNonQuery()

connection.Close()

Yes, the delete command physically deletes data.|||

It's funny how my dB size increased after I ran the Delete command.

It's up 29MB, but I'm not sure how many records I deleted and what's the root cause of the increase and what's in that increase. I did do a test preview and it did reflect the correct data. Oh well.

|||

at first u execute DBCC SHRINKDATABASE command.

Then you check your database size.

|||Do you have an example... wouldn't want to ruin a database...|||Just use the command mentioned above:

DBCC SHRINKDatabase(<name>,<RestofthecurrentSize>)

Information about that can be found int he BOL (Books Online, the help of SQL Server)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I wouldn't just arbitrarily shrink the database.

First of all, you should understand that your database is made of 2 types of files, data files and log files. 1 of each by default.

When you delete data, it physically deletes it from the data file, but the data file is not resized, it just has more free space. Meanwhile, the delete transaction is written to your log file. If your log file was full at the time of the transaction, the log file is expanded to make room. The file isn't grown just enough to fit the new transaction, it is expanded by a pre-determined amount. By default, your log file expands by 10% when it needs to expand. So if the log file is 100 MB, it expands by 10 MB to have a new size of 110MB. If your log file is 10 GB, it expands by 1 GB to a size of 11 GB.

Data and log file size is very important. It is key to keep enough free space in them that the files do not need to be resized frequently. Resizing files, whether expanding or shrinking, has a big impact on performance. You want to avoid resizing arbitrarily. And you want to limit sizing when possible.