Archive for May 2012

System Stored Procedures in SQL Server

Posted in | Leave a comment

sp_rename

sp_rename is a system stored procedure which is used to rename the database objects in sql server.

Using sp_rename any database object (table, stored procedure, view, column etc.,) can be rename .

Syntax for sp_rename is:

sp_rename <old database object Name> , <New database object Name>

                                         (or)

sp_rename @objname = <object old name>,
                @newname =<object new name>,
                @objtype = <Type of the database object>

@objtype in the above syntax represents database object type . By default this is NULL.


Example to Rename database 'Example' to 'Sample' following query can be used.

sp_rename 'Example','Sample','DATABASE'







Posted in | Leave a comment

sp_password

sp_password is a system stored procedure which is used to change the password.

syntax for sp_password is :

sp_password <old password > , <New Password > 

                               (or)

sp_password @old = <old password >, @new =  <New Password>

Example:

sp_password 'MY_OLD_PWD','MY_NEW_PWD'

Posted in | Leave a comment

sp_stored_procedures


sp_stored_procedures is a system stored procedure which will result in all the stored procedures that are present in a particular database.

That is sp_stored_procedures results in all system stored procedures, user defined stored procedures and extended stored procedures information in a database.

Syntax for sp_stored_procedures is:

sp_stored_procedures

Example :

Following picture represents the execution result  of sp_stored_procedures stored procedure in EXAMPLE database.





Posted in | Leave a comment

sp_tables

sp_tables is a system stored procedure which will result in all the table names and view names in a particular database:

Syntac for sp_tables is :

sp_tables

Example:

I executed sp_tables stored procedure in example database. Below picture represents the execution result for this.





Posted in | Leave a comment

sp_datatypeinfo

sp_datatypeinfo is a system stored procedure which result in all data types that are accepted by you sql server version on their corresponding data types, precision and etc, information.

syntax for sp_datatypeinfo is:

sp_datatypeinfo <data type>

If we need data type information for all the data types in sql server syntax will be:

sp_datatypeinfo

Example:

Following picture shows the result of sp_datatypeinfo


Posted in | Leave a comment

sp_databases

sp_databases is a system stored procedure which will return all the databases in a SERVER along with the database sizes:

syntax for sp_databases stored procedure is:

  • sp_databases : This will return all databases information.
  • sp_databases <database Name >: This will return only 1 particular Database information.

Example:

Following picture represents the database information for all the databases in SQL Server .




Posted in | Leave a comment

sp_depends

sp_depends is a system stored procedure which will be used to find out the dependent objects of a particular database object.

Syntax for sp_depends is:

sp_depdens <database object Name>

Example:

Here is an example for sp_depends in case of stored procedure:

Syntax foe finding out dependent database objects on a stored procedure is :

sp_depends < stored procedure Name>

This will return all the tables, stored procedures, function , views etc., database objects which are used to build stored procedure.

Below picture shows the dependent database object on USP_CLASS_OF_STUD stored procedure:




Posted in | Leave a comment

sp_helptext

sp_helptext is a system stored procedure in sql server which is used to find the definition or structure of a stored procedure.

syntax for sp_helptext is:

sp_helptext <stored procedure Name>


Example:

Here is an example for the usage of sp_helptext to find out the definition of USP_CLASS_OF_STUD stored procedure.





Posted in | Leave a comment

sp_help

sp_help is a system stored procedure in sql server which is used to find the structure of the database objects.

syntax for sp_help is :

sp_help < database object Name>

Example : sp_help will retun all the following information in case  tables:
  1. Table Name , database owner and table created time.
  2. columns and their corresponding data types 
  3. Identity column if any
  4. constraints like primary key etc if any.
Below picture shows the execution result of sp_help system stored procedure for tableSTUDENT.



In case of stored procedures sp_help will return the following information:
  • Stored procedure , database object owner and procedure created time
  • Input and output parameters used in stored procedure if any.
Following picture represents the output of the sp_help for a stored procedure:




Posted in | Leave a comment

Maximum Number of Non-Clustered indexes per table?


In the earlier version of SQL Server maximum number of Non-clustered indexes per table was limited to only 249.

But, in later version of SQL Server 2005, maximum number of Non-Clustered indexes accepted in a table is: 999


Posted in | Leave a comment

Maximum Number of Columns that can be used in Primary Key?


A primary key can contain maximum of 16 columns.



Posted in | Leave a comment

Maximum Number of Columns that can be used in Primary Key?


A primary key can contain maximum of 16 columns.



Posted in | Leave a comment

Maximum Number of Columns that can be used in Foriegn Key?


