Beginning Microsoft® SQL Server® 2012 Programming

Beginning Microsoft® SQL Server® 2012 Programming

by
Released April 2012
Publisher(s): Wrox
ISBN: 9781118102282

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Get up to speed on the extensive changes to the newest release of Microsoft SQL Server

The 2012 release of Microsoft SQL Server changes how you develop applications for SQL Server. With this comprehensive resource, SQL Server authority Robert Vieira presents the fundamentals of database design and SQL concepts, and then shows you how to apply these concepts using the updated SQL Server. Publishing time and date with the 2012 release, Beginning Microsoft SQL Server 2012 Programming begins with a quick overview of database design basics and the SQL query language and then quickly proceeds to show you how to implement the fundamental concepts of Microsoft SQL Server 2012.

You'll explore the key additions and changes to this newest version, including conditional action constructs, enhanced controls for results paging, application integration with SharePoint and Excel, and development of BI applications.

  • Covers new features such as SQL Azure for cloud computing, client-connectivity enhancements, security and compliance, data replication, and data warehouse performance improvements

  • Addresses essential topics including managing keys, writing scripts, and working with store procedures

  • Shares helpful techniques for creating and changing tables, programming with XML, and using SQL Server Reporting and Integration Services

Beginning Microsoft SQL Server 2012 Programming demystifies even the most difficult challenges you may face with the new version of Microsoft SQL Server.

