t8y2--dbx/plugins/dialects/sqlserver.yaml

dialect:
  name: "SQL Server"
  display_name: "SQL Server"
  versions:
    - version: "2022"
      status: "RECOMMENDED"
    - version: "2019"
      status: "COMPATIBLE"

types:
  - name: "TINYINT"
    category: "INTEGER"
    precision_range: [0, 255]
  - name: "SMALLINT"
    category: "INTEGER"
    precision_range: [-32768, 32767]
  - name: "INT"
    category: "INTEGER"
    aliases: ["INTEGER"]
  - name: "BIGINT"
    category: "INTEGER"
  - name: "BIT"
    category: "BOOLEAN"
    semantic_fidelity_base: 0.9
  - name: "FLOAT"
    category: "FLOAT"
  - name: "REAL"
    category: "FLOAT"
  - name: "DECIMAL"
    category: "DECIMAL"
    has_precision: true
    aliases: ["NUMERIC"]
  - name: "MONEY"
    category: "DECIMAL"
    semantic_fidelity_base: 0.8
  - name: "SMALLMONEY"
    category: "DECIMAL"
    semantic_fidelity_base: 0.9
  - name: "VARCHAR"
    category: "STRING"
    has_length: true
    max_precision: 8000
  - name: "NVARCHAR"
    category: "STRING"
    has_length: true
    max_precision: 4000
  - name: "CHAR"
    category: "STRING"
    has_length: true
  - name: "NCHAR"
    category: "STRING"
    has_length: true
  - name: "TEXT"
    category: "STRING"
    semantic_fidelity_base: 0.8
  - name: "NTEXT"
    category: "STRING"
    semantic_fidelity_base: 0.7
  - name: "VARBINARY"
    category: "BINARY"
    has_length: true
  - name: "IMAGE"
    category: "BINARY"
    semantic_fidelity_base: 0.8
  - name: "DATE"
    category: "DATE"
  - name: "TIME"
    category: "TIME"
  - name: "DATETIME"
    category: "DATETIME"
  - name: "DATETIME2"
    category: "DATETIME"
    semantic_fidelity_base: 0.9
  - name: "SMALLDATETIME"
    category: "DATETIME"
    semantic_fidelity_base: 0.9
  - name: "DATETIMEOFFSET"
    category: "DATETIME"
    semantic_fidelity_base: 0.8
  - name: "UNIQUEIDENTIFIER"
    category: "UUID"
  - name: "XML"
    category: "XML"
    semantic_fidelity_base: 0.5
  - name: "SQL_VARIANT"
    category: "VARIANT"
    semantic_fidelity_base: 0.2
  - name: "GEOMETRY"
    category: "SPATIAL"
  - name: "GEOGRAPHY"
    category: "SPATIAL"
  - name: "HIERARCHYID"
    category: "SYSTEM"
    semantic_fidelity_base: 0.1

ddl_capabilities:
  add_column: true
  drop_column: true
  rename_column: true
  alter_column_type: true
  comment: true
  create_index: true
  drop_index: true
  rebuild_index: true
  index_type: true
  index_include: true
  index_filter: true
  index_comment: true
  create_table: true
  drop_table: true
  truncate_table: true
  create_trigger: true
  drop_trigger: true
  create_function: true
  drop_function: true
  create_sequence: true
  drop_sequence: true
  alter_owner: true
  grant_revoke: true
  if_not_exists: true
  temporary_table: true
  transactional_ddl: true
  identity_columns: true
  templates:
    add_column: "ALTER TABLE {table} ADD {column} {type}"
    drop_column: "ALTER TABLE {table} DROP COLUMN {column}"
    rename_column: "EXEC sp_rename '{table}.{old}', '{new}', 'COLUMN'"
    modify_column: "ALTER TABLE {table} ALTER COLUMN {column} {type}"
    create_table: "CREATE TABLE {table} ({columns})"
    drop_table: "DROP TABLE IF EXISTS {table}"
    create_index: "CREATE INDEX {name} ON {table} ({columns})"
    drop_index: "DROP INDEX IF EXISTS {name} ON {table}"

