Introduction to Transact-SQL

The estimated reading time for this post is 3 minutes

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 and Operators Elements

PredicatesComparison OperatorsLogical OperatorsArithmetic OperatorsConcatenation
ALL=AND*+
ANY<OR/
BETWEEN>NOT%
IN>=+
LIKE<=-
OR!=
SOME!<
!>

Function Elements

String FunctionsDate and Time FunctionsAggregate Functions
SUBSTRINGGETDATESUM
LEFT , RIGHTSYSDATETIMEMIN
LENGETUTCDATEMAX
REPLACE DATEADDAVG
UPPER , LOWERDATEDIFFCOUNT
LTRIM , RTRIM YEARCOUNT_BIG
STUFFMONTHSTDEV
SOUNDEXDAYSTDEVP
DATENAMEVAR
DATEPART
ISDATE

Note : Scalar functions return single-valued result and window functions return entire set .

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

SELECT @@VERSION

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 Logic

Order Of Query Evaluating By SQL ServerOrder Of Query Writing By UserExpressionRole
5SELECT Select listDefines which columns to return
1FROMTable sourceDefines table(s) to query
2WHERESearch conditionFilters returned data using a predicate
3GROUPING BY Grouping by listArranges rows by groups
4HAVINGSearch conditionFilters groups by a predicate
6ORDER BY Order by listSorts 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.

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz