SQL Server Avancé

Objectifs

Have a better use of SQL Server and gain Advanced skills.

Public cible

People who did already follow an introductory training or who already work with SQL Server.

Prérequis

Have a knowledge of SQL Server.

Contenu

A correct approach to SQL Server (do’s and don’t’s)

• Tables versus views, versus stored procedures (the advanced and true explanation)
• When using a snowflake and when using star scheme
  • Spend a few hours/days more reflecting on reference tables, data tables and win in the end weeks of work
• Performance is a thing from the start
  • Historical versus live data
  • Hard delete versus soft delete
  • Execution plans
  • …
• To relationship or not to relationship

The real deal stored procedures

• How to set up correct stored procedures
  • Parameters versus variables
  • SELECT versus ACTION stored procedure and how to combine both
• Create hierarchical stored procedures
  • IF, WHILE, BEGIN, END
  • OFFSET/FETCH
  • Common Table Expressions versus temporary tables versus tables (CREATE)
• When regional settings becomes a burden
  • Make all data look the same (DATE-TIME, numeric functions)
  • CONVERT and CAST and FORMAT
• Aggregate data with advanced options
  • Beyond GROUP BY
  • OVER/PARTITION
  • ROLLUP
  • CUBE
  • GROUPING SETS
  • (UN)PIVOT
• Advanced window functions
  • RANK, DENSE_RANK, PERCENT_RANK
  • ROW_NUMBER
  • LAG, LEAD
  • NTILE
• ACTION stored procedures
  • UPDATE, INSERT, DELETE
  • Cascade operation (@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT)
• Useful operations
  • EXCEPT
  • UNION
  • INTERSECT
  • STRING_AGG
  • STRING_SPLIT
  • CROSS/OUTER APPLY
  • SELF JOINS
  • WITH RECURSIVE

Error handling

• TRY-CATCH/COMMIT
• THROW errors

Clean data

• Sequences
• Primary key types

Unique data

• Convertir une chaîne, un entier, un nombre
• Générer des nombres aléatoires
• Accomplir des opérations arithmétiques et arrondir

We listen to you and help you on the spot