Archive for December, 2011

Difference between DELETE and TRUNCATE

It’s interesting to know about the difference between DELETE and TRUNCATE statements in SQL Server.
The DELETE FROM <table_name> statement deletes each and every row from table one by one. Hence, it takes a lot of time and slower. This statement does not resets the IDENTITY values in that table as well. Hence, it comes under DML category. 
While, the TRUNCATE TABLE <table_name> statement also deletes all rows but it removes all data pages allocated to that table. Therefore, it is faster compare to DELETE FROM <table_name> statement. In addition, it resets the IDENTITY values to starting number given to identity column in that table. Since, it handles the physical structure allocated to the table, it comes under DDL category as well.
Trust it helps everyone…

Attaching MDXStepByStep DB Problem in SQL Server 2008

Today, I faced a problem while trying to attach the MDXStepByStep database, which comes with the Microsoft SQL Server 2008 MDX book’s companion CD.

It gave me an error saying:
“Directory lookup for the file “C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf” failed with the operating system error 5(Access is denied.)”.

The solution which I found was:
Step 1: Copy both .mdf and .ldf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Delete the MdxStepByStep_log.ldf file from “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA” folder.

Step 3: Modify the file path in attach_db.sql file with “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 4: Run attach_db.sql script now. It will create the .ldf file and attach the MDXStepByStep database successfully with following message:

File activation failure. The physical file name “C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep_log.LDF” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MdxStepByStep_log.LDF’ was created.

I got help from’s following post:

I’ve been working with following configuration:
OS: Win XP Professional with SP3.
SQL Server 2008 Developer Edition with SP1

ADO.NET Architecture

ADO.NET objects can be divided into two broad categories:

1.  Connected
2.  Disconnected
Connected:  The Connected category contains objects that directly deal with the databases. The Connected category contains following objects:
1. Connection
2. Transaction
3. Command
4. Parameter
5. DataAdapter
6. DataReader
Disconnected:  The Disconnected category contais objects that are in-memory objects. To put it in simple words, the disconnected objects don’t make direct connection with any database. Rather, they use Connected objects and receive data from them in turn. The Disconnected category contains following objects:
1. DataSet
2. DataTable
3. DataColumn
4. DataRow
5. Constraint
6. DataRelation
There is one more object in the Disconnected category, which is basically derived from DataTable i.e. DataView. Therefore, we can say that DataView also comes under the Disconnected category.

Test blog for SSIS

Test blog for SSIS

Introduction to Transactions in SQL Server 2008

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

SQL Server operates in the following transaction modes:

1.) Autocommit Transactions: This is the default transaction mode in SQL Server. Each individual statement is a transaction. Without using any kind of transaction statement, one can just execute INSERT, UPDATE or DELETE statement and the data modification will become permanent. 
2.) Explicit Transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement. When user executes BEGIN TRAN | TRANSACTION statemetn, SQL Server becomes aware the a new trasaction is being started on a particular connection. It monitors all subsequent statements being executed by that connection. When COMMIT TRAN | TRANSACTION statement is executed, the changes are made permanent in the database. If ROLLBACK TRAN | TRANSACTION statement is executed, all changes made after BEGIN TRANSACTION statement is rolled back and database retains earlier state. 
3.) Implicit Transactions: User has to execute SET IMPLICIT_TRANSACTION ON statement in order to tell SQL Server that an implicit transaction is being started by the connection. An implict transaction is committed or rolled back using COMMIT or ROLLBACK statement. Then, if user executes any other data modification statement, a new transaction is implicitly started when the prior transaction completes becuase IMPLICIT_TRANSACTION is still set to ON. We can switch off the implicit transaction mode by executing SET IMPLICIT_TRANSACTION OFF statement.
4.) Batch-Scoped Transactions: Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.

I’ll blog about each transaction mode in separately soon where I’ll explain how each transaction mode works.