Basic SQL Server Interview Questions and Answers

1. What is SQL ?

  • SQL (Structured Query Language) is a standard language used to manage and manipulate databases.
  • It allows users to create, retrieve, update, and delete data efficiently.
  • SQL is used in various database management systems such as MySQL, SQL Server, and PostgreSQL.
  • It consists of various commands categorized as DML, DDL, DCL, and TCL.
  • SQL helps in querying databases using SELECT, INSERT, UPDATE, and DELETE statements.
  • It is widely used in web applications, data analytics, and business intelligence.

2. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft.
It supports transaction processing, business intelligence, and analytics applications.
It allows users to store, retrieve, and manage structured data efficiently.
SQL Server provides features such as security, scalability, and high availability.
It supports T-SQL, which extends SQL with procedural programming capabilities.
SQL Server comes in different editions like Enterprise, Standard, and Express.

3. What are the different types of SQL commands?

SQL commands are categorized into four main types:
1. DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
2. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
3. DCL (Data Control Language): GRANT, REVOKE.
4. TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
These commands help in managing database structure, data, security, and transactions.

4. What is a primary key in sql?

A primary key is a column or set of columns in a table that uniquely identifies each row.
It ensures that no duplicate or NULL values exist in the column.
A table can have only one primary key, which can consist of multiple columns (composite key).
The primary key helps maintain data integrity and ensures efficient indexing.
It is automatically indexed for faster query performance.
The PRIMARY KEY constraint is defined using the CREATE TABLE or ALTER TABLE statement.

5. What is a foreign key in sql?

A foreign key is a column or set of columns in one table that refers to the primary key of another table.
It establishes a relationship between two tables and enforces referential integrity.
Foreign keys prevent actions that would break the link between related tables.
A foreign key can have duplicate values and NULL values unless specified otherwise.
It helps maintain consistency by ensuring only valid data is inserted into the referenced column.
The FOREIGN KEY constraint is defined using the CREATE TABLE or ALTER TABLE statement.

6. What is the difference between SQL and T-SQL?

SQL (Structured Query Language) is a standard language for managing databases.
T-SQL (Transact-SQL) is an extension of SQL used in Microsoft SQL Server.
T-SQL includes procedural programming constructs like variables, loops, and conditionals.
SQL is used to query and manipulate data, while T-SQL allows for more advanced scripting.
T-SQL enables the creation of stored procedures, functions, and triggers in SQL Server.
SQL is supported by multiple database systems, whereas T-SQL is specific to SQL Server.

7. What is T-sql?

  • T-SQL (Transact-SQL) is an extension of SQL used in Microsoft SQL Server.
  • T-SQL includes procedural programming constructs like variables, loops, and conditionals.
  • T-SQL enables the creation of stored procedures, functions, and triggers in SQL Server.

8. What is a unique key in SQL Server?

A unique key ensures that all values in a column are distinct.
Unlike a primary key, a table can have multiple unique keys.
A unique key column can have NULL values, but only one NULL value is allowed per column.
It helps in preventing duplicate values and maintaining data integrity.
The UNIQUE constraint is defined using the CREATE TABLE or ALTER TABLE statement.
Unique keys automatically create an index to speed up queries.

9. What is an index in SQL Server?

An index is a database object that improves the speed of data retrieval operations.
SQL Server supports clustered and non-clustered indexes.
A clustered index determines the physical order of data in a table.
A non-clustered index creates a separate structure to improve search performance.
Indexes speed up SELECT queries but may slow down INSERT, UPDATE, and DELETE operations.
They help in optimizing database performance for large datasets.

10. What is a view in SQL Server?

  • A view is a virtual table that represents the result of a SQL query.
  • Views help in simplifying complex queries and improving security.
  • They do not store data physically but provide a way to access data from multiple tables.
  • Views can be created using the CREATE VIEW statement and modified using ALTER VIEW.
  • Indexed views can improve performance by storing the result set.
    They help in hiding sensitive information by restricting direct table access.

11. What is a stored procedure in SQL Server?

  • A stored procedure is a precompiled set of SQL statements stored in the database.
  • It helps in improving performance and reducing network traffic.
    Stored procedures support input and output parameters.
  • They are used to encapsulate business logic within the database.
    Security is improved by restricting direct access to tables.
  • They are created using the CREATE PROCEDURE statement.

12. What is normalization in SQL?

  • Normalization is the process of organizing data in a database to reduce redundancy and improve integrity.
  • It involves dividing large tables into smaller tables and defining relationships between them.
  • The different normal forms (1NF, 2NF, 3NF, BCNF, etc.) help eliminate duplication and improve efficiency.
  • Normalization reduces data anomalies and ensures consistency.
  • It improves query performance by reducing redundant data.
  • However, excessive normalization may lead to complex joins and reduced performance in some cases.

13. What is denormalization in sql?

  • Denormalization is the process of combining tables to reduce joins and improve query performance.
  • It is used when normalized data structures lead to complex queries with multiple joins.
  • Denormalization helps in faster data retrieval at the cost of some redundancy.
  • It is commonly used in data warehousing and reporting applications.
  • While denormalization increases data redundancy, it enhances read performance.

14. What is a trigger in SQL Server?

  • A trigger is a special stored procedure that is automatically executed when a specific event occurs.
  • Triggers can be defined for INSERT, UPDATE, or DELETE operations.
  • They help enforce business rules and maintain data integrity.
  • SQL Server supports AFTER and INSTEAD OF triggers.
  • Triggers can be used to audit changes, prevent unwanted modifications, and log transactions.
  • They should be used carefully as they may impact performance.

