T-SQL Interview Questions

Payment control, and exception error handling, row processing, and declared variables are just a few of the features that T-SQL (Transact-SQL), a set of coding extensions from Sybase and Microsoft, adds to SQL. T-SQL statements are the universal language for all applications that interact with SQL Server. T-SQL queries can modify a search condition, restrict rows, select columns, label output columns, and use the SELECT statement. In contrast, all databases, servers, and database objects in SQL Server use T-SQL identifiers. Tables,  stored constraints, views, procedures, columns, and data types are a few examples. T-SQL identifiers are delegated whenever an object is created, must all have distinct names, and are employed to identify objects.

Evidently, the demand for T SQL professionals is quite high and so is the competition. We assure you that once you go through this blog, you will get an idea of how to go about the interview. For making the entire process a whole lot easier, we have divided the questions into three categories. They are:

• For Freshers
• For Experienced
• For FAQ's

Top 10 T-SQL Questions

1. What is T-SQL?
2. How do T-SQL Window Functions Work?
3. What is the use of Indexes?
4. Describe the T-SQL terms ROLLUP and CUBE
5. What are the Join Types in SQL?
6. What are some SQL Applications?
7. Define a DEFAULT Constraint?
8. What Distinguishes a Primary Key from a Unique Key?
9. What Applications does T-SQL have?
10. What Kinds of XML Indexes does SQL Server Support?

Top T-SQL Interview Questions for Freshers

1. A Query Language for working with sets is SQL.

• Microsoft uses the proprietary procedural language TSQL in SQL Server.
• Numerous features not found in SQL are added by T-SQL.

This gives you more flexible control over how the application behaves by incorporating procedural programming components and a local variable. T-SQL also received a number of functions that increased its functionality, including functions for string manipulation, date and time processing, and mathematical operations. These modifications enable T-SQL to pass the Turing completeness test, which assesses a computer language's universality. SQL's capabilities are severely constrained and it is not Turing complete.

2. Mention the IDENTITY Column's Restrictions.

The IDENTITY column has the drawback that once generated, column values could be changed. Additionally, it might be necessary to designate this column as a PRIMARY KEY; as a result, there is a chance that some values in a table will duplicate. Only columns with an integer base are eligible for the identity property.

If you want to enrich your career and become a Professional in MSBI, then enroll in "MSBI Online Training" course. This course will help you to achieve excellence in this domain.

3. Describe the T-SQL OFFSET-FETCH Filter.

In SQL, the OFFSET-FETCH filter has an additional element in addition to the TOP element. Before defining however many rows you want to filter, it can be helpful to specify how several rows you want to skip.

4. What are the three possible ways to issue Dynamic SQL?

• Creating a parameterized query.
• EXEC is used.
• sp_executesql is used.

5. What are the two commands for clearing a table of all data? Do the specific commands have any ramifications?

• TRUNCATE purges a table of all rows. No triggers will be shot and the operation can indeed be undone. As a result, TRUNCATE is quicker and requires less undo space than DELETE.
• Rows can be deleted from a table using the DELETE command. Only some rows can be deleted using a WHERE clause. All rows will be deleted if there is no WHERE condition specified. To make a change permanent or undo it after conducting a DELETE operation, you must COMMIT or ROLLBACK the transaction. Keep in mind that this operation will set off every DELETE trigger on the table.

6. How do T-SQL Window Functions Work?

In order to return a single value for every row from the underlying query, a window function must be applied to a group of rows defined by a window descriptor. The window descriptor's function is to specify the set of rows to which the function should be applied. Use the OVER clause to provide the window specification.

7. When should I employ an Index or a Primary Key?

A primary key is essentially (at the implementation level) a unique category of index. Specifically:

• With only a few exceptions, a table can only have one primary key, and every table ought to have one.
• Since a primary key's function is to identify individual rows, it is implicitly UNIQUE and cannot be used in more than one row.
• The row(s) a primary key consists of must not be NULL because a primary key can never be NULL.

[ Learn Complete T-SQL Tutorial ]

8. What is the use of Indexes?

Multiple indexes may be present in a table, and uniqueness is not a requirement for indexes. Indexes are used for two purposes:

• To make a uniqueness constraint mandatory, these can be created implicitly when you declare a column UNIQUE.
• To boost efficiency. On columns with an index, analogies for equality or "greater/less than" in WHERE clauses and JOINs execute much more quickly. However, keep in mind that each index reduces update/insert/delete performance; as a result, you should only use them where they are actually required.

9. When a column has a data type of Text, NText, or Image, how can you measure its length?

• To record the length, use the DATALENGTH command.
• For the data types Text, NText, and Image, the LEN command is invalid.

