Jared Westover

I was thrilled to get SQL Server 2025 Developer Edition up and running in just a few minutes! Here are the steps I took: 1. Install the free Docker Desktop if you don't already have it. Docker Desktop download docker.com/products/docker-d… 2. Pull the latest container image for 2025 (only took a few seconds). 3. Build and launch a container using the image from step 2. Ensure you follow the password requirements for SA; otherwise, the container won’t start. Also, pay attention to the port in my example; I used 51432:1433. You can find the container image and more detailed guidelines on the Microsoft Learn site. learn.microsoft.com/en-us/sq… Kevin Feasel uploaded a video on running a container. Although not specific to 2025, these steps remain applicable. youtube.com/watch?v=7cgIpreX…

What do deadlocks and the T-800 have in common? One of my favorite action movies growing up was Terminator 2, where the resistance sends the T-800 back in time to save John Connor. Like the T-800, a deadlock in SQL Server can seem scary, but it’s a protector. Deadlocks are a protective byproduct of SQL Server’s locking mechanisms and are resolved by the engine’s built-in deadlock detection logic. Deadlocks typically occur when locks are taken in an incompatible order, though other factors can also play a role. Without the deadlock mechanism, transactions could remain in contention indefinitely. If you occasionally encounter a deadlock, SQL Server fulfills its role by selecting one transaction to roll back. However, the rolled-back transaction may fail to persist valuable data unless you implement retry logic. Several methods can help reduce deadlocks, including ensuring proper indexes, accessing objects in a consistent order, and batching operations to reduce the number of locks taken. Additionally, you can always add retry logic to capture data effectively. No, I didn't use ChatGPT to come up with this content, so if it's terrible, it's on me. However, I did use ChatGPT for the image.

SQL is a declarative language, meaning you specify the data you want rather than how to retrieve it (of course, there are hints and other means of influence). In contrast, an imperative language like Python (which isn’t purely imperative) focuses on the steps necessary to achieve results. With declarative languages, it's similar to ordering food at a restaurant: you ask the chef (or more likely the server) for the dish you want, but you don't tell them the steps to prepare it unless you're that kind of person. Providing guidance, such as cooking the steak medium, adding garlic butter, or omitting onions, is like using filters that refine your order to exactly what you want, similar to predicates in a WHERE clause that filter rows based on specific criteria.

OLTP and OLAP Serve Different Purposes OLTP and OLAP systems both store data, right? Can't you use a lakehouse or a warehouse as your transactional database? I don't believe that’s wise. Each is fundamentally designed and optimized for different types of workloads. That said, there are Hybrid Transactional and Analytical Processing (HTAP) solutions like Azure Synapse or Snowflake Unistore that blur the lines. Even the Columnstore index in SQL Server represents a hybrid analytics blend. Maybe a simple way to think about the two systems is: OLAP = Analysis (Heavy reads & aggregations on large volumes of data) OLTP = Actions (Writes and reads small amounts of data frequently) These two bullets aren’t perfect, so I’m open to suggestions for improving them.

When a heap is small, say under a few thousand rows, SQL Server primarily relies on exclusive (X) RID locks and a few intent exclusive (IX) or (UIX) page locks during data deletion, of course, depending on the amount of data deleted in the transaction. With this smaller size, you typically don’t encounter deadlocks unless you access the table in a pattern that tends to generate them. However, when a heap grows to many thousands of rows, SQL takes more locks. If the threshold of lock escalation is met, an object (table) lock occurs. These factors all contribute to rampant deadlocks. This is why one day you start to see deadlocks on a table that didn’t experience them even without any code changes. If possible, consider adding a clustered index to see if it helps. Heaps are great for many things, but are not usually ideal for updating and deleting thousands of rows. To view the locks in action, take a look at the DMV [sys.dm_tran_locks].

If you are not already doing so, always exercise caution when granting access to query a production database. There are a multitude of reasons to keep users out not the least reducing exposing sensitive data. Following this advice is easier said than done. People often complain and insist they know how to write SQL, which may indeed be true. You might say, "Well, it’s just a single view; what harm can they do?" For starters, they could consume all the CPU, interfering with a database's primary objectives of collecting and storing data. I've included an article about how a user could consume all the CPU with access to query a single view. Technically, you don't even need access to the view. Additionally, I listed a few ways to alleviate the threat when you can't simply say, "No access for you!" mssqltips.com/sqlservertip/8…

Mistakes → Learning Failure → Feedback Feedback → Growth Growth → Mastery ~ Unknown

You can't always use an equality predicate (e.g., =, IN, EXISTS) in the WHERE clause. However, if possible, try to do so, because with inequality (e.g., <>, NOT IN, NOT EXISTS, etc.), SQL will generally perform a scan rather than a seek. For example, depending on the data distribution, the SQL optimizer chooses an index seek for the second statement, even though these two statements return the same number of rows. Granted, the performance difference isn't mind-blowing. /* * Inequality */ SELECT Id FROM dbo.MagicNumbers WHERE Number3 NOT IN ( 1, 2, 3, 4, 5, 6, 7 ); /* * Equality */ SELECT Id FROM dbo.MagicNumbers WHERE Number3 IN ( 10, 9, 8 );

