Author Archives: Deirdre O'Leary

Use Google Sheets to access protected Excel files

Aim: To access hidden sheets in Excel files with protected structures using Google Sheets. In an earlier post, I described how to unhide Very Hidden Sheets in Excel. That method does not work if the workbook structure has been protected. In this example, Sheet 2 has been hidden & attempting to unhide it using the […]

Filter Data in Outer Joins

Aim: To clarify the difference between filtering data in a WHERE clause or within the OUTER JOIN. When using an OUTER JOIN, there will be a difference in the results depending on whether the data is filtered in a WHERE clause or within the OUTER JOIN itself. The below queries have been run against a copy […]

A Good Reason to Explicitly Add Clustered Index Key to Covering Indexes

Some time ago I learned that the clustered index (CI) key is implicitly included in all non-clustered indexes (NCIs). This means that if I am creating a covering index for a query that features the CI key, I don’t need to explicitly add the key to the index. Excellent – less typing for me! However, I also […]

Use temporary stored procedures for performance tuning

I recently found out recently that when tuning stored procedures (e.g. for parameter sniffing), you shouldn’t try to tune the queries separately using local variables, e.g. DECLARE @InputParameter INT = 1; /* Query taken from stored procedure. */ SELECT [ColumeName] FROM [TableName] WHERE [Id] = @InputParameter; This is because using local variables will result in […]

Create an audit to monitor queries

As of SQL Server 2016 SP1, fine grained auditing is now available in Standard Edition. This has proved to be an easy way to monitor the querying of database objects. In my case, I use it to monitor SELECT queries on views in my data warehouse to determine which datasets are most popular with my […]

Unhide Very Hidden Sheets in Excel

Aim: To unhide Very Hidden Sheets in Excel. As part of a recent data warehouse project, I had to extract data from an Excel spreadsheet (yuck, I know!). Based on some of the formulae in the visible sheets, it was apparent that there was another sheet hidden in the file. However, it was not possible to […]

SSRS Expressions: Format Dates & Numbers

Aim: To format dates & numbers embedded in text strings in SQL Server Reporting Services. To embed formatted dates in text strings, set the expression for the text string to: =”From ” + Format(Parameters!StartDate.Value, “yyyy-MM-dd”) + ” to ” + Format(Parameters!EndDate.Value, “yyyy-MM-dd”) E.g. for @StartDate = 01/02/2017 & @EndDate = 31/03/2017, the string will appear in […]

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!