DEV Community

Cover image for 5 Essential SQL Skills Learned from a Decade of Experience

5 Essential SQL Skills Learned from a Decade of Experience

David Au Yeung on November 20, 2024

In my ten years of working with SQL, I've honed several critical skills that significantly enhance database management and data manipulation. Here’...
Collapse
 
aaronre16397861 profile image
Aaron Reese

Thanks for taking the time to craft a post on SQL. I have to call out a couple of things.
1) I'm not sure which dialect of SQL you are using but it is generally not recommended to use GUIDs or UUIDs as identity keys. There are many reasons but the primary one is index fragmentation.
2) on #2 (if exists) you say it is a good way to prevent duplicates when doing batch updates however your code leads to individual transactions for each update which will be magnitudes slower than a set-based update. In a batch situation this should be a left joint to the target table and only insert records where the joined table identifier is null. Ideally duplicate records would be prevented by a Unique Key.
3) I know code examples are simplified but your temp table would be better as a CTE. By using a temp table you lose any indexing optimisations and table locking as the underlying source data could change after building the temp table.
4) your transaction example is valid, however it is more relevant where you need to update multiple tables or records where system integrity demands all succeed or all fail. Examples would be a} update order status to shipped AND reduction qtys from product availability, b) debit my account and credit your account in a bank transfer.
In your example no other process can update the product table (and possibly read from it) until you choose to commit or rollback.

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

Hi Aaron, thanks for joining the discussion.

Regarding your first point, while using BIGINT can be sufficient for most situations when considering performance, there are cases where GUIDs can be beneficial. In complex scenarios that involve multiple tables, databases, or servers, GUIDs can provide a significant advantage. Additionally, many replication scenarios often require the use of GUIDs to ensure uniqueness across different systems.

Collapse
 
aaronre16397861 profile image
Aaron Reese

I'm not saying don't include UUIDs, just don't use them as a primary key. In most scenarios the vast majority of records you are interested in are the most recent and so you want them grouped together in the index to make page writes faster and generate fewer pages splits (and have a higher page full percentage to save space). UUIDs by their nature are spread evenly over the pages so you need more of them, less full and the entire index has to be traversed to get records with a common profile (normally temporal)
If you need database unique identifier I would normally prefer TIMESTAMP to UUID.

Thread Thread
 
auyeungdavid_2847435260 profile image
David Au Yeung

Yes Aaron, you are right!

Thread Thread
 
bd9000 profile image
David Nuss

Agreed. Using UUIDs and GUIDs as indexes absolutely destroys performance.

Thread Thread
 
auyeungdavid_2847435260 profile image
David Au Yeung

Yes, David, I appreciate your feedback. I will incorporate better practices in my next example. Thank you!

Collapse
 
dan_maroff profile image
Dan Maroff

I think what Aaron is saying is that using GUID types as a primary key will create a fragmented clustered index. Primary keys should be a sequential type (INT, BIGINT) otherwise your joins will result in inefficient table scans. With that said, GUIDs as you mentioned are a great way to uniquely identify a records across different systems or environments. But it’s more efficient to put those GUIDs in a separate column and keep a sequential type as the primary key.

Thread Thread
 
auyeungdavid_2847435260 profile image
David Au Yeung

Thanks for your clarification, I got the point now.

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

Point 2 is particularly useful in real-world scenarios. Imagine receiving a ton of unstructured information that needs to be organized for various ad hoc promotional events. In such cases, you might find yourself willing to trade off some performance in exchange for improved data accuracy :)

Collapse
 
nh profile image
Neil Hoskins

Really useful article and well written.

How about putting your transaction in a TRY/CATCH? Also, use the SQL Query Analyzer and now, ask your AI friend how to maximize efficiency. I've learnt more from doing this than any book I've read (I've been working with MS SQL Server since v7).

Everyone has a different opinion on how to do things (just read the comments here). The key for me is readability (#3), it's like coding software, use comments, format your code, just remember that someone else will inevitably have to maintain your code for you at some point.

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

Well said, Neil. Using TRY...CATCH with transactions is indeed common in stored procedures, especially for handling issues like division by zero. However, my main point in this article is to emphasize that using BEGIN TRAN is a best practice for everyone. It helps prevent human errors during data manipulation, particularly when working long hours 😉

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

And what kind of SQL analyzer you recommend? I usually only use execution plan for analysis.

Collapse
 
nh profile image
Neil Hoskins

I use 'Estimated Plan' in Azure Data Studio, so same. There are other tools on the market though, I tend to stick with what I know works for me.

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

If you want to learn more about SQL, please comment here :)

Collapse
 
ecstatic_niya profile image
Niya

Interested

Collapse
 
ladetunji profile image
Ladetunji OSIBANJO

I'd love to

Collapse
 
donsmak profile image
donsmak

damn thats so interesting thanks alottt

Collapse
 
auyeungdavid_2847435260 profile image
David Au Yeung

I will share more about SQL later, thanks for your support ;)

Collapse
 
nasma_agencywork_0b81c971 profile image
Comment deleted
Thread Thread
 
nasma_agencywork_0b81c971 profile image
Comment deleted
Thread Thread
Collapse
 
dev_olatunji profile image
Oladipupo Isaac Tunji

This is amazing. Thanks for sharing.

Collapse
 
aniruddhaadak profile image
ANIRUDDHA ADAK

wow amazing .

Collapse
 
dirktay profile image
Dirk Taylor

Thanks!

Collapse
 
jeremymoorecom profile image
Jeremy Moore

Thank you for sharing