A Foreign key can contain maximum of 16 columns.


Posted in | Leave a comment

sp_addserver

sp_addserver is a system stored procedure which is used to add a server in sql server.

Syntax for sp_addserver is:

sp_addserver 'Server New Name', 'Actual Server'

sp_addserver is generally used to rename a server.


Posted in | Leave a comment

sp_dropserver

sp_dropserver is a system stored procedure which is used to drop a server from a sql server.

Syntax for this system stored procedure is:

sp_dropserver <Server Name>

This stored procedure is generally used to rename the server.

Posted in | Leave a comment

sp_helplanguage

sp_helplanguage is a system stored procedure which will display a;; the languages supported by the sql server version that is installed on your machine.




Posted in | Leave a comment

System Stored Procedures

Many administrative activities are performed inside the sql server using system stored procedure.

Following are some of the frequently used system stored procedures in sql server:




Posted in | Leave a comment

What are the various ways in which a stored procedure can be executed?


A stored  procedure can be invoked or executed using the following syntax :


  1. EXEC < Procedure Name>
  2. EXECUTE <Procedure Name>
  3. <Procedure Name>
A stored procedure with the name 'USP_SAMP' can be executed using one of the following queries:

  • EXEC USP_SAMP
  • EXECUTE USP_SAMP
  • USP_SAMP 




 

Posted in | Leave a comment

Maximum Number of Variables that can be declared inside stored procedure?


It depends on the space available in the disk.

So, there is no exact limit for the number of variables that can be declared inside stored procedure.



Posted in | Leave a comment

Maximum Number of characters in a stored procedure Name?

Maximum of 128 characters are accepted in a stored procedure Name.

Below screen shot shows the error message displayed in sql server when a stored procedure with name more than 128 characters is created.



Posted in | Leave a comment

What are objects that cannot be created inside stored procedure?



Following objects cannot be created inside the stored procedure.

PROCEDURE
DEFAULT CONSTRAINT
TRIGGER
VIEW
RULE

Other database objects like TABLE, INDEX etc., can be created inside the stored procedure.


Posted in | Leave a comment

Maximum Number of Columns that can be used in Foriegn Key?


A Foreign key can contain maximum of 16 columns.


Posted in | Leave a comment

Maximum Number of Columns that can be used in Primary Key?


A primary key can contain maximum of 16 columns.



Posted in | Leave a comment

Maximum Number of Non-Clustered indexes per table?


In the earlier version of SQL Server maximum number of Non-clustered indexes per table was limited to only 249.

But, in later version of SQL Server 2005, maximum number of Non-Clustered indexes accepted in a table is: 999


Posted in | Leave a comment

Maximum Number of rows that are acceptable for a table in sql server?


A table can have any number of rows. Maximum number of rows allowed in table dependents on the storage available in the disk.


Posted in | Leave a comment

Maximum Number of parameters accepted in an user defined function?


Maximum Number of parameters that can be passed to an user defined function is: 2100


Posted in | Leave a comment

How to find the dependent objects of stored procedure in sql server?

sp_depends is the system stored procedure which is used to fetch the dependent objects of any database objects.

Syntax to find out the dependent objects of a stored procedure is :

sp_depends <Stored Procedure Name>


Example  to find out the Dependent objects of stored procedure USP_CLASS_OF_STUD is:

sp_depends USP_CLASS_OF_STUD




Posted in | Leave a comment

How to find the dependent objects of stored procedure without using sp_depends?


Following query can be used to find the dependent objects information of a stored procedure without using sp_depends:


select name from sysobjects where id
in
(select dep.depid
from sysdepends  dep
inner join sysobjects obj
on dep.id= obj.id and
obj.name=<Stored Procedure Name>)


Example: 

To find out the dependent objects of USP_CLASS_OF_STUD stored procedure, following query will be used.

select name from sysobjects where id
in
(select dep.depid
from sysdepends  dep
inner join sysobjects obj
on dep.id= obj.id and
obj.name='USP_CLASS_OF_STUD')



 

Posted in | Leave a comment

How to find the definition or structure of stored procedure without using sp_helptext?


 Following query can be used to find out the structure of a stored procedure without using sp_helptext:

select text from syscomments 
where id =(SELECT id from sysobjects where name =<Stored Procedure Name>
 






Text column in syscomments table will contain the definition for all the database objects in sql server.

Syscomments table will not hold the object name. It will contain just object id. So, object id for a particular object can be fetched from sysobjects table.

Posted in | Leave a comment

Maximum Number of parameters accepted in a stored procedure


Maximum Number of parameters accepted in a stored procedure is 2100.

Here is an example of the stored procedure in which I tried to use more than 2100  parameters:





Posted in | 1 Comment

How to find the definition or structure of stored procedures?

Structure or definition of the stored procedure is saved inside the syscomments  table.

Definition of the stored procedure can be obtained by using the following commands:

sp_helptext  <Stored Procedure Name>


Other way to find out the structure of the stored procedure is Using the following query to fetch stored procedure structure:

select text from syscomments where id =(SELECT id from sysobjects where name =<Procedure Name>) 






Posted in | Leave a comment

How stored procedures are saved?

We know that stored procedures are special database objects which are stored in database and can be invoked or executed as and when needed.
But, where these stored procedures are stored in database? And how are the stored procedures stored?

In this article we will discuss in detail about how and where stored procedures are saved.

When stored procedures are compiled, these stored procedures are saved in the following 3 system tables:

Sysdepends: This table will contain all the database objects which are referred or used in stored procedure like tables,columns,functions,Stored procedures, etc.,

Number of records inserted in this table for each stored procedure depends on number of dependent objects in that stored procedure.

Sysobjects : When ever any database object is created in the database an entry will be made in this table. Since Stored procedure is also a database object, this table will have an entry for this as well.

Syscomments : Text column in this table will hold the complete structure of stored procedure. That is text column in this table will contain the SQL statements which are used for creating stored procedure.



Posted in | Leave a comment

What are the checks performed by the SQL Server while compiling stored procedures?

SQL Server will parse the queries that are present in stored procedure and checks for the following:
  • Check whether all the queries are syntactically correct or not.
  • Check whether all the tables listed in stored procedures exists in database or not. If some tables are internally created as part of stored procedure, then temporary tables will be created fro time being and these tables will be used while compiling the query. These tables are not permanent. They will not be present in the database after stored procedure compilation. This type of compilation is known as Deferred Name Resolution.
  • Check whether all the columns which are used in stored procedure exists in data base in appropriate tables.




Posted in | Leave a comment

Stored procedures with input and output parameters

Stored procedures can be used to return values. Stored procedures can return int, char, varchar etc., data type values. But, then can not return table variables.

Syntax for the stored procedures with input and output parameters is:

           CREATE PROC <Procedure Name>
           @Input_Var     <Data Type>,
           @Output_Var  <Data Type>  OUTPUT
           AS
           BEGIN
                      <T-SQL Commands>
           END

Note:  Any number of input and output commands can be passed to the stored procedure till it reaches maximum parameters limit.

Example: Let’s create a stored procedure to find out to which class a student belongs to if student id is provided.

In this scenario student Id will be an input parameter and class will be output parameter.

Stored procedure:

           CREATE PROC USP_CLASS_OF_STUD
           @STUDENT_ID INT,
           @CLASS_ID   INT OUTPUT
           AS
           BEGIN
                      SELECT @CLASS_ID = CLASS_ID  
                       FROM STUDENT_CLASS  
                      WHERE STUDENT_ID      =@STUDENT_ID


                      RETURN @CLASS_ID
           END


Execute the above stored procedure to find out the class of a student whose id is 11.

DECLARE @CLASS INT
EXEC USP_CLASS_OF_STUD 11, @CLASS OUTPUT
SELECT @CLASS



Posted in | Leave a comment

Stored procedures with input parameters and without output parameters

Input parameters can be passed to stored procedures as and when required. Here is the syntax for the stored procedure with input parameters and without output parameters.

Syntax:

              CREATE PROC <Procedure Name>
              @Input1 <Data type>,
              @Input2 <Data type>,
 
              @InputN <Data type>,
               AS
               BEGIN
                          <T-SQL commands>
               END

Here is an example for the stored procedure with input parameters. In this Scenario, I would like to know the students who are in a particular class.
For this case, we will be passing Class Id as input parameter.

Stored procedure for the above scenario is:

Stored Procedure:

              CREATE PROC USP_STUDENTS_IN_CLASS
              @CLASSS_ID  INT
              AS
              BEGIN
                   --Select the student deatils whose class id is @CLASSS_ID
                   SELECT
                          S.STUDENT_ID,
                          S.FIRST_NAME,
                          S.LAST_NAME
                   FROM STUDENT      S
                   INNER JOIN STUDENT_CLASS      SC
                          ON    S.STUDENT_ID =SC.STUDENT_ID
                          AND SC.CLASS_ID= @CLASSS_ID
              END

Query to fetch the students who are in 5th class, following query can be executed. That is following query will be used to run the above stored procedure with the input parameter 5.


              EXEC USP_STUDENTS_IN_CLASS 5






Posted in | Leave a comment

Stored procedures without input and output parameters

Stored procedures can be created without input parameters. Here is the syntax for the stored procedure without input parameters.

                   CREATE PROC < Procedure Name>
                   AS
                   BEGIN
                         < T-SQL Commands>
                   END

Here is a simple stored procedure to retrieve data from STUDENT table:


CREATE PROC STUDENT_DATA
AS
BEGIN
      SELECT * FROM STUDENT
END

 Uses of creating stored procedure in this scenario is:

  • Instead of running the entire set of T-SQL Commands which are inside the stored procedure, we can just run the stored procedure using the following syntax to get the same result.
 
Result obtained by running the T-SQL Commands inside the stored procedure:



Same result should be obtained even by running the stored procedure.

Here is the syntax to Execute a stored procedure:


Syntax:

EXEC <Procedure Name>

 Result obtained by running Stored procedure is :


Posted in | Leave a comment

Why stored procedures are preferred in cross applications?


If we are running set of queries as part of a process, then sending or transmitting all those queries through network will increase the network traffic and it uses more bandwidth. Stored procedures internally encapsulate the entire business logic in a unit. By invoking just stored procedure name will reduce the burden on network. Also, this uses less bandwidth. This is the reason Stored procedures are more preferred in cross applications than independent queries.

Posted in | Leave a comment

Why stored procedures are faster than independent queries?

 Stored procedures are parsed and optimized as soon as they are created or complied in the database. Compiled stored procedures are stored in the memory. Database will internally stores the execution plan of a stored procedure. When ever a stored procedure is invoked or executed same query plan will be re-used. Running independent queries means that we are sending the queries 1st to database optimizer which will create the query plan and will execute that query plan. By using the stored procedure this extra burden on the database can be reduced, which in turn will result in a better performance and faster query or process execution,

Posted in | Leave a comment

Advantages and Disadvantages of Stored Procedures

Stored procedures are special Sql server objects which will be stored in database.

Advantages of  stored procedures:

  • Script re-usability: Script complied once can be reused as many times as needed.  
  • Execution Plan Retention and Re-usability:  Execution plan will be stored in the database for a stored procedure and same plan will be executed when ever a stored procedure runs.
  • Encapsulation of complete business logic in a unit:  Stored procedure encapsulates the complete process business logic inside the stored procedure. Instead of executing all the queries, it’s easy and safe to execute the stored procedure.
  • Safety and Security: Stored procedure can be granted with access. This will allow only intended resources to utilize or run the stored procedure. Security feature in stored procedures shield the user from directly accessing the tables in a database.
  • Easier to call from external application:  Stored procedures can be easily invoked from external applications like C#.net , Asp.Net and web services.
  • Client- Server traffic will be reduced by using stored procedures since this will use lesser bandwidth compared to the queries.
  • Temporary stored procedures are also supported in SQL Server. This feature will allow stored procedures to be created in temp db just like temporary tables. That is # and ## stored procedures can be created in SQL SERVER. They will automatically be dropped when you disconnect from the server.
  • Simple and Easy to use: When a process needs to be executed or invoked from some external application, we need not transfer all sql queries involved in the process. Instead we can just call the stored procedure.

Disadvantages of stored procedures:

  • Execution plan of the stored procedure is cashed.  Same Execution plan will be used and executed whenever a stored procedure is executed. Some times old execution plan may not be using the latest indexes. In those cases execution plan will not be correctly optimized. This will hinder the performance of the stored procedure.

Posted in | Leave a comment

Stored Procedures in SQL Server

As the Name implies stored procedures are data base objects which store the business logic or procedure in a complied script. These are special data base objects. They are pre-compiled T-SQL statements/queries.

Entire business logic of a process can be encapsulated in a stored procedure and this can be used when ever needed. This will reduce the overhead of writing the same queries again and again. Executing stored procedures is comparatively faster than executing the queries independently. Also, stored procedures use less bandwidth. That is the reasons stored procedures are most preferred for the cross applications.

Syntax for the stored procedures is:

CREATE PROCEDURE <Procedure Name>
<Input Parameters comma separated> , <Output parameters comma separated>
AS
BEGIN
< T-SQL Statements >
END

Stored procedures can be classified broadly into 3 categories:

  1. System stored procedures
  2. User Defined stored procedures
  3. Extended stored procedures

User defined stored procedures are again classified into the following categories:

  1. Stored procedures without input and output parameters (Simple stored procedures)
  2. Stored procedures with input parameters and without output parameters
  3. Stored procedures with input and output parameters.

We will discuss in detail about all these stored procedure types in the coming sessions.

Posted in | Leave a comment