Mitch Wheat - Treat the cause, not the symptoms!

An oldie but a goldie from Aaron Bertrand:  “Bad habits to kick : choosing the wrong data type”

I was looking up something Unicode related and I came across Jon Skeet’s talk on why coding is hard: why do fundamental datatypes such as floating point, strings and DateTime cause so many problems. The slide deck and transcript is here.

The only thing I’d add is that arithmetic might be easier if we had 12 digits rather than 10, rather than the 8 mentioned, but that’s another story…

Yes, a memory leak is possible if you forget to call sp_xml_removedocument (for each matching sp_xml_preparedocument):

A parsed document is stored in the internal cache of SQL Server. The MSXML parser (Msxmlsql.dll) uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

Example usage:

DECLARE @xml_text VARCHAR(4000), @i INT

SELECT @xml_text = '<root>
                      ... some valid xml ... 
                    </root>'

EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

....

EXEC sp_xml_removedocument @i

Another form of memory leak is forgetting to both close and deallocate a cursor:

DECLARE c CURSOR   
  LOCAL STATIC FORWARD_ONLY READ_ONLY   
  FOR SELECT ...

    ....

CLOSE c; 
DEALLOCATE c;

Just for the record, even though I always like to see an explicit CLOSE and DEALLOCATE for cursors:

LOCAL cursors are implicitly de-allocated when the stored procedure, trigger, or batch in which they were created terminates, unless the cursor has been passed back as a parameter. The LOCAL cursor will then be implicitly de-allocated when the parameter or variable referencing the cursor in the code that called the procedure goes out scope.

Ref.

[Note: I rarely use cursors. Wherever possible and appropriate, I always try to do it the set-based way]