NULL Handling in SSIS

Using Derived Column Transformation

Samples:
-----------

TRIM([CONVERSION_DATE  ]) == "" ? (DT_DATE)"1/1/1900" : (DT_DATE)TRIM([CONVERSION_DATE  ])

TRIM([CICD RESOURCE_SEQ_NUM]) == "" ? NULL(DT_I4) : (DT_I4)TRIM([CICD RESOURCE_SEQ_NUM])

TRIM([CICD USAGE_RATE_OR_AMOUNT]) == "" ? NULL(DT_NUMERIC,19,4) : (DT_NUMERIC,19,4)TRIM([CICD USAGE_RATE_OR_AMOUNT])

TRIM([BR STANDARD_RATE_FLAG]) == "" ? "-" : (DT_WSTR,1)TRIM([BR STANDARD_RATE_FLAG])

TRIM(ORGANIZATION_ID) == "" ? NULL(DT_I8) : (DT_I8)TRIM(ORGANIZATION_ID)

Tables having same column names

SELECT C.Name AS ColumnName
,  STUFF(
   (SELECT ',' + O1.Name
   FROM SYS.OBJECTS O1
   JOIN SYS.Columns C1 ON O1.object_id = C1.object_id
   WHERE C1.Name = C.Name
   FOR XML PATH('')),1,1,'') AS TableNames
FROM
SYS.OBJECTS O
JOIN SYS.Columns C ON O.object_id = C.object_id
JOIN SYS.Types T ON C.user_type_id = T.user_type_id
WHERE O.TYPE = 'U' AND C.Name like '%dead%'
GROUP BY C.Name
ORDER BY C.Name

TRY_PARSE

-- TRY_PARSE() function to translate value into specific datatype without throwing exceptions on failure

-- If the parse operation is successful, this function returns the resultant value; Otherwise, it returns NULL value.

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

GO

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

TRY_CONVERT

-- Often in queries, we can see conversion errors like "conversion failed when converting from .... "
--If we wants to hide these errors and instead of exception, if we want a NULL value, we can use this TRY_CONVERT() function.

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Throw

/*
In previous versions, by using TRY..CATCH block we used to catch the exceptions raised by application and by using RAISEERROR(), error has been thrown back to the called application. However RAISEERROR() has few drawbacks.

    It will not allow to throw the same exception number, we got in the code. Instead we need to create a custom exception and needs to to throw it.
    Because we are send the error separately, error line number also will change.

Now in SQL Server 2012, THROW command will solves these problems by allowing to throw the same exception we got in code */


BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR)
PRINT 'Throwing error...';
THROW
END CATCH

Temp Tables Existance Check

--One of the breaking changes added in SQL Server 2012 is related to the creation of #TEMP tables.
--SQL Server 2012 assigns a negative value as the OBJECT ID of the #TEMP tables.

CREATE TABLE #cust (Col1 INT)
GO

-- Right Way:

IF OBJECT_ID('tempdb..#cust', 'U') IS NOT NULL
BEGIN
    PRINT 'Object Exists'
    -- Do Something
END


-- Wrong Way if used in SQL 2012

IF OBJECT_ID('tempdb..#cust') > 0
BEGIN
    PRINT 'Object Exists'
    -- Do Something
END

sp_describe_undeclared_parameters & sp_describe_first_result_set

USE [AdventureWorks2012]
GO

EXEC sp_describe_first_result_set
N'SELECT * FROM Sales.SalesOrderDetail', NULL, 1
GO


