Identity int trop petit

Bonjour,

J'intégre des données dans une table avec la colonne int identity, et je reçois le message suivant :

Code:

---

dépassement arithmétique s'est produite lors de la conversion de IDENTITY en type de données int

---

Après quelques recherche, je pense que j'ai atteind la plus grande valeur possible de INT ! Pas pensé au début !! :oops:

Aussi comment résoudre mon problème :
- Passer la colonne en BIGINT, mais je me retrouve avec le même problème dans quelques mois
- Passer l'ID en ALPHA avec un trigger, mais le volume de données est très très conséquent
- Utiliser un FLOAT par exemple ou autre, mais je ne sais pas comment faire

Est il possible de faire un

Code:

---

DBCC CHECKIDENT('MaTable', RESEED, 0)

---

Pour résoudre le problème rapidement sans vider la table (min(ID) = 1 117 851 479)

Merci pour votre aide.

Citation:

---

Envoyé par tornade69

- Passer la colonne en BIGINT, mais je me retrouve avec le même problème dans quelques mois

---

En effet, vous vous retrouverez bloqué dans 12 mois si vous insérez 750 millions de milliards de lignes par mois... est-ce votre cas ??? :aie:

Les deux autres solutions solutions sont a éviter.

Pouvez vous décrire un peu le contexte ? (y-a-til des contraintes qui pointent vers cette table ?)
Quelle est la volumétrie ?

Une remarque : (Même si c'est un peu tard, ça pourra servir pour la prochaine fois )
En laissant les valeurs par défaut lors de la création d'une colonne identity, le compteur démarre à 0, vous "perdez" ainsi l'utilité de la moitié des valeurs possibles : les nombres négatifs. Dans votre cas pour un INT, ça fait plus de deux milliards d'identifiants non utilisés...

Bonjour,

Non je n'ajoute pas autant de données dans la table chaque mois, Le problème vient que le volume est important, mais aussi que des données sont régulièrement supprimer (erreur d'import ou correction de donnée). Donc la suite n'est pas pleine sur le nombre d'enregistrement.

