Toth K. - SQL Server 2014 Design & Programming

SQL Server 2014 Design & Programming Kalman Toth SQL Server 2014 Design & Programming Copyright 2014 by Kalman Toth ISBN-13: 978-1499529593 ISBN-10: 1499529597 Trademark Notices Microsoft is a trademark of Microsoft Corporation. SQL Server 2014 is a program product 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. ALL PHOTOS IN THIS BOOK ARE THE COPYRIGHTED PROPERTY OF THE AUTHOR 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 Design & Programming 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: SQL Server 2014 Database Design 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 INTRODUCTION SQL Server 2014 programming builds on achievements of decades in advanced relational database technology. Among the new SQL Server 2014 features is prominent: in-memory OLTP tables. Disk was always the slowest part of the computer system. Since memory is abundant, it is logical to place tables into memory to gain in performance.

Programming SQL Server 2014 is fun due to the host of marvelous tools available for the developer in SQL Server Management Studio and Visual Studio. Graphical query designer, for example, helps the developer to develop the JOIN structure of a complex query. SQL Server 2014 provides a highly productive environment for database software development. 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.