SQL Server Advanced

Have a knowledge of SQL Server.

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

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

Notre savoir-faire nous a permis de développer une méthode pédagogique spécifique basée sur la pratique et utilisée par tous nos formateurs. Notre méthode est construite autour de 2 piliers: l’expertise du formateur et l’analyse du besoin du participant.

Une attestation de présence peut être délivrée à la demande du participant en fin de formation.