CREATE VIEW dbo.MyView
AS
SELECT [SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

/* Now let us execute above stored procedure with various options.
You can notice I am changing the very last parameter which I am passing to the stored procedure.
This option is known as for browse_information_mode. */

--when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table.
--When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 0;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 1;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 2;
GO

DROP VIEW MyView


-- It returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.

-- Build a query string that contains parameters
DECLARE @query NVARCHAR(MAX) = N'
SELECT
    object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name'

-- Retrieve metadata of @id and @name parameters
EXECUTE sp_describe_undeclared_parameters
    @tsql = @query

Sequence

/*
The new SEQUENCE type in SQL Server 2012 will allows you to create sequential numbers.
In general identity columns can be used to uniquely identify records in a table.
But if we wants to maintain identity columns across multiple tables, or if we need the identity column before insertion of
record into table, we can use SEQUENCE type in SQL Server 2012.
*/

USE ExploringSQL
GO

CREATE SEQUENCE CustomSequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;
GO

CREATE TABLE TEST1
(
Id int,
Col1 varchar(100)
)
GO

CREATE TABLE TEST2
(
ID int,
Col2 varchar(100)
)
GO

INSERT INTO TEST1 (Id,Col1)
VALUES (NEXT VALUE FOR CustomSequence,'a')
INSERT INTO TEST2 (Id,Col2)
VALUES (NEXT VALUE FOR CustomSequence,'b')

SELECT * From Test1
SELECT * From Test2

DROP TABLE TEST1
DROP TABLE TEST2

Roll Up

-- COMPUTE BY REPLACED WITH ROLLUP IN SQL 2012

USE [ExploringSQL]
GO

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO

INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)
GO

-- SELECT * FROM tblPopulation

SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
GO

SELECT Country,[State],City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country,[State],City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]--,City
GO

DROP TABLE tblPopulation

PERCENTILE_CONT

/* This function will returns the continuous interpolated value at the specific offset

PERCENTILE_CONT() function will takes a parameter, which indicates the offset and it should be between 0 and 1. */

DECLARE @T TABLE
(
GroupID INT,
Number INT
)

INSERT INTO @T (GroupID,Number)
VALUES (1,10),(1,20),(1,30),(1,50),(1,60)

SELECT GroupID,Number,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Number)
OVER (PARTITION BY GroupID) AS [InterpolatedAt0.4],
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Number)
OVER (PARTITION BY GroupID) AS [InterpolatedAt0.8]
FROM @T

PERCENTILE_DISC

/*
PERCENTILE_DISC() function will takes a parameter, which indicates the offset and it should be between 0 and 1.

Suppose, in a group of values 10,20,30,40,50,60 PERCENTILE_DISC() function will returns any value between 10 and 50.
This value will be calculated based on interpolation, at given offset position.
*/

DECLARE @T TABLE
(
GroupID INT,
Number INT
)

INSERT INTO @T (GroupID,Number)
VALUES (1,10),(1,20),(1,30),(1,50),(1,60)

SELECT GroupID,Number,
            PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY Number)
            OVER (PARTITION BY GroupID) AS [InterpolatedAt0.4],
                PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY Number)
                OVER (PARTITION BY GroupID) AS [InterpolatedAt0.8]
FROM @T

PERCENT_RANK

/*
PERCENT_RANK() function will returns the percentage value of rank of the element among its group.

PERCENT_RANK() function value will be

    For first element in its group, it will be 0.
    For last element in its group, it will be 1.
    For remaining elements, it will be ( No. Of Elements Before that Element ) / (Total Elements - 1)
*/

DECLARE @T TABLE
( 
Number INT
)
INSERT INTO @T (Number)
VALUES (10),(20),(30),(50)

SELECT Number,
PERCENT_RANK() OVER (ORDER BY Number) AS PERCENTRANK
FROM @T

Parse()

--Parse() function will parse an value into specified datatype. This function relies on CLR.
--It will take some performance overhead. Use this function only to convert strings to/from datetime and numeric values.

Example:

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result

GO

SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result

Pagination Support

-- Support for Pagination using ORDER BY along with OFFSET and FETCH NEXT

-- The new pagination support allows you to write queries that can return rows from specific position of the result set.

--For example: the following query returns rows 20 to 30 from the result set.

USE [AdventureWorks2012]
GO

