Top 50 Practical SQL Server Questions and Answers for Everyday Use

1.Question: What is a SQL Server subquery, and how is it different from a JOIN?

Answer: A subquery is a query nested within another query. It can be used to retrieve data for filtering or performing calculations. The main difference from a JOIN is that a subquery is enclosed within parentheses and returns a single value or a single result set, which can be used as a part of a larger query.

Example: 

-- Subquery example

SELECT Name

FROM Employees

WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

2. Question: What is an SQL Server index, and why is it important?

Answer: An index is a database structure that improves the speed of data retrieval operations on a table. It works like a book index, allowing the database engine to quickly locate and access specific rows based on the indexed column(s). Indexes are essential for optimizing query performance.

Example:

-- Creating an index

CREATE INDEX IX_EmployeeLastName ON Employees(LastName);

3. Question: Explain the difference between the INNER JOIN and LEFT JOIN clauses with examples.

Answer:

INNER JOIN retrieves rows from both tables that have matching values in the specified columns.

LEFT JOIN retrieves all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Example:

-- INNER JOIN example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

-- LEFT JOIN example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

4. Question: What is a SQL Server stored procedure, and why would you use one?

Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. They are typically used for tasks like data manipulation, business logic, or database administration. Stored procedures are stored in the database and can accept parameters for dynamic execution.

Example:

-- Creating a simple stored procedure

-- Creating a simple stored procedure
CREATE PROCEDURE GetEmployeeByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Employees WHERE LastName = @LastName;
END;

5. Question: What is SQL injection, and how can it be prevented?

Answer: SQL injection is a security vulnerability that occurs when an attacker is able to manipulate SQL queries by injecting malicious code. To prevent SQL injection, you should:

Use parameterized queries or prepared statements.

Avoid dynamic SQL whenever possible.

Validate and sanitize user inputs.

Limit database permissions for application users to the minimum required.

Use stored procedures with parameterized inputs.

6. Question: How can you retrieve the top 5 records from a table in SQL Server?

Answer: You can retrieve the top 5 records using the TOP keyword.

Example:

 
-- Retrieve the top 5 records
SELECT TOP 5 * FROM TableName; 

7. Question: Explain the concept of ACID properties in the context of database transactions.

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability:

Atomicity ensures that a transaction is treated as a single, indivisible unit of work.

Consistency ensures that a transaction brings the database from one valid state to another.

Isolation ensures that concurrent transactions do not interfere with each other.

Durability guarantees that once a transaction is committed, its effects are permanent and survive system failures.

Example: ACID properties are fundamental concepts and are not directly demonstrated through SQL queries. They are enforced by the database management system to maintain data integrity and reliability.

These questions and examples should help you understand key SQL Server concepts and prepare for interviews.

8. Question: What is the difference between a primary key and a unique key constraint?

Answer: Both primary keys and unique keys enforce uniqueness, but there are differences. A primary key is a combination of a unique key and a NOT NULL constraint, meaning it uniquely identifies a record and does not allow NULL values. A unique key constraint enforces uniqueness but allows NULL values.

Example:

-- Creating a table with a primary key
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
);

-- Creating a table with a unique key constraint
CREATE TABLE Products (
    ProductID INT UNIQUE,
    ProductName VARCHAR(50)
);

9. Question: What is normalization, and why is it important in database design?

Answer: Normalization is a process in database design where you organize data into related tables to minimize data redundancy and improve data integrity. It helps in avoiding update anomalies and ensures that each piece of data is stored in only one place. Normalization is essential for efficient storage and retrieval of data.

Example:

Consider an unnormalized table:
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentName VARCHAR(50)
);
Normalized into separate tables:
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

10. Question: How can you retrieve the current date and time in SQL Server?

Answer:

-- Get the current date and time

SELECT GETDATE();

11. Question: Explain the concept of a SQL Server trigger and provide an example.

