Temporary Tables

Tables are created in the tempdb and are automatically deleted when they are no longer in use.


Two Types of Temporary Tables.

1. Local Temporary:

Local temporary tables are the tables stored in tempdb. 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 orsession. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.

Example:

1. create table #table_name  
2. (  
3. column_name varchar(20),  
4. column_no int  
5. )

2. Global Temporary:

Global temporary tables are also stored in tempdb. 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. They are specified with the prefix #, for example ##table_name.

Example:

1. create table ##GlobalTemporaryTable  
2. (  
3. column_name varchar(20),  
4. column_no int  
5. ) 

What is Table Variable

Table Variable:

A Table Variable is a variable that can store the complete table of the data inside.

Syntax:

1. Declare @<Variable_Name> TABLE(  
2. Column_Name [Data_Type],  
3. Column_Name [Data_Type],  
4. Column_Name [Data_Type],  
5. ......  
6. )  

Example:

Declare @TempTable TABLE(      

id int,    

Name varchar(20)      

)      

begin tran T  

insert into @TempTable values(1,'Sourabh Somani')  

insert into @TempTable values(2,'Shaili Dashora')  

insert into @TempTable values(3,'Divya Sharma')  

insert into @TempTable values(4,'Swati Soni')  

commit tran T  

Select * from @TempTable  

Difference between temporary tables and Table Variable

1. A Table Variable is not available after execution of the complete query so you cannot run a single query but a temporary table is available after executing the query. 

2. A Transaction (Commit and Rollback) operation is not possible in a Table Variable but in a temporary table we can perform transactiona (Commit and Rollback).

⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

⇒ Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.

⇒ Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

⇒ Temporary table allows Schema modifications unlike Table variables.

Points to Remember:

Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.

Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.

What is CTE (Common table Expression).

What is Common Table Expression:

Ans:

A CTE (Common Table Expression) defines a temporary result set which you can then use in a SELECT statement.  It becomes a convenient way to manage complicated queries.


Syntax:

With expression_name (columnname1, columnname2)
(
Cte query defination
)
Select columnname from expression_name

Example:

WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
        JobTitle
 FROM   HumanResources.Employee)
SELECT EmployeeNumber,
       Title
FROM   Employee_CTE


Advantage:

Can be used to create a recursive query.
Can be substituted for a view.
Allow grouping by a column which might be derived from a scalar subset.
Can reference itself multiple times.


DisAdvantage:

The Recursive member can refer to the CTE only once.

Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

Table Variables and CTE’s cannot be passed as parameters in stored procedures.




Difference between CTE and View

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive

Difference Between Row_Number() Rank() And Dense_Rank() In SQL Server

Difference Between  Row_Number()  Rank() And  Dense_Rank() In SQL Server

Ans:

Row_number plays a very important role in SQL server. Row_Number function can help to perform more complex ordering of row in the report format than allow the over clause in SQL standard.


Partition

ROW_NUMBER() Function with Partition By clause

If we want to add row number to each group, and it is reset for every group, let's take a look at the Employe table

Example:

Select *, ROW_NUMBER() over(partition by Empname order by Empname ) as rownumber from Employe 

Rank():

This function will assign a unique value to each distinct Row, but it leaves a gap between the groups.

Example:

SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe 





Dense Rank():

Dense_Rank() Funcation is similar to Rank with only difference, this will not leave gaps between groups.

Example:

1. SELECT EmpName ,EmpSalary ,DENSE_RANK() over(order by EmpName) as DrankID from Employe  

What is join and How Many Types of Join.

what is join?

Ans: A Sql join statement are used to combine data and rows between two or more tables bases on command field between them.


Mainly Four Types of Joins

1. Inner join
2. Left join
3. right join
4. full join


Inner join: Matching row between two tables.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

What is Self Join:

A self JOIN is a regular join, but the table is joined with itself.

Example:

Well, one classic example is where you wanted to get a list of employees and their immediate managers:

select e.employee as employee, b.employee as boss
from emptable e, emptable b
where e.manager_id = b.empolyee_id
order by 1

