SQL DBA Interview Questions and Answers for Freshers – Part 1

Question.1   Explain about your SQL Server DBA Experience?

Answer:   This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.

Question.2    What are the different SQL Server Versions you have worked on?

Answer:    The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

Question.3   What are the different types of Indexes available in SQL Server?

Answer:   The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

Question.4   What is the difference between Clustered and Non-Clustered Index?

Answer:   In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

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

Answer:   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 a Failover Cluster.

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

Peer-toPeer Replication

Database Mirroring

Question.6   What are the High-Availability solutions in SQL Server and differentiate them briefly?

Answer:   Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features. Comparing the High Availability Features in SQL Server 2005

Question.7   How do you troubleshoot errors in a SQL Server Agent Job?

Answer:   Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

Question.8   What is the default Port No on which SQL Server listens?

Answer:   433

Question.9   How many files can a Database contain in SQL Server?How many types of data files exists in SQL Server? How many of those files can exist for a single database?

Answer:   A Database can contain a maximum of 32,767 files.

There are Primarily 2 types of data files Primary data file and Secondary data file(s)

There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

Question.10   What is DCL?

Answer:   DCL stands for Data Control Language.

Question.11   What are the commands used in DCL?

Answer:   GRANT, DENY and REVOKE.

Question.12   What is Fill Factor?

Answer:   Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

Question.13   What is the default fill factor value?

Answer:   By default the fill factor value is set to 0.

Question.14   Where do you find the default Index fill factor and how to change it?

Answer:    The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.

The other option of viewing and changing this value is using sp_configure.

Question.15   What is a system database and what is a user database?

Answer:   System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

A user database is a database that we create to store data and start working with  the data.

Question.16   What are the recovery models for a database?

Answer:  There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

Question.17   What is the importance of a recovery model?

Answer:  Primarily, 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.

Question.18   What is Replication?

Answer:   Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.

Question.19    What the different types of Replication and why are they used?

Answer:   There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose, depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales

Question.1  Which TCP/IP port does SQL Server run on? How can it be changed?

Answer:  SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

Question.2   What are the difference between clustered and a non-clustered index?

Answer:  A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. 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 on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Question.3   What are the different index configurations a table can have?

Answer:  A table can have one of the following index configurations:

No indexes

A clustered index

A clustered index and many nonclustered indexes

A nonclustered index

Many nonclustered indexes

Question.4  What are different types of Collation Sensitivity?

Answer:

Case sensitivity – A and a, B and b, etc.

Accent sensitivity

Kana Sensitivity – When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity – A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

Question.5   What is OLTP (Online Transaction Processing)?

Answer:   In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

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

Answer:  Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Question.7   What is difference between DELETE and TRUNCATE commands?

Answer:  Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE:

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE cannot be rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table

DELETE:

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE can be rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

 Question.8   When is the use of UPDATE_STATISTICS command?

Answer:   This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

Question.9   What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Answer:   They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Question.10   What are the properties and different Types of Sub-Queries?

Answer:

Properties of Sub-Query

A sub-query must be enclosed in the parenthesis.

A sub-query must be put in the right hand of the comparison operator, and

A sub-query cannot contain an ORDER-BY clause.

A query can contain more than one sub-query.

Types of Sub-Query

Single-row sub-query, where the sub-query returns only one row.

Multiple-row sub-query, where the sub-query returns multiple rows,. and

Multiple column sub-query, where the sub-query returns multiple columns

Question.11   What is SQL Profiler?

Answer:  SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Question.12  What are the authentication modes in SQL Server? How can it be changed?

Answer:  Windows mode and Mixed Mode – SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Question.13   Which command using Query Analyzer will give you the version of SQL server and operating system?

Answer: SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’).

Question.14    What is SQL Server Agent?

Answer:  SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.

Question.15   Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Answer:  Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

Question.16   What is Log Shipping?

Answer:  Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Question.17  Name 3 ways to get an accurate count of the number of records in a table?

