Gunakan kolom jarang dan kumpulan kolom dengan mssql-python

Kolom sparse adalah pengoptimalan penyimpanan Microsoft SQL untuk nilai NULL pada tabel yang memiliki banyak kolom yang dapat bernilai NULL. Aplikasi klien melihat kolom jarang sebagai kolom biasa. Driver mssql-python membaca dan menulisnya seperti kolom lain tanpa penanganan khusus.

Feature	Deskripsi
Kolom jarang	Nilai NULL menggunakan penyimpanan nol
Kumpulan kolom	Representasi XML dari semua kolom jarang
Tabel lebar	Dukungan hingga 30.000 kolom

Note

Kolom sparse adalah fitur sisi server. Driver mssql-python tidak memerlukan konfigurasi atau API khusus untuk bekerja dengan kolom jarang. Satu-satunya perbedaan yang terlihat oleh klien: saat Anda menggunakan kumpulan kolom, kumpulan kolom tersebut mengembalikan representasi XML dari nilai kolom sparse.

Paling cocok untuk:

Tabel dengan 20–50%+ nilai NULL.
Penyimpanan dokumen dengan atribut variabel.
Pola EAV (Entity-Attribute-Value).
Data sensor dengan banyak pembacaan opsional.

Membuat kolom jarang

Definisikan kolom sparse dalam skema tabel Anda dengan menambahkan modifier SPARSE NULL pada kolom-kolom yang sering berisi nilai NULL.

Tabel kolom spars dasar

Buat tabel dengan kolom jarang untuk atribut opsional.

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

Dengan kumpulan kolom

Tambahkan kumpulan kolom untuk menyediakan akses XML ke semua kolom jarang secara bersamaan.

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

Menyisipkan data kolom jarang

Sisipkan ke kolom sparse berdasarkan nama, sama seperti pada kolom biasa.

Menyisipkan kolom masing-masing

Masukkan produk dengan nilai tertentu pada kolom sparse yang terisi.

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

Sisipkan melalui kumpulan kolom (XML)

Sisipkan beberapa nilai kolom jarang sekaligus dengan meneruskan XML ke kumpulan kolom.

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

Penyisipan atribut dinamis

Buat fungsi yang menerima atribut dinamis sebagai kamus dan membangun XML secara otomatis.

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

Kueri kolom jarang

Kueri kolom jarang berdasarkan nama, atau ambil semua nilai jarang sekaligus melalui kumpulan kolom.

Membuat kueri untuk masing-masing kolom

Kueri kolom jarang tertentu menggunakan sintaks SELECT standar.

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

Mengkueri melalui kumpulan kolom

Ambil semua nilai kolom jarang sebagai XML dari kumpulan kolom.

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

Mengurai XML kumpulan kolom di Python

Uraikan XML dari kumpulan kolom untuk mengubahnya menjadi kamus Python agar lebih mudah dimanipulasikan.

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

Kueri dengan SELECT *

Saat Anda menggunakan SELECT *, kumpulan kolom kembali sebagai kolom XML tunggal, bukan kolom jarang individual.

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

Mengkueri kolom individual secara eksplisit

Untuk mengambil kolom sparse satu per satu dari tabel dengan kumpulan kolom, cantumkan kolom-kolom tersebut secara eksplisit dalam klausa 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}")

Memperbarui kolom jarang

Perbarui masing-masing kolom sparse berdasarkan namanya atau ganti semua nilai sparse sekaligus melalui XML kumpulan kolom.

Perbarui masing-masing kolom

Perbarui nilai kolom jarang tertentu menggunakan sintaks SQL UPDATE standar.

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

Perbarui melalui kumpulan kolom

Ganti semua nilai kolom jarang dengan memperbarui XML kumpulan kolom secara langsung.

# 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

Pembaruan sebagian melalui kumpulan kolom

Perbarui hanya atribut jarang tertentu sambil mempertahankan nilai atribut lain yang tidak disertakan dalam pembaruan.

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

Pola kolom dinamis

Buat kueri fleksibel yang menelusuri kolom sparse secara dinamis, menggunakan daftar izin untuk memvalidasi nama kolom dan mencegah serangan injeksi SQL.

Kueri data gaya EAV

Cari produk berdasarkan nama dan nilai atribut tertentu menggunakan pencocokan pola 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")

Pencarian atribut yang fleksibel

Cari produk yang cocok dengan beberapa atribut opsional sekaligus.

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

Pertimbangan performa

Evaluasikan kapan kolom sparse memberikan manfaat penyimpanan, nilai biaya overhead, dan gunakan pemantauan kinerja untuk mengoptimalkan desain kolom sparse Anda.

Kapan menggunakan kolom jarang

Yang cocok untuk kolom jarang meliputi:

Kolom dengan lebih dari 60-70% nilai NULL.
Tabel lebar dengan banyak kolom opsional.
Beban kerja di mana pengoptimalan penyimpanan adalah prioritas.

Hindari menggunakan kolom jarang ketika:

Sebagian besar baris memiliki nilai (setiap nilai non-NULL menambahkan overhead sebesar 4 byte).
Kolom tersebut sering digunakan dalam klausa WHERE.
Kolom adalah bagian dari indeks berkluster.

Periksa penghematan penyimpanan

Bandingkan ukuran penyimpanan versi sparse dan non-sparse dari tabel yang sama.

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

Pertimbangan indeks

Anda dapat mengindeks kolom sparse. Indeks terfilter berfungsi dengan baik untuk data sparse karena mengabaikan baris NULL:

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

Operasi massal

Optimalkan penyisipan beberapa produk dengan kolom sparse dengan terlebih dahulu membuat XML set kolom di Python sebelum meneruskan baris ke bulkcopy().

Sisipan massal dengan kolom jarang

Gunakan salinan massal untuk menyisipkan beberapa produk secara efisien dengan atribut jarang.

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

Praktik terbaik

Ikuti pola validasi, gunakan kumpulan kolom untuk fleksibilitas, dan pantau jarangnya kolom untuk memastikan desain kolom Anda yang jarang memenuhi sasaran performa dan pemeliharaan.

Memvalidasi nilai kolom jarang

Pastikan semua atribut sparse sesuai dengan kumpulan yang diizinkan sebelum menyisipkan data.

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

Gunakan kumpulan kolom untuk fleksibilitas

Kumpulan kolom menyederhanakan bekerja dengan kolom jarang:

Tambahkan kolom sparse baru tanpa perubahan kode.
Menyimpan atribut dinamis.
Melakukan serialisasi dan deserialisasi XML secara otomatis.

Tanpa kumpulan kolom, Anda memerlukan daftar kolom eksplisit. Dengan kumpulan kolom, kolom XML menangani atribut dinamis secara otomatis.

Pantau persentase NULL

Analisis persentase NULL pada sebuah kolom untuk menentukan apakah kolom tersebut cocok untuk pengoptimalan kolom sparse.

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

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-07-22