Press "Enter" to skip to content

Category: Syntax

Character Generation with T-SQL

Published 2019-11-20 by Kevin Feasel

Bill Fellows shows off what you can do with character generation in T-SQL:

The mod or modulus function will return the remainder after division. Modding a value is a handy way to constrain a value between 0 and an upper threshold. In this case, if I modded any number by 26 (because there are 26 characters in the English alphabet), I’ll get 0 to 25 as my result.

Knowing that the modulus function will give me 0 to 25 and knowing that my target character range starts at 65, I could use the previous expression to print any number’s ascii value like SELECT CHAR((2147483625 % 26) + 65) AS StillB;. Break that apart, we do the modulus, %, which gives us the value of 1 which we then add to the starting offset (65).

He also provides a DB Fiddle for it.

Comments closed

Capturing Inserts and Updates in MERGE Statements

Published 2019-11-11 by Kevin Feasel

The Purple Frog folks show us how to collect the counts of insert and update operations when using MERGE statements:

This post hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement.

This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse.

You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns.

Read on for the answer. If only MERGE weren’t so riddled with problems.

Comments closed

Offset and Limit with Cosmos DB

Published 2019-10-30 by Kevin Feasel

Hasan Savran takes us through the OFFSET and LIMIT clauses in Cosmos DB:

OFFSET LIMIT clause one of the latest additions to the Azure Cosmos DB. Skip/Take function was a big request from users and Cosmos DB team listened users and deliver this functionality. If you think Cosmos DB is missing a feature and if you have a new idea, you can use Feedback Forums to give feedback to Cosmos Db team.


     OFFSET LIMIT clause let you skip x number of results then take y numbers of values from the query. Count for OFFSET and Limit are integer and both are required. In other words, You must use LIMIT if you use OFFSET.

A common use for this is paging. I’d be interested to see if this shares the issues that the SQL Server version has: you may only return back 20 rows, but you’re potentially scanning N + 20 each time.

Comments closed

Percentages of Totals in Snowflake

Published 2019-10-03 by Kevin Feasel

Koen Verbeeck shows how you can use the RATIO_TO_REPORT() function in Snowflake to determine the current row’s percentage of the total:

This episode talks about a new window function Snowflake recently introduced: RATIO_TO_REPORT. The function returns the ratio of the value of the current row to the sum of the values within the set. Or in other words, some sort of “percentage of total”. Nothing we couldn’t calculate before, but a bit of syntactic sugar so we don’t have to write two expressions.

Click through to see how to use it and a contrast with the ANSI SQL approach.

Comments closed

Removing Duplicates with Window Functions

Published 2019-09-19 by Kevin Feasel

Anvesh Patel shows how to remove find duplicate rows using ROW_NUMBER() and then delete them in a statement:

Recently, I got one request for one script to delete duplicate records in PostgreSQL.

Most of the Database Developers have such a requirement to delete duplicate records from the Database.

Like SQL Server, ROW_NUMBER() PARTITION BY is also available in PostgreSQL.

Click through for separate solutions for MySQL, SQL Server, and Postgres.

Comments closed

Pitfalls with Window Functions

Published 2019-08-14 by Kevin Feasel

Itzik Ben-Gan takes us through two issues you might run into when using window functions:

There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window frame with the implicit RANGE option. Another pitfall is somewhat related, but has more severe consequences, involving an implicit frame definition for the FIRST_VALUE and LAST_VALUE functions.

There’s a lot going on in these two examples, so read on.

Comments closed

Procedure Parameters: Optional and Required

Published 2019-07-30 by Kevin Feasel

Kenneth Fisher takes us through procedure parameters:

If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.

Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.

Comments closed

Median Calculation with T-SQL

Published 2019-07-24 by Kevin Feasel

Nisarg Upadhyay shows three ways to calculate the median in T-SQL:

To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Following is the example:

Median (M) = value of ((X + 1)/2) th item. (x is the number of values in the dataset)

Honestly, CLR’s probably the best approach here if you want a fast calculation for a reasonably large number of rows. Using ML Services and R/Python is another alternative, though the launchpad spinup time will probably make it slower than CLR.

Comments closed