Interview Question and Answers - 1

1. Difference between TRUNCATE and DELETE

                - Truncate is DDL command so can't be rolled back while Delete is a DML                               command so it can.

                - Truncate keeps the lock on table while Delete keeps the lock on each row.

                - Truncate resets the counter of the Identity column while Delete doesn't do so.

                - Truncate does not fire trigger but Delete fires trigger.

                -Truncate removes all rows by de allocating data pages allocated to the table                      while Delete removes rows one by one.

2.  What are the advantages a stored procedure?

                Stored Procedures are pre compiled and stored in the database. This enables the database to    execute the queries much faster. Since many queries can be included in a stored procedure,  round trip time to execute multiple queries from source code to database and back is   avoided.

3.  What are the advantages and disadvantages of views in a database?

   Advantages:

·             Views don't store data in a physical location.

·             The view can be used to hide some of the columns from the table.

·             Views can provide Access Restriction, since data insertion, update and deletion is not    possible with the view.

Disadvantages:

·             When a table is dropped, associated view become irrelevant.

·             Since the view is created when a query requesting data from view is triggered, its a bit slow.

·         When views are created for large tables, it occupies more memory.

4. What  is the ACID property?

·             ACID (Atomicity Consistency Isolation Durability

·             Atomicity is an all-or-none rule for database modifications.

·             Consistency guarantees that a transaction never leaves your database in a half-finished state.

·             Isolation keeps transactions separated from each other until they are finished.

·             Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination and committed transactions will not be lost.

5. What are the different types of triggers?

  There are three types of triggers

·         DML trigger

              There are two kinds of DML triggers

                a. Instead of Trigger

                     Instead of Triggers are fired in place of the triggering action such as an                             insert, update, or delete.

                b. After Trigger

                    After triggers execute following the triggering action, such as an insert,                            update, or delete.

·         DDL trigger

                  This type of trigger is fired against DDL statements like Drop Table, Create                        Table, or Alter Table. DDL Triggers are always after Triggers.

·         Logon trigger

                  This type of trigger is fired against a LOGON event before a user session is                         established to the SQL Server.

How to Find the number of rows,allocated size, rows size, index size and free space in a table

exec sp_spaceused 'AddressType'

name                  rows   reserved     data        index_size unused

AddressType         6        48 KB         8 KB       40 KB 0 KB

It returns number of rows, reserved space for the table, data size, index size and unused space left.

We can use above to find for one table.

When we tried to find all the tables in a database then we can achieve by writing t-SQL script

 --Declare the table variable to store the table names

DECLARE @Tables TABLE (SchemaTable varchar(100));

DECLARE @TableName varchar(100); --To store each table name

-- Insert table names into the table variable

INSERT @Tables (SchemaTable)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME

FROM INFORMATION_SCHEMA.tables

WHERE TABLE_TYPE = 'BASE TABLE'

--Loop through the table names

WHILE (SELECT COUNT(*) FROM @Tables) > 0

BEGIN

SELECT TOP 1 @TableName = SchemaTable

FROM @Tables

ORDER BY SchemaTable;

EXEC sp_spaceused @TableName;

DELETE @Tables

WHERE SchemaTable = @TableName;

END;

This will give the list of all the tables.