Category: Data Types

Parameter Types in DAX User-Defined Functions

Published 2026-04-21 by Kevin Feasel

Marco Russo and Alberto Ferrari talk about type systems:

In a previous article, Introducing user-defined functions in DAX, we described the syntax for creating user-defined functions, including the two passing modes (VAL and EXPR) and the fundamental parameter types SCALAR and TABLE. In this article, we build on that foundation and focus on the complete type system, with particular attention to the reference types introduced in March 2026 that provide better documentation, stronger validation, and improved IntelliSense support.

Before diving into the new types, let us briefly recap the full picture of parameter types and passing modes available in DAX user-defined functions.

Click through for a classic deep dive from Marco and Alberto.

Don’t Use FLOAT for Currency Data

Published 2026-04-13 by Kevin Feasel

Vlad Drumea has a public service announcement:

In this post I provide some examples as to why FLOAT isn’t the best option for storing exact financial data in SQL Server.

During the last couple of years I’ve seen quite a few cases of FLOAT being used to store financial data in SQL Server.
This isn’t ideal due to how the FLOAT data type behaves in SQL Server, and the official documentation makes a clear note of that.

I’m crotchety enough about this to say that you shouldn’t use any sort of decimal data type (DECIMAL, MONEY, etc.) when dealing with currency data in important financial contexts, either. I reach for BIGINT and divide by 100 (or 10,000 if you keep to four spots after the decimal) for the presentation layer.

For cases in which my data isn’t the source system of record for financial transactions, DECIMAL is fine. I don’t generally like MONEY as a data type in any circumstance, to be honest.

A Gotcha with pg_column_size()

Published 2026-04-08 by Kevin Feasel

Laetitia Avrot wants to know how large a column is:

Thanks to my colleague Ozair, who sent me a JIRA ticket saying “I need to drop that huge column, what are the consequences?” My first question was: how huge? That’s when the rabbit hole opened.

It looks simple. It is simple. Just use the administrative function pg_column_size(). Until you have toasted attributes. Then it gets interesting.

Read on for a demonstration.

User-Defined Types in PostgreSQL

Published 2026-03-30 by Kevin Feasel

Grant Fritchey dives into functionality:

I’m sure I’m not alone when I say, sometimes I get sidetracked. In this particular instance, I hadn’t intended to start learning about User-Defined Types (UDT) in PostgreSQL – I just wanted to test a behavior that involved creating a UDT. But, once I started reading, I was hooked. I mean, four distinct UDTs with different behaviors? That’s pretty cool. Let’s get into it.

Read on to learn more about why there are so many user-defined types in Postgres. Looking at the list, I do really like what they have available. You can, of course, replicate the functionality otherwise (e.g., check constraints on a regular column for DOMAIN, foreign key link to a lookup table for ENUM), but it’s nice to have those types right there for clarity of design and so you don’t accidentally forget to apply an appropriate constraint.

Comments closed

Data Type Precedence in SQL Server

Published 2026-03-09 by Kevin Feasel

Louis Davidson has a type:

There is one topic in query and equation writing that is constantly a minor issue for SQL programmers: implicit data type conversions. Whenever you don’t specifically state the datatype of an expression, like when you write SELECT 1;, it can feel a bit of a mystery what the datatypes of your literal values are. Like in this case, what is 1 ? You probably know from experience that this is an integer, but then what happens when you compare CAST(1 as bit) to the literal 1. Is that literal 1 now a bit? Or is it still an integer?

Perhaps even more importantly, why does this query succeed?

Click through to learn more.

Comments closed

BIGINT Serial Columns in PostgreSQL

Published 2026-01-21 by Kevin Feasel

Elizabeth Christensen lays out an argument:

Lots of us started with a Postgres database that incremented with an id SERIAL PRIMARY KEY. This was the Postgres standard for many years for data columns that auto incremented. The SERIAL is a shorthand for an integer data type that is automatically incremented. However as your data grows in size, SERIALs and INTs can run the risk of an integer overflow as they get closer to 2 Billion uses.

