Feeds:
Posts
Comments

Archive for the ‘Functions’ Category

SQL SERVER 2012 – Analytic Function – FIRST_VALUE

Posted in Functions, SQL SERVER, tagged , , on August 29, 2012| 5 Comments »

In SQL server 2012, Microsoft has introduced some new analytical functions and one of the most important functions in it is FIRST_VALUE. As It name implies, it does the same.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

FIRST_VALUE ( [scalar_expression )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Purpose :
It returns the first value from a list in a specified order (By default ascending order). Also we can do the partition in it but it is optional.

Lets create a student table and insert few records in it to demonstrate FIRST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)

Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)

Select * from [Student] Order By [Student ID]

Example-1 :
In this example, FIRST_VALUE returns the First Name of the student in the class (In ascending order) .

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Student Name]) as [First Value]
from dbo.[Student]
--Result

Example-2 :
In this example, FIRST_VALUE returns the student name having high score in the class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Example-3 :
In this example, FIRST_VALUE returns the student name having high score in each subject in the class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Partition By [Subject] Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CAST

Posted in Functions, SQL SERVER, tagged , , , on August 28, 2012| Leave a Comment »

In my previous post, I discussed about TRY_CONVERT. Today, we will discuss one more excellent conversion function of SQL server namely “TRY_CAST”. Before SQL server 2012, we use “Cast” function very frequently. whenever we need to cast expression from one data type to another data type, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to cast it into incompatible data type. It will generate an error. TRY_CAST is the solution of this problem.
For Example :

Select Cast ('test' as int)
--Result

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.

TRY_CAST requires three parameters expression, data type & Length. Expression and Data type are mandatory but the Length is optional.
Lets discuss this function syntax, purpose and examples in detail.

Syntax :

TRY_CAST ( expression AS data_type [ ( length ) ] )

Purpose :
This function cast expression from one data type to another data type and if it fails to cast, it give result as NULL.

Example-1 : TRY_CAST – Without Error

Select TRY_Cast(123 as int) as [Result]
--Result
123

Example-2 : TRY_CAST – With Error
If TRY_CAST fails, it will return NULL value.

Select TRY_Cast ('test' as int) as [Result]
--Result
NULL

Example-3 : TRY_Cast WITH IIF STATEMENT

In this example , we will try to convert a valid and invalid expression to Integer by using IIF statement.

