SQL Server Developer

SQL Server Developer Training

• 40 Hours, daily one and half hours

Introduction and Foundation to SQL Server

• Connecting To Server
  • Authentication Modes
  • Sql Server Management Studio and Tools In Management Studio
  • Create a sample database and table
  • Your first SQL query
    • Object Explorer Details
    • Query Editor
    • Data types and built-in functions
    • CASE Expression

TSQL (Transact Structured Query Language)

• History and Features of TSQL
• Logical  Query Processing
• Filtering data with WHERE clause
• Sorting data with ORDER BY
• Using TOP
• Types Of TSQL Commands (DDl,DML,DQL,DCL,TCL)

JOINS: Usage, Advantages/Disadvantages and Performance

• INNER JOINS
• OUTER JOINS
• CROSS JOINS
• SELF JOIN
• Multi-Join Queries
• SubQuery : Self Contained and Correlated Subqueries
• CTE( Common Table Expression)
• APPLY Operator: CROSS APPLY and OUTER APPLY

SET OPERATORS

• UNION and UNION ALL
• INTERSECT
• EXCEPT
• Grouping Data with GROUP BY
• HAVING clause

Commonly Used Functions:

• Aggregate Functions (SUM,COUNT,AVG,MIN,MAX)
• Ranking Functions (ROW_NUMBER,RANK,DENSE_RANK,NTILE)

Query a FULL-TEXT data

• Full text catalog
• Full Text Index
• CONTAINS and FREETEXT
• Full text search functions

Dealing with XML data

• Introduction and storage of XML data
• FOR XML RAW
• FOR XML AUTO
• FOR XML PATH
• Convert XML to table:OPENXML
• FOR XML clause
• Xquery and Xpath

Tables and Constraints

• Creating and altering table
• IDENTITY Column
• Computed Column
• PRIMARY KEY Constraint
• UNIQUE Constraint
• FOREIGN KEY Constraint
• CHECK Constraint
• DEFAULT Constraint

Views and Function

• Create and Alter view
• View Options: WITH ENCRYPTION, WITH SCHEMABINDING
• WITH CHECK Option
• Modify data from a view
• Indexed view
• Table valued functions
• Scalar valued functions
• Synonyms

INSERT, UPDATE and DELETE

• Simple Insert
• INSERT SELECT, INSERT EXEC,SELECT INTO
• UPDATE: Update based on join, Update based on variable, Update
• DELETE:  Simple delete,DELETE based on a join
• DELETE vs. TRUNCATE
• SEQUENCE object
• MERGE Statement
• OUTPUT Clause

Transactions and Error Handling

• ACID Properties
• Transaction Commands
• Locking, Blocking and Deadlocks
• Isolation Levels
• Error handling
• Dynamic SQL

Stored Procedures

• Create, Alter and Execute stored procedure
• Use of BEGIN/END,RETURN,SET NOCOUNT, IF/ELSE
• WHILE, WAITFOR, GOTO

Triggers

• DML Triggers: After, Instead Of
• Nested Triggers
• DDL triggers

Query Optimization and performance

• Using SQL Server Profiler
• Extended Events
• Checking Execution plans
• Using DMV

Indexes:

• Heaps
• Clustered index
• Non clustered index
• Indexes and query performance
• Cursors
• Temporary table vs Table Variable