"SQL Query Design Patterns and Best Practices" My review!

Hi Guys!

It’s been a while since the last post ...it’s been a lot of work!
In the meanwhile SQL Server 2022 get its second CU (download) and SSMS has reached version 19.02 (download)

However we are here and so …welcome!

I must say I like to review books because this is also a good way to stay updated.
For this I say a big thanks both the publisher Packt and the kind Nivedita Singh for offering me, once again, the opportunity to talk about their new book.

Packt is an English publishing house known worldwide and specialized in books related to information technology, including programming, web design, data analysis and hardware.

This new book will be published on March 22nd ( but yes you can preorded on Amazon here: https://packt.link/AYWje ) and it is related to the topic writing of Queries in SQL Server.

Its title is "SQL Query Design Patterns and Best Practices" and the authors are: Steve Hughes, Dr. Ram Babu Singh, Leslie Andrews, Dennis Neer, Shabbir H. Mala and Chi Zhang.

All the authors actually works in 3Cloud, a Gold-Certified Microsoft Azure technology consulting firm and Azure Expert Managed Services Provider.

3Cloud provides cloud strategy, design, implementation, acquisition and ongoing managed services to its clients across various industries.
All of them have been working in their industry for over 20 years.

And if you want to take a look at the previous review SQL Server Query Tuning and Optimization" by Benjamin Nevarez

SQL Query Design Patterns and Best Practices. The review.

One of the book’s focal points is to teach you to write query that are readable and mantainable.
The book has 270 pages and 4 sections where the first 3 are related to the T-SQL standard and the last is dedicated to the performance tuning.

In this book you will read about all the topics that you will need if you need to work with SQL Server.

In more detail.

The first of the four sections, made up of four chapters, is called "Refining Your Queries to Get the Results You Need".
In these chapters the goal is to increase your efficiency as query writer and so the book talks about data aggregation (showing the most common types of aggregation available in SQL Server such as the AVG, MIN, MAX, SUM and COUNT functions) and data fomatting (showing the functions FORMAT, CAST, CONVERT, ROUND.
Finally we read about conditional SQL having a look at the CASE, COALESCE and ISNULL functions.

The second section contains 4 chapters and I begin by explaining through examples how the (Common table Expression) commonly called CTE work. It is then shown how CTEs can also support recursivity.

In the following chapter we talk about window functions, how they work and how we can use them For example is shown how to make so-called "running totals".
It then goes to the operators PIVOT and UNPIVOT

The last chapter of this section is dedicated to security and shows how you can set access to individual tables per user.

If the first two sections were dedicated to query writing, the next section is about performance.
The following two chapters show how to read a query execution plan, what indexes are and how to use them to increase performance.

The fourth and final section, talks about three important topics that have gained notoriety in recent years. These topics are: the management of JSON data in SQL Server, read file data and the contents of data lakes and finally the Jupiter notebook that allow you to manage and organize projects made of many queries in a single file.

For each chapter many examples to follow step by step are provided.

I think that if you work with SQL Server, all the topics presented in this book are to be known absolutely.


That'all guys!
Luke "STAY TUNED"











Previous post: How to get the n-th row in a SQL table ..in 4 different ways! Simple, clear and with example!

Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?    I
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo
Read more

How to solve EXECUTE Permission denied on object 'sp_send_dbmail'

Image
Hi guys, Welcome back ! Today a light five minute post to talk about this error: EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. ..or in italian language: Autorizzazione execute negata per l'oggetto 'sp_send_dbmail' del database 'msdb' con schema 'dbo' The sp_send_dbmail You know that this stored procedure is used to send a mail through SQL server. You know also that this stored procedure is contained into the MSDB system Databases. But if you get the error above? What can you do? How to solve the execute permission denied on sp_send_dbmail You should consider that: Your user must be in the MSDB database.  Your user must be also member of the DatabaseMailUserRole .  You can solve the problem in this way: 1) In the Object Explorer of the SQL Server management Studio, locate the Security / Account menu. Locate your user and do a double click on it. 2) In the opene
Read more