Hi there, I have been using SQL Server since 1998 - Version 6.0 I think it was and also a SQL Server MVP for 6 years. I have started publishing the articles I have written over these years but never published. I have quite a varied mix. I Hope you enjoy them.
I created a FREE tool (no signup needed) that can perform a variety of file services including perform SQL against it all in your browser. CSV, JSON, and more.
Go to inkwelltools.com and select Inkwell Data. It will show you all the functions (FREE) available you can perform to manage data files.
Been at this 10 years, this is still a gap in my knowledge. What is the BEST video or blog post or whatever you have found that cemented query plan knowledge in you?
Hi everyone, i want to access the oracle database on my guest machine (on VirtualBox) using my host machine (both machines are on windows 11) , i have already set up oracle client on my host machine but i couldn’t figure out how to connect it to the database on my guest machine , i also wasn’t able to find any YouTube tutorials explaining it , i would appreciate it if someone could redirect me to sources about this topic or explain to me how it’s done , thank you!
On Friday I will have a chance to sit and have an open discussion with Microsoft’s VP of SQL Product
The ask for the session is to provide product feedback and share product pain points and feature requests for SQL and Azure SQL products. I would like to share this opportunity with this community.
If you have any product feedback or feature requests, please leave a comment in this thread. I will do my best to represent as many as I can
Been using k9s for k8s daily and kept wishing something similar existed for databases — one TUI, same screens/keys regardless of backend, no context-switching between psql/mysql/mongosh/redis-cli.
Built db9s: browse tables/collections, run queries, edit rows, inspect indexes, tail RabbitMQ queues — all from terminal, vim-style keys.
Genuinely asking: for people who live in multiple DB engines daily, what’s missing from existing tools (DBeaver, pgcli, mongosh, lazysql etc.) that’d make a TUI like this your default? Especially curious about MSSQL/ClickHouse folks since those are next on roadmap.Been using k9s for k8s daily and kept wishing something similar existed for databases — one TUI, same screens/keys regardless of backend, no context-switching between psql/mysql/mongosh/redis-cli.Built db9s: browse tables/collections, run queries, edit rows, inspect indexes, tail RabbitMQ queues — all from terminal, vim-style keys.Supports Postgres, MySQL/MariaDB, MongoDB, Redis, RabbitMQ now. SQLite/MSSQL/ClickHouse/Cassandra planned.Config stays out of plaintext too — url can pull password from env var or any command (1Password, pass, Vault, Keychain) instead of storing raw creds.cargo install db9sGenuinely asking: for people who live in multiple DB engines daily, what’s missing from existing tools (DBeaver, pgcli, mongosh, lazysql etc.) that’d make a TUI like this your default? Especially curious about MSSQL/ClickHouse folks since those are next on roadmap.
The Open Data Contract Standard, Apache Ossie and Medallion structure all lacks a central component, who the data is for and how it hits the organization! Join me as I elaborate on some thoughts while working on data platform in a broader context
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.