Select IIF(Try_Cast('test' as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Invalid Integer

Select IIF(Try_Cast(2 as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Valid Integer

Example-4 : TRY_Cast WITH CASE STATEMENT
In this example , we will try to cast a valid and invalid expression to Integer by using case statement.

Select (Case When Try_Cast(123 as int  ) is NULL Then 'Invalid Integer' else 'Valid Integer' end)
--Result
Valid Integer

Select (Case When Try_Cast(123.45 as int) is NULL Then 'Invalid Integer' else  'Valid Integer' end)
--Result
Invalid Integer

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CONVERT

Posted in Functions, SQL SERVER, tagged , , , , on August 27, 2012| 7 Comments »

In SQL server, we use “Convert” function very frequently.Whenever, we need to convert one expression data type to another, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to convert it. It will generate an error and can impact you application.
For Example :

Select Convert (datetime2,'2012-08-32')
--Result

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

To resolve this issue, Microsoft has introduced a new function in SQL SERVER 2012, namely “TRY_CONVERT”.
It requires three parameters data type, expression & style. Data type and expression are mandatory but the style is optional.
Lets discuss this function syntax , purpose and examples in detail.

TRY_CONVERT

Syntax :

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Purpose :
This function converts expression from one data type to another data type and if it fails to convert it will return a NULL value as a results.

Example-1 : TRY_CONVERT – Passed

Select TRY_Convert(datetime,'2012-09-30',101) as [Result]
--Result
2012-09-30 00:00:00.000

Example-2 : TRY_CONVERT – Failed
If try _convert Failed,  it will return NULL value.
Lets say we don’t have 31 days in September. Now, try to convert it

Select TRY_Convert(datetime,'2012-09-31',101) as [Result]
--Result
NULL

Example-3 : TRY_CONVERT WITH IIF STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select IIF(Try_Convert(int, 'test') is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Invalid Integer

Select IIF(Try_Convert(int, 2) is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Valid Integer

Example-4 : TRY_CONVERT WITH CASE STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select (Case When Try_Convert(datetime2,'2012-08-22') is NULL Then 'Invalid Date format' else 'Valid Date format' end) as [Result]
--Result
Valid Date format

Select (Case When Try_Convert(datetime2,'2012-08-32') is NULL Then 'Invalid Date format' else  'Valid Date format' end) as [Result]
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_PARSE()

Posted in Functions, SQL SERVER, tagged , , on August 27, 2012| 1 Comment »

In my previous post, I discussed about PARSE function. In this function, if the data type is not compatible and could not be parsed then this function will generate an error. Now, we have the solution for it namely “TRY_PARSE”. SQL SERVER has introduced this (TRY_PARSE) built in conversion function in 2012. This function is very useful because most of the time due to the incorrect data , your system may generate errors. And if you use this function it will return NULL in case of error.
Lets discuss its syntax , purpose and examples in detail.

TRY_PARSE

Syntax :

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function tries to parse string value (nvarchar(4000) data type) to datetime or numeric data types. But if it fails to parse, it will return NULL value. We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : TRY_PARSE – Successful 

SELECT TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US')
--Result
2012-08-22 00:00:00.0000000

Example-2 : TRY_PARSE – Unsuccessful
If the parsing will be unsuccessful, it will return NULL value.

SELECT TRY_PARSE('datetime2' AS datetime2 USING 'en-US')
--Result
NULL

Example-3 : TRY_PARSE – Real World Example with culture keyword
In this example, we will try to parse a valid and invalid date.

Select IIF(TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Example-4 : TRY_PARSE – Real World Example with Language keyword
In this example, we will try to parse a valid and invalid date with the language keyword instead of culture.

Set Language 'English'
Select IIF(TRY_PARSE('2012-08-22' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function – PARSE ()

Posted in Functions, SQL SERVER, tagged , , on August 26, 2012| 2 Comments »

SQL SERVER 2012 has a new built in function namely “PARSE”. Lets discuss its syntax , purpose and examples in detail.

PARSE

Syntax :

PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function converts the string value (nvarchar(4000) data type) to datetime or numeric data types.
We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : PARSE -Varchar to Numeric

SELECT PARSE('123.45' AS NUMERIC(18,2)) AS Output
--Result
123.45

Example-2 : PARSE – Varchar to Int
Lets use the same example of numeric and cast it to integer.

SELECT PARSE('123.45' AS INT) AS Output
--Result

Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘123.45’ into data type int using culture .
Note : The above line of code will generate error because whatever we want to parse should be compatible with the parse data type.

Now, lets do the same without scale.

SELECT PARSE('123' AS INT) AS Output
--Result
123

Example-3 : PARSE – Varchar to Datetime with Default culture.

Select Parse('Aug 26 2012' as datetime)
Select Parse('08/26/2012' as datetime )
Select Parse('2012.08.26' as datetime)
Select Parse('26 Aug 2012' as datetime)
Select Parse('Aug 26, 2012' as datetime)
Select Parse('08-26-2012' as datetime)
Select Parse('2012/08/26' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Example-4 : PARSE – Varchar to Datetime with culture

Select Parse('26/08/2012' as datetime using 'fr-FR')  --French
Select Parse('26.08.2012' as datetime using 'de-DE')  --German
Select Parse('26-08-2012' as datetime using 'it-IT')  --Italian
--Result of the above statements
2012-08-26 00:00:00.000

Example-5 : PARSE – Varchar to Currency with culture

Select Parse('$123.45' as money using 'en-US')
Select Parse('€123,45' AS money using 'de-DE')
--Result of the above statements
123.45

Example-6 : PARSE – Language Setting Instead of Culture

SET LANGUAGE 'French'
Select Parse('26/08/2012' as datetime)

SET LANGUAGE 'German'
Select Parse('26.08.2012' AS datetime2) AS Result

SET LANGUAGE 'Italian'
Select Parse('26-08-2012' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Error :
If the string value is invalid to convert it into date time, numeric & any specific culture format, then it will generate an error.

Reference : MSDN

Read Full Post »

SQL Server 2012 – New Logical Function – IIF

Posted in Functions, SQL SERVER, tagged on August 6, 2012| 6 Comments »

SQL Server 2012 introduced new logical function namely “IIF”. Lets discuss the syntax and purpose of this function in detail.

IIF:

Syntax :

IIF ( boolean_expression, true_value, false_value )

Purpose :
The purpose of this logical function is to check the Boolean condition and if the condition is true it will return true value otherwise false value.
This function is available in SSRS and now, it is available in SQL SERVER 2012. By using this function, you don’t need to use if else , you can validate and return value in a single line of code.

Examples :

Example-1

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, 'TRUE', 'FALSE' ) AS Result;
--Result
FALSE

Example 2

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, NULL ) AS Result;
--Result

Msg 8133, Level 16, State 1, Line 3</span>
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
Reason : Because both the constant are NULL, even if one constant is not NULL it will not give this error as explained in example 3.
But, you can pass NULL parameter in both true and false value and SQL server will accept it as explained in example 4.
Example 3

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, 'False' ) AS Result;
--Result
False

Example 4

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
DECLARE @Condition_True int = NULL;
DECLARE @Condition_False int = NULL;
SELECT IIF ( @int1> @int2, @Condition_True, @Condition_False) AS Result;
--Result
NULL

Example 5 :
Real world example of IIF function

In this example, we will enter marks in IIF condition and will get different grades as per the marks.

DECLARE @Marks int = 95;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
A Grade

GO
DECLARE @Marks int = 80;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
GO
--Result
B Grade

DECLARE @Marks int = 70;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
C Grade

Reference : MSDN

Read Full Post »

SQL Server 2012 – New Logical Function – CHOOSE

Posted in Functions, SQL SERVER, tagged , on August 5, 2012| 1 Comment »

SQL Server 2012 introduced new logical function namely “Choose”. Lets discuss the syntax and purpose of this function in detail.

Syntax:

CHOOSE ( index, val_1, val_2 [, val_n ] )



Purpose:


The purpose of this logical function is to return the value from the list as per the index (1 based index). For example, we have four quarters (QUARTER-1 on 1st, QUARTER-2 on 2nd , QUARTER-3 on 3rd and QUARTER-4 on 4th index respectively) and would like to pick the values (Quarters) as per the index.


Note : If choose function will not find any value in a given index, it will return NULL value.


Examples:


Select CHOOSE(0,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL

Select CHOOSE(1,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-1

Select CHOOSE(2,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-2

Select CHOOSE(3,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-3

Select CHOOSE(4,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-4

Select CHOOSE(5,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL





					
Read Full Post »

SQL SERVER 2012 – sys.dm_exec_describe_first_result_set – dynamic management function

Posted in Functions, SQL SERVER, tagged on July 29, 2012| 2 Comments »

In this article we will discuss the new dynamic management function namely “sys.dm_exec_describe_first_result_set”. The functionality of this function is almost similar  to “sp_describe_first_result_set” system stored procedure, but the implementation (Syntax) and usage is different.

Syntax :

sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

“sp_describe_first_result_set” provides the detailed meta data on the basis of different@include_browse_information parameters. But lets say if we need only two columns from this meta data info and you would like to query it like a table, both requirements are not fulfilled by “sp_describe_first_result_set” stored procedure, because it does not return the data as a tabular form and also you cannot query only few columns. In this case you can use “sys.dm_exec_describe_first_result_set” function to fulfill both your requirements in addition to the “sp_describe_first_result_set” functionality.

Let me explain the difference with the help of simple example :


---sp_describe_first_result_set
USE AdventureWorks2012;
GO
EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'

---sys.dm_exec_describe_first_result_set
USE AdventureWorks2012;
GO
SELECT [column_ordinal],[name],[system_type_id]   FROM sys.dm_exec_describe_first_result_set
(N'SELECT * FROM HumanResources.Department', null, 0) ;

--Output

Summary :
This function has the same functionality as “sp_describe_first_result_set” but the two main differences are given below :

  1. This function can filter the meta data column as per the requirement.
  2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – sp_describe_first_result_set – System Stored Procedure

Posted in Functions, SQL SERVER, System Stored Procedure, tagged , on July 4, 2012| 10 Comments »

In SQL Server 2012 , Microsoft has provided a very helpful new system stored procedure namely “sp_describe_first_result_set”. Like name, like Work.. As the name goes, so is the performance. It gives the detailed meta data(Schema) information for the first possible result set. Note that it works only in SQL Server 2012. If you need to get the meta data info in SQL Server 2005/2008, you need to use “SP_help” and with very limited info availability.

Syntax :

sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch'
[ , [ @params = ] N'parameters' ]
[ , [ @browse_information_mode = ]  ] ]

Lets make some simple examples to check its functionality  :

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'
GO
--OUTPUT

As I discussed, you can view the  output as a detailed meta data.

Now, let me explain why “first possible result set,” name is given to this procedure, with the help of a simple example.
Given below are two queries and it will return two sets of result as well.

SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];

But, if we pass both the queries to “sp_describe_first_result_set” to provide meta data, it will provide the meta data of the first query only and according to this functionality this name (sp_describe_first_result_set) is given to this stored procedure. Lets execute this scenario.

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];'
--Output

Now, lets look at the different option of @browse_information_mode in sp_describe_first_result_set

Lets create a view to explain the different option of @browse_information_mode.

Use AdventureWorks2012
GO
Create View [HumanResources].[vDepartment]
AS
SELECT [DepartmentID] as [ID]
,[Name] as [Department Name]
FROM [HumanResources].[Department]
--Output

Now, lets execute sp_describe_first_result_set with the different option of @browse_information_mode.

@browse_information_mode=0

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=0
GO
-- When @browse_information_mode=0, it will give you the meta data but no source data available in this option.

@browse_information_mode=1

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=1
GO
-- When @browse_information_mode=1, it will give you the meta data along with the source info but the source details will be based on this view's table.

@browse_information_mode=2

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=2
GO
-- When @browse_information_mode=2, it will give you the meta data along with the source info but the source details will be based on this view.

Reference : MSDN

Read Full Post »

« Newer Posts