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.