Utilize colunas esparsas e conjuntos de colunas com o mssql-python

Colunas esparsas são uma otimização de armazenamento Microsoft SQL para valores NULL em tabelas com muitas colunas anuláveis. As aplicações cliente veem colunas esparsas como colunas regulares. O driver mssql-python lê e escreve como qualquer outra coluna, sem tratamento especial.

Feature	Descrição
Colunas esparsas	Os valores NULL utilizam armazenamento zero
Conjuntos de colunas	Representação XML de todas as colunas esparsas
Mesas amplas	Suporte para até 30 000 colunas

Note

As colunas esparsas são uma funcionalidade do servidor. O driver mssql-python não requer nenhuma configuração ou API especial para funcionar com colunas esparsas. A única diferença visível para o cliente: quando usas conjuntos de colunas, eles retornam uma representação XML dos valores das colunas esparsos.

Mais indicado para:

Tabelas com 20-50% ou mais de valores NULL.
Armazenamento de documentos com atributos variáveis.
Padrões EAV (Entidade-Atributo-Valor).
Dados do sensor com muitas leituras opcionais.

Criar colunas esparsas

Defina colunas esparsas no seu esquema de tabela adicionando o SPARSE NULL modificador a colunas que frequentemente conterão valores NULL.

Tabela básica de colunas esparsas

Cria uma tabela com colunas esparsas para atributos opcionais.

CREATE TABLE ProductAttributes (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Sparse columns for optional attributes
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Com conjunto de colunas definido

Adicione um conjunto de colunas para fornecer acesso XML a todas as colunas esparsas simultaneamente.

CREATE TABLE ProductAttributesWithSet (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Column set provides XML access to all sparse columns
    SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    -- Sparse columns
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Inserir dados esparsos na coluna

Insira em colunas esparsas por nome, tal como faria nas colunas normais.

Inserir colunas individuais

Inserir produtos com valores específicos preenchidos em colunas esparsas.

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create the table with sparse columns
cursor.execute("DROP TABLE IF EXISTS ProductAttributes")
cursor.execute("""
    CREATE TABLE ProductAttributes (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert with some sparse columns populated
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Color, Size)
    VALUES (%(id)s, %(name)s, %(color)s, %(size)s)
""", {"id": 1, "name": "T-Shirt", "color": "Blue", "size": "Large"})

# Insert with different sparse columns
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Weight, Material)
    VALUES (%(id)s, %(name)s, %(weight)s, %(material)s)
""", {"id": 2, "name": "Coffee Mug", "weight": 0.35, "material": "Ceramic"})

conn.commit()

Inserir através do conjunto de colunas (XML)

Insira múltiplos valores de coluna esparsos ao mesmo tempo, passando XML para o conjunto de colunas.

# Create the table with a column set
cursor.execute("DROP TABLE IF EXISTS ProductAttributesWithSet")
cursor.execute("""
    CREATE TABLE ProductAttributesWithSet (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert using column set XML
cursor.execute("""
    INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
    VALUES (%(id)s, %(name)s, %(xml)s)
""", {
    "id": 3,
    "name": "Laptop Bag",
    "xml": "<Color>Black</Color><Size>Medium</Size><Material>Nylon</Material><Warranty>24</Warranty>"
})
conn.commit()

Inserção dinâmica de atributos

Crie uma função que aceite atributos dinâmicos como dicionário e construa o XML automaticamente.

def insert_with_attributes(cursor, product_id: int, name: str, attributes: dict):
    """Insert product with dynamic sparse column attributes."""
    # Build XML for column set
    xml_parts = [f"<{key}>{value}</{key}>" for key, value in attributes.items()]
    attributes_xml = "".join(xml_parts)
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": attributes_xml or None})

# Usage
insert_with_attributes(cursor, 4, "Headphones", {
    "Color": "Silver",
    "Warranty": 12,
    "Manufacturer": "AudioTech"
})
conn.commit()

Consultar colunas esparsas

Consulte as colunas esparsas por nome, ou recupere todos os valores esparsos de uma só vez através do conjunto de colunas.

Consultar colunas individuais

Consulta colunas esparsas específicas usando a sintaxe padrão SELECT.

# Query specific sparse columns
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size
    FROM ProductAttributes
    WHERE Color IS NOT NULL
""")

for row in cursor:
    print(f"{row.ProductName}: {row.Color}, {row.Size}")

Consulta por conjunto de colunas

Obtenha todos os valores de todas as colunas esparsas como XML a partir do conjunto de colunas.

# Get column set XML
cursor.execute("""
    SELECT ProductID, ProductName, SparseAttributes
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
print(f"Product: {row.ProductName}")
print(f"Attributes XML: {row.SparseAttributes}")

Analisar XML de conjunto de colunas em Python

Analise o XML do conjunto de colunas para o converter num dicionário Python para facilitar a manipulação.

from xml.etree import ElementTree as ET

def get_product_attributes(cursor, product_id: int) -> dict:
    """Get product with parsed sparse attributes."""
    cursor.execute("""
        SELECT ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        return None
    
    result = {"ProductName": row.ProductName}
    
    # Parse XML column set
    if row.SparseAttributes:
        # Wrap in root element for parsing
        xml_str = f"<root>{row.SparseAttributes}</root>"
        root = ET.fromstring(xml_str)
        
        for elem in root:
            result[elem.tag] = elem.text
    
    return result

# Usage
product = get_product_attributes(cursor, 3)
print(product)
# {'ProductName': 'Laptop Bag', 'Color': 'Black', 'Size': 'Medium', 'Material': 'Nylon', 'Warranty': '24'}

Consulta com SELECT *

Quando usas SELECT *, o conjunto de colunas devolve como uma única coluna XML em vez de colunas esparsas individuais.

# SELECT * returns column set instead of individual sparse columns
cursor.execute("""
    SELECT * FROM ProductAttributesWithSet WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
# Returns: ProductID, ProductName, SparseAttributes (not individual columns)
print(f"Columns: {[col[0] for col in cursor.description]}")

Consultar explicitamente colunas individuais

Para recuperar colunas esparsas individuais de uma tabela com um conjunto de colunas, liste-as explicitamente na cláusula SELECT.

# To get individual sparse columns with column set table, list them explicitly
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size, Weight, Material, Warranty, Manufacturer
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

# Now each sparse column is available as separate property
row = cursor.fetchone()
print(f"Color: {row.Color}, Material: {row.Material}")

Atualizar colunas esparsas

Atualize as colunas esparsas individuais pelo nome ou substitua todos os valores esparsos de uma só vez através do conjunto de colunas XML.

Atualizar colunas individuais

Atualize valores específicos de colunas esparsas usando a sintaxe SQL UPDATE padrão.

cursor.execute("""
    UPDATE ProductAttributes
    SET Color = %(color)s, Weight = %(weight)s
    WHERE ProductID = %(id)s
""", {"id": 1, "color": "Red", "weight": 0.2})
conn.commit()

Atualização por conjunto de colunas

Substitua todos os valores de colunas esparsos atualizando diretamente o conjunto de colunas em XML.

# Replace all sparse column values via column set
cursor.execute("""
    UPDATE ProductAttributesWithSet
    SET SparseAttributes = %(xml)s
    WHERE ProductID = %(id)s
""", {
    "id": 3,
    "xml": "<Color>Navy</Color><Size>Large</Size><Material>Leather</Material>"
})
conn.commit()
# Note: This clears any sparse columns not included in the XML

Atualização parcial através do conjunto de colunas

Atualize apenas atributos esparsos específicos, preservando os valores de outros atributos não incluídos na atualização.

# To update only specific attributes, merge with existing
def update_attributes(cursor, product_id: int, updates: dict):
    """Update specific sparse attributes while preserving others."""
    # Get current attributes
    cursor.execute("""
        SELECT Color, Size, Weight, Material, Warranty, Manufacturer
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        raise ValueError(f"Product {product_id} not found")
    
    # Merge updates
    current = {
        "Color": row.Color,
        "Size": row.Size,
        "Weight": row.Weight,
        "Material": row.Material,
        "Warranty": row.Warranty,
        "Manufacturer": row.Manufacturer
    }
    
    for key, value in updates.items():
        current[key] = value
    
    # Build XML with non-null values
    xml_parts = []
    for key, value in current.items():
        if value is not None:
            xml_parts.append(f"<{key}>{value}</{key}>")
    
    cursor.execute("""
        UPDATE ProductAttributesWithSet
        SET SparseAttributes = %(xml)s
        WHERE ProductID = %(id)s
    """, {"id": product_id, "xml": "".join(xml_parts) or None})

# Usage
update_attributes(cursor, 3, {"Color": "Brown", "Warranty": 36})
conn.commit()

Padrões dinâmicos de colunas

Construa consultas flexíveis que procurem dinamicamente em colunas esparsas, usando listas de permissões para validar nomes de colunas e prevenir ataques de injeção SQL.

Consultar dados ao estilo EAV

Pesquise produtos por um nome e um valor de atributo específicos, utilizando correspondência de padrões Entidade-Atributo-Valor (EAV).

def find_products_by_attribute(cursor, attribute_name: str, attribute_value: str) -> list:
    """Find products with specific attribute value."""
    # Validate column name against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    if attribute_name not in allowed_columns:
        raise ValueError(f"Invalid attribute: {attribute_name}")
    
    cursor.execute(f"""
        SELECT ProductID, ProductName, {attribute_name}
        FROM ProductAttributesWithSet
        WHERE {attribute_name} = %(value)s
    """, {"value": attribute_value})
    
    return cursor.fetchall()

# Find all blue products
blue_products = find_products_by_attribute(cursor, "Color", "Blue")

Pesquisa flexível de atributos

Procure produtos com múltiplos atributos opcionais ao mesmo tempo.

def search_by_attributes(cursor, **attributes) -> list:
    """Search products by multiple optional attributes."""
    # Validate column names against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    invalid = set(attributes.keys()) - allowed_columns
    if invalid:
        raise ValueError(f"Invalid attributes: {invalid}")
    
    conditions = ["1=1"]  # Always true base condition
    params = {}
    
    for i, (key, value) in enumerate(attributes.items()):
        if value is not None:
            conditions.append(f"{key} = %(attr_{i})s")
            params[f"attr_{i}"] = value
    
    query = f"""
        SELECT ProductID, ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE {' AND '.join(conditions)}
    """
    
    cursor.execute(query, params)
    return cursor.fetchall()

# Search by multiple attributes
results = search_by_attributes(cursor, Color="Black", Material="Nylon")

Considerações sobre desempenho

Avalie quando colunas esparsas proporcionam benefícios de armazenamento, avalie os custos indiretos e utilize a monitorização de desempenho para otimizar o design das colunas esparsas.

Quando usar colunas esparsas

Bons candidatos para colunas esparsas incluem:

Colunas com mais de 60-70% de valores NULL.
Tabelas largas com muitas colunas opcionais.
Cargas de trabalho onde a otimização do armazenamento é uma prioridade.

Evite usar colunas esparsas quando:

A maioria das linhas tem valores (cada valor não NULL adiciona 4 bytes de sobrecarga).
A coluna é frequentemente usada nas cláusulas WHERE.
A coluna faz parte do índice agrupado.

Verifique as poupanças de armazenamento

Compare o tamanho de armazenamento das versões esparsas e não esparsas da mesma tabela.

-- Compare storage with and without sparse
EXEC sp_spaceused 'ProductAttributes';
EXEC sp_spaceused 'ProductAttributesWithoutSparse';

Considerações sobre o índice

Podes indexar colunas esparsas. Índices filtrados funcionam bem para dados esparsos porque saltam linhas NULLA:

CREATE INDEX IX_Products_Color
ON ProductAttributes(Color)
WHERE Color IS NOT NULL;

Operações em massa

Otimize as inserções de múltiplos produtos com colunas esparsas construindo um conjunto de colunas XML em Python antes de passar as linhas para bulkcopy().

Inserção em lote com colunas dispersas

Use cópia em massa para inserir eficientemente múltiplos produtos com atributos esparsos.

def bulk_insert_with_attributes(conn, products: list[dict]):
    """Bulk insert products with sparse attributes."""
    rows = []
    for product in products:
        attrs = product.get("attributes", {})
        xml = "".join(f"<{k}>{v}</{k}>" for k, v in attrs.items()) or None
        rows.append((product["id"], product["name"], xml))
    
    cursor = conn.cursor()
    result = cursor.bulkcopy("ProductAttributesWithSet", rows)
    return result["rows_copied"]

# Usage
products = [
    {"id": 100, "name": "Widget A", "attributes": {"Color": "Red", "Size": "Small"}},
    {"id": 101, "name": "Widget B", "attributes": {"Weight": 1.5, "Material": "Steel"}},
    {"id": 102, "name": "Widget C", "attributes": {}},  # No sparse attributes
]
bulk_insert_with_attributes(conn, products)
conn.commit()

Melhores práticas

Siga padrões de validação, utilize conjuntos de colunas para maior flexibilidade e monitorize a escassez das colunas para garantir que o seu design de colunas esparso cumpre os objetivos de desempenho e manutenção.

Validar valores de colunas esparsas

Valide que todos os atributos esparsos correspondem ao conjunto permitido antes de inserir os dados.

# Sparse columns have the same constraints as regular columns
# The SPARSE keyword only affects storage

def validate_and_insert(cursor, product_id: int, name: str, attributes: dict):
    """Insert with validation."""
    allowed_attributes = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    
    invalid = set(attributes.keys()) - allowed_attributes
    if invalid:
        raise ValueError(f"Unknown attributes: {invalid}")
    
    xml_parts = [f"<{k}>{v}</{k}>" for k, v in attributes.items()]
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": "".join(xml_parts) or None})

Use conjuntos de colunas para maior flexibilidade

Os conjuntos de colunas simplificam o trabalho com colunas esparsas:

Adicione novas colunas esparsas sem alterações no código.
Armazenar atributos dinâmicos.
Serializa e desserializa XML automaticamente.

Sem um conjunto de colunas, são necessárias listas explícitas de colunas. Com um conjunto de colunas, a coluna XML gere automaticamente os atributos dinâmicos.

Monitorizar percentagens de NULL

Analise a percentagem de NULL de uma coluna para determinar se é uma boa candidata para otimização de colunas esparsas.

def analyze_sparseness(cursor, table: str, column: str) -> float:
    """Check if column is a good sparse candidate."""
    # Validate identifiers to prevent SQL injection
    import re
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', column):
        raise ValueError(f"Invalid column name: {column}")

    cursor.execute(f"""
        SELECT 
            COUNT(*) AS TotalRows,
            SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END) AS NullRows
        FROM {table}
    """)
    
    row = cursor.fetchone()
    null_percentage = (row.NullRows / row.TotalRows * 100) if row.TotalRows > 0 else 0
    
    print(f"Column {column}: {null_percentage:.1f}% NULL")
    print(f"Recommendation: {'Good sparse candidate' if null_percentage > 60 else 'Keep as regular column'}")
    
    return null_percentage

Comentários

Esta página foi útil?

Last updated on 2026-07-22