Search

Advertisements

Showing posts with label SQL Server 2012 (Denali). Show all posts
Showing posts with label SQL Server 2012 (Denali). Show all posts

Publish an SSIS Package result set as a SQL View

From SQL server version 2012 onwards there is an in-built mechanism to publish SSIS package result set into SQL view

1.    Requires SSIS package to be designed with Data Streaming Destination.

This destination component needs to be separately downloaded in 2012 and 2014. From 2016, it is part of standard toolbox item.

2.    Running Data Feed Publish Wizard

    i)  Creates a linked server – SSISOLEDB.

    ii) Creates a SQL view in the specified database.


Below are the steps to publish an SSIS Package result set as a SQL View

SQL Server access Previous / Next rows and first / last row from partitioned resultset

From SQL Server 2012, new built-in analytic functions are introduced, using that we can easily access subsequent/previous row in the same result set without the use of a self-join and first/last value in an ordered set of values.

In this post, let us see some examples for LEAD(), LAG(), FIRST_VALUE() and LAST_VALUE()

SSIS - Export XML - XML destination

In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.
 
Below is my Stored procedure:

SQL Server - Calender details

DECLARE @StartDate DATE = '2014-01-01', @EndDate DATE = '2014-12-31'
;With CTE
AS
(
SELECT @StartDate dt
UNION ALL
SELECT DATEADD(DAY, 1, dt) FROM Cte
WHERE dt < @EndDate
)
SELECT
FORMAT ( dt, 'D', 'en-US' ) Calender
,DATENAME(dayofyear, dt) DayNumber_Year
,DATEPART(day, dt) DayNumber_Month
,DATEPART(weekday, dt) DayNumber_Week
,DATENAME(weekday, dt) DayName
,DATEPART(WEEK,dt) WeekNumber
,IIF ( DATEPART(weekday, dt) IN (1,7) , 'Weekend', 'Weekday' ) Day
,DATENAME(month, dt) MonthName
,DATEPART(month, dt) MonthNumber
,DATENAME(year, dt) Year
,EOMONTH (dt) MonthLastDate
FROM cte
OPTION (MAXRECURSION 366);


 

SQL Server 2012 - PARSENAME function - To split delimited data

PARSENAME  -  Returns the specified part of an object name.

Syntax :
PARSENAME ( 'object_name' , object_piece ) 

Examples:

DECLARE @ObjectDetail VARCHAR(100) = 'SQLServer.AdventureWorks2012.Sales.Customer'
SELECT @ObjectDetail ObjectDetail

SELECT
PARSENAME(@ObjectDetail,4) [Server],
PARSENAME(@ObjectDetail,3) [Database],
PARSENAME(@ObjectDetail,2) [Schema],
PARSENAME(@ObjectDetail,1) [Table]


--usually Dot(.) is considered as the delimiter by PARSENAME function

DECLARE @pipedelimited VARCHAR(50) = 'Chennai|TamilNadu|India'

SELECT
PARSENAME(REPLACE(@pipedelimited,'|','.'),3)City,
PARSENAME(REPLACE(@pipedelimited,'|','.'),2) State,
PARSENAME(REPLACE(@pipedelimited,'|','.'),1) Country


DECLARE @DateTime DATETIME = GETDATE()
SELECT @DateTime [DATE]
SELECT PARSENAME(@DateTime,1) [DATE]


Reference - http://msdn.microsoft.com/en-in/library/ms188006.aspx

SSMS 2012 - Show/Hide Results Pane

In SQL Server Management Studio,developers can view or hide query results pane by using shortcut key CTRL + R in SSMS 2008.

But in SSMS 2012,after installing SQL Server 2012,shortcut key CTRL + R was not working to view/hide results pane.

Below images will show how to view/hide results pane :











Below images will show how to assign shortcut key CTRL + R to view/hide results pane:

In SSMS,Goto Tools -> Options... - > Keyboard



do the settings as shown,click Assign and Ok



Close & re-open SSMS to test working of Show/Hide Results Pane using shortcutkey CTRL + R

SQL Server 2012 - File Table

To enable Filestream while installing SQL Server 2012



To enable after installing & creating database :
Goto - > SQL Server Configuration Manager - > SQL Server Services -> double-click on SQL Server



To set Filestream directory name & access level ,while creating new database




SQL Server 2012 - IIF ( ) and CHOOSE ( )




IIF - Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Syntax:
IIF ( boolean_expression, true_value, false_value )
 
Examples :
 
SELECT IIF(1>2,'TRUE','FALSE')
GO
  output: FALSE 
 
 
 
SELECT IIF(2>1,'TRUE','FALSE')
GO 
  output: TRUE
 
 
SELECT IIF(1>2,NULL,NULL)
GO 
  Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. 
 
 
 
DECLARE @Exp CHAR(5)
SELECT IIF(1>2,NULL,@Exp)
GO
  output: NULL
 -----------------------------------------------------------------------------
 
 
 
CHOOSE - Returns the value at the specified index from a list of values. 
 
 Syntax:
 CHOOSE ( index, val_1, val_2 [, val_n ] )
 
 

SQL Server 2012 - THROW - Enhancement in error handling using TRY CATCH


THROW  statement can be used to raise an exception,like RAISERROR

Syntax : 

THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

error_number
 error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.
message
message is nvarchar(2048).
state
 between 0 and 255 that indicates the state to associate with the message. state is tinyint.

EXAMPLES:

