MS-SQL Server T-SQL Programming

Checkout Vskills Interview questions with answers in MS-SQL Server T-SQL Programming to prepare for your next job role. The questions are submitted by professionals to help you to prepare for the Interview.

Q.1 How do you use the MERGE statement to perform an "upsert" operation in T-SQL?

The MERGE statement combines INSERT, UPDATE, and DELETE operations based on a specified condition, making it useful for upsert operations.

Report This Question

Q.2 What is the purpose of the TRY...PARSE() function in T-SQL?

TRY...PARSE() attempts to convert a string to a specified data type using a culture-specific format, returning NULL if the conversion fails.

Report This Question

Q.3 Explain the purpose of the CONCAT_WS() function in T-SQL.

CONCAT_WS() concatenates multiple values into a single string with a specified delimiter.

Report This Question

Q.4 What is the purpose of the OPENJSON() function in T-SQL, and when would you use it?

OPENJSON() is used to parse JSON data and convert it into a tabular format for querying.

Report This Question

Q.5 How can you ensure data consistency in a distributed SQL Server environment?

You can use distributed transactions, two-phase commit, or distributed database technologies to maintain data consistency.

Report This Question

Q.6 Explain the difference between a left join and a right join with an example.

A left join returns all rows from the left table and matching rows from the right table, while a right join returns all rows from the right table and matching rows from the left table.

Report This Question

Q.7 How do you use the TRIGGER_NESTLEVEL() function in T-SQL?

TRIGGER_NESTLEVEL() returns the nesting level of the current trigger execution.

Report This Question

Q.8 What is the purpose of the SOUNDEX() function in T-SQL?

SOUNDEX() converts a string into a phonetic code, which can be used to find similar-sounding words.

Report This Question

Q.9 Explain the concept of the OFFSET and FETCH clauses in T-SQL, and provide an example.

OFFSET skips a specified number of rows, and FETCH limits the number of rows returned, useful for implementing pagination.

Report This Question

Q.10 What is the purpose of the CONVERT() function in T-SQL, and how can you use it to change data types?

CONVERT() is used to explicitly convert data from one data type to another, ensuring data type compatibility.

Report This Question

Q.11 How do you create and manage user-defined data types (UDTs) in SQL Server?

You can create UDTs using the CREATE TYPE statement and manage them using ALTER TYPE or DROP TYPE statements.

Report This Question

Q.12 Explain the purpose of the CONTEXT_INFO system function in T-SQL.

CONTEXT_INFO allows you to store binary information that can be accessed across multiple batches or sessions, often used for auditing or context preservation.

Report This Question

Q.13 What is the purpose of the SEQUENCE object's CYCLE property in SQL Server?

The CYCLE property, when set to ON, allows the sequence to restart from its minimum or maximum value when exhausted, instead of throwing an error.

Report This Question

Q.14 How do you use the PARSENAME() function in T-SQL, and what is its purpose?

PARSENAME() extracts parts of a four-part object name, such as database, schema, object, and column names, based on the specified part number.

Report This Question

Q.15 How can you retrieve the current user's login name in T-SQL?

You can use the SUSER_SNAME() function to retrieve the current user's login name.

Report This Question

Q.16 Explain the purpose of the SET ROWCOUNT statement in T-SQL, and when should you use it?

SET ROWCOUNT limits the number of rows affected by a query. It should be used cautiously and is typically not recommended for modern query tuning.

Report This Question

Q.17 What is the purpose of the TIME data type in SQL Server, and how is it different from DATETIME?

TIME stores time-of-day values with precision, while DATETIME stores both date and time values.

Report This Question

Q.18 How do you create and use temporary tables in T-SQL, and what are their benefits?

Temporary tables are created using CREATE TABLE #temp and are used to store intermediate results. They provide better performance than table variables for large datasets.

Report This Question

Q.19 What is the purpose of the TRIM() function in T-SQL?

TRIM() removes leading and trailing spaces from a string, improving data consistency.

Report This Question

Q.20 How can you calculate the difference between two dates in T-SQL, including years, months, and days?

You can use a combination of date functions such as DATEDIFF() and DATEPART() to calculate differences in years, months, and days.

Report This Question

Q.21 Explain the concept of a correlated subquery with an example.

A correlated subquery references columns from the outer query, often used to filter results based on values from the outer query. For example, finding employees with salaries higher than the average salary in their department.

Report This Question

Q.22 What is the purpose of the QUOTENAME() function in T-SQL?

