Explore topic-wise InterviewSolutions in .

Types of backups available in SQL Server:

• Complete: This creates a complete stand alone image of the database. This backup is self dependent and can be restored to either the same or a new database on same or other server.
• Differential: This backs up only the modified CONTENTS since the last backup. They do not PROVIDE much FLEXIBILITY.
• Transaction log: This backs up all transaction logs since the previous transaction log backup or the complete transaction log backup if there has not been one in past.
• Files and Filegroups backup: This option is suitable when time constraints are high and one cannot AFFORD to perform a complete database backup. It also needs transaction logs backup to take place to make it worth choosing this option. After restoring file backup, APPLY transaction logs to roll the file contents forward to make it consistent with the database..

Types of backups available in SQL Server:

• Deadlock occurs when TWO user processes/transactions have locks on 2 separate objects and each PROCESS is trying to acquire a lock on the object that has been acquired by the other process. In such a scenario each process is waiting for the other process to RELEASE the lock to acquire a lock on the object itself. When a request for exclusive lock is denied again and again because a series of overlapping shared locks are interfering with each other and to adapt from each other they keep on changing the STATUS, it is known as live lock.
• One can resolve deadlocks by using TRY CATCH blocks. If the CODE inside a TRY statement fails, the CATCH automatically catches the control of the flow letting the transaction rollback and resume execution.

A SPARSE column is ANOTHER tool used to REDUCE the amount of physical storage used in a database. They are the ORDINARY columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the COST of more overhead to retrieve non null values.

A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two COPIES of a single database that typically reside on different computers. At any given time, only one copy of the database is CURRENTLY available to clients which are known as the PRINCIPAL database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves APPLYING the transaction log from EVERY insertion, update, or deletion made on the principal database onto the mirror database.

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

Policy BASED Management in SQL SERVER 2012 Administration ALLOWS you to define and enforce policies for CONFIGURING and managing SQL Server ACROSS the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

Policy Based Management in SQL SERVER 2012 Administration allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server FULLY into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a DATABASE to send a message to another database without waiting for the response, so the APPLICATION will continue to function if the REMOTE database is temporarily unavailable.

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

1. The master database holds information for all databases located on the SQL Server instance. As SQL Server cannot start without a functioning master database, we must administer this database with care and monitor Startup Parameters in Configuration Manager.
2. The MSDB database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
3. The tempdb holds temporary objects such as global and local temporary tables and STORED procedures.
4. The model is essentially a template database USED in the creation of any new user database created in the instance.
5. The resource Database is a read-only database that contains all the system objects that are INCLUDED with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not CONTAIN user data or user metadata.

This can be started on both the nodes as this doesn't have any dependency on SQL service or any resource which is POSSESSED by ACTIVE NODE.

This can be started on both the nodes as this doesn't have any dependency on SQL service or any resource which is possessed by active node.

YES RESOURCES can be MOVED after pausing the node. But we can't move them back till the node is PAUSED.

Yes resources can be moved after pausing the node. But we can't move them back till the node is paused.

SQL SERVICES will be in MANUAL and stopped. Cluster service will be in automatic and started mode on both the NODES.

SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes.

We can make use of SQL Profiler , SQL Server LOG or use xp_readerrorlog EXTENDED Stored PROCEDURE to retrieve the error log INFORMATION.

We can make use of SQL Profiler , SQL Server Log or use xp_readerrorlog extended Stored Procedure to retrieve the error log information.

Database Mirroring is to be configured with TCP Protocol and ensure that data over each endpoint is ENCRYPTED. Better to MAKE use of TDE for more SECURITY.

Database Mirroring is to be configured with TCP Protocol and ensure that data over each endpoint is encrypted. Better to make use of TDE for more security.

The error comes when you are TRYING to restore the DB which ALREADY exists. USE WITH REPLACE OPTION to restore the DB with a different database name.

The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different database name.

If the Restore JOBS is scheduled / delayed for longer intervals this option can be used. As uses needs to be disconnected every time Restore JOB runs(to ENSURE timely shipping of Transaction Logs) its recommended not to use this option for regular, less interval Log Shipping configurations. Using this option gives us advantage of Load BALANCING for READONLY connections and Reporting purposes at the cost of timely disconnections to HELP success of Restore Jobs.

If the Restore Jobs is scheduled / delayed for longer intervals this option can be used. As uses needs to be disconnected every time Restore Job runs(to ensure timely shipping of Transaction Logs) its recommended not to use this option for regular, less interval Log Shipping configurations. Using this option gives us advantage of Load Balancing for READONLY connections and Reporting purposes at the cost of timely disconnections to help success of Restore Jobs.

Ensure that SQL BROWSER is running and TCP/IP is ENABLED. Enforce TDE (Transparent DATA Encryption) so that every data bit is encrypted.