Use the LEN() function to only count characters other than blanks; otherwise, use the DATALENGTH function (). Even the LEN() documentation advises using DATALENGTH to determine the number of bytes needed to represent an extension ().

10. Is there a distinction between a synonym and a T-SQL-linked server?

To attach to a database on another server, you use a linked server.

Similar to an alias, a synonym is used to specify an object (like a table) in SQL.

You still need the linked server even if you allow a synonym to point to one of its objects.

Related Article: Normalization and T-SQL in SQL Server

11. Describe the T-SQL terms ROLLUP and CUBE.

The clustering sets used in conjunction with the GROUP BY clause to produce summarized aggregations are ROLLUP and CUBE. These are primarily employed in report generation and data audits.

12. What are the Join Types in SQL?

The TSQL join types are,

• Inside join
• External join
• Outer joins on the left and right
• Outer join on the left with exclusions
• Outside right join with Exclusions
• Entire outer join
• Exclusions and full outer joins
• Crossing over

13. Describe the function of the T-SQL command IDENT CURRENT.

The last identification value generated for the stipulated table or view is returned by the SQL command IDENT_CURRENT. Any meeting and any scope may contain the most recent identity value created.

14. Describe what uncommitted transactions are.

If a transaction within a TRY block encounters an error, the transaction becomes open and uncommittable if the error is not severe. The transactions are unable to take any actions that would result in a write to the transaction log while they are in an uncommittable state.

15. What are Bitwise Operators, and why are they Important in terms of Database Design?

In SQL Server, the following bitwise operators are available: 

& (Bitwise AND), 

~(Bitwise NOT), 

| (Bitwise OR). (

^Excluding Bitwise OR)

Instead of using multiple lookup tables or multiple columns as a "flag" or condition indicator, bitwise technicians can be employed to store a complicated set of conditions as a single value from the perspective of database design.

Top T-SQL Interview Questions For Experienced

1. What are some SQL Applications?

The principal uses of SQL include:

• Writing scripts for data integration
• Establishing and executing analytical queries
• Retrieving specific data from a database to be used by analytics software and transaction processing
• A database's ability to add, update, and remove rows and columns of data

2. Define a DEFAULT Constraint?

SQL constraints are used to define guidelines for data processing and to restrict the kinds of data which can be entered into tables. Let's now examine what a default constraint is.

When no other value is specified, a default limitation is used to specify a default value for a column, which is added to all new records. In the following table, for instance, if we assign the E salary column a default constraint and set the null value to 85000, then all entries in this column will have that value by default unless another value was assigned during the insertion.

3. What are the Differences Between COALESCE() and ISNULL(,'')?

• COALESCE() - It evaluates the arguments in a specific order and returns the current value of the first expression that initially does not evaluate to NULL.
• ISNULL() - Replaces NULL with the specified replacement value.

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently:

• COALESCE() is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.
• COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null such as COALESCE(Col1, Col2, Col3, 'N/A'). It's recommended to use this by MS instead of ISNULL()
• ISNULL() can only have one input, however, it's been shown to be slightly faster than COALESCE.

4. What do the SQL terms "table" and "field" mean?

A table is defined as a collection of rows and columns of organized data. Simply put, it is a table-formatted collection of related data. The amount of columns in a table is made reference to as a field, and rows and columns are made referenced to as tuples and attributes in this context. Fields represent the traits and attributes in the record and hold detailed data about it.

5. Describe the Primary Key?

All table records are uniquely identified by a primary key. It must have distinct values and cannot have NULL values. In a table, there can only be one primary key, which is a composite key that can have one or more fields.

6. What is a Special Key?

A unique key is a key that can only accept a null value and therefore can accept two identical values. A unique key's responsibility is to guarantee the uniqueness of each column and each row. A unique key will have the same syntax as the primary key.

7. What Distinguishes a Primary Key from a Unique Key?

All primary and unique keys can have distinct values, but only unique keys can have a null value for a primary key. There can only be one primary key per table, but there may be many unique keys.

8. What is a Foreign Key?

An attribute or set of attributes, that refers to the primary key of another table is known as a foreign key. A foreign key is essentially used to connect two tables.

9. What Subsets of SQL are there?

The principal SQL subsets are:

• Language for Data Definition (DDL)
• Language for Data Manipulation (DML)
• Control Language for Data (DCL)
• Language for Transaction Control (TCL)

10. What Applications does T-SQL have?

Using a T SQL database, the following operations can be carried out:

• Making fresh databases
• Adding fresh data and erasing old data
• Updating data retrieving records
• Adding and removing tables
• Creating views and functions

Related Article: Using T-SQL With SSIS

11. What is an Index?

