Nov 8, 2015

SQL Server Interview Questions (Part-2)

What is the difference between a Local and a Global temporary table?
Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is #temp table and @table variable in SQL Server?
Both temp table and table variable are used to store data temporarily for a particular session.
Both have some similarities and differences.
Read More in detail...

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
It is called Heap. A heap is a table that does not have any index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is difference between Delete and Truncate commands?
Delete is a DML CommandTruncate is a DDL Command
"Where" clause can be used to remove partial rowsCan't use "Where" clause
Slow as maintain transaction logsFaster as transnational log is not maintained
Rollback is possible from log filesRollback is not possible from log files.
Delete does not reset identity of the table.Truncate resets the identity of the table
Delete activates Trigger because the operations are loggedDoes not activate trigger because operations are not logged.

What is Trigger?
Triggers are special types of stored procedures which are automatically executes when an event occur in database.
Read more in Detail...

How many types of triggers are there?
There are four types of triggers.
  1. Insert
  2. Delete
  3. Update
  4. Instead of
Read more in Detail...

What are Magic Tables?
Whenever DML operations insert/update/delete are performed "INSERTED" and "DELETED" are created automatically. These tables are called Magic Tables.
Read more in detail...

    Choose :
  • OR
  • To comment
No comments:
Write Comments