Writing T-SQL Queries in SQL Server 2008

מק"ט: #7048 | משך קורס: 32 שעות אק'

In this course, you will learn how to build basic queries using Transact-SQL, the language of SQL Server. Then, you will learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You will learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries.

הקורס פעיל לקבוצות מטעם ארגונים בלבד, ניתן לשלוח פנייה רק אם מדובר בקבוצה
*שדות חובה
PDF version

מטרות

  • Build basic queries using Transact-SQL
  • Build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL
     

קהל יעד

This course is intended for SQL Server database administrators, implementers, system engineers, testing professionals and developers who are responsible for writing queries

תנאי קדם

To get the most out of the Writing T-SQL Queries in SQL Server 2008 course, you should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer's perspective.

נושאים

Tour of SQL 08

  • Editions
  • Server Components
  • Management Tools
  • Documentation
  • Feature Pack
  • SSMS

\

Server Activity

  • Tables, Queries, Views
  • SSIS
  • BIDS
  • Reporting Services
  • SSAS

 

Relational Design

  • Design Principles
  • Key Terms
  • Relational Theory
  • Surrogate Keys
  • Data Normalization
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • Computed Columns
  • Key Dependence
  • Postal Lookup Tables
  • Beyond Normal Forms
  • Referential Integrity
  • Relationships
  • One-to-One
  • Enforcing Integrity
  • Cascading
  • Cascading Updates
  • Cascading Deletes
  • Cascading Actions
  • Beyond Normalization
  • Business Rules

 

Implementation

  • Database Storage
  • Storage
  • Data Integrity
  • Recovery Models
  • Rules for Identifiers

 

Data Types

  • Character-based
  • Numeric Data
  • Date and Time
  • Binary Types
  • Identifier Types
  • sql_variant
  • Variable-only
  • XML Type
  • System SQLCLR Types
  • Creating Constraints
  • Triggers
  • Creating Indexes

 

Management Studio

  • Configure Options
  • Customize Toolbars
  • Menu Items
  • Query Designer
  • Projects and Solutions
  • Object Explorer
  • Filter Nodes
  • View Dependencies
  • Object Details
  • Designers

 

Query Editor

  • Editing Options
  • Script Table as
  • Stored Procedure
  • Debugging
  • Template Explorer
  • Execution Plans
  • Results to a File
  • SQLCMD Mode
  • New Project
  • Working Offline
  • Books Online

 

Transact SQL

  • T-SQL Extensions
  • Batches and Scripts
  • Data Type Precedence
  • Built-In Functions
  • Working with Nulls

 

Handling Functions

  • Using RAND
  • ROUND
  • REPLACE
  • STUFF
  • LEN, LEFT, RIGHT
  • SUBSTRING
  • CHARINDEX
  • PATINDEX
  • SPACE
  • CHAR and ASCII
  • LOWER and UPPER
  • LTRIM and RTRIM
  • GETDATE
  • MONTH, DATE, YEAR
  • DATEPART
  • DATENAME
  • DATE Math
  • Global Functions

 

Controlling Flow

  • BEGIN...END
  • GOTO
  • RETURN
  • CASE
  • WHILE
  • WAITFOR
  • Ranking Results

 

Transactions

  • Passing the ACID Test
  • Transaction Types
  • Transaction Details
  • Isolation Levels
  • Locking
  • Viewing Lock Info
  • Avoiding Blocks
  • Deadlocks
  • Preventing
  • Applications
  • Designing
  • Efficient Transactions
  • Compile\Runtime Errors

 

Explicit Transactions

  • Syntax
  • Error Handling
  • @@ERROR
  • Stored Procedures

 

Raise Error Try/Catch

  • User-Defined Errors
  • TRY/CATCH Overview
  • Error Chaining
  • Errors Not Handled
  • XACT_STATE

 

Data Selection Queries

  • Transact-SQL
  • Schemas and Naming
  • Three-Valued Logic

 

Sorting

  • Group By Clause
  • Aggregate Functions
  • Joining Tables

 

HierarchyID

  • Indexing a Hierarchy
  • Depth-first
  • Breadth-first
  • Manipulating
  • Sparse Columns
  • Restrictions
  • Column Sets
  • Restrictions
  • Recommendations
  • Column Sets
  • Filtered Indexes
  • Using Filtered Indexes

 

FileStream Spatial Data

  • FILESTREAM Storage
  • Data Access Methods
  • Gotchas & Limitations
  • Spatial Data
  • Geometry vs. Geography
  • Geometric Plane
  • Mother Ship
  • Projection Distortion
  • Data Standards
  • Types of Spatial Data
  • Spatial Objects
  • Geospatial Data

 

Complex Queries

  • NULL Values
  • SQLTypes and CLR Types
  • ANSI_NULLS Option
  • NULLS and SQLBoolean
  • Assigning NULL Values
  • CLR Integration
  • Direct Assignment
  • NULLIF
  • COALESCE

 

Correlated Subquery

  • Correlated
  • WHERE Clause
  • ANY, SOME, ALL
  • HAVING Clause
  • Updates
  • Joins and Temp Tables

 

Common Table Expressions

  • When to Use CTEs
  • CTE Syntax
  • Recursive CTEs

 

Modifying Data

  • Inserting Data
  • Temporary Tables
  • Uses for Temp Tables

 

