“That query’s not sargable” says one developer to another.
“I know it’s not sargable… would you love it more if it had sargability?” replies the other developer.
“What does sargable mean?” you wonder in silence.
Continue readingTag: create query
Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)
If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.
We can’t use aggregates or subqueries in the GROUP BY clause.
To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.
Understanding the “Key Lookup” in SQL Server
When looking through the execution plan in SQL Server, you may occasionally see “key lookup” glaring back at you.
If you find this happening a lot, and on frequently run queries, then you might want to do something about it.
Continue readingUnderstanding the RID Lookup in SQL Server: How It Affects Performance
If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.
In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.
Continue readingUnderstanding the Covering Index in SQL
In SQL, adding an index to a table can provide us with a significant performance boost, especially if it’s a covering index for commonly run queries.
But what exactly is a covering index?
Let’s find out.
Continue readingAggregating Data in SQL with the GROUP BY and HAVING Clauses
When writing queries in SQL, we sometimes need to summarize data based on specific criteria. Fortunately SQL makes this easy to achieve with the GROUP BY and HAVING clauses. These two clauses allow us to aggregate data and filter the results based on group conditions.
More specifically, GROUP BY allows us to group the results, and HAVING is an optional clause that we can use to filter the results based on certain conditions.
Below is an example of where we can use these two clauses to aggregate data and filter it based on certain criteria.
Continue readingUnderstanding PostgreSQL’s FILTER Clause
When using aggregation functions in PostgreSQL, we have the option of including a FILTER clause. This clause allows us to narrow down – or filter – the rows that are fed to the aggregate function.
This can be be a handy way to provide functionality that we might otherwise need to use a CASE statement or perhaps a CTE.
In this article, we’ll take a look at PostgreSQL’s FILTER clause, and see how it can simplify our SQL queries by replacing CASE statements with more concise code.
Using a SQL Subquery in the SELECT List
Let’s dive into subqueries for a moment. Specifically, how to use a SQL subquery in the SELECT list. It’s a handy little trick that can help us fetch related data without resorting to complex joins.
How to Extract Fields from Composite Values in PostgreSQL
In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.
When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.
Continue readingHow to Update Multiple Columns in SQL with a Subquery
In SQL, it’s not unusual to see subqueries in SELECT statements, where they can help narrow down the results based on a complex condition.
But the SELECT statement isn’t the only place we can put a subquery.
We can use subqueries in an UPDATE statement, and we can update multiple rows too, if required.
In this article, we look at how to update multiple columns by using a SQL subquery in the UPDATE statement.