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.

SSMS Hotkey - Metadata Dump

I feel a bit late to the game on this, but I just found out about Alt-F1, which when pressed while an object is highlighted in an SSMS window, will display metadata for that object. For procedures it displays parameters and their types, for table it will show columns, etc. 

Var Assignment SELECT Gotcha

I got stumped by something I haven't seen in awhile. Question - what's the value of @objid at the end of this?

DECLARE @objid int = -1
SELECT @objid = [OBJECT_ID]
FROM sys.objects WHERE 1=0
SELECT @objid
I coded a script with the assumption that it would be null if the WHERE condition is not met, the same way a column would be null. But instead, it kept its original value.

To fix:

DECLARE @objid int = -1
SELECT @objid = CASE WHEN 1=0 THEN [OBJECT_ID] END
FROM sys.objects
SELECT @objid

This will provide the results we wanted.

SQL FOR XML for string concatenation

Just a quick link to a great tip of using the FOR XML clause to concatenate values in rows into a single string (grouped by another column).

These posts go into the mechanics of the FOR XML, and some debate as to the pros and cons.

SQL Merge

I recall a few years ago that the SQL MERGE statement had some problems, and decided that I'd avoid it by just coding its constituent parts (INSERT, UPDATE, DELETE). Today I was wondering if it still had problems, and came across this scoresheet from MSSQLTips. The use cases with problems seem mostly outliers, but there are enough issues that I have trouble feeling confident about the underlying technology. For now, I'll keep coding it by hand.

Complicated Comments

This is a simpler version of a post I made 10 years ago.


DECLARE @x int

/*

-- */

SET @x = 1

-- /*

--*/ SET @x = 2

PRINT @x





Question: after running the statement above, what will the output be?

1. 1
2. 2
3. Null
4. An error occurs.

Removing 100% Duplicates

In a table that contains duplicates as identified by some business rule (same name/address, etc.), but the rows included in the sets of duplicates are actually 100% the same, it's impossible to identify a "survivor" of a de-duplication process that deletes all but one of the rows in each set. There is a way in T-SQL to do this, by using the TOP(n) clause after the DELETE. It's kind of unusual, and you should be careful with it. Here's an example:

CREATE TABLE #src (x int, y int, z int)
GO
INSERT #src VALUES (1, 2, 3)
INSERT #src VALUES (1, 2, 3)
INSERT #src VALUES (1, 2, 3)
INSERT #src VALUES (10, 20, 30)
INSERT #src VALUES (100, 200, 300)
GO
SELECT * FROM #src

At this point, #src looks like this (I cannot seem to remove the odd white space in this table):
x   y   z
1   2   3
1   2   3
1   2   3
10  20  30
100 200 300

Now let's remove our duplicates:
DELETE TOP(2)
FROM #src
WHERE x = 1

SELECT * FROM #src

And we see the results:
x   y   z
1   2   3
10  20  30
100 200 300

In the DELETE TOP(n) part of the statement, n=#dupes - 1. In our case, we had 3 duplicates and removed 2 of them. Which brings up an important point - we used the "x=1" condition to identify the group, and we knew that it had exactly 3 rows in that dupe group. So if we generalize the problem, we may want to use dynamic SQL to build a solution:

; WITH DupeGroups AS (
 SELECT
  [x]
  ,[cnt] = COUNT(*)
 FROM #src
 GROUP BY [x]
 HAVING COUNT(*) > 1
)
SELECT

 [sql] = 'DELETE TOP(' + CONVERT(varchar, [cnt] - 1)

 + ') FROM #src WHERE [x] = ' + CONVERT(varchar, [x]) + ';'

FROM DupeGroups


This can then be executed using sp_SqlExec to remove the duplicates.

Default Schema

I'm working at a site where prior developers referenced objects without schemas, because everything is done under 'dbo'. I fell into this habit, and this led to me creating a table with no explicit schema - which means it was created under a schema tied to my login. When I later tried to reference the table in code, using the implicit 'dbo' schema, I got an error that it didn't exist, when it certainly seemed to exist! It took a  bit to track down the error, and it was a 'smack my forehead' feeling when I found it. Lesson learned: don't get lazy about explicit schemas.

Multiple UNPIVOTs

This is a great article about using multiple UNPIVOTs simultaneously on a data set, which I hadn't seen before.

Conversion failed when converting the varchar value '*' to data type int.

Interesting error today: "Conversion failed when converting the varchar value '*' to data type int." I partially recognized it as a conversion issue, when an int value with more digits than the target varchar column has, it will hold the value "*". A later process tried to convert it back to an int.

SSMS Slowness

I’ve had some Hulk-like moments dealing with SSMS’s tendency to burn through all available memory, push usage to near 100%, and then freeze when I try to continue working in it. Classic “turn it off and turn it on again” fix works sometimes. I’ve even threatened it with uninstalling it (I think it knows I was bluffing). But today – today – I found a silver bullet to kill the beast. This post recommends turning off Auto-recovery. I tried it – and it works. Before, memory for SSMS was allocated at around 2.5gb, but now it’s a sweet and cuddly half gig. Yup, 80% reduction. Now, you may flinch at turning off Auto-recovery, and in principle I agree. The thing about that is, when SSMS has completely crashed with it on, it doesn’t always recover my most important documents. So, if you’re sick of SSMS slowness, consider this.

CREATE TABLE Script for Temp Table

Following up on the earlier queries that interrogated INFORMATION_SCHEMA.TABLES to get metadata on temp tables, this script builds on those to produce a CREATE TABLE statement. I find this useful for capturing the table-typed output of a stored procedure, especially if I need to capture that output in an INSERT..EXEC statement.

DECLARE @sql varchar(max); SET @sql = 'CREATE TABLE #capture_output (' + CHAR(13) + CHAR(10)
; WITH struct AS (
SELECT *
FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#tmp'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))
)
SELECT
@sql += CHAR(9) + ',' + QUOTENAME(struct.[COLUMN_NAME]) + ' ' + struct.[DATA_TYPE]
+ ISNULL('(' + CASE struct.[DATA_TYPE]
WHEN 'varchar' THEN CONVERT(varchar, struct.[CHARACTER_MAXIMUM_LENGTH])
WHEN 'decimal' THEN CONVERT(varchar, struct.[NUMERIC_PRECISION]) + ', ' + CONVERT(varchar, struct.[NUMERIC_SCALE])
END
+ ')', '')
+ CHAR(13) + CHAR(10)
FROM struct
SET @sql = REPLACE(@sql, '(' + CHAR(13) + CHAR(10) + CHAR(9) + ',', '(' + CHAR(13) + CHAR(10) + CHAR(9)) + ')'
PRINT @sql

I realized that I don't have a link to a tip on the FOR XML trick for concatenating values over rows into one value. I like this one: https://www.mytecbits.com/microsoft/sql-server/concatenate-multiple-rows-into-single-string

Classic Mistake

Today I was working on an ETL feed that parsed data elements from a single column of a single string value. The table held a log from an enterprise build tool, and we wanted to analyze the logs to find patterns of unusually slow builds.

https://dba.stackexchange.com/questions/218073/the-disappearing-act-of-the-invalid-length-parameter-passed-to-the-left-or-subs

DISTINCT with TOP n

I came across an uncommon query need to use DISTINCT and TOP n in the same query, and it made me realize that I wasn't sure how they would interact. Which would take precedence? I decided to create a simple example to help understand this.

DECLARE @x TABLE (gender char(1))

INSERT @x VALUES ('M')
INSERT @x VALUES ('M')
INSERT @x VALUES ('F')
INSERT @x VALUES ('F')

SELECT DISTINCT TOP 2 gender FROM @x ORDER BY gender

If the TOP 2 took precedence and acted first, it would take the top 2 values of gender, 'F' and 'F', and then remove duplicates in the DISTINCT clause, resulting in one row of 'F'.

If the DISTINCT acted first, it would have the preliminary result of two rows, 'F' and 'M', and then the TOP 2 would return just that. 

 

When we run this code, we get two rows, 'F' and 'M', showing that the DISTINCT takes precedence over the TOP 2.

Clever workaround to the limitation of 900 byte index widths

Clever workaround to the limitation of 900 byte index widths: https://www.brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server/.

View defined as SELECT * FROM Another View

This has been covered previously, but is so insidious that it deserves more attention. Take a look at the code below. Fairly straightforward, right?

CREATE VIEW V1 AS SELECT [object_id] FROM sys.objects
GO

CREATE VIEW V2 AS SELECT * FROM V1
GO

SELECT TOP 10 * FROM V2
GO

Now let's modify the definition of view V1, such that the column definition includes [name]:

ALTER VIEW V1 AS SELECT [name], [object_id] FROM sys.objects

GO

SELECT TOP 10 * FROM V2

GO

See how the definition of V2 has not changed, even as the data that it contains has? No error has occured, which would have alerted us to the issue. This illustrates one of the biggest risks of using "SELECT *" in SQL code.