Différence entre RANK () et ROW_NUMBER ()

bonjour

Quelle est la différence entre ROW_NUMBER, RANK et DENSE_RANK Lequel utiliser? Ceci est un question très commune dans l’esprit de SQL Server  vu que ces fonctions sont similaires                        

Dans SQL Server, il y a 3 fonctions de classement :

1. ROW_NUMBER :Renvoie un nombre unique pour chaque ligne commençant à 1. Pour les lignes qui ont des valeurs en double, les numéros sont attribués arbitrale.
2. RANK:Attribue un numéro unique pour chaque ligne commençant par 1, sauf pour les lignes qui ont des valeurs en double, dans ce cas, le même classement est attribué et un écart apparaît dans la séquence pour chaque classement en double
3.  DENSE_RANK:Retourne le rang de lignes au sein de la partition d’un ensemble de résultats, sans aucune lacune dans le classement. Le rang d’une ligne est un plus le nombre de rangs distincts qui viennent avant la ligne en question.

Nous allons voir la différence entre ces 3 fonctions basées sur un exemple

CREATE TABLE dbo.Grades
(Names VARCHAR(1),
Grade INT )                                                           INSERT INTO dbo.Grades                                                VALUES ('A',100),('B',90),('C',80),('D',70),('E',70),('F',60)         
 SELECT Names,
     Grade,
     ROW_NUMBER () OVER (ORDER BY Grade DESC) as ROW_NUMBER,
       RANK () OVER (ORDER BY Grade DESC) as RANK,
       DENSE_RANK () OVER (ORDER BY Grade DESC) as DENSE_RANK,
       NTILE(3) OVER(ORDER BY Grade desc) AS NTILE
FROM dbo.Grades

Comme vous pouvez le voir, ROW_NUMBER retourne un numéro de série pour chaque ligne, sans aucune lacune. Les numéros renvoyées par la fonction de DENSE_RANK ne pas avoir des lacunes et ont toujours rangs consécutifs. La fonction de RANK ne renvoie pas toujours entières consécutives cordialement

Activer et désactiver le Suivi des modifications (Change Tracking)

bonjour a tous

SQL Server 2008 a introduit une nouvelle fonction de  suivi puissant et efficace appelés «Suivi des modifications (CT) » et capture de données modifiées (CDC) ——->change Traicking 

Dans cet article, on va voir comment activer cette fonctionalité et comment l’utiliser le CT est une nouvelle fonctionnalité de SQL Server 2008 qui nous permet de suivre les informations sur les modifications que nous avons apportées aux tables où CT est activée.

Lorsque cette option est activée pour une table, elle permet de conserver une trace de chaque opération DML et les touches de la rangée qui a été touchée. Cela signifie que, à tout moment, nous pouvons interroger pour savoir quelles lignes dans notre table (s) ont été insérés / supprimé / mise à jour

Si nous voulons travailler avec le CT, nous devons activer le  CT sur notre base de données et les liste des tables sur lesquelles nous sommes intéressés à suivre les changements. Nous ne pouvons pas permettre à CT pour la table sans activer CT pour la base de données. Alors, faut d’abord activer CT pour la base de données, puis pour la table                                                                                           comme le montre la capture d’écran ci-dessous:on fait un clique droit sur la base puis propriété puis on fait le chois sur le bouton suivi de modification

 

En T SQL

USE [master]
GO
ALTER DATABASE [Track_appli_portail] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS)
GO

maintenant on procède a l’activer pour une table en utilisant SSMS en sélectionnant Propriétés de table> Suivi des modifications, comme le montre la capture d’écran ci-dessous:

USE [Track_appli_portail]
GO
ALTER TABLE [dbo].[utilisateur] ENABLE CHANGE_TRACKING 
GO

pour récupérer les données modifiées on va utilisé une fonction appelées CHANGETABLE ,La fonction CHANGETABLE a deux paramètres. Le premier paramètre est de fournir le nom de la table. CHANGES  est un mot clé que vous devez inclure pour récupérer les changements. Une autre option VERSION sera discuté plus tard

