Používejte řídké sloupce a sady sloupců s mssql-python

Řídké sloupce jsou optimalizace úložiště v Microsoft SQL pro hodnoty NULL v tabulkách s mnoha sloupci s nulovatelnými hodnotami. Klientské aplikace vidí řídké sloupce jako pravidelné sloupce. Ovladač mssql-python je čte a zapisuje jako jakýkoli jiný sloupec bez speciálního zpracování.

funkce	Popis
Řídké sloupce	NULL hodnoty používají nulovou paměť
Sady sloupců	XML reprezentace všech řídkých sloupců
Široké tabulky	Podpora až 30 000 sloupců

Note

Řídké sloupce jsou funkcí na straně serveru. Ovladač mssql-python nevyžaduje žádnou speciální konfiguraci ani API pro práci se řídkými sloupci. Jediný rozdíl viditelný klientem je, že když použijete sady sloupců, vracejí XML reprezentaci řídkých sloupcových hodnot.

Nejlépe se hodí pro:

Tabulky s 20–50 % a více hodnotami NULL.
Ukládání dokumentů s proměnnými atributy.
Vzory návrhu EAV (Entity-Attribute-Value).
Data ze senzorů s mnoha volitelnými hodnotami.

Vytvořte řídké sloupce

Definujte řídké sloupce ve schématu tabulky přidáním modifikátoru SPARSE NULL do sloupců, které často obsahují hodnoty NULL.

Základní tabulka řídkých sloupců

Vytvořte tabulku s řídkými sloupci pro volitelné atributy.

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

Se sadou sloupců

Přidejte sadu sloupců, která umožní XML přístup ke všem řídkým sloupcům současně.

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

Vložte data do řídkého sloupce

Vkládejte do řídkých sloupců podle názvu, stejně jako byste to dělali u běžných sloupců.

Vložte jednotlivé sloupce

Vložte produkty s vyplněnými hodnotami v konkrétních řídkých sloupcích.

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

Vložit prostřednictvím sady sloupců (XML)

Vložte najednou hodnoty více řídkých sloupců předáním XML sadě sloupců.

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

Dynamické vkládání atributů

Vytvořte funkci, která přijímá dynamické atributy jako slovník a automaticky sestavuje XML.

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

Dotazování na řídké sloupce

Dotazujte řídké sloupce podle jména, nebo získávejte všechny řídké hodnoty najednou přes množinu sloupců.

Dotazujte jednotlivé sloupce

Dotazujte konkrétní řídké sloupce pomocí standardní syntaxe 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}")

Dotaz přes množinu sloupců

Získejte všechny řídké sloupcové hodnoty jako XML ze sloupcové množiny.

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

Parsování XML sady sloupců v Pythonu

Rozpracujte XML ze sady sloupců a převeďte ho do Python slovníku pro snadnější manipulaci.

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

Dotaz pomocí SELECT *

Když použijete SELECT *, sada sloupců se vrátí jako jeden XML sloupec místo jednotlivých řídkých sloupců.

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

Dotazujte se na jednotlivé sloupce explicitně

Pro získání jednotlivých řídkých sloupců z tabulky s množinou sloupců je explicitně uveďte v klauzuli 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}")

Aktualizovat řídké sloupce

Aktualizujte jednotlivé řídké sloupce podle názvu nebo nahraďte všechny řídké hodnoty najednou pomocí XML pro sadu sloupců.

Aktualizovat jednotlivé sloupce

Aktualizujte specifické hodnoty řídkých sloupců pomocí standardní UPDATE SQL syntaxe.

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

Aktualizace pomocí sady sloupců

Všechny řídké hodnoty sloupců nahraďte přímou aktualizací XML pro sadu sloupců.

# 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

Částečná aktualizace pomocí množiny sloupců

Aktualizujte pouze specifické řídké atributy a zachovávejte hodnoty ostatních atributů, které nejsou v aktualizaci zahrnuty.

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

Dynamické sloupcové vzory

Budujte flexibilní dotazy, které dynamicky prohledávají řídké sloupce, využívají allowlisty k ověřování názvů sloupců a prevenci útoků SQL injection.

Dotazování na data ve stylu EAV

Vyhledávání produktů podle konkrétního názvu atributu a hodnoty pomocí vzorového párování Entity-Attribute-Value (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")

Flexibilní vyhledávání atributů

Hledejte produkty, které odpovídají více volitelným atributům najednou.

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

Důležité informace o výkonu

Zhodnoťte, kdy řídké sloupy přinášejí výhody skladování, zhodnoťte režijní náklady a použijte monitorování výkonu k optimalizaci návrhu řídkých sloupů.

Kdy použít řídké sloupce

Mezi dobré kandidáty na řídké sloupky patří:

Sloupce s více než 60–70 % hodnot NULL.
Široké stoly s mnoha volitelnými sloupci.
Pracovní zátěže, kde je prioritou optimalizace úložiště.

Vyhněte se používání řídkých sloupců, když:

Většina řádků obsahuje hodnoty (každá hodnota jiná než NULL přidává 4 bajty režie).
Sloupec se často používá v klauzulích WHERE.
Sloupec je součástí shlukovaného indexu.

Zkontrolujte úsporu místa v úložišti

Porovnejte velikost úložiště řídkých a neřídkých verzí téže tabulky.

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

Úvahy o indexu

Můžete indexovat řídké sloupce. Filtrované indexy dobře fungují pro řídká data, protože přeskakují NULL řádky:

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

Hromadné operace

Optimalizujte vklady více produktů s řídkými sloupci vytvořením XML sady sloupců v Pythonu před předáním řádků do bulkcopy().

Hromadná vložka s řídkými sloupci

Použijte hromadné kopírování k efektivnímu vložení více produktů s řídkými atributy.

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

Osvědčené postupy

Řiďte se validačními vzory, používejte sady sloupců pro flexibilitu a sledujte řídkost sloupců, abyste zajistili, že váš návrh řídkých sloupců splňuje požadavky výkonu a udržitelnosti.

Ověřte hodnoty sloupce s řídkými hodnotami

Před vložením dat ověřte, že všechny řídké atributy odpovídají povolené množině.

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

Používejte sady sloupců pro flexibilitu

Sady sloupců zjednodušují práci se řídkými sloupci:

Přidávejte nové řídké sloupce bez změn kódu.
Ukládejte dynamické atributy.
Automaticky serializujte a deserializujte XML.

Bez množiny sloupců potřebujete explicitní seznamy sloupců. Při použití sady sloupců sloupec XML automaticky zpracovává dynamické atributy.

Monitorování nulových procent

Analyzujte NULL procento pro sloupec, abyste zjistili, zda je vhodný pro optimalizaci řídkých sloupců.

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

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-07-22