Archive for August 2012

Temporary Tables


If we can create permanent (persistent) tables in database, why to go for temporary tables. Many people use temporary tables in database without knowing what the purpose and how it will add to performance tuning of queries.

This article will brief you about various types of temporary tables and will show you the examples for each type of temporary table.

"Divide'n'Conquer" a large "problem".

You can get absolutely mind boggling blinding speed out of some queries. This is the rule based on which temporary table concept came into picture. Many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have couple of joins and that previous took anywhere from 30 minutes to 1 hour to run and have gotten them to run in seconds.

Temporary can be created at runtime and can do the all kinds of operations that one normal table can do. Scope of the temporary tables is limited to the session or instance. These tables are created in tempdb.

Temporary tables are used in the following scenarios:
  • When we are doing large number of row manipulation in stored procedures. In complex joins, instead of joining the main table directly we can use temporary tables. This will reduce the data load on the table which in turn reduces the execution time of the query.
  • This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
Some important information about temp tables:
  • Temporary table can't have foreign key constraints on it.
  • The best way to use a temporary table is to create it and then fill it with data. i.e., instead of using Select into temp table, create temp table 1st and populate with data later

Different Types of Temporary Tables

In SQL SERVER we can create the following types of temporary tables.
  • Local Temp Table
  • Global Temp Table
  • Table Variable

Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign
Here is an example for local temp table.



 

 

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
Here is an example for global temp table.









 
scope of Local Temporary table is only bounded with the current connection of current user. Where as, the scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables
We can create a table a global table in one instance of database and we can use it in another instance of the same database until we disconnect from database.

Table Variable

We have a data type called TABLE in sql server. We can make use of this data type to create temporary tables in database. Here is the syntax for temporary table variable.








If you have less than 100 rows generally use a table variable.  Otherwise use a temporary table.  This is because SQL Server won't create statistics on table variables.
  
Temporary tables are stored in tempdb as shown below:





















 

Posted in | Leave a comment

Examples in SQL SERVER

Posted in | Leave a comment

Example for Creating a stored procedure with input and output parameteres

Example 1:

Create a stored procedure to find out the class of a student based on the student id.

Here, Student ID is an input parameter and Class ID is a output parameter.


Query:

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

Example 2:

Execute the above stored procedure to find of the class of a student whose id is 5

Query:

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


Posted in | Leave a comment

Example for creating a stored procedure with an input parameter

Example 1:

Create a stored procedure Student id, firstname and lastname from STUDENT table by joining the STUDENT_CLASS table Where class = @Class_Id_parameter

Query:


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


Example 2:  Execute the above stored procedure to fetch 10th class student's information/

Query:

EXEC USP_STUDENTS_IN_CLASS 10




Posted in | Leave a comment

Example for creating a simple stored procedure

Example:

Create a simple stored procedure to fetch complete data from STUDENT table.

Query:


CREATE PROC STUDENT_DATA
AS
BEGIN
      SELECT * FROM STUDENT
END


Posted in | Leave a comment

Example for Inserting Data into a table

Example:

Insert data into STUDENT table without explicitly providing column names.



Query:

INSERT INTO STUDENT
VALUES
(    
      4,
      'Dennis',
      'Charle',
      'Demarcus'  ,
      '1000 Coney Island Ave. Brooklyn NY 11230',
      '3022151541'
)


Posted in | Leave a comment

Example for Truncate table

Example:

Truncate data from STUDENT table.

Query:

TRUNCATE TABLE STUDENT

Posted in | Leave a comment

Example for Deleting complete data from table

Example:

 Delete complete information from STUDENT table.

Query:

DELETE FROM STUDENT


Posted in | Leave a comment

Example for Deleting data from a table based on a condition

Example:

Delete data from STUDENT table where student id =1

Query:

DELETE FROM STUDENT
WHERE STUDENT_ID =1



Posted in | Leave a comment

Example for Update data in a table

Example:

Update FIRST_NAME to 'JOE' in STUDENT table for the student whose Id is 1 .

Query:

UPDATE STUDENT
SET FIRST_NAME = 'JOE'
WHERE STUDENT_ID =1


Posted in | Leave a comment

Insert data Examples

Example:  Insert data into STUDENT table .

Structure for Student table can be found in the below path:
STUDNET Table


Query:

INSERT INTO STUDENT
(
      STUDENT_ID,
      FIRST_NAME,
      LAST_NAME, 
      FATHE_NAME,
      ADDR  ,    
      PHONE
)
VALUES
(    
      1,
      'JAMES',
      'CAMERO',
      'AARON'     ,
      '19 Quaker Ridge Rd.,Bethel CT 06801',
      '6023458976'
)

Posted in | Leave a comment

Create Table Example

Example:

Create sample tables like Student, Class etc.,

Queries: 

                  STUNDENT Table:

CREATE TABLE STUDENT
(
      STUDENT_ID        INT,
      FIRST_NAME        VARCHAR(25),
      LAST_NAME         VARCHAR(25),
      FATHE_NAME        VARCHAR(25),
      ADDR              VARCHAR(400),
      PHONE             BIGINT
)
  
                  CLASS Table:

CREATE TABLE CLASS
(
      CLASS_ID                INT,
      CLASS_TEACH_ID          INT
)
    

 
                  TEACHERTable:

CREATE TABLE TEACHER
(
      TEACHER_ID        INT,
      FIRST_NAME        VARCHAR(25),
      LAST_NAME         VARCHAR(25),
      ADDR              VARCHAR(400),
      PHONE             BIGINT
)

                  STUNDET_CLASS relation Table:

CREATE TABLE STUDENT_CLASS
(
      STUDENT_ID              INT,
      CLASS_ID                INT
)

Posted in | Leave a comment

USE Clause Example


Example: 

Using USE  clause in SQL Server , database can be changed to point to the required database instead of default database.

Change the database from default database to EXAMPLE database

Query:

 USE EXAMPLE

Before running the USE EXAMPLE command database was pointing to 'master' database.





After running the USE EXAMPLE command database was pointing to EXAMPLE database.




Posted in | Leave a comment

Create database example by defining size etc.,

Example: 

Create a database with the name EXAMPLE by providing File Name, File Path, Database size, log size etc.,

Query:

CREATE DATABASE EXAMPLE
ON PRIMARY
      (    
      NAME = N'EXAMPLE',
      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EXAMPLE.mdf' ,
      SIZE = 1216KB ,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 1024KB
      )
LOG ON
      (
      NAME = N'EXAMPLE_log',
      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EXAMPLE_log.LDF' ,
      SIZE = 504KB ,
      MAXSIZE = 2048GB ,
      FILEGROWTH = 10%)



Note:  Simple database creation query need not require all these parameters. If we don't provide the parameters , then SQL Server will create the database in a default path with default sizes for Database and log.

Posted in | Leave a comment

Drop database Example


Example:

Drop a database with the name EXAMPLE in SQL SERVER.

QUERY:

DROP DATABASE EXAMPLE



Posted in | Leave a comment

Database Creation Example



Example: 

Create a database with name EXAMPLE in SQL SERVER.

Query:

CREATE DATABASE EXAMPLE



Posted in | Leave a comment

@@Functions in SQL SERVER

Posted in , | Leave a comment

@@LANGUAGE

@@LANGUAGE is a build in function in sql server which displayes information about the language that is being used in your instance of SQL SERVER.















Posted in , | Leave a comment

@@VERSION

@@VERSION  is an inbuild function which returns the version number of the sql server taht is running in your machine.




Posted in , | Leave a comment

@@SERVERNAME


@@SERVERNAME is an inbuild function in sql server which return the Server name as output.









Posted in , | Leave a comment

SQL SERVER OBJECT TEMPLATES -- FUNCTION

Template for creating a function in sql server is given below:
-----------------------------------------------------------------------



/****************************************************************
*This script file creates the function FN_FUNCTION_NAME *
****************************************************************/
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the function already exists


IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGIN-- If it exists, drop to recreate the function
DROP FUNCTION dbo.FN_FUNCTION_NAME
IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGINPRINT 'FUNCTION FN_FUNCTION_NAME DROPPED FAILED'
END
ELSE
BEGINPRINT 'FUNCTION FN_FUNCTION_NAME DROPPED SUCCESSFULLY'
END
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************
/
CREATE FUNCTION dbo.FN_FUNCTION_NAME(
/* Function parameters like
@pdtDate DATE
*/
)

RETURNS DATATYPE
-- Datatypes of output_parameter (Return parameter)
AS
BEGIN

/* Function Logic */
-- This will return the value output_parameter
RETURN output_parameter
END
GO
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the function

IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGIN
PRINT 'FUNCTION FN_FUNCTION_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'FUNCTION FN_FUNCTION_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/



Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- INDEX CREATION

Template for creating index script in sql server:
--------------------------------------------------------


/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the index already exists

IF EXISTS ( SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger

BEGIN
-- If it exists, drop to recreate the index on table TBL_TABLE_NAME
DROP INDEX dbo.TBL_TABLE_NAME.IX_INDEX_NAME
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE                     VERSION           NAME                    COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY          1.0               Developer Name           Initial Draft *
** *
*****************************************************************/

CREATE UNIQUE CLUSTERED INDEX IX_INDEX_NAME
ON TBL_TABLE_NAME
( COLUMN_NAME1

,COLUMN_NAME2
)

GO

/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the index

IF EXISTS (SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/

Posted in | Leave a comment