T-SQL Tricks for SQL-Server/MSBI Online Training/OnlineITGuru

Transact-SQL is a Central for Using  SQL Server Applications, that Interact with Fact of SQL Server, works for sending Transact-SQL Statements and Functions is used in Server,  Considering in a User Interface as an application.

The Following are the applications Used with Transact-SQL:

1) General office Production Applications where we use for Large Data.

2)Applications Using Graphical Interface (GUI), it lets the users to Select, the tables and columns from Which they want to Get the Data.

3)Applications Using General Language Sentences, to find out which data a User wants to see.

4)List of Business Applications that store their Data in SQL Server DataBases. These Apps Include Both Applications, Done by vendors and applications written in-house.

5)T-SQL  Scripts Runs on sqlcmd.(SQL  Command line utility for ad hoc).

6)Data in SQL Server Databases is Extracted from Web pages.

7)Data Warehouses that contain data Extracted from, Online Transaction Processing (OLTP) Systems.

Get in touch with OnlineITGuru for mastering the  MSBI Online Training

Deterministic Functions:

This Function will always return the Same result any time when they are called with a specific Set of Input data Values and are given in the Same State of the Database.

Non-Deterministic Functions:

This Functions may return Different Outputs when they are called each time with a set of Input values Even if the Database state that Access remains Always the same.

Function Determinism:                       

The In-Built  Functions of SQL Server are Deterministic or Non-Deterministic.The Functions are Deterministic when they always show the same, result in any time they are called as Specific  Set of Input Values.

Functions are Non-Deterministic when they return, a different set of output every time they are called, with a related specific set of Input values.

Function Collation:

If a function takes character string as Input and returns character, string as output.Functions if they take non-character inputs and return a character string, that uses, as default collation of the current database for the output.If the functions are taking Multiple character strings inputs and returns a character String, use the rules of collation of the output string.for more information on functions.

SQL/MSBI Online Training/OnlineITGuru

These are the Most Important Functions in T-SQL:
Rowset Functions:

It is returned object that can be used as table reference in an SQL Statement

Containstable ⇔ OpenQuery

Freetexttable ⇔  openrowset

OpendataSource ⇔  OpenXml

The Above All functions are non-deterministic.That Shows these functions always, don’t return the similar results when they called Every time.

 Aggregate Functions:

It works on the calculations for a given Set of values and returns a Single Value.  And Count is Excepted, Null values, are Didn’t Recognized by Aggregate Functions.These Functions are Regularly Used With a Group by Clause of Select Statement.

Every Aggregate Function is Deterministic, this means functions, returns Same values Whenever they are called by Certain Set of Input values. As a matter of fact Over Clause will May not or may follow, all Aggregate functions Except Checksum.

Get in touch with OnlineITGuru for mastering the MSBI Online Training

Aggregate Functions are used as Expressions by following :

1)Select Statement as Subquery or outer  Query.

2)Compute by clause or Compute.

3) Having Clause.

T-SQL  Provides the below Aggregate Functions :                    

Avg ⇔ Min

Checksum_agg ⇔ sum

Count ó⇔ stdev

Count_big ⇔ Stdevp

Grouping ⇔ var

Max ⇔ varp

 Ranking Functions:

It Returns a Ranking Value for each row in a Partition.

Following are the Ranking Functions of Transact-SQL:  

Rank ⇔ NTILE

Dense_Rank ⇔ Row_Number

 Scalar Functions:

Operates on a Single value and then return one value.These are used wherever an expression is valid.

Configuration Functions:

Shows Information about the Present  Configuration.These are non-deterministic Functions.

To obtain Current Configuration Functions  these are the following Scalar Functions:

@@Datefirst ⇔ @@options

@@Dbts ⇔ @@Remserver

@@Max_precision ⇔ @@Version


@@Max_Connections ⇔ @@ Textsize

@@Lock_Timeout ⇔ @@ pid

@@Language ⇔ @@Servicename


Cursor Functions:

As a matter of fact it Consists Information About Cursors and its options. In other words these are also non-deterministic Functions.

Cursers  are returned  Information by Following Scalar Functions