SELECT  *  FROM [HumanResources].[Employee]
ORDER BY HireDate   
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;


SELECT   *  FROM [HumanResources].[Employee]
ORDER BY HireDate

OVER Clause is enhanced

USE [ExploringSQLR2]
GO

-- OVER Clause is enhanced to apply aggregate functions while applying "Order By"
-- Before SQL Server 2012, We can't use "Order By" clause, while we are using aggregate functions with OVER clause.

DECLARE @Companies TABLE
(  
    CompanyId int,
    [Year] int,
    Amount int
)
INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),
       (2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT * FROM @Companies

SELECT  CompanyId,[Year],Amount,
        SUM(Amount) OVER (PARTITION BY CompanyID
                            ORDER BY [Year]) AS CumulativeRevenue        
FROM @Companies


-- OVER Clause is enhanced with ROWS/RANGE Clause to limit the rows

DECLARE @Companies TABLE
( 
CompanyId int,
[Year] int,
Amount int
)

INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),(2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT * FROM @Companies

SELECT  CompanyId,[Year],Amount,
SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS Next4YrsIncludingCurrentYrRevenue  ,

SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS  UNBOUNDED PRECEDING
) AS CumulativeRevenue,

SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS Last5YrsRevenue

FROM @Companies

Date Functions

-- End of the Month

SELECT EOMONTH('12/25/2011')

--you can add months also using this function by passing duration in second parameter.

DECLARE @Currentdate DATETIME
SET @Currentdate = '12/12/2010'
SELECT EOMONTH ( @Currentdate,-2 ) AS Result;

-- DATEFROMPARTS function to create date based on specified month,year and day

DECLARE @Year INT,
@Month INT,
@Day INT
SET @Year = 2011
SET @Month = 11
SET @Day = 29

SELECT DATEFROMPARTS (@Year,@Month,@Day) as Result

-- TIMEFROMPARTS function to create time based on specified Hour,Minute and Second

DECLARE @Hour INT,
@Minute INT,
@Seconds INT,
@Fractions INT
SET @Hour = 15
SET @Minute = 20
SET @Seconds = 50
SET @Fractions = 500

SELECT TIMEFROMPARTS (@Hour, @Minute, @Seconds, @Fractions, 3) as Result

-- Note: Last Argument of this function should be integer constant and it indicates number of digits in milliseconds part. It can be between 3 to 7 digits.

-- This function returns a fully qualified DATETIME value based on the specified datetime parts such as year, month, day, hour, minute, seconds and milliseconds.

DECLARE
@Year INT = 2011,
@Month INT = 11,
@Day INT = 29,
@Hour INT = 15,
@Minute INT = 20,
@Seconds INT = 50,
@MilliSeconds INT = 0

SELECT DATETIMEFROMPARTS(
@Year,
@Month,
@Day,
@Hour,
@Minute,
@Seconds,
@MilliSeconds) as Result

LAG & LEAD

--This functions will return columns values from its previous rows in the same result set without the use of a self-join.

DECLARE @Users TABLE( 
                        UserID INT IDENTITY,
                        UserName VARCHAR(20)
                    )

INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'


-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID - 1

-- USING LAG()

SELECT UserID,UserName,
LAG(UserName) OVER (ORDER BY UserName) AS PreviousRecordUserName
FROM @Users
ORDER BY UserName


-- LEAD(): This functions will return columns values from subsequent rows in the same result set without the use of a self-join.

DECLARE @Users TABLE( 
UserID INT IDENTITY,
UserName VARCHAR(20)
)

INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'

-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID + 1

-- USING LEAD()

SELECT
UserId,UserName,
LEAD(UserName) OVER (ORDER BY UserName) AS NextUser
FROM @Users
ORDER BY UserName

Index Rebuild Online

USE TempDB
GO

CREATE TABLE TestTable
(ID INT, FirstCol NVARCHAR(10), SecondCol NVARCHAR(MAX))
GO

