SQL Server DBA Interview Questions and Answers

🔰 MS SQL Server DBA 🔰

Last Updated: 2023-09-23

What types of replication are supported in SQL server?

Replication is the process by which data is stored in more than one place in a database. This is a general question that tests your knowledge of data processing in an SQL system.

Example: “There are three types of replication in SQL server, namely snapshot, merge and transaction.

*Snapshot replication: This kind of replication involves a snapshot of the publisher’s database that is then distributed to subscribers. Snapshot replication is rarely used, as it’s time and resource intensive, but would, for instance, be applicable when a subscriber’s database needs to be completely overwritten.*

*Merge replication: This replication is well-suited when both the publisher and subscriber need to make changes to their databases and these changes have to be merged.*

*Transaction replication: This kind of replication takes place when changes happen frequently. The replication process constantly monitors the publisher for changes and then updates subscribers accordingly.”*

How do you troubleshoot a performance issue on your SQL Server?

As a database administrator, troubleshooting database issues should be your specialty and is an essential function of the job. Answering this question allows you to show, by example, that you understand how to handle performance issues that are a part of the DBA role.

In the example below, you’ll see the STAR method of answering interview questions is used in part of the answer. This is a method that asks you to speak from your experience by considering a challenging situation, your role in the situation, what tasks were performed to solve it and what overall outcome was achieved.

Example: “The most common SQL server issues relate to CPU, memory, networking, and I/O bottlenecks. To get to the bottom of the problem, there are a few troubleshooting tools you can use.

*Performance Monitor: This is a Windows system monitoring tool that displays metrics with regard to key system components such as CPU, memory, disks, and networking.*

*Execution Plan: This is a useful SQL server tool, as query execution plans can be used to establish processes that need to be fine-tuned for a query to run faster.*

*SQL Profiler: This tool helps you trace and troubleshoot problems in SQL server, by, for instance, tracing and evaluating database queries.*

*DMV and DMF Queries: Dynamic Management Views and Functions are system views and functions that allow administrators to monitor the performance of the SQL server instance, in this way diagnosing issues.*

*SP_WHO2: This is a defined stored procedure that will give you information on current users, sessions and processes in a SQL server instance.*

In my experience, it’s best to have performance monitoring tools in place in advance of any problems that might require monitoring. For example, I was tasked with troubleshooting issues as they arise. To meet the challenge, I implemented monitoring software that would escalate issues prioritized by severity. With the level of visibility provided by monitoring tools, I was able to observe the system, even when things were running smoothly. This helped me be better prepared for problems and how to handle them.”

What is blocking and what’s the best way to deal with it?

To deal with reads and writes from multiple inputs in a database, either a read lock or an exclusive lock has to be applied to a piece of data. To prevent data changes while a person is reading it, a read lock is applied on that data and, if data needs to be edited, an exclusive lock is used to prevent people from reading the data while it's being changed.

These two important features of a database result in the intentional blocking of data. Blocking is important for administrators to understand because it’s a function they may be called upon to use or troubleshoot. Answering this question also gives you the chance to explain a database issue in simple, clear terms that even people without the same level of technical expertise can understand.

Example: “Blocking occurs when one connection is holding a lock on a resource while a second connection wants to read or write to it. An even more complex situation occurs when two connections each hold a lock but want to access each other’s resource, which is referred to as deadlocking.

Two effective ways of locating and dealing with block issues are:

*The SP_WHO2: This stored procedure will provide you with a list of queries, and any process that is locked will display the SPID of the process that is causing the block.*

*The DBCC set of commands, and more specifically DBCCINPUTBUFFER, can also be used to trace the “bad query” that is blocking other processes.*

*SQL Server Management Activity Monitor: This tool can also be used to view processes to find out which ones are in a blocked state.”*

What is DBCC?

DBCC is a hub of essential functions for any DBA. To appropriately answer this question, rather than recite the meaning of the acronym, instead, offer both definition and functional value to your answer.

Example: “DBCC is an acronym for Database Console Commands, and they are grouped in four categories:

*Maintenance commands: These commands allow a DBA to do maintenance activities on a database, such as clean caches, shrink databases, and maintain database space. Examples of maintenance DBCC commands include DBCCCLEANTABL, DBCCINDEXDEFRAG and DBCCSHRINKFILE.*

*Information commands: Provides information regarding the database, such as transaction logs, space usage, and system information. Information commands include DBCCDBINFO, DBCC LOG and DBCCDBTABLE.*

*Validation commands: These commands are used to check the consistency of the database. Commands include DBCCCHECKDB, DBCCCHECKCATALOG and DBCCCHECKCONSTRAINTS.*

*Miscellaneous commands: These commands do not fit into the other three categories. Examples are DBCC HELP, DBCCOUTPUTBUFFER and DBCC SHOW_STATISTICS.”*

