A Whitepaper of SQL Server 2014 Backup to the Cloud

http://sqlblog.com/blogs/john_paul_cook/archive/2014/04/17/sql-server-2014-backup-to-the-cloud.aspx

Issue with Decimal Data Type Missing Leading 0 before Decimal Point with bcp Export and a Fix

When you export data using bcp to a text file, decimal column with values less than 1  are loosing the leading 0 in the text file.

There is a workaround to fix this issue to use a varchar(20) in the place of decimal data type in a staging table or Convert/CAST the decimal data type to varchar(20) on the fly in the SELECT statement.

Date Range Sample T-SQL

http://social.msdn.microsoft.com/Forums/en-US/1f1dacda-2ae8-4c9b-90a3-bfd7182f4364/select-from-one-table-based-on-condition-from-another-table-using-date-ranges-and-criteria?forum=transactsql

 CREATE TABLE ActivityDates  ( StartDate DATE,FinishDate DATE,Criteria INT,ActivityType INT)
CREATE TABLE Calendar (  CalendarDate  DATE)
 
INSERT ActivityDates (StartDate, FinishDate, Criteria , ActivityType)
VALUES
('20140101',       '20140102',           1, 20),
('20140102',       '20140103',          1,  20),
('20140103',       '20140106',          1, 30),
('20140106',       '20140107',          1, 10), --error in your original sample
('20140107',       '20140108',          1, 10),
('20140108',       '20140113',          0, 20)

insert Calendar (CalendarDate)
values
('20140101'),
('20140102'),
('20140103'),
('20140104'),
('20140105'),
('20140106'),
('20140107'),
('20140108'),
('20140109'),
('20140110'),
('20140111'),
('20140112'),
('20140113'),
('20140114')
GO
--select * from ActivityDates
--select * from Calendar 
 

 --SELECT   CalendarDate   FROM Calendar t1
 --  WHERE  EXISTS 
 --    (SELECT * FROM ActivityDates t2 
 --      WHERE t1.CalendarDate >=t2.StartDate AND t1.CalendarDate< t2.FinishDate  and Criteria=1)

 ;with mycte as ( 
SELECT CalendarDate,
       Criteria,
       ActivityType
FROM   Calendar t1
       CROSS apply (SELECT Criteria,
                           ActivityType
                    FROM   ActivityDates t2
                    WHERE  t1.CalendarDate >= t2.StartDate
                           AND t1.CalendarDate < t2.FinishDate
                           AND Criteria = 1) d (Criteria, ActivityType) 

   )


SELECT c.CalendarDate, m.ActivityType
FROM   Calendar c LEFT JOIN mycte m  
ON m.CalendarDate = c.CalendarDate 

drop table ActivityDates, Calendar

 

Extract Value From HTML Data Column with XML (T-SQL)

create table test (htmlCol varchar(4000) )
insert into test values ('<img style=''border-style: none'' src=''../Images/flag_Green.gif'' onmouseover=''ddrivetip("<table     border=1 cellpadding=1 width=100%><tr><th nowrap width=20%>My Status</th><th>My Details</th></tr><tr><td>Green</td><td>Compliant - 06-0907370</td></tr></table>", 400, null, this);'' onmouseout=''hideddrivetip(this)''></img>')

select * from test 


SELECT distinct S.a.value('(/H/r)[2]', 'VARCHAR(4000)') AS splitVal2
 
FROM   (SELECT htmlCol,
               Cast (N'<H><r>' + Replace(Replace(Replace(Replace(htmlcol,'<','<'),'>','>'), '<td>', '</r><r>'),'</td>','</r><r>')  + '</r></H>' AS XML) AS [vals]
        FROM   test) d
       CROSS APPLY d.[vals].nodes('/H/r') S(a) 

drop table test

 

Reference: The Data Loading Performance Guide

http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Trace Flag 610

  --Trace Flag 610

INSERT INTO <DestinationTable> (<Columns>) WITH (TABLOCK)
SELECT <Columns> FROM <SomeStatement>

 

Dynamic Column Name wIth PIVOT

  create table Sales_Imports (ProductCode int, Price int, [date] date)
insert into Sales_Imports values
(1,10,'1/1/2014'),(1,109,'2/1/2014'),(1,10,'1/1/2014'),(1,109,'12/1/2014'),
(2,10,'1/1/2014'),(2,109,'2/1/2014'),(2,10,'10/1/2014'),(2,109,'12/1/2014')


declare @Year varchar(4)='2014'

DECLARE @MaxCount INT, @SQL NVARCHAR(max) ,@i INT;


Set @MaxCount = 12 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SELECT @SQL = @Sql + ', SUM(CASE WHEN datepart(mm,[Date]) = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Price END) AS ' +Left(DateName(month,Dateadd(month,@i-1,0)) ,3)+@Year
END
 
SET @SQL = N' SELECT  ProductCode, datepart(mm,Date) MonthValue ' + @SQL 
+ N' FROM  Sales_Imports
 where datepart(yyyy,Date) ='+ @Year + N'group by  ProductCode, datepart(mm,Date)
 Order by ProductCode, datepart(mm,Date)';

--PRINT @SQL;

execute (@SQL);


drop table Sales_Imports

 

UNNPIVOT and PIVOT (Matrix problem) -Dynamic T-SQL

  CREATE TABLE test (ID VARCHar(10),CMC INT,EMS INT,KBP decimal(10,2))
insert into test values
('Week1',501378,320967, 822.54)
,('Week2',13500,6000,3000)
,('Week3',34534,63563,9868)
,('Week4',32523,32532,54223)
,('Week5',235235,53453,34534)
,('Week6',34534,534534,34534)


DECLARE @MaxCount INT, @SQL NVARCHAR(max) ,@i INT;


Select @MaxCount=count(*) from test
 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
 SET @i = @i + 1;
 SET @SQL = @Sql + ',
 MAX(CASE WHEN rn = ' + cast(@i AS NVARCHAR(10)) + ' THEN val END) AS Week' + cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';with mycte
as (
SELECT ID, rn,col,val,sortOrder
FROM
(
select ID, row_number() Over(Order By ID) rn,CMC,EMS,KBP from test 
 ) AS src1 
CROSS APPLY (Values(''CMC'',CMC,1),(''EMS'', EMS,2),(''KBP'',KBP,3) ) d(col,val,sortOrder)
)
SELECT col as Product ' + @SQL + N'
FROM mycte
GROUP BY col,sortOrder';
 
--PRINT @SQL;

execute (@SQL);



drop table test

 

Check File/Folder Exists —undocumented extended stored procedures:xp_DirTree, xp_subdirs and xp_fileexist

  
  
CREATE TABLE #SubDirTree (folder varchar(200), IsFolder bit )

 INSERT INTO #SubDirTree (folder,  IsFolder)

EXEC Master.dbo.xp_DirTree 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL',1
 
 Select * FROM #SubDirTree 
 --where folder = 'Backup'

 drop table #SubDirTree

  
CREATE TABLE #DirTree (folder varchar(200), IsFolder bit )

 INSERT INTO #DirTree (folder,  IsFolder)

EXEC Master.dbo.xp_DirTree 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL',1 
 Select * FROM #DirTree 
 --where folder = 'Backup'

 drop table #DirTree

 
CREATE TABLE #folders (FileExists int, IsDirectory int, ParentDirExists int) 
INSERT INTO #folders 
EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL' 
IF EXISTS(SELECT IsDirectory FROM #folders WHERE IsDirectory=1) 
PRINT 'MSSQL exists' 
ELSE 
PRINT 'MSSQL not exists' 
DROP TABLE #folders
 
DECLARE @pathName varchar(255)
CREATE TABLE #folder (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)
SELECT @pathName='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL'
INSERT INTO #folder
(File_Exists, file_is_a_directory, parent_directory_exists)
EXEC Master.dbo.xp_fileexist @pathName
SELECT * FROM #folder
DROP TABLE #folder
GO


CREATE TABLE #folders (DirCol varchar(100))

 INSERT INTO #folders
 EXEC master.dbo.xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL'

 Select * FROM #folders
 -- where DirCol = 'Backup'


 drop table #folders


--EXEC master.dbo.xp_subdirs @pathName
--EXEC Master.dbo.xp_fileexist @pathName
--EXEC Master.dbo.xp_DirTree @pathName,1
   

 

T-SQL– Find Gap and Island for Date Range

Here is a solution for gaps and island from Alejandro Mesa:

http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx

Another one:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d3d38967-2802-43aa-ae2f-1f58b0b25ec1/min-and-max-date-of-blocks-of-subsequent-days?forum=transactsql

  
  SET NOCOUNT ON;
USE tempdb;
GO
create table #test (Item char(1), [Date] Date)
GO
Insert into #test 
VALUES
('A', '8/21/2014'),('A', '8/22/2014'),('A', '8/25/2014'),
('B', '09/12/2014'),('B', '09/13/2014'),('B', '09/14/2014'),
('A', '10/11/2014'),('A', '10/12/2014'),('A', '10/13/2014') 
GO
;with mycte as (
SELECT
    *,
    DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY Item ORDER BY [Date]), [Date]) AS grp
FROM
    #test
)
select Item, min([Date]) as[From],max([Date]) as [To] from mycte
group by Item, grp
Order by Item, grp
GO
drop table #test
GO
   

 

Date Range (Hourly) –Gap and Island Sample T-SQL

  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14 06:00','01-Aug-14 14:00')
,(123,'01-Aug-14 14:00','01-Aug-14 18:00')
,(345,'01-Aug-14 02:00','01-Aug-14 08:00')  
,(456,'01-Aug-14 18:00','02-Aug-14 04:00')
,(456,'02-Aug-14 04:00','02-Aug-14 06:00') 
,(456,'03-Aug-14 04:00','03-Aug-14 06:00') 

DECLARE @minstart_dt DATETIME, @maxend_dt DATETIME;
SELECT @minstart_dt = MIN(start_dt),  @maxend_dt = MAX(end_dt)  FROM SampleData;
 
--**** you can create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)
 
,myDateCTE as
(
select dateadd(hour,n-1,(select min(start_dt) from SampleData)) dt 
from Nums

)
 
 
, mycte as (
select dt, ID, row_number() Over(Partition by ID order by dt) rn1 from
(select distinct ID from SampleData) t1, myDateCTE  
)
 
,mycte1 as (
SELECT ID, dt, rn1, row_number() Over(Partition by ID order by dt) rn2
   FROM mycte t1
   WHERE  EXISTS 
     (SELECT * FROM SampleData t2 
       WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.ID=t2.ID)
 
       )
 
 
Select ID, Min(m.dt) As start_dt, Max(m.dt) As end_dt
From mycte1 m
Group By ID, m.rn1 - m.rn2
Order by ID
 
 
 
 
--OPTION (MAXRECURSION 0);  

drop table SampleData

/*
ID	start_dt	end_dt
123	2014-08-01 06:00:00.000	2014-08-01 18:00:00.000
345	2014-08-01 02:00:00.000	2014-08-01 08:00:00.000
456	2014-08-01 18:00:00.000	2014-08-02 06:00:00.000
456	2014-08-03 04:00:00.000	2014-08-03 06:00:00.000

*/

 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/335f29b3-1bd9-4ed3-876c-91176525fae7/aggregating-rows-based-on-contents?forum=transactsql