-- Exceptions raised by THROW statement will always have a severity of 16

THROW 50000, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
THROW 2147483647, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
 
--The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
USE AdventureWorks2012

CREATE PROCEDURE THROW_Example
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
DELETE FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1
END TRY

BEGIN CATCH
SELECT 'The statement before the THROW statement must be followed by the semicolon (;) statement terminator';
THROW
END CATCH

END
GO
EXECUTE THROW_Example

GO



 For more information  & differences between the RAISERROR and THROW statements - http://msdn.microsoft.com/en-us/library/ee677615.aspx
Structured Error Handling Mechanism in SQL Server 2012 -  http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx 

SQL Server 2012 - TRY_CONVERT( ) -Data type conversion function


TRY_CONVERT function tries to convert the values passed to it ,into a specified data type.


 If the conversion is successful then it will return the value of the specified data type,

 else it will return a NULL value.


Syntax :


TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
 
data_type [ ( length ) ] - The data type into which to cast expression.
                          
expression - The value to be cast         

style  -    Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.



Examples for TRY_CONVERT( ):

 USE AdventureWorks2012

--using TRY_CONVERT() , to convert integer to character 
 
SELECT TRY_CONVERT(NVARCHAR(1),1)
GO


--if the conversion fails , CONVERT ( ) will return error, 
TRY_CONVERT( ) will return NULL

SELECT CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M' to data type int.

SELECT TRY_CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1


--sufficient data type length is not specified ,CONVERT ( ) will return error, TRY_CONVERT( ) will return NULL

SELECT CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7

Msg 6354, Level 16, State 10, Line 1
Target string size is too small to represent the XML instance

SELECT TRY_CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7


--third argument in TRY_CONVERT( ) i.e ., style accepts the same values as the style parameter of the CONVERT ( ) function.

SELECT TRY_CONVERT(NVARCHAR(30),getdate(),107)
SELECT TRY_CONVERT(NVARCHAR(30),getdate(),104) 



--TRY_CONVERT returns an error when the cast is explicitly not permitted.


SELECT TRY_CONVERT(xml, 10) AS Result;
GO

     Msg 529, Level 16, State 2, Line 1
     Explicit conversion from data type int to xml is not allowed.
 
 

SQL Server 2012 - CONCAT() - String function


CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values.



The CONCAT function requires a minimum of two input values ,else the concatenation fails.



SYNTAX: - 
                   CONCAT (string_value1, string_value2 [,string_valueN])


In previous versions of SQL Server ,we had plus symbol " +  "  to concatenate values.

Even in SQL Server 2012 ,we can use plus symbol " +  "  to concatenate values.

CONCAT( )  function overcomes data type conversion problem while concatenating values of different data types,in a simple way by implicitly coverting all arguments to string types before concatenating the inputs.

Null values are implicitly converted to an empty string. 

If all the arguments are null, an empty string of type varchar(1) is returned. 

Examples for CONCAT( ) function:

USE AdventureWorks2012

--Concatenating using plus symbol "+"

SELECT JobTitle + '|' + HireDate FROM  [HumanResources].[Employee]
GO

Msg 402, Level 16, State 1, Line 1
The data types nvarchar and date are incompatible in the add operator.

--
Concatenating using CONCAT  ( ) function

SELECT CONCAT(JobTitle,'|',HireDate) FROM  [HumanResources].[Employee]
GO

SELECT CONCAT(JobTitle,NULL,HireDate) FROM  [HumanResources].[Employee]
GO

SELECT CONCAT(NULL,NULL,NULL)
GO

SELECT CONCAT(1,'A','!')
GO

PRINT CONCAT('CONCAT() function ' , 'can also be used with PRINT')
GO






Reference - http://msdn.microsoft.com/en-us/library/hh231515.aspx

SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)

Sqlserver 2012 introduced OFFSET and FETCH clause to limit the number of rows returned by a query,after sorting the rows using ORDER BY clause.

 OFFSET clause sets how many rows needs to be skipped  
 FETCH NEXT n ROWS ONLY, displays the next n records

Note :  OFFSET and FETCH clause can be used only with ORDER BY clause

Examples for understanding  OFFSET  & FETCH clause 

USE AdventureWorks2012

--Example 1: - using only
OFFSET  with  ORDER BY clause
--In below example,after sorting rows in ascending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROW
--Example 2 - In below example,after sorting rows in descending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID DESC
     OFFSET 1 ROW
 
--Example 3 - In below example,after sorting rows ,first row is skipped & next 10 rows are fetched.   

SQL Server 2012 - WITH RESULT SETS

What is WITH RESULT SETS  in SQL Server 2012 ?

                            WITH RESULT SETS  provides flexibility in getting output of stored procedure in two ways :

                              1.) column names returned as the result of stored procedure execution can be changed,
                              2.) data type of columns  returned as the result of stored procedure execution can be changed


                             WITH RESULT SETS can be used only at the time of executing stored procedure .

Examples for understanding  WITH RESULT SETS :


USE AdventureWorks2012

--Example 1

CREATE PROCEDURE With_Resultset
--Procedure to get top 5 married employee details

AS

BEGIN

        SELECT TOP 5
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M'

END

--  actual column names returned by stored procedure are changed using WITH RESULT SETS

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      )
  )
GO





--Example 2 - only compatible data type conversion is possible

-- In below example - column NationalID data type NVARCHAR(20) is converted to INT

Advertisements