Store temporary data in Stored Procedure in SQL

We  often have a problem to store a set of temporary data within the procedure, which not persist beyond the scope of the procedure. In such situation, we have basically four ways through which we can "store" this temporary : local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name). 

     Each of the four table options has its own purpose and use, and each has its benefits and issues:

1. Normal tables are exactly that, physical tables defined in your database.
2. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
3. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
4. Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Temporary Table

We 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. 

Example of Temp table:

CREATE TABLE #temp (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO #temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from #temp

drop table #temp

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data

Example of Table variable:

DECLARE @temp TABLE (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO @temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from @temp

Note: Table variables don't need to be dropped when you are done with them.

Which to Use

·         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.

·         If you need to create indexes on it then you must use a temporary table.

·         When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Cursor in SQL with example

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done
5. Deallocate  the cursor

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE
• DEALLOCATE

Declare @Firstname varchar(50)

Declare @Lastname varchar(50)

DECLARE @Email varchar(20)

DECLARE Student_cursor CURSOR FOR

Select Firstname,Lastname,Email from from student

OPEN Student_cursor;

FETCH NEXT FROM Student_cursor

INTO @Firstname, @Lastname, @Email;

WHILE @@FETCH_STATUS = 0

BEGIN

print ‘---------------‘

print @Firstname

print @Lastname

print @Email

print ‘---------------‘

FETCH NEXT FROM Student_cursor

    INTO @Firstname, @Lastname, @Email;

END

CLOSE Student_cursor;

DEALLOCATE Student_cursor;

Check the status of Cursor

--Create a cursor.

DECLARE cur CURSOR

Display the status of the cursor after declare

SELECT CURSOR_STATUS('global','cur') return -1

 OPEN cur

Display the status of the cursor after open

SELECT CURSOR_STATUS('global','cur') return 1

CLOSE cur

Display the status of the cursor after close

SELECT CURSOR_STATUS('global','cur')return -1

--Remove the cursor.

DEALLOCATE cur

Data Integrity in Database

Data Integrity in Database

It is very important for the data in a database to be accurate, consistent, and reliable. This implies that it is very crucial to enforce data integrity.

Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following four categories:

Entity integrity

Domain integrity

Referential integrity

User-defined integrity

Entity Integrity

 Entity integrity ensures that each row can be uniquely identified by an attribute called the primary key. The primary key cannot be NULL.

For example, there might be two students with the same name ‘John’. They can be identified using the unique code assigned to them.

Domain Integrity

Domain integrity ensures that only a valid range of values is allowed to be stored in a column. It can be enforced by restricting the type of data, the range of values and the format of the data.

For example, let’s assume a table called BranchOffices that stores the address of various offices of a company. Let’s also assume it has a column city that stores the name of cities where the branch offices are located. Assume that offices are located in  ‘Boston’, ’California’ ,’Dallas’, ’New York’, ’Washington’. By enforcing domain integrity we ensure that only valid values can be entered in the city column of the BranchOffice table.

Referential Integrity

Referential Integrity ensures that the values of the foreign key match with the values of the corresponding primary key.

For example, if a toy has been ordered and an entry is to be made in the OrderDetail table, the the code should exists in the Toys tables. This is to ensure that an order is being placed for the available toys.

User-defined Integrity

User-defined Integrity refers to a set of rules specified by a user, which do not belong to the entity, domain and referential integrity categories.

For example, you do not want a candidate who is less than 18 years to apply for a post.

Union in SQL

The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. Each query statement should have same column structure: same number of columns, same or compatible data types and in same order. The columns in each SELECT statement must be in exactly the same order too. You have to keep the column order of all unionized SQL SELECT expressions uniform, otherwise you’ll get an error.

SQL UNION Query Syntax

SELECT ColumnName(s) FROM Table1  

UNION  

SELECT ColumnName (s) FROM Table2  

Note: By Default result  of UNION is distinct. If you want all record you have to use UNION ALL

SQL UNION Query Example

Table: TestingEmployee

EmployeeId	FirstName	LastName
203	Rishi	Rawat
204	Michel	Jones
205	Nathen	Astel
206	Monica	George

Table: DevelopmentEmployee

EmployeeId	FirstName	LastName
206	Monica	George
207	Anita	Paul
208	John	Pit
209	Jennifer	Bob

Select all the different employees in TestingEmployee and DevelopmentEmployee:

SELECT EmployeeId, FirstName, LastName FROM TestingEmployee

UNION  

SELECT EmployeeId, FirstName, LastName FROM DevelopmentEmployees  

The result will look like:

EmployeeId	FirstName	LastName
203	Rishi	Rawat
204	Michel	Jones
205	Nathen	Astel
206	Monica	George
207	Anita	Paul
208	John	Pit
209	Jennifer	Bob

Note: Record of EmloyeeID 206 is in both tables but in result it display only one time if we use UNION ALL then the result display duplicate records for EmployeeId 206