Introducing SQL Server

Introducing SQL Server

by Mike McQuillan
Released October 2015
Publisher(s): Apress
ISBN: 9781484214190

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

Introducing SQL Server is a fast and easy introduction to SQL Server and the world of relational databases. You’ll learn how databases work and how to use the T-SQL language by practicing on one of the most widely-used and powerful database engines in the corporate world: Microsoft SQL Server.

Do you quake at the sight of a SELECT statement? Start to shiver when people start talking about tables and rows? Fear not, Introducing SQL Server is here to rescue you. The book focuses on the knowledge and skills needed to begin your journey toward becoming a solid and competent SQL Server professional and database programmer. You’ll learn the core concepts of SQL Server, from installing the software to executing and profiling queries.

Introducing SQL Server is aimed at SQL Server newcomers as well as at those wanting to improve their database skills. You’ll put a comprehensive database together as you work through the book. You will create tables and learn to use constraints; create reusable functions and stored procedures; and even learn how indexes work and what they bring in terms of increased performance. Introducing SQL Server shows you that databases don’t need to be difficult.

  • Teaches you how to build a SQL Server database from scratch
  • Takes a tutorial-based approach, with each chapter building on the last
  • Covers what you need to know for common SQL Server development tasks
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Author
    8. About the Technical Reviewer
    9. Acknowledgments
    10. Chapter 1 : What Is SQL Server?
      1. A Quick History of SQL Server
      2. SQL Server Services
        1. Business Intelligence Services
      3. Editions of SQL Server
      4. SQL Server Tools and Utilities
      5. Time to Kick Off
    11. Chapter 2 : Obtaining and Installing SQL Server
      1. Downloading SQL Server Express
      2. Beginning Installation
      3. Installation Wizard Steps
      4. Updates and Features
        1. Instance Features
        2. Shared Features
      5. Database Instances
        1. Default Instances
        2. Named Instances
      6. The Instance Configuration Page
      7. Service Accounts
      8. Collation
      9. Database Engine Configuration
        1. Server Configuration
        2. Data Directories
        3. User Instances
        4. FILESTREAM
      10. Reporting Services Configuration
      11. Installing
      12. A Very Quick SSMS Overview
      13. Summary
    12. Chapter 3 : Database Basics
      1. What Is a Database?
      2. Types of Databases
        1. System Databases
        2. User Databases
      3. Creating a Database Using SSMS
      4. Dropping a Database Using SSMS
      5. Creating a Database Using T-SQL
      6. Dropping A Database Using T-SQL
      7. Advanced Database Scripting
      8. Creating Scripts Automatically
      9. Database Properties
      10. Summary
    13. Chapter 4 : Tables
      1. Table Basics
      2. A Starting Point
      3. Naming Tables
      4. Types Of Table
      5. Columns and Table Data Types
      6. Primary Keys
      7. Clustered and Nonclustered Keys
      8. Foreign Keys and Relationships
      9. IDENTITY Columns
      10. Summary
    14. Chapter 5 : Putting Good Tables Together
      1. Database Normalization
        1. First Normal Form
        2. Multiple-Column Repeating Groups
        3. Second Normal Form
        4. Third Normal Form
      2. Further Analysis
      3. Looking Things Over
      4. A Second Enhancement
      5. Summary
    15. Chapter 6 : Automating Deployment with SQLCMD
      1. What Is SQLCMD?
      2. Why We Need a SQLCMD Script
      3. Rollback Scripts
      4. Creating the SQLCMD Rollback Script
      5. Creating the SQLCMD Apply Script
      6. Summary
    16. Chapter 7 : NULLs and Table Constraints
      1. NULL Constraints
      2. Constraints
        1. Default Constraints
        2. Creating a Default Constraint
        3. Table Constraints Part 1—Check Constraints
        4. Creating a Table Constraint
        5. Creating a Column Constraint
        6. Table Constraints Part 2—Unique Constraints
        7. Some Constraints Are Really Indexes
      3. Summary
    17. Chapter 8 : DML (or Inserts, Updates, and Deletes)
      1. Reference Data vs. Real Data
      2. Inserting Data
      3. More Inserts
      4. The UPDATE Statement
      5. Deleting Records
      6. Summary
    18. Chapter 9 : Bulk Inserting Data
      1. The BULK INSERT Statement
        1. Preparing the Data File
        2. Importing the Data File
      2. More Bulk Insert Escapades
      3. Truncating Tables
      4. Summary
    19. Chapter 10 : Creating Data Import Scripts
      1. Purpose of the Import Script
      2. Starting the Script with Variables
      3. Reference Data Lookups
      4. Inserting the Contact Record
      5. Adding Subrecords
      6. Displaying the Outcome
      7. The Rollback Script
      8. Summary
    20. Chapter 11 : The SELECT Statement
      1. What Is the SELECT Statement For?
      2. The Simple SELECT Statement
      3. The WHERE Clause
        1. The AND Operator
        2. The OR Operator
        3. The IN Operator
        4. Mixing Operators
      4. Ordering Data
      5. Grouping Data
      6. Column Aliases
      7. Limiting Rows with TOP
      8. Merging Columns
      9. Summary
    21. Chapter 12 : Joining Tables
      1. INNER JOIN
      2. LEFT OUTER JOIN
      3. RIGHT OUTER JOIN
      4. FULL OUTER JOIN
      5. CROSS JOIN
      6. Using JOINs In DML Statements
      7. Summary
    22. Chapter 13 : Views
      1. What Are Views For?
      2. Creating Views
      3. Adding Columns to Views
      4. Checking If a View Already Exists
      5. Broken Views
        1. SCHEMABINDING
      6. Using Views in Joins
      7. Using Views in Other Views
      8. Indexed Views
      9. Dropping the Views
      10. Summary
    23. Chapter 14 : Indexes
      1. What Is an Index?
      2. Why Are Indexes Useful?
      3. What Do Indexes Affect?
      4. Identifying Which Columns to Index
      5. How Indexes Work
        1. Table Scans
        2. B-Trees
        3. Non-Clustered Indexes And B-Trees
      6. Basics Of The CREATE INDEX Statement
      7. Clustered Indexes
        1. Creating a Clustered Index
        2. Modifying an Index Using SSMS
        3. Checking If an Index Exists with T-SQL
      8. Non-Clustered Indexes
        1. Execution Plan Percentages
        2. Execution Plans: A Quick Summary
      9. Indexed Columns vs. Included Columns
      10. Filtered Indexes
      11. Unique Indexes
      12. Other Types of Index
      13. Maintaining Indexes
        1. Identifying Index Fragmentation
        2. Altering Indexes
        3. Disabling Indexes
        4. Rebuilding Indexes
        5. Reorganizing Indexes
        6. Altering Indexes Using SSMS
      14. Dropping Indexes
      15. Statistics
      16. Creating Indexed Views
      17. Are Indexes Ever a Bad Idea?
      18. Summary
    24. Chapter 15 : Transactions
      1. What Is a Transaction?
      2. ACID
      3. A Transaction Example
      4. T-SQL Transaction Statements
        1. BEGIN TRANSACTION
        2. COMMIT TRANSACTION
        3. ROLLBACK TRANSACTION
      5. When Should I Use Transactions?
      6. What If I Don’t Use Transactions?
      7. Creating a Transaction
        1. Locking a Table with a Bad Transaction
        2. @@TRANCOUNT
        3. sp_who
        4. Rolling Back the Bad Transaction
      8. Committing Transactions
      9. Rolling Back Transactions
      10. General Transaction Rules
        1. Keep Transactions Short
        2. Limit Transactions to DML Statements
        3. Don’t Be Afraid to Split Transactions Up
      11. Summary
    25. Chapter 16 : Functions
      1. Function Types
        1. Deterministic vs. Non-Deterministic Functions
        2. User-Defined Functions vs. System Functions
      2. Why Are Functions Useful?
      3. What Can’t I Do with A Function?
      4. A First Function
        1. The CREATE FUNCTION Statement
        2. A Scalar Function: ContactName
        3. Testing Our Function
        4. Side Effects
        5. Further Testing
      5. Dropping Functions
      6. Saving the Function
      7. Using the Function in Queries
      8. Summary
    26. Chapter 17 : Table-Valued Functions
      1. Why TVFs Are Cool
      2. Sometimes, TVFs Are Not So Cool
      3. Building a TVF
        1. Adding the Script to SQLCMD
        2. Creating the Rollback Script
      4. Using TVFs in Queries
        1. Calling a TVF
        2. Joining to a TVF
      5. The APPLY Operator
        1. CROSS APPLY
        2. OUTER APPLY
      6. Performance Issues
      7. Summary
    27. Chapter 18 : Stored Procedures—Part 1
      1. What Are Stored Procedures, and Why Should I Use Them?
      2. CREATE PROCEDURE and ALTER PROCEDURE
      3. DROP PROCEDURE
      4. SSMS and Stored Procedures
      5. A First Stored Procedure
      6. Executing a Stored Procedure
      7. SET NOCOUNT ON and OFF
      8. Schema Names
      9. Returning Data from Stored Procedures
      10. Parameters
        1. Input Parameters
        2. Default Parameter Values
        3. Output Parameters
        4. READONLY Parameters
      11. Basic Debugging with PRINT
      12. Rollback
      13. Summary
    28. Chapter 19 : Stored Procedures—Part 2
      1. Enhancing the InsertContact Stored Procedure
      2. User-Defined Types
        1. Why Use Them?
        2. Creating a UDT
        3. Rolling Back the UDT
        4. Using the UDT
        5. Adding a Custom Type to a Stored Procedure
        6. Executing the Stored Procedure
      3. Set-Based Logic
      4. Summary
    29. Chapter 20 : Bits and Pieces
      1. Security
        1. GRANT, DENY, and REVOKE
        2. Built-in Security Roles
        3. Fixed Server Roles
        4. Fixed Database Roles
      2. Schemas
      3. Triggers
      4. Profiler and Extended Events
        1. SQL Server Profiler
        2. Starting Profiler
        3. Extended Events
      5. Summary
    30. Appendix A: SQL Data Types
    31. Appendix B: Glossary
    32. Appendix C: Common SQL Server System Objects
      1. Global Variables
        1. @@IDENTITY
        2. @@ROWCOUNT
        3. @@TRANCOUNT
      2. System Functions
        1. AVG()
        2. COALESCE(value1, value2, valueN)
        3. COUNT(criteria)
        4. DATABASEPROPERTYEX(database name, property name)
        5. DATEADD(datepart, number to add/subtract, date)
        6. DAY(date)
        7. DB_ID()
        8. DB_NAME(id)
        9. GETDATE()
        10. GETUTCDATE()
        11. LEFT(string, number of characters)
        12. LEN(string)
        13. LOWER(string)
        14. LTRIM(string)
        15. MAX()
        16. MIN()
        17. MONTH(date)
        18. OBJECT_NAME(id)
        19. RIGHT(string, number of characters)
        20. RTRIM(string)
        21. SCOPE_IDENTITY()
        22. UPPER(string)
        23. YEAR(date)
      3. System Stored Procedures
        1. sp_rename (@objname, @newname, @objtype)
        2. sp_help (@objname)
    33. Appendix D: Exercises
    34. Index

    Product information

    • Title: Introducing SQL Server
    • Author(s): Mike McQuillan
    • Release date: October 2015
    • Publisher(s): Apress
    • ISBN: 9781484214190