CREATE CLUSTERED INDEX [IX_TestTable]
ON TestTable
(ID)
GO

CREATE NONCLUSTERED INDEX [IX_TestTable_Cols]
ON TestTable
(FirstCol)
INCLUDE (SecondCol)
GO


ALTER INDEX [IX_TestTable_Cols] ON [dbo].[TestTable]
REBUILD WITH (ONLINE = ON)
GO

DROP TABLE TestTable
GO

IIF

DECLARE @Number1 INT,
        @Number2 INT,
        @true_value VARCHAR(20),
        @false_value VARCHAR(20)

SET @Number1 = 10
SET @Number2 = 20
SET @true_value = 'A is Big'
SET @false_value = 'B is Big'

SELECT IIF (@Number1 > @Number2, @true_value, @false_value) as Result

HASH BYTES Enhanced

-- HASH BYTES Enhanced

--Before SQL Server 2012, HashBytes function used to support following algorithms while encrypting a string.

--MD2,MD4,MD5,SHA and SHA1

--Now In SQL Server 2012, 2 more algorithms "SHA2256" and "SHA2512" has been added.

SELECT HASHBYTES('SHA2_256', 'Anitha Saradhi') AS 'SHA2256'
SELECT HASHBYTES('SHA2_512', 'Anitha Saradhi') AS 'SHA2512'

Format

/*
This function format the input value based on the specified format and optional culture input.
If the culture value is not specified, then the language of the current session is used.
This can be used to format the numbers and datetime values into required format.
*/

DECLARE @CurrentDate DATETIME,
        @Format NVarchar(20),
        @Culture NVarchar(10)

SET @CurrentDate = GETDATE(); 
SET @Format = 'dd-MM-yyyy:hh:mm:ss'
SET @Culture =  'ar-LB'

-- SELECT CONVERT(VARCHAR(20), GETDATE(),101)

SELECT FORMAT(@CurrentDate,@Format, @Culture)  as Result

SELECT FORMAT(@CurrentDate,@Format)  as Result

FORCESEEK Enhancement

-- FORCESEEK hint is extended to specify columns used to seek in the specified index

/*
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table.
Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows,
the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset.
But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

When SQL Server chooses seek over scan while retrieving record details, it will compare key value with search input, based on comparison result,
Query engine will move to appropriate page.
Suppose, index has multiple columns, if we don't want all key columns to consider, in SQL Server 2012, we can mention the index columns to consider when the index has multiple key columns.
*/

SELECT CompanyID,CompanyName,Amount
FROM COMPANIES
WITH (FORCESEEK(Idx_Company(CompanyID)))
WHERE CompanyID = 1

Forcescan

-- FORCESCAN Hint added to force query optimizer to choose scan over seek

 /* Often while tuning stored procedures, we will see tables have the "seek" operator.
 Few times one of reason for slow performance is wrong estimates on number of rows.
 Because of that, there are more chances that query optimizer choose "SEEK" over "SCAN".
 To force optimizer to choose "SCAN" over "SEEK", FORCESCAN hint has been added. */

Select OH.OrderID,OD.ItemName,OH.OrderCost
from OrderHeader OH
INNER JOIN OrderDetails OD WITH (FORCESCAN)
on OH.OrderID = OD.OrderID

First & Last Value

/* LAST_VALUE() function returns the last value among the set of ordered values according to specified ordered & partitioned criteria.
One difference between firstvalue and lastvalue is, for a group of rows, FIRST_VALUE() will return same value,
where as LAST_VALUE() function will return the last value until that row in that group.
*/

USE [ExploringSQL]
GO

DECLARE @Salaries TABLE
(
DepartmentID INT,
Salary INT  
)

INSERT INTO @Salaries (DepartmentID,Salary)
VALUES (1,23),(1,25),(1,21),(2,35),(2,15)

SELECT * FROM @Salaries

-- FIRST VALUE

