Friday, February 3, 2012

Difference between drop and truncate in sql server

Hi friends here i will explain the difference between Drop and Truncate

As we have seen in the last post about the difference between Truncate and Delete today we will seen the Difference between Drop and Truncate visually 

Let's see we have the table with the following data as given below




now suppose if we perform truncate operation on it all the Records will be remove from it. as you can see it from givenn slide


Above you can see all the data will be deleted from the table but all the columns will be as it is.

Now if you use Drop Query then whole the table will be deleted
let's see the example below




















so now as given above if u try  select query you have the above errors because table is not exists.

so we have conclusion is that

Drop - deletes the data as well as structure
Truncate - deletes only the data, and resets the auto increment column to 0

Wednesday, February 1, 2012

Diffrence between Stored Procedure and User Define Function in SQL

These are the main difference between Stored Procedure and User Define Function


Functions 


-can be used with Select statement
-Not returning output parameter but returns Table variables
-You can join UDF
-Can not be used to change server configuration
-Can not be used with XML FOR clause
-Can not have transaction within function


Stored Procedure

-have to use EXEC or EXECUTE
-return output parameter
-can create table but won’t return Table Variables
-you can not join SP
-can be used to change server configuration
-can be used with XML FOR Clause
-can have transaction within SP