Lukas Eder discusses window functions:
Now, let’s assume I’m interested in these things:
- How many payments were there in the same hour as any given payment?
- How many payments were there in the same hour before any given payment?
- How many payments were there within one hour before any given payment?
Those are three entirely different questions.
Lukas’s solution uses Oracle syntax, but most of it also applies to SQL Server 2012 and higher. The part that doesn’t apply, unfortunately, is the RANGE BETWEEN INTERVAL, which allows you to find values clustered in the same time period (one hour in his example).
Vladimir Oselsky notes an edge case where ISNULL and COALESCE can behave differently:
Even though we would expect to see both records returned we only get 1 record. Huh? This is exactly what puzzled a coworker, ofcourse query was not as simple as this one but same issue caused him to hit a road block.
In the case of COALESCE and OR methods, results are identical.
The underlying issue here is that the variable data type differs from the column’s data type, and exposes a difference in how COALESCE and ISNULL work.
Erik Darling warns us against tearing the fabric of time by dividing by zero:
There are several options for fixing this. For instance, you can use a CASE expression, or COALESCE, but I find they get a tad muddled to write after a while, especially if we’re safeguarding multiple columns from our mathematical disaster. Plus,under the covers, the functions I like to use are just case expressions anyway. Isn’t it nice that SQL Server will save you a touch of typing? I think so. What a considerate piece of software!
This is a bit of a beginner tip, but it came up while we were at DBA Days, so I figured I’d write about it in case anyone runs across it.
Read on for Erik’s favorite solution to the problem.
Chris Bell warns against using double dot syntax:
I am finding more and more cases where SQL code is being created using the double dot or period for the 2 part naming convention.
For example, instead of using dbo.table1 I am seeing ..table1.
I don’t know who suggested this in the first place, but it is not a good idea. Sure it works and does what you expect, but there is a HUGE risk with doing this. When you use the .. syntax, you are telling the code to use whatever the default schema is for the user that is running the query. By default that is the dbo schema, but there is no guarantee that all systems are going to be that way.
Read on to understand why this is a big deal.
Shane O’Neill shows how to rename a schema (and how to get yourself in trouble trying):
I repeat the above: eh…what???
Where did my table go???
Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.
Click through for the full story.
Lukas Eder explains COUNT versus EXISTS:
COUNT(*) needs to return the exact number of rows. EXISTS only needs to answer a question like:
“Are there any rows at all?”
In other words, EXISTS can short-circuit after having found the first matching row. If your client code (e.g. written in Java or in PL/SQL, or any other client language) needs to know something like:
“Did actors called “Wahlberg” play in any films at all?”
Lukas shows how it works in Oracle and Postgres; the result is still basically the same for SQL Server.
Jeffrey Verheul shows how to use the PIVOT operator in T-SQL:
The idea of using key-value pairs to store data isn’t new, but with the rapid development of cloud solutions like Azure and the hype around NoSQL databases, using key-value pairs to store data got a big boost. Especially developers (in my experience) love using key-value pair to store their data, because it’s easy for them to consume the data in an application. But it gives the database professional an extra challenge because we’re used to retrieve columns with values instead of a record per value. So how can we turn those key-value pairs into rows?
This is a good example of using PIVOT. I’m not a big fan of storing data in key-value pairs and using pivoting operators because you’re burning CPU on that very expensive SQL Server instance (and you’re not taking advantage of what relational databases do well); if you really need to store data as key-value, I’d recommend doing the pivot in cheaper application servers.
Kevin Hill explains a potential performance difference between using NOT IN and using a left join:
Basic stuff, right? Both will return 951 records (books) that I do not own. And, very quickly…because the tables are tiny. Sub-1 second is fast.
The issue here is HOW the rows are compared.
English version now, techy stuff later:
In the first query, this is equivalent to you standing at the bookstore and calling home to have someone check to see if the book in your hand is already in your collection. EVERY time. One by one.
In the second, you got really smart and brought a list with you, which you are comparing to the books on the shelf at the store. You’ve got both “lists” in one place, so it is far more efficient.
Even in the case with a few hundred records, you can see why there’d be a performance difference.
Lukas Eder discovers that Oracle’s PL/SQL has WHILE loops:
In SQL, everything is a table (see SQL trick #1 in this article), just like in relational algebra, everything is a set.
Now, PL/SQL is a useful procedural language that “builds around” the SQL language in the Oracle database. Some of the main reasons to do things in PL/SQL (rather than e.g. in Java) are:
- Performance (the most important reason), e.g. when doing ETL or reporting
- Logic needs to be “hidden” in the database (e.g. for security reasons)
- Logic needs to be reused among different systems that all access the database
Much like Java’s foreach loop, PL/SQL has the ability to define implicit cursors (as opposed to explicit ones)
The WHILE loop is a little more helpful in the SQL Server world for doing things like deleting lots of rows in small batches, but I agree with Lukas’s sentiment: if you start writing a WHILE loop, it’s best to sit back and think about whether this is the best decision.
Grant Fritchey shows that CTEs are not tables; they’re expressions:
The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. However, the CTE has a very unfortunate name. Over and over I’ve had to walk people back from the “Table” in Common Table Expression. The CTE is just a query. It’s not a table. It’s not providing a temporary storage space like a table variable or a temporary table. It’s just a query. Think of it more like a temporary view, which is also just a query.
Read the whole thing.
Kevin Feasel
2016-11-03
Syntax