What is T-SQL?

• T-SQL Definition

  T-SQL Definition

  Transact-SQL, known as T-SQL for short, is a set of programming extensions that enhances the capabilities of Structured Query Language (SQL). With Transact-SQL, developers gain access to additional features, such as row processing, transaction control, declared variables, and robust exception and error handling. Moreover, T-SQL includes string operations and date and time processing.

  This popular and widely used database language is mainly used in and ultimately makes creating, modifying, and retrieving data more efficient.
• Deterministic Vs. Non-Deterministic Functions

  Deterministic Vs. Non-Deterministic Functions

  Transact-SQL functions can be deterministic or non-deterministic. Deterministic functions will always return the same result when called with a specific set of input values and the same database state. However, non-deterministic functions can yield different results with every call, even if you input the same values and the database state hasn’t changed. For example, the AVG function will always return the same average when given the same input values and database state. However, GETDATE will return the current datetime value, which will change even if nothing in your database has changed.
• T-SQL Functions

  T-SQL Functions

  Types of T-SQL functions include:

  Aggregate Functions

  Aggregate functions can perform a calculation on a set of values but will return one value. These deterministic functions ignore null values (with the exception of COUNT) and are often used with the GROUP BY clause of SELECT statements to calculate the aggregation on categories of rows.

  Aggregate functions T-SQL provides include:

  APPROX_COUNT_DISTINCT, AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, GROUPING_ID, MAX, MIN, STDEV, STDEVP, STRING_AGG, SUM, VAR, and VARP.

  Scalar Functions

  Unlike aggregate functions, scalar functions will only operate on a single value and return a single value. Several functions fall under the umbrella of scalar functions, including configuration functions, conversion functions, cursor functions, data and time data types and functions, graph functions, JSON functions, logical functions, mathematical functions, metadata functions, security functions, string functions, system functions, system statistical functions, and text and image functions.

  Analytic Functions

  T-SQL analytic functions can compute an aggregate value based on a group of rows. However, while an aggregate function will return one row for each group, analytic functions can return multiple rows. You can use analytic functions for ranking, percentiles, cumulative sums, and moving averages.

  Bit Manipulation Functions

  Those with SQL Server 2022 (16.x) can also access bit manipulation functions. These functions can help users process and store data more efficiently than with individual bits. So, whether you need to move, retrieve, set, or count single bits within a binary value or integer, bit manipulation functions can help you save time and energy. Available bit manipulation functions include: LEFT_SHIFT(), RIGHT_SHIFT(), BIT_COUNT(), GET_BIT(), and SET_BIT().

  Ranking Functions

  Ranking functions are non-deterministic. When you use a ranking function, SQL Server will return a ranking value for every row within a partition. T-SQL ranking functions include: RANK, NTILE, DENSE_RANK, and ROW_NUMBER.

  Some rows may receive the same value as other rows, depending on which function you use.

  Rowset Functions

  When you use a rowset function, your Microsoft data platform will return an object you can use, like a table reference in SQL statements.

  Replace Function

  The T-SQL replace function (REPLACE()) will replace any occurrences of a substring with a new substring within a string. For example, if you want to replace the word “taller” with “shorter,” you might type:

  SELECT

              REPLACE(

              ‘She was taller than the sign.’,

              ‘taller’,

              ‘Shorter’

  ) result;

  This T-SQL replace function would change the output, replacing taller with shorter and resulting in ‘She was shorter than the sign.’

  This can also be done with characters and groups of letters and is often used to correct data in tables.

  Substring Function

  The T-SQL substring function will allow you to extract characters from a string and requires three parameters: expression, start, and length. Expression is a character, binary, text, ntext, or image expression, while start specifies where you want to start using an integer or bigint expression. Length is given as a positive integer or bigint expression, and it specifies how many characters of the expression you want returned. Make sure to use a positive integer to avoid an error and termination.

  You might use the T-SQL substring function to display the first few characters of a column of string like the first part of customers’ last names or emails.

  Datediff Function

  The T-SQL datediff function uses the syntax DATEDIFF ( datepart , startdate , enddate ) and will tell you how long passed between your startdate and enddate. Datepart indicates the units used to discuss the difference between startdate and enddate. Options include year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, and nanosecond. Startdate and enddate specify where you want to start and end counting.

  If you have a large difference between your startdate and enddate values, you can use the DATEDIFF_BIG function instead of the T-SQL datediff function.

  Convert Function

  The T-SQL convert function can convert values from one datatype into another. For example, you might use the T-SQL convert function to turn a datetime into a character, a float into a real, or money into a character. The syntax for this function is: CONVERT ( type [ (length) ], expression [ , style ] ). Type represents the datatype you want to convert to, length specifies the length of the result, expression is the value you want to convert, and style is the format used to convert between datatypes.
• A Comparison of T-SQL and SQL

  A Comparison of T-SQL and SQL

  Both T-SQL and SQL are query languages, meaning they can help you store, retrieve, and manipulate data in a relational database management system. However, there are a few differences between the two. For one, SQL was developed by IBM, whereas Microsoft developed T-SQL. Plus, since T-SQL was developed by Microsoft, it is most often used in Microsoft SQL Server software and databases, while people generally use SQL across a wider variety of programs and systems.

  Additionally, T-SQL is an extension of SQL, allowing users to do more. Notably, T-SQL offers row processing, declared variables, error and exception handling, the ISNULL function, and additional support functions for string and data processing. T-SQL also provides transaction control with commands like BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

  T-SQL and SQL also sometimes use slightly different command keywords. For example, you would use LIMIT 6 to select the top six rows in a table ordered using SQL. In T-SQL, you would use TOP. It’s also worth noting that SQL is a non-procedural language, but T-SQL has procedural programming.

  While T-SQL makes creating, modifying, and retrieving data more efficient, SolarWinds^® SQL Sentry can help ensure optimal efficiency. Not only does SQL Sentry offer visibility across your database environment at a glance, but it also provides fast root cause analysis, enabling you to identify and fix high-impact queries quickly. You can keep a closer eye on your database and potentially improve performance with the database monitoring tools SQL Sentry provides.