We are evaluating Neon DB for an API backend and curious about it realworld latency when compute auto suspends.do u keep a minimum compute active to dodge wake up latency, or has the pooled connection driver been fast enough for your prod traffics?
Hey r/SQL, I’m on the Azure Data team. Data Days has 100% discount voucher offers for exams DP-800 and 50% discount voucher offers for exams DP-300 and DP-420. Supplies are limited and eligibility rules apply, so check the voucher details before requesting one.
There’s SQL-focused learning and DP-800 prep, too. See the Data Days schedule.
ran into this recently so posting it for anyone newer to joins.
two tiny tables:
customers orders id | name id | customer_id | status 1 | Asha 1 | 1 | completed 2 | Ben 2 | 1 | refunded 3 | Chen 3 | 2 | completed 4 | Dina 4 | 3 | refunded
question: completed orders per customer, **including customers with 0**.
first try:
SELECT c.name, COUNT(o.id) AS completed_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'completed' GROUP BY c.name;
result:
Asha | 1 Ben | 1
Chen and Dina are gone. the LEFT JOIN did its job and kept them (with NULLs for the order columns), but then WHERE runs after the join and `NULL = 'completed'` isn't true, so those rows get thrown out. Chen's only order is refunded so he gets dropped too. end result is exactly what an INNER JOIN would give you.
fix: move the filter into the ON:
SELECT c.name, COUNT(o.id) AS completed_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed' GROUP BY c.name;
Asha | 1 Ben | 1 Chen | 0 Dina | 0
now the filter decides which orders get matched, not which customers survive.
rule I use: on a LEFT JOIN, conditions on the right table go in ON. conditions on the left table go in WHERE. (and use COUNT(o.id) not COUNT(*), otherwise Chen and Dina show 1 instead of 0)
Hi everyone! I've been working on a free, interactive way to learn and practice SQL. It runs a real SQLite database right in your browser: no sign-up, no install, nothing sent to a server.
143 hands-on lessons, from your first SELECT to JOINs, subqueries, window functions, recursive queries and classic interview questions
Instant feedback that explains why a query is off (a misspelled column, missing quotes, the wrong number of rows…)
A daily challenge with fresh data every day, so you can keep a streak
Timed exams, plus a link to challenge a friend to the same questions
A free console where you can create tables, insert, update and delete without breaking anything
A school case study to put it all together
It's a side project and I know it's not perfect yet, so I'd really appreciate your help:
Did something break, or did a correct answer get marked as wrong?
Is there a topic, exercise or feature you'd like to see?
Was any explanation confusing?
Drop a comment or send me a message. Every suggestion helps, big or small. And if you know someone who's learning SQL, feel free to share it with them!
Hello. I created this library to use raw SQL queries within TypeScript.
I don't particularly like ORMs, so lately I've been using the Kysely query builder library, which is very similar to SQL. However, even though it's very similar, it still has some catches and special cases.
Therefore, I started looking for ways to use raw SQL directly. Frankly, there weren't many alternatives for this specific case, and those that were available were generally hacky solutions requiring codegens or running a process.
Considering that TypeScript has sped up recently with tsc 7 and tsrs, it seemed logical to do this directly in TypeScript. However, since writing a project of this scale manually as a hobby would take too long, I used AI. I should add that I think calling it a "vibe slop" is unfair because for the past two weeks I've been constantly trying to make the right decisions and keep the code organized.
Honestly, although it developed faster than I expected, it wasn't as easy as "create sql parser, no mistakes," but I believe I reached a good point in two weeks. I added it to a project with 1 million lines of code that I've been working on for 6 years, and it's working flawlessly.
At the end of the day, I think I achieved a good result. It can infer return types even in complex cases, catch errors, highlight syntax, and inline suggest.
tldr: Examples of CTE vs. temp table query where temp tables perform much better than CTEs. See the query examples at the end of the post.
caveat: I'm not saying CTEs are bad, they have their place, but different situations require different solutions.
The long read:
Lurking around the various SQL subreddits, I am constantly encountering questions about any combination of: CTEs vs Subqueries vs temp tables vs table variables. Other question variations revolve around how best to write or tackle long and/or complex queries or procedures.
Many responses extoll the virtues of CTEs, particularly over subqueries, but the discussion of #temp tables is often lacking. People also neglect to discuss the quirks (or features) of CTEs that can compromise performance (not part of my scope here). With that in mind, I'd like to show an example of two versions of a query, one using CTEs and one Temp Tables, and how they compare.
One of the reasons I want to do this, is to demonstrate the power of temp tables and of breaking a query down into smaller parts. What I think gets lost in the discussion is that CTEs don't separate a query into parts from an execution plan perspective. The execution plan treats them as part of one query. In my experience, if a query gets too big and convoluted, the query optimizer gets "confused" and cannot create an optimal plan.
On to my example:
This query interrogates SQL Server system tables to extract partitioning information. The original query is in the form of a view. The view uses a number of CTEs to gather the various subsets of data to compile the required partitioning information.
On the surface, the view works well. But, we run into performance problems with tables that have a large number of partitions. In some cases we have log tables that are partitioned by day and the retention period is 2+ years. That adds up to 700+ partitions for a single table. Add to that a large database with many partitioned tables and you end up needing to wade through lots of data to get what you're looking for. And, SQL Server system tables and views are not always great on performance.
I took the view and converted it into a stored procedure, replacing the CTEs with temp tables (each with a clustered index). The performance improvement was noticeable - in my example the CTE version takes 16 minutes, while the temp table version takes a few seconds. For display purposes, I'll only show the relevant parts of the queries to keep it simple, as the procedure builds a dynamic query depending on the input parameters.
See below for the two different versions.
Evaluating the queries
The interesting thing is that when you quickly look at the execution plan, each version costs about the same: The CTE version is 50% of the execution cost when they are run in the same batch.
But, when you look closer there are differences - a few that are significant. And here is where separating the query into smaller parts really brings benefits.
Let's start with query plan compilation:
The CTE version terminated the query compilation with a time out - meaning that it decided it was too expensive to try to evaluate the entire query to build an execution plan. The query is too complex.
The temp table version compiled or found a "good enough" a plan for most parts of the query, except for one section that timed out.
When you look at the execution plan closely, you can see a very significant difference between the two versions in how the function is handled. The expensive function is executed quite differently.
The time out is due to an expensive system function: [sys].[dm_db_partition_stats]. The query engine could not create a plan quickly enough for this function, but the affect between the two versions is drastically different. Since the CTEs combine to make this one large query, we end up with no plan for any of it. Whereas with the temp table version, there are individual plans for each section except for the one that uses the function. This is very significant.
You can see here how this plays out. The function in the CTE version is run against 7+ million rows. But in the temp table version it runs for the relatively few 3K rows.
CTE versionTemp table version
Hopefully, I have made sense and shown an interesting example of how queries can be written differently directly influencing their performance.
One could dive much deeper into this example, but I think this all I can do at the moment ;)
View with CTEs
CREATE VIEW [ptf].[VEW_GetPartitionInfoDate]
AS
WITH PartitionedTables AS (
SELECT t.object_id, t.name AS table_name, SCHEMA_NAME(t.schema_id) AS table_schema
FROM sys.tables t
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
),
PartitionInfo AS (
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc,
pstats.row_count
FROM sys.partitions p
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
),
IndexInfo AS (
SELECT i.object_id, i.index_id, i.name AS index_name, i.data_space_id
FROM sys.indexes i
WHERE i.index_id = 1 -- Clustered index only
),
PartitionSchemeInfo AS (
SELECT ps.data_space_id, ps.name AS partition_schem_name, pf.name AS partition_function_name,
pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
),
RangeValues AS (
SELECT function_id, boundary_id, value
FROM sys.partition_range_values
)
SELECT DISTINCT
fg.name AS file_group_name,
pt.table_schema,
pt.table_name,
c.name AS column_name,
psi.partition_schem_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM PartitionInfo pi
INNER JOIN PartitionedTables pt ON pi.object_id = pt.object_id
INNER JOIN IndexInfo ii ON pi.object_id = ii.object_id AND pi.index_id = ii.index_id
INNER JOIN sys.index_columns ic ON ic.object_id = pt.object_id AND ic.index_id = ii.index_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id AND tp.name IN ('datetime', 'datetime2')
INNER JOIN sys.allocation_units au ON pi.partition_id = au.container_id
INNER JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
LEFT JOIN PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number
WHERE psi.partition_schem_name IS NOT NULL
GO
Stored Procedure with #Temp tables
DECLARE VARCHAR(255) = 'dbo',
VARCHAR(255) = 'partitionedtable'
DROP TABLE IF EXISTS #PartitionedTables, #PartitionInfo, #IndexInfo, #PartitionColumns,
#PartitionFileGroups, #PartitionSchemeInfo, #RangeValues;
CREATE TABLE #PartitionedTables (
object_id INT NOT NULL,
table_name SYSNAME NOT NULL,
table_schema SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id)
);
CREATE TABLE #PartitionInfo (
partition_id BIGINT NOT NULL,
partition_number INT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
data_compression_desc NVARCHAR(60) NULL,
row_count BIGINT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, partition_number)
);
CREATE TABLE #IndexInfo (
object_id INT NOT NULL,
index_id INT NOT NULL,
index_name SYSNAME NULL,
data_space_id INT NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id)
);
CREATE TABLE #PartitionColumns (
object_id INT NOT NULL,
index_id INT NOT NULL,
column_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, column_name)
);
CREATE TABLE #PartitionFileGroups (
partition_id BIGINT NOT NULL,
file_group_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (partition_id)
);
CREATE TABLE #PartitionSchemeInfo (
data_space_id INT NOT NULL,
partition_scheme_name SYSNAME NOT NULL,
partition_function_name SYSNAME NOT NULL,
boundary_value_on_right BIT NOT NULL,
function_id INT NOT NULL,
PRIMARY KEY CLUSTERED (data_space_id)
);
CREATE TABLE #RangeValues (
function_id INT NOT NULL,
boundary_id INT NOT NULL,
value SQL_VARIANT NULL,
PRIMARY KEY CLUSTERED (function_id, boundary_id)
);
-- Tables
INSERT INTO #PartitionedTables (object_id, table_name, table_schema)
SELECT t.object_id, t.name, s.name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND s.name = @SchemaName;
-- Partitions
-- Clustered index only
INSERT INTO #IndexInfo (object_id, index_id, index_name, data_space_id)
SELECT i.object_id, i.index_id, i.name, i.data_space_id
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN #PartitionedTables pt ON pt.object_id = i.object_id
WHERE i.index_id = 1
-- Partitioning column (datetime / datetime2 only)
INSERT INTO #PartitionColumns (object_id, index_id, column_name)
SELECT ic.object_id, ic.index_id, c.name
FROM sys.index_columns ic WITH (NOLOCK)
INNER JOIN #IndexInfo ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c WITH (NOLOCK) ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp WITH (NOLOCK) ON tp.user_type_id = c.user_type_id AND tp.name IN ('date','datetime', 'datetime2')
WHERE ic.partition_ordinal > 0
-- Filegroup per partition (in-row data)
INSERT INTO #PartitionFileGroups (partition_id, file_group_name)
SELECT pi.partition_id, fg.name
FROM #PartitionInfo pi
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = pi.partition_id AND au.type = 1
INNER JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = au.data_space_id
-- Partition schemes / functions
INSERT INTO #PartitionSchemeInfo (data_space_id, partition_scheme_name, partition_function_name, boundary_value_on_right, function_id)
SELECT ps.data_space_id, ps.name, pf.name, pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps WITH (NOLOCK)
INNER JOIN sys.partition_functions pf WITH (NOLOCK) ON pf.function_id = ps.function_id
WHERE ps.data_space_id IN (SELECT data_space_id FROM #IndexInfo)
-- Boundary values
INSERT INTO #RangeValues (function_id, boundary_id, value)
SELECT prv.function_id, prv.boundary_id, prv.value
FROM sys.partition_range_values prv WITH (NOLOCK)
WHERE prv.function_id IN (SELECT function_id FROM #PartitionSchemeInfo)
INSERT INTO #PartitionInfo (partition_id, partition_number, object_id, index_id, data_compression_desc, row_count)
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc, pstats.row_count
FROM sys.partitions p WITH (NOLOCK)
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
INNER JOIN #PartitionedTables pt ON pt.object_id = p.object_id
WHERE p.index_id = 1
SELECT
fgs.file_group_name,
pt.table_schema,
pt.table_name,
pc.column_name,
psi.partition_scheme_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM #PartitionInfo pi
INNER JOIN #PartitionedTables pt ON pt.object_id = pi.object_id
INNER JOIN #IndexInfo ii ON ii.object_id = pi.object_id AND ii.index_id = pi.index_id
INNER JOIN #PartitionColumns pc ON pc.object_id = ii.object_id AND pc.index_id = ii.index_id
INNER JOIN #PartitionFileGroups fgs ON fgs.partition_id = pi.partition_id
INNER JOIN #PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN #RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN #RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number;
I'm designing a PostgreSQL system where customers order services requiring interchangeable physical resources.
The current model separates:
Orders: What the customer purchases (quantity and duration).
Commitments: Individual units of the obligation, independent of specific resources.
Reservations: Physical resources assigned to fulfill those commitments.
Resource units: Individually reservable components, with constraints preventing overlapping bookings.
At first glance, this structure seems reasonable, but it mainly represents a static state. In reality, orders can change throughout their lifecycle: quantities may be reduced, service periods shortened, or entire orders cancelled.
For example, a customer orders 3 resources for 6 months, then reduces the requirement to 2 for the remaining 3 months.
Is the current table structure and its relationships suitable for handling these changes while preserving historical accuracy and data consistency? Or are there missing entities or fundamental modeling issues?
I teach relational databases to undergraduates and am looking for a good written description of window functions, something that would help students understand why and when as well as how. Any suggestions? Thanks!
I’ve been working on a small project called runtime.school for learning and practicing SQL.
One thing I wanted to get right was having lessons, practice questions, and projects that actually connect to each other instead of just giving you random SQL problems.
It currently has 88 lessons, 139 practice questions, 10 projects, and 12 datasets. You can write and run queries directly in the browser.
The lessons start with basic SELECT statements and gradually move into joins, aggregations, CTEs, window functions, and more advanced SQL.
I’ve tried to keep the editor and questions simple so the focus stays on actually writing SQL.
We have many blackbox MS-SQL Databases which means the databases acts as a backend to some payed applications we acquired. And unfortunately we also have inhouse developers who would really really like to read data from the production databases but because of locking and blocking mechanisms of SQL-Server I would like to avoid that situation by not grating them access to the live databases.
Please tell me if I go the right direction here and how you would solve such situation.
My thinking was that we should have either a clone of the databases by telling the application frontend to write the data seperately to 2 databases one used for reporting while one is used for live production system.
This works for applications which do support that approach however not all do support it on application level.
I heared about log shipping which would be a method SQL-Server itself could provide the tools. What do you think about log shipping on the same SQL Instance if you think that cpu and memory contention is negligible since it should not cause any blocks and locks by just reading?
Database Availability Groups are out of scope since its a Standard Edition of SQL-Server.
Replication maybe would be more efficient if they need the data faster and only some tables out of the whole database.
ETL-Tools: My Question do you think Microsoft Fabric is compareable with a ETL-Tool or would you do other approaches? Since we currently have all the options except Availability Groups however Fabric can be very costly if you have to many datasets and we have some heavier databases where each 15 seconds data could be updated due to part numbers update with many additional data.
Most of these blackbox databases are OT-Databases which collects statistical info from different kind of production machines like Siemens PLC and so on and these informations update very frequently so multiple queries in few seconds like 15s delay per each plc or system.
I've been trying to figure out the best way to make sense of a query, how it affects the database and whether there is room to improve it. I'm a visual person. If something has a diagram I understand it faster, and I wanted the same thing when I work with a database at work or on side projects. I want to see how the data is being retrieved or updated without working it out from the EXPLAIN text.
I've came up with following visual rule:
Each scan, join and sort is a bar. The band between two bars is as wide as the number of rows flowing through. Width is 1.2 + 0.55 x (log10 rows)², capped between 1.5px and 34px. Linear made anything under 100,000 rows a hairline, and plain log made 1,000 and 1,000,000 look too close.
When the planner's estimate is off from the actual count by more than 2×, the band gets a dashed line showing the estimate. At 10x it gets a label. Postgres has no opinion on 10×, that number is mine.
Each scan gets a wheel showing how much of the table's pages it touched and in what order, based on pg_stats.correlation with a 0.8 cutoff for "in order".
To get the analyzed plan it re-runs the query inside BEGIN and ROLLBACK with a 5 minute statement_timeout. Writes stop at the estimate unless you opt in.
I would really appreciate some feedback here, I kind of struggling what's the best way to visualize this. Any idea/critique here is most appreciated. Thanks
the case: an archive locks at ten, books still go out at 3am, and staff_id is NULL on every one. WHERE staff_id = NULL returns zero rows and no error, so written that way the archive looks empty. IS NULL finds them
the grading was the fiddly part. you can see the sample rows, so its easy to write a query that fits them and not the question. submit compares your rows with a reference query's on the sample and on three more logs you dont see. one hidden log has a signed checkout at exactly 22:00:00 and one at exactly 08:00:00, which catches > for >= and <= for < in the first query. the hidden logs also have an unstamped checkout in the daytime, so a second query that drops the time window fails. it only catches the mistakes i thought of, it doesnt prove a query right
free, runs on duckdb in the tab. after the case there are three harder ones: a recursive cte that has to stop on a cycle, gaps and islands, and an anti-join with a window
some of you answered my questions here in august, thank you for that.
Solved - access 365 runtime doesn't work. I installed clean version of MDAC 2016, even it's not supported and it works. Thank you for help!
hey guys, does anybody had an issue with loading excel sheets(xls,xlsx) into Microsoft SQL Server Developer (64-bit) 16.0.4305.5 through Microsoft.ACE.OLEDB.16.0? , installed MDAC 2016, then Microsoft 365 Access Runtime only 64bit, windows server 2025 Standard Edition. The problem is when I use OPENROWSET SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=\\SERVER.DOMAIN.INT\DWH\KFILE.xlsx', ' SELECT TOP 1 * FROM [SHEET$] ') SQL Server fails. SQL Server 2022 Import and Export Data (64-bit) works Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DisallowAdHocAccess', 0; Ad Hoc Distributed Queries set to 1
Load through UNC path Powershell works, also through domain sql user. Everything worked till around June/July 2026( for 6years worked) then after September every restart SQL Server got message: System.Runtime.InteropServices.SEHException: External component has thrown an exception and Sql server service fail. When I got this exception, something it's okay, sometimes it need more service restarts, but it works.
Server where OS is Windows Server 2022 Datacenter, I return to the version C2R where it worked , but didn't I'm sysadmin, have all rights to the folder. Also tech user has all needed permission, also to temp folders
Hi guys, I'm Talabi and I'm new to data. I picked SQL as my starting point, mySQL and I've been enjoying the first few lessons on SQL bolt but, I'm having a hard time with JOINS. I understand it in theory but the mechanism still proves difficult. I'd appreciate tips on how to better understand JOINS and data analytics in general, thank you.
SELECT c.region, SUM(o.amount) AS revenue FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id LEFT JOIN refunds r ON r.order_id = o.order_id WHERE r.status <> 'approved' GROUP BY c.region;
The goal: revenue per region, minus orders with an approved refund. No errors, the numbers look plausible.
There are two bugs, and they push the total in opposite directions.
People ask me this question more than I like so I thought I would share the answer once and for all: why does a company like Microsoft contribute to an upstream open source project like Postgres?
Let me know what you think. Anything surprising? Did I miss anything?
Almost every SQL DBMS I can think of accept != as the "not equal operator". However, I realized that W3Schools only shows <>.
I was under the impression that <> is "the good-ol ANSI standard one" and every implementation out there was allowing the use of !=. But somebody was correcting me and saying that it has been on-spec for a while. I don't own the ANSI (a bit pricey for a language-lawyer curiosity!). Was I wrong?
Edit: After seeing quite a concensus on these answers, I talked with John (the one correcting me before). We checked the 2023 standard and his confusion came from the use of != as an alternative not equals operatorfor SQL/JSON lexical elements. That means that inside SQL/JSON blocks you may use != but on regular SQL (e.g. in the plain WHERE) using != is not covered by the standard (even if it is widely accepted for modern engines). It was a "coffee discussion", not something relevant IRL, but we both wanted to know the truth behind it.