SQL Server : Indexer le colonnes de foreign key systématiquement

Une pratique qui permet d'obtenir un bon premier résultat lors de l’indexation d'une base de données SQL Server est de créer les indexes correspondants aux colonnes de clés étrangères.
En effet lorsque l'on fait des requêtes avec des jointures, d'un côté, la colonne de clé primaire auquel on accède est indexée par l'index de clé primaire, mais de l'autre, la colonne de clé étrangère elle, si elle n'est pas indexée, SQL Server doit parcourir l'ensemble des données.

Attention cette méthode est une première approche. Elle ne doit pas empêcher l'analyse des requêtes et structures des tables pour trouver les meilleurs index en fonction des contextes connus.

Voici ma requête qui me permet de trouver et créer les index absents lorsque la structure de la base a changé :

SELECT t.*
, 'IF (NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + t.parent_schema_name + '].[' + t.parent_table_name + ']'') AND name = N'''+ t.index_name + '''))
BEGIN
   CREATE NONCLUSTERED INDEX [' + t.index_name  + '] ON [' + t.parent_schema_name + '].[' + t.parent_table_name + '] 
(
[' + t.parent_column_name + '] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
' AS requete
FROM (
        SELECT fk.Name AS foreign_key_name, cfk.constraint_column_id,  pos.name AS parent_schema_name, po.name AS parent_table_name, cpo.name AS parent_column_name
         , oc.name AS referenced_table_name, cpc.name AS referenced_column_name
         , 'idx_' + LOWER(pos.name) + '_' + LOWER(po.name) + '_colonne_fk_' + LOWER(cpo.name) AS index_name
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns cfk ON fk.object_id = cfk.constraint_object_id
        INNER JOIN sys.objects po ON cfk.parent_object_id = po.object_id
        INNER JOIN sys.columns cpo ON cpo.object_id = po.object_id AND cpo.column_id = cfk.parent_column_id
        INNER JOIN sys.schemas pos ON po.schema_id = pos.schema_id
        INNER JOIN sys.objects oc ON cfk.referenced_object_id = oc.object_id
        INNER JOIN sys.columns cpc ON cpc.object_id = oc.object_id AND cpc.column_id = cfk.referenced_column_id
     ) t
LEFT JOIN (SELECT ix.name AS index_name, tx.name AS table_name, sx.name AS schema_name, ix.object_id
FROM sys.indexes ix
INNER JOIN sys.tables tx ON ix.object_id = tx.object_id
           INNER JOIN sys.schemas sx ON tx.schema_id = sx.schema_id
) i ON i.schema_name = t.parent_schema_name
             AND i.table_name = t.parent_table_name
             AND i.index_name = t.index_name
WHERE i.object_id IS NULL

Le colonne "requete" une fois sélectionnées et exécutée crée les index manquants.
Ajuster le texte en fonction des besoins (surtout par rapport aux options de l'index créé)

Expressions régulières pour parser du XLM

J'ai voulu de manière simple mettre en surbrillance une string XML pour un rendu dans une page HTML !


Bon même y a pleins de plug-in c# ou JS, voir des supers convertisseurs en ligne... je préfère me braquer cela à la main : voici ma proposition...

Il y a plusieurs types de "balises" à détecter, entourer dans le texte d'origine ces balises de <span class="Type Balise">xx</span> pour gérer cela avec du CSS.

Voici les expressions régulières associées aux types de balises :

Les commentaires :	(?<comment><!--(\w|\W)*?-->)
Les tags :	(<|</)(?<tag>\w*?)([>\s])
Les attributs :	((<\w*?\s*?)|(("|')\s*?))(?<attr>\w*)=("|')
Les valeurs des attributs :	(=(?<quote>"|'))(?<attvalue>(\w|\W)*?)?(\k<quote>)
Les valeurs des tags :	(?<value>(\w|\W)*?)<   [Penser à éliminer les valeurs vide ici]
Les séparateurs :	(?<sep><|>|=|"|')

Détecter les infos dans l'ordre et éliminer des résultats les infos dont le texte est déjà dans un groupe précédent (Par exemple pour éviter de mettre en surbrillance un tag dans un commentaire).


La suite quand j'ai le c# qui marche !!

SQL : Recherche dans le contenu des procédures, vues, ou fonctions

Vous recherchez un texte dans tous les codes des procédures, fonctions ou vues SQL Server voici une requête qui permet de faire cela...

C'est utile dans le cas suivant ;
On change les paramètres d'une fonction ou procédure. Ou est appelée cette fonction ? Quels sont les impacts de mon changement ?


La procédure utilise largement le schéma système sys de SQL Server avec les tables (ou vues) :

• sys.all_objects : qui liste les objets qui nous interresse ici
• sys.schemas pour avoir les noms des schémas
• sys.sql_modules pour avoir le contenu des procédures
• sys.system_sql_modules pour avoir le contenu des procédure systèmes s'il y a lieu

La table sys.all_objects : dispose d'une colonne type qui indique de quoi on parle :

• 'P' ou 'PC pour une procédure
• 'FN' ou 'TF' ou 'IF' pour une fonction
• 'V' pour une vue

En enlevant ces filtres on s'assure d'avoir tous les objets de la base. Cela peut être utile en fonction des besoins


Le code SQL :

DECLARE @txt VARCHAR(MAX) = 'xxx'   --- Texte à chercher

SELECT *

    FROM  (

      SELECT sp.object_id AS [id]

         , CASE sp.type WHEN 'AF' THEN 'Fonction d''agrégation (CLR)'

                        WHEN 'C'  THEN 'Contrainte CHECK'

                        WHEN 'D'  THEN 'DEFAULT (contrainte ou autonome)'

                        WHEN 'F'  THEN 'Contrainte FOREIGN KEY'

                        WHEN 'FN' THEN 'Fonction scalaire SQL'

                        WHEN 'FS' THEN 'Fonction scalaire d''assembly (CLR)'

                        WHEN 'FT' THEN 'Fonction table d''assembly (CLR)'

                        WHEN 'IF' THEN 'Fonction table en ligne SQL'

                        WHEN 'IT' THEN 'Table interne'

                        WHEN 'P'  THEN 'Procédure stockée SQL'

                        WHEN 'PC' THEN 'Procédure stockée d’assembly (CLR)'

                        WHEN 'PG' THEN 'Repère de plan'

                        WHEN 'PK' THEN 'Contrainte PRIMARY KEY'

                        WHEN 'R ' THEN 'Règle (ancienne, autonome)'

                        WHEN 'RF' THEN 'Procédure de filtre de réplication'

                        WHEN 'S ' THEN 'Table de base système'

                        WHEN 'SN' THEN 'Synonyme'

                        WHEN 'SO' THEN 'Objet séquence'

                        WHEN 'SQ' THEN 'File d''attente du service'

                        WHEN 'TA' THEN 'Déclencheur d''assembly DML (CLR)'

                        WHEN 'TF' THEN 'Fonction table SQL'

                        WHEN 'TR' THEN 'Déclencheur DML SQL'

                        WHEN 'TT' THEN 'Type de table'

                        WHEN 'U'  THEN 'Table (définie par l''utilisateur)'

                        WHEN 'UQ' THEN 'Contrainte UNIQUE'

                        WHEN 'V'  THEN 'Vue'

                        WHEN 'X'  THEN 'Procédure stockée étendu'

            ELSE sp.type END AS [type]

        , s.[name] AS [schema], sp.[name] AS [nom]

        , ISNULL(smsp.[definition], ssmsp.[definition]) AS [code_sql]

       FROM sys.all_objects AS sp

       INNER JOIN sys.schemas AS s ON sp.schema_id = s.schema_id

       LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id

       LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id

       WHERE CAST(CASE WHEN sp.is_ms_shipped = 1 THEN 1

                       WHEN (SELECT p.major_id

                             FROM sys.extended_properties AS p

                              WHERE p.major_id = sp.object_id

                                AND p.minor_id = 0

                                AND p.class = 1

                                AND p.name = N'microsoft_database_tools_support'

                             ) IS NOT NULL THEN 1

                       ELSE 0 END AS bit) = 0

         AND smsp.execute_as_principal_id IS NULL

    ) pfv

    WHERE pfv.code_sql LIKE '%' + @txt + '%'

    ORDER BY [type], [schema], [nom]

Enjoy

ADD 07-2018 
Manière plus simple d'obtenir le code d'une procédure / fonction / vue / trigger /...  (ça ne marche pas pour les tables !)
SELECT object_definition(object_id('[Nom schéma].[Nom Objet]')) AS [Definition] FOR XML  PATH('')

La clause FOR XML évite que SSMS tronque le résultat !
Plus simple
A adapter dans ce qui précède si besoin d'élargir les recherches

EDIT 08/2021

Mise à jour de la procédure pour remonter tous les cas possible de code.

Principe de fonctionnement des extractions de données

Mise en place de vue

En première approche, la mise en place de vue est la plus simple.

Cela permet de mettre au point la liste des données à manipuler simplement. D’affiner les requêtes et cas particulier jusqu’à obtenir le bon résultat.

La vue pouvant être indexé, les performances peuvent être au rendez–vous un bon moment.

Essayer de fenêtre les données dans les vus (Sur les X mois passés)

Mettre les vues dans un schéma dédié au Datamart du client.

Remplacement des Vues

Quand la vue ne fonctionne plus

C’est-à-dire, la vue est trop lente ou elle manipule trop de données.

Mise en place d’une table qui reprend les données de la vue et qui est remplie à intervalle régulier.

Remplissage ponctuel avec interruption du service

Si dans ce contexte, on peut se permettre lors du remplissage de ne pas servir les données = pas d’interrogations continue ou remplissage à heures fixes durant cette période aucune information n’est demandée.

Procédure de remplissage

Si pas de verrou alors

Verrouillage du processus

        RAZ de la table

        Remplissage de la table

        Retrait du verrou

        Cela implique de mettre en place un WatchDog qui libère les verrous au bout d’un temps anormalement long

Cela implique :

        Une table verrou avec une ligne par notion à remplir

        Une procédure qui remplit selon l’algo ci-dessus (1 par notion)

        Une procédure de watchDog pour l’ensemble des verrou

        Une table par notion à remplir.

Avec Sql Server, l'instruction TRY CATCH permet de récupérer des erreurs, mais que faire quand on est dans une transaction.

Voici un modèle qui fonctionne bien.

BEGIN TRY
   BEGIN TRANSACTION    

   --- Faire ce qu'on a a faire ici
   
   COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

  --- Traiter l'erreur ici    

END CATCH

Les points à ne pas oublier :

L'instruction BEGIN TRANSACTION est juste après le BEGIN TRY (= y a rien entre les 2 instructions)
Le COMMIT termine le traitement est est juste avant le END TRY (= la aussi y a rien entre les 2 instructions)
Le test de présence de transaction et le ROLLBACK sont fait dès le début du BEGIN CATCH

Enjoy

SQL et c# Jour de la semaine

Pour avoir le jour de la semaine avec SQL Server il faut utiliser la fonction DATEPART avec le paramètre WEEKDAY :  SELECT DATEPART(WEEKDAY, GETDATE()) pour avoir le numéro du jour d'aujourd'hui. Le résultat est un nombre entre 1 et 7. Soit :

• 1 = Lundi
• 2 = Mardi
• 3 = Mercredi
• 4 = Jeudi
• 5 = Vendredi
• 6 = Samedi
• 7 = Dimanche

Attention le résultat est fonction de la variable @@DATEFIRST qui permet d'ajuster le nombre voulu pour le résultat.

Voir la doc SQL Server pour plus de détail ; ici

En C# la propriété DayOfWeek d'un type DATETIME renvoie une énumération qui une fois casté en int renvoie des valeurs entre 0 et 6 !!
Evidement c'est pas les mêmes que SQL Server, mais pas trop loin

• 1 = Lundi
• 2 = Mardi
• 3 = Mercredi
• 4 = Jeudi
• 5 = Vendredi
• 6 = Samedi
• 0 = Dimanche

Bon pour uniformiser tout cela est simplifier l'utilisation et le transfert des infos entre les deux technos je propose la formule ci dessous qui renvoie toujours les données au format c# quelque soit la configuration SQL Server.

SELECT (DATEPART(WEEKDAY, ma_date) +  @@DATEFIRST - 1) % 7 AS jour_semaine

FROM ...

Explications : Le DATEPART renvoie le jour de la semaine pour SQL Server, L'ajout du @@DateFirst -1 permet de retomber sur un nombre qui avec le Modulo (%) 7 sera toujours identique quelque soit la valeur du @@DATEFIRST et toujours compris entre 0 et 6 c'est ce qu'il faut pour c#

Enjoy !

SQL : La date du jour sans heure

Pour Obtenir en SQL la date du jour sans avoir l'heure, et afin que cela fonctionne quelque soit la culture du serveur SQL voila ce que j'ai trouvé de mieux :

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)


Pour mémoire explication :
en 1) on fait la différence en jours entre GetDate et 0 (!) ==> cela donne le nombre de jour depuis la date 0 (!)
en 2) on ajoute à la date 0 (!!) le nombre de jour trouvé en 1) ==> la date sans l'heure !!

Enjoy !

Mise à jour 06/2021 : Ou alors plus simple CONVERT(DATE, @variableDateHeure)