@@Curser_Rows ⇔ Curser_Status


 Date and Time Functions:

At the same time it Performs operations like Date and Time and return String, a numeric date, and Time Values.

The Determinism Property of the T-SQL Date and Time Functions are the Following:

Determinism ⇔ Functions

Deterministic ⇔ Datadd

Deterministic ⇔ Datediff

Non-Deterministic ⇔ Datename

Deterministic  ⇔ Date

Non-Deterministic  ⇔ Getdate

Non-Deterministic  ⇔ Getutcdate

Deterministic ⇔ Month

Deterministic ⇔ Year

 Mathematical Functions:

Works with  Calculations Based on Input Values Given as Parameters, on the functions and return numeric Values

rand ⇔ degrees ⇔   abs

round ⇔ exp   ⇔   acos

sign ⇔      floor ⇔    asin

sin   ⇔          log ⇔  atan

sqrt   ⇔       log10  ⇔  atn2

square  ⇔     pi   ⇔  ceiling

tan  ⇔         power ⇔  cos

radians  ⇔ cot

 MetaData Functions:

Finally it Consists Information about the Database and Database objects.

The  Following are Some of  the Scalar Functions that return Information about the database objects and database

fn_listextendedproperty ⇔ @@procid

fulltextcatalogproperty  ⇔ assemblyproperty

fulltextserviceproperty ⇔ col_length

index_col                      ⇔ col_name

indexkey_property     ⇔ columnproperty
indexproperty          ⇔ databaseproperty

String Functions:

As a matter of fact, It Runs on a string char or varchar input value and returns a string, Numeric value.

The Below are the some of the Functions of String Functions

soundex   ⇔ nchar    ⇔ ascii

space      ⇔   patindex ⇔ char

 str         ⇔   quotename ⇔  charindex

                    rtrim               ⇔     ltrim

upper        ⇔    right ⇔  lower

unicode    ⇔  reverse  ⇔     len

substring   ⇔ replicate ⇔  left

stuff            ⇔   replace ⇔ difference

 System Statistical Functions:

Not to mention this Function contains statistical Information about the system.

The Below are functions related to system statistical Functions

@@total_write  ⇔  @@packet_errors

@@total_read  ⇔  @@io_busy

@@total_errors  ⇔   @@idle

@@timeticks  ⇔        fn_virtualfilestats

@@pack_sent  ⇔   @@cpu_busy

@@pack_received  ⇔ @@connections

 Text and Image Functions:

Especially It works with actions like text, image, and in fact their input values, to illustrate  it returns information about the value.


Textvalid ó patindex


 T-SQL Server  Stored Procedures:

Generally Microsoft SQL-server is having so many storage Procedures.but you can create your own T-SQL Stored Procedures. By this, there is no requirement of storing your T-SQL codes in files. In fact it Stored Procedures that produce the all the reports. Similarly You execute files that stored in the Procedure as you want it from any machine that connected to the database server. In detail if you want to perform a report frequently. As a matter of fact you can create a Stored Procedure that produces, the report which is having access the database.

Especially there is no requirement to understand the T-SQL Statements in the Stored Procedures.You can Find a database by MSBI security with Stored Procedures. In the same fashion you can give permissions to execute a Stored Procedure but not including the underlying tables. For example Centralizing the code in stored Procedure gives you to reduce the amount of redundant code in all your applications and give insulation all the applications, in the same fashion from the effects of database schema changes.


1)The process of Storing files has directly connected the Database.

2)In addition it contains two types of functions Deterministic and non-deterministic.

3)In fact It is user-friendly with multiple security features.

4)It is having SQL-Command line utility.

 Recommended Audience:

1) Developers.

2) Project Managers.

3) Analytical Professionals.

4) ETL Professionals.

5) People who Interested in Maintaining the Data.


It is good to Have Knowledge on Data Bases. But it is Not Mandatory and there is no necessity for the Knowledge of Programming Language.

Get in touch with OnlineITGuru for mastering the MSBI Online Training

Drop Us A Query

100% Secure Payments. All major credit & debit cards accepted.