Toth - SQL Server 2014 Database Design

SQL Server 2014

Database Design

Kalman Toth

SQL Server 2014 Database Design

Copyright 2014 by Kalman Toth

ISBN-13: 978-1499367676

ISBN-10: 1499367678

Trademark Notices

Microsoft is a trademark of Microsoft Corporation.

SQL Server 2012 is a program product of Microsoft Corporation.

SQL Server 2008 is a program product of Microsoft Corporation.

SQL Server 2005 is a program product of Microsoft Corporation.

SQL Server 2000 is a program product of Microsoft Corporation.

SQL Server Management Studio (SSMS) is a program product of Microsoft Corporation.

SQL Server Data Tools (SSDT) is a program product of Microsoft Corporation.

Microsoft Help Viewer is a program product of Microsoft Corporation.

SQL Server Analysis Services (SSAS) is a program product of Microsoft Corporation.

SQL Server Integration Services (SSIS) is a program product of Microsoft Corporation.

SQL Server Reporting Services (SSRS) is a program product of Microsoft Corporation.

SQL Azure is a program product of Microsoft Corporation.

Office Visio is a program product of Microsoft Corporation.

Exam 70-461 is an exam product of Microsoft Corporation.

ORACLE is a trademark of ORACLE Corporation.

Java is a trademark of ORACLE Corporation.

DB2 is a trademark of IBM Corporation.

SYBASE is a trademark of Sybase Corporation.

McAfee is a trademark of McAfee Corporation.

Warning and Disclaimer

Every effort has been made to ensure accuracy, however, no warranty or fitness implied. The information & programs are provided on an "as is" basis.

SQL Server 2014 Database Design

Contents at a Glance

ABOUT THE AUTHOR

Kalman Toth has been working with relational database technology since 1990. One day his boss at a commodity brokerage firm in Greenwich, Connecticut, had to leave early. He gave his SQL Server login and password to Kalman along with a small SQL task. Kalman was a C/C++ developer fascinated by SQL. Therefore, he studied a Transact-SQL manual 3 times from start to finish "dry", without any server access. His boss was satisfied with the results of the SQL task and a few days later Kalman's dream came true: he got his very own SQL Server login.

Kalmans relational database career since then includes database design, database development, database administration, OLAP architecture, and Business Intelligence development. Applications have included enterprise-level general ledger and financial accounting, bond funds auditing, international stock market feeds processing, broker-dealer firm risk management, derivative instruments analytics, consumer ecommerce database management for online dating, personal finance, physical fitness, and diet and health. His MSDN forum participation in the Transact-SQL and SQL Server Tools was rewarded with the Microsoft Community Contributor award. Kalman has a Master of Arts degree in Physics from Columbia University and a Master of Philosophy degree in Computing Science, also from Columbia. Additionally, he has Microsoft certifications in database administration, development, and Business Intelligence. His dream SQL career took him across the United States and Canada as well as to South America and Europe. SQL also involved him in World History. At one time he worked for Deloitte and Touche on the 96th floor of World Trade Center North. On September 11, 2001, he was an RDBMS consultant at Citibank on 111 Wall Street. After escaping at 10:30 on that fateful Tuesday morning in the heavy dirt and smoke, it took 10 days before he could return to his relational database development job just 1/2 mile from the nearly three thousand victims buried under steel.

What Kalman loves about SQL is that the same friendly, yet powerful, commands can process 2 records or 2 million records or 200 million records in the same easy way. Currently, he is Principal Trainer at www.sqlusa.com. His current interest is Artificial Intelligence. He is convinced that machine intelligence will not only replace human intelligence, but will even surpass it by millions of times in the near future. Kalmans hobby is flying gliders and vintage fighter planes. Kalman is moderator on the following MSDN forums: Database Design, Transact-SQL and Getting Started with SQL Server.

OTHER BOOKS & EBOOKS BY THE AUTHOR:

Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

SQL Server 2012 Programming

SQL Server 2012 Database Design

Beginner SQL Programming Using Microsoft SQL Server 2012

Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

SQL Server 2012 SELECT Statement Amazon Instant Video

SQL Server 2012 Administration

1000 Difficult Word Search Puzzles to Improve Your IQ

Brain Puzzles for Adults (IQ BOOST PUZZLES)

CONTENTS

This page is intentionally left blank.

INTRODUCTION

SQL Server 2014 introduces 3 new database design features: in-memory OLTP tables, inline index declaration in CREATE TABLE statement and updateable clustered columnstore indexes. In-memory OLTP aims to boost performance by reducing disk io as memory capacities are expanding frequently beyond the size of the database. SQL Server 2012 columnstore indexes are static. Updateable columnstore index expands the applicability of the performance feature. Inline index declaration elevates indexes to the level of PRIMARY KEY, FOREIGN KEY, CHECK constraint and DEFAULT constraint which can be declared inline.

Developers across the world face database issues daily. While immersed in procedural languages with loops, RDBMS forces them to think in terms of sets without loops. It takes transition. It takes training. It takes experience. Developers are exposed also to Excel worksheets, or spreadsheets, as they were called in the not so distant past. So, if you know worksheets, how hard can databases be? After all, worksheets look pretty much like database tables, dont they? The big difference is the connections among well-designed tables. A database is a set of connected tables, which represent entities in the real world. A database can be 100 connected tables or 3000. The connection is very simple: row A in table Alpha has affiliated data with row B in table Beta. However, even with 200 tables and 300 connections (FOREIGN KEY references), it takes a good amount of time to become familiar to the point of having an acceptable working knowledge.

"The Cemetery of Computer Languages" is expanding. You can see tombstones like PL/1, Forth, Ada, Pascal, LISP, RPG, APL, SNOBOL, JOVIAL, Algol the list goes on. For some, the future is in question: PowerBuilder, ColdFusion, FORTRAN and COBOL. On the other hand, SQL is running strong after 3 decades of glorious existence. What is the difference? The basic difference is that SQL can handle large datasets in a consistent manner based on mathematical foundations. You can throw together a computer language easily: assignment statements, looping, if-then conditional, 300 library functions, and voila! Here is the new language: Mars/1, named after the red planet to be fashionable with NASA's new Mars robot. However, can Mars/1 JOIN a table of 1 million rows with a table of 10 million rows in a second? The success of SQL language is so compelling that other technologies are tagged onto it like XML/XQuery, which deals with semi-structured information objects. In SQL you are thinking at a high level. In C# or Java, you are dealing with details lots of them. That is the major difference.

Why is so much of the book dedicated to database design? Why not plunge into SQL coding and eventually the developer will get a hang of the design? Because high-level thinking requires thinking at the database design level. A farmer has six mules. H how do we model it in the database? We design the Farmer and FarmAnimal tables, and then connect them with FarmerID FOREIGN KEY in FarmAnimal referencing the FarmerID PRIMARY KEY in the Farmer table. What is the big deal about it? It looks so simple. In fact, how about just calling the tables Table1 and Table2 to be more generic. Ouch! Meaningful naming is the very basis of good database design. Relational database design is truly simple for simple well-understood models. The challenge starts in modeling complex objects such as financial derivative instruments, airplane passenger scheduling, or a social network website. When you need to add 5 new tables to a 1000 table database and hook them in (define FOREIGN KEY references) correctly, it is a huge challenge. To begin with, some of the five new tables may already be redundant, but you don't know that until you understand what the 1000 tables are really storing. Frequently, learning the application area is the biggest challenge for a developer when starting a new job.