SQL Server Blog

I was asked this the other day and it’s actually something that I’m surprised hasn’t come up before as people all seem to know it’s a bad thing, but many people don’t know why and don’t even know what one is in the first place.



Therefore I thought I would give a quick explanation for anyone still unsure what a Key Lookup is actually doing and why it’s considered bad.

This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

Well, let’s be honest here… in a nutshell a Filegroup is… wait for it… a group of files.



Bet you didn’t see that one coming.



However, it’s their uses which are actually worth discussion because they provide some useful functionality as a whole.

This is something that I’ve been hoping would make an appearance in SQL Server at some point, and also why I included my last post about tempDB contention. It’s the ability to choose the number of tempDB files during install rather than having to add them yourself at a later date.

We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.



However, where some people can fall down is that they don’t realise that tempDB can easily get overwhelmed and therefore can suffer badly from contention.

This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.



Partitioned Views are also available in any edition, which is always a bonus.

This is something I’ve had mentioned to me a lot recently… that a clustered index ensures that records must be stored in order on the data pages.



But it’s just not true.

This is a quick post about something I only found out recently and which has made my life so much easier in many circumstances. It’s something I also imagine you’ll soon use a lot too if you don’t already.

I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.