It's basically used where there is any relationship between rows stored in the same table.

• employees.
• multi-level marketing.
• machine parts.

Different between Delete and Truncate and Drop

Truncate:

1.Truncate is Data Definition Language command which is used to remove the all Rows from the table.

2.You can not Filter rows while truncate data from the database because it does not allows where clause.


3.Truncate does not return number of rows truncated from the table.


4.Truncate deallocates the memory for that object and other object will use that deallocated space.
5.Truncate operation can not roll backed because it does not operates on individual row.It directly processes all rows from the table.
6.Truncate is faster than delete.
7.You can not use conditions in case of truncate.
8.truncate======it will do 2 actions
1.drop the table from db
2.after that it will recreate the object with metadata
it releases space occupied by the rows, we can use that space for another purpose by using reuse command. (Suggested By Mohan from Readers )

Syntax:
Truncate table <Tablename>;


Delete:

1.Delete is Data Manipulation Language statement which is used to manipulate the data from the table.

2.Delete Statement does not change any property of database.

3.Delete statement is used to remove the rows from the table.you can use filtering criteria or where condition to remove the specific rows from the table.

4.If You can not specify the where condition all rows will be removed from the table.

5.After using delete you need to commit the changes to make it permanent.

6.It deletes the row by row data from the table so Delete is slower than truncate.

7.Every deleted row is locked,thus it requires more number of locks.

8.This operation uses all Delete triggers.

Syntax:

Delete from tablename


Where column name= condition;


Drop:

1.Drop is Data Definition Language Statement.

2.The Drop command removes the table from the database.

3.It removes all the indexes,privilleges,rows and frees the memory space for other objects.

4.You can not drop the table referenced by foreign key constraint.

5.The objects dependent on the table which we are dropping like Views,procedures needs to be explicitly dropped.

6.No DML triggers will be fired.

7.You can not roll back the drop table operation.

8.drop =====it will delete the db object permanently from db like tables, (Suggested by Mohan by Readers)

Syntax:

Drop table tablename;

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+

Function in SQL

1. A function must have a name and a function name can never start with a special character such as @, $, #, and so on.
2. Functions only work with select statements.
3. Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements.
4. Functions compile every time.
5. Functions must return a value or result.
6. Functions only work with input parameters.
7. Try and catch statements are not used in functions.

SQL Server support two types of user defined functions:

1. Table Valued Functions
2. Scalar Valued Functions

Table Valued Functions

In this type of function, we select a table data using a user created function. 

Example:

1. Create function Fun_EmployeeInformation()      
2. returns table       
3. as      
4. return(select * from Employee  ) 

Syntax:

1. create function fun_JoinEmpColumnInfo  
2. (  
3.    @EmpContact nchar(15),  
4.    @EmpEmail nvarchar(50),  
5.    @EmpCity varchar(30)  
6. )  
7. returns nvarchar(100)  
8. as  
9. begin return(select @EmpContact+ ' ' +@EmpEmail + ' ' + @EmpCity)  
10. end  

Stored Procedure:

what is Stored Procedure:

Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which are grouped to perform a specific task.

There are many benefits of using a stored procedure. The main benefit of using a stored procedure is that it increases the performance of the database.

Types of Stored Procedure.

1. User Defined Stored procedure:
 The user defined stored procedures are created by users and stored in the current database


2. System Stored Procedure
The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database

3. Temporary Stored Procedure:

The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection  terminates between client and server

4. Remote stored procedure:

The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission.

5. Extended  Stored procedure:

These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

Benefits of Using the Stored Procedure

1. One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time.
4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5. Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.

what is Difference between @@Identity,@scope Identity,Identity Current

@@Identity:

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

@Scope Identity:

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

============================

INSERT Parent1 DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/ 

@IDENTITY CURRENT:

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

eXAMPLE:

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

@@IDENTITY method is not limited to a specific scope.

SCOPE_IDENTITY method is limited for current scope.

IDENT_CURRENT method is not affected by scope and session, it only depends on a specific table.