I've always had trouble relating a deadlock in SQL to a real-world example. With that in mind, I'll give it a shot. Imagine my two sons, Henry and Liam. They both want to play video games. Henry picks up the only controller and starts searching for the Mario Kart cartridge. Liam has the cartridge in hand but wants to play first and is waiting for the controller. Now, they're stuck, each holding onto something the other needs and not willing to give up. That’s a deadlock. We could easily solve this by purchasing another controller and suggesting the boys take turns deciding who goes first. But that would destroy my example.

To think, I have to write. I'm not the first person to make that statement. A quote from David McCullough: "Writing is thinking. To write well is to think clearly. That's why it's so hard." If ChatGPT does all my writing, I am not thinking. Do I use ChatGPT? I certainly do, every single day, but it does not do my writing or thinking for me. However, it helps facilitate both processes. My writing serves as a personal artifact of my thoughts. ChatGPT totally created the image below.

Should you skip Repeatable Read for Snapshot Isolation in SQL Server? The answer depends on your specific workload. What does Repeatable Read do? It prevents a non-repeatable read from occurring. A non-repeatable read happens when a transaction retrieves the same row multiple times, and another transaction updates that row, resulting in a different value. Repeatable Read works by holding locks for the duration of the transaction, which reduces concurrency (writers block reads). However, Snapshot uses row-versioning, which does not block readers but allows for the possibility of write conflicts and the need to retry a failure or let the end user try again.  The bottom line is that I would at least consider using Snapshot if you are thinking about using Repeatable Read.

Have you ever wanted to demonstrate two T-SQL scripts side by side in SQL Server Management Studio? Making this happen is easy. 1. Open your scripts. For this example, let's keep it simple and choose two. 2. Right-click one of the script tabs and choose New Vertical Document Group. Like magic, your scripts are now side by side. This layout is perfect for demonstrating deadlocks and isolation level topics. By the way, I'm NOT going back to SSMS 20. Don't tell Microsoft, but I might be willing to pay for SSMS 21.

Over the years, I've collected numerous technical books, primarily on SQL and a few on C# and Python. Two of my favorite authors are Ken Henderson and Itzik Ben-Gan. I seldom read them from cover to cover. Instead, I pick one up when I'm interested in exploring a specific topic (isolation levels, for example) and read the corresponding chapter. Eventually, I may read the entire book, but I'm fine if I don't. I'm curious to hear how others approach technical books. Do you read from cover to cover, or do you prefer specific chapters?

You often hear SQL experts say that using SELECT * in a statement is an anti-pattern or bad practice, and I agree. The two biggest issues people encounter include: 1. SELECT * can return a lot of data to the client, which is wasteful if you don’t need it. Imagine a table with over 30 columns, and you only need 2; that’s a lot of unnecessary overhead. What if you have multiple NVARCHAR(MAX) columns? 2. If you consistently use SELECT *, you are probably not leveraging performance-enhancing indexes, because you're accessing the heap or the clustered index directly.

Want to get tons of comments about a SQL Server post? Refer to CASE as a statement. It’s easy to mistakenly call a CASE expression a CASE statement, even though it's technically an expression within a statement. If you want to kindly correct someone, consider sending a direct message if that’s an option. It’s like telling a coworker they have lettuce stuck in their teeth after lunch. We’ve all made mistakes and still do. It doesn't diminish the value you bring if you can’t remember which SQL Server version or edition supports a particular feature. That’s why we have books, notes, and this new thing called the internet, so we don’t have to remember all this information, and it is just information. As David Alan said, “Your mind is for having ideas, not holding them.” For the longest time, I referred to GO as a statement until someone pointed out in a comment on a course I taught that it's a command. After reviewing the Microsoft document, I thought, "Well, Duh." Remember that we are all still learning, one statement or expression at a time.

Ken Henderson describes concurrency as the simultaneous access and modification of a dataset by multiple users. In contrast, consistency refers to having accurate data at the beginning and end of a transaction. Imagine a seesaw; on one end is concurrency, and on the other is consistency. When you use an isolation level like Read Uncommitted, the concurrency side touches the ground. Conversely, if you use Serializable, the balance shifts to the consistency side. In other words, having more of one means having less of the other. It's like balancing free time and money.

I don’t believe someone can go from zero experience to learning a language like SQL in a day, just as I don’t believe someone can learn Python, C#, or JavaScript in a single day. Would someone expect to be fit after meeting with a personal trainer once? That said, you can start learning SQL or any language in a day. If you keep starting and practicing, you’ll become proficient as you gain more experience. Cultivate a habit of applying your new skills through online challenges or ask ChatGPT to create one for you.

One often-overlooked benefit of a nonclustered index in SQL Server is its impact on data deletion. When used properly, indexes can significantly enhance performance when retrieving rows for deletion. However, don’t overdo it, as indexes introduce extra overhead.

Wow, time flies! It seems like yesterday I created my favorite @pluralsight course. I used your feedback to rebuild Optimizing Query Performance with Columnstore Indexes. Please check it out at the link below. 👇 pluralsight.com/courses/opti… #sqlserver #pluralsight #mssql

Wow, time flies! It seems like yesterday I created my first @pluralsight course. I used your feedback to rebuild Troubleshooting Your First Low-performing T-SQL Query. Please check it out at the link below. 👇 #sqlserver #pluralsight #mssql pluralsight.com/courses/trou…