Answer:A trigger is a database object that automatically executes in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers are often used to enforce data integrity rules or perform audit logging.

Example:

-- Creating an AFTER INSERT trigger
CREATE TRIGGER AuditInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, TableName, RecordID, DateTime)
    SELECT 'INSERT', 'Employees', EmployeeID, GETDATE()
    FROM inserted;
END;

12. Question: What is the purpose of the HAVING clause in SQL?

Answer:The `HAVING` clause is used to filter the results of a grouped query. It specifies a condition that applies to the result of an aggregation function, allowing you to filter groups based on aggregate values.

Example:

-- Find departments with more than 3 employees
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 3;

13. Question: How do you use the CASE statement in SQL, and provide an example?

Answer:The `CASE` statement is used for conditional logic in SQL queries. It allows you to perform different actions based on specified conditions.

Example:

-- Using CASE to categorize employees based on salary
SELECT EmployeeName, Salary,
    CASE
        WHEN Salary >= 50000 THEN 'High'
        WHEN Salary >= 30000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

14. Question: What is a self-join, and when would you use one?

Answer:A self-join is a type of join where a table is joined with itself. It is typically used when you need to retrieve related data within the same table, such as hierarchical data or when comparing records within the same table.

Example:

-- Retrieving managers and their direct reports
SELECT M.EmployeeName AS Manager, E.EmployeeName AS DirectReport
FROM Employees AS M
INNER JOIN Employees AS E ON M.EmployeeID = E.ManagerID;

15. Question: How do you use the PIVOT and UNPIVOT operators in SQL Server?

Answer:

PIVOT is used to transform rows into columns.

UNPIVOT is used to transform columns into rows.

Example (PIVOT):

-- Pivoting data to show sales by year
SELECT *
FROM Sales
PIVOT (SUM(Revenue) FOR Year IN ([2020], [2021], [2022])) AS SalesByYear;
Example (UNPIVOT):

-- Unpivoting data to show sales by year SELECT Year, Revenue FROM Sales UNPIVOT (Revenue FOR Year IN ([2020], [2021], [2022])) AS SalesByYear;

16. Question: What is the purpose of the RANK() and DENSE_RANK() window functions in SQL?

Answer:`RANK()` and `DENSE_RANK()` are window functions used to assign a ranking to rows within a result set based on specified columns. The main difference is that `RANK()` leaves gaps in rankings for duplicate values, while `DENSE_RANK()` assigns the same rank to duplicate values.

Example (RANK()):

-- Ranking employees by salary
SELECT EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
Example (DENSE_RANK()):


-- Ranking employees by salary with dense ranking
SELECT EmployeeName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseSalaryRank
FROM Employees;

17. Question: What are Common Table Expressions (CTEs) in SQL, and how are they different from subqueries?

Answer:CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the `WITH` clause and provide a more readable way to structure complex queries. Subqueries, on the other hand, are nested queries within the main query.

Example (CTE):

-- Using a CTE to find the average salary
WITH AverageSalaries AS (
    SELECT AVG(Salary) AS AvgSalary FROM Employees
)
SELECT EmployeeName, Salary, AvgSalary
FROM Employees
CROSS JOIN AverageSalaries;

18. Question: What is dynamic SQL, and when might you use it in SQL Server?

Answer:Dynamic SQL is a technique where SQL statements are constructed and executed dynamically at runtime. It is often used when you need to build SQL statements based on user input, change table or column names dynamically, or perform conditional SQL generation.

Example:

-- Dynamic SQL to query a specified table
DECLARE @TableName NVARCHAR(50) = 'Customers';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sp_executesql @SQL;

19. Question: What is the purpose of the TRY...CATCH block in SQL Server, and how does it work?

Answer:The `TRY...CATCH` block is used for error handling in SQL Server. It allows you to catch and handle errors that occur during the execution of a batch of SQL statements. If an error occurs within the `TRY` block, control is transferred to the `CATCH` block for error handling.

Example:

BEGIN TRY
    -- SQL statements that might cause an error
    INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, 'Customer1');
END TRY
BEGIN CATCH
    -- Error handling code
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

20. Question: How can you calculate the running total of a column in SQL Server?

Answer:

-- Calculating the running total using a subquery
SELECT OrderID, OrderDate, Amount,
       (SELECT SUM(Amount)
        FROM Orders o2
        WHERE o2.OrderID <= o1.OrderID) AS RunningTotal
FROM Orders o1;

21. Question: What are SQL Server transactions, and how do you ensure data consistency using them?

Answer:A transaction is a sequence of one or more SQL statements that are treated as a single unit of work. Transactions ensure data consistency by guaranteeing that a series of operations either all succeed or all fail. You use the `BEGIN TRANSACTION`, `COMMIT`, and `ROLLBACK` statements to manage transactions.

Example:

BEGIN TRANSACTION;

-- SQL statements here

COMMIT; -- If everything is successful
-- or
ROLLBACK; -- If an error occurs

22. Question: How do you create a SQL Server view, and what is its purpose?

Answer:A view is a virtual table based on the result of a SELECT statement. Views allow you to simplify complex queries, provide an abstracted layer of data, and control access to specific columns or rows of a table.

Example:

-- Creating a simple view
CREATE VIEW SalesSummary AS
SELECT OrderID, OrderDate, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY OrderID, OrderDate;

23. Question: What is the purpose of an SQL Server index, and how does it improve query performance?

Answer: An index is a database object that improves the speed of data retrieval operations on a table. It works like a book index, allowing the database engine to quickly locate and access specific rows based on the indexed column(s). Indexes improve query performance by reducing the need for full table scans.

Example:

-- Creating an index on the "LastName" column
CREATE INDEX IX_LastName ON Employees(LastName);

24. Question: What is the difference between the ROW_NUMBER() and RANK() functions in SQL Server?

Answer: Both ROW_NUMBER() and RANK() are window functions used to assign a ranking to rows, but the key difference is how they handle ties. ROW_NUMBER() assigns a unique number to each row, while RANK() assigns the same rank to rows with equal values and leaves gaps for ties.

-- Ranking employees by salary using ROW_NUMBER()
SELECT EmployeeName, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
Example (RANK()):

-- Ranking employees by salary using RANK()
SELECT EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

25. Question: Explain the purpose of the SQL Server MERGE statement, and provide an example.

Answer: The MERGE statement in SQL Server is used to perform insert, update, or delete operations on a target table based on the results of a source table or query. It's often used for performing "upserts" (update or insert) in a single operation.

Example:

-- Merging data from a source table into a target table
MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.KeyColumn = S.KeyColumn
WHEN MATCHED THEN
    UPDATE SET T.Column1 = S.Column1
WHEN NOT MATCHED THEN
    INSERT (Column1, Column2) VALUES (S.Column1, S.Column2);
    

26. Question: What are SQL Server table-valued functions, and how can you use them?

Answer: Table-valued functions (TVFs) are functions that return a table as their result. They can be used in queries just like regular tables. TVFs are useful for encapsulating complex logic and making it reusable.

Example:

-- Creating an inline table-valued function
CREATE FUNCTION GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);
 

27. Question: Explain the purpose of SQL Server views and provide a use case.

Answer: A view is a virtual table based on the result of a SELECT statement. Views allow you to simplify complex queries, provide an abstracted layer of data, and control access to specific columns or rows of a table. A common use case is to hide sensitive data from users by exposing only necessary columns.

Example:

Suppose you have a table Employees with sensitive information like salary. You can create a view that exposes only non-sensitive information:

 -- Creating a view to hide salary information
CREATE VIEW PublicEmployees AS
SELECT EmployeeID, EmployeeName, DepartmentID
FROM Employees;

28. Question: How can you pivot data in SQL Server, and provide an example?

Answer: Pivoting data in SQL Server involves transforming rows into columns. You can achieve this using the PIVOT operator.

Example:

-- Pivoting data to show sales by year
SELECT *
FROM Sales
PIVOT (SUM(Revenue) FOR Year IN ([2020], [2021], [2022])) AS SalesByYear;

29. Question: Explain the purpose of SQL Server triggers and provide an example.

Answer: A trigger is a database object that automatically executes in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers are often used to enforce data integrity rules or perform audit logging.

Example:

-- Creating an AFTER INSERT trigger for audit logging
CREATE TRIGGER AuditInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, TableName, RecordID, DateTime)
    SELECT 'INSERT', 'Employees', EmployeeID, GETDATE()
    FROM inserted;
END;

30. Question: How do you handle NULL values in SQL Server, and what are some common functions for working with NULLs?

Answer: In SQL Server, you can handle NULL values using functions like IS NULL, IS NOT NULL, and COALESCE. These functions allow you to check for NULLs and provide default values when necessary.

Example (IS NULL and IS NOT NULL):

-- Selecting rows with NULL values in a column
SELECT * FROM TableName WHERE ColumnName IS NULL;

-- Selecting rows with non-NULL values in a column
SELECT * FROM TableName WHERE ColumnName IS NOT NULL;
Example (COALESCE):


-- Using COALESCE to provide a default value
SELECT COALESCE(NullableColumn, 'DefaultValue') AS Result FROM TableName;

31. Question: What is the purpose of the OFFSET and FETCH clauses in SQL Server, and how can you use them to implement pagination?

Answer: The OFFSET and FETCH clauses are used for result set pagination in SQL Server. OFFSET specifies the number of rows to skip, and FETCH specifies the number of rows to return.

Example:

-- Implementing pagination using OFFSET and FETCH
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 2;

SELECT * FROM TableName
ORDER BY ColumnName
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

32. Question: What is the difference between a clustered index and a non-clustered index in SQL Server?

Answer: A clustered index determines the physical order of data rows in a table and defines the table's storage structure. There can be only one clustered index per table. A non-clustered index, on the other hand, creates a separate structure for index storage and does not affect the physical order of data. Multiple non-clustered indexes can exist on a table.

33. Question: How can you delete duplicate rows from a table in SQL Server?

Answer: You can delete duplicate rows from a table using a Common Table Expression (CTE) with the ROW_NUMBER() window function.

Example:

-- Deleting duplicate rows based on a unique key
WITH CTE AS (
    SELECT Column1, Column2, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY Column1) AS RowNum
    FROM TableName
)
DELETE FROM CTE WHERE RowNum > 1;

34. Question: What are the benefits of using transactions in SQL Server?

Answer: Transactions in SQL Server provide the following benefits:

Data consistency: Ensures that a series of operations either all succeed or all fail.

Atomicity: Transactions are treated as a single unit of work.

Isolation: Allows concurrent transactions to run without interfering with each other.

Durability: Ensures that committed transactions are permanent.

35. Question: How can you generate unique identifiers (GUIDs) in SQL Server?

Answer: You can generate unique identifiers using the NEWID() function for non-sequential GUIDs or the NEWSEQUENTIALID() function for sequential GUIDs.

Example:

-- Generating a non-sequential GUID
SELECT NEWID() AS UniqueID;

-- Generating a sequential GUID
SELECT NEWSEQUENTIALID() AS SequentialUniqueID;

36. Question: What is the purpose of the TRY_CAST and TRY_CONVERT functions in SQL Server?

Answer: TRY_CAST and TRY_CONVERT are used to safely convert data types in SQL Server. They return NULL if the conversion fails instead of raising an error.

Example:

-- Using TRY_CAST to safely convert to INT
SELECT TRY_CAST('123' AS INT) AS Result;

-- Using TRY_CONVERT to safely convert to DATE
SELECT TRY_CONVERT(DATE, '2023-09-17') AS Result;

