What is Transact-SQL
The SQL language
SQL is an acronym for Structured Query Language. SQL is used to communicate with relational databases. SQL statements are used to perform tasks such as update data in a database, or retrieve data from a database. For example, the SQL SELECT statement is used to query the database and return a set of data rows. Some common relational database management systems that use SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle.
There is a SQL language standard defined by the American National Standards Institute (ANSI). Each vendor adds their own variations and extensions.
In this module, you will learn how to:
- Understand what SQL is and how it is used
- Identify database objects in schemas
- Identify SQL statement types
- Use the SELECT statement to query tables in a database
- Work with data types
- Handle NULLs
Transact-SQL
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.
SQL is a Declarative language
Programming languages can be categorized as procedural or declarative. Procedural languages enable you to define a sequence of instructions that the computer follows to perform a task. Declarative languages enable you to describe the output you want, and leave the details of the steps required to produce the output to the execution engine.
SQL supports some procedural syntax, but querying data with SQL usually follows declarative semantics. You use SQL to describe the results you want, and the database engine’s query processor develops a query plan to retrieve it. The query processor uses statistics about the data in the database and indexes that are defined on the tables to come up with a good query plan.