A local temporary table is created using CREATE TABLE statement with the table name prefixed with single hash sign (#table_name).
Constraints are rules and restrictions applied on a column or a table such that unwanted data can't be inserted into tables.
NonClustered Index does not alter the stored data in physical order of the table and maintains logical order of data. Each table can have more than one non-clustered indexes.
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed on a table.
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.
The clustered index is used to reorder the stored data in physical order of the table and search based on the key values. Each table can have only one clustered index.
A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).
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.
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.
1. TRUNCATE is a DDL command
2. TRUNCATE is executed using a table lock and whole table is locked for remove all records.
1. DELETE is a DML command.
2. DELETE is executed using a row lock, each row in the table is locked for deletion.