Gebruik sparse columns en column sets met mssql-python

Sparse columns zijn een Microsoft SQL-opslagoptimalisatie voor NULL-waarden in tabellen met veel nullable kolommen. Clientapplicaties zien spaarzame kolommen als reguliere kolommen. De mssql-python driver leest en schrijft ze zoals elke andere kolom zonder speciale behandeling.

Het meest geschikt voor:

• Tabellen met 20-50%+ NULL-waarden.
• Documentopslag met variabele attributen.
• EAV (Entity-Attribute-Value) patronen.
• Sensorgegevens met veel optionele metingen.

Maak spaarzame kolommen

Definieer de spaarzame kolommen in je tabelschema door de SPARSE NULL modifier toe te voegen aan kolommen die vaak NULL-waarden bevatten.

Basistabel voor schaarse kolommen

Maak een tabel met spaarzame kolommen voor optionele attributen.

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
);

Met kolommenset

Voeg een kolomset toe om XML-toegang te bieden aan alle spaarzame kolommen tegelijk.

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
);

Voeg spaarzame kolomgegevens in

Voeg gegevens in sparse kolommen op naam in, net zoals bij gewone kolommen.

Vul afzonderlijke kolommen in

Voeg producten in waarbij specifieke waarden in sparse kolommen zijn ingevuld.

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()

Invoegen via kolommenset (XML)

Voeg meerdere spaarzame kolomwaarden tegelijk in door XML aan de kolomset door te geven.

# 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()

Dynamische attribuutinvoeging

Maak een functie die dynamische attributen accepteert als woordenboek en de XML automatisch bouwt.

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()

Zoek op spaarzame kolommen

Zoek op schaarse kolommen op naam, of haal alle spaarzame waarden tegelijk op via de kolomset.

Zoek individuele kolommen op

Raadpleeg specifieke spaarzame kolommen met standaard SELECT-syntaxis.

# 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}")

Zoeken via kolommenset

Haal alle spaarzame kolomwaarden als XML op uit de kolomset.

# 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}")

XML-kolommenset parseren in Python

Parse de XML uit de kolomset om deze om te zetten in een Python-woordenboek voor eenvoudigere manipulatie.

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'}

Vraag met SELECT *

Wanneer je SELECT * gebruikt, wordt de kolomset teruggegeven als één enkele XML-kolom in plaats van als afzonderlijke spaarzame kolommen.

# 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]}")

Zoek expliciet individuele kolommen op

Om individuele spaarzame kolommen uit een tabel met een kolomset op te halen, vermeld je ze expliciet in de SELECT-clausule.

# 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}")

Werk de spaarzame kolommen bij

Werk individuele sparse kolommen bij op naam of vervang alle sparse waarden tegelijk via de column set XML.

Werk individuele kolommen bij

Werk specifieke spaarzame kolomwaarden bij met standaard SQL-syntaxis UPDATE .

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

Bijwerken via kolomset

Vervang alle spaarzame kolomwaarden door de kolomset XML direct bij te werken.

# 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

Gedeeltelijke update via kolommenset

Update alleen specifieke spaarzame attributen, terwijl de waarden van andere attributen die niet in de update zijn opgenomen behouden blijven.

# 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()

Dynamische kolompatronen

Bouw flexibele queries die dynamisch zoeken over steile kolommen, waarbij je allowlists gebruikt om kolomnamen te valideren en SQL-injectieaanvallen te voorkomen.

Zoek EAV-achtige gegevens op

Zoek naar producten op een specifieke attribuutnaam en -waarde met behulp van Entity-Attribute-Value (EAV) patroonmatching.

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")

Flexibele attribuutzoektocht

Zoek naar producten die meerdere optionele attributen tegelijk hebben.

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")

Prestatie-overwegingen

Evalueer wanneer spaarzame kolommen opslagvoordelen bieden, beoordeel overheadkosten en gebruik prestatiemonitoring om je structuur van de sparse kolom te optimaliseren.

Wanneer sparse kolommen gebruiken

Goede kandidaten voor schaars rubrieken zijn onder andere:

• Kolommen met meer dan 60-70% NULL-waarden.
• Brede tabellen met veel optionele kolommen.
• Werklasten waarbij opslagoptimalisatie prioriteit heeft.

Vermijd het gebruik van spaarzame kolommen als:

• De meeste rijen hebben waarden (elke niet-NULL waarde voegt 4 bytes overhead toe).
• De kolom wordt vaak gebruikt in WHERE-clausules.
• De kolom maakt deel uit van de geclusterde index.

Controleer opslagbesparingen

Vergelijk de opslaggrootte van de sparse- en niet-sparse-versies van dezelfde tabel.

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

Indexoverwegingen

Je kunt spaarzame kolommen indexeren. Gefilterde indexen werken goed voor schaarse data omdat ze NULL-rijen overslaan:

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

Bulkbewerkingen

Optimaliseer het invoegen van meerdere producten met schaarse kolommen door kolommenset-XML in Python op te bouwen voordat de rijen aan bulkcopy() worden doorgegeven.

Bulksgewijs invoegen met sparse kolommen

Gebruik bulk copy om efficiënt meerdere producten met spaarzame attributen in te voegen.

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()

Beste praktijken

Volg validatiepatronen, gebruik kolomsets voor flexibiliteit en bewaak de mate van sparsiteit van kolommen om ervoor te zorgen dat je ontwerp met sparse kolommen voldoet aan prestatie- en onderhoudsdoelen.

Valideer spaarzame kolomwaarden

Controleer of alle spaarzame attributen overeenkomen met de toegestane set voordat je gegevens invoegt.

# 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})

Gebruik kolomsets voor flexibiliteit

Kolomverzamelingen vereenvoudigen het werken met spaarzame kolommen:

• Voeg nieuwe spaarzame kolommen toe zonder codewijzigingen.
• Dynamische attributen opslaan.
• Automatisch XML serialiseren en deserialiseren.

Zonder een kolomset heb je expliciete kolomlijsten nodig. Met een kolomset behandelt de XML-kolom dynamische attributen automatisch.

Monitor NULL-percentages

Analyseer het NUL-percentage voor een kolom om te bepalen of het een goede kandidaat is voor sparse column-optimalisatie.

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

Verwante onderwerpen

• Gegevenstypetoewijzingen
• XML-gegevens
• Bewerkingen voor bulkkopiëren
• Microsoft SQL sparse columns