Transact-SQL (T-SQL) Interview Questions Answers

Home Computer Science Programming Technologies Transact-SQL (T-SQL)

Professional Transact-SQL (T-SQL) FAQ Questions and Answers

Welcome to our comprehensive Trivia Quiz and interview preparation guide. Below you will find a curated list of popular Trivia Questions and Answers specifically for Transact-SQL (T-SQL). Whether you are preparing for a technical interview or just testing your knowledge, these FAQ Questions will help you succeed.

What is T-SQL?

T-SQL (Transact-SQL) is an extension of SQL used in Microsoft SQL Server for procedural programming, error handling, and transaction control.

How is T-SQL different from standard SQL?

T-SQL includes control-of-flow, error handling, variables, stored procedures, functions, and transactions, which are not present in standard SQL.

What is the difference between CHAR and VARCHAR in T-SQL?

CHAR has a fixed length, while VARCHAR has a variable length and saves space.

How do you declare a variable in T-SQL?

DECLARE @variableName INT;

How do you assign a value to a variable in T-SQL?

SET @variableName = 100; or SELECT @variableName = column FROM Table;

What is a stored procedure in T-SQL?

A precompiled set of SQL statements that can be executed multiple times.

How do you create a stored procedure in T-SQL?

CREATE PROCEDURE ProcName AS BEGIN SELECT * FROM Table END;

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one and logs them, while TRUNCATE removes all rows instantly without logging.

What is a trigger in T-SQL?

A special procedure that automatically executes in response to an INSERT, UPDATE, or DELETE event on a table.

How do you create a trigger in T-SQL?

CREATE TRIGGER trgName ON Table AFTER INSERT AS BEGIN PRINT 'Insert Trigger Fired' END;

What are transactions in T-SQL?

A sequence of operations that are executed as a single unit (Atomicity, Consistency, Isolation, Durability - ACID).

How do you start a transaction in T-SQL?

BEGIN TRANSACTION;

How do you commit a transaction in T-SQL?

COMMIT TRANSACTION;

How do you roll back a transaction in T-SQL?

ROLLBACK TRANSACTION;

What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only matching records, while OUTER JOIN returns all records with NULLs for non-matching rows.

How do you use LEFT JOIN in T-SQL?

SELECT * FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID;

What is a CASE statement in T-SQL?

A conditional expression used to implement IF-ELSE logic inside SQL queries.

How do you use a CASE statement in T-SQL?

SELECT Name, CASE WHEN Age > 18 THEN 'Adult' ELSE 'Minor' END AS Category FROM Users;

What is COALESCE() in T-SQL?

A function that returns the first non-null value from a list.

How do you find duplicate records in a table?

SELECT Column, COUNT(*) FROM Table GROUP BY Column HAVING COUNT(*) > 1;

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

RANK() leaves gaps in ranking, DENSE_RANK() does not leave gaps, and ROW_NUMBER() provides unique numbers.

How do you find the second highest salary using T-SQL?

SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

What is a Common Table Expression (CTE) in T-SQL?

A temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE query.

How do you create a CTE in T-SQL?

WITH CTE AS (SELECT * FROM Table) SELECT * FROM CTE;

What is a temporary table in T-SQL?

A table stored in tempdb and used within a session (#TempTable).

What is a table variable in T-SQL?

A variable (@TableVariable) that stores a temporary result set like a table but exists only within a batch.

How do you create a temporary table in T-SQL?

CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));

How do you create a table variable in T-SQL?

DECLARE @TableVar TABLE (ID INT, Name VARCHAR(50));

What is the difference between HAVING and WHERE?

WHERE filters before aggregation, while HAVING filters after aggregation.

What is an index in T-SQL?

A structure that improves query performance by reducing scan time.

What are the types of indexes in T-SQL?

Clustered, Non-clustered, Unique, Full-text, Columnstore.

How do you create an index in T-SQL?

CREATE INDEX idx_name ON Table(Column);

What is a primary key in T-SQL?

A unique identifier for a table row (cannot contain NULL values).

What is a foreign key in T-SQL?

A field in a table that references a primary key in another table.

How do you add a primary key to a table?

ALTER TABLE Table ADD PRIMARY KEY (Column);

How do you add a foreign key in T-SQL?

ALTER TABLE ChildTable ADD FOREIGN KEY (Column) REFERENCES ParentTable(Column);

What is the IDENTITY property in T-SQL?

It generates auto-incrementing numbers for a column.

How do you insert data into an identity column?

INSERT INTO Table (Name) VALUES ('John'); (ID auto-increments).

How do you retrieve the last inserted identity value?

SELECT SCOPE_IDENTITY();

How do you update data in T-SQL?

UPDATE Table SET Column = Value WHERE Condition;

How do you delete a row in T-SQL?

DELETE FROM Table WHERE Condition;

How do you use EXISTS in T-SQL?

SELECT * FROM Table WHERE EXISTS (SELECT 1 FROM OtherTable WHERE ID = Table.ID);

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

To handle runtime errors inside stored procedures.

How do you implement TRY...CATCH in T-SQL?

BEGIN TRY -- SQL Code END TRY BEGIN CATCH -- Error Handling END CATCH;

How do you fetch the first 10 records in T-SQL?

SELECT TOP 10 * FROM Table;

How do you paginate results in T-SQL?

SELECT * FROM Table ORDER BY ID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

What is MERGE in T-SQL?

It allows INSERT, UPDATE, and DELETE in one statement based on condition.

How do you use MERGE in T-SQL?

MERGE INTO TargetTable USING SourceTable ON Condition WHEN MATCHED THEN UPDATE SET Column = Value WHEN NOT MATCHED THEN INSERT (Column) VALUES (Value);

How do you check SQL Server version using T-SQL?

SELECT @@VERSION;

What is DBCC CHECKDB used for?

It checks database integrity and consistency.