SQL Server Blog

Well, based on some relatively recent posts I’m pretty sure you can all guess what this will be… it’s Christmas and therefore we need a Christmas drawing!!!

Something that came up the other day when talking to a developer was that they were unsure as to whether or not they could have a foreign key relationship to a table when they didn’t necessarily have a foreign key value for all records.

I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.



Let’s presume that your database usage has changed and your transaction log has grown and become fragmented. Therefore you now want to shrink your log to remove the fragmentation and then resize accordingly to accommodate for future use.

This isn’t going to anything in depth around the subject such as what you should or shouldn’t have as your standard settings etc. but instead it’s actually just a handy piece of code I knocked together which I happen to use quite a lot, especially when in a new company, just to see how SQL Server is set up.

Now, anyone who’s read enough of my blog will probably know that I’m not too fond of the Table Variable as I see it being used too often and generally in a way that it’s not designed.



Well, this is yet another reason why I’m not a fan.

I had a discussion with someone the other day who stated that all temp tables should be dropped at the end of a procedure because otherwise they never get cleaned up.



I could understand the statement, but it’s not actually how SQL Server works because within a stored procedure temp table caching generally makes dropping redundant.

This is something I’ve never really been able to prove but have now found, courtesy of 2 VERY good posts (Grant Fritchey and Jonathan Kehayais respectively - links to follow), that it’s actually quite simple to track and monitor in order to ascertain whether or not your SQL Server is experiencing memory pressure.

I did a post a while back called “A Little Happiness in SSMS” in which I abused the Spatial Results tab in SSMS using the Geometry data type in order to make a smiley face.



At the time I did say that I’d delve a little deeper into this datatype but, as you can tell, I’ve never quite gotten around to it.



What I have done in the meantime though, is just play some more.