Sql server interview questions and answers-Part-4

QUESTION - What are the different types of Locks? 
ANSWER - Types of locks: 
Shared Lock: Shared locks are used for operations that read data, such as a SELECT statement. During Shared locks used, concurrent transactions can read a resource, but cannot modify the data.

Update Lock: Update locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update locks are used to prevent a deadlock.

Exclusive Lock: Exclusive locks are used for data modification operations, such as UPDATE, INSERT, or DELETE. Other transactions cannot read or modify data locked with an Exclusive lock.

QUESTION - What is Extent and types of Extent ?

ANSWER - An extent is 8 continuous pages to hold server object.

Uniform extents - This type of extent contains data from one table.

Mixed extents - This type of extent contains data from two to eight different tables.

QUESTION - What is the use of DBCC commands? 
ANSWER - DBCC is database consistency checker. DBCC commands are used to check the consistency of the databases.
DBCC CHECKDB - Ensures that tables and the indexes are correctly linked in the database.
DBCC CHECKALLOC - Ensures all pages are correctly allocated in the database.
DBCC SQLPERF - Provides report on current usage of transaction log in percentage.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

QUESTION - Define COLLATION?

ANSWER - Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

QUESTION - Where is users names and passwords stored in SQL Server?

ANSWER - They are stored in master db in the sysxlogins table.

QUESTION - What is BCP?

ANSWER - It is utility used to copy huge data from tables and views without copy schema of the server object.

QUESTION - How can we move data along with schema of the server object?

ANSWER - We can copy data along schema using DTS package.

QUESTION - Define sub-query?

ANSWER - Sub-query is a query within a Query.

Example of sub-query:

Select CustId, Custname From Customer Where Cust_Id IN (Select Doct_Id from Doctor)

QUESTION - sp_grantlogin, sp_denylogin and sp_revokelogin

ANSWER - All these three are the system stored procedure used to manage windows account authentication. sp_grantlogin allows to add and grant access to windows account.
sp_denylogin denies user to access server without deleting windows account from SQL server. sp_revokelogin delete windows account from SQL server.

QUESTION - Write SQL query to retrieve all tables of the database.

ANSWER - Select name from sysObjects where xtype=’u’

QUESTION - Define Local temporary table and global temporary table.

ANSWER - Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name).
Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends.

QUESTION - When do we use the UPDATE_STATISTICS command?
ANSWER - UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.

QUESTION - Difference between clustered and non-clustered index. 
ANSWER - Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data. We can have only one clustered index in a table but we can have many non-clustered index in a table. Physical data in the table is sorted in the order of clustered index while not with the case of non-clustered data.