rollback_templates:
  add_column: "ALTER TABLE {table} DROP COLUMN {column}"
  drop_column: "ALTER TABLE {table} ADD {column} {original_type}"
  rename_column: "EXEC sp_rename '{table}.{new}', '{old}', 'COLUMN'"
  modify_column: "ALTER TABLE {table} ALTER COLUMN {column} {original_type}"
  create_table: "DROP TABLE IF EXISTS {table}"
  drop_table: "-- Restore from backup: {table}"

online_safety:
  add_column:
    level: "BLOCKING_SHORT"
    cost: "LOW"
  modify_column:
    level: "BLOCKING_LONG"
    cost: "MEDIUM"
  drop_column:
    level: "BLOCKING_LONG"
    cost: "MEDIUM"
  drop_table:
    level: "REQUIRES_GHOST"
    cost: "HIGH"
  truncate:
    level: "REQUIRES_GHOST"
    cost: "HIGH"
  create_index:
    level: "BLOCKING_SHORT"
    cost: "MEDIUM"
  drop_index:
    level: "NON_BLOCKING"
    cost: "LOW"

destruction_level:
  drop_table: "FATAL"
  drop_schema: "FATAL"
  truncate: "DANGEROUS"
  drop_column: "DANGEROUS"
  alter_type: "MODIFY"
  add_column: "SAFE"
  create_table: "SAFE"

structural_capabilities:
  supports_schemas: true
  supports_catalogs: true
  max_columns_per_table: 1024
  max_indexes_per_table: 999
  max_query_size_bytes: 268435456
  max_foreign_key_name_length: 128
  supports_full_text_index: true
  supports_spatial_index: true
  supports_partitioning: true
  supports_table_sampling: true
  supports_on_update_cascade: true
  supports_on_delete_set_null: true
  supports_json_type: true
  supports_uuid_type: true
  supports_sequences: true

identifier_rules:
  quote_char: '"'
  case_sensitive: false
  max_length: 128

metadata_queries:
  list_tables:
    sql: "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}' AND TABLE_TYPE = 'BASE TABLE'"
    performance_profile: "LIGHT"
  list_columns:
    sql: "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{table}' ORDER BY ORDINAL_POSITION"
    performance_profile: "MEDIUM"
  list_indexes:
    sql: "SELECT i.name AS index_name, c.name AS column_name, i.is_unique, i.type_desc FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE OBJECT_NAME(i.object_id) = '{table}'"
    performance_profile: "MEDIUM"
  list_foreign_keys:
    sql: "SELECT fk.name AS constraint_name, c1.name AS column_name, OBJECT_NAME(fk.referenced_object_id) AS ref_table, c2.name AS ref_column FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id WHERE OBJECT_NAME(fk.parent_object_id) = '{table}'"
    performance_profile: "MEDIUM"
  list_triggers:
    sql: "SELECT name, OBJECT_DEFINITION(object_id) AS definition FROM sys.triggers WHERE parent_id = OBJECT_ID('{table}')"
    performance_profile: "MEDIUM"
  list_functions:
    sql: "SELECT name, OBJECT_DEFINITION(object_id) AS definition FROM sys.objects WHERE type IN ('FN', 'IF', 'TF') AND SCHEMA_NAME(schema_id) = '{schema}'"
    performance_profile: "HEAVY"
  dependencies:
    sql: "SELECT OBJECT_NAME(fk.parent_object_id) AS table_name, OBJECT_NAME(fk.referenced_object_id) AS ref_table FROM sys.foreign_keys fk"
    depth_support: 2
    performance_profile: "HEAVY"

pre_execution_checks:
  - name: "lock_timeout"
    sql: "SET LOCK_TIMEOUT 3000"