Optimization Of Stored Procedure In SQL Server

This article describes that how we can optimize the Stored Procedures so that we can get better performance while our application is talking to database. I am enlisting some of the points from my personal experience.

 

1)    Always Use Fully Qualified Name For All Database Objects:

While working with stored procedure we need to pass name of database objects (table, view, function, other stored procedure(s) etc.) several times. I recommend every developer to use fully qualified object names instead of using only object name. There is a very strong reason behind this recommendation.

• 
  

  SQL server always has to find supplied database object name from sys object that took some millisecond of processing time and consumption of CPU resources. And if, we pass fully qualified name then that processing time and CPU resource consumption to search the object from sys objects get reduced to significant amount.

  
  


• 
  

  Another reason behind the scene is it helps SQL Server to directly finding the Complied Cache Plan if available instead of searching the objects in other possible schema. This process of searching and deciding a schema for a database object leads to COMPILE lock on stored procedure which decreases the performance of a stored procedure.

  
  

Database Concept

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Top 10 features of SQL Server 2008

Introduction

                      I this article i am going to describe top 10 features of SQL Server 2008 database.

Description

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg.

Instead of:

DECLARE @myVar int
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

Temporary Table Concept In SQL Server

Introduction

This article covers the concept of temporary tables and tables variables in SQL server database.

Temporary Tables

Lets take a look at a simple CREATE TABLE statement that is going to create a temporary table:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Points to consider while designing database

SQL Server DO's and DON'Ts

DO know your tools.

Please, don't underestimate this tip. This is the best of all of those you'll see in this article. You'd be surprised of how many SQL Server programmers don't even know all T-SQL commands and all of those effective tools SQL Server has.

"What? I need to spend a month learning all those SQL commands I'll never use???" you might say. No, you don't need to. But spend a weekend at MSDN and browse through all T-SQL commands: the mission here is to learn a lot of what can and what can't be done. And, in the future, when designing a query, you'll remember "Hey, there's this command that does exactly what I need", and then you'll refer again to MSDN to see its exact syntax.

In this article I'll assume that you already know the T-SQL syntax or can find about it on MSDN.

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

 Following are some of the techniques that i remember now to optimize the SQL :--

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;