clé primaire de type identity

**ep31** · 03/04/2008, 09h41

Bonjour,
j'ai sur une table un clé primaire étant numéric identity (l'équivalent d'une sequence oracle).
J'aurais deux questions à vous poser :
- Est-il possible de récupérer le numéro identity créé par SQL Server lors d'une requête INSERT INTO MA_TABLE VALUES (...) via la méthode executeUpdate du package java.sql.PreparedStatement ?
- Ensuite, est-il possible de désactiver l'incrémentation d'une identity lors d'une erreur survenue lors d'un INSERT ? Je m'explique :

A un instant x, mon identity vaut 10
J'insère un élément, mon identity est incrémenté et vaut 11
J'exécute un INSERT mais dont un champ est trop long ou constraint violation Foreign Key ... : mon identity est incrémenté alors que rien n'a été inséré dans ma table. Est-il possible de ne pas incrémenter cet identity lors d'une erreur d'INSERT ?

Merci pour vos réponses

**SQLpro** · 03/04/2008, 11h28

1) dernier auto incrément
Le dernier auto incrément effectué dans la session est obtenu par la variable globale @@IDENTITY.
Pour le lire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT @@IDENTITY AS N

Le dernier auto incrément effectué dans la session et dans l'étendue de code est obtenu par la fonction SCOPE_IDENTITY()
Pour le lire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT SCOPE_IDENTITY() AS N

C'est notamment utile si l'insertion à générer une autre insertion dans un trigger...

Le dernier auto incrément effectué sur une table précise et quelque soit la session et l'étendue de code est obtenu par la fonction IDENT_CURRENT('nom_table')

2) trous d'auto incrément
Il est normal que vous ayez des trous si au moment de l'insertion une contrainte est violée. En effet il faut d'abord vérifier la contrainte de clef primaire, ce qui suppose la génération de la valeur de la clef auto incrémentée.
Vous pouvez parfaitement récupérer ces trous, mais cela est inutile et extrêmement dangereux...
Inutile car s'agissant de n° attribuer avec un hasard ordonné il n'y a pas d'intérêt que Pierre, Paul ou Jacques hérite de tel n° ou tel autre, pas plus qu'il n'y a d'intérêt que l'on gère la continuité.
Dangereux car pour effectuer cette manœuvre il vous faut piloter une transaction au plus haut niveau d'isolation (SERIALIZABLE) ce qui verrouille de manière exclusive toute la table et provoque une forte contention de nature à générer des verrous mortels. Bref si vous voulez des performances catastrophiques, c'est ce qu'il faut faire...
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 
BEGIN TRANSACTION
 
INSERT INTO MA_TABLE VALUES (...) 
IF @@ERROR <> 0 GOTO LBL_ERROR
 
GOTO LBL_RESUME
 
LBL_ERROR:
 
DECLARE @LASTID INT
SET @LASTID  = @@IDENTITY
DECLARE @SQL VARCHAR(512)
SET @SQL = 'DBCC CHECKIDENT (''matable'', RESEED), ' + CAST(@LASTID - 1 AS VARCHAR(32)) + ')'
EXEC (@SQL)
 
ROLLBACK TRANSACTION
 
LBL_RESUME
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED