SQL DBA Interview Questions
SQL is used to communicate with a database. It is used in programming and designed for managing data held in a relational database management system. SQL includes data insert update and delete, query, schema creation and modification and data access control. Here I have listed the Top Most 25 SQL DBA Interview questions.
1. What are the two authentication modes in SQL Server?
There are two authentication mode
- Windows mode
- Mixed mode
2. What is the difference between Clustered and Non-Clustered Index?
- The leaf level pages are the actual data pages of the table. The data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
Non- Clustered Index
- The leaf node of a Non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index.
3. What is SQL server agent?
- SQL is a component of Microsoft SQL server.
- SQL server agent plays a vital role in day to day task.
- Server agent purpose is to implement the task easily with the scheduler engine. It allows our jobs and handles other automated scheduled date and time.
4. What is Trigger?
- Trigger is a database object that is attaches to the table.
- There are many aspects in its similar way to store a procedure.
- It is executed with automatically modified data, insert, delete, and update operations.
- Trigger consists of event an insert, delete, update and an action.
- Triggers are used to preserve data integrity by checking on or changing data in a consistent manner.
5. What are the different index configurations a table can have?
- Index configuration
- No indexes
- Clustered indexes
- Clustered index and many non-clustered indexes
- Non-clustered indexes
- Many non-clustered indexes
6. What are the properties and different Types of Sub-Queries?
Properties of sub Query
- A sub-query must be enclosed in the parenthesis.
- A sub-query must be put in the right hand of the comparison operator.
- A sub-query cannot contain an ORDER-BY clause.
- A query can contain more than one sub-query.
Types of sub Query
- Single Row Sub Query: The sub query returns only one row.
- Multiple Row Sub Query: The sub query returns multiple rows.
- Multiple column Sub Query: The sub query returns multiple columns.
7. Can we check locks in database?
Yes,we can check locks in database.
8. What is DCL?
DCL is stands for Data Control Language.
9. What is fill factor and its value?
- Fill Factor is a setting that is applicable to Indexes in SQL Server.
- The fill factor value determines how much data is written to an index page when it is created or rebuilt.
- Default the fill factor value is set to 0
10. What the different Topologies in which Replication can be configured?
- Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication.
- It can be any of the following Publisher, Distributor and Subscriber on the same SQL Instance.
- Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
- Publisher, Distributor and Subscriber on individual SQL Instances.
11. What are the different ways you can create Databases in SQL Server?
- T-SQL; Create Database command.
- Using Management Studio
- Restoring a database backup
- Copy Database wizard
12. What are the new features in SQL Server 2005 when compared to SQL Server 2000?
- Database Partitioning
- Dynamic Management Views
- System Catalogue Views
- Resource Database
- Database Snapshots
- SQL Server Integration Services
- Support for Analysis Services on Failover Cluster.
- Profiler being able to trace the MDX queries of the Analysis Server.
- Peer-toPeer Replication
- Database Mirroring
13. What are the different types of database compression introduced in SQL Server 2008?
- Row compression
- Page compression
14. What are the advantages of using Stored Procedures?
- Stored procedure can reduced network traffic and latency, boosting application performance.
- Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
- Stored procedures help promote code reuse.
- Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
- Stored procedures provide better security to your data.
15. Can SQL Servers linked to other servers like Oracle?
- SQL server can be linked to any server provided.
- It has OLE –DB provider from Microsoft allow to a link.
16. Which TCP/IP port does SQL server run on?
SQL server runs on part 1433 but we can also change it is for better security.
17. Can you explain the types of joins that we can have with SQL server?
Three types of joins
- Inner join
- Outer join
- Cross join
18. When do you use SQL profiler?
- SQL profiler utility allows us to basically track connections to the SQL server.
- It also determine activities such as which SQL scripts are running failed jobs and etc.
19. What is BCP? When do we use it?
- Bulk Copy is a tool used to copy huge amount of data from tables and views.
- It won’t copy the structures of the same.
20. What are the OS services that the SQL server installation adds?
- MS SQL SERVER SERVICE
- SQL AGENT SERVICE
- DTC ( Distribution transaction coordinator)
21. What are the commands used in DCL?
22. What are the recovery models for a database?
There are 3 recovery models available database
- Bulk – Logged
23. What is database isolation levels in SQL server?
- Read Uncommitted The lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read.
- Read Committed Database Engine default level.
- Repeatable read
- Serializable the highest level, where transactions are completely isolated from one another
24. What is Log Shipping?
- Log shipping is the process of automating backup database and transaction log files on a primary database server, and the restoring them into a standby server.
- In this technique supported by Microsoft SQL server, MySQL, 4D, and PostgreSQL.
25. What are the different types of Upgrades that can be performed in SQL Server?
Two different upgrades are In-place upgrade and Side-by-Side Upgrade.