Changing a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do ALTER TABLE…ALTER COLUMN as an online operation using the WITH (ONLINE=ON) … [Read More]
I was fiddling around with some data, looking at making a quick aging report. I probably should have reached for Power BI Desktop, but sometimes I can be stubborn, and I wanted to get my T-SQL to work–if nothing else to figure out how to … [Read More]
If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine) Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation … [Read More]
Last week, I wrote about the SQL Server Version Store. The week before, I looked at crash recovery. Let’s look next at a new feature in SQL Server 2019, where these two features intersect: Accelerated Database Recovery. What is it? Accelerated Database Recovery(ADR) is a … [Read More]
Maybe you’ve read about the Read Committed Snapshot (RCSI) or Snapshot Isolation levels. Or maybe you were trying to figure out the difference between the two snapshot isolation levels. Perhaps you’ve looked into read-only secondary replicas in your Availability Group, or even had some problems … [Read More]
If you’ve ever had a server reboot unexpectedly, you’ve probably seen SQL Server start up, with some databases “stuck” in recovery status, then eventually they come online. Or maybe you thought “this session is stuck in rollback. It’ll be quicker if I just reboot the … [Read More]
Earlier this year, I published the first crack at a Data Blogger Resource Kit. When I initially created the kit, I wasn’t quite sure exactly what direction I would go with it. In the months since, I’ve started to get a better idea of where … [Read More]
I won’t be at PASS Summit this year, so unlike last year, I won’t be organizing an LGBTQ Meetup at PASS Summit. I’m hoping that even without the meetup, all my LGBTQ #SQLFamily friends will still get together for some fun and networking. Keep scrolling … [Read More]
There is a corner case where it makes sense to have a non-clustered index “duplicating” the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index.
I was recently chatting with a coworker, discussing why there is no discrete DROP permission for tables, procedures, etc. The conversation actually came from a #SQLHelp question on SQL Slack that was asked along the lines of “What if I want to let a user … [Read More]
Copyright © 2020 | Andy Mallon | am2.co