SQL DBCC USEROPTIONS

Overview:

                Returns the SET options active for the current connection or database. By executing the DBCC USEROPTIONS command you get to know, what are all values set for Set Option for the current connection or database.

Code:

                DBCC USEROPTIONS

Result:

textsize	2147483647
language	us_english
dateformat	mdy
datefirst	7
lock_timeout	-1
quoted_identifier	SET
arithabort	SET
ansi_null_dflt_on	SET
ansi_warnings	SET
ansi_padding	SET
ansi_nulls	SET
concat_null_yields_null	SET
isolation level	read committed

SQL PIVOT table

Overview:

                It gives you the transition from rows to columns. Below example explains how we can use PIVOT on table data.

Code:

Prerequisites

/*Create a tempeorary table*/

CREATE TABLE #PivotExample(Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 FLOAT)

GO

/*Inserting data into the tempeorary table*/

INSERT INTO #PivotExample(Col1, Col2, Col3) VALUES('A','x',5.4)

,('A','y',3.4)

,('A','z',5.2)

,('A','x',4.4)

,('B','y',3.4)

,('B','z',1.4)

,('B','x',3.2)

,('B','z',7.1)

GO

/*Displays the data from the table*/

SELECT * FROM #PivotExample

GO

PIVOT usage

/*Pivot table logic*/

SELECT Col2, A, B

FROM (

SELECT Col1, Col2, Col3

FROM #PivotExample) actualTable

PIVOT (SUM(Col3) FOR Col1 IN (A, B)) AS pivotTable

ORDER BY Col2

GO

/*Removing tempeorary table*/

DROP TABLE #PivotExample

GO

Result:

Data in #PivotExample table

PIVOT Result (Applied on “Col1”)

SQL String Split Function

Overview:

 The SQL code gives you the string spilt functionality of given delimiter. You can define your own delimiter character, in below code I have used ‘:’ as a delimiter. If you want to use other delimiter replace ‘:’ with your character.

SQL Code:  

CREATE FUNCTION dbo.fnSplitString (@string nvarchar(MAX))

   RETURNS @tblTemp TABLE (splittedValue nvarchar(MAX) NOT NULL) AS

BEGIN

   DECLARE @posIndex        int,

           @nextposIndex    int,

           @valuelen   int

   SELECT @posIndex = 0, @nextposIndex = 1

   WHILE @nextposIndex > 0

   BEGIN

      SELECT @nextposIndex = charindex(':', @string, @posIndex + 1)

      SELECT @valuelen = CASE WHEN @nextposIndex > 0

                              THEN @nextposIndex

                              ELSE len(@string) + 1

                         END - @posIndex - 1

      INSERT @tblTemp (splittedValue)

         VALUES (substring(@string, @posIndex + 1, @valuelen))

      SELECT @posIndex = @nextposIndex

   END

   RETURN

END

Usage:

SELECT      *

FROM  dbo.fnSplitString('ABC:DEF:GEH')

Result: