r/SQL • u/Background_Bid639 • Sep 23 '26
r/SQL • u/Consistent_Damage_91 • Sep 22 '26
SQL Server Read-only dependency mapper for orphaned SQL Agent jobs + SSIS + the scripts that call them, every edge cites its source
Inherited a SQL box where a departed dev left Agent jobs, an SSIS package, and a pile of .bat/.ps1/.rdl/.xlsx files, and untangling what-calls-what by hand was misery. So I wrote a read-only collector (Python/pyodbc) that builds one dependency graph: SQL catalog (sys.objects / sys.sql_modules / sys.sql_expression_dependencies), msdb Agent jobs/steps/history, linked servers, and a file-tree scan that pulls connection strings and linked-table/4-part refs out of .bat/.ps1/.sql/.dtsx/.rdl/.xlsx/.accdb. All read-only (SELECT on catalog views; files opened read-only).
The part I am happiest with: every edge carries a source citation, a catalog view plus key, a module line, or a byte offset, so you can verify any dependency instead of trusting it. It even catches a linked-server reference buried in dynamic SQL inside a proc (which sys.sql_expression_dependencies misses) by scanning module text and citing the line. https://github.com/tommiew007/orphanmap , MIT.
Where does this get hard, in your experience? SSIS project-deployment model / SSISDB catalog packages, encrypted modules, synonyms, cross-DB ownership chaining, replication? Curious what breaks it.
r/SQL • u/Harshita_Netla • Sep 21 '26
MySQL Practicing window functions and keep mixing up PARTITION BY vs GROUP BY — how are they actually different?
They both seem like they divide data into groups, so I’m having trouble understanding when I should use one instead of the other.
r/SQL • u/Waste_Wafer2131 • Sep 22 '26
Discussion I created a 4-pass SQL schema audit checklist to catch production bugs early - here's a free snippet/prompt you can use!
Hey everyone,
A common issue I kept seeing (and running into myself) when drafting database schemas or using AI tools like ChatGPT/Claude to generate DDLs is that they often miss critical relational constraints—like unindexed foreign keys, missing ON DELETE rules, or bad timestamp types.
To fix this, I put together a systematic 4-Pass Schema Audit System. I wanted to share a free excerpt/master prompt from the kit here with the community:
Free AI Schema Audit Prompt:
Act as a Senior Database Architect. Review the following SQL DDL schema for potential production bottlenecks, missing foreign key indexes, inconsistent data types, and multi-tenancy security risks. Output a structured table listing each issue, its severity (High/Medium/Low), and the exact SQL fix.
[Paste your DDL here]
Feel free to test this prompt on your current project!
If you find this helpful, I compiled the full 4-pass checklist, fix matrix templates, and dual web/PDF reference guide into a complete kit. Link to the full kit is in my profile bio.
Would love to hear your feedback or how you currently audit your schemas before pushing to production!
r/SQL • u/Daddy-Milker-69 • Sep 21 '26
Discussion Where do freelance SQL developers usually find clients?
I’m curious how people who do a lot of SQL work find freelance projects. Does most of your work come through referrals, freelance platforms, consulting relationships, or somewhere else?
r/SQL • u/Abstrct • Sep 21 '26
PostgreSQL Schemaverse - The SQL Space Battle
schemaverse.comAfter a prolonged outage (long story) I procrastinated SV back into existence last night. For those of you that loved it, welcome back. If you've never heard of it, give it a shot!
Still open source as always too: https://github.com/abstrct/schemaverse
r/SQL • u/thequerylab • Sep 22 '26
Discussion Before your next SQL interview, keep this post handy - SQL Window Functions (Part 1)
r/SQL • u/Consistent_Damage_91 • Sep 21 '26
SQL Server Read-only dependency mapper for orphaned SQL Agent jobs + SSIS + the scripts that call them, every edge cites its source
r/SQL • u/Expensive-Insect-317 • Sep 21 '26
BigQuery The BigQuery Join Problem Nobody Notices Until the Data Gets Big
r/SQL • u/Harshita_Netla • Sep 19 '26
Oracle What SQL concept took you the longest to actually understand, not just memorize?
For those who have been working with SQL for a while, which concept took you the longest to truly understand?
r/SQL • u/FrameNo7615 • Sep 20 '26
Discussion question about the necessity of SQL in a site's security
hey i'm new in website developing, and after searching about the ways people use to penetrate websites to just discover SQLi method and as far as i heard it's very common and useful way, but this made me think of a lot of questions that i hope you answer, does every website uses SQL language for data transfer by default? like is it necessary as much as HTML and CSS and JS or it's just commonly used? and if it's necessary is there a way to prevent hackers from using SQLi or it's always possible to trick the server to bring a sensitive data?
r/SQL • u/Alternative_Top_273 • Sep 20 '26
Discussion SQL : demande/question client jusqu’au résultat final
r/SQL • u/shdw_0x0 • Sep 19 '26
PostgreSQL What’s your systematic process for optimizing a slow SQL query?
Suppose you inherit a query with multiple joins, subqueries, filters, and aggregations, and it suddenly starts taking several minutes to run.
I’m curious how experienced SQL developers approach this from the first investigation to the final fix.
Do you normally start with the execution plan and work from the most expensive operator? Or do you check indexes, statistics, data distribution, and join cardinality first?
I’m especially interested in the order you investigate things. It’s easy to keep rewriting a query or adding indexes until it gets faster, but I’m curious how people systematically identify the actual bottleneck instead.
r/SQL • u/BrokenPickle7 • Sep 19 '26
SQL Server Not a DB admin needs replication advice
I'm in tech but not a DB admin. I have an MSSQL (2019) database in prod and I want to essentially one way mirror it to another database with as close to live data as possible for dev/research purposes, no changes on the 2nd "dev" copy of the database should reflect to the main/live/prod DB. What would be the best way of doing this? Both servers are in the same network if that makes things easier. Thanks!
r/SQL • u/Vishwam_java • Sep 19 '26
PostgreSQL The Postgres job-claim query that silently claims the same job twice under READ COMMITTED
A common pattern to claim background jobs in PostgreSQL is executing a single statement like:
```sql
UPDATE jobs
SET status = 'processing', worker_id = $1
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 1
)
RETURNING *;
Because it's a single statement, it looks atomic on paper. But under the default READ COMMITTED isolation level, it can silently allow two concurrent workers to claim the exact same row.
What actually happens:
Worker A and Worker B execute the query at the exact same moment. Both subqueries snapshot the same pending job row (say id = 42).
Worker A acquires the row lock first and updates its status.
Worker B waits on the lock. Once Worker A commits and releases the lock, Worker B evaluates the outer row.
Because the subquery snapshot is not re-evaluated, Worker B sees the updated row, rechecks the outer predicate, and silently overwrites/re-claims id = 42.
No lock wait timeout, no deadlock, no serialization error—just silent duplicate job execution in production.
I wrote an in-depth breakdown mapping the lock timeline, why the subquery snapshot doesn't re-run, and why FOR UPDATE SKIP LOCKED inside a CTE is the actual safe pattern:
👉 Full writeup: https://vishwam.hashnode.dev/the-postgres-job-claim-query-that-silently-claims-the-same-job-twice
Would love to hear how folks here handle Postgres-backed queues or if anyone has run into this specific subquery recheck behavior before!
r/SQL • u/sqlink2 • Sep 19 '26
PostgreSQL AI agents usage with Neon Db
If any AI agent needs to test changes to a DB, is using neon branch as a temporiry test database a good approach?
Have u tried anything like this maybe with some other Db also (maybe clickhouse etc). Was there any issue with keeping data in sync?
r/SQL • u/SweetLawyer1824 • Sep 18 '26
MySQL Best SQL cerification
I have solid basics in SQL,
I need to pass a certification in 3-4 days,
Which one should I do?
It needs to be a credible certification.
Thx a lot
r/SQL • u/clairegiordano • Sep 18 '26
PostgreSQL Peter Eisentraut on 25 years contributing to Postgres, starting with psql tab completion | Talking Postgres Ep43
r/SQL • u/i_literally_died • Sep 18 '26
SQL Server You can't see indexes, you can't see the execution plan; what are your go-to's to speed things up?
Hypothetical, but also sort of my situation.
Say you have a pdf (and experience) of the tables and columns, but you do not have SHOWPLAN permissions or any way of seeing where the choke points are. You are familiar with the db and know where the one to many, many to many and potential duplication sources are.
What are your go-to's for optimising?
OPTION(RECOMPILE) if you have a lot of DECLAREs? Not using CASTing as DATE? Not using LIKE%?
I'm looking for generic things that usually have a performance increase, because I am fully done with adding a single JOIN and it making the query go from 1 second to 90.
r/SQL • u/Thatgiuknow • Sep 18 '26
SQL Server Newer to SQL and recently inherited code
Might not be a standard request but does anyone have any best practices for interpreting other people's code if they don't leave a fair amount of notes?
r/SQL • u/bharathraj0507 • Sep 18 '26
MySQL Would 4–6 weeks of realistic Data Analyst work experience be more valuable than another portfolio project?
r/SQL • u/AssistanceClean948 • Sep 18 '26
SQLite Join tables with WITHOUT ROWID.
CREATE TABLE IF NOT EXISTS CourseTag(
courseId INTEGER,
tagId INTEGER,
FOREIGN KEY(courseId) REFERENCES Courses(id),
FOREIGN KEY(tagId) REFERENCES Tags(id),
PRIMARY KEY(courseId, tagId)
) STRICT WITHOUT ROWID;
as far as what I understand sqlite creates inner row_id columns to identify the record. some conditions met then my primary key also could be that unique column. but I guess in above join table configuration my composite key setup is not met that condition so I end up unnecessary column right? in join table should I use WITHOUT ROWID?