SELECT DepartmentID,Salary,
FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID
ORDER BY Salary) AS FirstSalaryInDepartment
FROM @Salaries

-- LAST VALUE

SELECT DepartmentID,Salary,
LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID
ORDER BY Salary) AS LastSalaryInDepartment
FROM @Salaries

USE AdventureWorks2012;
GO

SELECT Department, LastName, Rate, HireDate,
    LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS eph 
    ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control');


SELECT BusinessEntityID, DATEPART(QUARTER,QuotaDate)AS Quarter, YEAR(QuotaDate) AS SalesYear,
    SalesQuota AS QuotaThisQuarter,
    SalesQuota - FIRST_VALUE(SalesQuota)
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate)
              ORDER BY DATEPART(QUARTER,QuotaDate) ) AS DifferenceFromFirstQuarter,
    SalesQuota - LAST_VALUE(SalesQuota)
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate)
              ORDER BY DATEPART(QUARTER,QuotaDate)
              RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
               ) AS DifferenceFromLastQuarter
FROM Sales.SalesPersonQuotaHistory
WHERE YEAR(QuotaDate) > 2005
AND BusinessEntityID BETWEEN 274 AND 275
ORDER BY BusinessEntityID, SalesYear, Quarter;

File Tables

--select value , value_in_use  from sys.configurations  where name like 'filestream access level'


--EXEC sp_configure filestream_access_level, 2
--RECONFIGURE

USE [master]
GO

CREATE DATABASE SQLDocumentStoreDB
ON PRIMARY
(NAME = SQLDocumentStoreDB
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\SQLDocumentStoreDB.mdf'),
FILEGROUP FileStreamFG CONTAINS FILESTREAM
(NAME = SQLDocumentStoreFileTable
,FILENAME = 'C:\FileStreamShare\DocumentStore' ) --Folder location
LOG ON
(NAME = SQLDocumentStoreDB_Log
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log\SQLDocumentStoreDB_Log.ldf')
WITH FILESTREAM
--Gives full non-transactional access to the share/ directory
(NON_TRANSACTED_ACCESS = FULL
,DIRECTORY_NAME = N'DocumentStore');
GO


USE [SQLDocumentStoreDB]
GO

CREATE TABLE MyDocuments AS FILETABLE
WITH (FileTable_Directory = 'MyDocumentStore');

--INSERT INTO MyDocuments(name,is_directory)
-- values('Docs',1)
-- INSERT INTO MyDocuments(name,is_directory)
-- values('Multimedia',1)

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT * FROM [SQLDocumentStoreDB].[dbo].[MyDocuments]

USE [master]
GO

SELECT DB_NAME(Database_id) AS [Database]
,[database_id] AS [DatabaseID]
,[non_transacted_access] AS [NonTransactedAccess]
,[non_transacted_access_desc] AS [NonTransactedAccessDesc]
,[directory_name] AS [DirectoryName]
FROM [sys].[database_filestream_options]
WHERE DB_NAME(Database_id) = 'SQLDocumentStoreDB'

File Growth Error & its resolution

/*Error Message:

Msg 5169, Level 16, State 1, Line 1
FILEGROWTH cannot be greater than MAXSIZE for file ‘NewDB’.
Creating Scenario: */

CREATE DATABASE [NewDB]
ON PRIMARY
(NAME = N'NewDB',
FILENAME = N'D:\NewDB.mdf' ,
SIZE = 4096KB,
FILEGROWTH = 1024KB,
MAXSIZE = 4096KB)
LOG ON
(NAME = N'NewDB_log',
FILENAME = N'D:\NewDB_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%)
GO

--Now let us see what exact command was creating error for him.

USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024MB )
GO

/* Workaround / Fix / Solution:
The reason for the error is very simple. He was trying to modify the filegrowth to much higher value than the maximum file size
specified for the database. There are two way we can fix it. */

--Method 1: Reduces the filegrowth to lower value than maxsize of file

USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024KB )
GO

--Method 2: Increase maxsize of file so it is greater than new filegrowth

USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024MB, MAXSIZE = 4096MB)
GO

Cumulative Distribution

/* Distribution Value:

A distribution value indicates the possibility of occurance of that value among a group of values.
For Example, when a coin is thrown, it can be head or tail. Distribution value of heads is 1/2 = 0.5 , distribution value of tails is 1/2 =0.5
For example, when a dice is thrown, it can be any number among 1 to 6. So, distribution value of 1 is 1/6 = 0.1666, distribution value of 2 is 1/6 = 0.666.....

Cumulative Distribution Value:

Cumulative distribution value will tells you the possibility of occurance of that value or lesser than that among a group of values.
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 1 is, 1/6 = 0.1666
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 2 is, 2/6 = 0.3333
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 3 is, 3/6 = 0.5000
*/

Use [ExploringSQL]
GO

-- Creating table
CREATE TABLE [Results]
(
[Subject] varchar(10),
[Student] varchar(50),
[Marks] int
)

-- Inserting sample records
INSERT INTO [Results] (Subject,Student,Marks)
VALUES
('Maths','Student1',45),
('Physics','Student2',45),
('Physics','Student1',50),
('Chemistry','Student3',20),
('Physics','Student3',35),
('Biology','Student1',20),
('Biology','Student2',60),
('Biology','Student3',65),
('Chemistry','Student1',75),
('Biology','Student4',30)
GO

select * from [Results] order by Student , Marks

-- Querying with CUME_DIST()

SELECT [Student],[Subject],[Marks],
CUME_DIST() OVER(PARTITION BY [Subject] ORDER BY [Marks]) as CD
FROM [Results]
ORDER BY [Subject],[Marks]
GO

DROP TABLE [Results]

CONCAT

/*
CONCAT function can be used to concatenate two or more strings.
This function is available before in other databases like Oracle,MySQL etc.
This feature will offer below advantages over standard concatenation operator(+)

    If any of the string is null, standard concatenation operator will return null as output.
    However, CONCAT() function will ignore null values while concatenating.
    As CONCAT() function is already available in other databases such as Oracle and MySql, while porting from one DB to other,
    this will gives more flexibility compared to standard operator.
*/


DECLARE
@firstname VARCHAR(20) = 'Pardha Saradhi',
@middlename VARCHAR(10) = NULL,
@lastname VARCHAR(20) = 'V'

-- Without CONCAT function
SELECT @lastname + ' ' + @middlename + '' + @firstname


-- Using CONCAT function
SELECT CONCAT(@lastname, ' ', @middlename, ' ', @firstname)

Choose

-- CHOOSE() function will returns the value based on the specified index from the list of values.

DECLARE @Index INT,
        @FirstValue VARCHAR(10),
        @SecondValue VARCHAR(10),
        @ThirdValue VARCHAR(10)

SET @Index = 2
SET @FirstValue = 'One'
SET @SecondValue = 'Two'
SET @ThirdValue = 'Three'

SELECT CHOOSE (@Index, @FirstValue, @SecondValue, @ThirdValue)

SELECT * FROM [Person].[Address] ORDER BY CHOOSE (4 ,AddressLine1, AddressLine2, City, AddressID) -- Can Replace Dynamic SQL.

New in SSMS 2012

-- 1. CODE SNIPPET:

press "Ctrl+k,Ctrl+x"

To add a new code snippet:

    Create the snippet file with .snippet extension(Its advisable to take existing snippet file and modify it)
    Go to Tools -> Code Snippets Manager
    Select the folder, under which you wants to add code snippet and click on Add button and select the snippet file.

-- 2. TASK LIST : GO TO VIEW -> TASK LIST
    We often use some external tools to manage our task list, where we will add our tasks and will follow the tasks according to the task list.
    Now with SQL Server 2012, external tool is no longer required.

-- 3. Zoom/Magnify feature helps in presentations

