SQL DBA Interview questions

Here are the few SQL SERVER DBA Interview questions

• What is RDBMS?
• What are the Properties of the Relational Tables?
• What is Normalization?
• What is De-normalization?
• How is ACID property related to Database?
• What are the Different Normalization Forms?

• What is a Stored Procedure?
• What is a Trigger?
• What are the Different Types of Triggers?
• What is a View?
• What is an Index?
• What is a Linked Server?
• What is a Cursor?
• What is Collation?

• What is the Difference between a Function and a Stored Procedure?
• What is subquery? Explain the Properties of a Subquery?
• What are Different Types of Join?
• What are Primary Keys and Foreign Keys?
• What is User-defined Functions? What are the types of User-defined Functions that can be created?

• What is an Identity?
• What is DataWarehousing?
• What languages BI uses to achieve the goal?
• What is Standby Servers? Explain Types of Standby Servers.
• What is Dirty Read?
• Why can’t I use Outer Join in an Indexed View?
• What is the Correct Order of the Logical Query Processing Phases?

• Which TCP/IP port does the SQL Server run on? How can it be Changed?
• What are the Difference between Clustered and a Non-clustered Index?
• What are the Different Index Configurations a Table can have?
• What are Different Types of Collation Sensitivity?
• What is OLTP (Online Transaction Processing)?
• What’s the Difference between a Primary Key and a Unique Key?
• What is Difference between DELETE  and TRUNCATE Commands?

• What are Different Types of Locks?
• What are Pessimistic Lock and Optimistic Lock?
• When is the use of UPDATE_STATISTICS command?
• What is the Difference between a HAVING clause and a WHERE clause?
• What is Connection Pooling and why it is Used?
• What are the Properties and Different Types of Sub-Queries?
• What are the Authentication Modes in SQL Server? How can it be Changed?

• Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
• What is an SQL Server Agent?
• Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
• What is Log Shipping?
• Name 3 ways to get an Accurate Count of the Number of Records in a Table?
• What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
• What is the Difference between a Local and a Global Temporary Table?
• What is the STUFF Function and How Does it Differ from the REPLACE Function?
• What is PRIMARY KEY?
• What is UNIQUE KEY Constraint?
• What is FOREIGN KEY?

• What is CHECK Constraint?
• What is NOT NULL Constraint?
• What is the difference between UNION and UNION ALL?
• What is B-Tree?
• How to get @@ERROR and @@ROWCOUNT at the Same Time?
• What is a Scheduled Job or What is a Scheduled Task?
• What are the Advantages of Using Stored Procedures?
• What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
• Can SQL Servers Linked to other Servers like Oracle?
• What is BCP? When is it Used?

• What Command do we Use to Rename a db, a Table and a Column?
• What are sp_configure Commands and SET Commands?
• How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
• What is Difference between Commit and Rollback when Used in Transactions?
• What is an Execution Plan? When would you Use it? How would you View the Execution Plan?

• What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
• What is the difference between CHAR and VARCHAR Datatypes?
• What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
• What is the Difference between VARCHAR and NVARCHAR datatypes?
• Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
• How to Optimize Stored Procedure Optimization?
• What is SQL Injection? How to Protect Against SQL Injection Attack?
• How to Find Out the List Schema Name and Table Name for the Database?
• What is CHECKPOINT Process in the SQL Server?

• How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
• How to Find the List of Fixed Hard Drive and Free Space on Server?
• Why can there be only one Clustered Index and not more than one?
• What is Difference between Line Feed (\n) and Carriage Return (\r)?
• Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
• What is a Hint?
• How to Delete Duplicate Rows?
• Why the Trigger Fires Multiple Times in Single Login?

• What is Aggregate Functions?
• What is Use of @@ SPID in SQL Server?
• What is the Difference between Index Seek vs. Index Scan?
• What is the Maximum Size per Database for SQL Server Express?
• How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
• What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
• How to Create Primary Key with Specific Name while Creating a Table?
• What is T-SQL Script to Take Database Offline – Take Database Online
• How to Enable/Disable Indexes?
• Can we Insert Data if Clustered Index is Disabled?
• How to Recompile Stored Procedure at Run Time?
• Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
• What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
• Does the Order of Columns in UPDATE statements Matter?

• What are the basic functions for master, msdb, model, tempdb and resource databases?
• What is the Maximum Number of Index per Table?
• Explain Few of the New Features of SQL Server 2008 Management Studio
• Explain IntelliSense for Query Editing
• Explain MultiServer Query
• Explain Query Editor Regions
• Explain Object Explorer Enhancements
• Explain Activity Monitors

• What is Service Broker?
• Where are SQL server Usernames and Passwords Stored in the SQL server?
• What is Policy Management?
• What is Database Mirroring?
• What are Sparse Columns?
• What does TOP Operator Do?
• What is CTE?
• What is MERGE Statement?
• What is Filtered Index?
• Which are the New Data Types Introduced in SQL SERVER 2008?

• What are the Advantages of Using CTE?
• How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
• What is CLR?
• What are Synonyms?
• What is LINQ?
• What are Isolation Levels?
• What is Use of EXCEPT Clause?
• What is XPath?
• What is NOLOCK?
• What is the Difference between Update Lock and Exclusive Lock?

• How will you Handle Error in SQL SERVER 2008?
• What is RAISEERROR? What is RAISEERROR?
• How to Rebuild the Master Database?
• What is the XML Datatype?
• What is Data Compression?
• What is Use of DBCC Commands?
• How to Copy the Tables, Schema and Views from one SQL Server to Another?
• How to Find Tables without Indexes?

• How to Copy Data from One Table to Another Table?
• What is Catalog Views?
• What is PIVOT and UNPIVOT?
• What is a Filestream?
• What is SQLCMD?
• What do you mean by TABLESAMPLE?
• What is ROW_NUMBER()?
• What are Ranking Functions?
• What is Change Data Capture (CDC) in SQL Server 2008?

• How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
• What is the CPU Pressure?
• How can I Get Data from a Database on Another Server?
• What is the Bookmark Lookup and RID Lookup?
• What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
• What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
• How can I Check that whether Automatic Statistic Update is Enabled or not?
• How to Find Index Size for Each Index on Table?
• What is the Difference between Seek Predicate and Predicate?
• What are Basics of Policy Management?
• What are the Advantages of Policy Management?

• What are Policy Management Terms?
• What is the ‘FILLFACTOR’?
• Where in MS SQL Server is ’100’ equal to ‘0’?
• What are Points to Remember while Using the FILLFACTOR Argument?
• What is a ROLLUP Clause?
• What are Various Limitations of the Views?
• What is a Covered index?
• When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
• What are Wait Types?
• How to Stop Log File Growing too Big?
• If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?