Le second paramètre est le numéro de version. Le numéro de version sera maintenue entre la base de données. Pour une seule opération sur une table qui a suivi des modifications  le numéro de version sera incrémenté. Par exemple, après l’activation du suivi des modifications, le numéro de version sera 0 et après l’insertion d’enregistrements dans le tableau A le numéro de version sera 1 et d’effectuer les mises à jour se traduira par un numéro de version 2.

Si vous passez 0 comme  paramètre de version, vous obtiendrez toutes les modifications qui ont été faites après la version 0 qui signifie que toutes les modifications après l’activation du suivi Change. La chose importante à noter ici est que vous obtiendrez les variations nettes, ce qui est très pratique pour l’entreposage de données.                                                                                                                            la fonction la plus importante est CHANGE_TRACKING_CURRENT_VERSION. Cela renvoie la version actuelle après la dernière transaction validée au niveau de la base de données. pour connaitre il faut taper cette requêtes :SELECT CHANGE_TRACKING_CURRENT_VERSION()

Maintenant on va procéder a faire quelque opération d’insertion,mise a joue,suppression….

faisant maintenant l’exécution de cette requête et voir la résultat

SELECT * FROM CHANGETABLE(CHANGES dbo.utilisateur,0)as résultat

SELECT SYS_CHANGE_VERSION,SYS_CHANGE_OPERATION,matricule
FROM CHANGETABLE(CHANGES dbo.utilisateur,0)AS ET
ORDER BY SYS_CHANGE_VERSION

Dans ce tableau on explique la définition de chaque colonne

Nom de la colonne	Description
SYS_CHANGE_VERSION	Il représente le dernier numéro de version quand une ligne particulière a été mise à jour.
SYS_CHANGE_CREATION_VERSION	Il représente le numéro de version lorsqu’un enregistrement a été inséré. Il ne sera pas écrasée comme dans le cas de SYS_CHANGE_VERSION.
SYS_CHANGE_OPERATION	Il représente les opérations DML (I = INSERT, UPDATE et U = D = DELETE)
SYS_CHANGE_COLUMNS	Elle représente toutes les colonnes touchées depuis la version dernière de référence. Cette colonne devra valeurs seulement pour les opérations UPDATE et si les colonnes ne sont pas affectés lors de la mise à jour, il aura la valeur NULL.
	Il représente les colonnes de clé primaire de votre table user suivis. Vous pouvez rejoindre votre table user suivis avec ces colonnes pour obtenir des données seulement changé à partir de la table user suivis.

Je espère que vous avez apprécié l’apprentissage CT ? N’hésiter a activer cette fonctionnalité sur vos base de production

cordialement

..

la différence entre l’opération Key Lookup et RID Lookup

bonjour

Question :Quel est la différence en entre l’opération « Key Lookup » et l’opération « RID Lookup »
et comment optimiser ces opérations quant il apparaît dans un plan d’exécution ?

Réponsse :

Key Lookup (Clustered) :Récupère une ligne unique à partir d’un index organisé

RID Lookup (Heap) Récupère une ligne unique à partir d’une table Heaps

La différence réside que ces opérations ne s’appliquent pas aux mêmes structures. « Key lookup » signifie que l’opération se fera dans un index alors que pour « RID Lookup » indique que  l’opération concernant une recherche de ligne dans une table Heap                                                                            en résumé Key Lookup survient lorsque la table est organisée en Cluster, alors que l’opération RID Lookup survient lorsque la table n’est pas organisée en cluster

une des optimisations envisageables lorsque ce type d’opération existe est de créer un index dit « couvrant ». Le but de cet index est d’éliminer ces opérations de recherche qui sont souvent coûteuses. Pour cela l’index doit satisfaire au prédicat et aux colonnes à ramener de la requête
Le fait d’inclure des colonnes dans les index permet d’éviter, lorsque l’index est utilisé, de devoir aller chercher des données complémentaires dans la table sous jacente.

cordialement

Ajouter un fichier vidéo ou audio dans un site sharepoint

bonjour a tous

Nous allons voir dans cet article comment ajouter une vidéo dans un site  SharePoint2010 ,avant de commencer

il faut  Vérifier si tout est près à l’utilisation…

1. Est-ce que vous voyez les animations sur le site SilverLight ?

