Call today 0845 643 6463

SQL Server

  • #This vs @That – Should I use a Table Variable or a Temporary Table?

    April 17th, 2023 in Blog, SQL Server

    When working with SQL Server, it’s not uncommon to need to store data in a temporary table or table variable. While both options can be used to accomplish the same goal, there are differences between the two that can affect performance and the ability to write efficient code. Let’s explore the differences between table variables and temporary tables, and when to use each one. @Table Variables Table variables are declared using the…

    » Read more

  • Linked Server with Unique Identifier Issue

    March 2nd, 2023 in Blog, Linked Server, SQL Server, Synapse

    There is currently an issue with Linked Servers when querying and filtering by a guid (uniqueidentifier). If you query a Synapse SQL pool from your on-prem SQL server, for example, and attempt to filter the results using a uniqueidentifier column, you will get the following error message: Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 408: Incorrect syntax near ‘guid’. Msg 110811, Level 16, State 1, Line 1…

    » Read more

  • SQL Server Agent Gantt Chart in Power BI

    December 22nd, 2022 in Blog, Power BI, PowerBI, SQL Server

    In this blog post, I walk-through how to use a Power BI template file I have created that displays a Gantt chart which shows which SQL Server Agent Jobs have run and when. The purpose of the chart is to identify when jobs are overlapping and taking server resource off each other. If two or more resource intensive jobs overlap, it’s a good idea to schedule them at different times if possible. To use the template file, input the server name.…

    » Read more

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

    November 9th, 2022 in Azure, Blog, SQL Server, Uncategorised

    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

    July 28th, 2022 in Azure, Blog, SQL Server, Uncategorized

    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

    May 17th, 2022 in Blog, SQL Server, Uncategorized

    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

    March 1st, 2022 in Azure, Blog, Business Intelligence, Power BI, Power Query, SQL Server, Uncategorized

    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

    February 25th, 2022 in Azure, Blog, Data Factory, SQL Server, SSIS, Synapse

    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

    October 15th, 2021 in Blog, Integration Services, SQL Server, SSIS, Uncategorized

    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

    March 5th, 2021 in Azure, Blog, SQL Server, Synapse

    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