Some useful Interview questions

What is materialized view?We can name it materialized view in different ways like Schema Binding view, Structure binding view and materilizaed view. The main use of the view is it acts as a table like hold the schema. For a example normal view is a select query written by combining multiple tables. If we want to improve the performance there is only a way that we can alter the statements in better way. But if consider materialized view, one can create indexes apart from altering a query for better performance. As you understood indexes will give more performance compares with any other option available in sql server.

What is the diff between inner join and union?Inner join retrieves the matched data from 2 or more tables where as union will retrieve distinct data from 2 or more tables. Join is like horizontal result, Union is like vertical result.

What is the diff between master database and resource database? If resource database was corrupted sql server will work or not? How you can repair that?
Master and Resource both are system dbs in sql server. But master is the primary database which will contain all security structures, objects structures and other user database dependent information where as resource will have copy of the users database system tables which helps at the time of upgradation or degradation. The resource purpose is intended in Sql server 2005 is only for Upgradation or degradation of the versions or service packs. If resource is not available, then also Sql server will not work as some dependent services are working based on resource db structured in Sql Server 2005. Eg : Alert Service, Performance related services.

If i need to give permission per column in table how you can give and tell me process?--
Step 1:
Create table NB_Perm (sl int, name varchar(10))

--STep 2 :
Create role NB_role

--Step 3 :
grant select (sl) on nb_perm to NB_role
deny select(sl) on nb_perm to NB_Role

Make your users member of this role.

What is use of built/administrator in sql server?It is a Windows Group defaultly created with installation of SQL Server. The role defaultly assigned is Sysadmin. If we want this group to be there in Sql server we can keep it and create the users under this group. For creating users refer Administrator Tools – Computer Management - Users and Groups.

What is the diff between having clause and where clause?‘Having’ clause can be used when the query consists aggregate functions like count, avg …..
Eg : Select id,count(id) from table having count(id)>5
‘Where’ clause can be used to filter the data in any statement irrespective of conditions.

How you can know who was last using the query?In earlier versions we can find by implementing the trigger. But in Sql server 2008 new concept auditing is available. With help of this we can find out.

What is the diff between physical file and logical file and which one you refer at the time shrink the file?Logical and Physical both are database files where logical file available inside the database structure and physical file available at the destination drive. Logical and physical both are important at the time of backup, restore or any kind of database operation. As you requested I refer the logical file only at the time of shrinking or restoring the db.

What is covering Index and can you give me one example?Covering index is an index which helps to reduce the index size in a database. Like a index will improve the performance and as a disadvantage it occupies some space in your db. Suppose if you want to create index by using more columns then you can refer convering index as best solution. Addition to decreasing the table space it gives good performance also as running the indexes only it requires on the columns. The suggestions from my side is create all int kind of data columns in index list and varchar kind of column in include list.
Eg : Create (cluster/nonclustered) index IndexName on table (main columns) include (other columns)


What is live lock?A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely

Diff between live lock and dead lockDeadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A Live lock also occurs when read transactions monopolize a table or page, forcing a write
transaction to wait indefinitely

Lock escalationLock escalation is the process of escalating a significant number of row level locks to a single table lock. This is trick performed by SQL Server as a way of conserving memory and improving performance. The problem is it works well for smaller systems but can easily throttle performance on larger systems when the activity reaches a certain threshold.

How can rebuild master dbProcess :
1) Shutdown the SQL server and its services.
2) Goto Program Files\Microsoft SQL Server\80\Tools\Binn directory.
3) Open Rebuildm.exe
4) In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
5) Change collation settings if required or leave it default.
6) In the Rebuild Master dialog box, click Rebuild to start the process.
DTS to SSIS migration
We cannot migrate DTS packages from 2000 to 2005 (or) 2008. If it is simple package then we can use it as it is. If it consists vbcode and all it will not convert instead we need to rewritten the package using SSIS. Because SSIS uses .net code.

Types of replication agentsReplication Snapshot Agent:
Replication Log Reader Agent:
Replication Distribution Agent:
Replication Merge Agent:
Replication Queue Reader Agent:

Public/private networkPublic vs. private networks
Computers that are connected to each other create a network. These networks are often configured with "public" Internet Protocol (IP) addresses -- that is, the devices on the network are "visible" to devices outside the network (from the Internet or another network). Networks can also be configured as "private" -- meaning that devices outside the network cannot "see" or communicate directly to them.
Computers on a public network have the advantage (and disadvantage) that they are completely visible to the Internet. As such, they have no boundaries between themselves and the rest of the Internet community. This advantage oftentimes becomes a distinct disadvantage since this visibility can lead to a computer vulnerability exploit -- a.k.a., a "hack" -- if the devices on the public network are not properly secured.

The difference between Master and Resource databases

The difference between Master and Resource database

Mster db is the mother database for all other databases like it consits of other database information, configurations, file locations, security context and etc. whereas Resource db is a database addition to master consists of database schema and stored procedures which requires to run databases in current instance. Resource db mainly introduced for Upgradation or rollback your installations in Sql server 2005.

Some differences:

Master :
1. Master will have data specific to your instance.

2. The database can be found in your sqlserver installation data folder with the name of master.mdf.

3. For any maintenance activity you need to make the db in single user mode.



Resource :
1. Resource will have schema and stored procedures to run your instance.

2. It is introduced in Sql server 2005 for upgradation purpose. In sql server 2000 if you want to upgrade to a new service pack, you would need to run many log scripts that drop and create system scripts. It is too long process and much time consume. In Sql server 2005 if you install service pack or quick fix a copy of resource database will overrite the old database. With help of this resource db you can easily upgrade or rollback your service packs.


3. The database can be found in your sqlserver installation data folder with the name of mssqlsystemresource.mdf.

4. It is a hidden database, you can't findout it in sysdatabases table. By using serverproperty we can view the information about the resource db.