Discussion :

[StringBuilder]

Bonjour,

J'ai une table qui a été créée avec ce script :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE [infocentre].[contacts](
	[statut] [tinyint] not null,
	[nom] [nvarchar](80) NOT NULL,
	[prenom] [nvarchar](40) NULL,
	[email] [nvarchar](80) NULL,
	[telephone] [nvarchar](40) NULL,
	[mobile] [nvarchar](40) NULL,
	[datemodif] date NOT NULL,
	[codeclient] [nvarchar](80) NOT NULL,
	[codecrm] [bigint] NOT NULL,
	[id_contactcrm] [numeric](27, 0) NOT NULL,
	primary key (statut, id_contactcrm, codeclient)
) ON [PRIMARY]
 
GO

Dedans, j'ai 8 millions de lignes.
Statut contient les valeurs de 1 à 8, réparties de manière homogène (c'est l'historique glissant du contenu d'une table, statut indiquant l'âge de l'historique).

Suite à un problème d'interface, j'ai besoin de revenir en arrière.

Je lance donc cette commande :

Code sql :

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

update infocentre.activites set statut = statut - 3;

Ca tourne un moment, et me dit que ça a mis à jour 8 millions de lignes.

Je lance ensuite :

Code sql :

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

delete infocentre.activites where statut <= 3;

=> 0 lignes lises à jour ! WTF!?

Je lance pour vérifier :

Code sql :

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

select distinct statut from infocentre.activites;

Et au lieu d'avoir :
-2
-1
0
1
2
3
4
5

J'ai toujours les valeurs de 1 à 8...

Je veux bien que tinyint soit éventuellement unsigned, mais à ce moment :
- soit ça aurait du planter
- soit repartir à 2^16 à la place des valeurs négative

Pourtant, je n'ai pas eu le moindre message d'erreur... Comment se fait-ce ?

Bon, je m'en suis sorti en faisant :

Code sql :

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

1
2
3
4
5
 
delete infocentre.activites where statut <= 3;
 
update infocentre.activites set statut = statut - 3;
select distinct statut from infocentre.activites;

Mais j'aimerais quand même comprendre ce qui c'est passé avec mon premier update ???

[AaâÂäÄàAaâÂäÄàAaâÂäÄ]

Y a-t'il un déclencheur sur cette table ?

[escartefigue]

Est-ce que tout simplement il n'y aurai pas eu un problème de COMMIT lors de la première mise à jour ?

Et, en marge de cette discussion, la PK de cette table est loin d'être optimale, elle commence par une colonne ne pouvant prendre que 8 valeurs (donc peu filtrante), puis une colonne décimale(27,0), puis... du nvarchar(80)

[StringBuilder]

Non, pas de trigger.

Pour le commit, pourquoi pas, mais j'ai lancé dans SSMS et je n'ai pas eu de message d'erreur, donc bizarre qu'il ait fait un rollback.

On va dire que j'avais pas les yeux en face des trous

Pour ce qui est de la clé primaire, c'est qu'une table tampon : je m'en sers pour stocker quelques jours d'historique, et j'ai une vue qui permet d'identifier les création/modifications/suppression entre la génération 1 et la génération 2.
Ca sert dans un traitement qui tourne une fois par jour. En fait le PK sert surtout de contrainte unique dans mon cas.

D'ailleurs, une question bête... vu que je mets à jour la colonne status chaque jour, est-ce que sa position dans la PK (par défaut clustered) a un impact sur le temps de mise à jour ?

[Michel.Priori]

Bonsoir,

Dans le même esprit que la vérification de la présence d'un trigger, cette table a t'elle été partitionnée par la suite ?

Pour ce qui est de la clé primaire, c'est qu'une table tampon : je m'en sers pour stocker quelques jours d'historique, et j'ai une vue qui permet d'identifier les création/modifications/suppression entre la génération 1 et la génération 2.

La fonctionnalité de CDC fait ça très bien mais sur une notion de période de rétention et non pas de génération.
A voir si c'est applicable ici ...

Ca sert dans un traitement qui tourne une fois par jour. En fait le PK sert surtout de contrainte unique dans mon cas.
D'ailleurs, une question bête... vu que je mets à jour la colonne status chaque jour, est-ce que sa position dans la PK (par défaut clustered) a un impact sur le temps de mise à jour ?

Une Pk = 3 contraintes (not null, unique, 1 seule Pk par table)
une Pk repose sur un index Btree pour gérer l'unicité => oui l'ordre des colonnes est important
Choisir les colonnes les plus stables et "séquensables" en premier (sinon, migration de ligne) et parmi celles-ci les plus discriminantes d'abord
A ce sujet les colonnes de type uniqueidentifier avec comme valeur par défaut new_id() est une catastrophe du fait des migrations de lignes

Ici, il me semble que :
* id_contactcrm a toutes les chances d'être une reprise de valeurs d'un SGDB externe, donc unique par nature. vrai ?
* exiger l'unicité du triplet de colonne n'a pas beaucoup de sens
- (statut, id_contactcrm, codeclient) : si on met à jour id_contactcrm ou codeclient quel est le statut à donner ?
+ ajouter une colonne id bigint identity en pk et une colonne version_antérieure FK vers ID (avec on delete set null). ça fait du récursif mais c'est tout terrain

[escartefigue]

En fait le PK sert surtout de contrainte unique dans mon cas.

D'ailleurs, une question bête... vu que je mets à jour la colonne status chaque jour, est-ce que sa position dans la PK (par défaut clustered) a un impact sur le temps de mise à jour ?

Dans ce cas, il est nettement préférable de positionner une contrainte unique si besoin, et d'ajouter un identifiant technique stable comme PK.
D'une part, comme dit précédemment, la première colonne est insuffisamment filtrante pour être retenue comme critère de recherche à elle seule.
D'autre part, comme la valeur est modifiée quotidiennement, l'index cluster est désorganisé en permanence, ce qui pénalise toutes^* les recherches par index, voire, les compromet dans ce cas précis, puisque les modifs se font en masse !
* toutes : car pour SQL server, tous les index passent par l'index cluster, ce n'est pas le cas de tous les SGBD.

La PK doit toujours être une colonne ou un groupe de colonnes stables, c'est la raison pour laquelle on choisit des identifiants techniques, dépourvus de sens, gage de stabilité. Le plus simple est d'utiliser une colonne de type IDENTITY, comme ça c'est le SGBD qui se charge d'alimenter sa valeur.

Une Pk = 3 contraintes (not null, unique, 1 seule Pk par table)

Et aussi : irréductible (sinon c'est une sur-clef, pas une PK) et, ce n'est pas obligatoire, mais très fortement recommandé, stable