Transact-SQL. The SQL is a universal language used in any system manager databases relational. It has a defined standard, from which each management system has developed its own version. In SQL Server the version of SQL that is used is called Transact-SQL.
Summary
[ hide ]
- 1 Introduction
- 2 General characteristics of the Transact-SQL language
- 3 Identifier format rules
- 4 Types of data
- 5 The constants
- 6 The expressions
- 1 Types of operators
- 2 Expression results
- 7 Features
- 1 The variables
- 2 Other elements of language
- 8 See also
- 9 Sources
Introduction
Transact-SQL or T-SQL is a very powerful language that allows defining almost any task that you want to carry out on the database , it goes beyond any SQL language since it includes characteristics characteristic of any programming language , characteristics that allow define the logic necessary for the treatment of information.
Transact-SQL allows:
- Define SQLstatement blocks that are treated as units of execution.
- Perform conditional executions.
- Perform iterative or repetitive executions.
- Guarantee modular treatment with the declaration of local variables and the use of stored procedures.
- Manipulate tuple by tuple the result of a query.
However, it does not allow :
- Create user interfaces.
- Create executable applications, but elements that at some point will reach the data server and will be executed.
Due to these restrictions it is generally used to create stored procedures, triggers and user functions. It can be used like any SQL as an embedded language in applications developed in other programming languages such as Visual Basic , C , Java , etc.
General characteristics of the Transact-SQL language
It is the primary means of programming and administering SQL Server. Exposes keywords for operations that can be performed in SQL Server, including creating and modifying database schemas , entering and editing data in the database, as well as monitoring and managing the server itself. Client applications, whether consuming data or managing the server, take advantage of SQL Server functionality by sending T-SQL queries and statements that are processed by the server and the results (or errors) return to the SQL client application Server that are managed using T-SQL. For this, it exposes read-only tables with server statistics. Management functionality is exposed through system-defined stored procedures that can be invoked from T-SQL queries to perform the administration operation. It is also possible to create Linked Servers using T-SQL. Linked servers allow operation between multiple servers with one query.
Transact-SQL is essential to work with Microsoft® SQL Server ™. All applications that communicate with SQLThey do this by sending Transact-SQL statements to the server, regardless of the application’s user interface. It should be remembered that Transact-SQL does not implement all the options present in SQL92 and others it modifies or extends. It also implements a series of resources that are not linked to the standards, generally related to programming facilities and data access. The SQL language was created with the purpose of being a very powerful language and at the same time very easy to use, it has been achieved to a great extent since with a single phrase (instruction) we can retrieve complex data (for example data that is found in several tables, combining them, calculating summaries), and using a language very close to the spoken language. Simplicity also lies in that what we indicate is what we want to obtain, not how we have to obtain it, that is what the system will take care of automatically. Additionally, SQL statements all follow the same pattern. They start with a verb that indicates the action to be performed, completed by the object on which we want to perform the action, followed by a series of clauses (some mandatory, others optional) that complete the sentence, and provide more details about what is want to do.
Identifier format rules
Identifiers are the names of the objects in the database: servers , databases , tables, views, columns, indexes, triggers, procedures, constraints, rules, and so on. The formatting rules for normal identifiers depend on the compatibility level of the database, using the SET COMPATIBILITY_LEVEL clause of the ALTER TABLE statement. When the compatibility level is 90, (the one assigned by default) the following rules apply for identifier names:
- It cannot be a reserved word.
- The name must be between 1 and 128 characters, except for some types of objects where the number is more limited.
- The name must begin with:
- A letter, as defined by the Unicode 3.2 standard. The Unicode definition of letters includes the Latin characters from “a” to “z” and from “A” to “Z”.
- The underscore character (_), at (@), or number (#).
- Certain symbols at the beginning of an identifier have special meaning in SQL Server. An identifier that begins with the at sign indicates a parameter or a local variable. An identifier that begins with the number sign indicates a temporary table or procedure. An identifier that begins with a double number sign (##) indicates a global temporary object.
- Some Transact-SQL functions have names that start with a double at sign (@@). To avoid confusion with these functions, it is recommended not to use names that start with @@.
- Special characters or embedded spaces are not allowed.
If we want to use a name that does not follow these rules, normally in order to include blank spaces, we must write it enclosed in square brackets (you can also use quotation marks but it is recommended to use square brackets).
Type of data
In SQL Server 2005 , each column, expression, variable, and parameter is associated with a data type. A data type actually defines the set of valid values for the defined fields of that type. Indicates if the field can contain: numeric, character, currency, date and time data, etc. SQL Server provides a set of system data types that define all the data types that can be used. We can also define our own data types in Transact-SQL.
The most used data types are
- Numerics: int, decimal, money
- The date and time: datetime
- And the character strings: varchar
The constants
A constant is a specific value or a symbol that represents a specific data value. The format of the constants depends on the data type of the value they represent. The most used.
- The numerical constants are written using a string of numbers, with the consideration that the decimal separator is a point, not a comma, and that if it is a monetary value, we must include the currency at the beginning of the constant. By default, the values will be positive. To indicate otherwise we write the – sign at the beginning.
- Date and time constants are enclosed in single quotation marks and in a suitable date and time format.
- And the constants in character strings are enclosed in single quotes. For example: ‘Juan García López’.
To indicate negative and positive values we add the prefix + or – depending on whether the value is positive or negative. Without a prefix it is understood that the value is positive.
The expressions
An expression is a combination of symbols and operators that the SQL Server database engine evaluates to obtain a single value. A simple expression can be a single constant, variable, column, or scalar function. Operators can be used to combine two or more simple expressions to form a complex expression. Two expressions can be combined using an operator if they both have data types supported by the operator and at least one of these conditions is true:
- Expressions have the same data type.
- The lowest priority data type can be implicitly converted to the highest priority data type.
- The CAST function can explicitly convert the lowest priority data type to the highest priority data type or to an intermediate data type that can be implicitly converted to the highest priority data type.
Types of operators
- Numerical operators
- Bitwise operators: perform bit manipulations between two expressions of any of the data types in the integer data type category.
- Comparison operators.
- Logical operators.
- Chain operators.
Expression results
- If two expressions are combined using comparison or logical operators, the resulting data type is Boolean and the value is one of the following: TRUE, FALSE, or UNKNOWN.
- When two expressions are combined using arithmetic operators, bitwise or string, the operator determines the resulting data type.
Complex expressions consisting of various symbols and operators are evaluated as a result consisting of a single value. The data type, collation, precision and value of the resulting expression is determined by combining the component expressions two by two, until a final result is reached. The priority of the expression operators defines the sequence in which the expressions are combined.
Features
SQL Server provides numerous built-in functions and allows you to create user-defined functions. There are different types of functions:
- Rowset functions return an object that can be used, in Transact-SQL statements, instead of a reference to a table.
- Aggregate functions (also called column functions), operate on a collection of values and return a single summary value.
- Category functions, return a category value for each row in a rowset, for example it returns the row number, the rank of the row in a certain order, etc.
- Scalar functions operate on one value and then return another value. Scalar functions are classified according to the data type of their operands.
Variables
In Transact-SQL we can define variables, which will be of a certain data type, as data types you can use those of the SQL-SERVER database, but it is also possible to use own types of the language that cannot be used in DDL (Data Description Language). The Cursor type and the Table type are two of these types. Variables are defined using the DECLARE statement in the following format: DECLARE @nbvariable type The variable name must start with the @ symbol, this symbol causes SQLinterpret the name as a variable name and not a database object name. For example: DECLARE @empleados INT With this we have defined the variable @empleados of type integer. To assign a value to a variable, the assignment is done with the word SELECT and the equal sign with the format: SELECT @nbvariable = value The value can be any constant value, another variable name, a valid expression or something more powerful, part of a SQL SELECT statement. The value stored in the variable can be viewed using the PRINT command. o SELECT PRINT @nbvariable o SELECT @nbvariable The value stored in the variable will be displayed in the results tab. It can also be used to write messages: PRINT ‘This is the message’
Other language elements
- As in any other programming language, comments are used to facilitate the readability of the code. In SQL , comments with the signs are inserted:
Insert Table Image
- Changes the context of the database to that of the specified database.
USE nbBaseDeDatos
It causes the active database to become the database indicated in the statement, the queries that are executed next will be made on tables of that database if not indicated otherwise. It is a useful instruction to make sure that the query runs on the correct database.
- It is not a Transact-SQL statement, but a command recognized by the sqlcmd and osql utilities, as well as by the SQL Server Management Studio Code Editor .
The SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of instructions consists of all the instructions specified since the last GO command or from the beginning of the session or script if it is the first GO command. For example, if we want to create a query to create a database and its tables, after CREATE DATABASE …; we have to put GO before the first CREATE TABLE so that the system performs the first operation and the database is created before executing the first CREATE TABLE.
- BEGIN … END. Enclose a set of Transact-SQL statements so that these statements form a statement block.
