Equivalent du RowID d'Oracle dans SQL Server

Question

Quel est l'équivalent du RowID d'Oracle dans SQL Server ?

Answer 1

Extrait de la documentation d'Oracle

ROWID Pseudo-colonne

Pour chaque ligne de la base de données, la pseudo-colonne ROWID renvoie l'adresse de la ligne. adresse de la ligne. Les valeurs rowid des bases de données Oracle contiennent des informations nécessaires pour localiser une ligne :

• Le numéro d'objet de données de l'objet
• Le bloc de données du fichier de données dans lequel se trouve la ligne.
• La position de la rangée dans le bloc de données (la première rangée est 0).
• Le fichier de données dans lequel se trouve la ligne (le premier fichier est le 1). Le numéro du fichier est relatif au tablespace.

L'équivalent le plus proche de cette fonction dans SQL Server est la fonction rid qui comporte trois composantes File:Page:Slot .

Dans SQL Server 2008, il est possible d'utiliser la méthode non documentée et non prise en charge intitulée %%physloc%% colonne virtuelle pour voir ça. T binary(8) avec l'ID de la page dans les quatre premiers octets, puis 2 octets pour l'ID du fichier, suivis de 2 octets pour l'emplacement sur la page.

La fonction scalaire sys.fn_PhysLocFormatter ou le sys.fn_PhysLocCracker TVF peut être utilisé pour convertir ceci en une forme plus lisible.

CREATE TABLE T(X INT);

INSERT INTO T VALUES(1),(2)

SELECT %%physloc%% AS [%%physloc%%],
       sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM T

Exemple de sortie

+--------------------+----------------+
|    %%physloc%%     | File:Page:Slot |
+--------------------+----------------+
| 0x2926020001000000 | (1:140841:0)   |
| 0x2926020001000100 | (1:140841:1)   |
+--------------------+----------------+

Notez que le processeur de requêtes n'en tire pas parti. Alors qu'il est possible pour l'utiliser dans un WHERE clause

SELECT *
FROM T
WHERE %%physloc%% = 0x2926020001000100 

Le serveur SQL sera no recherche directement la ligne spécifiée. Au lieu de cela, il effectuera un balayage complet de la table, évaluera %%physloc%% pour chaque ligne et renvoie celle qui correspond (s'il y en a une).

Pour inverser le processus effectué par les 2 fonctions mentionnées précédemment et obtenir la binary(8) correspondant à des valeurs connues de Fichier, Page, Slot, les valeurs ci-dessous peuvent être utilisées.

DECLARE @FileId int = 1,
        @PageId int = 338,
        @Slot   int = 3

SELECT CAST(REVERSE(CAST(@PageId AS BINARY(4))) AS BINARY(4)) +
       CAST(REVERSE(CAST(@FileId AS BINARY(2))) AS BINARY(2)) +
       CAST(REVERSE(CAST(@Slot   AS BINARY(2))) AS BINARY(2))

Answer 2

Je dois déduire une très grande table avec de nombreuses colonnes et la vitesse est importante. J'utilise donc cette méthode qui fonctionne pour n'importe quelle table :

delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From MyTable) T
Where T.RowNumber > 1

Answer 3

Si vous souhaitez identifier de manière unique une ligne dans la table plutôt que dans votre ensemble de résultats, vous devez envisager d'utiliser quelque chose comme une colonne IDENTITY. Voir "Propriété IDENTITY" dans l'aide de SQL Server. Le serveur SQL ne génère pas automatiquement un ID pour chaque ligne de la table comme le fait Oracle. Vous devez donc prendre la peine de créer votre propre colonne ID et de la récupérer explicitement dans votre requête.

EDIT : pour la numérotation dynamique des lignes du jeu de résultats, voir ci-dessous, mais cela serait probablement un équivalent pour le ROWNUM d'Oracle et je suppose, d'après tous les commentaires sur la page, que vous voulez les choses ci-dessus. Pour SQL Server 2005 et les versions ultérieures, vous pouvez utiliser la nouvelle fonction Fonctions de classement pour réaliser une numérotation dynamique des lignes.

Par exemple, je fais cela pour une de mes requêtes :

select row_number() over (order by rn_execution_date asc) as 'Row Number', rn_execution_date as 'Execution Date', count(*) as 'Count'
from td.run
where rn_execution_date >= '2009-05-19'
group by rn_execution_date
order by rn_execution_date asc

Vous donnera :

Row Number  Execution Date           Count
----------  -----------------        -----
1          2009-05-19 00:00:00.000  280
2          2009-05-20 00:00:00.000  269
3          2009-05-21 00:00:00.000  279

Il y a aussi un article sur support.microsoft.com sur la numérotation dynamique des lignes.

Answer 4

Découvrez le nouveau ROW_NUMBER fonction. Cela fonctionne comme suit :

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

Answer 5

Plusieurs des réponses ci-dessus seront travailler autour l'absence de référence directe à une ligne spécifique, mais ne fonctionnera pas si des modifications sont apportées aux autres lignes d'un tableau. C'est mon critère pour lequel les réponses sont techniquement insuffisantes.

Une utilisation courante du ROWID d'Oracle consiste à fournir une méthode (assez) stable pour sélectionner des lignes et revenir ultérieurement à la ligne pour la traiter (par exemple, pour la mettre à jour). La méthode de recherche d'une ligne (jointures complexes, recherche en texte intégral ou navigation ligne par ligne et application de tests procéduraux sur les données) peut ne pas être réutilisée facilement ou en toute sécurité pour qualifier l'instruction UPDATE.

Le RID de SQL Server semble fournir la même fonctionnalité, mais n'offre pas les mêmes performances. C'est le seul problème que je vois, et malheureusement le but de conserver un ROWID est d'éviter de répéter une opération coûteuse pour trouver la ligne dans, disons, une très grande table. Néanmoins, les performances sont acceptables dans de nombreux cas. Si Microsoft ajuste l'optimiseur dans une prochaine version, le problème de performance pourrait être résolu.

Il est également possible d'utiliser simplement FOR UPDATE et de garder le CURSEUR ouvert dans un programme procédural. Toutefois, cela peut s'avérer coûteux dans le cadre d'un traitement par lots important ou complexe.

Attention : Même le ROWID d'Oracle ne serait pas stable si le DBA, entre le SELECT et le UPDATE, par exemple, devait reconstruire la base de données, car il s'agit de l'identifiant physique de la ligne. Le dispositif ROWID ne devrait donc être utilisé que dans le cadre d'une tâche bien délimitée.