Transact SQL

A Beginner’s Guide to Transact SQL

in ,

None of us has not heard about SQL or even dealt with it. Yes, it is the Structured Query Language. But how many of us heard about the so important “Transact SQL”?

To really give you the confidence sense, yes, it is from our beloved Microsoft. To let us know more about and define Transact SQL.

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc., and changes to the DELETE and UPDATE statements. Stored procedures in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

That’s great, is not it? But from where it really gets its importance is that Basic SQL statements, such as SELECT, INSERT, UPDATE, and DELETE are available no matter what relational database system you’re working with. Although these SQL statements are part of the ANSI SQL standard, many database management systems also have their own extensions. These extensions provide functionality not covered by the SQL standard and include areas such as security management and programmability. Microsoft database systems such as SQL Server, Azure SQL Database, Azure Synapse Analytics, and others use a dialect of SQL called Transact-SQL, or T-SQL. T-SQL includes language extensions for writing stored procedures and functions, which are application code that is stored in the database, and managing user accounts.

To get closer and closer, here we have a good example of Transact SQL Query, which is CONTAINS (Transact-SQL), which Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINS can search for:

  • A word or phrase.
  • The prefix of a word or phrase
  • A word near another word.
  • A word is inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
  • A word that is a synonym of another word using a thesaurus (for example, the word “metal” can have synonyms such as “aluminum” and “steel”).

That was a single and short query example of Transact SQL, which is important for the fact that T-SQL is a very rich and complex scripting language offering you a great degree of flexibility in how you use it. Because of this, you can really think of it almost like a programming language, and that is exactly what encourages to go along with Microsoft SQL Server.

References: