سرفصل و محتوای دوره آموزش برنامه نویسی به زبان T-SQL

طول دوره : 40 ساعت

پیشنیاز دوره : Query نویسی در محیط  SQL SERVER

مشاهده رزومه استاد

مشاهده این درس در نقشه راه

سرفصل دوره:

T-SQL Building Blocks

          Working with Variables

Dynamic SQL Execution

IF Statement

CASE Expressions

WHILE Loop

(Raising Errors(RAISERROR and THROW

(Error Handling (@@ERROR and TRY/CATCH

Transaction, Locking, Blocking and Isolation Levels

Transaction and ACID properties

Auto Commit Transactions

BEGIN TRAN and compound transactions

Error handling in Transactions

Lock Types

:Transaction Isolation Levels

Read Committed Isolation Level and READ_COMMITTED_SNAPSHOT database property-

(Read Uncommitted Isolation Level (Dirty Read-

Repeatable Read Isolation Level-

SNAPSHOT Isolation Level and Concurrency Law-

Serializable Isolation Level-

Using Semaphore (Application Lock) to manage Concurrency

Lock Timeout

DEADLOCKS and Using Profiler to diagnose Deadlocks

Nested Transactions

Transaction Save Points

Who has locked what and resolving blocking issues

(CURSOR (Record Based Data Processing

Set based vs Record based data processing

ISO syntax for DECLARE CURSOR

FORWARD ONLY - READ ONLY Cursor

Looping records

Techniques for working with Cursors

Updatable Cursor

SCROLL and INSENSITIVE Cursors

Microsoft T-SQL syntax for DECLARED CURSOR

(Stored Procedures (SP

(Server-Side Programming (Pros and Cons

Parameter Passing

OUTPUT Parameters

Three types of Stored Procedures

Returning OUTPUT parameters-

Returning Single Row-

Returning Full Result-

Error handling in nested SPs

Table Valued Parameter

(Used Defined Functions (UDF

?What is the difference between Function and SP

Scalar Functions

Scalar Function performance issues

Introduction to CLR Programming

Using CLR to code Scalar functions-

(Tabled Valued Functions (TVF

In-Line-

Multi-Statement-

Cross APPLY and OUTER APPLY-

• Using Cross Apply to solve the performance problem of Scalar functions

Triggers

Pros and Cons for using Triggers

(DML Trigger (AFTER-

• Advanced Data Validation
• Trigger vs Check constraint
• Automatic actions after data entry

Audit Log

Maintaining Calculated columns

(DML Trigger(INSTEAD OF-

• Changing SQL Server Behavior

DDL Trigger-

• ON ALL SERVER
• ON DATABASE
• Using XML methods
• LOGON Trigger