SQL interview questions and answers page gives you a snapshot of the SQL interview for the Database Administrator job (SL DBA). It’s one of the fastest-growing job positions in the I.T. Industry in current days. Here we have listed SQL Interview Questions and answers for DBA. SQL Interview Questions cover basic & important SQL DBA interview questions for experienced and freshers.
What is SQL
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 25 SQL Interview questions & answers.
SQL Interview Questions and Answers
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?
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 an index.
3. What is an SQL server agent?
- SQL is a component of the Microsoft SQL server.
- SQL server agent plays a vital role in the day-to-day tasks.
- The server agent’s purpose is to implement the task easily with the scheduler engine. It allows our jobs and handles other automated scheduled dates and times.
4. What is Trigger?
- A trigger is a database object that is attached to the table.
- There are many aspects in its similar way to storing a procedure.
- It is executed with automatically modified data, insert, delete, and update operations.
- A trigger consists of an 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 subquery returns only one row.
- Multiple Row Sub Query: The subquery returns multiple rows.
- Multiple column Sub Query: The subquery returns multiple columns.
7. Can we check locks in the database?
Yes, we can check locks in the database.
8. What is DCL?
DCL stands for Data Control Language.
9. What is the fill factor and its value?
Fill Factor
- 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.
Value
- Default the fill factor value is set to 0
10. What are the different Topologies in which Replication can be configured?
- Replication can be configured in any topology depending on keeping in view 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-to-peer 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?
- A stored procedure can reduce 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 be linked to other servers like Oracle?
- SQL Server can be linked to any services provided.
- It has an OLE –DB provider from Microsoft that allows a link.
16. Which TCP/IP port does the SQL server run on?
SQL server runs on part 1433 but we can also change it for better security.
17. Can you explain the types of joins that we can have with the 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 determines 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 a huge amounts 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?
- Grant
- Deny
- Revoke
22. What are the recovery models for a database?
There are 3 recovery models available database
- Full
- Bulk – Logged
- Simple
23. What are database isolation levels in SQL servers?
- 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 is 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 restoring them to a standby server.
- This technique is 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 upgrades and Side-by-Side upgrades.