Microsoft SQL Server 2012 T-SQL

Tom Coffing, Debra Aaron

• Publisher: Coffing Publishing
• Year: 2015
• eBook ISBN: 9781940540306
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

One of the most popular databases worldwide is the Microsoft's SQL Server. The Microsoft Azure Cloud is rapidly making T-SQL one of the standards of SQL among millions of companies. The Microsoft SQL Server 2012 T-SQL book is your key to mastering T-SQL. With easy to use sample tables, and 700 pages of real-world example's and explanations, this book is your perfect reference guide. Each chapter in this book starts with simple examples and continues to use a building block approach to teach both the new user and the advanced user. Pleasing to the eye, and stimulating to the mind, makes this book a worldwide masterpiece where all can learn.

Chapter 1 – Introduction

“Fall seven times – Stand up eight.”

– Japanese proverb

Introduction to the Family of SQL Server Products

Microsoft SQL Server Compact 4.0

Microsoft SQL Server Compact 4.0 is a compact database that is embedded inside Nexus and other desktops around the world. It is ideal for also embedding in web applications. SQL Server Compact 4.0 provides developers a common programming model with other SQL Server editions. This is important for developing both native and managed applications. SQL Server Compact provides outstanding flexibility, but in a small footprint.

SQL Server 2014 Express Edition

Microsoft provides this for free! This powerful database engine is perfect for embedded applications or for redistribution with other solutions. Independent software vendors (ISV's) use it to build desktop applications. If you need support for greater than 10 GB databases, SQL Server Express is compatible with other editions of SQL Server.

SQL Server Standard Edition

Microsoft's robust data management and business intelligence database is ideal for departments and small workgroups. It supports common development tools for both on premise and cloud applications. This edition enables effective database management with minimal IT resources and it is compatible with other editions.

Above are the first three offerings from Microsoft on SQL Server.

Introduction to the Family Continued

Microsoft SQL Server Web Edition

Microsoft's Web edition is a low total-cost-of-ownership option to host Web applications that provides scalability, affordability, and manageability capabilities for small to large scale Web initiatives.

SQL Server 2014 Business Intelligence Edition

Microsoft's Business Intelligence edition is for the BI intelligence community and delivers a comprehensive platform. This empowers organizations to build and deploy secure, scalable and manageable BI solutions. It has browser based data exploration and visualization, plus includes powerful integration capabilities.

SQL Server 2014 Enterprise Edition

Microsoft's SQL Server 2014 Enterprise edition delivers high-end datacenter capabilities with performance that has been enhanced for virtualization, business intelligence and integration capabilities. This enables high service levels for mission-critical workloads and end user access to data insights.

Above are the next three offerings from Microsoft on SQL Server.

Microsoft Azure SQL Data Warehouse

Azure SQL Data Warehouse

Microsoft's Azure SQL Data Warehouse is a massively parallel processing (MPP) data warehousing appliance built for any volume of relational data and provides integration to Hadoop. Azure SQL Data Warehouse can provide up to 100x performance gains over other SQL Server platforms. This is the MPP platform that provides linear scalability for when data volumes grow and the number of users increases.

Azure SQL Data Warehouse is designed to parallelize and distribute the processing across multiple Symmetric Multi-Processing (SMP ) compute nodes. Azure SQL Data Warehouse is only available as part of Microsoft’s Analytics Platform System (APS) appliance.

Azure SQL Data Warehouse is a shared-nothing architecture, which means each processor has its own operating system, memory and set of disks. Nothing is shared! Data is “horizontally partitioned” across nodes. This means that each node has a subset of the rows from each table in the database. Each node is then responsible for processing only the rows on its own disks.

Above is the information about Microsoft's Azure SQL Data Warehouse, which is Microsoft's MPP system for cloud computing and available on the Azure cloud.

Nexus is Now Available on the Microsoft Azure Cloud

NEXUS QUERY CHAMELEON

Why the Nexus Chameleon should be your query tool of choice:

1)Queries every major system

2)Provides visualization and automatically writes the SQL

3)Can perform cross-system joins with a few clicks of the mouse

4)Converts table structures and moves the table and data between systems

5)Compares and synchronizes databases

6)Can move an entire database of tables or views between systems

7)Has the "Garden of Analysis" to re-query answer sets inside your PC

8)Provides a dashboard of graphs and charts for answer sets

Download the Nexus for a free trial at www.CoffingDW.com and use Nexus in-house or on the Azure cloud.

Symmetric Multi-Processing (SMP)

A Symmetric Multi-Processing system has multiple processors for extra power, but these processors share a single operating system, memory pool and they share access to the disks. This is a great architecture for speed, similar to a restaurant that is quick and organized, but it lacks the ability for unlimited expansion. When there are too many cooks in the kitchen you need an MPP system that scales many SMP systems together as one parallel processing data warehouse.

A Symmetric Multi-Processing (SMP) system is what Microsoft is known for in their SQL Server suite of products.

Naming of Objects

Object names can include the following characters:

•Letters from a-z, A-Z, and letter characters from other languages.

•Numeric characters 0-9.

•Special characters _, @, $, #.

Object names must also meet the following requirements:

•Object names must not be reserved words.

•Embedded spaces are not allowed in object names.

•Unicode supplementary characters are not allowed in object names.

More facts about objects:

Database, table, and view names must be 1-128 characters long and begin with a letter character. They are case insensitive. Database names are unique per system. Table and view names must be unique per database.

Index, column, and statistics names must be 1-32 characters long and begin with a letter character. They are case insensitive. Index names are unique per database. Column and statistics nam...

Table of contents

• Cover
• The Tera-Tom Video Series
• The Tera-Tom Genius Series
• Tera-Tom- Author of over 50 Books
• The Best Query Tool Works on all Systems
• Trademarks and Copyrights
• About Tom Coffing
• About Debra Aaron
• Contents
• Chapter 1 – Introduction
• Chapter 2 – The Basics of SQL
• Chapter 3 – The WHERE Clause
• Chapter 4 – Distinct, Group By and TOP
• Chapter 5 – Aggregation
• Chapter 6 - Join Functions
• Chapter 7 – Date Function
• Chapter 8 - Temporary Tables
• Chapter 9 – Sub-query Functions
• Chapter 10 – Window Functions OLAP
• Chapter 11 - Working with Strings
• Chapter 12 - Interrogating the Data
• Chapter 13 – Table Create and Data Types
• Chapter 14 – View Functions
• Chapter 15 – Data Manipulation Language (DML)
• Chapter 16 – Set Operators Functions
• Chapter 17 – Stored Procedures
• Chapter 18 – Statistical Aggregate Functions
• Chapter 19 – Nexus