Transact structured query language (T-SQL) is a querying language which user can use to ask Microsoft SQL server to process request . You also can use many tools in SQL Server Management Studio (SSMS) instead of writing query in T-SQL but it’s much better to use T-SQL for professional Database Administrator (DBA) because sometimes you don’t have access to any broker applications.
When you are using T-SQL to retrieve a data , you don’t need to write all steps for finding requested data to SQL server . Just need to provide and write attributes of the data , so SQL server can find it by itself intelligently .
T-SQL statements can be categorized as below :
1- DDL (Date Definition Language) is the set of T-SQL statements for handling the definition and life cycle of database objects such as table , views and procedures by using statements such as CREATE , ALTERÂ and DROP .
CREATE TABLE dbo.customer_service (id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, name VARCHAR (30) )
ALTER TABLE dbo.customer_service (name VARCHAR (50))
DROP TABLE dbo.customer_service
2- DML (Data Management Language) is the set of T-SQL statements that focuses on querying and modifying data by using statements such as SELECT , INSERT , UPDATE , DELETE .
SELECT * FROM dbo.customer_service;
INSERT INTO dbo.customer_service (name) VALUES ('Saeed');
UPDATE dbo.customer_service SET name = 'Saeed Jabarpour' WHERE name = 'Saeed';
DELETE FROM dbo.customer_service;
3- DCL (Date Control Language) is the set of T-SQL statements for managing security permissions for users and objects by using statements such as GRANT , REVOKE and DENY .
GRANT INSERT ON OBJECT::[dbo].[customer_service] TO [Saeed]
T-SQL has many elements in common with other languages such as commands , variables , loops , functions and operators . By using these elements you can write a query with high performance . Due number of elements is many I want to show some of these elements in a example query and tables .
Predicates Comparison Operators Logical Operators Arithmetic Operators Concatenation
ALL = AND * +
ANY < OR / BETWEEN > NOT % IN >= + LIKE <= - OR != SOME !< !> String Functions Date and Time Functions Aggregate Functions
SUBSTRING GETDATE SUM
LEFT , RIGHT SYSDATETIME MIN
LEN GETUTCDATE MAX
REPLACE DATEADD AVG
UPPER , LOWER DATEDIFF COUNT
LTRIM , RTRIM YEAR COUNT_BIG
STUFF MONTH STDEV
SOUNDEX DAY STDEVP
[info]Note : Scalar functions return single-valued result and window functions return entire set .[/info]
SQL Server has two different variable elements as explained below:
User defined variables: It is temporarily storing a value of a specific data type which user can declare in T-SQL batch and it is visible only on that batch . Its name must start with one @ .
DECLARE @name VARCHAR (20) ='Saeed'
DECLARE @currenttime TIME
System defined variables: Some of these variables return a value from system since SQL server has ran and some of these return value of an event on SQL server . User can’t declare system variables . System variables named with double @@ .
SQL Server also supports expressions elements within T-SQL. Combination of identifiers , symbols and operators that are evaluated by SQL server to return a single result is expressions which you may also use in SELECT statements .
SELECT salesorderid , orderdate , unitprice * quantity AS Total FROM salesorderdetail
WHERE unitprice >= 1000
ORDER BY salesorderid
SQL Server engine execute queries set-based , it means SQL server find its own manner to execute your query and after finding result set , it won’t execute rest of date set , except when you use user defined function (UDF) and cursor in your query . In these situation it has to check all rows one by one even it has been found result set . So for retrieving data from large set of data it’s better don’t use UDF and cursor because will reduce SQL server performance.
The order in which a query should be written by user is as below but it is not the order in which it is evaluated by SQL server.
Order Of Query Evaluating By SQL Server Order Of Query Writing By User Expression Role
5 SELECT Select list Defines which columns to return
1 FROM Table source Defines table(s) to query
2 WHERE Search condition Filters returned data using a predicate
3 GROUPING BY Grouping by list Arranges rows by groups
4 HAVING Search condition Filters groups by a predicate
6 ORDER BY Order by list Sorts the results
As mentioned above, you are free to execute T-SQL queries from any broking tool in the market as long as it is compatible with SQL Server. As a performance improvement tip, it is better to not use excessive expressions or aggregate functions within T-SQL query.