Friday, December 28, 2012

 

SQL Server: Choose the Right Data Types

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


Sunday, December 23, 2012

 

Several Reasons Why Coding is Hard!

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…


Saturday, December 22, 2012

 

SQL Server: Can a stored procedure cause a memory leak?

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]


    

Powered by Blogger