A Fine Slice Of SQL Server
Rob Douglas ran out the clock:
My preview trial of 2025 was evaluation version, and if you let that run past 180 days your SQL instance just will not start. I had an old Azure VM that I fired up to grab some code from a SQL Agent job I had been playing with and hit exactly this problem. Here’s the workaround:
Click through for the process. Fortunately, Rob doesn’t stop at how to get the instance up again, but continues into installing a non-evaluation version.
Leave a CommentMarco Russo and Alberto Ferrari check how two sets of functionality overlap:
The primary reason to adopt the new calendar-based time intelligence in Power BI is its flexibility. Classic time intelligence functions work out of the box and deliver meaningful results in most scenarios. However, to do so, they make assumptions about the calendar structure and the desired outcomes. Sometimes, the choices are not aligned with the user requirements, and developers need to author their own time intelligence calculations.
The new calendar-based time intelligence functions provide greater flexibility by allowing developers to configure parameters that drive the internal algorithms to meet diverse requirements. Using these parameters requires a precise understanding of the scenario for which they were built, which requires some attention to detail.
Click through to learn more.
Leave a CommentDeepak Mahto diagnoses a tricky difference in behavior:
You migrate a perfectly stable Oracle application to PostgreSQL.
- The SQL runs
- The tests pass
- The syntax looks correct
- Nothing crashes
And yet… the numbers or query calculations are wrong.
Not obviously wrong. Not broken. Just different.
Those are the worst bugs the ones that quietly ship to production. This is a story about one such bug, hiding behind familiar operators, clean-looking conversions, and false confidence.
Read on for the story.
Leave a CommentElizabeth 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 andINTs 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
BIGINTis 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 closedAkhil Reddy Banappagari performs a comparison:
Choosing a correct datatype mapping while migrating from Oracle to PostgreSQL is very important to avoid migration failures. Especially when we have date and time involved, it is very important to understand the behavior in both Oracle and PostgreSQL. In this article, we are going to discuss about DATE datatype in Oracle and PostgreSQL, and avoiding constraint violations while migrating from Oracle to PostgreSQL when DATE data type is involved.
I’d consider this a case where Oracle is the weird one.
Comments closedJovan Popovic makes an announcement:
We have introduced a new DATE_BUCKET() function in Fabric Data Warehouse SQL language that makes reporting and analytics even easier.
In this blog post, you’ll discover how it simplifies time-based reporting and makes grouping dates effortless.
My experience is that DATE_BUCKET() takes a bit of effort getting used to, as it is not an intuitive function. That said, it can be really powerful for dealing with time series data. It is also available in SQL Server, as of SQL Server 2022.
Brent Ozar explains why there can be gaps in identity columns:
And you use that identity number for invoice numbers, or customer numbers, or whatever, and you expect that every single number will be taken up. For example, your accounting team might say, “We see order ID 1, 3, and 4 – what happened to order ID #2? Did someone take cash money from a customer, print up an invoice for them, and then delete the invoice and pocket the cash?”
Well, that might have happened. But there are all kinds of reasons why we’d have a gap in identities. One of the most common is failed or rolled-back transactions. To illustrate it, let’s start a transaction in one window:
I have a talk on applying forensic accounting techniques using SQL and Python (as well as an older version using R) and this is one of the things I bring up. In cases where you absolutely need contiguous numbers, the best I can do for you is no identity column and a stored procedure that runs in a SERIALIZED transaction isolation level, using an app lock to prevent anybody else from calling the stored procedure concurrently, taking a table lock out on the relevant table prior to doing any real work, and hard blocking everybody else until your transaction either succeeds or fails. And I’m not even 100% sure on that if you have enough concurrency to matter.
Comments closedAdron Hall put a database in his database:
I’ve been using TimescaleDB for time-series data on and off for a while now. I recently fired up Postgres.app for local development. It’s one of the cleanest ways to get PostgreSQL running on macOS, and adding TimescaleDB is surprisingly straightforward once you know where to look.
Time-series data is everywhere—sensor readings, application metrics, user events, IoT data. Regular PostgreSQL can handle it, but once you’re dealing with millions of rows, you’ll notice queries slowing down. TimescaleDB solves this by turning your time-series tables into hypertables that automatically partition by time, compress old data, and optimize queries. The best part? It’s still PostgreSQL, so all your existing tools and SQL knowledge work exactly the same.
Read on for a demo of how it all works.
Comments closedChris Webb hits the Turbo button on his PC:
Calendar-based time intelligence (see here for the announcement and here for Marco and Alberto’s more in-depth article) is at least the second-most exciting thing to happen in DAX in the last few months: it makes many types of time intelligence calculation much easier to implement. But as far as I know only Reid Havens, in this video, has mentioned the performance impact of using this new feature and that was for Import mode. So I wondered: do these benefits also apply to DirectQuery mode? The answer is on balance yes but it’s not clear-cut.
Click through to see what Chris found.
Comments closedHans-Jürgen Schönig ran out of fingers:
The purpose of this post is not to share some fancy SQL techniques, some super cool magic AI tool that does whatever new hot thing or some crazy coding technique one can use in PostgreSQL. This is all about two simple things that are often forgotten or just neglected. We are of course talking about “awareness” and “clarity”.
Yes, it is that simple. If you don’t know what you have or if you don’t know what you want, all data is worthless (in a best case scenario) or even dangerous.
The concept for discussion here is a simple count: how many customers do you have? But even fairly simple questions like this can cause difficulty in answering because of business complexities that we need to model in our databases. The problem is that the expedient answer may not be the correct one. This is also a key reason why we end up with debates in meetings about which value of revenue to use.
Comments closed