What kind of recovery models are there in SQL server?

To set up a functional backup plan, you need to decide on what recovery model you need to set for each database. This recovery model tells SQL what data should be kept in the transaction log and for what amount of time. The type of backups you will perform will also depend on the recovery model you have chosen, and the type of restores that you can do.

For a DBA, recovery is an important function because sometimes data gets lost, accidental changes are made to it or it gets deleted. To answer this question, be straightforward and detailed with your response.

Example: “There are three types of recovery models:

*Full: This is the most complete recovery model as all database operations are fully logged to the transaction file, which allows you to recover data to any specific point in time. You can do all kinds of backups with this recovery model, including full, differential, partial and point-in-time recovery. With this kind of model, however, you need to ensure that you back up the transaction log periodically, otherwise, it will just keep growing.*

*Bulked logged: This recovery model is the same as the full recovery model, except that certain bulk data operations, such as BULK INSERT, SELECT INTO and CREATE INDEX, are not fully logged, with the effect that they don’t take up so much space. With this recovery model, you can still do a point-in-time restore, as long as your last transaction log backup didn’t include a bulk operation. You can do all of the backups with this recovery model too, and should also ensure that you prevent constant expansion of the log file by scheduling regular backups.*

*Simple: This simple recovery model doesn’t keep log files permanently, but overwrites transactions once they have been completed. Since the data is overwritten, you will not be able to do a point-in-time recovery. Your latest restore point will be the latest full or differential backup that was completed. With this recovery model, however, your transaction log will not keep growing as is the case with the previous two models.”*

What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Even parameters can be manipulated by the skilled and experienced attackers.

Which TCP/IP port does SQL Server run on?

Default port 1433

What is the difference between clustered and non-clustered index?

A clustered index is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.

A non-clustered index is an index that does not re-arrange the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.

List the different index configurations possible for a table?

A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes

What is the recovery model? List the types of recovery models available in SQL Server?

The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model. It also tells SQL server which backup is possible in a particular selected recovery model.

There are three types of recovery models:

Full
Simple
Bulk-Logged

What are the different backups available in SQL Server?

Different possible backups are:

Full backup
Differential Backup
Transactional Log Backup
Copy Only Backup
File and Filegroup backup

What is a Full Backup?

A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of the transaction log so that it can be recovered.

What is OLTP?

OLTP means Online Transaction Processing which follows rules of data normalization to ensure data integrity. Using these rules, complex information is broken down into a most simple structure.

What is RDBMS?

RDBMS or Relational Database Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS can recombine the data items from different files, providing powerful tools for data usage.

What are the properties of the Relational tables?

Relational tables have six properties:

Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.

What’s the difference between a primary key and a unique key?

The differences between the primary key and a unique key are:

The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.

When is the UPDATE_STATISTICS command used?

As the name implies UPDATE_STATISTICS command updates the statistics used by the index to make the search easier.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

The differences between HAVING CLAUSE and WHERE CLAUSE is:

Both specify a search condition but the HAVING clause is used only with the SELECT statement and typically used with GROUP BY clause.
If the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE clause only.

What is Mirroring?

Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

What are the advantages of the Mirroring?

Advantages of Mirroring are:

It is more robust and efficient than Log shipping.
It has an automatic failover mechanism.
The secondary server is synced with the primary in near real-time.

What is Log Shipping?

Log shipping is nothing but the automation of backup and restores the database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.

What are the advantages of Log shipping?

Advantages of Log Shipping includes:

Easy to set up.
The secondary database can be used as a read-only purpose.
Multiple secondary standby servers are possible Low maintenance.

Can we take the full database backup in Log shipping?

Yes, we can take the full database backup. It won’t affect the log shipping.

What is an execution plan?

An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

Query Analyzer has an option, called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on, it will display a query execution plan in a separate window when the query is run again.

What is the Stored Procedure?

A stored procedure is a set of SQL queries that can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

List the advantages of using Stored Procedures?

Advantages of using Stored procedures are:

Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
They can be reused.
It can encapsulate logic. You can change the stored procedure code without affecting clients.
They provide better security for your data.

What is identity in SQL?

An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.

What are the common performance issues in SQL Server?

Following are the common performance issues:

Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation

List the various tools available for performance tuning?

Various tools available for performance tuning are:

Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor

What is a performance monitor?

Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.

Some useful counters are – Disks, Memory, Processors, Network, etc.

What are 3 ways to get a count of the number of records in a table?

						
SELECT * FROM table_Name;
SELECT COUNT(*) FROM table_Name;
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid&amp;lt; 2;

Can we rename a column in the output of the SQL query?

Yes, by using the following syntax we can do this.

						
SELECT column_name AS new_name FROM table_name;

What is the difference between a Local and a Global temporary table?

If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

What is the SQL Profiler?

SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