15. What is a cursor in SQL Server?

  • A cursor is a database object used to retrieve and process row-by-row results from a query.
  • It is used when individual row processing is required, such as in procedural logic.
  • Cursors can be forward-only, static, dynamic, or keyset-driven.
  • They should be used cautiously as they can degrade performance.
  • SQL Server provides alternatives like set-based operations for better performance.
  • Cursors are declared, opened, fetched, and closed using specific SQL commands.

16. What are Common Table Expressions (CTEs) in SQL Server?

  • A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SQL statement.
  • It is created using the WITH keyword and can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
  • CTEs improve code readability and can be used recursively to simplify hierarchical queries.
  • They help avoid the use of complex subqueries by breaking them into simpler parts.
  • Unlike temporary tables, CTEs do not store data permanently.
    Example usage:
    WITH CTE_Name AS (SELECT column1, column2 FROM Table_Name) SELECT * FROM CTE_Name;

17. What is the difference between UNION and UNION ALL ?

UNION and UNION ALL are used to combine the results of two or more SELECT queries.
UNION removes duplicate rows from the final result set, whereas UNION ALL includes all rows, even duplicates.
UNION has additional processing overhead due to duplicate elimination, making it slower than UNION ALL.
UNION ALL is faster because it does not perform a DISTINCT operation.
Example:
SELECT column1 FROM table1 UNION SELECT column1 FROM table2; (Removes duplicates)
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2; (Includes duplicates)

18. What is an Index in SQL Server, and what are its types?

An index in SQL Server is a database object that improves the speed of data retrieval operations.
It works like a book index, allowing faster lookups based on indexed columns.
Types of indexes:
1. Clustered Index: Determines the physical order of data in a table (one per table).
2. Non-Clustered Index: Creates a separate structure from the data and stores pointers to actual data.
3. Unique Index: Ensures that all values in an indexed column are unique.
4. Filtered Index: Indexes a subset of data based on a filter condition.
5. Full-Text Index: Used for complex text-based queries.

19. What is a Stored Procedure in SQL Server ?

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit.
It improves performance by reducing query parsing and optimizing execution plans.
Stored procedures allow modular programming and code reuse.
They enhance security by restricting direct access to database objects.
Stored procedures accept parameters and can return values.

Example:
CREATE PROCEDURE GetCustomers AS SELECT * FROM Customers;

20. What are Transactions in SQL Server?

A transaction in SQL Server is a sequence of operations performed as a single unit of work.
It ensures data consistency using the ACID properties (Atomicity, Consistency, Isolation, Durability).
Transactions can be explicitly controlled using COMMIT and ROLLBACK.
Types of transactions:
1. Explicit Transactions: Defined using BEGIN TRANSACTION, COMMIT, and ROLLBACK.
2. Implicit Transactions: SQL Server automatically starts a transaction for certain operations.
3. Auto-commit Transactions: Each statement is treated as an individual transaction.
4. Distributed Transactions: Spans multiple databases or servers.

21. What is Deadlock in SQL Server and how can it be avoided?

A deadlock occurs when two or more transactions hold locks on resources and wait indefinitely for each other to release the locks.
SQL Server automatically detects and resolves deadlocks by terminating one of the transactions (deadlock victim).
Deadlocks can be avoided by:
1. Accessing resources in a consistent order.
2. Keeping transactions short and efficient.
3. Using lower isolation levels where possible.
4. Using NOLOCK or READ COMMITTED SNAPSHOT to minimize locking issues.
5. Avoiding user interaction within transactions.

22. write intermediate level sql server quesions and answers

INNER JOIN: Returns only the matching records from both tables based on the join condition.

OUTER JOIN: Returns matching records as well as unmatched records.

  • LEFT OUTER JOIN: Returns all records from the left table and matching records from the right table. NULL is returned for non-matching records.
  • RIGHT OUTER JOIN: Returns all records from the right table and matching records from the left table.
  • FULL OUTER JOIN: Returns all records from both tables, with NULLs for non-matching records.

23. What is the difference between WHERE and HAVING?

WHERE: Used to filter records before grouping (applies to individual rows).

HAVING: Used to filter records after grouping (applies to aggregated data).

Example:

SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department
HAVING COUNT(*) > 5;

24. What are Common Table Expressions (CTEs) in SQL Server ?

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example:

WITH EmployeeCount AS (
    SELECT department, COUNT(*) AS emp_count
    FROM Employees
    GROUP BY department
)
SELECT * FROM EmployeeCount WHERE emp_count > 5;

25. What is the difference between a TEMP table and a TABLE variable?

Temporary Table (#TempTable): Stored in the tempdb database and can have indexes. They can be used in multiple stored procedures or batches.

Table Variable (@TableVar): Stored in memory, does not support indexes (except primary keys), and is scoped to a batch or stored procedure.

Example:

DECLARE @TempTable TABLE (ID INT, Name VARCHAR(50));
INSERT INTO @TempTable VALUES (1, 'John'), (2, 'Jane');
SELECT * FROM @TempTable;
line

Mail us :

Address :

India
  • Facebook_logo
  • Instagram_logo
  • Linkedin_logo
  • X-twitter_logo
  • Youtube_logo
  • Pinterest_logo

Sub Categories

Copyrights © 2024 letsupdateskills All rights reserved