SQL Server – Difference between Delete and Truncate

Click Here for complete List.

In this post we will see the common differences between DELETE and TRUNCATE

DELETE is a DML (Data Manipulation langauge) command. TRUNCATE is a DDL (Data Defintion language) command.
We can use filters while deleting the data using WHERE clause. We cannot use WHERE clause .
It activates triggers because the operations are logged individually. It cannot activates triggers, because it does not log individual row deletions.
Slower in performance, because it has to write data to logs. Faster in performance, no logs are maintained to logs.
Deletion of any row is possible that not violate the constraints, as long as the foreign key or any other constraint in place. TRUNCATE cannot used on a table that has any foreign key constraints. If we want to use, then We have to remove the constraints then issue TRUNCATE command on that table and then reapply the constraints.
It does not reset Identity column values. It resets the Identity column values.

I hope this article clearly explains the differences between Delete and Truncate in SQL Server.

Please leave any feedback in the comments area. Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment

SQL Server – Difference between a Stored Procedure and Function

Click Here for complete List.

In this post I will list out some of the basic differences that we should know about Stored Procedures and Functions.

  • Stored Procedures are pre-compiled objects and execution plan is reused.
Functions are executed and compiled every time it is called.
  • Stored Procedure may or may not return a value.
A function must return a value.
  • Stored Procedures can call functions.
Functions can not call Stored Procedures.
  • Stored Procedures can have input and output parameters.
Functions can have only input parameters.
  • Stored Procedures can manipulate the data, i.e., it can use DML operations on the objects.
Functions can not do DML operations, it has only SELECT Statements.
  • Stored Procedures are called using EXEC statements.
Functions can be called in a statement. i.e., in FROM, WHERE , JOIN clauses.
  • Exception handling can be done in Stored Procedures.
We can have exception handling in Functions.
  • We can have Transactions in Stored Procedures. i.e., BEGIN TRANS, ROLLBACK AND COMMIT TRANS.
We can not have Transaction management in Functions.

I hope this article comes a quick reference to understand differences between Stored Procedures and Functions in SQL Server.

Please leave any feedback in the comments area. Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment

SQL Server – Advantages of using Stored Procedures

Click Here for complete List.

In this post we will understand the advantages of using Stored Procedures over adhoc queries or inline SQL.

  1. Re-usability and retention of Execution plan.
  2. Reduces Network traffic.
  3. Better maintainability and code re-usability.
  4. Better Security.
  5. Avoids SQL Injection attack.

Let us discuss the above points below.

1.Re-usability and retention of Execution plan: When we issue a sql query, 3 things happen in SQL Server

  • It checks the syntax of the query.
  • Compiles the query
  • Generates the execution plan for re-usability.

Execution plan: It is a way to retrieve the data in the best possible way from the database, and it depends on the indexes available on the table. Every time when we issue a query it will reuse the execution plan. This results in the performance boosts when stored procedures are executed again and again.

Now a days in latest versions of SQL Server, even adhoc queries will reuse the execution plan, but a small change in the sql query leads to generation the new execution plan, even if there a space in the query.

2.Reduces Network traffic: A stored procedure usually have sql statements in the body as shown below.

     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.

    -- Insert statements for procedure here
    SELECT Statements and Business Logic

They have enough number of lines in queries. I have seen 2000 lines of queries in stored procedure. When we have such kind of business need we can make use of stored procedures. In this case, simply we need to specify the name of the procedure and the parameter values over the network, all it transfers 3 words. If we have adhoc queries we have to send 2000 lines of code over the network.

3.Better maintainability and code re-usability: If the Stored Procedure resides on the server, several applications can use that Procedure, the advantage of doing so is if there is a bug in the Stored Procedure or the logic to be changed, there is only one place to change irrespective of used by multiple applications. If it is a inline sql every applicator has to change in each application which leads to complex process.

4.Better Security: In terms of fine grain control i.e., to control at lower level

For Example: A user needs a data from a specific table and it has all the employees information. let’s say one of the managers needs IT data, if we grant access to that user he can see all the employees records, which we don’t want. In those cases we can create a Procedure and grant execution on that Stored Procedure.

5.Avoids SQL Injection attack:

They avoid sql injection attack, From the user interface elements people can type sql queries and can inject sql if use dynamic sql for execution as shown below.

Consider the below example if we executing the queries in the database as shown below.
var Location;
ShippingLocation = Request.form ("Location");
var sql = "select * from Employee where Location = '" + ShippingLocation + "'";

Assume that the user is prompted to enter the name of a Location. then query assembled by the script looks like the following:

select * from Employee where Location = ‘Hyderabad’

However, assume that the user enters the following statement:

Hyderabad'; drop table Employee–

In this case, the following query looks like this.

select * from Employee where Location = ‘Hyderabad';drop table Employee–‘

In SQL Server the semicolon (;) denotes the end of one query and the start of another statement. The double hyphen (–) indicates that the rest of the current line is a comment and should be ignored.

If the sql statement syntax is correct, it will be executed by the server, first select all records in Employee table where Location is Hyderabad. Then, SQL Server will drop Employee table.

Therefore, we must validate all user input and carefully review code that executes constructed SQL commands.

I hope this article is helpful to understand advantages of Stored Procedures in SQL Server.Please leave any feedback in the comments area. Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment