Temp Table Vs Table Variable In Sql


This article lists out
  • Difference between Temporary Table and Table Variable.
  • Global Temporary Table Declaration
  • Indexing  Temporary Table and Table Variable.
 
Temp Table Table Variable
Below is the sample example of Creating a Temporary Table, Inserting data, Selecting Data, Droping table.
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample		 Below is the sample example of Declaring a Table Variable, Inserting records into it and selecting the rows from it.
-- Create Table Variable
DECLARE @Sample TABLE
(
Id INT,
StdName VARCHAR(50)
)
--Inserting Records
INSERT INTO @Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
--Retrieve the records
SELECT * FROM @Sample
Possible to applay DDL statements ALTER, CREATE, DROP, After table declaration
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Add New Column
ALTER TABLE #Sample
ADD Age INT
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample		 Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP after its declaration.
Possible to create index explicitly,after table declaration
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Creating NONCLUSTERED index
CREATE NONCLUSTERED INDEX IndX_Name ON #Sample([StdName]);
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample		 Not possible to create Index explicitly after table declaration. only possible indexing during declaration

--Create Table Variable with primary key
DECLARE @Sample TABLE
(
Id INT PRIMARY KEY,
StdName VARCHAR(50),
INDEX IndX_Name NONCLUSTERED (StdName)
)
--Inserting Records
INSERT INTO @Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
--Retrieve the records
SELECT * FROM @Sample
Temporary Tables are not allowed in User Defined Functions Table Variables can be used in User Defined Functions.
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. Or you can access Global Temporary Table out side the procedure

/*Create and dropping Global temporary table*/

CREATE TABLE ##Sample
(
Id INT,
StdName VARCHAR(50)
)
DROP TABLE ##Sample
 Scope of the Table variable is the Batch or Stored Procedure in which it is declared
No comments:
Labels: , , ,

Difference Between Union Vs Union ALL In Sql


What Is Union In SQL?

The UNION operator is used to combine the result-set of two or more data set .
  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

Difference Between Union and Union ALL In SQL ?


/*Union Removes Duplicate Rows,So it Apply expensive Distinct sort opertaion */

SELECT 1 AS ID,'Anoop' AS Name
UNION
SELECT 1 AS ID,'Anoop' AS Name
UNION
SELECT 2 AS ID,'Manu' AS Name

/*Union ALL Returs All Rows, So it is better to use unionAll instead of union*/

SELECT 1 AS ID,'Anoop' AS Name
UNION ALL
SELECT 1 AS ID,'Anoop' AS Name
UNION ALL
SELECT 2 AS ID,'Manu' AS Name



 
No comments:
Labels:

SQL Query Optimization Tips

In every interview you might ask the basic questions related to SQL Performance Because Query optimization is very essential factor for any project.In this article I will cover most important Performance Tuning tip.




  • Use WHERE expressions to limit the size of result tables that are created with joins.
  • Avoid * in SELECT statement. Give the name of columns which you require. 
            SELECT * FROM TABLE1 
                   SELECT COL1,COL2,COL3 FROM TABLE1 /*This Is better*/
  • Use joins instead of sub-queries.
  • Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
  • Use WITH (NOLOCK) while querying the data from any table.
                  SELECT COL1,COL2,COL3
                  FROM TABLE1 WITH(NOLOCK) /*This Is better*/

  • Use TRY- CATCH to avoid deadlock condition.
  • Avoid Cursors since cursor are very slow in performance.
  • Use Table variable in place of Temp table

                 INSERT INTO #TABLE
                 SELECT COL1,COL2,COL3
                 FROM TABLE1 WITH(NOLOCK) /*Instead of This*/

                INSERT INTO @TABLE
                SELECT COL1,COL2,COL3
                FROM TABLE1 WITH(NOLOCK) /* This is better handling with small row set*/

  • Use UNION ALL in place of UNION if possible.
    Try to avoid triggers if possible.
  • Avoid Using DISTINCT clause
  • Use EXISTS instead of IN to check existence of data.
  • Avoid nchar and nvarchar if possible since both the data types takes just double memory.
  • Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
  • Use Schema name before SQL objects name
  • Use Stored Procedure for frequently used data and more complex queries.
  • Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
  • Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
  • Use set NOCOUNT ON at the top of each stored procedure.
  • Remove calculations in your JOIN and WHERE clauses , Avoid using OR in join conditions
                  SELECT ID
           FROM TABLE1 a
                JOIN
             TABLE2 b ON
             ((year(a.COL1)* 100) + month(a.COL2)) = b.COL3
  • Follow proper indexing method and remove unwanted index
           















6 comments:
Labels:

TSQL Interview Tips


Basic TSQL interview tips that has been asked in many companies.
1. What is DBMS?
A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

2. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

 3. What is SQL?
SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.
Standard SQL Commands are Select.

4. What is a Database?
Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.
Example: School Management Database, Bank Management Database.

 5. What are tables and Fields?
A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.
Example:.
Table: Employee.
Field: Emp ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960

6. What is a primary key?
A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

7. What is a unique key?
A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

 8. What is a foreign key?
A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

9. What is a join?
This is a keyword used to query data from more tables based on the relationship between 
the fields of the tables. Keys play a major role when JOINs are used.

10. What are the types of join and explain each?
There are various types of join which can be used to retrieve data and it depends on 
the relationship between tables.
  
Inner join.
Inner join return rows when there is at least one match of rows between the tables.
  
Right Join.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

Left Join.
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.





1 comment:
Subscribe to: Posts (Atom)