Category: Uncategorized

Erik Darling has a video for us:

So we’re going to talk about just some not terribly advanced stuff, but stuff that is at least worth making sure that everyone understands when it comes to the date diff function.

There’s not terribly a lot of advanced things to say about it, but who knows where you’re starting off. So one thing that seems to get on some people’s nerves is deciding on a boundary.

So when you say, I only care about a year of data, you need to think carefully about how you do that. 

This is one tiny part of how working with dates is so painful, and not specifically due to any gap in tooling but just in how complex date and time logic is. And that’s before we even think about the date math involved in working with the Future Semiconditionally Modified Subinverted Plagal Past Subjunctive Intentional.

Leave a Comment

Qiaosheng Liu makes an announcement:

We started pg_ducklake in January, and today we are releasing v1.0, the first production-ready version. Three things define this release:

1. Full DuckLake workflow. v1.0 brings most of DuckLake’s feature set to PostgreSQL SQL: DML, schema evolution, time travel, partitioning, sorted tables, the variant type, ACID transactions, and automatic maintenance. Every table stays open to any DuckDB client.
2. Production-grade. pg_ducklake is tuned to live inside an OLTP database: stable under continuous load, disciplined with memory, and offering the fastest ingestion path for DuckLake.
3. Standalone and compatible. pg_ducklake started as a fork of pg_duckdb. v1.0 no longer forks or depends on it. It is a self-contained extension, built on a reusable kernel we extracted called libpgduckdb, and it stays compatible with pg_duckdb.

Read on to learn more about what’s in this production release.

Comments closed

Josephine Bush doesn’t need enormous system databases:

Always set this on your SQL Servers so you don’t have this problem in the first place. This is in the SQL Server Agent settings. I remember having some agent jobs that used to serve this function that ran on a schedule, which may have been required in older versions of SQL Server.

Josephine focuses on SQL Agent history and database backup history, both of which are good ones. If you have an older version of SQL Server or are using the package deployment model, there may be an explosion of information in msdb regarding SSIS that you’d want to manage. Also, check if any of the databases are in Full recovery mode; if so, ensure that the backup script you’re using for transaction log backups actually backs up system databases.

Comments closed

Hugo Kornelis digs into one type of cursor:

I have used the GLOBAL scope option, so that it is possible to step through the code one statement at a time. This option does not affect the execution plans used. The FORWARD_ONLY and READ_ONLY options are probably the most commonly used options with cursors, especially with static cursors. We will briefly look at the effect of other options for read direction and concurrency at the end of this post.

Click through to learn more about how cursors show up in execution plans and how you can tell, based on the execution plan of a static cursor, why it’s either really fast (relative to other cursor types) or really slow.

Comments closed

I have a new video:

In this video, we will configure SQL Server on Linux to use Active Directory and enable Windows authentication, whether you’re connecting from Windows or Linux.

Also, thanks to a viewer request, I finally got my lazy bones self to include the commands that I’d enter into the console during these videos.

Comments closed

Steven Sanderson builds a chart:

The ts_growth_rate_vec() function is part of the healthyR.ts library, designed to work with numeric vectors or time series data. It calculates the growth rate or log-differenced growth rate of the provided data, offering valuable insights into the underlying trends and patterns.

Read on to see how this function works, as well as several examples of plotting growth rates of airline data which exhibits both strong cycles and an overall trend.

Comments closed

Hugo Kornelis offers a mea culpa:

In my post, I pointed out that SQL Server automatically creates a unique nonclustered index on the (internal and hidden) column graph_id, that you can delete, but can’t modify. I added that users cannot specify indexes on that column, nor on the other internal and hidden columns that exist in edge tables (from_obj_id, from_id, to_obj_id, and to_id). These statements are factually correct, but misleading.

I then concluded from those facts that once you delete the automatically generated index, you can not re-create it. This is actually incorrect.

Read on for the correct answer, as well as more information on indexing practices and advice on the right kinds of indexes to create on graph tables.

Comments closed

Vladimir Khorikov hits on a topic of particular interest:

On the front-end, the duration is represented as 1h 47m. But how should we store it in our database? What data type should we use?

We could keep the duration as a 1h 47m string, just as we render it on the screen, but what if we later decide to change the string format? We might need to display it as 1:47 or 107 minutes instead. Do we then parse all the existing strings and convert them into a new format?

I like the thrust of this article. Two things I think people forget about in data modeling are ledgers and events, instead trying to store the results of these. Ledgers and events can be slower—though there are things we can do to pre-calculate more user-friendly results and speed up the process—but they provide you auditability, flexibility in how you want to calculate and display the information, and the ability to correct errors over time.

Comments closed

Itzik Ben-Gan twiddles some bits:

The need to manipulate data at the bit level with bitwise operations isn’t common in T-SQL, but you might stumble into such a need in some specialized scenarios. Some implementations store a set of flags (yes/no, on/off, true/false) in a single integer or binary-typed column, where each bit represents a different flag. One example is using a bitwise representation of a set of user/role permissions. Another example is using a bitwise representation of a set of settings turned on or off in a given environment. Even SQL Server stores some flag-based data using bitwise representation.

Here’s the deal. I don’t mind that this new syntax exists, particularly because—as Itzik points out—there are areas built into SQL Server which use integers to store bit flags. In application code, however, this gets a sharp “No!” from me in any code review. If you need to decompose values in your table as a matter of course, your table is not in first normal form. Having a table not be in 1NF isn’t the end of the world but at that point, I think the onus is on the developer to defend the violation at that point.

Comments closed

Erik Darling explains an important difference:

When you use views, the only value is abstraction. You still need to be concerned with how the query is written, and if the query has decent indexes to support it. In other words, you can’t just write a view and expect the optimizer to do anything special with it.

SQL Server doesn’t cache results, it only caches raw data. If you want the results of a view to be saved, you need to index it.

And naturally, those indexed views are different from materialized views in Oracle/PostgreSQL but that’s a topic for another day.

Comments closed