La table ne comporte que 893 902 750 eng (ok c'est un bon début) mais en bien deça de la valeur maxi du small int. J'estime que le volume va tripler au cours des 12 prochains mois.

Pourquoi avoir ajouté un champs ID, uniquement pour amélioré sensiblement les performances des requetes sur la table.

Merci

Bonjour,

Pour résoudre mon problème, la solution suivante est elle bonne :

Code:

---

1 2 3

Alter table matable drop column id   alter table matable add id int (ou bigint) identity (-2 147 483 648,1)

---

Et faire un prière pour que le fichier de log n'explose pas !

Merci de vos conseils.

En faisant ainsi (il faudra d’ailleurs préalablement supprimer la clef primaire), vous perdrez vos id...
n'est-ce pas gênant ???

de plus, a terme, vous pourriez avoir des collision d'id, entrainant des erreurs de violation de clef primaire

Je ne sais pas si c'est une bonne idée mais si on mets les contraintes d'intégrité de cette clef primaire en on update cascade, on peut alors renuméroté toutes nos lignes depuis le plus petit entier et résoudre ainsi ce dépassement.

Et pour être sûr, on peut en profiter pour changer le datatype en bigint histoire d'assurer ses arrières.

Par contre, ça risque de prendre pas mal de temps...

Concrètement :

1. j'ajouterais une colonne bigint à la table et je numéroterais chaque ligne.
2. je passe les contraintes d'intégrité en "ON UPDATE : CASCADE"
3. je passe le datatype de la colonne qui pose problème en bigint (chez moi, ça aussi aussi le datatype des colonnes liées)
4. je modifie les valeurs de la colonne qui pose problème avec les valeurs de ma nouvelle colonne
5. je supprime la colonne bigint qui a été ajoutée au début et qui ne sert plus à rien

Voilà, étant DBA débutant, qu'en penses nos chers experts du forum ?

En règle générale, on évite de modifier des identifiants. C'est la raison pour laquelle il convient d'opter pour des valeurs asémantiques.
Ici, le cas semble un peu particulier, mais il nous faudrait plus de détails (c'est pour cela que je demandais si d'autres tables référençaient la table en question).

Avec les détails dont on dispose, sachant que le bigint laisse quand même une sacrée marge (Pour rappel, du bigint, c'est jusqu’à plus de 9 trillions) et ne devrait pas beaucoup impacter les performances (voire même les améliorer si le serveur est en 64 bits), je passerai simplement la colonne en bigint (il faudra pour cela supprimer puis recréer la clef primaire).

Merci à tous pour vos propositions

Les ID ne servent à rien, sauf accélérer les temps de recherches dans la base : pas de relation

Oui bien sur je suis obliger de tuer la clé primaire avant. (environ 2 heures)

Avec un peu de chance, je peux modifier le type de Int à Bigint. Je ne suis pas sûr que cela soit possible sur une colonne Identity
Sinon
Je drop la colonne (a mon avis 5 heures) et un fichier de log à 300 Go . et ensuite je crée la nouvelle colonne en BIGINT avec une valeur de départ (-9 223 372 036 854 775 800) (encore 5 heures de travail)

Par contre je ne sais pas ce que c'est : valeurs asémantiques

Coup de chance le serveur est 64 bits

Un grand merci pour vos conseils et vos analyses.
Et donc je vais passer mon WE a modifier le type de la colonne en question.

Vraiment un grand merci à vous.

Citation:

---

Envoyé par tornade69

Les ID ne servent à rien, sauf accélérer les temps de recherches dans la base : pas de relation

---

J'ai du mal à comprendre... un exemple concret serait le bienvenu...

Citation:

---

Envoyé par tornade69

Par contre je ne sais pas ce que c'est : valeurs asémantiques

---

Des valeurs qui ne portent pas de sens particulier dans votre SI.

Citation:

---

Envoyé par tornade69

Avec un peu de chance, je peux modifier le type de Int à Bigint. Je ne suis pas sûr que cela soit possible sur une colonne Identity

---

Si, c'est possible.

Citation:

---

Envoyé par tornade69

Et donc je vais passer mon WE a modifier le type de la colonne en question.

---

Non, c'est votre serveur qui va y passer son WE, pendant que vous profiterez de vos grass mat'. Car globalement, le script ressemble a ceci :

Code:

---

1 2 3 4 5 6 7

--suppression de la PK ALTER TABLE LaTable DROP PK_LaTable --passage de la colonne en BIGINT ALTER TABLE LaTable ALTER COLUMN id BIGINT --recréation de la contrainte ALTER TABLE LaTable ADD CONSTRAINT PK_LaTable PRIMARY KEY(id)

---

A adapter, et bien sûr à tester avant, sinon il n'est pas a exclure que vous ayez à faire face à des utilisateurs mécontents lundi matin si quelque chose s'est mal passé :aie:

Bonsoir,

Les ID ne servent à rien, sauf accélérer les temps de recherches dans la base : pas de relation

en fait au début de la base il n'y avait pas d'ID Identity sur la table. Mais au fur et a mesure de l'ajout de données, les performances sont devenu déplorable. Au dela des index, l'ajout de la colonne Identity a permit d'avoir de meilleures performances.

Pour info on est passé de 12 min à 12 secondes sur certaines requetes. Cette table en fait contient des résultats de collect sur des automates.

J'espère avoir été plus clair.

Bonjour,

Citation:

---

Envoyé par tornade69

Non je n'ajoute pas autant de données dans la table chaque mois, Le problème vient que le volume est important, mais aussi que des données sont régulièrement supprimer (erreur d'import ou correction de donnée). Donc la suite n'est pas pleine sur le nombre d'enregistrement.

La table ne comporte que 893 902 750 eng (ok c'est un bon début) mais en bien deça de la valeur maxi du small int. J'estime que le volume va tripler au cours des 12 prochains mois.

---

SMALLINT ? 8O
Cela m'étonnerait, la plus grande valeur possible est 32 767 !
INT ?
La plus grande valeur possible est 2 147 483 647. Combien de lignes insérez vous (vos automates) par mois pour atteindre cette valeur ? :koi:

Pour atteindre cette limite en fonctionnement 24 heures sur 24, 7 jours sur 7, il faut insérer :

• en 30 jours, plus de 828 lignes/secondes,
• en 90 jours, plus de 276 lignes/secondes,
• en 180 jours, plus de 138 lignes/secondes,
• en 360 jours, plus de 69 lignes/secondes.

Le nombre de lignes de votre table est actuellement à 42% de la valeur maximale d'un INT. Même en ne connaissant pas l'age de votre base, ce n'est pas anodin...
Toutefois, d'après vos déclarations, j'en déduis que chaque jour (mois), des milliers (millions) de lignes sont supprimées... :weird:

Passer la colonne en BIGINT est effectivement une piste, mais n'est ce pas reculer pour mieux sauter ?

Il y a la AMHA un sérieux problème de conception... :(

A quoi sert cette colonne ID ? à des jointures ? ou juste à un index sur la table pour accélérer des requêtes notamment une qui serait passée de 12 minutes à 12 secondes ?

Bonsoir,

Je n'avais pas vu la réponse de philippe :

Il suffit d'avoir 800 millions de lignes et de devoir refaire des imports complet tous les mois !! Et c'est bien du INT

C'est malheuresement la vérité !

Et oui c'est reculer pour mieux sauter, car lors de la conception le nombre de ligne n'était pas important, seulement 400 000. Et a cause de ses problèmes, nous allons reconcevoir l'application. C'est pour cela que la version actuelle doit tenir 6 mois, le temps de la nouvelle version.

Cordialement

Et encore merci à tous pour votre aide

800 millions de lignes par mois... voilà une valeur concrète qui permet d'apporter une réponse concrète :
- Avec du INT, vous êtes en effet "bloqués" au bout de 2 mois.
- Avec du BIGINT, vous serez "bloqués" au bout de... 960 millions d'années. Je sais qu'a l'instar du bâtiment, les projets informatiques sont toujours en retard, mais là je pense que ça devrait quand même vous laisser le temps de développer votre nouvelle version non ?

Citation:

---

Envoyé par aieeeuuuuu

En règle générale, on évite de modifier des identifiants. C'est la raison pour laquelle il convient d'opter pour des valeurs asémantiques.
Ici, le cas semble un peu particulier, mais il nous faudrait plus de détails (c'est pour cela que je demandais si d'autres tables référençaient la table en question).

Avec les détails dont on dispose, sachant que le bigint laisse quand même une sacrée marge (Pour rappel, du bigint, c'est jusqu’à plus de 9 trillions) et ne devrait pas beaucoup impacter les performances (voire même les améliorer si le serveur est en 64 bits), je passerai simplement la colonne en bigint (il faudra pour cela supprimer puis recréer la clef primaire).

---

Aprés avoir supprimer ce PK et de le crée de nouveau est'il nécessaire de recréé l'index cluster qui pointe directement sur ce colonne
je pause cette question car je suis presque en méme situation et je pense a le modifier vers Bigint mais j’insiste sur le performance et la qualité d'index après cette modification