A Fine Slice Of SQL Server
Steve Jones links to Steve Jones talking about Steve Jones:
I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.
Click through for an example of how to do this.
Comments closedTomaz Kastrun figures out a useful way to play a game while looking like you’re doing important work:
Connect 4 is a classical board game, consisting of a board of 6 rows and 7 columns and 42 tokens (of two colours, each player with 21). The first player to get four tokens in a row, column or diagonally, wins!
Simple game rules:
1. only one token can be added at the time
2. each player have their own colour (sign) of tokens
3. the game is ended, when the first player connects four tokens in a row, column or diagonally
Actually, I take that back: Tomaz has figured out a useful way for two people to play a game while looking like they’re doing important work. That’s twice as good.
Comments closedSteve Jones points out one of the changes to T-SQL I really like:
This assumes I’ve added a table called dbo.Order with a PK of OrderID.
However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.
This came about in SQL Server 2014, along with In-Memory OLTP and the ability to create indexes inline with the table create script. It’s a minor quality of life thing but I do enjoy it.
Comments closedEtienne Lopes has some fun with a feature:
Before 2012, creating analytical queries (that usually scan many rows and have lots of aggregations) from big OLTP databases to feed real-time based reports used in decision making processes, could be quite challenging. Then ColumnStore Indexes arrived and they’ve been enhanced overtime, offering amazing gains both in performance and storage.
Unfortunately, regarding pure OLTP databases, there are many situations in which ColumnStore Indexes can’t (or won’t) be used. There are some great performance enhancements present in columnstore that’s for sure and today I’m going to speak about one that became automatically available since SQL Server 2019 for “traditional” RowStore tables. It’s called “Batch Mode on Rowstore” and it can really boost some of our analytical queries over the “traditional tables” without any effort from our side!
There are a series of specific rules you need to hit but if you hit them, I’ve noticed about a 3x performance gain with you doing nothing at all.
Comments closedThe SQL Server TDS protocol provides two main paths for query execution (Language and RPC events.) You can trace these events using the Batch::Starting/Completed (Language) and RPC:Starting/Completed (RPC) XEvents.
Language events are text streams requiring full parsing and may be susceptible to injection attacks. Language events also require educated guesses. For example, should select 1 return a smallint, bigint?
Bob goes on to show an example of a simple call being susceptible to SQL injection, explains why Remote Procedure Calls (RPCs) are superior, and what packing (or batching) RPCs does for you.
Comments closedChad Callihan converts a date:
CAST and CONVERT can both be used to switch a value to a new data type. They are similar, but certainly not identical. While CAST is considered ANSI SQL and will get you across the finish line, CONVERT can give you more flexibility when it comes to formatting date values. Let’s look at an example comparing the usage of CAST and CONVERT with dates.
Most of the time, I’ll use CAST() over CONVERT(), not so much because the former is ANSI compliant, but rather because I think it’s more intuitive to remember. Date formatting is one of the few occasions in which I usually prefer CONVERT() and that’s precisely because of the format options. Of course, if you want more custom formatting options, you can use FORMAT(), though that function uses .NET in the background and is remarkably slow. It’s fine if you’re formatting a few dates, but if you’re outputting millions of rows, you will certainly see a marked difference.
An underrated, and perhaps less well-known T-SQL function is QUOTENAME(). If you haven’t heard of it, chances are that it could do wonders for your dynamic SQL scripts.
Read on to see what QUOTENAME() does and one important limitation to it. Daniel does have a feedback item to remove this limitation, so upvote if this is something you’d like to see change.
Aaron Bertrand wants to solve the actual problem:
I’ve quietly resolved performance issues by re-writing slow queries to avoid
DISTINCT. Often, theDISTINCTis there only to serve as a “join-fixer,” and I can explain what that means using an example.
I’ve seen this a lot as well, and it usually comes from people not understanding the data model or not understanding how to use subqueries (or common table expressions, the APPLY operator, etc.) to define subsets of data.
Comments closedHugo Kornelis performs a survey:
The MERGE statement compares source and target data, and then inserts into, updates, and deletes from the target table, all in a single statement. This statement was introduced in SQL Server 2008. I liked it, because it allows you to replace a set of multiple queries with just one single query. And while a statement with that many options necessarily has a more complex syntax, I still believe that, in most cases, a single MERGE statement is easier to read, write, and maintain, than a combination of at least an INSERT and an UPDATE, often a DELETE, and sometimes first a SELECT into a temporary table if the source is complex.
Click through for a review of a variety of problems people have had in the past. It surprised me a bit when I learned how few of these issues were still active problems caused by MERGE.
Comments closedItzik Ben-Gan lays out some fundamentals:
When people start learning a new field, for example T-SQL, it’s tempting to spend very little time trying to understand the fundamentals of the field so that you can quickly get to the advanced parts. You might think that you already understand what certain ideas, concepts and terms mean, so you don’t necessarily see the value in dwelling on them. That’s often the case with newcomers to T-SQL, especially because soon after you start learning the language, you can already write queries that return results, giving you a false impression that it’s a simple or easy language. However, without a good understanding of the foundations and roots of the language, you’re bound to end up writing code that doesn’t mean what you think it means. To be able to write robust and correct T-SQL code, you really want to spend a lot of energy on making sure that you have an in-depth understanding of the fundamentals.
No matter your knowledge level, there’s a really good chance you’ll learn at least one new thing in this article.
Comments closed