SQL Server

  • 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
  • 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
  • My Introduction to the SQL Server Community

    Once I was blind, but now I see! In the early part of 2015 (I think) a work colleague and friend suggested that on Thursday evening I attend the SQL Server Midlands User Group. The event was completely new to me, I’d never before ventured outside my professional comfort zone, or even had any appreciation that there was such a gathering about SQL Server locally. Anyway, I registered and got some more details. The event was currently ran…

    » Read more
  • Excel doesn’t open CSV files correctly from sp_send_dbmail

    A nice little nugget today for anyone who uses SQL dbmail to send data in csv attachments. Sometimes, depending on your language settings, CSV files don’t open correctly in Excel. Excel ignores the ‘,’ and puts all data in column A. You then have to go through the hassle of closing the file and opening it throug the flat file wizard.   There’s a very nice solution to this that I found in this thread. It’s to…

    » Read more
  • Analysis Services Tabular or Multidimensional? A performance comparison

    Can SSAS Multidimensional be faster than SSAS Tabular for distinct counts on large datasets? We’ve all seen how fast the SSAS Tabular model can work – it’s an in-memory, heavily compressed analytics database. However you may have seen my previous posts on Tabular and NUMA, which show that at the moment Tabular is limited to a single CPU. This obviously limits its scalability and maximum performance. The situation A large…

    » Read more
  • LogParser Performance Inserting into SQL Server

    Recently I’ve been using LogParser a lot to import log files, such as W3C logs from an IIS web server. The distasteful format of these files is made palatable by the awesomeness that is LogParser; it just takes care fo it for you. (Check out this SQLBits session on LogParser by Jonathan Allen (Twitter | Blog) for a good intro to LogParser) However I’ve been suffering with very poor performance with large files, so started to…

    » Read more