SQL SERVER – Questions and Answers with Database Administrators

I have been in India for long time now, and at present, I am managing a very large outsourcing project. Recently, we conducted few interviews since the project required more Database Administrators and Senior Developers, and I must say it was an enthralling experience for me! I got the opportunity to meet some very talented and competent programmers from all over the country. Scores of interesting questions were discussed between the interviewers and the candidates, which made the whole interview process nothing short of an enriching occasion! I am listing some of the interesting questions discussed during the interviews. Some are technical and some are purely my personal opinion.

Q. According to you what goes into making the best Database Administrator?

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

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

A. 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 that database using sp_attach_db.

Q. As per your opinion what are the five top responsibilities of a DBA?

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

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

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

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

Q. What is the difference between SQL Server 2000 object owner and SQL Server 2005 schema?

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

Q. What is BI? I have heard this term before but I have no idea about it?

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

Q. What is your recommendation for a query running very slow?

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

Q. What should be the fill factor for Indexes created on tables?

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

Q. Which feature in SQL Server 2008 has surprised you? You can name just one.

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

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

Reference : Pinal Dave (https://blog.sqlauthority.com), DotNetSlackers