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 […]

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 […]

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 […]

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 […]

Aim: Rename objects in SQL Server Data Tools (SSDT) without breaking dependencies. You can rename SSDT objects in their definition (.sql) files however this does not update the name of the object throughout the project. Renaming an object in this way will break any references to the object in dependent objects (e.g. views or stored […]

Aim: To remove unnecessary information from SSMS query tabs, to make them more useful & easier to read. I was reminded of this very useful customisation in SSMS the other day while reviewing Brent Ozar training notes. By default, SSMS includes the file (query), server, database & login names in the query tab. Unless these […]

Aim: To script multiple database objects using the Object Explorer Details window in SQL Server Management Studio (SSMS). It’s not possible to script multiple objects at the same time from the Object Explorer window in SSMS. However, by opening the Object Explorer Details window from the View menu, you can navigate to the relevant folder […]

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

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!