A local temporary table is
created using CREATE TABLE statement with the table name prefixed with single hash sign (#table_name). Local temp tables are only available to the SQL Server session or
connection (means single user) that created the tables. These are automatically
deleted when the session that created the tables has been closed.
A global temporary table is created using CREATE TABLE statement with
the table name prefixed with a double number sign (##table_name). Global temporary tables are visible to all sessions. Global temporary tables are dropped when the session
that created it ends, and all other sessions have stopped referencing it.
Create Global Temporary Table
CREATE TABLE ##gbtemp_Customer
ID bigint primary key,
UNION: Union command is used to
select related information from two tables. It will remove duplicate rows, it
will retrieve unique rows from all tables.
UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.
Triggers are used to execute a batch of SQL code when
insert or update or delete commands are executed on a table. Triggers are
automatically triggered or executed when the data is modified. It can be
executed automatically on insert, delete and update operations.
A Stored Procedure is a collection or a group of SQL
statements. Stored Procedures are a precompiled set of one or more statements
that are stored together in the database. They reduce the network load because
of the precompilation. We can create a Stored Procedure using the "Create
Create store procedure
CREATE PROC Proc_GetUser(
SELECT * FROM UserProfile WHERE USERID=@UserID
Execute store procedure
EXEC Proc_GetUser 1
--(1 IS USERID PASS AS PARAMETER)
Constraints are rules and restrictions applied
on a column or a table such that unwanted data can't be inserted into tables.
This ensures the accuracy and reliability of the data in the database. We can
create constraints on single or multiple columns of a table. Constraints
maintain the data integrity and accuracy in the table.
SQL Server contains the following 6 types of constraints:
1. Not Null Constraint
2. Check Constraint
3. Default Constraint
4. Unique Constraint
5. Primary Constraint
6. Foreign Constraint
Temp table is a temporary table that is
generally created to store session specific data. Once the connection's severed for any
reason, the table's automatically dropped. They're also only visible to the
current user, so multiple users can use the same temporary table name without
1. TRUNCATE is a DDL command
2. TRUNCATE is executed using a table lock and whole table is locked for remove all records.
3. We cannot use Where clause with TRUNCATE.
4. TRUNCATE removes all rows from a table.
5. Minimal logging in transaction log, so it is performance wise faster.
6. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
7. Identify column is reset to its seed value if table contains any identity column.
8. To use Truncate on a table you need at least ALTER permission on the table.
9. Truncate uses the less transaction space than Delete statement.
10. Truncate cannot be used with indexed views.
1. DELETE is a DML command.
2. DELETE is executed using a row lock, each row in the table is locked for deletion.
3. We can use where clause with DELETE to filter & delete specific records.
4. The DELETE command is used to remove rows from a table based on WHERE condition.
5. It maintain the log, so it slower than TRUNCATE.
6. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
7. Identity of column keep DELETE retain the identity.
8. To use Delete you need DELETE permission on the table.
9. Delete uses the more transaction space than Truncate statement
10. Delete can be used with indexed views.