Press "Enter" to skip to content

Category: Syntax

Checking XML Validity

Published 2023-03-23 by Kevin Feasel

Kevin Wilkie doesn’t like misshapen XML data:

Sometimes you’ll find that you will have XML in your database. This could be for various reasons – from storing the XML after receiving an API response to keeping it in a table because a web developer couldn’t figure out another way to store their data. Sometimes – no matter how much you trust your source – you should question if the XML is well-formed. Let’s work out a few ways you can do that in a database.

Read on for a few tests. The more concerned you are about XML data quality, the more you’d want to push in the direction of having an XSLT defined as well.

Comments closed

Mnemonics for Remembering SQL Clause Order

Published 2023-03-22 by Kevin Feasel

Bob Pusateri keeps it all straight in his head:

Ooh! A mnemonic! And a pretty good one at that. The idea being that the first letter of each word of the sentence helps you remember something else, like the order of the major parts of a SELECT statement:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Click through for Bob’s best attempts. Oddly enough, now I want some Rally’s fries. Or maybe Checkers—I can’t decide.

Comments closed

DBCC CHECKALLOC

Published 2023-03-15 by Kevin Feasel

Steve Stedman helps us understand a DBCC command:

DBCC CHECKALLOC is a database console command (DBCC) in Microsoft SQL Server that can be used to check the allocation and structural integrity of the data and index pages in a database. Checking the allocation and structural integrity of the pages can be useful for identifying and correcting issues with the database that could cause errors or performance issues.

Read on to learn more and see a couple examples of it in action.

Comments closed

End of Month in Snowflake and SQL Server

Published 2023-03-15 by Kevin Feasel

Kevin Wilkie is ready for that end-of-month paycheck:

When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.

Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.

Comments closed

Building Your Own TRY_PARSE Function

Published 2023-03-10 by Kevin Feasel

Aaron Bertrand gives it a go:

A while back, I wrote a couple of tips about simulating TRY_CONVERT – a function added in SQL Server 2012 – in earlier versions (see Part 1 and Part 2). Recently, someone in the community had a similar requirement: they wanted to make TRY_PARSE work for dates in SQL Server 2008. Now, I’m not one to help people stay on versions of SQL Server that are now 15 years old, but I do acknowledge that hands may be tied, and they can’t always control the version they’re stuck with.

Aaron does this community member a solid, though it’s probably something you’d never want to use if you’re on SQL Server 2012 or later.

Comments closed

Tips for People Moving to Postgres

Published 2023-03-06 by Kevin Feasel

Ryan Booz shares a few bits of advice about people learning about Postgres:

I was much further in my SQL Server career than I should have been before I understood the usefulness of APPLY in T-SQL. I didn’t realize that APPLY isn’t a SQL standard and so when I started using PostgreSQL and needed similar functionality, I was perplexed trying to find the equivalent. Fortunately, I wasn’t the only one searching.

My recollection here is that Microsoft introduced the APPLY operator before there was an ANSI standard, so as long as my memory isn’t faulty here, this wasn’t a case of a vendor just deciding to name something differently for the sake of it.

Regardless, Ryan has a lot of good advice for learners of PL/SQL.

Comments closed

Approximate Percentiles in Azure SQL DB and MI

Published 2023-03-02 by Kevin Feasel

Balmukund Lakhani announces a feature has gone generally available:

Today, we are announcing General Availability (GA) of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. We announced preview of these functions in October 2022. Since then, many customers have adopted these for the applications where response time of percentile calculation was more important than the accuracy of the result.

I have and will continue to extol the virtues of these two functions wherever I go. They’re considerably better than the originals once you start getting into the hundreds of thousands or millions of rows. They’re also available in SQL Server 2022.

Comments closed

Speeding Up Queries via IF EXISTS

Published 2023-03-01 by Kevin Feasel

Chad Callihan doesn’t need to wait for the query’s end credits sequence:

When checking for the existence of a value or values in a table, typically, the whole table does not need to be read. The goal is to obtain more of a true or false answer, whether a criteria is met or not. If the criteria is met with the first few records then there’s no need to read on. Nonetheless, you may come across scripts written to unnecessarily retrieve the complete count of a value in a table. Let’s compare the performance difference between using COUNT(*) and using “IF EXISTS” when checking a table for values.

One’s going to give you a full scan and the other will give you a semi-join. Read on to see what the practical effect of this is.

Comments closed

Pivoting in Postgres with CROSSTAB

Published 2023-02-17 by Kevin Feasel

Rajendra Gupta pivots abruptly:

A pivot table is a popular tool in Microsoft Excel that shows summarized data and helps you analyze it in various ways. Pivot tables collect and organize data from different rows, columns, and tables. Pivot tables are a great way to summarise data, and a handy tool for analyzing sales revenue, products sold, sales performance, etc.

Relational database tables store data in multiple rows and columns. You can calculate data using various functions such as count, sum, and average. SQL Server provides the PIVOT and UNPIVOT functions for working with pivot tables. How do we create the pivot tables in PostgreSQL? Let’s find it out.

Read on for a demonstration.

Comments closed