I wanted to create a list of numbers so I could cross join them to another set of data to generate some test data quickly. There are ways to generate numbers using a CTE (Common Table Expression) or if you’re in a real hurry you can make us of a fairly undocumented table called master..spt_values. Seeing as this table isn’t well documented don’t go using it for production code since it might change but for quick one-off tasks it seems fine.  Read →

I saw this months ago and wondered where it had gone so when I came across it by chance whilst googling I thought I would save myself a link to it. Many thanks to Raymond Lewallen who credits Leon Andrianarivony!  Read →

I came across an article on Microsofts support site which has raised a point which I’d never considered before. It basically says that if the user that executes a stored procedure is not the owner of the procedure then locks are acquired during the process to find a cached plan that may lead to blocking. It also states this could be avoided by fully qualifying the sproc name (e.g. dbo.mysproc) when calling it to avoid the extra lookups. This has been a problem for me in the past since the projects I’ve worked on had been setup with an account running as dbowner, however, if I was setting up a database from scratch I’d set up a specific user(s) for accessing the database and give it just the permissions it requires (sproc execute permissions for example.) I think, therefore, this would fall into these problem. However, I would also make use of the schema prefix (e.g. Sales.Order table) rather than leaving it as dbo and therefore avoid this problem altogether.  Read →