What do you mean by authentication modes in SQL Server?

There are two authentication modes in SQL Server.

Windows mode
Mixed Mode – SQL and Windows.

How can we check the SQL Server version?

By running the following command:

						
SELECT @@Version

Is it possible to call a stored procedure within a stored procedure?

Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

What is the SQL Server Agent?

SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

What is the PRIMARY KEY?

The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

What is a UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

What is FOREIGN KEY

When a one table’s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

What is a CHECK Constraint?

A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

What are a Scheduled Jobs?

The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which command executes and the best time to run the job to avoid the load on the system.

What is a heap?

A heap is a table that does not contain any clustered index or non-clustered index.

What is BCP?

BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures the 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 Normalization?

The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more tables and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

List the different normalization forms?

Different normalization forms are:

1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to the description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them into distinct tables.
4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF (Isolate Semantically Related Multiple Relationships): There may be practical constraints on information that justifies separating logically related many-to-many relationships.
ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.

What is De-normalization?

De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling to speed up database access.

What is a Trigger and types of a trigger?

The trigger allows us to execute a batch of SQL code when table event occurs (INSERT, UPDATE or DELETE command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.

3 types of triggers that are available in the SQL Server are as follows:

DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development of database environments.
Logon Triggers: These are very special triggers that fire in case of the logon event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

What is the Subquery?

A Subquery is a subset of SELECT statements, whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

Single-row sub-query: The subquery returns only one row
Multiple-row sub-query: The subquery returns multiple rows
Multiple column sub-query: The subquery returns multiple columns

What is a Linked Server?

Linked Server is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

What is View?

A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Where SQL server usernames and passwords are stored in a SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

What are the properties of a transaction?

Generally, these properties are referred to as ACID properties.

They are:

Atomicity
Consistency
Isolation
Durability

Define UNION, UNION ALL, MINUS, INTERSECT?

UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.

What is SQL Server used for?

SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Which language is supported by SQL Server?

SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Which is the latest version of SQL Server and when it is released?

SQL Server 2019 is the latest version of SQL Server that is available in the market and Microsoft launched this on November 4th, 2019 with the support of the Linux O/S.

What are the various editions of SQL Server 2019 that are available in the market?

SQL Server 2019 is available in 5 editions. These are as follows:

Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

What are functions in the SQL Server?

Functions are the sequence of the statements which accept inputs, process the inputs to perform some specific task and then provide the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

What is a User-Defined function in the SQL Server and what is its advantage?

User-Defined Function is a function that can be written as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the requirement.

This returns Scalar value or a table.

Explain the creation and execution of a user-defined function in the SQL Server?

A User-Defined function can be created in the following way:

						
CREATE Function fun1(@num int) 
returns table
as
return SELECT * from employee WHERE empid=@num;
This function can be executed as follows:

SELECT * from fun1(12);

So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.

What are the Pre-Defined functions in the SQL Server?

These are built-in functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

Why are Views required in the SQL Server or any other database?

Views are very beneficial because of the following reasons:

Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
Views provide a mechanism to control access to particular rows and columns.
These help in aggregating the data to improve the performance of the database.

What is TCL in SQL Server?

TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Which TCL Commands are available on the SQL Server?

There are 3 TCL Commands in the SQL Server. These are as follows:

Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

What are the 2 types of classifications of constraints in the SQL Server?

Constraints are classified into the following 2 types in the SQL Server:

Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. The definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defined after the creation of a table is complete. Alter command is used to apply the table type constraint.

How is table type constraint applied to a table?

Table Type Constraint is applied in the following way:

						
Alter Table Name of the Constraint
Alter Table Constraint_1

What are the different types of Columns Types Constraints in the SQL Server?

SQL Server provides 6 types of Constraints. These are as follows:

Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Check Constraint: This puts a constraint by checking some particular condition before inserting data in the table.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Unique Constraint: This puts a constraint that each row of a particular column must have a unique value. More than one unique constraint can be applied to a single table.
Primary Key Constraint: This puts a constraint to have a primary key in the table to identify each row of a table uniquely. This cannot be null or duplicate data.
Foreign Key Constraint: This puts a constraint that the foreign key should be there. A Primary key in one table is the foreign key of another table. Foreign Key is used to create a relation between 2 or more tables.

What command is used to delete a table from the database in the SQL Server and how?

DELETE Command is used to delete any table from the database in the SQL Server.

						
Syntax: DELETE Name of the table
Example: If the name of a table is “employee” then DELETE command to delete this table can be written as
DELETE employee;

Why is replication required on the SQL Server?

Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of reading and to provide an option to its users to select among various servers to perform the read/write operations.

What command is used to create a database in the SQL Server and how?

CREATEDATABASE Command is used to create any database in the SQL Server.

						
Syntax: CREATEDATABASE Name of the Database
Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.

What function does a database engine serve in the SQL Server?

Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

What are the advantages of having an index on the SQL Server?

The index has the following advantages:

Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way that helps in minimizing data comparisons.
This improves the performance of the retrieval of the data from the database.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

What are the steps you will take to improve the performance of a poor-performing query?

This is a very open-ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures, and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

						
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS IO ON
SQL Server Profiler
Windows NT /2000 Performance monitor
Graphical execution plan in Query Analyzer.

You are being you being assigned a task to move 5 million rows from one server to another using T-SQL with a linked server. What will you consider to avoid transaction log fill up at destination server?

We will prefer to use SET ROWCOUNT and a while loop to commit data in batches.

What is the optimal Disk configuration for a database server and what RAID configurations would you use if budget is not a constraint?

RAID 1 for the OS / Applications

RAID 1 for the page file

RAID 10 for the Data file (or RAID 5 for few writes)

RAID 1 (or 10) for the transaction log

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock 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 livelock 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 livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. The query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

If there is a significant change in the key values in the index

If a large amount of data in an indexed column has been added, changed, or removed (that is if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated

The database is upgraded from a previous version

Look up SQL Server books online for the following commands:

						
UPDATE STATISTICS,
STATS_DATE,
DBCC SHOW_STATISTICS,
CREATE STATISTICS,
DROP STATISTICS,
sp_autostats,
sp_createstats,
sp_updatestats

Could you please some items which you may see in an execution plan indicating the query is not optimized.

Index Scan or Table Scan

Hash Joins

Thick arrows (indicating large work tables)

Parallel streams (Parallelism)

Bookmark lookup (or key lookup)

What structure can you implement for the database to speed up table reads?

Follow the rules of DB tuning we have to:

properly use indexes ( different types of indexes)

properly locate different DB objects across different tablespaces, files, and so on.

3 create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB, and …)

What is normalization? Explain different levels of normalization?

It is the way to eliminate redundant data

Reduces null value

Enables efficient indexing

1NF – Removes duplicated attributes, Attribute data should be atomic, and attribute should be the same kind.

2NF – Should be in 1NF and each non-key is fully dependent on the primary key.

3NF – Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed. Normalization is done in.

What is denormalization and when would you go for it?

It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications.

How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

Relationships in SQL server are explained below

One to One –It can be implemented as a single table. Rarely it is implemented in two tables. For each instance, in the first entity, there is one and only one in the second entity and vice versa.

One to Many –For each instance, in the first entity, there can be one or more in the second entity. For each instance, in the second entity, there can be one and only one instance in the first entity.

Many to Many –For each instance, in the first entity there can be one or more instances in the second entity, and for each instance, in the second entity there can be one or more instances in the first entity.

What is the difference between the Primary key and the Unique key.

Primary Key

1.Enforces uniqueness of the column in a table

2.Default clustered index

3.Does do not Allow nulls

Unique Key

1. Enforces the uniqueness of the column in a table.

2.Default non-clustered index.

3.Allows one null value

Define the following keys:

Candidate key, Alternate key, Composite key.

1. Candidate key –Key which can uniquely identify a row in the table.

2. Alternate key –If the table has more than one candidate key and when one becomes a primary key the rest becomes alternate keys.

3. Composite key –More than one key uniquely identifies a row in a table.

What are defaults? Is there a column to which a default can’t be bound?

1. It is a value that will be used by a column if no value is supplied to that column while inserting data.

2. I can’t be assigned for identity and timestamp values.

What are user-defined data types and when you should go for them?

Lets you extend the base SQL Server data types by providing a descriptive name and format to the database

E.g. Flight_num appears in many tables and all these tables have varchar(8)

Create a user-defined data-type

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.

What part does database design have to play in the performance of a SQL Server-based application?

It plays a very major part. When building a new system, or adding to an existing system, the design must be correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables, and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed.

It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.

What can a developer do during the logical and physical design of a database to help ensure that their database and SQL Server-based application will perform well?

A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design.

A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler.

What are the main steps in Data Modeling?

1. Logical – Planning, Analysis, and Design

2. Physical – Design, Implementation, and Maintenance

What is BCP? When does it use?

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.

When would you use it?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when the query is run again.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

The one-to-one relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Explain primary key in Sql Server?

This is the combination of fields/columns which are used to uniquely specify a row. Primary Key has a unique constraint defined on the column and the value in the column cannot be NULL.

Explain foreign key in Sql Server?

Foreign key is used to establish a relationship between the columns of another table. Foreign key relationship to be created between two tables by referencing a column of the table to the primary key of another table.

What are the difference between “Where” and “Having” clause in Sql Server?

“Where” clause is used to filter the rows based on condition. “Having” clause used with SELECT clause and this is used with GROUP BY clause. If GROUP BY clause not used then “HAVING” clause works like a “WHERE” clause. Top 50 Sql Server Dba Interview Questions And Answers Pdf

What are Magic Tables in SQL Server?

The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED

These are maintained by SQL server for there Internal processing. When we use update insert or delete on tables these magic tables are used. These are not physical tables but are Internal tables. Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the delete drow. But in case of update statement is fired both Inserted and Deleted table used for records the Original row before updation get store in the Deleted table and new row Updated get store in Inserted table.

List out the different types of locks available in Sql Server?

Below is the list of locks available in Sql Server –

Update Locks

Shared Locks

Exclusive Locks

What is a recursive stored procedure in Sql Server?

A recursive stored procedure is the stored procedure called a child stored procedure inside the parent or main stored procedure. This can be done easily in Sql Server by using “EXEC” keyword in a stored procedure.

For Example

Create Procedure SP_Test

BEGIN

EXEC sp_Child @params

END

How the authentication mode can be changed?

Authentication mode can be changed using the following steps –

Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run SQL Enterprise Manager.

What are the new features in SQL Server 2005 when compared to SQL Server 2000?

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

Database Partitioning

Dynamic Management Views

System Catalog Views

Resource Database

Database Snapshots

SQL Server Integration Services

Support for Analysis Services on a Failover Cluster.

Profiler being able to trace the MDX queries of the Analysis Server.

Peer-to-Peer Replication

Database Mirroring

How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in the same statement and store them in local variable.

Explain Sql server authentication modes?

Below are the two authentication modes of sql server –

Mixed Mode

Windows Mode

What does man by SQL Wildcard Characters in Sql Server?

WildCard Characters are used with “LIKE” operator in Sql Server. Wildcards are used for data retrieval process from the table. Some of the wildcards are

“-“ – This is used for substituting a single character.

“%” – This is used for substituting zero or more characters.

[list of chars] – Ranges of characters for matching.

Explain Indexing and what are the advantages of it?

Indexing contains pointers to the data in a table. Indexes are created in a table to retrieve the data quickly. So Indexing improves the performance as the retrieval of data takes less time. Indexing will be done for columns which are being used more often while retrieving.

Explain “NOT NULL Constraint” in Sql Server?

“NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If this constraint has not set then by default columns will accept NULL values too.

Why use Sub Query in Sql Server and List out types of Sub Queries?

Sub Queries are queries within a query. The parent or outer query is being called as the main query and the inner query is called as inner query or subquery. Different types of Sub Queries are

Correlated – It is not an independent subquery. It is an inner query which is referred by the outer query.

Non-Correlated – It is an independent subquery. It can be executed even without an outer query.

What are user-defined functions (UDFs) in Sql Server?

User-Defined functions are being used to handle complex queries.

There are two types of user-defined functions –

Scalar – This type of functions are used for returning a single scalar value.

Table-Valued – This type of function are used for returning a table which has a list of rows. Sql supports data type called table which is used here for returning a table.

List out the difference between Union and Union All in Sql Server?

Union is used to combine all result sets and it removes the duplicate records from the final result set obtained, unlike Union All which returns all the rows irrespective of whether rows are being duplicated or not.

Union checks the number of columns given in the SELECT statement should be equal or not and the datatypes are also should be same and same applied to UnionAll.

Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?

@@ROWCOUNT – Used to return the number of rows affected in the table due to the last statement.

@@ERROR – Used to return the error code which is occurred due to the last SQL statement. ‘0’ means there are no errors.

Why use Cursor in Sql Server?

The cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.

List all types of constraints in Sql Server?

Below is the list of constraints in Sql Server –

NOT NULL

DEFAULT

CHECK

PRIMARY KEY

FOREIGN KEY

UNIQUE

Why use IDENTITY in Sql Server?

IDENTITY is used for a column to auto increment the value of the column in a table and it is mainly used with Primary Key.

What are the differences between Union, Intersect, and Minus operators?

Union operator is used to combining all the results or records of the table and it removes the duplicate values.

Interact operator is used to returning the common list of records between two result sets.

Minus operator is used to getting the list of records from the first result set and which is not there in the second result set.

Explain “ROW_NUMBER()” in Sql Server with an example?

“ROW_NUMBER()” is used to return a sequential number of each row within a given partition. “1” will be the first position. “Partition By” and “Order By” can be used along with “ROW_NUMBER()”. Below is the example for the same

SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE Emp Name Name IS NOT NULL

What are the differences between “ROW_NUMBER()”, “RANK()” and “DENSE_RANK()”?

“ROW_NUMBER” – Used to return a sequential number of each row within a given partition.

“RANK” – Used to returns a new row number for each distinct row in the result set and it will leave a number gap in case of duplicates.

“DENSE_RANK” – Used to returns a new row number for each distinct row in the result set and it will not leave any number gap in case of duplicates.

Explain about Link Server in Sql Server?

Linked Server is used to enable execution of OLEDB data sources in remote servers. With Linked servers, we can create easy SQL statements which will allow remote data to be joined, combined and retrieved with data in local.

What are the advantages of user-defined functions over stored procedures in Sql Server?

User-Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored procedure cannot be called. In the case of table-valued functions, the returned Table.

Why use “NoLock” in Sql Server?

“No Lock” is used for unlocking the rows which are locked by some other transaction. Once after the rows are committed or rolled back no need to use No Lock. For example.

What is the significance of master, tempdb and model databases?

master – This database will have data and catalog of all the databases of SQL Server instance.

tempdb – tempdb database will have temporary objects like local and global temporary tables and stored procedures as well.

the model – model database is mainly used for creating new user databases.

Explain about unique identifier data type in Sql Server?

Unique Identifier datatype mainly used for primary key columns of the tables or any other columns which need to have unique Ids. “NEWID()” function can be used for generating a unique identifier for the column. Unique Identifiers are also named as GUIDs.

Why use “PIVOT” in Sql Server?

Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create a separate table for displaying summarized data.

Explain Alternate key, Candidate Key and Composite Key in Sql Server?

Alternate Key – To identify a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys.

Candidate Key – Set of fields or columns which are uniquely identified in a row and they constitute candidate keys.

Composite Key – One key formed by combining at least two or more columns or fields.

How to use “DROP” keyword in Sql Server and Give an example?

“DROP” keyword is used to drop either Index or database or table. Below is a list of Sql statements using Drop keyword.

Dropping Index

DROP INDEX my_index

Dropping Database

DROP DATABASE my_database

Dropping Table

DROP TABLE my_table

deleted when the connection that created it is closed.

Can SQL servers link to other servers?

SQL server can be joined to any database which owns OLE-DB provider to provide a link. Example: Oracle holds OLE-DB provider which has a link to unite among the SQL server club.

What are subquery and its properties?

A subquery is a query which can be nested inside the main query like Select, Update, Insert or Delete statements. This can be used when the expression is allowed. Properties of the subquery can be defined as.

A sub query should not have ordered by clause.

A subquery should be placed in the right-hand side of the comparison operator of the main query.

A subquery should be enclosed in parenthesis because it needs to be executed first before the main query.

More than one subquery can be included.

What are the kinds of subquery?

There are 3 kinds of subquery –

The query which returns only one row is Single row subquery.

Which returns multiple row is a multiple-row subquery.

Which returns multiple columns to the main query is multiple-column subqueries. Beside that subquery returns, the Chief query will be performed.

What is the SQL server agent?

The SQL Server agent performs an active role in day to day duties of SQL server manager (DBA). Server agent’s goal is to achieve the jobs simply with the scheduler motor which provides our jobs to work at proposed date and time.

What are scheduled tasks in SQL Server?

Scheduled jobs are practiced to automate methods that can be operated on a cataloged event at a constant interval. This scheduling of jobs benefits to decrease human interference throughout the night time and feed can be produced at an appropriate time. A user can further order the jobs in which it allows to be produced.

What is COALESCE in SQL Server?

COALESCE is used to return the first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

What structure can you implement for the database to speed up table reads?

A) Follow the rules of DB tuning we have to:

