Używaj rzadkich kolumn i zestawów kolumn w mssql-python

Kolumny rzadkie to optymalizacja przechowywania danych Microsoft SQL dla wartości NULL w tabelach z wieloma kolumnami do zerowania. Aplikacje klienckie postrzegają rzadkie kolumny jako kolumny regularne. Sterownik mssql-python odczytuje i zapisuje je jak każdą inną kolumnę, bez specjalnego obsługiwania.

Funkcja	Opis
Rzadkie kolumny	Wartości NULL nie używają pamięci
Zestawy kolumn	Reprezentacja XML wszystkich rzadkich kolumn
Szerokie tabele	Wsparcie dla maksymalnie 30 000 kolumn

Note

Rzadkie kolumny to funkcja po stronie serwera. Sterownik mssql-python nie wymaga żadnej specjalnej konfiguracji ani API do pracy z rzadkimi kolumnami. Jedyna widoczna dla klienta różnica: gdy używasz zestawów kolumn, zwracają one reprezentację XML o rzadkich wartościach kolumn.

Najlepiej dopasowane do:

Tabele z ponad 20–50% wartości NULL.
Przechowywanie dokumentów z atrybutami zmiennymi.
Wzorce EAV (Entity-Attribute-Value).
Dane z czujników z wieloma opcjonalnymi odczytami.

Tworz rzadkie kolumny

Zdefiniuj rzadkie kolumny w schemacie tabeli, dodając SPARSE NULL modyfikator do kolumn, które często zawierają wartości NULL.

Podstawowa tabela kolumn rzadkich

Stwórz tabelę z rzadkimi kolumnami dla opcjonalnych atrybutów.

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

Z zestawem kolumn

Dodaj zestaw kolumn, aby zapewnić dostęp XML do wszystkich rzadkich kolumn jednocześnie.

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

Wstaw rzadkie dane kolumny

Wstawiaj do rzadkich kolumn według nazwy, tak jak w zwykłych kolumnach.

Wstaw poszczególne kolumny

Wstaw produkty z określonymi rzadkimi wartościami kolumn.

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

Wstaw za pomocą zestawu kolumn (XML)

Wstaw wiele rzadkich wartości kolumn jednocześnie, przekazując XML do zbioru kolumn.

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

Dynamiczne wstawianie atrybutów

Stwórz funkcję, która akceptuje dynamiczne atrybuty jako słownik i automatycznie buduje XML (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()

Wykonywanie zapytań dotyczących kolumn rozrzedzonych

Wykonuj zapytania dotyczące kolumn rzadkich po nazwie lub pobieraj jednocześnie wszystkie wartości kolumn rzadkich za pomocą zestawu kolumn.

Zapytuj poszczególne kolumny

Zapytaj konkretne rzadkie kolumny za pomocą standardowej składni 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}")

Zapytanie przez zbiór kolumn

Pobierz wszystkie rzadkie wartości kolumn jako XML ze zbioru kolumn.

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

Analizować kod XML zestawu kolumn w Pythonie

Rozparuj XML z zestawu kolumn, aby przekonwertować go na słownik Python dla łatwiejszej manipulacji.

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

Zapytanie za pomocą SELECT *

Gdy używasz SELECT *, zestaw kolumn zwraca jako pojedynczą kolumnę XML zamiast pojedynczych kolumn rzadkich.

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

Jawnie odwołuj się do poszczególnych kolumn

Aby pobrać pojedyncze rzadkie kolumny z tabeli z zestawem kolumn, wymień je jawnie w 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}")

Aktualizuj rzadkie kolumny

Aktualizuj poszczególne kolumny rzadkie według nazwy lub zastąp wszystkie rzadkie wartości jednocześnie za pomocą zestawu kolumn XML.

Aktualizuj poszczególne kolumny

Aktualizuj konkretne rzadkie wartości kolumn za pomocą standardowej składni SQL 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()

Aktualizacja za pomocą zestawu kolumn

Zastąp wszystkie wartości w kolumnach SPARSE, bezpośrednio aktualizując kod XML zestawu kolumn.

# 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

Częściowa aktualizacja przez zestaw kolumn

Aktualizuj tylko konkretne rzadkie atrybuty, zachowując wartości innych atrybutów, które nie są uwzględnione w aktualizacji.

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

Dynamiczne wzory kolumnowe

Buduj elastyczne zapytania dynamicznie przeszukujące rzadkie kolumny, wykorzystując listy dozwolonych do weryfikacji nazw kolumn i zapobiegania atakom SQL injection.

Zapytanie o dane w stylu EAV

Wyszukaj produkty według określonej nazwy atrybutu i wartości, używając dopasowywania wzorca 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")

Elastyczne wyszukiwanie atrybutów

Szukaj produktów spełniających jednocześnie wiele opcjonalnych atrybutów.

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

Zagadnienia dotyczące wydajności

Oceń, kiedy rzadkie kolumny przynoszą korzyści z magazynowania, oceń koszty ogólne i wykorzystaj monitorowanie wydajności, aby zoptymalizować projekt kolumn rzadkich.

Kiedy używać rzadkich kolumn

Dobrymi kandydatami dla kolumn rzadkich są:

Kolumny, w których ponad 60–70% wartości to NULL.
Szerokie tabele z wieloma opcjonalnymi kolumnami.
Obciążenia, gdzie priorytetem jest optymalizacja pamięci masowej.

Unikaj używania rzadkich kolumn, gdy:

Większość wierszy zawiera wartości (każda wartość różna od NULL dodaje 4 bajty narzutu).
Kolumna jest często używana w klauzulach WHERE.
Kolumna jest częścią indeksu skupionego (clustered index).

Sprawdź oszczędności miejsca na dysku

Porównaj rozmiar zajmowanego miejsca przez rzadką i nierzadką wersję tej samej tabeli.

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

Rozważania dotyczące indeksu

Możesz indeksować rzadkie kolumny. Indeksy filtrowane dobrze sprawdzają się przy danych rzadkich, ponieważ pomijają wiersze NULL:

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

Operacje zbiorcze

Zoptymalizuj operacje wstawiania wielu produktów z użyciem kolumn rzadkich, tworząc kod XML zestawu kolumn w języku Python przed przekazaniem wierszy do elementu bulkcopy().

Wkład zbiorczy z rzadkimi kolumnami

Użyj kopii masowej, aby efektywnie wstawiać wiele produktów o rzadkich atrybutach.

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

Najlepsze rozwiązania

Stosuj wzorce walidacji, korzystaj z zestawów kolumn dla elastyczności i monitoruj rzadkość kolumn, aby upewnić się, że Twój projekt rzadkich kolumn spełnia cele wydajności i utrzymalności.

Zweryfikowaj rzadkie wartości kolumnowe

Sprawdź, czy wszystkie rzadkie atrybuty odpowiadają dozwolonemu zbiorowi, zanim włożysz dane.

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

Używaj zestawów kolumn dla elastyczności

Zestawy kolumn upraszczają pracę z rzadkimi kolumnami:

Dodaj nowe rzadkie kolumny bez zmian w kodzie.
Przechowuj atrybuty dynamiczne.
Automatycznie serializuj i deserializuj XML.

Bez zestawu kolumn potrzebujesz wyraźnych list kolumn. W przypadku zestawu kolumn, kolumna XML automatycznie obsługuje atrybuty dynamiczne.

Monitorowanie wartości procentowych NULL

Przeanalizuj odsetek wartości NULL w kolumnie, aby określić, czy jest ona dobrym kandydatem do optymalizacji kolumn rzadkich.

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

Opinia

Czy ta strona była pomocna?

Last updated on 2026-07-22