Comment sélectionner efficacement des lignes dans SQL Server au-delà de la limite de 7374 lignes ?

Sélection de lignes dans SQL Server : gestion de grands nombres au-delà de 7 374 lignes

Vous pouvez rencontrer une limitation lorsque vous tentez de récupérer des lignes à l'aide de la fonction ROW_NUMBER de la table sys.all_columns . À partir de 7 374 lignes, la requête se termine brusquement. En effet, vous avez atteint le nombre maximum de lignes disponibles dans sys.all_columns.

CTE en cascade pour générer de grands nombres

Pour surmonter cette limitation, vous pouvez tirer parti de la cascade CTE pour créer une « Table de nombres » ou une « Table de pointage ». Cette approche est considérée comme la méthode la plus efficace pour générer un tableau d’entiers séquentiels. L'extrait suivant démontre :

DECLARE @Range AS INT = 7374

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)
SELECT * FROM CteTally

Explication

Le CTE E1 sert de point de départ avec dix lignes. Les CTE suivants (E2, E4, etc.) effectuent des jointures croisées pour multiplier le nombre de lignes par 10. Cette approche en cascade vous permet de générer efficacement un grand nombre de lignes.

Évitement infini des récursions

Pour éviter une erreur liée à la récursion maximale, vous pouvez spécifier une profondeur de récursion maximale de 0 dans les Options clause :

OPTION (MAXRECURSION 0)

Cela demande à l'optimiseur de requêtes d'utiliser la profondeur nécessaire pour terminer l'exécution sans limitation.

Considérations sur les performances

While l'approche récursive est simple, elle peut être plus lente pour certains scénarios. Pour une solution plus optimisée, envisagez d'utiliser d'autres méthodes telles que les suivantes :

• Fonction NUMERIC_TABLE() (Azure SQL Database et SQL Server 2022 et versions ultérieures)
• Procédure stockée récursive (SQL Server 2000 et versions ultérieures)
• Colonne d'identité avec graine et incrément (SQL Server 2005 et plus tard)

Conclusion

En tirant parti de la technique CTE en cascade ou en explorant des méthodes alternatives, vous pouvez sélectionner efficacement un grand nombre de lignes dans SQL Server, dépassant les limites rencontré avec la table sys.all_columns.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!