SQL Server Advanced

Duration

3,00 day(s)

Next session

Who is organizing this training?

Catalogue de formations étendu et évolutif : End-user (MS 365, SharePoint, Teams, Internet,...), IT (Power BI Desktop, langage SQL, VBA,.NET, C#,...), Web et Design (Adobe Photoshop, InDesign, Illustrator, Premiere Pro, Animate, Acrobat Pro, After Effects, HTML, Javascript,...), Project Management (MS Project)

Prerequisites

Have a knowledge of SQL Server.

Goals

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

@@teachingmethods@@

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.