2. Est-ce que vous voyer l’option “Vidéo et audio” dans l’onglet “insérer ” du ruban ? Vous ne trouvez pas le webpart de Vidéo et audio ?

 il faut procéder a: Activer la fonctionnalité d’infrastructure de publication

Si vous avez créé votre site ou collection de sites à l’aide de l’un des modèles de site de publication, l’infrastructure de publication est déjà désactivé donc il faut procéder a l’activer

1. aller dans la racine de votre collection de sites, cliquez sur Paramètres > Paramètres du site.
2. Dans la page Paramètres du site, sous Administration de la collection de sites, cliquez sur Fonctionnalités de la collection de sites.
3. Dans la page Fonctionnalités de la collection de sites, cliquez sur Activer en regard de Infrastructure de publication SharePoint Server

une fois la publication activée dans une collection de sites il faut l’activer dans la page Paramètres du site

Lorsque vous activez la fonctionnalité d’infrastructure de publication, le composant web part vidéo et audio est activé  :

maintenant Vous pouvez ajouter insérer un composants WebPart multimédias à la page pour lire un fichier audio ou vidéo

    

cordialement

remplacer les valeurs null sql

bonjour

On va voir dans article  comment remplacer les valeur Nul par d’autre valeur

on va utiliser pour cela 3 méthode (2 en T SQL) et une en utilisant SSIS

* fonction COALESCE                                                                                                                       * fonction ISNULL                                                                                                                                        * Expression REPLACENULL (ssis 2012 –>2016) 

Démonstration

