Book Image

SQL Server 2014 Development Essentials

By : Basit A. Masood-Al-Farooq
Book Image

SQL Server 2014 Development Essentials

By: Basit A. Masood-Al-Farooq

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2014 Development Essentials
Basit A. Masood-Al-Farooq
Jul, 2014 | 214 pages
No titles found
Table of Contents (14 chapters)
SQL Server 2014 Development Essentials
SQL Server 2014 Development Essentials
Credits
Credits
About the Author
About the Author
Acknowledgments
Acknowledgments
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Microsoft SQL Server Database Design Principles
Microsoft SQL Server Database Design Principles
Database design
Table design
Relationships
Data integrity
The basics of data normalization
The SQL Server database architecture
The importance of choosing the appropriate data type
Summary
2
Understanding DDL and DCL Statements in SQL Server
Understanding DDL and DCL Statements in SQL Server
Understanding the DDL, DCL, and DML language elements
Understanding the purpose of SQL Server 2014 system databases
An overview of database recovery models
Creating and modifying databases
Creating and managing database schemas
Creating and managing tables
Grant, deny, and revoke permissions to securables
Summary
3
Data Retrieval Using Transact-SQL Statements
Data Retrieval Using Transact-SQL Statements
Understanding Transact-SQL SELECT, FROM, and WHERE clauses
Using T-SQL functions in the query
Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs
Subqueries
Common Table Expressions
Organizing and grouping data
Using the Transact-SQL analytic window functions
Summary
4
Data Modification with SQL Server Transact-SQL Statements
Data Modification with SQL Server Transact-SQL Statements
Inserting data into SQL Server database tables
Updating data in SQL Server database tables
Deleting data from SQL Server database tables
Using the MERGE statement
The TRUNCATE TABLE statement
The SELECT INTO statement
Summary
5
Understanding Advanced Database Programming Objects and Error Handling
Understanding Advanced Database Programming Objects and Error Handling
Creating and using variables
Control-of-flow keywords
Creating and using views
Creating and using stored procedures
Creating and using user-defined functions
Creating and using triggers
Handling Transact-SQL errors
Summary
6
Performance Basics
Performance Basics
Components of SQL Server Database Engine
The SQL Server 2014 in-memory OLTP engine
Indexes
Query optimization statistics
The fundamentals of transactions
SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance
Summary
Index
Index

Creating and using views

A view is a virtual table whose result set is derived from a query. In reality, a view is simply a SELECT statement that is saved with the name in the database. Views are used just like regular tables without incurring additional cost, unless you are indexing the view. We typically create views based on one or more tables, views, CTEs, table-valued functions, or a combination of them all. We can reference views in Transact-SQL statements in the same way tables are referenced. We can also perform DML operations on views. The typical uses of views include:

  • A denormalized presentation of normalized data

  • Limiting access to specific columns of the underlying tables

  • Creating a reusable set of data

  • Restricting users' access to sensitive data

    Tip

    You should avoid using SELECT * in views, because when you do, the columns list is resolved each time you query the view. Moreover, the result set of the view query changes when the underlying table schema changes. A good practice is...

Previous Section
End of Section 4
Next Section