SQL Developer Ramblings

I often find that not many people truly harness the power of SQL Server Management Studio (SSMS).  In the next few quick tips, I’m going to highlight some nice functionality that can help in your everyday use of SSMS. In this tip, I’m going to talk about Templates, how to use them, how to create your own and where on your system they are being stored.

Download Source Files: Here Another excellent addition to SQL Server 2008 was the introduction of the row or table constructors.  In this quick tip I’m going to illustrate how to use the row constructor inside INSERT and SELECT statements.

Download Source Files: Here One of the frustrating things about using the SQL Server PIVOT function is that you need to explicitly name the field values that you want to be pivoted across the horizontal access of your result set.  The problem with this is that this list of values could change over time as new data is added to the database.  So what's the solution?  In this post I’ll demonstrate how you can create a stored proc that will take a SQL query, derive the unique values in a column and dynamically create the SQL necessary to generate PIVOT table based on these values.

Download Source Files: Here In this post I will illustrate a high performance method for calculating running total by traversing a table index. I’ve seen many example of people using sub queries and CROSS JOIN’s in order to accomplish the same thing, the only problem with these is the overhead of joining so many rows together.  In this example I’ll illustrate how to traverse an index and create a running total at the same time.

Download Source Files: Here In this post I will illustrate how you can multiply rows in a table together without using a cursor.