r/learnSQL • u/Due_Counter_2286 • 4d ago
What are some interesting SQL problems or query mistakes worth discussing?
I'm planning to share some SQL-related content on LinkedIn, but I want to avoid the usual beginner-level tutorials or generic interview questions.
I'm particularly interested in intermediate-level SQL concepts, window functions, CTEs, tricky query behavior, or situations where a query looks correct but produces unexpected results.
I'd love to hear from people working with SQL regularly.
What's an interesting SQL problem, subtle query mistake, or unexpected behavior you've encountered that made you think twice?
Looking for something technically meaningful that could spark a good discussion rather than another basic SQL tutorial.
1
u/you_are_wrong_tho 4d ago
drop table if exists #temp
create table #temp (id int identity (1,1) not null, textValue varchar(200))
Insert into #temp (textValue)
values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')
select * from #temp
truncate table #temp
Insert into #temp (textValue)
values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')
select * from #temp
delete from #temp
Insert into #temp (textValue)
values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')
select * from #temp
This behavior screwed up an ETL process for me once. So I thought, easy, just reseed!
1
1
u/amuseboucheplease 4d ago
Can you explain what's happening /not happening correctly here?
1
u/you_are_wrong_tho 4d ago
when you truncate a table (instead of deleting/dropping then recreating) it starts the identity column where it left off. so you think, ill just reseed to 0, then you can see what happens in different scenarios in the comment below.
1
u/amuseboucheplease 4d ago
Ahh thank you. Is this typically a problem for temp tables?
1
u/you_are_wrong_tho 4d ago
Not just temp tables, any table
1
u/amuseboucheplease 4d ago
Why would ID be used in temp table though?
1
u/you_are_wrong_tho 4d ago
to have an id to batch through during an insert/upsert/delete etc
1
u/amuseboucheplease 4d ago
You wouldn't do that through a cte?
1
u/you_are_wrong_tho 4d ago
as is the answer with many things sql, it depends. but generally no I would not use a cte to do batching

1
u/Imaginary__Bar 4d ago
It's your post, you decide! Otherwise you're just posting for the sake of posting and that is simply pointless for everyone.