Tag Archive: SQL Server

  • 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
  • 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
  • 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
  • SQL Server 2016 Licensing Changes, SP1, v.Next CTP1, etc.

    By now you’ve probably heard the exciting news announced at today’s Microsoft Connect conference; SQL Server licensing is changing, significantly, from SQL 2016 SP1. The functionality of SQL Server Standard, Web & Express editions are being brought up to [almost, 99%] match that of Enterprise Edition. This has a number of critical impacts: Developers can write applications using a single set of SQL Server functionality, and can…

    » Read more
  • SQL Battleships

    When Battleships Met SQL Server, like Bailys meeting Coffee, but better. Do you have an equal love of the classic two player board game Battleship and also SQL Server database development? If the answer is yes (which it obviously should be) then this is the blog post for you! The next question that I’m more than happy to answer for you is; how can I combine these 2x loves?… Well my friend, please continue reading to find out. So, let’s…

    » Read more
  • SQL Server Management Studio Code Snippets

    Code Snippets are not a new thing. Many languages have them and many plugins for SQL Server Management Studio (SSMS) include them. However I believe the out of the box offering in SSMS is just fine and this post explores how I use the feature and how you could too. Code Snippet Types Firstly it is important to understand that within SSMS you have two different types of code snippet. These are: Expansion – offering the insertion of a…

    » Read more
  • Using Hyper-V and PowerShell to Create the Perfect Developer Workstation

    So the second challenge I faced after unboxing and plugging in my new workstation at Purple Frog Systems was software. Having customers using all versions of SQL Server from 2005 onwards I didn’t want to tie myself to a particular version locally and I also didn’t want the hassle of running lots of different SQL Server services all on the same host operating system. Especially if I wanted to use Windows 10 as my host, which as we…

    » Read more
  • SQL Server Art using Spatial Data

    This post follows on from an earlier post on drawing with SQL Server, and explains how to create much more complex drawings using a couple of neat tricks and SQL Server spatial data. Firstly, apologies to those at my session at SQL Bits to whom I promised this blog post, I did say I’d try and get it posted in a week, and it’s been a month – but it’s here now! So, what are we trying to do? In my earlier post I demonstrated…

    » Read more
  • SQL Server User Group – Birmingham

    It’s only a week to go until the first Midlands SQL Server User Group, being held on March 10th 2011 at the Old Joint Stock pub in Birmingham. We’ve got two of the best speakers in the UK lined up, Allan Mitchell and Neil Hambly, we’re putting food on for you (pork pies and chip butties!) and it’s being held in a pub so beer will also be involved. SQL Server, pork pies and beer – all in one place?! Go on, how can…

    » Read more