-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Gavin Draper's SQL Server Blog

Software Dev/DBA living the dream in sunny Brighton, UK. I've been playing with SQL Server about nearly 20 years now and still feel just as clueless as I did back then. I love writing about things I find as I go along.

Archives: May 2017

SQL Server Parameter Sniffing In Depth

If you’ve ever searched for SQL Server parameter sniffing you’ve probably read all sorts of bad things about it. In truth parameter sniffing is an optimization technique SQL Server uses to allow plans to be cache and reused with different parameter values and for the most part it works well… Read more

0 comments, 60 reads

Posted in Gavin Draper's SQL Server Blog on 31 May 2017

SQL Server Filtered Indexes By Example

What Are Filtered Indexes

Filtered indexes are essentially an index with a predicate on them filtering the data that gets stored in the index. For example on a bug tracking system I could creatre a filtered index that just indexed bugs with a status of open.

This post will make… Read more

0 comments, 37 reads

Posted in Gavin Draper's SQL Server Blog on 30 May 2017

SQL Server Window Functions in Action

SQL Server Window Functions were introduced in SQL Server 2005 with a basic set of operators and massively upgraded in SQL Server 2012 to include a lot more operators.

A window function is a function that takes a window descriptor which describes the subset of rows on the overall dataset… Read more

0 comments, 28 reads

Posted in Gavin Draper's SQL Server Blog on 23 May 2017

Introduction To SQL Server Statistics

SQL Server statistics are often thought of as a bit of a black box, this is completely not the case and I want to use this post to detail what they are, how they work and how we can view what they’re doing….

What Are Statistics

Statistics data is information… Read more

0 comments, 25 reads

Posted in Gavin Draper's SQL Server Blog on 22 May 2017

SQL Server Clustered & NonClustered Indexes Explained

When using row level indexes there are two types Clustered and NonClustered both of which are there to make data easy to find and sort. Before we look at these index types lets go over a couple of things

  • Both these indexes are stored as B-Trees
  • Root node is the…

Read more

0 comments, 47 reads

Posted in Gavin Draper's SQL Server Blog on 16 May 2017

Data Warehousing Explained

Why Warehouse

Data warehousing is the act of transforming application database into a format more suited for reporting and offloading it to a separate store so your day to day transactions are not affected. This process typically involves flattening the data.

Let’s imagine we have an online store

SELECT
    dbo

Read more

0 comments, 31 reads

Posted in Gavin Draper's SQL Server Blog on 14 May 2017

Using The SQL Server Merge Statement

Note : Since publishing this I’ve been notified that there are a number of outstanding issues with the SQL Server merge statement. I currently work with a system the heavily relies on the Merge statement and we’ve not knowingly hit any of these issues but it is worth knowing about…

Read more

0 comments, 28 reads

Posted in Gavin Draper's SQL Server Blog on 8 May 2017

SQL Server Dynamic Pivot

Often when trying to pivot data you wont know what the possible values that you need to pivot on, in this case you can use dynamic SQL.

Lets say we have a table with employee sale counts

Employee Sales
Gavin 10
Troy 4
Joe 3

We can pivot this data… Read more

0 comments, 24 reads

Posted in Gavin Draper's SQL Server Blog on 7 May 2017

Using JSON In SQL Server 2016

The new JSON bits in SQL Server 2016 give you the ability to pull stuff out of JSON and to convert relational sets to JSON. This is similar to the XML features in SQL Server that have existed for some time now.

Lets create a table that we can run… Read more

0 comments, 40 reads

Posted in Gavin Draper's SQL Server Blog on 6 May 2017

C#7 Show Me The New Stuff!

I’m going to walk through an example that we can build up and improve with a number of the new C# 7 features.

Lets say we have a method that takes an object and tries to convert it to a number. There are two ways it does this

  • If it’s…

Read more

0 comments, 21 reads

Posted in Gavin Draper's SQL Server Blog on 5 May 2017

SQL Server Pivot and Unpivot Explained

The Pivot and Unpivot features in SQL Server are I find quite underused. For a long time I got by with SELECT, JOIN, GROUP in SQL Server and missed out on some really handy features like PIVOT, CROSS APPLY, MERGE, XML and more recently JSON. Each of these features is… Read more

0 comments, 27 reads

Posted in Gavin Draper's SQL Server Blog on 4 May 2017

Why CROSS APPLY is AWESOME

CROSS APPLY was introducted as part of TSQL in SQL Server 2005. Origionally it was created as a way to join on table value functions. They are the perfect tool for TVF joins however what I’ve missed out on until recently is how they are also a great tool for… Read more

0 comments, 22 reads

Posted in Gavin Draper's SQL Server Blog on 3 May 2017