Answer:

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Question.18    What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

Answer:  When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

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

Answer:  A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

Following are SQL DBA Interview Questions and Answers for Freshers – Part 1

1. What are the different components in SQL Server?

Database Engine, Analysis Services, Reporting Services, Integration Services, Master Data Services, Data Mining, PowerPivot, Replication etc.

2. Tell me different SQL Server Editions?

Enterprise, Standard, Developer, Express, Compact, Workgroup, Web, SQL Azure, Datacenter, Data Warehouse.

3. What is Tabular Data Stream?

To connect SQL Server Database, client applications uses particular protocol which is called Tabular Data Stream (TDS)

4. What is SQL Server Configuration Manager (SSCM)?

SQL Server Configuration Manager maintains database services, network protocols and ports for SQL Server Database.

5. What is the default port number for SQL Server? How to find it?

Port 1433 for TCP IP & port 1434 for UDP.

You can check the same from SQL Server Configuration Manager or by executing below query.

xp_readerrorlog 0, 1, N’Server is listening on’

6. What is Collation in SQL Server? 

A Collation in SQL Server defines a set of rules governing correct use of characters for language like Polish, Greek etc and alphabets such as Latin1_General etc.

7. What are different system databases in SQL Server?

master, model, msdb, tempdb, resource.

8. How do you monitor disk space used by tempdb files?

By querying sys.dm_db_file_space_usage

9. What are different types of recovery models in SQL Server?

simple, full, bulk logged.

10. Tell me different types of backup in SQL Server?

Full, Differential, Transaction Log, Partial, Copy-Only, File/File Group and Tail-log.

Interview Questions


Some important interview questions..on SQL DBA


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
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..
3. What third party database tools are your favorites?If somebody’s been doing database administration long enough to claim the title Senior DBA, they’ve built up a little wish list of database management tools they’ve seen along the way. Tool types might include:

Data modeling
Change management
Backup compression
Performance monitoring
AlertingIf they had a $X tool budget for their workstation, how would they spend it? Forget corporate standards – I want to know what tools they’d use if they could pick on their own. I’m not asking what software they’ve had experience using, because they might work somewhere so cheap that they’re restricted to native tools only. They have to have at least seen some ads for products that looked cool, though.
I might follow up with questions about tools we were using in-house already by saying things like, “We’re currently using Product X for monitoring. Have you seen it? What’d you think of it?”
This does two things: it gives me an outside opinion about other tools out there that my DBAs could be using to do a better job, and it tells me how much the job candidate has seen.

How do you identify and correct a SQL Server performance issue?
Identification - Use native tools like Profiler, Perfmon, system stored procedures, dynamic management views, custom stored procedures or third party tools
Analysis - Analyze the data to determine the core problems
Testing - Test the various options to ensure they perform better and do not cause worse performance in other portions of the application
Knowledge sharing - Share your experience with the team to ensure they understand the problem and solution, so the issue does not occur again
Additional information - MSSQLTips.com Category:Performance Tuning and Query Optimization
How do you re-architect a process?
Review the current process to understand what is occurring
Backup the current code for rollback purposes
Determine what the business and technical problems are with the process
Document the requirements for the new process
Research options to address the overall business and technology needs
For example, these could include:
Views
Synonyms
Service Broker
SSIS
Migrate to a new platform
Upgrade in place
Design and develop a new solution
Conduct testing (functional, load, regression, unit, etc.)
Run the systems in parallel
Sunset the existing system
Promote the new system
Additional information - Checklist to Re-Architect a SQL Server Database
What types of replication are supported in SQL Server?SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.

What happens on checkpoint?Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

What purpose does the model database serve?The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.

How do you trace the traffic hitting a SQL Server?SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

Why would you use SQL Agent?SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups

Which TCP/IP port does SQL Server run on? –
SQL Server runs on port 1433 but we can also change it for better security.
What is the basic difference between clustered and a non-clustered index?
- The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.