CREATE TABLE [dbo].[T_test](
[test1] [int] NULL,
[test2] [int] NULL,
[test3] [int] NULL,
[test4] [int] NULL,
[test5] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO T_test VALUES (1, 2, 3, 4, 5)                             INSERT INTO T_test VALUES (NULL, 2, 3, 4, 5)                          INSERT INTO T_test VALUES (NULL, 2, NULL, 4, NULL)                    INSERT INTO T_test VALUES (NULL, NULL, NULL, 4, 5)        

select isnull(test1,0),isnull(test2,0),isnull(test3,0),isnull(test4,0),isnull(test5,0) from T_test

 select coalesce(test1,0),coalesce(test2,0),coalesce(test3,0),coalesce(test4,0),coalesce(test5,0) from T_test

une petit remarque ISNULL est spécifique à SQL Server alors que COALESCE est conforme à la norme ANSI

Voici maintenant comment nous allons remplacer les valeurs NULL en utilisant Dérivé Transformation de colonne ,SSIS 2012 a introduit une nouvelle Expression REPLACENUL , avec  les versions précédentes de SSIS, nous devons utiliser  les expressions  else ISNULL                              L’exemple suivant remplace toute valeur NULL dans une colonne de base de données avec la valeur (0).Cette fonction est surtout utilisé dans les modèles de colonne dérivée communes                dans lequel vous souhaitez remplacer les valeurs NULL avec autre chose

ci joint une petit démonstration

 

Comme nous pouvons le voir  les valeurs NULL sont remplacés par « 0 »

cordialement

order by newid()

bonjour a tous

SI Vous souhaitez sélectionner un échantillon aléatoire de lignes d’une grande table avec beaucoup de lignes C’est  order by newid()

La méthode standard  pour obtenir des lignes aléatoires à partir d’une petite table est d’utiliser order by newid() cette dernière va trier les enregistrements au hasard

select * from RessHum_Conge 
where dt_cuf>dateadd(day,-10,current_timestamp) 
order by newid()

la fonction NEWID, qui génère un identifiant unique global (GUID) en mémoire pour chaque ligne. Par définition, le GUID est unique et assez aléatoire, vous obtenez un ordre aléatoire des lignes de la table

cordialement

paramétrer calendrier dans un rapport SSRS

Bonjour a tous

En SSRS, la date est un paramètre particulier, on peut utiliser le format d’affichage de calendrier.

étape 1:Filtrez les données de rapport au niveau de la source de données en écrivant des requêtes de dataset qui comportent des variables (on utilise @date1,@date2 dans cette exemple)

WHERE dtsys BETWEEN @Date1 AND @Date2

L’image suivante du Générateur de rapports montre que les deux paramétrés mis dans le requétes du dataset @date1 et @date2 s’apparaît dans l’icone paramètre 

Dans la boîte de dialogue clique droit Propriétés de paramètres choisissez comme type de paramètres de données « date / Heure »

Un calendrier Aperçu dans l’affichage du rapport  afin de sélectionner la date 1 et la date 2

Lorsque vous visualisez un rapport, la barre d’outils de la visionneuse de rapports affiche chaque paramètre de sorte que l’utilisateur puisse spécifier des valeurs de manière interactive

 

cordialement

identifier les indexes manquants

bonjour

Une fonctionnalité intéressante, introduite avec SQL Server 2005, est la recherche d’indexes manquants et leur bénéfices  Les informations d’index manquants sont également stockés dans un espace de cache, et peuvent être requêtes à travers trois vues système, qui offrent toutes les informations nécessaire pour la création de l’index : colonnes qui devraient composer le clé, colonnes à inclure dans le noeud feuille, avec un compteur qui indique le nombre de fois où l’index aurait été utile

La fonctionnalité d’index manquant est très intéressante, mais il faut la voir comme un indicateur d’une possibilité de contre-performance dans un plan de requête                                                                       Vérifiez l’usage de vos index à l’aide de sys.dm_db_index_usage_stats avec suffisamment de recul (au moins 1 mois d’existence de l’index).

voici une requête qui vous donne toutes les informations :

SELECT TOP 20 *,
 ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)*0.01,1) AS [Bénéfice Cumulé (%)] --Bénéfice cumulé pour les requêtes utilisateurs ,avg_user_impact AS [Bénéfice Moyen (%)] --Bénéfice moyen (en pourcentage) dont les requêtes utilisateur pourraient tirer parti si ce groupe d'index manquants était implémenté. Cela signifie que le coût des requêtes diminuerait, en moyenne, de la valeur de ce pourcentage si ce groupe d'index manquants était implémenté. , [statement] AS [Table] , equality_columns AS [ListeColonnesEgalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'égalité au format :table.column = constant_value , inequality_columns AS [ListeColonnesInegalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'inégalité, par exemple, les prédicats au format : table.column > constant_value , included_columns AS [ListeColonnesCouverture] -- Liste de colonnes, séparées par des virgules, requises comme colonnes de couverture pour la requête 
FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [Bénéfice Cumulé (%)] DESC;

Voici le résultat :

comment Restaurer la base de données master ?

bonjour

Cette rubrique explique comment restaurer la base master  à partir d’une sauvegarde de base de données complète

Il est nécessaire de démarrer SQL Server en mode mono utilisateur Pour démarrer SQL Server en mode mono-utilisateur une procédure très simple comme indiqué ci-dessous  ,Aller au service et sélectionner Service SQL Server (MSSQL Server). choisir le paramètre  ‘-m;’ comme option du démarrage

une fois l’instance SQL Server est en cours d’exécution en mode mono-utilisateur passer vers le  cmd et taper sqlcmd et exécuter la commande suivante

SQLCMD  
Restore database master from Disk ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\master.bak’  WITH REPLACE ;  GO

Bien évidemment il faudra revenir au mode multi utilisateur en supprimant le  -m et en redémarrant le service

cordialement

comment identifier les transactions ouvertes ?

bonjour tout le monde

Aujourd’hui sur le site developpez.net dans le forum dédier pour  SQL server un utilisateur a pauser cette question

Question:Bonjour tout le monde,
je voudrais savoir si il existe une fonction SQL-serveur qui me permettrait de savoir si une transaction est en cours, et connaitre quelques renseignement sur celle-ci

Réponsse:Pour obtenir des informations sur la transaction active la plus ancienne dans la base courante , Il existe de nombreuses façons d’identifier les transactions ouvertes                                    vous pouvez utiliser l’un de ces exemples code

DBCC OPENTRAN

select * from sys.sysprocesses
where open_tran <>0

-- vérifier le nombre de transactions en cours    
SELECT @@TRANCOUNT -- Résultat : 0   
GO

ci joint l’imprime écran après l’exécution du commande DBCC opentran

cordialement