As a Database Administrator or a developer working with SQL Server, it is important to stay updated with the latest concepts and ideas in the field. In this article, we will discuss some interesting questions and concepts related to SQL Server that were discussed during recent interviews.

What makes the best Database Administrator?

According to one of the interviewees, the primary job of a Database Administrator (DBA) is to secure the data and efficiently retrieve it when required. Therefore, a DBA who can fulfill these requirements is considered the best. During interviews, questions about backup strategies and efficient restoring methodologies were commonly asked to assess the candidate’s skills in securing and retrieving data.

Top responsibilities of a DBA

When it comes to the responsibilities of a DBA, there are several key tasks that are considered important. These include:

1. Securing the database from physical and logical integrity damage.
2. Restoring the database from backup as part of a disaster management plan.
3. Optimizing query performance through appropriate indexing and optimization techniques.
4. Designing new schemas and supporting legacy schema and database systems.
5. Helping developers improve their SQL-related code writing skills.

Finding a column that has been moved to another table

In a scenario where a developer has moved a column from one table to another within the same database, you can use system views to find the name of the new table. For example, in SQL Server 2005, you can run the following code:

SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.columns
WHERE name = 'YourColumnName'

This query will return all the tables that use the specified column name, helping you identify the new table where the column has been moved.

SQL Server 2000 object owner vs SQL Server 2005 schema

In SQL Server 2000, dropping a user who owns database objects requires either dropping all the objects or changing their owner. This can be inconvenient for system administrators. However, in SQL Server 2005 and later versions, a user can access a database through a schema. Users are assigned to schemas, and multiple users can be assigned to a single schema, automatically receiving the same permissions and credentials. This eliminates the need to change object owners when a user is dropped, resulting in no negative effects on the database itself.

Understanding Business Intelligence (BI)

BI stands for Business Intelligence and is a method for storing and presenting accurate and timely key enterprise data to drive intelligent decisions for business success. It provides up-to-date information to CXOs, IT Managers, Business Consultants, and distributed teams, enabling them to understand the cause of business results, make informed decisions, and accurately forecast future results. Microsoft has been promoting BI since the launch of SQL Server 2005.

Improving query performance

When faced with a slow-running query, there are a few immediate actions that can be taken:

• Restart the server.
• Upgrade hardware if necessary.
• Check indexes on tables and create new indexes if needed.
• Ensure SQL Server has priority over other operating system processes.
• Update statistics on the database tables.

Choosing the fill factor for indexes

The fill factor specifies how full the leaf level of each index page should be during index creation or alteration. It is represented as a percentage from 1 to 100. The default fill factor is 0. It is recommended to set the fill factor to a value that suits your specific server and workload. Some administrators prefer a fill factor of 90 as a starting point.

Surprising features in SQL Server 2008

One of the interviewees mentioned that Plan Freezing is a surprising feature in SQL Server 2008. This feature provides greater query performance stability and predictability by locking down query plans. It allows organizations to promote stable query plans across hardware server replacements, upgrades, and production deployments.

Testing databases and stored procedures

When it comes to testing databases, it is important to validate table column data types and values, ensure proper index implementation and performance, validate constraints and rules for data integrity, and match application field requirements with corresponding database fields.

For testing stored procedures, it is essential to understand the business logic, follow coding standards, compare application field requirements with stored procedure output, run the stored procedures with different input parameters, and test error handling with invalid input parameters.

These were some of the interesting questions and concepts discussed during recent interviews. As a SQL Server professional, it is important to stay updated with such concepts and ideas to enhance your skills and knowledge in the field.