Updating Data

  • Transaction Isolation
  • Isolation Levels
  • Blocking and Deadlocks
  • Deadlocks
  • Snapshot Isolation
  • Caveats

 

XML Data Type

  • Declaring XML Objects
  • Loading Data
  • SELECT...FOR XML
  • OPENROWSET
  • Indexing Columns

 

Ranking

  • Ranking Grouped Data
  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE
  • RANK
  • DENSE_RANK
  • NTILE

 

Correlated Subqueries

  • Writing Subqueries
  • Subquery Basics
  • Primary Indexes
  • Secondary Indexes

 

XML Schema Collections

  • Typed and Untyped XML
  • Schema Basics
  • Lax Validation
  • Full DateTime Support
  • Registering Schemas
  • Viewing Stored Schemas
  • Collections
  • Namespaces
  • Components
  • Namespace

 

Querying XML

  • Using XQuery
  • query()
  • value()
  • exist()
  • modify()
  • nodes()
  • Using XQuery
  • FLWOR
  • FOR XML
  • OPENXML

 

Best Practices

  • Relational vs. XML
  • Storing XML
  • Reasons to Index
  • Querying

 

Views

  • Advantages of Views
  • Views and Security
  • Creating Views
  • View Rules
  • Views and ORDER BY
  • Syntax and Options
  • CHECK OPTION
  • Other Options
  • Tools for Creating Views
  • From Templates
  • Edit Views
  • View Examples
  • Nesting Views
  • CTE
  • Encrypting

 

Updating View Data

  • Updating Rules
  • Updating Behavior
  • Using Computed Columns
  • Indexed Views
  • How Indexed View Work
  • Performance Benefits
  • Data Modifications
  • Requirements
  • Partitioned Views

 

SQL Server Partitions

  • Why Partition?
  • Partitioning
  • 6.5 and Earlier
  • SQL Server 7.0
  • SQL Server 2000
  • SQL Server 2005/2008
  • SQL Server 2008
  • Range Partitions
  • Partition Key
  • Multiple Filegroups
  • Index Partitioning

 

Creating Partitioned Tables

  • LEFT and RIGHT
  • Datetime Values
  • RANGE RIGHT
  • Partition Scheme
  • Partition Scheme
  • Tables and Indexes
  • Partitioned Index

 

Querying Partitions

  • $PARTITION Function
  • Catalog Views
  • sys.partition_functions
  • sys.partition_range_values
  • sys.partition_schemes
  • sys.data_spaces

 

Managing Partitions

  • Tables or Indexes
  • Partition Function
  • Splitting Partitions
  • Merging Partitions
  • Switching Partitions
  • Partition Scheme
  • Backing Up Partitions
  • Performance
  • Join Queries
  • Multiple Disk Drives
  • Lock Escalation

 

User Defined Functions

  • Scalar Functions
  • WITH Function Options
  • With Parameters
  • Inline Functions
  • Inline Syntax
  • Inline Parameters
  • Updating Data

 

Table Valued Functions

  • Multi-Statement Syntax
  • Limitations
  • TABLE Data Type
  • Using Functions, Views...
  • Table-Valued Functions
  • Choosing Between

 

Stored Procedures

  • Performance Benefits
  • Deferred Name Resolution
  • Execution Plans
  • Plan Recompilation
  • Using Schema Qualifiers
  • Reasons to Use
  • Stored Procedure Syntax
  • Creating Procedures
  • SET NOCOUNT ON
  • Parameters
  • Optional Parameters
  • Output Parameters
  • Return
  • Variables
  • Debugging

 

Testing and Triggers

  • PRINT Statements
  • Output Parameters
  • Creating Triggers
  • Types of Triggers
  • Uses for Triggers
  • Trouble with Triggers
  • How Triggers Work
  • INSTEAD OF Trigger
  • DDL Trigger

 

Full-Text Search

  • Full-Text Queries
  • CONTAINS Predicate
  • Generation Terms
  • Prefix Terms
  • Proximity Terms
  • FREETEXT
  • CONTAINSTABLE
  • Ranking Relevance

 

Advanced TSQL

  • Using APPLY
  • APPLY With TVF
  • FOR XML PATH
  • MERGE
  • Five Clauses in MERGE
  • WHEN Clause
  • Recursive Queries
  • Grouping Sets
  • Grouping Sets
  • ROLLUP
  • CUBE
  • GROUPING_ID
  • Pivot Queries

 

Еxecuting Dynamic SQL

  • sp_executesql
  • QUOTENAME
  • Output Parameters
  • Signing Stored Procedures

 

Complex Data And Structures

  • Issues with Data Types
  • Multinational Data
  • Hierarchical Data

 

Efficient Queries

  • EXISTS Clause
  • Join vs. Subqueries
  • One-Pass Queries
  • One-Pass
  • Multiple Resultsets
  • Temp Tables
  • Table Variables
  • UDFs
  • CTEs
  • Worktables
  • User-Defined Table Types

 

Working With Complex Queries

  • Implicit Transactions
  • Keeping it Simple
  • UDF

 

Maintaining Query Files

  • Source Control
  • SourceSafe
  • Files Under Control
  • Keyword Expansion
  • Enabling
  • Version Info
  • File Versions
     
לפי יצרן: 
תגיות