Réatsortingbution d'ID dans un champ de type non-IDENTITY dans la database SQL Server

AVERTISSEMENT: Ce conte de malheur contient des exemples d'odeurs de code et de mauvaises décisions de design, ainsi que des dettes techniques.

Si vous maîsortingsez les principes SOLID, pratiquez le TDD et testez votre travail, NE LISEZ PAS . À less que vous ne vouliez un bon rire devant le malheur de quelqu'un et que vous vous réjouissiez de votre propre expérience, sachant que vous ne laisseriez jamais derrière une telle stack monumentale de merde pour vos successeurs.

Donc, si vous êtes assis confortablement, je vais commencer.

Dans cette application que j'ai héritée et supscope et corrigée pendant les 7 derniers mois, je me suis retrouvée avec un DOOZY de balles d'un développeur qui est parti il ​​y a 6 mois et demi. Oui, 2 semaines après avoir commencé.

• Très long statut de procédure stockée
• Convertir une colonne existante en identité
• Comment convertir un script mssql en mysql
• Trouver des tables sans données
• Combiner les lignes si la valeur est vide

En tous cas. Dans cette application, nous avons des clients , des employees et des tables de visits .

Il y a aussi une table appelée AppNewRef (ou quelque chose de similaire) qui … attendez-la … contient l'ID d'logging suivant à utiliser pour chacune des autres tables. Ainsi, peut contenir des données telles que: –

 TypeID Description NextRef 1 Employees 804 2 Clients 1708 3 Visits 56783 

Lorsque l'application crée de nouvelles lignes pour les Employees , elle search dans la table AppNewRef , obtient la valeur, utilise cette valeur pour l'ID, puis met à jour la colonne NextRef . Même chose pour les Clients , les Visits et toutes les autres tables dont le NextID à utiliser est stocké ici.

Oui, je sais, pas de colonnes IDENTITY numérotation automatique sur cette database. Tous sous l'excuse de "quand c'était une application Access". Ces identifiants sont conservés dans le code (VB6) en tant que longs. Donc, jusqu'à 2 milliards 147 millions d'loggings possibles. OK, cela semble fonctionner assez bien. (à part le fait que l'application met à jour et s'occupe du locking / mise à jour, etc., et non de la database)

Ainsi, nos users créent assez heureusement des Employees , des Clients , des Visits etc. L' Visits ID augmente régulièrement quelques douzaines à la fois. Alors les problèmes arrivent. Nos clients provoquent des corruptions de database lors de la création de lots de visites, car le server fonctionne correctement et l'application ne répond plus. Donc, ils tuent l'application en utilisant le gestionnaire de tâches au lieu d'être patient et en attente. Certes, l'application semble bloquer.

Au début de cette année, le développeur Tim (nom réel, pas de protection du coupable ici) commence à modifier le code pour faire les mises à jour par étapes, de sorte que l'interface user rest «réactive». Puis Avril arrive, et il travaille son avis (vous pouvez imaginer la scène maintenant, n'est-ce pas?) Et il se prépare à terminer les mises à jour.

Fin avril et début mai, nous mettons à jour certains de nos clients. Au cours des prochains mois, nous mettons à jour de plus en plus d'entre eux.

Invisible par Tim (vrai nom, callbackez-vous) et moi (qui a commencé deux semaines avant le départ de Tim) et l'autre nouveau développeur qui a commencé une semaine après, les ID dans la table de visite commencent à faire des bonds énormes. Par énorme, je veux dire 10000, 20000, 30000 à la fois. Parfois quelques centaines de milliers.

Voici un graphique qui illustre l'augmentation rapide des ID utilisés.

Rouler en novembre. Le client appelle le support technique et signale qu'il reçoit une erreur. Je regarde le message d'erreur et request la database afin que je puisse déboguer le code. Je trouve que la valeur est trop grande pour longtime. Je fais quelques requêtes, tire l'information, la dépose dans Excel et la représente graphiquement.

Je ne pense pas que faire le code gérer quelque chose de plus long que l'ID est la bonne approche, comme cette application passe cet ID dans d'autres DLL et OCX et briser l'interface sur ceux qui semblent juste un monde entier de mal que je ne Je ne veux pas rencontrer maintenant.

Une idée potentielle que j'étudie est d'essayer de modifier les ID afin que je puisse les descendre à un niveau inférieur. Essentiellement combler les lacunes. Utilisation de la fonction ROW_NUMBER

Ce que je pense faire est d'append une nouvelle colonne à chacune des tables qui ont une reference de key étrangère à ces ID de visite (pas un esprit de key étrangère correcte, ces contraintes n'existent pas dans cette database). Cette nouvelle colonne pourrait stocker l'ancienne valeur (actuelle) de l'ID de visite (oh, juste pour confondre les choses, sur certaines tables, elle s'appelle EventID , et sur certains, elle s'appelle VisitID ).

Ensuite, pour chacune des autres tables faisant reference à VisitID , mettez à jour à la nouvelle valeur.

Des idées? Suggestions ? Des extraits de T-SQL pour aider tous avec reconnaissance.

• Cross applique une fonction de valeur table
• Correctement la sécurisation de l'instruction SQL avec les parameters
• Mise à jour SQL Paramètres facultatifs PHP
• Table Valued Constructor Limite maximale des lignes dans Select
• La manière la plus performante dans SQL Server de condenser plusieurs modifications de données dans les valeurs avant et après

 WITH resequenced AS ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS newID, * FROM yourTable ) UPDATE resequenced SET id = newID 

 UPDATE yourTable SET id = -id 

 CREATE TABLE temp AS newID INT IDENTITY (1,1), oldID INT ) INSERT INTO temp (oldID) SELECT id FROM yourTable /* Do this once for the table you are re-identifiering */ /* Repeat this for all fact tables holding that ID as a foreign key */ UPDATE factTable SET foreignID = temp.newID FROM temp WHERE foreignID = temp.oldID 

 select min(number) from visits right join numbers on visits.id = numbers.number