properly use indexes ( different types of indexes)

properly locate different DB objects across different tablespaces, files and so on.

create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB,

What are statistics, under what circumstances they go out of date, how do you update them?

A) Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. The query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

If there is a significant change in the key values in the index.

The database is upgraded from a previous version Q) Could you please some items which you may see in an execution plan indicating the query is not optimized.

Index Scan or Table Scan.

Hash Joins.

Thick arrows (indicating large work tables).

Parallel streams (Parallelism).

Bookmark lookup (or key lookup).

In what sequence SQL statement is processed?

The clauses of the select are processed in the following sequence

FROM clause

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

ORDER BY clause

TOP clause

Can we add an identity column to decimal datatype?

YES, SQL Server support this

What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN with nowhere clause?

OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

What is meant by Active – Passive and Active-Active clustering setup?

An Active- Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

How do you generate file output from SQL?

While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR. QUERTY >> RESULT TO >> Result to FILE

How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution?

SET NOCOUNT OFF

What is the importance of a recovery model?

Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.

What is Index, cluster index and nonclustered index?

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore the table may have only one clustered index.

Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consist of the data pages. instead, the leaf node contains index rows.

Write down the general syntax for a SELECT statement covering all the options.

Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).

What are the different authentication modes in SQL Server and how can you change authentication mode?

SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred to as Mixed Mode. To change the Authentication mode, read one of my blogs Changing SQL Server Authentication Mode.

What are the common trace flags used with SQL Server?

Deadlock Information: 1204, 1205, and 1222
Network Database Files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 (SQL Server 2005 and 2008) to reduce TempDB contention

Why is resource database hidden in SQL Server?

Resource database is a read-only system database which is hidden from users. System objects such as sys. objects are physically stored in Resource Database which logically appears in the sys schema of each database. However, resource database will only store system objects and you cannot store user data or metadata.

Can we take a backup for Resource DB?

No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.

How to upgrade SQL Server 2000 to SQL Server 2008?

To upgrade SQL Server 2000 to SQL Server 2008, the safest approach would be a side-by-side upgrade. We can do this either by using backup and restore or by detaching/attaching the database files. However, it is suggested to use the former as it is a safer approach. The steps are as follows:

Run the Upgrade Analysis tool from Microsoft. Address any issues raised there, first
Identify DTS packages. These must be migrated manually unless we buy Pragmatic Works
Rebuild the DTS packages as SSIS
Script out all SQL Agent jobs
Script out all security
Backup the systems and validate the backups (preferably by restoring them to another system)
Run the security script on the new system
Run restore on the new system
Validate the databases by running DBCC
Manually update all statistics
Run the SQL Agent script

