A Fine Slice Of SQL Server
When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table:
It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids that got deleted or skipped, how do we do it?
Click through for two methods, one specific to SQL Server 2022 and one which works for all versions of SQL Server.
Comments closedRobert Sheldon looks at the syntax for common table expressions in MySQL:
As with many relational database management systems, MySQL provides a variety of methods for combining data in a data manipulation language (DML) statement. You can join multiple tables in a single query or add subqueries that pull data in from other tables. You can also access views and temporary tables from within a statement, often along with permanent tables.
MySQL also offers another valuable tool for working with data—the common table expression (CTE). A CTE is a named result set that you define in a
WITHclause. TheWITHclause is associated with a single DML statement but is created outside the statement. However, only that statement can access the result set.
The syntax is very similar to that of SQL Server save for an explicit RECURSIVE clause rather implicit recursion as in T-SQL.
Rituraj Khare makes some connections:
In Apache Spark, we can use the following types of joins in SQL:
Inner join: An inner join in Apache Spark is a type of join that returns only the rows that match a given predicate in both tables. To perform an inner join in Spark using Scala, we can use the
joinmethod on a DataFrame.
The set of options is the same as you’d see in a relational database: inner, left outer, right outer, full outer, and cross. The examples here are in Scala, though would apply just as easily to PySpark and, of course, writing classic SQL statements.
Comments closedChad Callihan compares and contrasts:
We recently looked at looked at comparing schemas using Azure Data Studio. What if we need to compare tables by using a query? For this post we’ll compare using EXCEPT, NOT IN, and NOT EXISTS to find differences between two tables.
Our two tables to compare will be Comic and Comic_Copy. Based on counts, we have 48 more records in Comic than we do in Comic_Copy. Let’s find the differences.
In Chad’s specific query, NOT EXISTS works great. Where I like EXCEPT is when you need to see if any of the non-key columns differ. For example, if you also needed to compare titles for rows with the same ID and ensure those titles matched.
Brent Ozar points out the ups and downs of DATETRUNC():
The first one, passing in a specific start & end date, gets the best plan, runs the most quickly, and does the least logical reads (4,299.) It’s a winner by every possible measure except ease of writing the query. When SQL Server is handed a specific start date, it can seek to that specific part of the index, and read only the rows that matched.
DATETRUNC and YEAR both produce much less efficient plans. They scan the entire index (19,918 pages), reading every single row in the table, and run the function against every row, burning more CPU.
SQL Server’s thought process is, and has always been, “I have no idea what’s the first date that would produce YEAR(2017). There’s just no way I could possibly guess that. I might as well read every date since the dawn of time.”
Read on for the upshot.
Comments closedJeffrey Wang is speaking my language:
The December 2022 release of Power BI Desktop includes three new DAX functions: OFFSET, INDEX, and WINDOW. They are collectively called window functions because they are closely related to SQL window functions, a powerful feature of the SQL language that allows users to perform calculations on a set of rows that are related to the current row. Because these functions are often used for data analysis, they are sometimes called analytical functions. In contrast, DAX, a language invented specifically for data analysis, had been missing similar functionalities. As a result, users found it hard to write cross-row calculations, such as calculating the difference of the values of a column between two rows or the moving average of the values of a column over a set of rows.
Read on to learn more about how these functions work and how they differ from their SQL Server counterparts.
Comments closedKevin Wilkie plays duck-duck-goose with columns:
With Snowflake, we could do many different things that we’re not used to seeing with a SELECT statement. We’re all used to seeing this – SELECT * and it shows all kinds of columns.
With Snowflake, we can tell Snowflake NOT to show certain columns by using the EXCLUDE operator.
Read on to see how it works and specific requirements around operation. In addition, Kevin shows a way to perform aliasing.
Comments closedKevin Wilkie punctuates the statement:
With our last blog post, we started discussing Snowflake and the SELECT statement. Now, if you remember, there is this great thing called a semi-colon.
The main reason you should use the semicolon is to terminate all of your queries. Snowflake does this great thing by default, letting you run one query at a time.
I remember back when Microsoft deprecated T-SQL statements which did not end with semi-colons. It was fun speculating for about 5 minutes regarding the carnage which would happen if they carried out the deprecation notice, not least of which we’d find in Microsoft-developed code.
Comments closedLouis Davidson explains how bit operations work in T-SQL:
I expect that 99% of the people reading this looks at this probably would expect there to be a status table that contained the values of status. Seeing that this is a base 2 number, you may be in that 1% that thinks this might be a bitmask. but unless you have and eidetic memory, you probably don’t know what all of the bits mean.
A bitmask is a type of denormalization of values where instead of having a set of columns that have on or off values (no Null values), you encode it like:
Bitmasks make me break out the angry nun ruler. You can almost guarantee you’re doing something wrong if you design a bitmask as a column in a table.
Comments closedBill Fellows runs into an issue:
Perfect, now I have a row for each second from midnight to approximately 5.5 hours later. What if my duration need to vary because I’m going to compute these ranges for a number of different scenarios? I should make that 19565 into a variable and let’s overengineer this by making it a bigint.
Things don’t work out quite the way you might have expected there. Read on and see what Bill found and how you can circumvent the problem.
Comments closed