-- 4. Multi Monitor Support:

    Until SQL Server 2012, in SSMS, we were able to open only one query window. 2 or more query windows can't share the same screen.
    Only one query window can be visible on the screen.

    Now SQL Server 2012 SSMS provides the option to open multiple query windows at a time.
    Easily we can drag the query window and can place that query window where ever we want.

-- 5. Debugging Enhancements:
        Before SQL Server 2012, SSMS already provided a feature to debug the queries.
        In SQL Server 2012, this has been enhanced more to provide more rich experience.

        Conditional Hit breakpoints ? Break point will hit only when the specified condition met.

        Hit Count -> Break point will hit when that breakpoint is hit specified number of times.
        Export/Import the breakpoints to an XML file.
        and more..

        You can get these additional options by Right click on a break point and explore these additional capabilites

-- 6. New Sequence Node to manage Sequences:

        SQL Server 2012 SSMS has a new node “Sequences” in object explorer under Database-> programmability.
        This will allows you to create new sequences or update existing sequences by using GUI.

            Go to Object Expolorer -> Expland database -> Programmability -> Sequences.
            Right click the folder -> New Sequence

        This will open the dialog box, where you can create new sequence and mention its properties, such as minimum value, maximum value and increment value and set its other properties like "Cycle","Cache" etc.

-- 7. “Surround With” feature to enclose your query with IF,BEGIN..END,WHILE blocks

Often in stored procedures/queries, we will use IF, BEGIN..END, WHILE statements. Now, In SQL Server 2012, SSMS provides an option “Surround with”,
which simplifies in enclosing query statements with these blocks.

    Right Click and choose “Surround with”, which shows IF,BEGIN,WHILE options. Or Alternatively press Ctrl+k,Ctrl+s

-- 8. Keyboard Shortcuts enhancement

Few users are used to shortcuts in SSMS, where as users coming from Visual Studio background, might be used to certain shortcuts,
which might be different from SSMS. Now for the users, who are used to Visual Studio, SQL Server 2012 allows to change the keyboard shortcuts similarly like Visual Studio.
This feature also gives ability to export keyboard shortcuts to a file or import these shortcuts from a file.

To change keyboard shortcuts like Visual Studio,

    Go to Tools -> Options -> Keyboard
    Change dropdown selection from "Default" to "Visual Studio 2010 compatible" and Click OK.

To export/import settings to/from a file, you can find it under Tools -> import/export settings

-- 9 . Cycle Clipboard Ring feature to access clipboard and previously copied items

Often we will copy various parts of a query window content to another query window. While copying we often switch between multiple windows.
Now in SQL Server 2012, SSMS supports a new feature "Cycle Clipboard Ring", which allows us to access previously copied items from clipboard.
With the help of this feature, we can copy all the queries at once in a query window and now in another query window, we can paste all those consecutively..

Eg:

    From a query window, copy the query content by pressing Ctrl+C
    Copy another query content by pressing Ctrl+C
    To access recently copied content, press Ctrl+Shift+V
    Now to access the first copied content, press Ctrl+Shift+ V two times.

-- 10. One of the challenges involved in moving/copying databases to a new location (prior to SQL Server 2012) was copying the dependencies along with the database.
External dependencies such as login names, linked servers etc are not automatically copied to the new location when you restore a regular database backup.

SQL Server 2012 (Denali) introduces Contained Databases, a new feature that allows you to embed all the dependencies right into your database.
When the database is moved/copied to a new location, all the dependencies are also moved/copied along with the database.

GO TO Database Properties -> Options -> Containment Type.


-- 11. Following permissions has been added in SQL Server 2012

    Alter any availability Group - Users having this permission can update any availability group.
    Alter any server role - Users having this permission can update server roles.
    Create availability Group - Users having this permission can create availability group.
    Create any server role - Users having this permission can create server role.

