3 ms·

Pretty senior developer here (manager now, actually) but I'm realizing that other than college, I haven't really picked up a book on SQL. What would you recomm

by Spoom 2y ago

Pretty senior developer here (manager now, actually) but I'm realizing that other than college, I haven't really picked up a book on SQL. What would you recommend?

Spoom 2y ago
Point of clarification: I've used SQL almost my entire development career, with multiple RDBMS engines, I've simply never looked at a book solely on SQL.
7thaccount 2y ago
Just go through the W3Schools website first. It's free and you can write queries in the little window to test out things against their simple database.
baq 2y ago
I usually recommend the table of contents of your RDBMS but just grab whatever you can access quickest which has good reviews.
rawgabbit 2y ago
For SQL Server, I would read through these articles: https://www.itprotoday.com/sql-server/sql-server-database-engine-basics https://www.itprotoday.com/sql-server/sql-server-database-en.... Explains role of query plan (can pull either from actual file or from an index(s)) and write-ahead-log (Microsoft gave it the unfortunate name of Transaction Log) for hardening. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/p.... Explains how to use the Query Store to identify poor performing queries. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/s.... Explains that SQL Server handles the problem of two concurrent users attempting to modify the same record at the same time via DEADLOCKS. You want to use Extended Events to troubleshoot deadlocks. https://learn.microsoft.com/en-us/azure/architecture/patterns/category/data-management https://learn.microsoft.com/en-us/azure/architecture/pattern.... Explains how CQRS and Event Sourcing architectures can help minimize deadlocks. https://www.business-case-analysis.com/accounting-cycle.html https://www.business-case-analysis.com/accounting-cycle.html. I would argue the best architecture to minimize deadlocks is to mimic what accounting systems do. Multiple Clients submit order requests to a batch system that posts/processes them sequentially. Until the central batch system posts, the transaction is pending. Similar to how Amazon accepts your order request instantaneously; but it may take several minutes for your order to be confirmed.
- rawgabbit 2y ago
  For bonus minutiae, SQL Server has a problem with upserts. Here is the old school and the new way for handling it. https://sqlperformance.com/2020/09/locking/upsert-anti-pattern https://sqlperformance.com/2020/09/locking/upsert-anti-patte... https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking https://learn.microsoft.com/en-us/sql/relational-databases/p...
  - pragmatic 2y ago
    The second is azure sql only.

by Spoom 2y ago

Pretty senior developer here (manager now, actually) but I'm realizing that other than college, I haven't really picked up a book on SQL. What would you recommend?

Spoom 2y ago

Point of clarification: I've used SQL almost my entire development career, with multiple RDBMS engines, I've simply never looked at a book solely on SQL.

7thaccount 2y ago

Just go through the W3Schools website first. It's free and you can write queries in the little window to test out things against their simple database.

baq 2y ago

I usually recommend the table of contents of your RDBMS but just grab whatever you can access quickest which has good reviews.

rawgabbit 2y ago

For SQL Server, I would read through these articles: https://www.itprotoday.com/sql-server/sql-server-database-engine-basics https://www.itprotoday.com/sql-server/sql-server-database-en.... Explains role of query plan (can pull either from actual file or from an index(s)) and write-ahead-log (Microsoft gave it the unfortunate name of Transaction Log) for hardening. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/p.... Explains how to use the Query Store to identify poor performing queries. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/s.... Explains that SQL Server handles the problem of two concurrent users attempting to modify the same record at the same time via DEADLOCKS. You want to use Extended Events to troubleshoot deadlocks. https://learn.microsoft.com/en-us/azure/architecture/patterns/category/data-management https://learn.microsoft.com/en-us/azure/architecture/pattern.... Explains how CQRS and Event Sourcing architectures can help minimize deadlocks. https://www.business-case-analysis.com/accounting-cycle.html https://www.business-case-analysis.com/accounting-cycle.html. I would argue the best architecture to minimize deadlocks is to mimic what accounting systems do. Multiple Clients submit order requests to a batch system that posts/processes them sequentially. Until the central batch system posts, the transaction is pending. Similar to how Amazon accepts your order request instantaneously; but it may take several minutes for your order to be confirmed.

rawgabbit 2y ago
For bonus minutiae, SQL Server has a problem with upserts. Here is the old school and the new way for handling it. https://sqlperformance.com/2020/09/locking/upsert-anti-pattern https://sqlperformance.com/2020/09/locking/upsert-anti-patte... https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking https://learn.microsoft.com/en-us/sql/relational-databases/p...
- pragmatic 2y ago
  The second is azure sql only.