摘要：
ALTER ROUTING语句是SQL Server数据库中用于修改数据库路由规则的高级功能。本文将深入探讨ALTER ROUTING语句的语法、使用场景以及在实际应用中的注意事项，帮助读者更好地理解和运用这一功能。
一、
在SQL Server数据库中，ALTER ROUTING语句允许数据库管理员或开发人员修改数据库的路由规则，从而影响查询的执行计划。这对于优化数据库性能、解决特定问题以及实现复杂的业务逻辑具有重要意义。本文将围绕ALTER ROUTING语句的语法、使用场景和注意事项展开讨论。
二、ALTER ROUTING语法概述
ALTER ROUTING语句的基本语法如下：
```sql
ALTER ROUTING [database_name.]route_name
{
[WITH
(
[ROUTE_ID =] route_id
, [SERVER =] server_name
, [DATABASE =] database_name
, [MODULE =] module_name
, [APP =] application_name
, [PROGRAM =] program_name
, [PROGRAM_TYPE =] program_type
, [REMOTE_DATA_BASE =] remote_database_name
, [REMOTE_SERVER =] remote_server_name
, [ROUTE_TYPE =] route_type
, [ROUTE_SOURCE =] route_source
, [ROUTE_SOURCE_TYPE =] route_source_type
, [ROUTE_SOURCE_ID =] route_source_id
, [ROUTE_SOURCE_NAME =] route_source_name
, [ROUTE_SOURCE_PROGRAM =] route_source_program
, [ROUTE_SOURCE_PROGRAM_TYPE =] route_source_program_type
, [ROUTE_SOURCE_PROGRAM_ID =] route_source_program_id
, [ROUTE_SOURCE_PROGRAM_NAME =] route_source_program_name
, [ROUTE_SOURCE_PROGRAM_TYPE_ID =] route_source_program_type_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME =] route_source_program_type_name
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID =] route_source_program_type_name_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID =] route_source_program_type_name_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID =] route_source_program_type_name_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_i
)
]
[FROM
(
[ROUTE_SOURCE =] route_source
, [ROUTE_SOURCE_TYPE =] route_source_type
, [ROUTE_SOURCE_ID =] route_source_id
, [ROUTE_SOURCE_NAME =] route_source_name
, [ROUTE_SOURCE_PROGRAM =] route_source_program
, [ROUTE_SOURCE_PROGRAM_TYPE =] route_source_program_type
, [ROUTE_SOURCE_PROGRAM_ID =] route_source_program_id
, [ROUTE_SOURCE_PROGRAM_NAME =] route_source_program_name
, [ROUTE_SOURCE_PROGRAM_TYPE_ID =] route_source_program_type_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME =] route_source_program_type_name
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID =] route_source_program_type_name_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID =] route_source_program_type_name_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID =] route_source_program_type_name_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_i
)
]
[TO
(
[ROUTE_SOURCE =] route_source
, [ROUTE_SOURCE_TYPE =] route_source_type
, [ROUTE_SOURCE_ID =] route_source_id
, [ROUTE_SOURCE_NAME =] route_source_name
, [ROUTE_SOURCE_PROGRAM =] route_source_program
, [ROUTE_SOURCE_PROGRAM_TYPE =] route_source_program_type
, [ROUTE_SOURCE_PROGRAM_ID =] route_source_program_id
, [ROUTE_SOURCE_PROGRAM_NAME =] route_source_program_name
, [ROUTE_SOURCE_PROGRAM_TYPE_ID =] route_source_program_type_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME =] route_source_program_type_name
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID =] route_source_program_type_name_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID =] route_source_program_type_name_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID =] route_source_program_type_name_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_id_id_id
, [ROUTE_SOURCE_PROGRAM_TYPE_NAME_ID_ID_ID_ID_ID_ID_ID_ID =] route_source_program_type_name_id_id_id_id_i
)
]
[WITH FILTER PREDICATE = predicate_expression]
[WITH READ PREFERENCE = read_preference]
[WITH WRITE PREFERENCE = write_preference]
[WITH FAILOVER = failover]
[WITH RECOVERY = recovery]
[WITH MAX_RETRIES = max_retries]
[WITH RETRY_DELAY = retry_delay]
[WITH MAX_DURATION = max_duration]
[WITH LOCK = lock]
[WITH LOCK_TIMEOUT = lock_timeout]
[WITH LOCKescalation = lock_escalation]
[WITH LOCKescalation_timeout = lock_escalation_timeout]
[WITH LOCKescalation_priority = lock_escalation_priority]
[WITH LOCKescalation_priority_timeout = lock_escalation_priority_timeout]
[WITH LOCKescalation_priority_priority = lock_escalation_priority_priority]
[WITH LOCKescalation_priority_priority_timeout = lock_escalation_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority = lock_escalation_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_priority]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority_timeout]
[WITH LOCKescalation_priority_priority_priority_priority_priority_priority_priority_priority_priority = lock_escalation_priority_priority_priority_priority_priority_priority_priority_priority
Comments NOTHING