Before SQL Server 2012, we have 8 Server Roles. There was no provision to create our own Custom Server Role. Now, in SQL Server 2012, we can create our own Custom Server Role.
While creating Custom Server Role, we can configure the required Endpoints,Logins,Servers,TSQL Named pipes etc.

To add your own custom server role,

    Connect to SSMS
    In object expolorer, Go to Security-> Server Roles.
    Right click and choose "Add new server role"

Date Ranges using SQL 2012 Functions

 -- Fiscal Year : July to June
DECLARE @Today        DATE = CAST(GETDATE() AS DATE)
DECLARE @BeginDate    DATE = CAST(CASE WHEN @DateSelection = 6 -- Last 12 Months
                                     THEN dbo.fn_LocalTimeToUtcTime(STR(MONTH(DATEADD(MONTH, -12,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -12, @Today)),4))
                                     WHEN @DateSelection = 5 -- This Fiscal Year
                                     THEN dbo.fn_LocalTimeToUtcTime(IIF(MONTH(@Today) > 6, '7/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4),'7/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)) - 1,4)))
                                    WHEN @DateSelection = 4 -- This Fiscal Quarter
                                        THEN dbo.fn_LocalTimeToUtcTime(CHOOSE(MONTH(@Today),'1', '1', '1','4','4','4','7','7','7', '10', '10', '10') + '/1/' + STR(YEAR(DATEADD(MONTH, -1, @Today)),4))
                                    WHEN @DateSelection = 3 -- Last 3 Months
                                        THEN dbo.fn_LocalTimeToUtcTime(STR(MONTH(DATEADD(MONTH, -3,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4))
                                    WHEN @DateSelection = 2 -- Next Month
                                        THEN dbo.fn_LocalTimeToUtcTime(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) + 1, 0))
                                    WHEN @DateSelection = 1 -- Last Month
                                        THEN dbo.fn_LocalTimeToUtcTime(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) - 1, 0))
                                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today), 0) -- This Month
                                END AS DATE)
DECLARE @EndDate DATE  = CAST(CASE WHEN @DateSelection = 6
                                      THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) - 1, 0)))
        WHEN @DateSelection = 5
        THEN dbo.fn_LocalTimeToUtcTime(IIF(MONTH(@Today) > 6, '6/30/' + STR(YEAR(DATEADD(MONTH, -3, @Today)) + 1 ,4),'6/30/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4)))
        WHEN @DateSelection = 4
            THEN dbo.fn_LocalTimeToUtcTime(CHOOSE(MONTH(@Today),'3/31/', '3/31/', '3/31/','6/30/','6/30/','6/30/','9/30/','9/30/','9/30/', '12/31/', '12/31/', '12/31/') + STR(YEAR(DATEADD(MONTH, -1, @Today)),4))
        WHEN @DateSelection = 3
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(STR(MONTH(DATEADD(MONTH, -1,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -1, @Today)),4)))
        WHEN @DateSelection = 2
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(@BeginDate))
        WHEN @DateSelection = 1
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(@BeginDate))
    ELSE EOMONTH(@Today)
                                END AS DATE)

SELECT @BeginDate, @EndDate

Get Last 12 Months and their Start Dates

CREATE PROCEDURE P_Last12Months
AS
BEGIN
    DECLARE @Months TABLE
    (
    MID INT IDENTITY(1,1),
    MonthNumber INT,
    MonthDesc VARCHAR(15),
    StartDate DATE
    )

    DECLARE @Loop INT = 0, @Today DATETIME = GETDATE()

    WHILE (@Loop > -12)
    BEGIN

    INSERT @Months
    SELECT      IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) AS MonthNumber
            , DATENAME(MONTH,DATEADD(MONTH, @Loop, @Today)) AS MonthDesc
            , DATEFROMPARTS(IIF(MONTH(@Today) + @Loop <= 0, YEAR(@Today) - 1,YEAR(@Today)),IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) , 1)

    SET @Loop = @Loop - 1

    END

    SELECT * FROM @Months
END