Quand MS-SQL gère-t-il les index de table?

Par souci d'arguments, disons que c'est pour SQL 2005/8. Je comprends que lorsque vous placez des index sur une table pour régler des SELECT , ces index doivent être conservés pendant les actions INSERT / UPDATE / DELETE .

Ma question principale est la suivante:

Quand SQL Server conservera-t-il les index d'une table?

J'ai beaucoup de questions suivantes:

Je suppose naïvement qu'il le fera après l'exécution d'une command. Dites que vous insérez 20 lignes, il conservera l'index après que 20 lignes ont été insérées et validées.

Que se passe-t-il dans le cas où un script comporte plusieurs instructions sur une table, mais sont des instructions distinctes?
Le server a-t-il l'intelligence de maintenir l'index après l'exécution de toutes les instructions ou le fait-il par instruction?

J'ai vu des situations où les index sont supprimés et recréés après de grandes / nombreuses actions INSERT / UPDATE .

Cela suppose vraisemblablement la reconstruction des index de la table entière même si vous ne changez qu'une poignée de lignes?
Y aurait-il un avantage de performance à essayer de regrouper les actions INSERT et UPDATE dans un plus grand lot, disons en collectant des lignes à insert dans une table temporaire, plutôt que de faire beaucoup d'insertions plus petites?
Comment l'assemblage des rangées ci-dessus se compare-t-il à la suppression d'un index plutôt qu'à la prise en charge de la maintenance?

Désolé pour la prolifération des questions – c'est quelque chose dont j'ai toujours été conscient, mais lorsque j'essaie d'ajuster un script pour get un équilibre, je ne sais pas quand la maintenance de l'index se produit.

Edit: Je comprends que les questions de performance dépendent en grande partie de la quantité de données lors de l'insertion / mise à jour et du nombre d'index. Encore une fois pour des raisons d'intérêt, j'aurais deux situations:

Une table lourde d'index accordée pour les sélections.
Une table de lumière d'index (PK).

Les deux situations ont un grand nombre d'insertions / mises à jour, disons 10k + lignes.

Edit 2: Je suis conscient de pouvoir profiler un script donné sur un set de données. Cependant, le profilage ne me dit pas pourquoi une approche donnée est plus rapide qu'une autre. Je suis plus intéressé par la théorie derrière les indices et où les problèmes de performance découlent, pas une réponse définitive "c'est plus rapide que cela".

Merci.

Lorsque votre déclaration (même pas transaction) est terminée, tous vos index sont à jour. Lorsque vous vous engagez, tous les changements deviennent permanents et tous les verrous sont libérés. Faire autrement ne serait pas une «intelligence», cela violerait l'intégrité et pourrait causer des erreurs.

Edit: par "intégrité" Je veux dire ceci: une fois validées, datatables devraient être immédiatement disponibles pour tout le monde. Si les index ne sont pas à jour à ce moment, quelqu'un peut get des résultats incorrects.

À mesure que vous augmentez la taille du lot, votre performance s'améliore à l'origine, puis elle ralentit. Vous devez exécuter vos propres tests et déterminer votre taille de lot optimale. De même, vous devez referencer pour déterminer s'il est plus rapide de supprimer / recréer des index ou non.

Edit: si vous insérez / mettez à jour / supprimez des lots de lignes dans une instruction, vos index sont modifiés une fois par instruction. Le script suivant montre que:

 CREATE TABLE dbo.Num(n INT NOT NULL PRIMARY KEY); GO INSERT INTO dbo.Num(n) SELECT 0 UNION ALL SELECT 1; GO -- 0 updates to 1, 1 updates to 0 UPDATE dbo.Num SET n = 1-n; GO -- doing it row by row would fail no matter how you do it UPDATE dbo.Num SET n = 1-n WHERE n=0; UPDATE dbo.Num SET n = 1-n WHERE n=1;