Microsoft SQL Server interview questions for DBA

Are you a Database Administrator? Need to update DBA technical knowledge or need to prepare for a job interview? Check out this collection of Microsoft SQL Server interview questions for DBA...

What's SQL Server
How do you test proper TCP/IP configuration Windows machine?
What is RAID and what are different types of RAID configurations?
How do you define testing of network layers?
What are the steps you will take to improve performance of a poor performing query?
How many layers of TCP/IP protocol combined of?
What are the steps you will take, if you are tasked with securing an SQL Server?
How many bits IP Address consist of?
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
What is blocking and how would you troubleshoot it?
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
What protocol both networks use?
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
What is the difference between them (Ethernet networks and token ring networks)?
What are statistics, under what circumstances they go out of date, how do you update them?
What kind of LAN types do you know?
What are the different ways of moving data/databases between servers and databases in SQL Server?
Do you know how to configure DB2 side of the application?
What is database replicaion? What are the different types of replication you can set up in SQL Server?
Explain CREATE DATABASE syntax
Let�s assume you have data that resides on SQL Server 6.5. You have to move it SQL Server 7.0. How are you going to do it?
But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%?
When you create a database how is it stored?
How to determine the service pack currently installed on SQL Server?
What is new philosophy for database devises for SQL Server 7.0?
Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
What is a default TCP/IP socket assigned for SQL Server?
What is database replication? What are the different types of replication you can set up in SQL Server?
What is the system function to get the current user�s user id?
Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
What is a major difference between SQL Server 6.5 and 7.0 platform wise?
Is SQL Server implemented as a service or an application?
What is the difference in Login Security Modes between v6.5 and 7.0?
What is a traditional Network Library for SQL Servers?
If you encounter this kind of an error message, what you need to look into to solve this problem?
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
What is a join and explain different types of joins.
Can you have a nested transaction?
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
What is the system function to get the current user's user id?
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
What is a self join? Explain it with an example.
What is a transaction and what are ACID properties?
Explain different isolation levels
What type of Index will get created after executing the above statement?
What's the maximum size of a row?
What is lock escalation?
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
What are constraints? Explain different types of constraints.
CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement?
What�s the maximum size of a row?
What is lock escalation?
What�s the difference between DELETE TABLE and TRUNCATE TABLE commands?
Explain the storage models of OLAP
What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
What are the steps you will take to improve performance of a poor performing query?
Database design - What is normalization? Explain different levels of normalization?
Database design - What is denormalization and when would you go for it?
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
What�s the difference between a primary key and a unique key?
What are user defined datatypes and when you should go for them?
What is bit datatype and what�s the information that can be stored inside a bit column?
Define candidate key, alternate key, composite key.
What are defaults? Is there a column to which a default can�t be bound?