QUOTENAME() is used to correctly quote or escape identifiers, such as table or column names, to prevent SQL injection.

Report This Question

Q.23 How do you use the FOR JSON clause in T-SQL to format query results as JSON?

The FOR JSON clause allows you to format query results as JSON objects or arrays, making it useful for web applications and APIs.

Report This Question

Q.24 What is a windowed aggregate function in T-SQL, and how is it different from a regular aggregate function?

A windowed aggregate function performs calculations across a set of rows related to the current row, while a regular aggregate function operates on the entire result set.

Report This Question

Q.25 Explain the concept of a recursive trigger in T-SQL.

A recursive trigger is a trigger that fires itself in response to changes made by its own execution. They should be avoided to prevent infinite loops.

Report This Question

Q.26 What is the purpose of the FORMATMESSAGE() function in T-SQL?

FORMATMESSAGE() is used to format messages with placeholders, allowing you to build custom error messages or informational messages.

Report This Question

Q.27 How do you use the CROSS APPLY and OUTER APPLY operators in T-SQL, and what are their differences?

CROSS APPLY and OUTER APPLY are used to apply a table-valued function to each row of a table. CROSS APPLY returns only matching rows, while OUTER APPLY returns all rows from the left table.

Report This Question

Q.28 Explain the concept of a filtered index in SQL Server, and when would you use it?

A filtered index is an index that includes only a subset of rows based on a filter condition. It is useful when you want to index a specific subset of data to improve query performance.

Report This Question

Q.29 What is the purpose of the PARSE() function in T-SQL, and how is it different from CONVERT() and TRY_CAST()?

PARSE() converts a string to a specified data type using a culture-specific format, similar to CONVERT() and TRY_CAST(), but with more flexibility in formatting.

Report This Question

Q.30 Explain the purpose of the HASHBYTES() function in T-SQL.

HASHBYTES() computes a hash value for a given input, which can be useful for data integrity checks or hashing passwords.

Report This Question

Q.31 How can you determine the version and edition of SQL Server that you are connected to?

You can use the SELECT @@VERSION statement to retrieve version information.

Report This Question

Q.32 What is the purpose of the DATEFROMPARTS() function in T-SQL?

DATEFROMPARTS() constructs a date from separate year, month, and day components.

Report This Question

Q.33 How do you handle errors and exceptions in a SQL Server stored procedure?

You can use the TRY...CATCH block to catch and handle errors within a stored procedure.

Report This Question

Q.34 Explain the concept of dynamic SQL in T-SQL, and provide an example.

Dynamic SQL allows you to build and execute SQL statements dynamically at runtime. For example, constructing and executing a query based on user inputs.

Report This Question

Q.35 How do you concatenate columns from multiple rows into a single string in T-SQL?

You can use the FOR XML PATH or STRING_AGG() method to concatenate values from multiple rows into a single string.

Report This Question

Q.36 What is the purpose of the DATEPART() function in T-SQL, and how is it different from DAY(), MONTH(), and YEAR() functions?

DATEPART() extracts a specific part of a date or time value, such as day, month, or year. It provides more flexibility than the specific functions like DAY(), MONTH(), and YEAR().

Report This Question

Q.37 Explain the purpose of the TRY...CONVERT() function in T-SQL.

TRY...CONVERT() attempts to convert a value to a specified data type and returns NULL if the conversion fails, similar to TRY_CAST().

Report This Question

Q.38 How can you create and use table-valued constructors in T-SQL?

Table-valued constructors allow you to create a table using a constructor-like syntax. For example, VALUES (1, 'Alice'), (2, 'Bob').

Report This Question

Q.39 What is the purpose of the SEQUENCE object's INCREMENT BY property in SQL Server?

The INCREMENT BY property sets the amount by which the sequence value is incremented or decremented.

Report This Question

Q.40 Explain the concept of a sparse column in SQL Server, and when would you use it?

A sparse column is used to efficiently store NULL values in a table when most of the values are NULL. It reduces storage requirements and improves query performance.

Report This Question

Q.41 How can you prevent and handle deadlocks in SQL Server?

Deadlocks can be prevented by using appropriate indexes, minimizing transaction duration, and using isolation levels. Handling deadlocks can be done by monitoring and using deadlock detection and resolution techniques.

Report This Question

Q.42 What is the purpose of the TRANSACTION statement in T-SQL, and how do you use it to control transactions?

The TRANSACTION statement is used to start, commit, or rollback transactions explicitly in T-SQL code.

Report This Question

Q.43 Explain the concept of a recursive common table expression (CTE) with an example.

A recursive CTE allows you to query hierarchical data structures by repeatedly referencing the CTE within itself. For example, querying an organizational hierarchy.

Report This Question

Q.44 How do you handle concurrency issues in SQL Server, such as preventing two users from updating the same record simultaneously?

Concurrency issues can be handled using mechanisms like locking, optimistic concurrency control, and versioning.

Report This Question

Q.45 What is the purpose of the IIF() function in T-SQL, and how does it differ from the CASE expression?

IIF() is a shorthand way to write a simple CASE expression and returns one of two values based on a specified condition.

Report This Question

Q.46 Explain the concept of a partitioned table in SQL Server, and what are the benefits of partitioning?

A partitioned table is divided into smaller, manageable partitions. It improves performance for large tables, facilitates data maintenance, and enables efficient data archiving and purging.

Report This Question

Q.47 How can you retrieve the identity value generated by an INSERT statement in T-SQL?

You can use the SCOPE_IDENTITY(), @@IDENTITY, or IDENT_CURRENT() functions to retrieve the identity value after an INSERT operation.

Report This Question

Q.48 Explain the concept of dynamic pivot tables in T-SQL, and provide an example.

Dynamic pivot tables allow you to pivot data when you don't know the column names beforehand. You construct the pivot query dynamically at runtime.

Report This Question

Q.49 How can you use the FILESTREAM data type to store and manage binary data in SQL Server?

FILESTREAM allows you to store binary data, such as images or documents, outside of the database while still allowing SQL operations.

Report This Question

Q.50 Explain the purpose of the FOR JSON AUTO and FOR JSON PATH options in T-SQL.

These options are used to format query results as JSON objects or arrays when retrieving data.

Report This Question

Q.51 How do you use the WITH ENCRYPTION option when creating a stored procedure in SQL Server?

WITH ENCRYPTION encrypts the text of a stored procedure, making it harder to view the code. It is primarily used for security purposes.

Report This Question

Q.52 Explain the purpose of the STUFF() function in T-SQL.

STUFF() is used to replace a portion of a string with another string at a specified starting position.

Report This Question

Q.53 What is T-SQL?

T-SQL (Transact-SQL) is Microsoft's proprietary extension of SQL used for managing and querying SQL Server databases.

Report This Question

Q.54 What is the basic syntax of a SELECT statement in T-SQL?

SELECT column1, column2 FROM table_name WHERE condition;

Report This Question

Q.55 What is normalization and why is it important in database design?

Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity by dividing tables into smaller, related tables.

Report This Question

Q.56 What is a stored procedure?

A stored procedure is a precompiled set of one or more SQL statements that can be executed on demand.

Report This Question

Q.57 What is a user-defined function (UDF) in T-SQL?

A UDF is a reusable piece of T-SQL code that can be used to perform specific tasks and return a value.

Report This Question

Q.58 Explain the differences between a primary key and a unique key constraint.

A primary key enforces uniqueness and ensures that no NULL values are allowed, while a unique key constraint enforces uniqueness but allows for NULL values.

Report This Question

Q.59 What is the difference between clustered and non-clustered indexes?

A clustered index determines the physical order of data in a table, while a non-clustered index provides a logical ordering of data.

Report This Question

Q.60 How can you add a new column to an existing table in T-SQL?

Use the ALTER TABLE statement with the ADD keyword.

Report This Question

Q.61 Explain the ACID properties in the context of database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are essential properties to ensure the reliability of database transactions.

Report This Question

Q.62 What is a SQL injection, and how can it be prevented in T-SQL?

SQL injection is a type of security vulnerability where malicious SQL code is inserted into user inputs. It can be prevented by using parameterized queries or stored procedures.

Report This Question

Q.63 What is the purpose of the HAVING clause in SQL?

The HAVING clause is used to filter the results of a GROUP BY query based on aggregate functions.

Report This Question

Q.64 How can you concatenate strings in T-SQL?

You can use the + operator or the CONCAT() function to concatenate strings.

Report This Question

Q.65 What is a common table expression (CTE), and how is it different from a subquery?

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It's similar to a subquery but more readable and reusable.

Report This Question

Q.66 What is the purpose of the UNION and UNION ALL operators in T-SQL?

UNION combines the results of two or more SELECT statements and removes duplicates, while UNION ALL includes duplicates.

Report This Question

Q.67 What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows from both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table.

Report This Question

Q.68 Explain the concept of transactions in SQL Server.

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work, ensuring data consistency and integrity.

Report This Question

Q.69 What is the purpose of the ROW_NUMBER() function in T-SQL?

ROW_NUMBER() assigns a unique integer to each row in the result set, based on the specified order.

Report This Question

Q.70 How can you update data in a table using T-SQL?

Use the UPDATE statement with the SET clause to modify data in a table.

Report This Question

Q.71 What is the purpose of the MERGE statement in T-SQL?

The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement for handling data synchronization.

Report This Question

Q.72 How do you handle exceptions in T-SQL?

You can use TRY...CATCH blocks to handle exceptions and errors in T-SQL code.

Report This Question

Q.73 Explain the difference between a table variable and a temporary table in T-SQL.

Table variables are in-memory structures with a limited scope, while temporary tables are physical tables stored in the tempdb database.

Report This Question

Q.74 What is a trigger in SQL Server, and when would you use one?

A trigger is a database object that automatically executes in response to a specific event, such as an INSERT, UPDATE, or DELETE operation.

Report This Question

Q.75 What is the purpose of the FOR XML clause in T-SQL?

FOR XML is used to retrieve query results as XML data.

Report This Question

Q.76 Explain the concept of indexing and its impact on query performance.

Indexing is a database optimization technique that speeds up data retrieval by providing quick access to rows in a table. It can significantly improve query performance.

Report This Question

Q.77 What is the purpose of the PIVOT and UNPIVOT operators in T-SQL?

PIVOT is used to transform rows into columns, and UNPIVOT is used to transform columns into rows.

Report This Question

Q.78 What are correlated subqueries, and when would you use them?

Correlated subqueries are subqueries that reference columns from the outer query. They are used when you need to filter results based on values from the outer query.

Report This Question

Q.79 Explain the difference between @@IDENTITY and SCOPE_IDENTITY() in T-SQL.

@@IDENTITY returns the last identity value inserted across all sessions, while SCOPE_IDENTITY() returns the last identity value inserted within the current scope or session.

Report This Question

Q.80 What is a deadlock in SQL Server, and how can you prevent it?

A deadlock occurs when two or more processes block each other's resources. You can prevent deadlocks by using proper indexing, minimizing transaction duration, and using isolation levels.

Report This Question

Q.81 What is the purpose of the TOP keyword in T-SQL?

TOP is used to limit the number of rows returned in a result set.

Report This Question

Q.82 Explain the purpose of the RANK(), DENSE_RANK(), and NTILE() window functions.

RANK() assigns a unique rank to each row in a result set based on specified criteria. DENSE_RANK() is similar but doesn't leave gaps in rank values. NTILE() divides rows into specified quantiles.

Report This Question

Q.83 What is the difference between a primary key and a foreign key constraint?

A primary key enforces uniqueness and ensures data integrity within a table, while a foreign key establishes a relationship between two tables, enforcing referential integrity.

Report This Question

Q.84 Explain the concept of a self-join in SQL.

A self-join is when a table is joined with itself. It's useful when you have hierarchical data stored within a single table.

Report This Question

Q.85 What is an execution plan, and how can you view it in SQL Server?

An execution plan is a detailed explanation of how SQL Server intends to execute a query. You can view it using the "Display Estimated Execution Plan" or "Include Actual Execution Plan" options in SQL Server Management Studio.

Report This Question

Q.86 What is the purpose of the NOLOCK hint in T-SQL?

The NOLOCK hint allows for reading uncommitted data in a SELECT statement, potentially improving query performance but risking data inconsistency.

Report This Question

Q.87 What is the INDEX hint, and when would you use it?

The INDEX hint allows you to specify which index to use in a query. It's used to override the query optimizer's choice in specific situations.

Report This Question

Q.88 How do you retrieve the current date and time in T-SQL?

You can use the GETDATE() function or SYSDATETIME() function to retrieve the current date and time.

Report This Question

Q.89 What is the purpose of the ISNULL() function in T-SQL?

The ISNULL() function is used to replace NULL values with a specified value.

Report This Question

Q.90 Explain the difference between a scalar function and a table-valued function in T-SQL.

A scalar function returns a single value, while a table-valued function returns a table as its output.

Report This Question

Q.91 What is the purpose of the CHECK constraint in T-SQL?

A CHECK constraint enforces data integrity by limiting the values that can be placed in a column.

Report This Question

