Colourful book case with paper files

Canada Day 2020

It’s Canada Day here, the commemoration of the country’s confederation in 1867. Given events over the last few months such as the worldwide pandemic, followed by a number of politically-motivated movements for change across the United States of America and other countries, it’s a good idea to reflect on what database design might look like
-> Continue reading Canada Day 2020

Assorted coin lot in clear glass jar

How should I store currency values in SQL Server?

On Twitter, Michael Dyrynda writes: ⚠️ Never ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever store currency as
-> Continue reading How should I store currency values in SQL Server?

A stack of coins

How SQL Server stores data types: money

In this post we look at how SQL Server stores currency values using the MONEY and SMALLMONEY data types. If you’d like to read the previous posts in this series of how SQL Server stores data types, here’s what we’ve got so far: Bit columns Dates and times Integers and decimals 🎶 It’s a rich
-> Continue reading How SQL Server stores data types: money

Blue number notebook

How SQL Server stores data types: integers and decimals

This week we’re going to look at how numbers are stored. This post will cover integers (TINYINT, SMALLINT, INT, BIGINT), and decimals (DECIMAL, which is the same as NUMERIC). If you’d like to read the previous posts in this series of how SQL Server stores data types, here’s what we’ve covered: Bit columns Dates and
-> Continue reading How SQL Server stores data types: integers and decimals

Calculator on written page

Negative identity values don’t suck

A quick(er) post this week, in response to Greg Low’s blog post from a few weeks ago titled “Don’t start identity columns or sequences with large negative values.” Greg writes that you shouldn’t use large negative values in a table, because… it’s hard to read them, I guess? And also they don’t compress well. I
-> Continue reading Negative identity values don’t suck

flat lay photography of purple and red leaves

The XML data type is not immutable

Immutability In many programming languages, strings of text are immutable, meaning they don’t change. When you modify a string, a new string is created in memory by copying the original. The old string stays in memory unless some process removes it. This might be a manual process of de-allocating that memory, or garbage collection if
-> Continue reading The XML data type is not immutable

The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short post this week. On a mailing list recently, someone noticed that a .NET application writing to SQL Server did not have the expected behaviour with UTF-8 collation and data types. To refresh our memories, UTF-8 is newly supported in SQL Server 2019, and provides potential savings of up to 50% when storing strings,
-> Continue reading The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

Post image

Dates and Times in SQL Server: What about TIMESTAMP?

It occurred to me that we haven’t covered the TIMESTAMP data type in this series about dates and times. TIMESTAMP is the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling it TIMESTAMP and use ROWVERSION instead. Much like DECIMAL
-> Continue reading Dates and Times in SQL Server: What about TIMESTAMP?

Dates and Times in SQL Server: DATETIME2

This post continues our look at date and time data types in SQL Server. SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously. This week, we look at the DATETIME2 data type. I’m not the first person
-> Continue reading Dates and Times in SQL Server: DATETIME2

watch

Dates and Times in SQL Server: TIME

This post continues our look at date and time data types in SQL Server. SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously. What is the time? This week, we look at the TIME data type. It
-> Continue reading Dates and Times in SQL Server: TIME