SQL Server

  • What are SQL Server Index Fragmentation and Index Fill Factor?

    What is index fragmentation and how does it occur? It is important to know that SQL Server data is stored in data pages, with each page holding 8KB of data. There are two types of fragmentation, both are a result of these pages not being used as efficiently as possible. When you UPDATE or INSERT data on a page that is already full, SQL Server creates a new page. The information from the original page will be split 50/50 with half being added to…

    » Read more
  • Query Store Forced Plan Failures

    Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server. Here we can see an…

    » Read more
  • The SQL Query Alias Conundrum – Order of Execution

    So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘. The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it? If you’ve ever written a SQL query, this should look familiar: SELECT DISTINCT FROM JOIN ON…

    » Read more
  • Combining Queries from Multiple Sources in Power BI using Merge and Append

    It is always good practice to do as much data preparation as close to the sources as you can before importing or connecting them to your Power BI reports, but what if there are circumstances where this isn’t possible? I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.…

    » Read more
  • Execute SQL Task in ADF

    If you’re new to ADF or Synapse pipelines and looking for a way to execute SQL commands in pipelines, unfortunately there is no equivalent to the Execute SQL Task in SSIS, but I have found some alternatives which work well for me. For something as simple as a SELECT statement where you want to return results to be used later, you can use a Lookup activity: You can then reference the output of this activity in other activities using dynamic…

    » Read more
  • Automate changing SSIS connection at runtime

    Recently a customer came to us with an issue: “We have a primary/secondary AlwaysOn failover cluster, we want the data warehouse ETL to always pull from the secondary read-only replica. When the primary fails over, how can we detect this and repoint the ETL to the new secondary?” This led us to the following qualification steps:1. Is the secondary server accessible? a. Yes – Use it. b. No – Try the primary server.2. Is…

    » Read more
  • Azure Synapse Series: Hash Distribution and Shuffle

    For this post I’m going to presume you’ve already taken a look at distributing your data using a hash column, and you’re not experiencing the performance you’re expecting. (If you’re not already aware of what this is, take a look at the following link to learn the basics of what a distributed table is and why you need it in Azure Synapse. I’ll be here when you get back.)…

    » Read more
  • ADF Switch Activity – A neat solution for nested IFs.

    Whilst carrying out some work for a client using Azure Data Factory I was presented with the challenge of triggering different activities depending on the result of a stored procedure. In fact the challenge posed was to… Execute ‘Copy A’ activity if the result of a stored procedure returned (A), Execute ‘Copy B’ activity if the result of a stored procedure returned (B), Execute ‘Copy C’ activity if the result of a stored…

    » Read more
  • My experience in obtaining an MCSE

    Background I have recently completed my MCSE in Data Management and Analytics, and I wanted to share my experience of working towards and passing the exams that have led to me getting this MCSE. In this post I will cover some of the challenges I faced and provide some tips and advice, hopefully others following the same certification path, or other paths, will find this useful. I am a business intelligence developer at Purple Frog, I have…

    » Read more
  • SQL LAST_VALUE() wrong results

    You may have come across the following scenario when using the LAST_VALUE() function. You want to find the first or last value loaded in a result set, therefore you use the FIRST_VALUE() and LAST_VALUE() functions like below: You expect to get “A” for every record in the FirstValue column and “E” for every record in the LastValue column.However you see the following output: LAST_VALUE() on its own is implemented from the…

    » Read more