Introduction to Transact-SQL

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 .

[codesyntax lang=”tsql”]

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

[/codesyntax]

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 .

[codesyntax lang=”tsql”]

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;

[/codesyntax]

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 .

[codesyntax lang=”tsql”]

Grant insert on object::[dbo].[customer_service] to [Saeed]

[/codesyntax]

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 .

[table id=11/]

[table id=12/]

[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 @ .

[codesyntax lang=”tsql”]

Declare @name varchar (20) ='Saeed'
Declare @currenttime time 
Set @currenttime=convert(time,getdate(),105)

[/codesyntax]

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 @@ .

[codesyntax lang=”tsql”]

select @@version

[/codesyntax]

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 .

[codesyntax lang=”tsql”]

select salesorderid , orderdate , unitprice * quantity as Total from salesorderdetail 
where unitprice >= 1000
order by salesorderid 

[/codesyntax]

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.

[table id=13/]

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan