Why is a value in DATETIME2 8 bytes, but in BINARY it is 9 bytes?

In the #sqlhelp Slack channel on the SQL Server Community Slack workspace last month, Jemma Hooper asked: When trying to CAST or CONVERT a datetime2 value to binary(8), I’m getting a “binary or text data would be truncated” error. This seems really weird in context with the fact that SELECT DATALENGTH() on a DATETIME2 value[…]

Changes to Service Packs and Cumulative Updates for SQL Server 2017

For a few years now, Microsoft has augmented its irregular release of Service Packs with a more frequent Cumulative Update model, in order to get bug fixes and other improvements to customers faster. With SQL Server 2017, which runs on both Linux and Windows (as well as Docker containers for Linux, Windows and macOS), the[…]

Compañero Conference and SQL Modernization Roadshow

October is a busy month for me. I am flying all over the US and Canada for speaking engagements to share some thoughts about migrating your SQL Server environment to the cloud (specifically Azure). Compañero Conference I will be presenting at the Compañero Conference, which takes place over two days, October 4 – 5 (that’s[…]

construction

Does rebuilding my clustered index also rebuild my non-clustered indexes?

I’ve been working with SQL Server for many years now, and up until recently, I assumed that rebuilding any clustered index would cause non-clustered indexes to be rebuilt as well, because the non-clustered index includes the clustered index in it. This assumption is wrong. On SQL Server 2000, this only used to affect non-unique clustered[…]

A trillion and one

Joe Obbish wrote an epic post a few weeks ago about loading a trillion rows into a non-partitioned table in SQL Server, using a clustered columnstore index to maximise the compression. (Short version: it’s very slow to query. Don’t do it. Bad things happen. I have an ongoing investigation with Ewald Cress about the evil[…]

Connect Windows 10 client to a VPN on Windows Server 2012 R2

I got a strange request in a Slack channel the other day. A colleague in South Africa, who uses Windows, was unable to connect to our VPN (Virtual Private Network). We use the built-in VPN on Windows Server 2012 R2, which makes it extremely convenient to manage per-user security without opening up the firewall for[…]

Post image

What you need to know about memory limits on SQL Server

Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool. Joey D’Antoni was quick to remind me that this only applies to SQL[…]

Post image

Microsoft presents: MySQL and PostgreSQL?

For the longest time, MySQL has been the recommended database platform for blogs and other websites. It’s free. It’s available on every version of Linux, which is the most common web server platform. If you need something more complex but still free, there’s PostgreSQL. But there’s a lot going on beyond that word “free”. Because[…]