Press "Enter" to skip to content

Category: Syntax

Session-Scoped Temp Tables in Microsoft Fabric now GA

Published 2025-04-23 by Kevin Feasel

Twinkle Cyril gets something GA:

Introducing distributed session-scoped temporary (#temp) tables in Fabric Data Warehouse and Fabric Lakehouse SQL Endpoints.

#temp tables have been a feature of Microsoft SQL Server (and other database systems) for many years. In the current implementation of Fabric data warehouse, #temp tables are session scoped or local temp tables. Global temp tables are not included in this release.

Session-scoped #temp tables exist only within the session in which they are created and last only for the duration of that session. They are not visible to other users or sessions and are automatically dropped from the system once the session ends or the user decides to drop the temp table. These tables are accessible to all users without requiring specific artifact-level permission.

Click through for examples of how it works and how you can specify a session-level temp table over a local temp table.

Comments closed

The CHOOSE Function in SQL Server

Published 2025-04-22 by Kevin Feasel

Louis Davidson chooses the form of our destroyer:

I preface a lot of what I write with whether or not it is for a “practical” use. One of the Simple Talk authors used this function in a forthcoming article. and I realized I hadn’t heard of it before (or I forgot about it… which is not completely unlikely.) The practical use was to generate some data and have at least a little variety to the values.

The CHOOSE function has this syntax.

CHOOSE(item_to_choose, item1, item2 [,item3]…[itemN])

In fairness to Louis, CHOOSE() was one of those additions to SQL Server 2012 that we mostly forgot about. It’s similar in that vein to IIF() if you weren’t living in Excel at the time. In any event, Louis takes CHOOSE() through its paces, showing some useful scenarios and stretching the limits to see what happens.

Comments closed

Hierarchy Management in PostgreSQL

Published 2025-04-18 by Kevin Feasel

Boris Novikov builds a list:

The Postgres database management system was originally designed as object-relational. We remind why PostgreSQL is an object-relational rather than just relational database management system, and briefly discuss what kind of object-oriented features it offers. We demonstrate how these features can be used to manage hierarchies, and how applications can benefit using them.

Click through for a technique native to PostgreSQL.

Comments closed

Comparing INSERT INTO and SELECT * INTO

Published 2025-04-17 by Kevin Feasel

Haripriya Naidu runs an experiment:

Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.

INSERT INTO TARGETTABLE
SELECT * FROM SOURCETABLE

OR

SELECT * INTO TARGETTABLE 
FROM SOURCETABLE

Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.

Comments closed

The WINDOW Clause in SQL Server 2022

Published 2025-04-16 by Kevin Feasel

Andy Brownsword takes a look at one quality of life improvement in SQL Server 2022:

Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined.

You can do some neat things with the WINDOW clause, including partial matches. For example, you could define a window r1 AS (PARTITION BY x.MyColumn) and then another window r2 AS (r1 ORDER BY x.SomeOtherColumn). I don’t think there are a huge number of scenarios in which this is helpful, especially because performance typically depends upon minimizing the unique number of window functions in your query, but every once in a while it’s a really neat thing.

Comments closed

Finding Object Dependencies in SQL Server

Published 2025-04-16 by Kevin Feasel

Vlad Drumea is looking for matches:

Figuring out object dependencies in a SQL Server database, especially one you’re not familiar with, can be a challenge.
Luckily, Microsoft provides the sys.sql_expression_dependencies system catalog view to help DBAs and developers track down various object dependencies.

I leveraged this catalog view in a query that returns the necessary info in a more user-friendly way, while also allowing easy filtering for referenced or referencing object.

Click through for the script. It’s been a while since I’ve used this DMV in anger, though my recollection is that I had problems finding dependencies over linked servers.

Comments closed

Using DATETRUNC() in SQL Server

Published 2025-04-11 by Kevin Feasel

Rajendra Gupta shows off a nice feature in SQL Server 2022:

Suppose you are a data strategist or analyst for an organization. You have been tasked with getting actionable insights from customers who want to track customer patterns at different intervals, such as hourly, daily, or weekly. To do this, you need to use several date functions such as DATEADD, DATEDIFF, DATEPART, and DATEFROMPARTS to get the required date format.

In SQL Server 2022, this got a lot easier to do using the DATETRUNC function.

Solutions using DATETRUNC() are significantly easier to read and understand than the alternative of combiningDATEADD() and DATEDIFF()

Comments closed

SELECT FOR UPDATE in PostgreSQL

Published 2025-04-09 by Kevin Feasel

Umair Shahid preps for an update:

When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.

In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.

Click through to understand how this works and also some notes on when to use it and when not to use it.

Comments closed

Handling NULL in T-SQL

Published 2025-04-08 by Kevin Feasel

Chad Callihan deals with non-existent data:

Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.

For the longest time, I strongly preferred COALESCE() over ISNULL() because of how it can handle multiple scenarios, as well as it being ANSI SQL syntax. But it turns out that, if you do only have two things to compare, ISNULL() is a little bit faster.

Comments closed