difference between TRUNCATE & DELETE statement in SQL Server

-->Difference between TRUNCATE & DELETE statement in SQL Server -->

TRUNCATE V/s DELETE

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock,                                             each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.	Slower than truncate because, it maintain logs for every record
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.	Delete works at row level, thus row level constrains apply

 

Insert Data more than 1 Table using Transaction

Insert Data more than 1 Table using Transaction

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class Default3 : System.Web.UI.Page

{

    SqlConnection cn = new SqlConnection();

    SqlCommand cmd = new SqlCommand();

    SqlTransaction Tr;  // Create transcation
object

   

  

    DataTable dt = new DataTable();

    protected void Page_Load(object sender, EventArgs e)

    {

    
cn.ConnectionString = @"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated
Security=True;User Instance=True";

    
cn.Open();

    }

    protected void Button2_Click(object sender, EventArgs e)

    {

    
Tr = cn.BeginTransaction();
// Begin Transaction

    
cmd.Connection = cn;

     cmd.Transaction = Tr;
// Assign Transaction object value to the command object

    
try    // Try block for Error
checking block

    
{

cmd.CommandText =  
"INSERT INTO employee(eid,ename,epass)VALUES(@eid,@ename,@epass)";

cmd.Parameters.AddWithValue("@eid", TextBox1.Text); // @eid used in insert query;

   
cmd.Parameters.AddWithValue("@ename",TextBox2.Text);
// @ename used in insert query;

    
cmd.ExecuteNonQuery();

        
cmd.CommandText = "INSERT
INTO password(eid,epass)VALUES(@eid,@epass)";

        
cmd.Parameters.AddWithValue("eid", TextBox1.Text);

       
cmd.Parameters.AddWithValue("@epass",TextBox3.Text);

        
cmd.ExecuteNonQuery();

        
Tr.Commit();   // Commint
statement save the transcation complete

    

    
}

    
catch (Exception a)  // Cathch block is used for catching the  error type in try block

    
{

        
Tr.Rollback(); // Now rollback statement undo the changes and not saved data

    
}

    }

   

}

Insert Command Stored Procedure

Insert Command Stored Procedure

Create Stored Procedure in SQL SERVER,

Create Table in DB and Go to server Explorer And Right Click on Store Procedure

Code For Create Insert Command In Save Data

ALTER PROCEDURE  dbo.StoredProcedure1

    /*

    (

    @parameter1 int
= 5,

    @parameter2
datatype OUTPUT

    )

    */(

   

 @eid varchar(20) ,

 @ename varchar(20) )

   

AS

begin

INSERT INTO employee(eid,ename)VALUES(@eid,@ename)

    /* SET NOCOUNT
ON */

   

    END

Now Code IN .cs file On SAVE Button in web Page.

using System;

using
System.Collections;

using
System.Configuration;

using
System.Data;

using
System.Linq;

using
System.Web;

using
System.Web.Security;

using
System.Web.UI;

using
System.Web.UI.HtmlControls;

using
System.Web.UI.WebControls;

using
System.Web.UI.WebControls.WebParts;

using
System.Xml.Linq;

using
System.Data.SqlClient;   //
add ado classes for SQL server

public partial class Default3 : System.Web.UI.Page

{

    // Create
Connection and Command Object

    SqlConnection
cn = new SqlConnection();

    SqlCommand
cmd = new SqlCommand();

        

//
Now Define SQL Parameters for Insert data using Stored Procedure

    SqlParameter e1 = new SqlParameter();

    SqlParameter e2 = new SqlParameter();

    SqlParameter e3 = new SqlParameter();

    SqlParameter e4 = new SqlParameter();

  

    protected void Page_Load(object sender, EventArgs e)

    {

    
// Noe Open Connetion Using Connection Object

   
cn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated
Security=True;User Instance=True";

        cn.Open();

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        cmd = new SqlCommand("StoredProcedure1",cn);

// "StoredProcedure1"- is name of stored procedur created in sql server.

        cmd.CommandType = CommandType.StoredProcedure;

        e1.ParameterName= "@eid"; 
 // Parameters namegiven in store procedure (@eid,@ename)

        e1.Direction = ParameterDirection.Input; // For Input data

        e1.Value = TextBox1.Text;    
                    // Value read from TextBox1  control from page

        e1.SqlDbType = SqlDbType.VarChar;  
                                                              // Type of Parameter store value

        e1.Size = 20;                
                   //Size of Parameter

cmd.Parameters.Add(e1); // Add parameter using ADO.NET

        e2.ParameterName = "@ename";

        cmd.CommandType = CommandType.StoredProcedure;

        e2.ParameterName = "@ename";

        e2.Direction = ParameterDirection.Input;

        e2.Value =TextBox2.Text;

        e2.SqlDbType = SqlDbType.VarChar; 

        e2.Size = 20;   

       

    
cmd.Parameters.Add(e2);

    
cmd.ExecuteNonQuery();

        Response.Write("Record insert successfully");

    }