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 in Bangalore.

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.Over Clause will May not or may follow, all Aggregate functions Except Checksum.

Get in touch with OnlineITGuru for mastering the MSBI Online Training in Hyderabad.

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

@@Nestlevel

@@Max_Connections ⇔ @@ Textsize

@@Lock_Timeout ⇔ @@ pid

@@Language ⇔ @@Servicename

@@langid⇔@@Servername

Cursor Functions:

Consists Information About Cursors and its options.These are also non-deterministic Functions.

Cursers  are returned  Information by Following Scalar Functions

@@Curser_Rows ⇔ Curser_Status

@@Fetch_status

 Date and Time Functions:

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:

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:

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:

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:

It works with actions like text, image, and their input values, and it returns information about the value.

Functions:-

Textvalid ó patindex

Textptr

 T-SQL Server  Stored Procedures:

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.Stored Procedures that produce the all the reports.You can execute files that stored in the Procedure as you want it from any machine that connected to the database server.So if you want to perform a report frequently, you can create a Stored Procedure that produces, the report which is having access t the database.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, you can give permissions to execute a Stored Procedure but not including the underlying tables. Centralizing the code in stored Procedure gives you to reduce the amount of redundant code in all your applications and give insulation t all the applications, from the effects of database schema changes.

Advantages:
1)we can use more types of functions to execute the files.

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

3)IT contains two types of functions Deterministic and non-deterministic.

4)It is user-friendly with multiple security features.

5)It is having SQL-Command line utility.

 Recommended Audience:

1) Developers.

2) Project Managers.

3) Analytical Professionals.

4) ETL Professionals.

5) People who are Interested in Maintaining the Data.

Prerequisites:

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 in Bangalore.

 
Drop Us A Query

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