Q.92 What is the purpose of the SET NOCOUNT ON statement in T-SQL?

SET NOCOUNT ON suppresses the message that shows the number of rows affected by a T-SQL statement, improving performance for stored procedures.

Report This Question

Q.93 Explain the concept of normalization forms and provide an example.

Normalization forms are a set of rules to organize data efficiently. For example, first normal form (1NF) ensures that each column in a table contains atomic values, and there are no repeating groups.

Report This Question

Q.94 What is a common use case for the COALESCE() function in T-SQL?

COALESCE() is used to return the first non-NULL value from a list of expressions.

Report This Question

Q.95 What are triggers in T-SQL, and when should you avoid using them?

Triggers are database objects that automatically execute in response to specific events. They should be used sparingly due to their potential impact on performance and complexity.

Report This Question

Q.96 How can you retrieve the nth highest (or lowest) value from a table in T-SQL?

You can use a combination of ORDER BY and TOP or the ROW_NUMBER() function.

Report This Question

Q.97 What is the purpose of the TRY_PARSE() function in T-SQL?

TRY_PARSE() attempts to convert a string to a specified data type and returns NULL if the conversion fails.

Report This Question

Q.98 Explain the purpose of the PIVOT and UNPIVOT operators with an example.

PIVOT transforms rows into columns, while UNPIVOT transforms columns into rows. For example, you can pivot sales data by month or unpivot a result set to get a normalized view.

Report This Question

Q.99 What is the difference between an inner join and a cross join in T-SQL?

An inner join returns only matching rows, while a cross join combines every row from one table with every row from another, resulting in a Cartesian product.

Report This Question

Q.100 What is the purpose of the SET TRANSACTION ISOLATION LEVEL statement in T-SQL?

It sets the isolation level for a transaction, controlling how data is locked and shared between transactions.

Report This Question

Q.101 How do you retrieve the last N rows from a table in T-SQL?

You can use ORDER BY with TOP or the OFFSET FETCH clause (SQL Server 2012 onwards).

Report This Question

Q.102 What is the purpose of the PARSE() function in T-SQL?

PARSE() converts a string to a specified data type, and it raises an error if the conversion fails.

Report This Question

Q.103 Explain the purpose of the SET ANSI_NULLS and SET QUOTED_IDENTIFIER options in T-SQL.

SET ANSI_NULLS controls how NULL values are compared, while SET QUOTED_IDENTIFIER enforces the use of double quotes for identifiers.

Report This Question

Q.104 What are correlated joins, and when would you use them?

Correlated joins are subqueries in a join condition that reference columns from the outer query. They are used to filter results based on values from the outer query.

Report This Question

Q.105 What is the purpose of the TRY...CATCH block in T-SQL?

TRY...CATCH is used for error handling, allowing you to gracefully handle exceptions and errors in your code.

Report This Question

Q.106 How can you remove duplicates from a result set in T-SQL?

You can use the DISTINCT keyword or the GROUP BY clause with aggregation functions to remove duplicates.

Report This Question

Q.107 What is the purpose of the DATEPART() function in T-SQL?

DATEPART() extracts a specific part (e.g., year, month, day) from a date or time value.

Report This Question

Q.108 Explain the concept of a recursive common table expression (CTE) in T-SQL.

A recursive CTE allows you to query hierarchical data structures, such as organizational charts or bill of materials, by repeatedly referencing the CTE within itself.

Report This Question

Q.109 How do you handle large result sets in T-SQL to improve performance?

You can use pagination with OFFSET FETCH or ROW_NUMBER() to retrieve data in smaller chunks.

Report This Question

Q.110 What is the purpose of the RAISEERROR() function in T-SQL?

RAISEERROR() is used to generate custom error messages in T-SQL code.

Report This Question

Q.111 Explain the difference between the TRY_CONVERT() and TRY_CAST() functions in T-SQL.

TRY_CONVERT() attempts to convert a value to a specified data type and returns NULL if it fails, while TRY_CAST() is used for the same purpose but with a more specific data type.

Report This Question

Q.112 How can you import data from an external file into a SQL Server table?

You can use the BULK INSERT statement or the OPENROWSET function to import data from external files.

Report This Question

Q.113 What is the purpose of the IIF() function in T-SQL?

IIF() is a shorthand way to write a simple CASE expression and returns one of two values based on a specified condition.

Report This Question

Q.114 Explain the purpose of the FORMAT() function in T-SQL.

FORMAT() is used to format date and time values as strings using a specified format.

Report This Question