Ensure that SQL Browser is running and TCP/IP is enabled. Enforce TDE (Transparent Data Encryption) so that every data bit is encrypted.

Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this Replication TOPOLOGY each node is Publisher, Distributor and Subscriber. This INCREASES availability of the database system and Failure of any node does not impact the health of Replication PROCESS. This topology ALSO offers automatic conflict detection and correction. Hence, recommended in Realtime.

Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this Replication topology each node is Publisher, Distributor and Subscriber. This increases availability of the database system and Failure of any node does not impact the health of Replication process. This topology also offers automatic conflict detection and correction. Hence, recommended in Realtime.

Use TRUNCATE_ONLY OPTION for SQL Server 2005 systems while performing Backup. This option is not AVAILABLE in SQL Sever 2008 and R2. INSTEAD, we use ON_TRUNCATE option available in the Backup statement.

Use TRUNCATE_ONLY option for SQL Server 2005 systems while performing Backup. This option is not available in SQL Sever 2008 and R2. Instead, we use ON_TRUNCATE option available in the Backup statement.

There are 21 types of BACKUPS in SQL Server. A non-base FULL BACKUP is called COPYONLY Data Backup. This is used to service 'on demand' Backup requests from END users. Using this type of Backups, we can service the 'on demand' backup requests from end users without disturbing the Backup sequence of Jobs / Maintenance Plans or other DIFFERENTIAL Backups. This gives us easy restore PATHS as well in case of DR process.

There are 21 types of Backups in SQL Server. A non-base FULL BACKUP is called COPYONLY Data Backup. This is used to service 'on demand' Backup requests from end users. Using this type of Backups, we can service the 'on demand' backup requests from end users without disturbing the Backup sequence of Jobs / Maintenance Plans or other differential Backups. This gives us easy restore paths as well in case of DR process.

SQL profiler is a tool to monitor performance of various STORED procedures. It is used to debug the QUERIES and procedures. Based on performance, it identifies the SLOW executing queries. Capture any problems by capturing the events on PRODUCTION environment so that they can be solved.

SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.

BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow you to truncate the log now otherwise whole purpose of a DB is defeated. You have to make SURE whether you need log or not. If you do not need log then have the recovery model simple instead of FULL. If you do not want the log to be accumulated in some particular bulk logging then CHANGE the recovery model BULK LOGGED for that duration and take one TLog Backup just before and after this change. I shall discuss this later in my later BLOG. BACKUP LOG COMMAND backs up the t-log and frees the space in the log file.

BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow you to truncate the log now otherwise whole purpose of a DB is defeated. You have to make sure whether you need log or not. If you do not need log then have the recovery model simple instead of full. If you do not want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one TLog Backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.

SELECT * from sys.dm_db_missing_index_details

Select * from sys.dm_db_missing_index_details

DYNAMIC Management Views (DMV) return server state information that can be used to MONITOR the health of a server INSTANCE, diagnose PROBLEMS, and tune performance.

Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

SQL Server PROFILER is a graphical user interface to SQL Trace for monitoring an instance of the DATABASE Engine or ANALYSIS Services. You can capture and save data about each EVENT to a file or table to ANALYZE later.

SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

A Table/view SCAN OCCURS when no USEFUL indexes exist. A TABLE SCAN reads all data, ROW by row, to find the MATCH.

A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.

CREATE CLUSTERED INDEX index_name on empmst(CARD)

create clustered index index_name on empmst(card)

An unique KEY CANT not be REFERENCED as FOREIGN key. And it may allow on NULL.

An unique key cant not be referenced as foreign key. And it may allow on null.

SELECT @@VERSION Or select serverproperty (‘productlevel’)

Select @@version Or select serverproperty (‘productlevel’)

SELECT @@VERSION

Select @@version

LEFT Outer join Retrieves the all RECORDS from LEFT table and matching from the RIGHT table, and null values where is no MATCH. Right Outer Join just OPPOSITE.

Left Outer join Retrieves the all records from LEFT table and matching from the RIGHT table, and null values where is no match. Right Outer Join just opposite.

Inner JOIN, OUTER (LEFT Outer & Right Outer) Joins and CROSS join.

Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

It’s stored into ROOT FOLDER SQL server, LOG folder.

It’s stored into root folder SQL server, LOG folder.

Definition of SYS objects, which LOGICALLY SHOWS into all DATABASE and DMVS.

Definition of sys objects, which logically shows into all database and DMVs.

NO

NO

SCHEDULED JOBS, Backup/Restore and DTA INFORMATION.

Scheduled jobs, Backup/Restore and DTA information.

SERVER’s CONFIGURATIONS and LOGINS.

Server’s configurations and logins.

TEMPLATES of new DATABASE OBJECTS, like tables and COLUMN.

Templates of new database objects, like tables and column.

Answer :ROW versions, CURSOR, TEMP objects.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

This is a very generic question. I would like to describe my generic DATABASE testing method as well as stored procedure testing methods.

Testing Databases:

Table Column data type and data value validation.
Index implementation and performance improvement.
Constraints and Rules should be validated for data integrity.
Application field length and type should match the corresponding database field.
Database objects like stored procedures, triggers, functions should be tested USING different kinds of input values and checking the expected output variables.

Testing Stored Procedures:

Understand the requirements in terms of Business Logic.
CHECK if the code follows all the coding standards.
COMPARE the fields’ requirements of application to the fields RETRIEVED by a stored procedure. They must match.
Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
Pass invalid input parameters and see if a stored procedure has good error handling.

This is a very generic question. I would like to describe my generic database testing method as well as stored procedure testing methods.

Testing Databases:

Table Column data type and data value validation.
Index implementation and performance improvement.
Constraints and Rules should be validated for data integrity.
Application field length and type should match the corresponding database field.
Database objects like stored procedures, triggers, functions should be tested using different kinds of input values and checking the expected output variables.

Testing Stored Procedures:

Understand the requirements in terms of Business Logic.
Check if the code follows all the coding standards.
Compare the fields’ requirements of application to the fields retrieved by a stored procedure. They must match.
Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
Pass invalid input parameters and see if a stored procedure has good error handling.

Plan Freezing is a new feature I never thought of. I FIND it very interesting! It is included in SQL Server 2008 CTP5. SQL Server 2008 ENABLES greater query performance stability and PREDICTABILITY by providing new functionality to lock down query plans. This empowers organizations to promote STABLE query plans across hardware server replacements, server UPGRADES, and production deployments.

Plan Freezing is a new feature I never thought of. I find it very interesting! It is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans. This empowers organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Fill factor specifies a PERCENTAGE that indicates how full the Database ENGINE should make the leaf LEVEL of each INDEX PAGE during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I prefer to keep my servers default fill factor as 90.

Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I prefer to keep my servers default fill factor as 90.

Well, your question is very difficult to answer without looking at the code, application and PHYSICAL server. In such situations, there are a few things that MUST be paid attention to right away.

RESTART Server
Upgrade HARDWARE

Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over other operating system processes in SQL Server settings.

Update STATISTICS on the database tables.

Well, your question is very difficult to answer without looking at the code, application and physical server. In such situations, there are a few things that must be paid attention to right away.

Restart Server
Upgrade Hardware

Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over other operating system processes in SQL Server settings.

Update statistics on the database tables.

BI stands for Business Intelligence. Microsoft started to promote the acronym BI SINCE the launch of SQL Server 2005. However, it has been in use for a LONG time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting accurate and TIMELY key enterprise data to CXO, IT Managers, Business Consultants, and distributed teams of a company, to provide them with up-to-date information to drive intelligent decisions for business success, which ultimately leads to enhanced revenue, reduced risk, decreased cost, and BETTER operational control for business agility and competitiveness. An effective BI empowers end users to use data to understand the cause that led to a particular business result, to decide on the course of action based on past data, and to accurately FORECAST future results.

BI stands for Business Intelligence. Microsoft started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for a long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting accurate and timely key enterprise data to CXO, IT Managers, Business Consultants, and distributed teams of a company, to provide them with up-to-date information to drive intelligent decisions for business success, which ultimately leads to enhanced revenue, reduced risk, decreased cost, and better operational control for business agility and competitiveness. An effective BI empowers end users to use data to understand the cause that led to a particular business result, to decide on the course of action based on past data, and to accurately forecast future results.

Let us first see the fully QUALIFIED query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the USER who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by USING this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no NEGATIVE effect on the database itself.

Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.

This question can be answered by querying SYSTEM views.

For SQL Server 2005 run the FOLLOWING code:

SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.columns
WHERE name = 'YourColumnName'

The previous QUERY will return all the tables that use the column name specified in the WHERE condition. This is a very small but a very handy script.

This question can be answered by querying system views.

For SQL Server 2005 run the following code:

SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.columns
WHERE name = 'YourColumnName'

The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but a very handy script.

I rate the following five tasks as the key responsibilities of a DBA.

1. Securing database from physical and logical integrity damage.
2. Restoring database from backup as a part of disaster management plan.
3. Optimizing QUERIES performance by appropriate indexing and optimizing joins, where conditions, select clause etc.
4. Designing new schema, SUPPORT legacy schema, and legacy database systems.
5. Helping developers improve their SQL-related CODE writing skill.

I rate the following five tasks as the key responsibilities of a DBA.

You cannot restore the database without having a FULL database BACKUP. However, if you have the copy of all the DATA files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is POSSIBLE to attach the database using sp_attach_db.

You cannot restore the database without having a full database backup. However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is possible to attach the database using sp_attach_db.

The primary job of DBAs is to secure the data. They should be able to keep it safe as WELL as reproduce it efficiently, whenever required. So as PER my view, a DATABASE Administrator who can fulfill the REQUIREMENTS of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ASK them questions about backup strategies and efficient restoring methodologies.

The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

There are MANY ways but I prefer following method. Take a scenario when you WANT to find the error log when the DB was put in a single USER mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’

There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’

Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the METHOD to have your DB in a single user mode.

Use master
GO sp_dboption dbname, single, true

Following is the error which you get when you RUN the DBCC CHECKDB with repair option WO having the DB in single user mode. The same is true for DBCC CHECKDB ALSO

Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.

Use master
go sp_dboption dbname, single, true

Following is the error which you get when you run the DBCC CHECKDB with repair option wo having the DB in single user mode. The same is true for DBCC CHECKDB also