Indexes facilitate quicker database searching. The SQL Server must scan the entire table and check every single row to find matches if a column in the WHERE stipulation has no index. This may slow down operations in large data sets. Indexes are used to locate all rows that match a specific set of columns, which allows users to quickly search through only some of these subsets of the data for matches.

12. What are Relationships and Entities?

Entities: A database can contain information about any identifiable person, place, thing, or other objects. Staff members, projects, salaries, etc., can all be referred to as entities in a company's database, for instance.

Relationships: A link between two tables or entities can be used to describe a relationship between entities. The student organization and the department entity, for instance, are related to a college database.

13. What is a Data Warehouse?

Within an organization, a data warehouse is a sizable collection of accumulated data from numerous sources. Data is used to inform business decisions.

14. Why does SQL Server utilize the FLOOR Function?

The largest integer value for a given number—which may be equal to or less than the given number—can be found with the aid of the FLOOR() function.

15. What Kinds of XML Indexes does SQL Server Support?

Different XML index types are supported by Microsoft SQL Server. A relational index is distinct from an XML index. XML indexes can be broadly divided into TWO categories:

• Primary and secondary XML indexes are available.
• Users cannot use the Primary XML index directly from their T-SQL statements because it is a grouped index on an internal table called the node table. We produce secondary XML indexes to improve search performance. These produce secondary links (RID) at the leaf level for already existing KEY pages based on clustered indexes. It is recommended to create a primary XML index first before moving on to secondary XML indexes.

Frequently Asked T-SQL Interview Questions

1. What is T-SQL?

Transact Structured Query Language is referred to as T-SQL. It is an expansion of the SQL functionality that Sybase ASE and Microsoft SQL Server support.

2. Describe the Distinction between SQL and T-SQL.

In contrast to TSQL, which is a patented technology procedural language in use by MS SQL Server, SQL is a query language to functions on sets. Additionally, T-SQL implements DELETE and UPDATE differently from SQL.

Related Article: SQL Interview Questions

3. Describe the Process of Writing and Submitting T-SQL Statements to the Database Engine.

The following methods exist for writing and submitting T-SQL statements to the database engine:

• With the help of the sqlcmd Utility
• Through the use of SQL Server Management Studio
• By trying to connect from a program you develop

4. What does "GO" in T-SQL Stand for?

"GO" is a batch separator rather than a Transact-SQL statement. The sqlcmd, osql, and SQL Server Management Studio Code editors all recognize it as a command. "GO'' instructs SQL Server utilities to send the latest batch of SQL declarations to a SQL Server instance.

5. Explain the Distinction between the TRUNCATE and DELETE statements.

• The TRUNCATE statement differs from the DELETE statement in that,
• For the complete deletion of data records from Tables, use TRUNCATE. Operations to truncate are not recorded.
• For the contingent removal of data records from Tables, use DELETE. These actions are recorded.

6. Mention the T-SQL Syntax for Defining a Local Variable.

In SQL, a local variable is created by using the "DECLARE" statement, and its name must contain the "@" symbol as the first character. For instance, we will define a local variable as an integer CNT. ANNOUNCE @CNT INT

7. What is the Purpose of the T-SQL command IDENT CURRENT?

The last identification value generated for the specified table or view is returned by the SQL command IDENT_CURRENT. Any meeting and any scope may contain the most recent identity value created.

8. Describe the Purpose of the T-SQL command IDENT INCR.

In a table or view with an identity column, the TSQL command IDENT INCR returns the increment value specified during the formation of the identity column.

9. What is a subquery?

Data that would be used in the primary query as a situation to further limit the data that can be retrieved is returned by a sub-query. With operators like =, >,, >=, =, etc., a subquery can be combined with assertions like update, select, delete, and insert.

10. Mention whether SQL Server Integration Services is required to import information directly from T-SQL commands. If so, what are the Instructions?

You can import data without ever using the SQL Server Integration Services by using T-SQL commands directly. These instructions comprise:

• Linked Servers for BCP 
• OpenRowSet 
• Bulk Insert 
• OPENQUERY 
• OPENDATASOURCE

Features of T-SQL

T-SQL offers its users a few Special Features.

• Users can import a file into a DBMS table or view it in a consumer format using the T-SQL BULK INSERT statement.
• T-SQL offers a variety of data processing and string processing support features.

Types of Functions in T-SQL

T-SQL has Four Different Types of Functions.

• Aggregate functions operate on a group of values but only produce one result.
• For each partitioning row, the ranking function returns a ranking value.
• Rowset functions return an object that can be used in the SQL statement in place of a table reference.
• Functions with a scalar type operate on and return a single value.

Key Upshots

T-SQL is derived from SQL, whereas SQL is a foundation programming language. The requirements of your project will determine which database to use.