SQL Truncate vs. Delete

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage

1. DELETE FROM TABLENAME
3. TRUNCATE TABLE TABLENAME

Even though they do a similar thing, there are some differences which makes them unique commands.

	DELETE	TRUNCATE
1.	Considered as Data Mining Language (DML) statement.	Considered as Data Definition Language (DDL) statement.
2.	Can be used to delete all or part of data in a table.	Will delete all the data in a table.
3.	Will log the actions.	There will be no logging on record deletion.
4.	Will use more locks.	Will use fewer locks.
5.	Will use more resources.	Will use less resources.
6.	Slow.	Fast.
7.	Will not reset the identity seed.	Will reset the identity seed to 0.
8.	Can be used to delete data in tables having relationships.	Cannot use in tables having relationships.
9.	Can be used in tables involved in log shipping or replication.	Cannot be used in tables involved in log shipping or replication.
10.	Transaction can be rolled back.	No rollback.
11.	Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name).	Data pages related to the table will be de allocated and returned to the system.
12.	Related Triggers are fired.	Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.

1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)

If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.

1. DBCC CHECKIDENT (TABLENAME, NORESEED)

Repairing a Corrupted Database

When we are regularly using databases, sometimes those will get corrupted. This happened to one of my applications, it was a Windows Mobile application with SQL Server CE database. Since it is running in a mobile it is bit difficult for us to fix the database using the desktop machine. I used the following .Net Compact framework code to fix the corrupted database while the database is in the mobile.

1. private void btnRepair_Click(object sender, EventArgs e)
2. {
3.     Cursor.Current = Cursors.WaitCursor;
4.     Cursor.Show();
6.     // txtDBPath will contain the path to the database.
7.     engine = new SqlCeEngine(@"Data Source=" + txtDBPath.Text + ";Password=DBPassword");
8.     try
9.     {
10.         if (!engine.Verify())
11.         {
12.             Cursor.Current = Cursors.Default;
13.             Cursor.Hide();
15.             DialogResult result = MessageBox.Show("Database is corrupted. Do you want to repair?", "My App", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
16.             if (result == DialogResult.Yes)
17.             {
18.                 Cursor.Current = Cursors.WaitCursor;
19.                 Cursor.Show();
21.                 // You can also use RepairOption.DeleteCorruptedRows.
22.                 engine.Repair(null, RepairOption.RecoverCorruptedRows);
24.                 Cursor.Current = Cursors.Default;
25.                 Cursor.Hide();
27.                 MessageBox.Show("Database repaired successfully.", "My App");
28.             }
29.         }
30.         else
31.         {
32.             Cursor.Current = Cursors.Default;
33.             Cursor.Hide();
34.             MessageBox.Show("Database is not corrupted.", "My App");
35.         }
36.     }
37.     catch (Exception ex)
38.     {
39.         Cursor.Current = Cursors.Default;
40.         Cursor.Hide();
41.         MessageBox.Show(ex.Message, "Error!");
42.     }
43. }

Next time you get a corrupted SQL CE database try it out.

Project has Stopped Working

If you create a .NET application to use SQL CE database when you try to run your application on a Windows Vista or Windows 7 machine you might get an error saying your application did stop working, and the error details might show that your are having a problem with System.Data.SQLServerCE.

The reason for this is that your system is not having the SQL Server CE runtime in your machine. To fix it what you need to do is to install the Microsoft SQL Server Compact 3.5 Service Pack 1 on your system. You can download it from the following Microsoft link.

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

Microsoft SQL Server Compact 3.5 for Windows Mobile

SQL Server CE 3.5 has been released. According to Microsoft this is having some cool features. Some of them are as follows according to Microsoft.

• Side-by-side installation together with SQL Server 2005 Compact Edition (version 3.1)
• Support for newer and more secure encryption algorithms
• Additional timestamp (rowversion) data type
• Enhanced support for Transact-SQL statements including:

- Nested query in FROM clause
- CROSS APPLY and OUTER APPLY
- CAST and DECIMAL
- SET IDENTITY INSERT
- TOP CLAUSE

To download and learn more visit Microsoft using the following URL.

http://www.microsoft.com/downloads/details.aspx?FamilyID=38ED2670-A70A-43B3-87F3-7AB67B56CBF2&displaylang=en

Making a normal Column, an Identity column in SQL Server CE

In SQL CE making a normal column an identity column is not possible. This is because the process involved in the making of identity columns. If you would like to know more about the Identity creation behaviour please read my earlier article.

http://arjunachith.blogspot.com/2007/08/making-normal-column-identity-column-in.html

In SQL CE the process mentioned in the article is not possible because that SQL CE does not support enabling and disabling IDENTITY INSERT.

If you did try it out, should have noted that even SQL CE table editor is not supporting this.

So if you really want to change a column in to an identity column (which already having unique values) then what you can do is, create a new table and copy the data your self. But the problem is that you will not get the old values in the identity column because there is no way to copy them without turning the IDENTITY INSERT off. So identity column data will be new from 1 to n, But all other information will be there in the table with the identity column in place. If you even wants to keep the old identity values you can keep that old identity column and create a new column for the Identity column.

Retrieving Schema Information of SQL CE Database

There are very limited features offered in SQL Compact Edition (SQL CE). But with those offered you can do many things if you know what is there and what is not there.

Following are some views available in SQL CE which you can use to retrieve information about the tables in the database.

-- Gets the list of Columns with their associated Tables. You will be able to retrieve the column properties such as Data Type, Allow Null, Default Values, etc.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

-- Retrieves information about the indexes contained in the database.
SELECT * FROM INFORMATION_SCHEMA.INDEXES

-- Retrieves information about the Primary Keys used in the tables.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

-- Retrieves all the Tables in the database including the System tables.
SELECT * FROM INFORMATION_SCHEMA.TABLES

-- Retrieves all the Constrains in the database.
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

-- Retrieves all the Data Types available in the database.
SELECT * FROM INFORMATION_SCHEMA.PROVIDER_TYPES

-- If you have any referential integrity constraints in your database defined, those will be returned by this.
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS