SQL Server 2014 Development Essentials

Name: SQL Server 2014 Development Essentials
Brand: Packt Publishing
Availability: OnlineOnly

Basit A. Masood-Al-Farooq(Autor*in)

Packt Publishing

1. Auflage

Erschienen am 25. Juli 2014

214 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-78217-256-7 (ISBN)

ab 24,99 €

Als Download verfügbar

Merkliste: siehe Preise

Beschreibung

Alle Preise

Weitere Details

Weitere Ausgaben

Inhalt

Intro
SQL Server 2014 Development Essentials
Table of Contents
SQL Server 2014 Development Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and more
Why subscribe?
Free access for Packt account holders
Instant updates on new Packt books
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. Microsoft SQL Server Database Design Principles
Database design
The requirement collection and analysis phase
The conceptual design phase
The logical design phase
The physical design phase
The implementation and loading phase
The testing and evaluation phase
The database design life cycle recap
Table design
Tables
Entities
Attributes
Relationships
A one-to-one relationship
A one-to-many relationship
A many-to-many relationship
Data integrity
The basics of data normalization
The normal forms
The first normal form (1NF)
The second normal form (2NF)
The third normal form (3NF)
Denormalization
The SQL Server database architecture
Pages
Extents
The transaction log file architecture
The operation and workings of a transaction log
Filegroups
The importance of choosing the appropriate data type
SQL Server 2014 system data types
Alias data types
Creating and dropping alias data types with SSMS 2014
Creating and dropping alias data types using the Transact-SQL DDL statement
Creating an alias data type using CREATE TYPE
Dropping an alias data type using DROP TYPE
CLR user-defined types
Summary
2. Understanding DDL and DCL Statements in SQL Server
Understanding the DDL, DCL, and DML language elements
Data Definition Language (DDL) statements
Data Manipulation Language (DML) statements
Data Control Language (DCL) statements
Understanding the purpose of SQL Server 2014 system databases
SQL Server 2014 system databases
The master database
The model database
The msdb database
The tempdb database
The resource database
The distribution database
An overview of database recovery models
The simple recovery model
The bulk-logged recovery model
Full recovery
Creating and modifying databases
Create, modify, and drop databases with T-SQL DDL statements
Creating a database with T-SQL DDL statements
Example 1 - creating a database based on a model database
Example 2 - creating a database that explicitly specifies the database data and the transaction log file's filespecs properties
Example 3 - creating a database on multiple filegroups
Modifying a database with T-SQL DDL statements
Example - adding a secondary data file to an existing database
Dropping a database with T-SQL DDL statements
Create, modify, and drop databases with SSMS 2014
Creating a database with SSMS 2014
Modifying a database with SSMS 2014
Dropping a database with SSMS 2014
Creating and managing database schemas
Managing schemas using T-SQL DDL statements
Managing schemas using SSMS 2014
Creating and managing tables
Creating and modifying tables
Creating and modifying tables with T-SQL DDL statements
Creating a table with T-SQL DDL statements
Modifying a table with T-SQL DDL statements
Dropping a table with T-SQL DDL statements
Creating and modifying tables with SSMS 2014
Creating a table with SSMS 2014
Modifying a table with SSMS 2014
Deleting a table with SSMS 2014
Grant, deny, and revoke permissions to securables
Grant, deny, and revoke permissions to securables with T-SQL DCL statements
Granting permissions to securables with T-SQL DCL statements
Denying permissions to securables with T-SQL DCL statements
Revoking permissions to securables with T-SQL DCL statements
Managing permissions using SSMS 2014
Summary
3. Data Retrieval Using Transact-SQL Statements
Understanding Transact-SQL SELECT, FROM, and WHERE clauses
The SELECT statement
The FROM clause
The WHERE clause
Using T-SQL functions in the query
Aggregate functions
Configuration functions
Cursor functions
Date and time functions
Mathematical functions
Metadata functions
Rowset functions
Security functions
String functions
System statistical functions
Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs
The UNION operator
The EXCEPT operator
The INTERSECT operator
The JOIN operator
Using INNER JOIN
Using outer joins
Using LEFT OUTER JOIN
Using RIGHT OUTER JOIN
Using FULL OUTER JOIN
Using CROSS JOIN
Using self joins
Subqueries
Examples of subqueries
Common Table Expressions
Organizing and grouping data
The ORDER BY clause
The GROUP BY clause
The HAVING clause
The TOP clause
The DISTINCT clause
Pivoting and unpivoting data
Using the Transact-SQL analytic window functions
Ranking functions
PERCENT RANK
CUME_DIST
PERCENTILE_CONT and PERCENTILE_DISC
LEAD and LAG
FIRST_VALUE and LAST_VALUE
Summary
4. Data Modification with SQL Server Transact-SQL Statements
Inserting data into SQL Server database tables
The INSERT examples
Example 1 - insert a single row into a SQL Server database table
Example 2 - INSERT with the SELECT statement
Example 3 - INSERT with the EXEC statement
Example 4 - explicitly inserting data into the IDENTITY column
Updating data in SQL Server database tables
The UPDATE statement examples
Example 1 - updating a single row
Example 2 - updating multiple rows
Deleting data from SQL Server database tables
The DELETE statement examples
Example 1 - deleting a single row
Example 2 - deleting all rows
Using the MERGE statement
The MERGE statement examples
The TRUNCATE TABLE statement
The SELECT INTO statement
Summary
5. Understanding Advanced Database Programming Objects and Error Handling
Creating and using variables
Creating a local variable
Creating the cursor variable
Creating the table variable
Control-of-flow keywords
BEGIN.END keywords
The IF.ELSE expression
A CASE statement
WHILE, BREAK, and CONTINUE statements
RETURN, GOTO, and WAITFOR statements
Creating and using views
Creating views with Transact-SQL and SSMS 2014
Creating, altering, and dropping views with Transact-SQL DDL statements
The CREATE VIEW statement
The ALTER VIEW statement
The DROP VIEW statement
Creating, altering, and dropping views with SSMS 2014
Creating views with SSMS 2014
Altering and dropping views with SSMS 2014
Indexed views
Indexed view example
Creating and using stored procedures
Creating a stored procedure
Modifying a stored procedure
Dropping a stored procedure
Viewing stored procedures
Executing stored procedures
Creating and using user-defined functions
Creating user-defined functions
Creating a user-defined scalar function
Using a user-defined scalar function
Creating a user-defined table-valued function
Inline table-valued function example
Multistatement table-valued function example
Modifying user-defined functions
Using a user-defined table-valued function
Dropping user-defined functions
Viewing user-defined functions
Creating and using triggers
Nested triggers
Recursive triggers
DML triggers
Inserted and deleted logical tables
Creating DML triggers
Modifying a DML trigger
Dropping a DML trigger
Data Definition Language (DDL) triggers
The EVENTDATA function
Creating a DDL trigger
Modifying a DDL trigger
Dropping a DDL trigger
Disabling and enabling triggers
Viewing triggers
Handling Transact-SQL errors
An example of TRY...CATCH
An example of TRY...CATCH with THROW
An example of TRY...CATCH with RAISERROR
Summary
6. Performance Basics
Components of SQL Server Database Engine
The SQL Server Relational Engine architecture
Parsing and binding
Query optimization
Query execution and plan caching
Query plan aging
The improved design in SQL Server 2014 for the cardinality estimation
Optimizing SQL Server for ad hoc workloads
Manually clearing the plan cache
The SQL Server 2014 in-memory OLTP engine
The limitations of memory-optimized tables
Indexes
The cost associated with indexes
How SQL Server uses indexes
Access without an index
Access with an index
The structure of indexes
Index types
Clustered indexes
When should you have a clustered index on a table?
Nonclustered indexes
Single-column indexes
Composite indexes
Covering indexes
Unique indexes
Spatial indexes
Partitioned indexes
Filtered indexes
Full-text indexes
XML indexes
Memory-optimized indexes
Columnstore indexes
The architecture of columnstore indexes
Creating and managing columnstore indexes
Guidelines for designing and optimizing indexes
Avoid overindexing tables
Create a clustered index before creating nonclustered indexes when using clustered indexes
Index columns used in foreign keys
Index columns frequently used in joins
Use composite indexes and covering indexes to give the query optimizer greater flexibility
Limit key columns to columns with a high level of selectability
Pad indexes and specify the fill factor to reduce page splits
Rebuild indexes based on the fragmentation level
Query optimization statistics
Database-wide statistics options in SQL Server to automatically create and update statistics
Manually create and update statistics
Determine the date when the statistics were last updated
Using the DBCC SHOW_STATISTICS command
Using the sys.stats catalog view with the STATS_DATE() function
The fundamentals of transactions
Transaction modes
Implementing transactions
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION
SAVE TRANSACTION
An overview of locking
Basic locks
Optimistic and pessimistic locking
Transaction isolation
SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance
Activity Monitor
The SQLServer:Locks performance object
Dynamic Management Views
SQL Server Profiler
The sp_who and sp_who2 system stored procedures
SQL Server Extended Events
Summary
Index

Systemvoraussetzungen

Dateiformat: ePUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet – also für „fließenden” Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Dateiformat: PDF
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Als PDF speichern Als Link merken