John McCormack DBA

SQL Server Databases and AWS Cloud Solutions

  • Scripts
  • Guides
  • Personal
  • About

How to write a PIVOT query in T-SQL

12th January 2016 By John McCormack Leave a Comment

sql server pivotIn this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[Read more…]

Filed Under: front-page, T-SQL Tagged With: aggregation, grouping, How to write a PIVOT query in T-SQL, PIVOT, PIVOT Operator, spreading, SQLNEWBLOGGER, t-sql, T-SQL PIVOT, t-sql scripts

Index maintenance using Ola Hallengren’s scripts

17th July 2015 By John McCormack Leave a Comment

Having recently set up Index maintenance using Ola Hallengren’s scripts on a range of my company’s production servers, I wanted to write about the steps involved including some custom steps to ensure the overall health of the SQL Server database.

The aim of this blog post is to be not too technical, I don’t cover what Ola has documented on his site however I do include scripts for my custom steps. I will only cover index maintenance, although these scripts give the options for statistics maintenance also. This is because we already had a custom solution for statistics maintenance that was working great, whereas we needed to make improvements to our index maintenance solution.

[Read more…]

Filed Under: front-page, Guides Tagged With: dba, dba scripts, index maintenance, index rebuild, index reorganize, ola hallangren, t-sql scripts

John McCormack · Copyright © 2019