Ans :

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.


 

Ans :

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.

 

Ans :

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.

 

Ans :

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,
NAME VARCHAR(50),
CONTACT VARCHAR(20)
)

 

Ans :

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.

 

Ans :

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.

 

Ans :

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 proc" statement.

Create store procedure

CREATE PROC Proc_GetUser(
@UserID BIGINT
)
AS
BEGIN
SELECT * FROM UserProfile WHERE USERID=@UserID
END

Execute store procedure

EXEC Proc_GetUser 1 
--(1 IS USERID PASS AS PARAMETER)

 

Ans :

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

 

Ans :

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

 

Ans :

TRUNCATE

 

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.

 

DELETE

 

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.

 
Page 1 of 0