What if we can’t change the design? — My reflection of <Effective SQL> Part 3

<Effective SQL: 61 Specific Ways to Write Better SQL> gave me many tips for using the database.

The following links are reflections of previous chapters. You can feel free to navigate them.

This article is my third note and the reflection of this book with additional references. It includes the note for the following paragraphs:

• Item 18: Use Views to Simplify What Cannot Be Changed
• Item 20: Create Summary Tables and Maintain Them
• Item 21: Use UNION Statements to “Unpivot” Non-normalized Data

Item 18: Use Views to Simplify What Cannot Be Changed

The view is the pre-defined searching query for tables or view. It simplifies tables.

1.What can view help for us?

• We can use it to improve the denormalized table.

Recall the example in item 2. If we can’t change tables, we can use view to simplify existed tables.

We can also use the following code to generate normalized views.

• We can use it to get rid of repeating groups. Recall the repeating groups in item 3.

We can also use the following code to generate views without repeating groups.

2. The restrictions of using views

• The view with the union or distinct can’t be updated. SQL server and some systems provide triggers in the view. We can use triggers to update the origin tables.

3. The concerns for using view

• Don’t refer to other views in a view. The optimizer has to decompose the view. If views are in a view, the optimizer has to decompose them as well. It will cause the performance issue.

4. The conditions for using view

• Use it when we are focus on specific tables.
• Use it when we want to simplify tables or rename the column name.
• Use it when we want to get data from different tables together.
• It can make sure the consistency of the data operations.
• Use it when we want to hide the sensitive data.

Item 20: Create Summary Tables and Maintain Them

It’s much easier to understand the data structure and can make sure we have already summarized all data together.

1. How do summary tables work?

• Use triggers to update the summary table when detail tables update.
• Use stored procedures updates the summary table periodically.

2. Summary tables in SQL server

In SQL server, it provides the indexed view. You can refer to the following code.

3. The concerns for using summary tables

• It needs storage.
• We have to manage the summary table and the origin tables. (e.g., Triggers, Constraints, Stored procedures)
• We have to set the scheduler to update the summary table.

4.The alternative way instead of using the summary table

To reduce the effort of triggers, constraints, and stored procedures, we can use inline summarization instead of using the summary table.

Item 21: Use UNION Statements to “Unpivot” Non-normalized Data

1. The rules for using union

• The number of columns for composing union query has to be equal.
• The order of columns for composing union query has to be equal.
• The data type of columns for composing union query has to be equal.

2. The details for using union

• The union can get rid of repeating rows. We can use union all if we don’t want to get rid of repeating rows.
• Use as to identify data from the different tables.
• Use order by to sort. We can place the order by statement at the end of the last select statement.

We can use the following code.

Summary

Thanks for your patient. I am Sean. I work as a software engineer.

This article is my note. Please feel free to give me advice if any mistakes. I am looking forward to your feedback.

Please feel free to clap if this article can help you. Thank you.

You can also subscribe my page on Facebook.

Related topics

How to use the two-way binding in Knout.js and ReactJS?

My reflection of :

APM & Logging Services:

IT & Network:

Database:

Software testing:

Debugging:

DevOps:

References

• Create Indexed Views