SQL Server Code,Tips and Tricks, Performance Tuning

Very similar to my After 20+ years in IT .. I finally discovered this... post, I discovered yet another command I should have know about

Originally I was not going to write this post, but after I found out that several other people didn't know this, I figured what the heck, why not, maybe someone will think this is cool as well

Open up a command prompt or powershell command window, , navigate to a folder, type in tree... hit enter

Here is what I see

I was watching a Pluralsight course and the person typed in the tree command.. and I was like whoaaaa.. How do I not know this? Perhaps maybe because I don't use the command window all that much? Anyway I thought that this was pretty cool

As you can see tree list all the directories and sub directories in a tree like structure. This is great to quickly see all the directories in one shot

The same command will work in Powershell

Finally, here is an image of the command window output as well as the explorer navigation pane side by side

Hopefully this will be useful to someone

WAITFOR TIME '09:57:16'
PRINT 'DONE ' 

WAITFOR TIME '09:57'
PRINT 'DONE ' 

SELECT CONVERT(time, getdate()) --'09:57:16.9600000'
WAITFOR TIME '09:57:16.9600000'

WAITFOR TIME '09:57:16'

1
2

SELECT PARSENAME('09:57:16.9600000',2), 
PARSENAME('09:57:16.9600000',1)

 
1
2
 
SELECT CONVERT(time, getdate()) ,--'09:57:16.9600000'
CONVERT(time(0), getdate()) --'09:57:16

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DECLARE @DelayTime time(0)= GETDATE()
PRINT @DelayTime
SELECT @DelayTime =DATEADD(second,10,@DelayTime)
PRINT @DelayTime
DECLARE @d varchar(100) = @DelayTime -- you have to use varchar in command
WAITFOR TIME @d 
-- Run your command here
PRINT 'DONE ' + CONVERT(varchar(100),CONVERT(time(0), getdate()))

1
2
3
4

INSERT #temp(SomeCol, SomeTimeStamp)
SELECT COUNT(*), GETDATE() FROM sometable 
WAITFOR DELAY '00:01:00'
GO 20

USE tempdb
GO
CREATE TABLE StagingData (SomeColumn varchar(255) NOT NULL )
ALTER TABLE dbo.StagingData ADD CONSTRAINT
 PK_StagingData PRIMARY KEY CLUSTERED 
 (
 SomeColumn
 ) ON [PRIMARY]
GO

DECLARE @guid uniqueidentifier
SELECT @guid = 'DEADBEEF-DEAD-BEEF-DEAD-BEEF00000075' 
INSERT StagingData
SELECT CONVERT(varchar(200),@guid) + '.100'
SELECT * FROM StagingData

INSERT StagingData
SELECT top 999999 CONVERT(varchar(200),NEWID()) 
 + '.' 
 + CONVERT(VARCHAR(10),s2.number)
FROM master..SPT_VALUES s1
CROSS JOIN master..SPT_VALUES s2
WHERE s1.type = 'P'
AND s2.type = 'P'
and s1.number between 100 and 999
and s2.number between 100 and 999

SET STATISTICS IO ON
GO
SELECT SomeColumn FROM StagingData
WHERE RIGHT(SomeColumn,3) = '333'
SET STATISTICS IO OFF
GO

ALTER TABLE StagingData ADD RightChar as RIGHT(SomeColumn,3)
GO
CREATE INDEX ix_RightChar on StagingData(RightChar)
GO

SET STATISTICS IO ON
GO
SELECT SomeColumn FROM StagingData
WHERE RightChar = '333'
SET STATISTICS IO OFF
GO

SET STATISTICS IO ON
GO
SELECT SomeColumn FROM StagingData
WHERE RIGHT(SomeColumn,3) = '333'
SET STATISTICS IO OFF
GO

(900 rows affected)

Table 'StagingData'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Damn right, the optimizer can, , there it is, it uses the new index and column although we specify the original column..... (must be all that AI built in... (just kidding))


If you look at the execution plan, you can see it is indeed a seek


 

CREATE TABLE foo(bar int)
INSERT foo values(1),(null),(2),(3),(4),
 (null),(5),(6),(7)
SELECT * FROM foo

SELECT COUNT(*) as CountAll FROM foo WHERE bar IS NOT NULL
SELECT COUNT(*) as CountAll FROM foo WHERE bar IS NULL

SELECT COUNT(*) as CountAll, 
 COUNT(bar) as CountColumn
FROM foo

SELECT COUNT(*) as CountAll, 
 COUNT(bar) as CountColumn, 
 (COUNT(bar)*1.0/COUNT(*))*100 as PercentageOfNonNullValues 
FROM foo

(COUNT(bar)*1.0/COUNT(*))*100

SELECT ASCII(LEFT(SomeColumn,1)),
 ASCII(RIGHT(SomeColumn,1)),* 
FROM StagingData s

SELECT * FROM StagingData s
WHERE LEFT(SomeColumn,1) = char(9)
OR RIGHT(SomeColumn,1) = char(9)

CREATE TABLE BadCharacters(
 BadChar char(1) NOT NULL, 
 ASCIINumber int NOT NULL,
 CONSTRAINT pk_BadCharacters 
 PRIMARY KEY CLUSTERED( BadChar )
 )
GO
INSERT BadCharacters
SELECT char(number),number
FROM master..SPT_VALUES
WHERE type = 'P'
AND number BETWEEN 1 AND 32
OR number = 160

SELECT * FROM BadCharacters

CREATE TABLE StagingData (SomeColumn varchar(255) )
INSERT StagingData
SELECT CONVERT(VARCHAR(10),s1.number) + '.' + CONVERT(VARCHAR(10),s2.number)
FROM master..SPT_VALUES s1
CROSS JOIN master..SPT_VALUES s2
WHERE s1.type = 'P'
AND s2.type = 'P'

;WITH cte as(SELECT CONVERT(VARCHAR(10),number) as num
FROM master..SPT_VALUES
WHERE type = 'P'
AND number BETWEEN 1 AND 1000)
--INSERT StagingData
SELECT b.BadChar + c1.num + '.' + c2.num + b2.BadChar
FROM cte c1
CROSS JOIN cte c2
JOIN BadCharacters b on c1.num = b.ASCIINumber
JOIN BadCharacters b2 on c2.num = b2.ASCIINumber

CREATE INDEX ix_StagingData on StagingData(SomeColumn)

SELECT * FROM StagingData
WHERE TRY_CONVERT(numeric(20,10),SomeColumn) IS NULL

SET STATISTICS IO ON
GO

SELECT * FROM StagingData s
JOIN BadCharacters b on b.BadChar = LEFT(s.SomeColumn,1)
SELECT * FROM StagingData s
JOIN BadCharacters b on s.SomeColumn like b.BadChar +'%'

SELECT * FROM StagingData s
JOIN BadCharacters b on b.BadChar = RIGHT(s.SomeColumn,1)
SELECT * FROM StagingData s
JOIN BadCharacters b on s.SomeColumn like +'%' + b.BadChar 

ALTER TABLE StagingData ADD RightChar as RIGHT(SomeColumn,1)
GO
CREATE INDEX ix_RightChar on StagingData(RightChar)
GO

SELECT * FROM StagingData s
JOIN BadCharacters b on b.BadChar = RIGHT(s.SomeColumn,1)
SELECT * FROM StagingData s
JOIN BadCharacters b on s.SomeColumn like +'%' + b.BadChar