List Calendar Dates

I use this to bounce against my weekly ETL’s to make sure that every day of the week data is captured. This helps me quickly identify a Calendar date with no ETL date which means no data was pulled.

DECLARE @start_date DATETIME= ‘2017-01-01’;
DECLARE @end_date DATETIME= GETDATE();
WITH CalendarDates
AS (
SELECT CAST(@start_date AS DATE) AS Calendar_Day
UNION ALL
SELECT DATEADD(DAY, 1, Calendar_Day)
FROM CalendarDates
WHERE Calendar_Day < @end_date)

SELECT Calendar_Day,
DATENAME(dw, Calendar_Day) AS Day_of_Week,
DATENAME(week, Calendar_Day) AS Week_Number
FROM CalendarDates OPTION(MAXRECURSION 0);

 

Advertisements

LIKE Regular Expression

Here’s a great trick to find specific patterns for a column. The LIKE CLAUSE can be use as a RegEx

Here’s a link to LIKE from Microsoft.

Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol	Meaning
LIKE ‘5[%]’	5%
LIKE ‘[_]n’	_n
LIKE ‘[a-cdf]’	a, b, c, d, or f
LIKE ‘[-acdf]’	-, a, c, d, or f
LIKE ‘[ [ ]’	[
LIKE ‘]’	]
LIKE ‘abc[_]d%’	abc_d and abc_de
LIKE ‘abc[def]’	abcd, abce, and abcf

 

In this example, I want to find all user names that starts with the letter “S” and is follow by numbers 0-9.

 

SELECT Text File using OpenRowSet

Here’s a simple query to directly SELECT a Text File.

HDR = Header Row. If YES, that means your first row is a header.

SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Text;Database=E:\;HDR=YES’, ‘SELECT * FROM Log.txt’)

If you need the Microsfot ACE OLEDB 12 driver, please refer to  Microsoft Jet OLEDB 12

 

Why use SQLCMD

What’s wrong with using SQL Server Management Studio (SSMS)? Nothing! What’s wrong with you! Well, I’m in a hurry.

As much as I love GNR Patience, I don’t have any… well not so much of it. Thanks Smart Phone era.

I always use SQLCMD when SSIS is not available– not all of my customers install SSIS along with SQL. I once had a project where I had to scan through terabytes of files to find certain image files. Once the files were found, it would immediately write the path to the database. Since this had to be performed and completed over the weekend, I ran hundreds instances of my C# application to seek out the files.

So here’s how it works

In the command prompt (cmd.exe), run sqlcmd /? and you’ll see the following

There’s a lot of options and the ones I frequently use are
-S Server Name
-E Trusted Connection
-Q Query

In the example below, I’m querying a table, however you can always call a stored procedure to INSERT/DELETE/UPDATE/SELECT your data instead of writing out the query– SP is safer

I hope this gives you some insight as to why/when you would use SQLCMD

How to get the Previous and Next Row Data

If you’ve ever wrote reports that requires comparing numbers from previous weeks, months, quarter, and year SQL 2012 and later versions will solve your issue.

In this example, I have a table call HBClaimsRemits_DL_NW. Everyweek I bring in new data into this table. However, I would like to see if the data grew or shrank.

To do this, we use LAG and LEAD. Both of these functions requires that we use PARTITION so that we’re getting the LAG and LEAD data for the specific group of data.

LAG gets the data from the previous record. If no previous record exists, a NULL value will return.

LEAD gets the next row data. If no future record exits, a NULL value will return

Here is the script to show Previous (LAG) and Future (LEAD) records.

SELECT
Name,
RecordCount AS Current_Record_Count,
ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Previous_Record_Count,
ISNULL(LEAD(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Next_Record_Count,
CreateDate
FROM SSIS_Log.dl.event

I like to simplify the results to something more of an Indicator (KPI).

Here is the script with an Indicator field. There’s more scripting involved but the end results is easier to read.

SELECT
Name,
RecordCount,
CASE
WHEN ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) = 0 THEN ‘No Changes’
WHEN RecordCount ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0)
THEN ‘Up by ‘ + CAST(FORMAT(RecordCount – ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0), ‘##,###’) AS VARCHAR(20))
END AS Indicator,
CreateDate
FROM SSIS_Log.dl.event

Truncate Transaction Log

Hopefully, you’re not visiting this page in a panic because you or your users are not able to use the Database because it’s out of space. Keep your sanity and strategically plan out the database maintenance plan to prevent issues like this from surfacing.

Before you truncate the transaction log, please remember that if you want to recovery any data before the truncation, that data will no longer exists. Before you truncate the log files, you’ll need to know the exact file name. Here’s how you do that.

Go to the database properties

Database Properties

Under Files, you’ll see the file log name.

File Log Name

SQL 2005

USE MyDatabaseName
GO
BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

SQL 2008, 2012 R2, 2014

Steer away from WITH TRUNCATE_ONLY in these versions because it no longer exists.

If your database is already set in SIMPLE recovery mode, then every time you do a backup, the transaction log will be automatically truncated for you.

If your database is in FULL recovery mode, then here are a couple of steps

USE MyDatabaseName
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

If your database is already part of a maintenance place that backups the data then use this.

BACKUP LOG MyDatabaseName_Log TO DISK = N’C:\Backup\MyDatabaseNameDB.bak’
GO

Have a cup of BEETS juice and good luck!

Table Size

There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.

Here are 3 methods but not limited.

1. Get a specific table size

EXEC sp_spaceused N’dbo.MyTable’

2. Get all table size

CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’

SELECT * FROM #TableSize ORDER BY Name ASC

3. Custom Code

SELECT
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.OBJECT_ID > 255
GROUP BY t.Name
,s.Name
,p.[Rows]
ORDER BY t.Name