SQL server basics/job interview questions Part 1

SQL server basics/job interview questions Part 1


1.  What is difference between clustered and nonclustered index




Table can have only one clustered index and records are physically stored in clustered index.So we can also say that clustered index is also a table  and the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A table can have multiple nonclustered indexes and A nonclustered index contains the index key values and row locators that point to the storage location of the table data. If  table has clustered index than nonclustered index point to clustered index else it will point to table.A table without clustered index is called as a heap.Also if we are dropping clustered index first we should drop all nonclustered index and recreate it after dropping clustered index. Since they all will be redesigned after dropping a clustered index , it better to drop and recreate them later . While creating index we should first create clustered index then create nonclustered index

2. What are type of joins
1. inner join
2.left join
3.right join
4.full join
5.cross join


3.Difference between delete and truncate

Delete action is logged with data while truncate is logged as deallocation of data page
So truncate is faster
You can filter record for deleting, while truncate will remove all rows
truncate cant fir trigger
you cant truncate table which is part of publication or key constraints

4.What is LOCK escalation?

Lock escalation is the process of converting many fine-grain locks(row , page lock) into fewer coarse-grain locks(table lock), reducing system overhead while increasing the probability of concurrency contention.
Managing locks takes memory and resource , so when there is need of coarse grain lock(table level) or number of lower level locks increased too much lock is escalated to higher level and all lower level locks are released to better management of locks



5.what is procedure to roll over error log
use proc  sp_cycle_errorlog  to roll over error log

6.What is procedure to read error log
xp_readerrorlog


7 type of temp tables
local temp table
they are accessable only in the session where they are created and dropped when session ends
they starts with #

global temp table
they are accessable in all session instead of where they are created and dropped when session ends
they starts with ##

8.What is ACID propery
A Atomicity
 A transaction must be an atomic , all operation in the transcation should be committed or none of them

C Consistency
 Data should be consistent after completion of transaction.In a relational database, all rules must be applied to the transactions modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

I Isolation

Transaction should isolated and they cant affect each other.
 A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.


Durability

After a transaction has completed, its effects are permanently in
place in the system. The modifications persist even in the event of a system
failure.


7.Give name of systemdatabases

1.Master
2.MSDB
3.TempDB
4.Distribution
5.Model

 Next Page