How to rollback the upgrade?

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming; whereas, in a side-by-side upgrade, the legacy instance remains available if a rollback is needed.

How to speed up the DBCC CHECKDB execution process?

The below command enforces an exclusive lock on the database, which makes the process faster:

						
DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK

What is PHYSICAL_ONLY in DBCC CHECKDB?

The PHYSICAL_ONLY command limits checking the integrity of the physical structure of a page and record headers and can also detect torn pages, checksum failures, and common hardware failures. Using this option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying the PHYSICAL_ONLY option causes DBCC CHECKDB to skip all checks of the FILESTREAM data.

						
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY

How to check data purity using DBCC CHECKDB?

The below command causes DBCC CHECKDB to check the database for column values that are not valid or out of range:

						
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, DATA_PURITY

With this command, DBCC CHECKDB detects columns with date and time values, which are either larger or less than the acceptable range for the DATETIME data type. It also limits checking the integrity of the physical structure of the page and record.

How to read the graphical execution plan?

The graphical execution plan should be read from Right to Left:

Check the graphical execution plan of a stored procedure/query
Table Scan: Index is missing
Index Scan: Proper indexes are not used
BookMark Lookup: Limits the number of columns in the select list
Filter: Removes any functions from the WHERE clause; may require additional indexes
Sort: Checks if the data really needs to be sorted, if an index can be used to avoid sorting, and if sorting can be done at the client-side more efficiently?
DataFlow Arrow (high density): Sometimes, we find few rows as the outcome, but the arrow line density indicates the query/proc processing huge number of rows
Cost: Easily finds out which table/operation taking much time