Q.115 How do you pivot data dynamically in T-SQL when you don't know the column names beforehand?

You can use dynamic SQL to pivot data dynamically by constructing the SQL statement at runtime.

Report This Question

Q.116 What is the purpose of the EXISTS operator in T-SQL?

The EXISTS operator checks for the existence of rows returned by a subquery and returns TRUE if the subquery returns any rows.

Report This Question

Q.117 Explain the difference between the UNION and UNION ALL operators with respect to performance.

UNION removes duplicate rows from the result set, which can impact performance, while UNION ALL includes all rows, including duplicates.

Report This Question

Q.118 What is the purpose of the TRY...CONVERT() function in T-SQL?

TRY...CONVERT() attempts to convert a value to a specified data type and returns NULL if it fails, similar to TRY_CONVERT().

Report This Question

Q.119 What is the purpose of the SEQUENCE object in SQL Server, and how do you create one?

A SEQUENCE object generates a sequence of numbers. You can create one using the CREATE SEQUENCE statement.

Report This Question

Q.120 Explain the purpose of the CHOOSE() function in T-SQL.

CHOOSE() returns an item from a list based on a specified index.

Report This Question

Q.121 What is a table variable and when would you use it over a temporary table or a regular table?

A table variable is a variable that holds a table-like structure. You might use it when you need to store a small amount of data temporarily, and you don't want to create a permanent table.

Report This Question

Q.122 How do you handle date and time calculations in T-SQL?

You can use various date and time functions such as DATEADD(), DATEDIFF(), and GETDATE() to perform calculations.

Report This Question

Q.123 Explain the concept of window functions in T-SQL, and provide an example.

Window functions perform calculations across a set of table rows that are related to the current row. An example is calculating a running total or a moving average.

Report This Question

Q.124 What is a subquery, and how does it differ from a join in T-SQL?

A subquery is a query nested within another query, while a join combines rows from two or more tables based on a related column.

Report This Question

Q.125 What is a parameterized query, and why is it important for security in T-SQL?

A parameterized query uses placeholders for values and ensures that user input is sanitized, reducing the risk of SQL injection attacks.

Report This Question

Q.126 Explain the purpose of the FILESTREAM data type in SQL Server.

FILESTREAM is used to store and manage large binary data (e.g., images, documents) outside of the database, while still allowing for SQL operations.

Report This Question

Q.127 How do you handle null values in T-SQL when performing arithmetic operations?

You can use the ISNULL(), COALESCE(), or NULLIF() functions to handle null values during arithmetic operations.

Report This Question

Q.128 What is the purpose of the OFFSET FETCH clause in T-SQL, and when is it used?

OFFSET FETCH is used for pagination and allows you to skip a specified number of rows and return a limited number of rows from a result set.

Report This Question

Q.129 How can you create and manage transactions in T-SQL code?

You can use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements to create and manage transactions.

Report This Question

Q.130 Explain the difference between a clustered index and a non-clustered index with respect to storage.

A clustered index determines the physical order of data in a table, whereas a non-clustered index is a separate structure that stores a copy of the indexed columns and a pointer to the actual data rows.

Report This Question

Q.131 What is a full-text index in SQL Server, and when would you use it?

A full-text index is used to improve the performance of text-based searches. It's particularly useful when searching large amounts of textual data.

Report This Question

Q.132 How can you optimize T-SQL queries for performance?

You can optimize queries by using appropriate indexes, avoiding functions in WHERE clauses, minimizing the use of wildcards in LIKE statements, and using query execution plans.

Report This Question

Q.133 Explain the purpose of the TRY...PARSE() function in T-SQL.

TRY...PARSE() attempts to convert a string to a specified data type using a culture-specific format, returning NULL if the conversion fails.

Report This Question

Q.134 What is the purpose of the SEQUENCE object's CACHE property in SQL Server?

The CACHE property specifies the number of sequence values to preallocate and store in memory for better performance.

Report This Question

Q.135 How can you implement error handling with the THROW statement in T-SQL?

The THROW statement is used to raise a custom error message and propagate it to the calling program.

Report This Question

Q.136 What is the purpose of the STRING_SPLIT() function in T-SQL?

STRING_SPLIT() splits a string into rows based on a specified delimiter, returning the result as a table.

Report This Question

Q.137 Explain the concept of table-valued parameters in T-SQL.

Table-valued parameters allow you to pass a table as a parameter to a stored procedure or function, enabling efficient bulk data operations.

Report This Question