SQL Server Blog

There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

There are a few flaws with the sp_rename method when switching tables or even simply renaming them… note that these aren’t bugs, they’re simply annoyances encountered which can otherwise be avoided.



Let’s take a simple example and presume that we have a table, with primary key and separate index, which needs to be renamed…

These aren’t new functions as they were brought in for SQL 2012, but I was using them the other day in a SQL 2014 environment and the person I was talking to at the time had never heard of them, so I thought I’d make mention of them here in case other people hadn’t heard of them either.



These are basically functions along the same line as RANK and ROW_NUMBER in that they take an OVER clause.

In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.



I’ve seen a good few people try to achieve this in a function because this seems an easier way to produce the required result. But, as we know, functions have a lot of flaws (such as prohibiting parallelism) and therefore I wanted another approach.

Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.



This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.

This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.



In the end I got annoyed with copy and paste and decided to use a Table Type as a template.

This is something I’ve seen cause plenty of confusion and so I thought I would investigate. It’s actually lucky that I did before decommissioning my old SQL 2008 R2 server as the same proof can’t be run on SQL Server 2014 (I’ve not tried SQL Server 2012).

Well, after a short break let’s start the new year with a nice and short post.



This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.