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.
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 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
@@Nestlevel
@@Max_Connections ⇔ @@ Textsize
@@Lock_Timeout ⇔ @@ pid
@@Language ⇔ @@Servicename
@@langid⇔@@Servername
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
@@Fetch_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 Valuesrand ⇔ 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 ⇔ columnpropertyindexproperty ⇔ 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.Functions:-
Textvalid ó patindex
Textptr
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.