Calculate Age in SQL

 I found a very cool way on StackOverflow to calculate age in SQL:

DECLARE @bday date = '6/16/1970'

DECLARE @as_of date = GETDATE()

SELECT [Age] = (0+ FORMAT(@as_of,'yyyyMMdd') - FORMAT(@bday,'yyyyMMdd') ) /10000 --The 0+ part tells SQL to calc the char(8) as numbers

C# SqlCommand Query Timeout

 Intriguing article about how to fix a query timeout from a SqlCommand in C#.

Identity Functions

 This article does a good job of summarizing the various functions you can use to grab the primary key value of the row just inserted into a table.

Here's a blurb from the post that summarizes findings:

So what are the differences, and which one should we use?

@@Identity, Scope_Identity(), and Ident_Current() are all similar functions because they return values that are inserted into identity columns. Output is not a function, but a clause we add directly into the DML statement we use.

The @@Identity function will return the last identity value inserted in the current session, in any table and in any scope.

The Scope_Identity() function will return the last identity value inserted in the current scope (and session), in any table.

The Ident_Current() function takes in a table (or view) name and returns the last identity value generated for that table, regardless of session or scope.

The output clause returns the values inserted directly from the DML statement it's a part of.

sys.modules vs INFORMATION_SCHEMA.ROUTINES

 One thing I have to remember is that INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION only returns 4000 characters, which can mean that a search could return a false negative. I'm going to let this article speak for me on the deets.

How much space does all this take up?

 Good posts on how to query the database to see how much space is used (specifically by tables, but also other types).

I like this query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

But some other good ways were presented also, such as SSMS's built-in report, and a loop over 'sp_spaceused' with 'sp_msforeachtable'.

SQL XML String Splitter

I don't use this method often enough to memorize it, so I'm making a bookmark for MSSQL Tips article on how to implement. One trick to it is that I found that the ampersand character "&" will break the XML parsing. The workaround to this is substituting another rarely found character for it before parsing, then reverse the substitution afterwards. This StackOverflow post goes into some detail about this.

Check out this site for detailed analysis of how XML string parsing compares to iterative method.

And finally, to raise this even higher, Brent Ozar has a great article about using the STRING_SPLIT function in SQL 2016 and higher, which simplifies this process immensely (although getting it to output parsed element numbers adds a layer of complexity). Check out this StackOverflow post on how to add those element numbers. Beware that although they will be consecutive, they may not start with "1", which means another pass to recalibrate.

MS-DOS Batch File Copy with Date-Stamped Name

 I created a batch file to copy the compiled exe of a Winform app, and then backup the source code to an archive folder named with the date/time stamp:

@echo off

cls

echo Date format = %date%

echo.

echo Time format = %time%

echo.

set Timestamp=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

set folder=_versions\wiki%Timestamp%\

xcopy app %folder% /s /e 

copy app\Wiki\Wiki\obj\Debug\Wiki.exe _release\Wiki.exe

I got a lot of help for this from this StackExchange entry. I found this Steve Jansen article useful also.