SQL Server Programming

Registration closed

Description

Programming Microsoft SQL Server 2005 or its newer version is not only about writing T-SQL scripts that create views or stored procedures. SQL server does not only allow to store and manage non-relational types of data, it is also a universal programming environment. Apart from being integrated with CLR environment, SQL Server has been equipped with its own data managing mechanisms, such as Service Broker which helps to create service oriented applications (SOA), or SQL Compact and SQL Express Local Database Runtime editions which allow to build scattered, sporadically connected systems. The aim of the course is to train database programmers to be able to use fully the capabilities of SQL Server, starting with suitable programming tools, writing and optimizing T-SQL code, programming the access to non-relational data and writing SQLCLR code, and finishing with organizing and managing projects. At the heart of this training there are verified standards and best programming practices, as well as methods of their application which we have successfully put to practical use in database projects ourselves.  In effect, we have put together a training which will prevent you from making common mistakes which often result in delayed projects and suboptimal end effects.

Options

  • Course level: 300
  • Start date: as required
  • Duration time: 5 days

Price: 5000 zł

  • Open training
  • On-site (costumer's premises)
  • Customer’s computer

Class

The course has been designed for database programmers and anybody who wants to become familiar with the structure and the functioning of SQL Server. The participants will acquire the practical skills necessary to create database applications based on different SQL Server editions. The course is available in 3 versions, devoted to SQL 2005, 2008, 2008R2 and 2012 respectively.

Plan

The basic training is comprised of 16 modules. Just like all the other courses on our offer, it has been designed by our experts, therefore we can freely customize it to suit the individual needs of the participants.  We would like to encourage you not only to choose modules which you find relevant for your needs, but also to feel free to contact us with your suggestions and questions you would like to hear answered during the course.
Our trainings are unique also because we limit the time set aside for laboratory type classes customarily conducted after the completion of each module, to the benefit of exercises done with the assistance of the trainer and demonstrations. We believe in this manner we can equip you with substantially more practical information and tips, and focus on dealing with the issues directly within your scope of interest. However, each day of the training is finished with a laboratory class of approximately 1hr, so that you can do self-practice and do exercises of your choice.

Modules

Duration: Level:
Module 1

Development Tools
  • SQL Server Management Studio and SQL Server Data Tools
  • Visual Studio 2010 Database Projects (SQLCLR)
  • Online Development
  • Database Projects
  • Source Code Versioning
  • Debugging Stored Procedures And Functions
  • Code Versioning and Synchronizing Changes
  • Refactoring T-SQL Code
  • Unit Tests
  • Deploying and Publishing Database Projects
  • Data-Tier Applications

90 minutes 300
Module 2

T-SQL Crash Course
  • Relative Database Model
  • SQL Language Standards
  • SELECT and NULL Commands and Three-Valued Logic
  • Joining Tables and Query Results
  • Grouping Rows
  • Subqueries
  • INSERT, UPDATE, DELETE and MERGE Commands
  • Transactions and Concurrence
  • CREATE, ALTER and DROP Commands

150 minutes 200
Module 3

Designing and Creating Tables
  • Logical Database Model (Entity- Relationship)
  • Physical Database Model (Data Types, Tables and Indexes)
  • Computed Columns
  • Partitioning
  • Constraints
  • Storing and Retrieving Rows

120 minutes 300
Module 4

Indexing Strategies
  • Heaps and B-Trees (HOBTS)
  • Index Internals
  • Clustered Index best Practices
  • Non-Clustered Index Design for Optimal Performance
  • Filtered Indexes

270 minutes 300
Module 5

Non-Relational Data
  • Spatial Data (Geometry and Geography Data Types)
  • Spatial Indexes
  • Hierarchical Data
  • XML (XML Data Type, XML Schema, Xpath and Xquery, FOR XML Clause, XML Data Type Methods)
  • XML Indexes
  • Effective Use of XML Indexes

210 minutes 400
Module 6

Large Binary Objects and Full-Text Search Service
  • FILSTETREAM Attribute
  • Integrated Full Text Search Service - Overview
  • Full Text Indexes and their Components
  • Working with Multilanguage Documents
  • FileTable
  • Property-Scoped Searching
  • Statistical Semantic Search

120 minutes 400
Module 7

CLR Integrations
  • SQL Server as a .Net Runtime Host
  • SQLCLR Reliability, Scalability, and Security
  • Assemblies and User Defined Objects (Functions, Procedures, DML and DDL Triggers, User-Defined Types and Aggregates)
  • SQLCLR Best Practices

120 minutes 300
Module 8

Service-Oriented Architecture and HTTP Programming
  • Service Broker and Messaging (Messages, Contracts, Queues, Services and Routes)
  • Service Broker Programmability (Conversations, Sending & Receiving Messages, Security)
  • Service Broker Troubleshooting
  • Web Services and REST Architecture
  • ADO.NET Data Services

60 minutes 400
Module 9

Occasionally Connected Systems
  • SQL Server Compact Edition (Tools, Features and Limitations, Programmability, Distribution)
  • Server-Directed Synchronization (Remote Data Access , Merge Replication, Sync Services and SQL Server 2008 Change Tracking)
  • SQL Express Local Database Runtime (Installation and Configuration, Creating and Connecting Databases, Managing Local Databases)

60 minutes 300
Module 10

Writing Effective Queries with T-SQL Extensions
  • Data Types and Conversions
  • System Functions
  • Row Constructors
  • Table-Valued Parameters
  • Common Table Expressions
  • Ad-Hoc Query Paging
  • GROUPING SETS Clause
  • OVER Clause (Ranging, Grouping and Analytic Functions, Grouping, Ordering and Selecting Rows)
  • ”Top N Per Group” Query Pattern
  • Sequence Objects
  • MERGE Statement

150 minutes 400
Module 11

Programmability Objects
  • Views
  • Stored Procedures
  • DML and DDL Triggers
  • Scalar and Tabular Functions
  • Metadata Discovery
  • Structured Exceptions Handling
  • Common Mistakes
  • Execution Context
  • Signing Code Modules

90 minutes 300
Module 12

SQL Server Cryptography
  • Symmetric and Asymmetric Algorithms
  • Keys Management by SQL Server
  • Generating, Archiving and Restoring Cryptographic Keys
  • Signing Data
  • Encrypting Data

60 minutes 300
Module 13

Query Execution
  • SQL Server Query Processing
  • Reading Query Plans
  • Analyzing Query Plans (Scans and Seeks, Seekable Predicates, Joins and Unions, Aggregations, Subquery Plans, Parallelism)

90 minutes 400
Module 14

The Query Optimizer
  • How Does the Query Optimizer Work?
  • Statistics
  • Hints
  • Plan Guides

90 minutes 400
Module 15

Plan Caching and Recompilation
  • Plan Caching and Reuse
  • What Is in the Procedure Cache?
  • Managing the Procedure Cache
  • Controlling Plan Reuse
  • Recompilation Triggers

60 minutes 400
Module 16

Transactions and Concurrency Control: Locking, Blocking and Row Level Versioning
  • SQL Server Locking Architecture
  • Isolation Levels Internals
  • Nesting Transactions
  • Distributed Transactions
  • Managing Locks
  • Locks Escalation and Partitioning
  • Row-Level Versioning Managing Version Store

60 minutes 300

Form

Ask Question

Register