From the execution plan, we can find out the bottlenecks and give possible solutions to avoid latency

What are the permissions required to view the execution plan?

Either a user must be mapped to sysadmin/db_owner/db_creator or the user will be granted the below permission:

						
GRANT SHOWPLAN TO [username]

What Is The Difference Between Lock, Block And Deadlock?

Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.

Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using

						
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0

Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.

What Is The Meaning Of Lock Escalation And Why/how To Stop This?

Understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.

How To Truncate The Log In Sql Server 2008?

BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server doesn’t allow you to truncate the log now otherwise whole purpose of a DB is defeated.

What Changes In The Front End Code Is Needed If Mirroring Is Implemented For The High Availability?

You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

Where Does The Copy Job Runs In The Log Shipping Primary Or Secondary?

Secondary server. This question is basically asked to find out whether you have a hands on work on log shipping or not.

What Are The Ways To Find What Code Is Running For Any Spid?

Well there are many ways to do this.

						
SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
SELECT * FROM sys.dm_exec_requests
Get the spid from above two queries and use any of the following query to get what is happening behind that spid.
dbcc inputbuffer()
sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text()
sql2005 and sql2008 – SELECT * FROM fn_get_sql()

When You Get Following Error? Error 3154: The Backup Set Holds A Backup Of A Database Other Than The Existing Database?

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 name

Does Dbcc Checkdb Requires Db To Be In Single_user Mode?

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

How To View The Error Log For Any Specific Instance?

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%’

According To You What Goes Into Making The Best Database Administrator?

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.

I Have All The Primary Data Files, Secondary Data Files As Well As Logs. Now, Tell Me Can I Still Restore The Database Without Having A Full Backup?

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.

As Per Your Opinion What Are The Five Top Responsibilities Of A Dba?

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

Securing database from physical and logical integrity damage.

Restoring database from backup as a part of disaster management plan.

Optimizing queries performance by appropriate indexing and optimizing joins, where conditions, select clause etc.

Designing new schema, support legacy schema, and legacy database systems.

Helping developers improve their SQL-related code writing skill.

