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 .
[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 Set @currenttime=convert(time,getdate(),105)
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.
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.