Here's another product suggestion I've logged with Microsoft along the same lines as the previous one. This one is to allow you to dynamically specify the list of columns you want to unpivot. This might be from a stored procedure parameter or a subquery, either way you can't do it easily at the moment. Register your support here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332512

I've logged a suggestion with Microsoft for support for the inclusion of NULLs when you use the UNPIVOT operator. Add your support if you think this would be useful: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332325 Read more if you want to see why this feature is useful...

Auditing the actual changes made to a table can be quite complex, but is often required. In SQL Server 2008, you can use the new feature Change Data Capture, but in 2005 you have fewer options. Here's an approach using common table expressions, table variables and a nice, big and hopefully fast query!

I've been looking at the performance of SQL Server 2008, following the recent SQL Bits conference (see http://www.sqlbits.com), and have been looking at creating some test data for my analysis. This is probably old stuff to most of you, but I hit an interesting issue...

Have you ever wondered how to view object permissions using a query? Read more...

Do you have lots of tables that reference each other using ID fields? Are they of different types, such as nvarchar(10) and int? Do you sometimes find that it can be hard to remember how many characters should be in the type? And what about when you decide that you need a bigger type, such as nvarchar(30)? How can you ensure that your database design remains consistent?

I'm in the process of reviewing a large number of SQL stored procedures, and occasionally I find that a table join isn't properly expressed. This can result in hard to find bugs, but how can you make your SQL code simpler? Isn't using a stored procedure best-practice anyway?

I've just found this by chance while in the process of reviewing around 100 stored procedures to check for performance efficiency. It doesn't help with badly written queries, but if the query is reasonable then it can help you find those elusive missing indices. Thought I'd have to find them myself, but thankfully help is at hand...

You may know that it is best practice to create FOREIGN KEY constraints on related tables in a database. Why is this?

Read more here...

Following on from my previous post, here's a quick way to find out which data tables are using the most disk space...

I'm in the process of reviewing a database for performance analysis. One of the most important tasks to perform is reviewing of statistics. Because this database is currently live, I cannot get the locks to use the sys.dm_db_index_physical_stats built-in function. I have an Oracle background and didn't know how to get the row count quickly. Here's my solution...

How can you make your Commerce Server reports easy to maintain, and how can you make it work with long SQL queries? Did you even know that long SQL queries won't always work?! Read more here...

What is a malicious trigger? What can it do? How can you protect yourself and your database server? Read more here...

Sometimes you need to use a temporary table within a SQL Server stored procedure. How can you make sure that this will work? Read more here...

It is the responsibility of a stored procedure to manage the transaction context of the session. An error can result when the transaction depth on entry and exit from a stored procedure do not match. This may occur when a transaction is rolled back in a stored procedure due to an error. Read more to find out how to address this issue.