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

Advertisements

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