Table of contents

  1. Cover
  2. Contents
  3. Chapter 1: RDBMS Basics: What Makes Up a SQL Server Database?
    1. An Overview of Database Objects
    2. SQL Server Data Types
    3. SQL Server Identifiers for Objects
    4. Summary
  4. Chapter 2: Learning the Tools of the Trade
    1. Getting Help with Books Online
    2. SQL Server Configuration Manager
    3. SQL Server Management Studio
    4. SQL Server Data Tools (formerly BIDS)
    5. SQL Server Integration Services (SSIS)
    6. SQL Server Reporting Services (SSRS)
    7. SQL Server Analysis Services (SSAS)
    8. Bulk Copy Program (BCP)
    9. SQL Server Profiler
    10. sqlcmd
    11. PowerShell
    12. Summary
  5. Chapter 3: The Foundation Statements of T-SQL
    1. Getting Started with a Basic SELECT Statement
    2. Adding Data with the INSERT Statement
    3. Changing What You’ve Got with the UPDATE Statement
    4. The DELETE Statement
    5. Summary
  6. Chapter 4: Joins
    1. Combining Table Data with JOINs
    2. Selecting Matching Rows with INNER JOIN
    3. Retrieving More Data with OUTER JOIN
    4. Seeing Both Sides with FULL JOINs
    5. Understanding CROSS JOINs
    6. Exploring Alternative Syntax for Joins
    7. Stacking Results with UNION
    8. Summary
  7. Chapter 5: Creating and Altering Tables
    1. Object Names in SQL Server
    2. The CREATE Statement
    3. The ALTER Statement
    4. The DROP Statement
    5. Using the GUI Tool
    6. Summary
  8. Chapter 6: Keys and Constraints
    1. Types of Constraints
    2. Constraint Naming
    3. Key Constraints
    4. CHECK Constraints
    5. DEFAULT Constraints
    6. Disabling Constraints
    7. Rules and Defaults — Cousins of Constraints
    8. Triggers for Data Integrity
    9. Choosing What to Use
    10. Summary
  9. Chapter 7: Adding More to Your Queries
    1. What Is a Subquery?
    2. Building Correlated Subqueries
    3. Derived Tables
    4. Using Common Table Expressions (CTEs)
    5. Using the EXISTS Operator
    6. Mixing Data Types: CAST and CONVERT
    7. Synchronizing Data with the MERGE Command
    8. Gathering Affected Rows with the OUTPUT Clause
    9. Through the Looking Glass: Windowing Functions
    10. One Chunk at a Time: Ad Hoc Query Paging
    11. Performance Considerations
    12. Summary
  10. Chapter 8: Being Normal: Normalization and Other Basic Design Issues
    1. Understanding Tables
    2. Keeping Your Data “Normal”
    3. Understanding Relationships
    4. Diagramming Databases
    5. Denormalization
    6. Beyond Normalization
    7. Drawing Up a Quick Example
    8. Summary
  11. Chapter 9: SQL Server Storage and Index Structures
    1. SQL Server Storage
    2. Understanding Indexes
    3. Creating, Altering, and Dropping Indexes
    4. Choosing Wisely: Deciding Which Index Goes Where and When
    5. Maintaining Your Indexes
    6. Summary
  12. Chapter 10: Views
    1. Creating Simple Views
    2. Editing Views with T-SQL
    3. Dropping Views
    4. Creating and Editing Views in the Management Studio
    5. Auditing: Displaying Existing Code
    6. Protecting Code: Encrypting Views
    7. About Schema Binding
    8. Making Your View Look Like a Table with VIEW_METADATA
    9. Indexed (Materialized) Views
    10. Indexing an Aggregate View
    11. Summary
  13. Chapter 11: Writing Scripts and Batches
    1. Understanding Script Basics
    2. Grouping Statements into Batches
    3. Running from the Command Prompt: sqlcmd
    4. Dynamic SQL: Using the EXEC Command
    5. Using Control-of-Flow Statements
    6. Summary
  14. Chapter 12: Stored Procedures
    1. Creating the Sproc: Basic Syntax
    2. Changing Stored Procedures with ALTER
    3. Dropping Sprocs
    4. Parameterizing Sprocs
    5. More on Dealing with Errors
    6. What a Sproc Offers
    7. Extended Stored Procedures (XPs)
    8. A Brief Look at Recursion
    9. Debugging
    10. Understanding .NET Assemblies
    11. When to Use Stored Procedures
    12. Summary
  15. Chapter 13: User-Defined Functions
    1. What a UDF Is
    2. UDFs Returning a Scalar Value
    3. UDFs That Return a Table
    4. Debugging User-Defined Functions
    5. Using .NET in a Database World
    6. Summary
  16. Chapter 14: Transactions and Locks
    1. Understanding Transactions
    2. How the SQL Server Log Works
    3. Understanding Locks and Concurrency
    4. Setting the Isolation Level
    5. Dealing with Deadlocks (aka “a 1205”)
    6. Summary
  17. Chapter 15: Triggers
    1. What Is a Trigger?
    2. Using Triggers for Data Integrity Rules
    3. Other Common Uses for Triggers
    4. Other Trigger Issues
    5. Instead of Triggers
    6. Performance Considerations
    7. Dropping Triggers
    8. Debugging Triggers
    9. Summary
  18. Chapter 16: A Brief XML Primer
    1. XML Basics
    2. What SQL Server Brings to the Party
    3. A Brief Word on XSLT
    4. Summary
  19. Chapter 17: Business Intelligence Fundamentals
    1. What Is Business Intelligence?
    2. Those Who Forget History: The Data Warehouse
    3. Dimensional Modeling: Why Be Normal?
    4. ETLs
    5. Making Your Data Actionable: BI Reporting Techniques
    6. Summary
  20. Chapter 18: BI Storage and Reporting Resources
    1. BI in T-SQL
    2. SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube
    3. Building Your First Cube
    4. Self-Service BI: User Tools
    5. Summary
  21. Chapter 19: Reporting for Duty, Sir! A Look at Reporting Services
    1. Reporting Services 101
    2. Understanding the SSRS Report Lifecycle
    3. Understanding the Reporting Services Architecture
    4. Building Simple Report Models
    5. Report Server Projects
    6. Summary
  22. Chapter 20: Getting Integrated with Integration Services
    1. Understanding the Problem
    2. Using the Import/Export Wizard to Generate Basic Packages
    3. Examining Package Basics
    4. Executing Packages
    5. A Final Word on Packages
    6. Summary
  23. Chapter 21: Playing Administrator
    1. Scheduling Jobs
    2. Logins and Users
    3. Backup and Recovery
    4. Index Maintenance
    5. Policy Based Management
    6. Automating Administration Tasks with PowerShell
    7. Summary
  24. Appendix: Answers to Exercises
  25. Bonus Appendices
    1. Bonus Appendix 1: System Functions
      1. System Statistical Functions
      2. Aggregate Functions
      3. Analytic Functions
      4. Configuration Functions
      5. Conversion Functions
      6. Cryptographic Functions
      7. Cursor Functions
      8. Date and Time Functions
      9. Hierarchy Functions
      10. Mathematical Functions
      11. Basic Metadata Functions
      12. Ranking Functions
      13. Rowset Functions
      14. Security Functions
      15. String Functions
      16. System Functions
      17. Text and Image Functions
    2. Bonus Appendix 2: Very Simple Connectivity Examples
      1. Some General Concepts
      2. Connectivity Examples
      3. A Brief Word on LINQ
  26. Introduction
  27. Advertisement

Product information

  • Title: Beginning Microsoft® SQL Server® 2012 Programming
  • Author(s):
  • Release date: April 2012
  • Publisher(s): Wrox
  • ISBN: 9781118102282