We covered a lot of this in a blog post The Integer at the End of the Universe: Integer Overflow in Postgres a few years ago. Since that was published we’ve helped a number of customers with this problem and I wanted to refresh the ideas and include some troubleshooting steps that can be helpful. I also think that BIGINT is more cost effective than folks realize.

Click through for Elizabeth’s argument. I’d say that this is very similar for SQL Server, where I’m more inclined to create a BIGINT identity column, especially because I almost automatically apply page-level compression to tables so there’s not really a downside to doing this. Identity columns don’t have a domain, so there’s no domain-specific information like you’d get with a column such as Age; and with page-level compression, you’re not wasting space.

Comments closed

Sequence Integer Overflows and BIGINT in PostgreSQL

Published 2026-01-13 by Kevin Feasel

Laurenz Albe performs a migration:

In a previous article, I recommended using bigint for sequence-generated primary keys (but I make an exception for lookup tables!). If you didn’t heed that warning, you might experience integer overflow. That causes downtime and pain. So I thought it would be a good idea to show you how to monitor for the problem and how to keep the worst from happening.

Read on for the downtime-rich solution (thanks to table blocking), as well as a solution that requires less downtime.

Comments closed

ISNULL() and COALESCE() Return Types

Published 2025-12-29 by Kevin Feasel

Andy Brownsword checks some data types:

Last week we looked at how expressions are evaluated with the ISNULL and COALESCE functions. Whilst we’re in the area it’s worth running through how data types are selected for them too. It might have implications with how you implement them.

The way these functions choose which data type to return differs, so they aren’t direct swap outs for each other, and you can’t simply ‘upgrade’ an ISNULL to COALESCE and expect the same result.

Read on to see how the two behave under different circumstances with changing data types.

Comments closed

UUIDv4 and UUIDv7 in PostgreSQL 18

Published 2025-12-09 by Kevin Feasel

Josef Machytka notes a change:

In the past there have been many discussions about using UUID as a primary key in PostgreSQL. For some applications, even a BIGINT column does not have sufficient range: it is a signed 8‑byte integer with range −9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. Although these values look big enough, if we think about web services that collect billions or more records daily, this number becomes less impressive. Simple integer values can also cause conflicts of values in distributed system, in Data Lakehouses when combining data from multiple source databases etc.

However, the main practical problem with UUIDv4 as a primary key in PostgreSQL was not lack of range, but the complete randomness of the values. This randomness causes frequent B‑tree page splits, a highly fragmented primary key index, and therefore a lot of random disk I/O. There have already been many articles and conference talks describing this problem. What many of these resources did not do, however, was dive deep into the on‑disk structures. That’s what I wanted to explore here.

In fairness to BIGINT, if you collect 10 billion records a day, that’s still about 180 million days of activity before you need to reset the ID, if I got my math right. But read on to see what UUIDv7 does for GUIDs.

Comments closed

VARCHAR or NVARCHAR

Published 2025-10-02 by Kevin Feasel

Brent Ozar asks a question:

You’re building a new table or adding a column, and you wanna know which datatype to use: VARCHAR or NVARCHAR?

If you need to store Unicode data, the choice is made for you: NVARCHAR says it’s gonna be me.

But if you’re not sure, maybe you think, “I should use VARCHAR because it takes half the storage space.” I know I certainly felt that way, but a ton of commenters called me out on it when I posted an Office Hours answer about how I default to VARCHAR. One developer after another told me I was wrong, and that in 2025, it’s time to default to NVARCHAR instead. Let’s run an experiment!

This is going back a long way (June of 2020) but one of my earliest YouTube videos is entitled NVARCHAR Everywhere. I’ve gotten a lot better at presentation skill since then (and have a much nicer camera), but I still stand by the arguments.

Comments closed