One Of The Developers In My Company Moved One Of The Columns From One Table To Some Other Table In The Same Database. How Can I Find The Name Of The New Table Where The Column Has Been Moved?

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.

What Is The Difference Between Sql Server 2000 Object Owner And Sql Server 2005 Schema?

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.

What Is Bi? I Have Heard This Term Before But I Have No Idea About It?

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.

What Is Your Recommendation For A Query Running Very Slow?

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.

What Should Be The Fill Factor For Indexes Created On Tables?

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.

Which Feature In Sql Server 2008 Has Surprised You? You Can Name Just One.

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.

How Do You Test Your Database?

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.

What Are System Databases Into Sql Server (2005/2008)

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

What Stored By The Tempdb ?

Row versions, cursor, temp objects.

What Stored By The Model?

Templates of new database objects, like tables and column.

What Stored By The Master?

Server’s configurations and logins.

What Stored By The Msdb?

Scheduled jobs, Backup/Restore and DTA information.

Can We Perform Backup Restore Operation On Tempdb?

What Is Stored In The Mssqlsystemresource Database?

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

Where The Sql Logs Gets Stored?

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

What Are The Joins In Sql Server?

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

Describe The Left Outer Join & Right Outer Join.

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.

How To Find The Version Of Sql Server?

						
Select @@version

How To Find The Service Pack Installed?

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

What Are The Difference Between Primary Key And Unique Key?

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

What Is Mean By Clustered Index And Non Clustered Index, Give Syntax Of Creation?

create clustered index index_name on empmst(card)

What Is Scan Table/view And Seek Table/view When Its Occurs?

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

What Is Sql Profiler. What Are The Default Templates With It?

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.

What Are The Dmvs?

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.

What Is The Syntax To Execute The Sys.dm_db_missing_index_details?

Select * from sys.dm_db_missing_index_details

What Is Lock Escalation?

The Query Optimizer initially locks the required Rows for DML operations / Retrieval operations. This also get relevant Pages and Completely table to be 'INTENT' Locked. If more than 50% of rows are specifically locked then this automatically gets the complete PAGE or TABLE to be locked. Lock Escalation mechanism can be controlled by using Locking Hints.

How To Truncate The Log In Sql Server 2012?

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.

What Is The Purpose Of Sql Profiler In Sql Server?

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.

What Is Copyonly Data Backup? How This Is Useful?

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.

What Are Truncate Options Available In Sql Server?

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.

What Are Advantages Of Peer-peer Replication?

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.

What Options We Use To Secure Replication Data?

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

What Are The Uses Of Standby Mode In Log Shipping?

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.

When Does Error 3154 Occur?

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.

What Are Recommended Options To Be Used While Using Db Mirroring?

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.

Where Can You Find 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.

What Is The Status Of Services On Passive Node For Failover Cluster In Sql Server?

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

Can You Move The Resources After Pausing The Node?

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

What Happens If We Start The Full Text Service On Passive 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.

What Is Data Compression?

In SQL SERVER 2008 R2, Data Compression comes in two types viz., Row Compression where It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. Page compression uses the Row compression technique internally and also applies prefix level compression.For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which reside after page headers. A reference number is assigned to these prefixes and that reference number is replaced wherever those prefixes are being used.

What Are The Basic Functions For Master, Msdb, Model, Tempdb And Resource System Databases?

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.

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.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

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.

What Is Service Broker?

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.

Where Sql Server User Names And Passwords Are Stored In Sql Server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

What Is Policy Based Management (pbm)?

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.

What Is Replication With Database Mirroring?

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.

What Are Sparse Columns In Sql Server?

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.

What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

Steps to take to improve performance of queries:

Use indexes efficiently

Create all primary and foreign keys and relationships among tables.

Avoid using cursors

Avoid using Select*, rather mention the needed columns and narrow the resultset as needed.

Denormalize

Use partitioned views

Use temporary tables and table variables

Reduce joins and heavy clauses like GROUP BY if not needed

Implement queries as stored procedures.

Have a WHERE Clause in all SELECT queries.

Use data types wisely

Instead of NULLS use string values such as N/A

What Is A Deadlock And What Is A Live Lock? How Will You Go About Resolving Deadlocks?

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.

What Is Blocking And How Would You Troubleshoot It?

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.

Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.

Use sp_who and sp_who2 to return more columns to get more information around the blocking.

Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.

To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

Explain The Different Types Of Backups Available In Sql Server?

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..

What Is Database Isolation In Sql Server?

Database isolation comes into play when we need to isolate the database and protect it from other things on the network. This protection is achieved using locks. The type of lock and the level of isolation level needed is referred as isolation level in SQL Server.

Types of isolation levels:

READ COMMITTED: Shared locks are held while any data is being read.

READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.

REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.

SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.

Last Updated: 2023-09-23