37. Question: How do you grant and revoke permissions in SQL Server, and what is the purpose of roles?

Answer: Permissions in SQL Server are granted using the GRANT statement and revoked using the REVOKE statement. Roles are used to group users with similar permissions, simplifying the management of access control.

Example (Granting Permissions):

-- Grant SELECT permission on a table to a user

GRANT SELECT ON TableName TO UserName;

Example (Revoking Permissions):

-- Grant SELECT permission on a table to a user
GRANT SELECT ON TableName TO UserName;
Example (Revoking Permissions):


-- Revoke SELECT permission on a table from a user
REVOKE SELECT ON TableName FROM UserName;
Example (Creating and Assigning Roles):


-- Creating a role
CREATE ROLE MyRole;

-- Assigning a user to a role
EXEC sp_addrolemember 'MyRole', 'UserName';

38. Question: What is the purpose of the SQL Server STUFF() function, and how can you use it?

Answer: The STUFF() function is used to replace a portion of a string with another string. It is often used for manipulating string data.

Example:

-- Using STUFF() to replace characters in a string
SELECT STUFF('Hello World', 7, 5, 'SQL') AS ModifiedString;
-- Result: Hello SQL

39. Question: Explain the concept of foreign keys in SQL Server, and why are they important?

Answer: A foreign key is a column or set of columns in a table that is used to establish a link between the data in two tables. It enforces referential integrity by ensuring that values in the foreign key column(s) match values in the primary key column(s) of another table. Foreign keys are important for maintaining data consistency and relationships between tables.

Example:

Suppose you have two tables, Orders and Customers. The CustomerID column in the Orders table can be a foreign key that references the CustomerID primary key in the Customers table.

40. Question: What are SQL Server user-defined functions (UDFs), and how can you create and use them?

Answer: User-defined functions (UDFs) in SQL Server are custom functions created by users to encapsulate logic and make it reusable. There are two types of UDFs: scalar and table-valued. Scalar UDFs return a single value, while table-valued UDFs return a table.

Example (Scalar UDF):

-- Creating a scalar UDF
CREATE FUNCTION CalculateDiscount(@Price DECIMAL(18, 2), @DiscountPercentage DECIMAL(5, 2))
RETURNS DECIMAL(18, 2)
AS
BEGIN
    RETURN @Price - (@Price * @DiscountPercentage / 100);
END;

-- Using the scalar UDF
SELECT ProductName, CalculateDiscount(Price, 10) AS DiscountedPrice
FROM Products;
Example (Table-Valued UDF):


