A Fine Slice Of SQL Server
Andy Brownsword has a solution:
INSERT statement exceeds the maximum allowed number of 1000 row values
Folks may tell you there are better ways to solve the issue, but the likelihood is that you’re doing it this way for a particular reason. So let’s skip the spiel and jump straight to a simple solution.
Click through to see what you can do to get around that issue.
Leave a CommentSebastiao Pereira approximates an integral in T-SQL:
The Newton-Cotes formulas are a useful and straightforward family of numerical integration, which is the approximate computation of an integral using numerical techniques. The numerical computation of an integral is also called quadrature. How can we build these formulas in SQL Server without using external tools?
Click through to see how it all works.
Leave a CommentLouis Davidson lays out the facts:
Which brings me to the point. There is a myth that goes around that you need to place filters in your SQL statements as early in the statement as possible. Most of this is due to the wild misunderstanding of how a query is executed (versus how your query is processed, which I covered last week.) The actual issue here is that the concept of filtering early is actually true, but certainly not in the way it has been taught.
SQL is a fourth-generation language and implementations approach it. With fourth-generation languages, the actual query you write is not the thing that runs, and there is an entire process to interpret what you wrote and execute operations that meet the intent of your query in the most efficient manner.
Now, this is where someone chimes in and gives all of the circumstances in which T-SQL (or pick your variant) fails to live up to its fourth-generation heritage, such as particularly complex queries, nested views with multiple joins, you using mechanisms that force a specific plan, etc. This is because real life is messy, as Louis shows in some of the examples.
So what’s the point of the first paragraph, then? Because I never miss an opportunity to talk about language generations.
Leave a CommentErik Darling here with Darling Data, and in today’s video we’re going to continue on the Learn T-SQL voyage that we have started, and I’m going to talk today a little bit about indexed view matching, because SQL Server is, let’s just call it a mature, or an experienced database engine, and is quite capable, at least in Enterprise Edition, Standard Edition, you do not pay the Microsoft Friendship Tax, so you will be taxed performance-wise, but is quite capable of matching base queries to an indexed view where the syntax matches in some way between them. So, usually exactly between them, not in some way, usually pretty close to just about what you would ask for.
Click through for several tips and, as you experience the frustration of consistently trying to make best use of the view’s index, be glad you’re not trying to get filtered views to work.
Leave a CommentAndy Brownsword tries out a new feature:
Dealing with endless formats for SQL scripts is a special type of pain you get to enjoy as a data developer. With the arrival of SSMS v22.7 last week, we have a solution in the form of a T-SQL formatter which is now available in preview.
Read on for Andy’s thoughts.
Leave a CommentLouis Davidson disambiguates a pair of terms:
There have been a lot of posts on LinkedIn of late about the “logical execution order” of a query that all really miss some really big points. I was corrected myself in some terminology because I mistook the term “processing” to mean the same as “order” in these discussions when I was explaining why logical execution order is not what people expected.
Click through for a good explanation, as well as a plan to have your company pay for your Disney World trip.
I also appreciate how Louis still calls it SQL Sentry Plan Explorer because that’s what it always will be in my heart.
Leave a CommentLouis Davidson explains the unknown:
There is no simpler topic in relational comparisons than three valued logic. I am being mostly facetious about this, but in reality, it seems so simple that people don’t think about how a
NULLworks, and make mistakes all of the time. I was reading a post about this the other day on LinkedIn (which by no means could one ever find again!) where one of the comments chastised the author of the post for not understanding “the fundamentals” of relational theory. The original poster wasn’t completely right (and my post may not be completely perfect either, though I will back most of what I write with code.)In this post I want to point out a few of the key basics that one really should understand.
Click through for a primer on what NULL means and doesn’t mean. And by the time you’re done, I’d like to interest you in the power of 6th normal form, where you can effectively banish NULL into the abyss (at least until you join the bits back together).
Comments closedEd Pollack explains a pair of operators:
There are few operators in T-SQL that cause developers to scramble for documentation more than
PIVOTandUNPIVOT. Beyond documentation, transforming columns into rows (and vice-versa) can often be confusing and frustrating for those of us tasked with reformatting data for use by an application.This article walks through
PIVOTandUNPIVOT, providing examples of simple use cases for both – as well as some more complex scenarios we can run into in real-world data. These can be extraordinarily useful ways to reformat data efficiently and quickly with less code than the alternatives. So, there is no need to fear them again!
Click through for Ed’s article. I definitely don’t fear either PIVOT or UNPIVOT and they can be quite useful. But if you locked me in a room and I couldn’t leave until I came up with the proper syntax for both from memory, well, I’d be in that room for a while.
John Deardurff takes a look at a new capability in SQL Server 2025:
Data rarely arrives in perfect condition. Typos, regional spelling differences, and inconsistent formats make exact matching unreliable in real-world scenarios. That’s where fuzzy matching comes in; and SQL Server 2025 introduces powerful built-in functions to handle it directly in T-SQL.
None of the functions are particularly novel, but it is nice to have them directly available in SQL Server, especially because Integration Services (where some of this functionality lived) has been on life support for a decade.
Comments closedErik Darling talks about an old-style way of partitioning in SQL Server:
Erik Darling here with Darling Data. And we’re going to finish off this Friday by talking about partitioned views. And look, there are a lot of things I could say about partitioned views that are great and grand and that have come in handy for me over the years in ways that I’m like, wow, thank you partitioned views. Thank you for not being normal table partitioning. Thank you for existing.
Read on to see how they work, how you can write into them, things that might prevent you from writing into partitioned views directly, and why you probably don’t want writable partitioned views anyhow.
Comments closed