Data Analytics with T-SQL: LEAD ()

This will be the first of a series of posts where I showcase some functions you can use in SQL Server to analyse your data. At the end of this post we will look at using the LEAD() function in a real world use case.

This week we will be checking out the LEAD() function.

What does it do?

LEAD() selects a value from a subsequent row in the same result set. You can specify the row by declaring an offset. This is useful to compare values in the current row with values in a following row.

The Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] )   
    OVER ( [ partition_by_clause ] order_by_clause )

Advertisement

TinyInt vs SmallInt vs int vs BigInt, does size matter?

Size matters – I don’t care what your girlfriend says.

When creating tables, deciding on what data types to use is important in regards to long term performance from your SQL Server. In this post we will be comparing tinyint, smallint, int and bigint.

We will be looking at how storage size and memory usage differs between the data types, and how this impacts performance.

I created four tables for each data type, each with five columns and five million records. Below is an example of one of those tables, using tinyint.

CREATE TABLE [Tinyint] (
Col1 tinyint,
Col2 tinyint,
Col3 tinyint,
Col4 tinyint,
Col5 tinyint
)

This isn’t a ‘real world’ example, but the purpose of this post is to showcase the impact on performance between the data types – at scale, the difference will become more apparent.

Non-SARGable Queries cause Performance Issues and you may be writing them without knowing

A non-SARGable query refers to a query that is not able to properly utilise an index, due to its Search ARGuments.

In this post, all examples are demonstrated using the WideWorldImporters database.

The following are examples of non-SARGable queries:

-- Example 1
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE (CONVERT(NVarChar, OrderID)) = 10 

-- Example 2
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE Description LIKE '%Pack%'

Does the Order of Columns in an Index Matter?

Have you ever been in the situation where you were creating an index that required more than one column in its key and thought, “Hmm I wonder if the order here matters?”.

To put it simply: yes, it does. The order of the columns matters when it comes to improving performance of queries in your SQL Server.

In this post we’ll be going through a few short examples and observing how different column orders behave with the same query.

I used the sample Stack Overflow database (50 GB) for the demos.

Let’s look at the query below:

 SELECT LastEditorDisplayName, Score, ViewCount
 FROM Posts
 WHERE LastEditorDisplayName = 'Roger Pate'
 AND ViewCount > 500