Ed Wagner (6/8/2016)

---

jasona.work (6/8/2016)

---

I had to (politely) beat on an end-user for running this against their database the other day:

SELECT A.COL1

, B.COL2

FROM TBLA A, TBLA B

There are only around 5...

Quick solution

😎

USE TEEST;

GO

SET NOCOUNT ON;

declare @x xml = '<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS"><SSIS:Parameter SSIS:Name="FileFolderName"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{aaeb860f-66f6-40b9-aa99-487b880767d8}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></SSIS:Property><SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property><SSIS:Property SSIS:Name="Required">0</SSIS:Property><SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property><SSIS:Property SSIS:Name="Value">C:\TransferIn\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter><SSIS:Parameter SSIS:Name="ArchiveFolder"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{0b640567-dc04-44fe-9a3f-0494135326e2}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></SSIS:Property><SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property><SSIS:Property SSIS:Name="Required">0</SSIS:Property><SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property><SSIS:Property SSIS:Name="Value">C:\Archive\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter></SSIS:Parameters>'

;WITH XMLNamespaces ('www.microsoft.com/SqlServer/SSIS'...

drew.allen (6/6/2016)

---

Eirikur Eiriksson (6/6/2016)

---

Quick thought, the isnull statement is redundant ad null is never equal to null. It will on the other hand impede performance as the resulting execution plan...

Excellent article Alan, very nice work indeed!

😎

Quick thought, the isnull statement is redundant ad null is never equal to null. It will on the other hand impede performance as the resulting execution plan will use scans...

Piling on, here is an easy way to remember this

😎

DECLARE @X_BYTES BIGINT = 10240000000000;

SELECT

@X_BYTES / POWER(CONVERT(BIGINT,2,0),10) AS KILO_BITES

,@X_BYTES / POWER(CONVERT(BIGINT,2,0),20) ...

Would be interesting to know which method you choose, there is up to 25 times difference...

No mistery here, just a minor mistake in the third parameter of the LEAD function which is specifying 0 as the missing value / default. Simply replace this with NULL...

Jeff Moden (6/4/2016)

---

Which query is "this" query? A lot...

ffarouqi (6/4/2016)

---

I am in a need to track database log file growth. I googled a lot but did not get the right way to track what is causing the log...

gideon_king (6/2/2016)

---

Was looking at deprecated features in future versions of SQL in the sys.dm_os_performance_counters table. Trying to figure out what code changes we need to start implementing.

Most of...

Quick suggestion

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp;

CREATE TABLE #temp

(

STORE_ID INT

,PRINTER_NM VARCHAR(50)

,PRIORITY_IND INT

,PRINTER_ID INT

)

INSERT INTO #temp

values(1000,'PRINT1',1,100)

,(1000,'PRINT2',2,101)

,(1001,'PRINT1',1,102)

,(1002,'PRINT2',2,103)

,(1002,'PRINT1',1,104)

,(1002,'PRINT2',2,105)

,(1003,'PRINT1',1,106)

,(1003,'PRINT2',2,107);

SELECT

T.STORE_ID

,MAX(CASE WHEN T.PRIORITY_IND =...

Do you have a BYOD discount? 😛

😎

SQL-DBA-01 (6/1/2016)

---

Even two single quotes will also work fine.

INSERT mytest

VALUES

(1, CAST('<customer id = ' '1200' '>Acme</customer>' AS XML))

, (2, CAST(...

Elementary when using the DelimitedSplit8K function

😎

USE TEEST;

GO

DECLARE @testdata TABLE

(

IDINT NOT NULL,

DegreeStr VARCHAR(100),

YearEarnedStrVARCHAR(100)

);

INSERT INTO @testdata

SELECT 1, 'BS,MS,PhD', '2001,2005,2011' UNION ALL

SELECT 2, 'BS', '2003' UNION ALL

SELECT 3, 'BS,MS', '2002,2008'

SELECT

...