Total votes: 0
Print: Print Article
Please login to rate or to leave a comment.
Published: 30 Nov 2007
Pinal Dave shares his notes about Q&A with Database Administrators.
I have been in India for more than a month now, as I am leading a very large outsourcing project. We have conducted few interviews since the project required more Database Administrators and Senior Developers. I am listing few of the questions discussed during all the interviews. The whole event of interviews was very interesting. I met some very good programmers from all over the country. Many interesting questions were discussed between interviewers and candidates. I am listing some of those questions here. Some are technical and some are just my personal opinions.
Questions & Answers
Q. What kind of Database Administrator do you think is the best Database Administrator?
A. Primary job of DBA is to secure the data and keep it safe as well as being able to reproduce data efficiently, when required. A Database Administrator, who can fulfill the requirements of Securing Data and Retrieving Data, is the best DBA as per my view.
When I hire DBA I always ask them questions about backup strategies and efficient restoring methodologies.
Q. Can I restore the database if I do not have full backup but I have all the primary data file and secondary data files as well as logs?
A. You can not restore the database without having full database backup. However, if you have copy of all the data files (.
.ndf) and logs (.
ldf), when database was in working condition (or your desired state) you can attach that database using
Q. As per your opinion, what are the five top responsibilities of DBA?
A. I rate following five tasks as the most important responsibilities of DBA.
- Securing the database from physical as well as logical integrity damage.
- Restore the database from backup as part of disaster management plan.
- Optimize the queries performance by proper indexing and optimizing joins, where conditions, select clause etc.
- Design the new schema and support legacy schema as well legacy database systems.
- Help developers to be better at writing SQL related code.
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:
The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but 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:
SQL Server 2005:
In SQL Server 2000, before dropping the user who owns database objects, all the objects belonging to that user need to be either dropped or their owner has to be changed. Every time a user has to be dropped or modified, system admin has to go through this inconvenient process.
In SQL Server 2005, 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 automatically receive the same permissions and credentials as the schema to which they are assigned. Due the same reason in SQL Server 2005 - 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 what is it?
A. BI is an acronym that stands for Business Intelligence. Microsoft has started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting key enterprise data so that anyone in your company can quickly and easily ask questions based on accurate and timely data. Effective BI allows end users to use data to understand why your business got the particular results that it did, to decide on courses of action based on past data, and to accurately forecast future results
Q. What is your recommendation if a query is running very slow?
A. Your question is very difficult to answer without looking at code, application and physical server. Few things should be looked at right away when similar situations arise.
- 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 keep my servers default fill factor as 90.
Q. Which feature in SQL Server 2008 (to be released in February 2008) has surprised you? Name only one.
A. Plan Freezing is the new feature I never thought of. It is a very interesting feature and 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, enabling 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 will be describing my generic database testing method as well as stored procedure testing methods.
- 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 kind of input values and checking the expected output variables.
Testing Stored Procedures:
- Understand the requirements in terms of Business Logic.
- Check that code follows all the coding standards.
- Comparing the fields' requirements of application to the fields retrieved by a stored procedure. They should match.
- Repeatedly run stored procedures many times with different input parameters and compare the output with expected results.
- Pass invalid input parameters and see if a stored procedure has good error handling.
Pinalkumar Dave is Microsoft SQL Server MVP and author of several hundreds SQL Server articles. He has six years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NE...
This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.
You might also be interested in the following related blog posts
Defusing Database Time Bombs: Avoiding the Need to Refactor Databases
MYSQL Could not Drop Object [Content] (Cannot delete or update a parent row: a foreign key constraint fails, 1217) DROP DATABASE DatabaseName
Big Data Operational Databases Supporting Big Data Columnar, Graph and Spatial Database Day 14 of 21
SQL SERVER Difference between DATABASEPROPERTY and DATABASEPROPERTYEX
Testing object databases for .NET: Eloquera, STSdb, Siaqodb, Ninja Database Lite
Telerik OpenAccess ORM introduces the industry-first full support for SQL Azure and extends functionality
Issue 50 - DotNetNuke 5 Admin Modules Access, Reset Logins, iPhone OWS
Migrating to Postgresql with my friend NHibernate
Using SqlBulkCopy To Perform Efficient Bulk SQL Operations
Five Simple Database Design Errors You Should Avoid
Please login to rate or to leave a comment.