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 (http://blog.SQLAuthority.com), DotNetSlackers

12 thoughts on “SQL SERVER – Questions and Answers with Database Administrators

  1. “What is your recommendation for a query running very slow”

    Are the items you’d check in the order you’d check them?

    My first thing to check would always be the query itself to see whether it was using indexes effectively – 99% of the slow performing queries I encounter are either badly written or not supported by suitable indexes.

    Occasionally statistics need updating, but SQL Server mostly takes care of that.

    Restarting servers and upgrading hardware is generally a long way down the list of things to try.

    Like

  2. “What is your recommendation for a query running very slow”

    I’m questioning even giving a DBA the option of retarting the data services to fix that one. Given a Jr level reader, they might actually think that is a good idea. Granted hardware is always a major consideration when seeing long running queries based on already well formed plans, I would state to perfmon the server while under pressure of the query first. Then the first option is check the configuration of the server OLTP vs OLAP and warehouse solutions may be more relavant

    Like

  3. Pinal,
    i have some dbts in the following areas in this article.
    1. How do you test your database?

    Testing Databases:

    Test database is a replica of development database. so
    do we need to check column datatype, constraints, stored procedures, triggers and these methods should match with the application?.
    would that be enough to check the count for tables, stored procedures, functions, views, triggers?

    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.

    These points are fine. even these should be verified at the time of design phase, and coding phase. by conducting design reviews, code reviews the above mentioned points are covered right?
    the last two points are fine in order to check the performance of the query and validating the data.

    Please let me know if i m wrong…
    Thanks in advance.

    Like

  4. Good questions … unfortunately I expected a lot more advanced stuff. To maintain real live high transaction databases you should ask DBAs a lot more tougher questions.

    Keep it up. Nice post.

    Like

  5. Hey Pinal,

    I am a Lecture, and currently i am taking SQL server 2000 as wel as Oracle in Master.

    so i want some material of these two. with examples,,,

    can you give some important links for this…..

    thanks..

    Bhavin Jasani

    Like

  6. Hi Pinal ,
    I read yours site time to time and I do enjoy it.I am trying to solve a complex query that has table joins.

    May I ask you to guide me how/where I can write my problem so you can take alook at it?

    Many thanks
    Regards
    Josh

    Like

  7. Hi Pinal
    I hope I am posting my question in the correct place. I ‘m trying to implement this query but I can’t figure out why I am not getting the result

    Here are the descriptions:
    I have a table let’s say its TableAct
    Acct# Date WithdrawAmt DepositAmt
    !24455 2012-11-19-00.00.00 1245.77 200.50
    125577 2011-02-12-00.00.00 100.98 578.00

    TableCustomerOrd
    ID# COrder# CustID Ord_Descriptions VendorType
    124455 7712AS 123 1AAA Permanent
    125577 9914DL 346 1BBB Partial
    124789 UK1234 111 2HJ5 Permanent
    567891 FR0912 567 5LGY Partial

    TableCustomerDtls
    CustID Descriptions Delivery Address ZipCode
    123 1AAA_BLUESH SUCCESSFUL222 Main St 97002
    346 1BBB_CHASE DECLINE 40 West Side 97122
    111 2HJ5_CITIBANK SUCCESSFUL ……. …….
    567 5LGY_VANGURD DECLINED ……..

    DeliveryFlight
    FlightOrder# FlightCustID FlightDt
    7712AS 123 2011-9-29-00.00.00
    9914DL 346 2010-11-2-00.00.00
    UK1234 111 2012-4-1-00.00.00
    FR0912 567 2012-9-11-00.00.00

    I want to update TableAct on the following condition
    1.TableAct. Acct# = TableCustomerOrd. ID#
    AND
    2. TableCustomerOrd. CustID = TableCustomerDtls.CustID and I want to take TableCustomerOrd. Ord_Descriptions field that matches with TableCustomerDtls. Descriptions field anything before “_” . Therefore ‘1AAA’, ‘2HJ5’ etc
    AND
    3. DeliveryFlight.FlightOrder# = TableCustomerOrd. COrder#
    AND DeliveryFlight.FlightCustID = TableCustomerOrd. CustID
    Also TableCustomerDtls. Delivery = ‘SUCCESSFUL’

    AND
    DeliveryFlight.FlightOrder# = TableCustomerOrd. COrder#
    AND DeliveryFlight.FlightCustID = TableCustomerOrd. CustID
    Also TableCustomerDtls. Delivery = ‘DECLINED

    Then I want to compare
    DeliveryFlight. FlightDt > DeliveryFlight. FlightDt

    Basically I need to match table DeliveryFlight columns FlightOrder#, FlightCustID with TableCustomerOrd. Moreover TableCustomerDtls column Delivery = ‘DECLINED’
    Again
    Table DeliveryFlight columns FlightOrder#, FlightCustID need to match with TableCustomerOrd. Moreover TableCustomerDtls column Delivery = ‘SUCCESSFUL’
    And then compare ‘SUCCESSFUL’ FlightDt with ‘DECLINED’ FlightDt

    Update table1
    Set …
    FROM TableAct AC
    Join TableCustomerOrd CustOd
    ON AC.Acct# = CustOd.ID#
    Join TableCustomerDtls CDtls
    ON CDtls.CustID = CustOd. CustID
    AND (CustOd.Ord_Descriptions =
    Left(CDtls.Descriptions, LEN(rtrim(CDtls.Descriptions))))
    JOIN DeliveryFlight DF
    ON DF.FlightOrder# = CustOd.COrder#
    AND DF.FlightCustID = CustOd.CustID
    AND CDtls.Delivery = ‘SUCCESSFUL’
    JOIN DelivaryFlight DF2
    ON DF2.FlightOrder# = DF.COrder#
    AND DF2.FlightCustID = DF.CustID
    AND CDtls.Delivery = ‘DECLINED’
    WHERE DF. FlightDt > DF2. FlightDt
    AND DepositAmt > 100

    Please take a look all the tables above, this query needs to match DF.FlightOrder# = CustOd.COrder#
    AND DF.FlightCustID = CustOd.CustID
    AND TableCustomerDtls.Delivery = ‘SUCCESSFUL’
    Not only this also needs to match
    DF2.FlightOrder# = DF.COrder#
    AND DF.FlightCustID = DF.CustID
    AND TableCustomerDtls.Delivery = ‘DECLINED’
    So I am doing two separate JOIN conditions on the DeliveryFlight table.

    Any other way I can implement it? Is it a good choice? Moreover please assist me to make this query work.

    Also TableCustomerOrd.Ord_Descriptions field should match with TableCustomerDtls. Descriptions field anything before “_” . Therefore ‘1AAA’, ‘2HJ5’ etc. However TableCustomerDtls Descriptions field could have values those does not have any ‘_’ in between Ex: ‘TT11’, ‘GOL1’ etc so I want to select those values too.
    This one is not working correctly CustOd.Ord_Descriptions =
    Left(CDtls.Descriptions, LEN(rtrim(CDtls.Descriptions))))
    So I tried this :
    SUBSTRING(CDtls.Descriptions, 0, CHARINDEX(‘_’, CDtls.Descriptions))

    It works but elimiate any string that doesn’t have a ‘_’ into it.
    How can I accomplish this?

    Thank you.

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s