-- Creating a table-valued UDF
CREATE FUNCTION GetEmployeesByDepartment(@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

-- Using the table-valued UDF
SELECT * FROM GetEmployeesByDepartment(1);

41. Question: What is the purpose of the GROUP BY clause in SQL Server, and how can you use it?

Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on grouped data.

Example:

Example:

-- Using GROUP BY to calculate total sales per product
SELECT ProductID, SUM(Quantity * Price) AS TotalSales
FROM Sales
GROUP BY ProductID;

42. Question: Explain the purpose of the UNION and UNION ALL operators in SQL Server.

Answer: Both UNION and UNION ALL are used to combine the result sets of two or more SELECT statements into a single result set. The key difference is that UNION eliminates duplicate rows, while UNION ALL includes all rows, including duplicates.

Example:

-- Using UNION to combine distinct rows
SELECT EmployeeName FROM Department1
UNION
SELECT EmployeeName FROM Department2;

-- Using UNION ALL to combine all rows
SELECT EmployeeName FROM Department1
UNION ALL
SELECT EmployeeName FROM Department2;

43. Question: How can you update data in SQL Server, and what is the purpose of the UPDATE statement?

Answer: The UPDATE statement is used to modify existing data in a table. It allows you to change values in one or more columns based on specified conditions.

Example:

-- Updating employee salaries
UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 1;

44. Question: What is the purpose of the TRUNCATE TABLE statement in SQL Server, and how is it different from DELETE?

Answer: The TRUNCATE TABLE statement is used to remove all rows from a table quickly, but it does not log individual row deletions. It is a faster operation than DELETE, which removes rows one by one and logs each deletion. However, TRUNCATE TABLE cannot be used if the table is referenced by a foreign key constraint.

Example:

-- Truncating a table

TRUNCATE TABLE TableName;

These additional SQL Server interview questions and examples should further expand your knowledge and preparation for interviews.

45. Question: What is the purpose of the SQL Server EXISTS and NOT EXISTS operators, and how can you use them in subqueries?

Answer: The EXISTS operator is used to check if a subquery returns any rows, and NOT EXISTS checks if a subquery returns no rows. They are often used in correlated subqueries to filter results based on the existence of related data.

Example (EXISTS):

-- Checking if employees with a specific skill exist
SELECT EmployeeName
FROM Employees E
WHERE EXISTS (
    SELECT 1
    FROM Skills S
    WHERE E.EmployeeID = S.EmployeeID
    AND SkillName = 'SQL Server'
);
Example (NOT EXISTS):


-- Checking if employees with a specific skill do not exist
SELECT EmployeeName
FROM Employees E
WHERE NOT EXISTS (
    SELECT 1
    FROM Skills S
    WHERE E.EmployeeID = S.EmployeeID
    AND SkillName = 'Java'
);

46. Question: How can you calculate the difference between two dates or times in SQL Server, and what functions are available for this?

Answer: You can calculate the difference between two dates or times using functions like DATEDIFF() and DATEADD().

Example (DATEDIFF()):

-- Calculating the number of days between two dates
SELECT DATEDIFF(DAY, StartDate, EndDate) AS DaysDifference
FROM Tasks;
Example (DATEADD()):


-- Adding 30 days to a date
SELECT DATEADD(DAY, 30, StartDate) AS NewDate
FROM Orders;

47. Question: What is the purpose of the SQL Server TOP keyword, and how can you use it with the ORDER BY clause?

Answer: The TOP keyword is used to limit the number of rows returned by a query. When used with the ORDER BY clause, it specifies the number of rows to return based on a specified sorting order.

Example:

-- Retrieving the top 5 highest-paid employees
SELECT TOP 5 EmployeeName, Salary
FROM Employees
ORDER BY Salary DESC;

48. Question: How can you handle exceptions and errors in SQL Server, and what is the purpose of the TRY...CATCH block?

Answer: The TRY...CATCH block is used for error handling in SQL Server. It allows you to catch and handle errors that occur during the execution of a batch of SQL statements. If an error occurs within the TRY block, control is transferred to the CATCH block for error handling.

Example:

BEGIN TRY
    -- SQL statements that might cause an error
    INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, 'Customer1');
END TRY
BEGIN CATCH
    -- Error handling code
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

49. Question: What is the purpose of the SQL Server PATINDEX() function, and how can you use it to search for patterns in strings?

Answer: The PATINDEX() function is used to search for a specific pattern within a string and returns the starting position of the pattern if found. It is often used with wildcard characters.

Example:

-- Finding the position of "SQL" in a string
SELECT PATINDEX('%SQL%', 'SQL Server is great!') AS Position;
-- Result: 1 (SQL starts at the first position)

50. Question: What is the purpose of the SQL Server @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions, and how are they used to retrieve identity values?

Answer: These functions are used to retrieve identity (auto-increment) values after an INSERT operation:

@@IDENTITY returns the last identity value inserted in the current session.

SCOPE_IDENTITY() returns the last identity value inserted within the current scope.

IDENT_CURRENT('TableName') returns the last identity value inserted for a specific table.

Example:

-- Retrieving the last identity value inserted
INSERT INTO Customers (CustomerName) VALUES ('New Customer');
SELECT @@IDENTITY AS LastIdentity;