A Fine Slice Of SQL Server
Andy Brownsword uses the QUOTENAME() function:
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the
QUOTENAMEfunction.
My personal preference is to use exec sp_executesql for parameterization instead, simply because it’s a lot harder to mess up.
Erik Darling shares some advice:
I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:
- When I have a list of literal values
That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.
I’m typically a lot more flexible about using IN, though I do agree with NOT IN: that clause is usually more trouble than it’s worth.
In our data hungry world, knowing how to effectively load and transform data from various sources is a highly valued skill. Over the last couple of years, I’ve learned how useful many of the data manipulation functions in PostgreSQL can supercharge your data transformation and analysis process, using just PostgreSQL and SQL.
For the last couple of decades, “Extract Transform Load” (ETL) has been the primary method for manipulating and analyzing the results. In most cases, ETL relies on an external toolset to help acquire different forms of data, slicing and dicing it into a form suitable for relational databases, and then inserting the results into your database of choice. Once it’s in the destination table with a relational schema, querying and analyzing it is much easier.
I call out CROSS JOIN LATERAL (or any kind of lateral join) here because it’s the ANSI equivalent of T-SQL’s APPLY operator, and I’ve already pointed out once today that I’m a huge fan of APPLY.
Erik Darling gets an auto-link for talking about my favorite operator:
I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.
One of the most common questions I get is when developers should consider using apply over other join syntax.
The short answer is that I start mentally picturing the apply syntax being useful when:
To learn when, you’re going to have to read the whole thing. And, if you want to learn even more about it, I have a talk on the topic that might be of interest.
Comments closedAndy Brownsword breaks out the stopwatch:
The
TOPclause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. In contrast, when usingMAXwe’re applying a function to our data to select the largest value from our data.Let’s dive into some examples with the StackOverflow data, specifically the Votes table.
Read on for several scenarios and how the two perform. Things get a bit more complicated as you introduce other tables in joins and similar additional factors, but this gives you a good foundation for comparison.
Comments closedErik Darling plays Descartes in his spare time:
Let’s talk about two of the most overlooked and undervalued facilities in the SQL language: EXISTS and NOT EXISTS. Perhaps they’d get more traction is they were called THERE and NOT THERE, but but it would be perilously easy to start confusing your WHERE and THERE clause.
Often besmirched by someone who read a blog post about MySQL in 1998 as “subqueries, which are slower than joins”, they’re two of the most useful things you can grasp and use regularly.
This post does a great job of explaining when you’d want to use EXISTS and NOT EXISTS.
Ed Pollack deals with the lack of regular expressions in T-SQL:
This article is focused on how to write, simplify, and get the most out of string data using the least amount of code to do so. It’s a reminder that code complexity is equivalent to technical debt and that in these scenarios, less is always more.
Text-manipulating functions will be introduced and reviewed throughout this article, so feel free to perform searches against it to find what you are looking for.
Read on for mechanisms to split strings into multiple rows, build strings from multiple rows, and search+replace within strings.
Comments closedTobias Petry shows off a feature in Postgres:
SQL is a straightforward and expressive language, but it sometimes lacks constructs for writing queries in a simple way. It’s more complicated than it should be to write a query to get, e.g., only the most expensive order for every customer of the current fiscal year. You can either use PostgreSQL’s vendor-specific DISTINCT ON feature or window functions for every other database.
Click through for the normal method (which works for MySQL, Oracle, SQL Server, etc.) and the Postgres way.
Comments closedThink of your standard CRUD operations. Most don’t even require a join; they’re very straightforward. And hey, if you completely denormalize all your data to one huge table, you’ll never have to think about a lot of this stuff anyway.
It’s only when developers are forced to think about things that things start to go wrong. I don’t mean to pick on developers specifically. It’s the human condition. Thinking often leads to poor choices.
In this post, I’m going to give you some basic guidance on when to use various T-SQL facilities, based on years of finding, fixing, and writing queries.
You get a free second quotation because I love this line:
I’m not going to talk about right outer joins, because that’s the foolish domain of characterless buffoons who use Venn diagrams to explain join results.
If I were emotionally capable of crying, that would bring a tear to my eye because it’s savagery against two things I hate.
Comments closedDeepak Mahto builds some rows:
A ROW expression allows you to construct ROW values, which can represent anonymous records, specific table row types, or custom composite types. Its uses include processing records within multiple expressions (using operators like =, <>, <, <=, >, or >=), evaluation with subqueries, and combining ROW values to build composite types. You can even export these to JSON format (using functions like
rowtojson). Some key operations you can perform with a ROW constructor in Conversion or migration are :
Click through for that list and several examples.
Comments closed