Skip to main content
main-content

Über dieses Buch

Learn effective and scalable database design techniques in a SQL Server 2016 and higher environment. This book is revised to cover in-memory online transaction processing, temporal data storage, row-level security, durability enhancements, and other design-related features that are new or changed in SQL Server 2016.

Designing an effective and scalable database using SQL Server is a task requiring skills that have been around for forty years coupled with technology that is constantly changing. Pro SQL Server Relational Database Design and Implementation covers everything from design logic that business users will understand, all the way to the physical implementation of design in a SQL Server database. Grounded in best practices and a solid understanding of the underlying theory, Louis Davidson shows how to "get it right" in SQL Server database design and lay a solid groundwork for the future use of valuable business data.

The pace of change in relational database management systems has been tremendous these past few years. Whereas in the past it was enough to think about optimizing data residing on spinning hard drives, today one also must consider solid-state storage as well as data that are constantly held in memory and never written to disk at all except as a backup. Furthermore, there is a trend toward hybrid cloud and on-premise database configurations as well a move toward preconfigured appliances. Pro SQL Server Relational Database Design and Implementation guides in the understanding of these massive changes and in their application toward sound database design.

Gives a solid foundation in best practices and relational theory

Covers the latest implementation features in SQL Server 2016

Helps you master in-memory OLTP and use it effectively

Takes you from conceptual design to an effective, physical implementation

What You Will LearnDevelop conceptual models of client data using interviews and client documentationRecognize and apply common database design patternsNormalize data models to enhance scalability and the long term use of valuable dataTranslate conceptual models into high–performing SQL Server databasesSecure and protect data integrity as part of meeting regulatory requirementsCreate effective indexing to speed query performanceWho This Book Is For

Programmers and database administrators of all types who want to use SQL Server to store data. The book is especially useful to those wanting to learn the very latest design features in SQL Server 2016, features that include an improved approach to in-memory OLTP, durability enhancements, temporal data support, and more. Chapters on fundamental concepts, the language of database modeling, SQL implementation, and of course, the normalization process, lay a solid groundwork for readers who are just entering the field of database design. More advanced chapters serve the seasoned veteran by tackling the very latest in physical implementation features that SQL Server has to offer. The book has been carefully revised to cover all the design-related features that are new in SQL Server 2016.

Inhaltsverzeichnis

Frontmatter

Chapter 1. The Fundamentals

I have a love–hate relationship with fundamentals. And the easier the task seems to be, the less enjoyable I seem to find it, at least unless I already have a love for the topic at some level. In elementary school, there were fun classes, like recess and lunch for example. But when handwriting class came around, very few kids really liked it, and most of those who did just loved the taste of the pencil lead. But handwriting class was an important part of childhood educational development. Without it, you wouldn’t be able to write on a white board, and without that skill, could you actually stay employed as a programmer? I know I personally am addicted to the smell of whiteboard marker, which might explain more than my vocation.

Louis Davidson, Jessica Moss

Chapter 2. Introduction to Requirements

If there is anything worse than doing a simple task without fully understanding the requirements for success, it is doing a complex one. It happens every day, computer projects are created with only a shimmer of an idea of what the end goal is. Sometimes this is really interesting, and leads you to something interesting. . .sometimes, but almost never when the goal of that project is to deliver value to a customer who knows what they want. Hence, someone involved in every software project must take time to figure out what the customer wants before any software designing starts. If you are very lucky, this will not be you, as capturing requirements is considerably more difficult than any task that will follow in later chapters.

Louis Davidson, Jessica Moss

Chapter 3. The Language of Data Modeling

A data model is one of the most important tools in the design process, but it has to be done right. It very often starts as a sketch of the data requirements that you use to communicate with the customer, and is refined over and over until you get it right. However, a common misconception is that a data model is only a picture of a database. That is partly true, but a model is truly so much more. A great data model includes nongraphical representations of pretty much everything about a database and serves as the primary documentation for the life cycle of the database. Aspects of the model will be useful to developers, users, and the database administrators (DBAs) who maintain the system.

Louis Davidson, Jessica Moss

Chapter 4. Conceptual and Logical Data Model Production

In this chapter, we are going to really wind things up and begin to apply the skills that were covered in the previous chapters and begin creating a data model. It won’t likely be identical to the final model that gets implemented by any means, but the goal of this model will be to serve as the basis for the eventual model that will get implemented. Personally, I both love and loathe this particular step in the process because this is where things get complicated. All of the requirements and documents need to be considered. The architects and programmers from all disciplines, ideally, will be collaborating to achieve a data model and designs for the user and back-end experiences. This step is where you will also get to be the most artistic, looking for unique and interesting solutions to a variety of data needs. This chapter is the final stop before we get to do what most programmers look forward to…writing code.

Louis Davidson, Jessica Moss

Chapter 5. Normalization

By now, you should have built the conceptual and logical model that covers the data requirements for your database system. As we have discussed over the previous four chapters, our design so far needn’t follow any strict format or method (even if it probably will), the main point being that it does have to cover the data-related requirements for the system that needs to be built.

Louis Davidson, Jessica Moss

Chapter 6. Physical Model Implementation Case Study

In some respects, the hardest part of the database project is when you actually start to create code. If you really take the time to do the design well, you begin to get attached to the design, largely because you have created something that has not existed before. Once the normalization task is complete, you have pretty much everything ready for implementation, but tasks still need to be performed in the process for completing the transformation from the logical model to the physical, relational model. We are now ready for the finishing touches that will turn the designed model into something that users (or at least developers!) can start using. At a minimum, between normalization and actual implementation, take plenty of time to review the model to make sure you are completely happy with it.

Louis Davidson, Jessica Moss

Chapter 7. Expanding Data Protection with Check Constraints and Triggers

One of the weirdest things I see in database implementations is that people spend tremendous amounts of time designing the correct database storage (or, at least, what seems like tremendous amounts of time to them) and then just leave the data unprotected with tables being more or less treated like buckets that will accept anything, opting to let code outside of the database layer to do all of the data protection. Honestly, I do understand the allure, in that the more constraints you apply, the harder development is in the early stages of the project, and the programmers honestly do believe that they will catch everything. The problem is, there is rarely a way to be 100% sure that code written will always enforce every rule.

Louis Davidson, Jessica Moss

Chapter 8. Patterns and Anti-Patterns

There is an old saying that you shouldn’t try to reinvent the wheel, and honestly, in essence it is a very good saying. But with all such sayings, a modicum of common sense is required for its application. If everyone down through history took the saying literally, your car would have wheels made out of the trunk of a tree (which the MythBusters proved you could do in their “Good Wood” episode), since that clearly could have been one of the first wheel-like machines that was used. If everyone down through history had said “that’s good enough,” driving to Walley World in the family truckster would be a far less comfortable experience.

Louis Davidson, Jessica Moss

Chapter 9. Database Security and Security Patterns

I have a love–hate relationship with fundamentals. And the easier the task seems to be, the less enjoyable I seem to find it, at least unless I already have a love for the topic at some level. In elementary school, there were fun classes, like recess and lunch for example. But when handwriting class came around, very few kids really liked it, and most of those who did just loved the taste of the pencil lead. But handwriting class was an important part of childhood educational development. Without it, you wouldn’t be able to write on a white board, and without that skill, could you actually stay employed as a programmer? I know I personally am addicted to the smell of whiteboard marker, which might explain more than my vocation.

Louis Davidson, Jessica Moss

Chapter 10. Index Structures and Application

In this life, we have to make many choices. Some are very important choices. Some are not. Many of our choices are between good and evil. The choices we make, however, determine to a large extent our happiness or our unhappiness, because we have to live with the consequences of our choices.

Louis Davidson, Jessica Moss

Chapter 11. Matters of Concurrency

If you try to multitask in the classic sense of doing two things at once, what you end up doing is quasi-tasking. It’s like being with children. You have to give it your full attention for however much time you have, and then you have to give something else your full attention.

Louis Davidson, Jessica Moss

Chapter 12. Reusable Standard Database Components

As we near the end of the database design process, the database is pretty much completed from a design standpoint. We have spent time looking at performance, concurrency, and security patterns that you can follow to help implement the database in a manner that will work well under most any typical OLTP-style load. In this chapter (and again somewhat in the next), we are going to look at applying “finishing touches” to the database that can be used to enhance the user experience and assist with the querying and maintaining of the database.

Louis Davidson, Jessica Moss

Chapter 13. Architecting Your System

So far in the book, we started with how one should design a database without regard for technology, then covered how one might implement that database using SQL Server. So far the process has been one of building one precept upon another. The one topic we really haven’t breached is data access.

Louis Davidson, Jessica Moss

Chapter 14. Reporting Design

People use reporting in every aspect of their daily lives. Reports provide the local weather, the status of the morning train, and even the best place to get a cup of joe. And this is all before getting to work! Once at work, reports include enterprise dashboards, system outage reports, and timesheet cards. Any way you slice it, reporting is something that everyone uses.

Louis Davidson, Jessica Moss

Appendix A. Scalar Datatype Reference

Choosing proper datatypes to match the domain to satisfy logical modeling is an important task. One datatype might be more efficient than another of a similar type. For example, you can store integer data in an integer datatype, a numeric datatype, a floating point datatype, a character type, or even a binary column, but these datatypes certainly aren’t alike in implementation or performance.

Louis Davidson, Jessica Moss

Backmatter

Weitere Informationen

Premium Partner

Neuer Inhalt

BranchenIndex Online

Die B2B-Firmensuche für Industrie und Wirtschaft: Kostenfrei in Firmenprofilen nach Lieferanten, Herstellern, Dienstleistern und Händlern recherchieren.

Whitepaper

- ANZEIGE -

Best Practices für die Mitarbeiter-Partizipation in der Produktentwicklung

Unternehmen haben das Innovationspotenzial der eigenen Mitarbeiter auch außerhalb der F&E-Abteilung erkannt. Viele Initiativen zur Partizipation scheitern in der Praxis jedoch häufig. Lesen Sie hier  - basierend auf einer qualitativ-explorativen Expertenstudie - mehr über die wesentlichen Problemfelder der mitarbeiterzentrierten Produktentwicklung und profitieren Sie von konkreten Handlungsempfehlungen aus der Praxis.
Jetzt gratis downloaden!

Bildnachweise