mssql-python ile seyrek sütunlar ve sütun setleri kullanın

Seyrek sütunlar, birçok nullable sütunlu tablolarda NULL değerler için Microsoft SQL depolama optimizasyonudur. İstemci uygulamaları, seyrek sütunları düzenli sütunlar olarak görür. mssql-python sürücüsü, onları diğer sütunlar gibi özel bir işlem olmadan okur ve yazar.

Özellik	Açıklama
Seyrek sütunlar	NULL değerler sıfır depolama kullanır
Sütun kümeleri	Tüm seyrek sütunların XML temsili
Geniş tablolar	30.000 sütuna kadar destek

Note

Seyrek sütunlar sunucu taraflı bir özelliktir. mssql-python sürücüsü, seyrek sütunlarla çalışmak için özel bir yapılandırma veya API gerektirmez. İstemci tarafından görülen tek fark: sütun setleri kullandığınızda, seyrek sütun değerlerinin XML temsilini döndürürler.

Şunlar için en uygundur:

%20-50+ NULL değer içeren tablolar.
Değişken nitelikli belge depolama.
EAV (Varlık-Nitelik-Değer) örüntüleri.
Çok sayıda isteğe bağlı ölçüm içeren sensör verisi.

Seyrek sütunlar oluşturun

Tablo şemanızda seyrek sütunları tanımlayın; bu SPARSE NULL değiştiriciyi sık sık NULL değerleri barındıran sütunlara ekleyin.

Temel seyrek sütun tablosu

İsteğe bağlı öznitelikler için seyrek sütunlu bir tablo oluşturun.

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

Sütun kümesiyle

Tüm seyrek sütunlara aynı anda XML erişimi sağlamak için bir sütun seti ekleyin.

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

Seyrek sütun verisi ekle

Normal sütunlarda olduğu gibi isimlerine göre seyrek sütunlara ekleyin.

Bireysel sütunlar ekleyin

Belirli seyrek sütun değerleri doldurulmuş ürünler ekleyin.

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

Sütun kümesi aracılığıyla ekleme (XML)

XML'i sütun kümesine geçirerek birden fazla seyrek sütun değerini aynı anda ekleyin.

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

Dinamik öznitelik ekleme

Dinamik nitelikleri sözlük olarak kabul eden ve XML'i otomatik olarak oluşturan bir fonksiyon oluşturun.

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

Seyrek sütunları sorgulama

Sparse sütunlarını isme göre sorgulayın veya sütun seti üzerinden tüm seyrek değerleri bir anda alın.

Bireysel sütunları sorgulayın

Standart SELECT sözdizimi kullanarak belirli seyrek sütunları sorgulayın.

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

Sütun kümesi aracılığıyla sorgulama

Tüm seyrek sütun değerlerini sütun setinden XML olarak alın.

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

Python'da sütun kümesi XML'sini ayrıştırma

XML'i sütun setinden ayrıştırarak Python sözlüğüne dönüştürerek daha kolay işleme sağlanır.

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

SELECT ile sorgu *

SELECT * ifadesini kullandığınızda, sütun kümesi ayrı ayrı seyrek sütunlar yerine tek bir XML sütunu olarak döner.

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

Bireysel sütunları açıkça sorgulayın

Sütun seti olan bir tablodan bireysel seyrek sütunları almak için bunları SELECT maddesinde açıkça listeleyin.

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

Seyrek sütunları güncelle

Bireysel seyrek sütunları isimlerine göre güncelleyin veya tüm seyrek değerleri bir kerede sütun seti XML üzerinden değiştirin.

Bireysel sütunları güncelle

Standart SQL UPDATE sözdizimi kullanarak belirli seyrek sütun değerlerini güncelleyin.

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

Sütun kümesi aracılığıyla güncelleme

Tüm seyrek sütun değerlerini sütun seti XML'i doğrudan güncellerek değiştirin.

# 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

Sütun kümesi aracılığıyla kısmi güncelleştirme

Güncellemede yer almayan diğer niteliklerin değerlerini koruyarak yalnızca belirli seyrek nitelikleri güncelledin.

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

Dinamik sütun desenleri

Sütun isimlerini doğrulamak ve SQL enjeksiyon saldırılarını önlemek için allowlistleri kullanarak seyrek sütunlarda dinamik arama yapan esnek sorgular oluşturun.

EAV tarzı veri sorgulama

Belirli bir öznitelik adına ve değerine göre, Varlık-Nitelik-Değer (EAV) örüntü eşleştirmesini kullanarak ürün arayın.

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

Esnek özellik araması

Birden fazla isteğe bağlı özelliği aynı anda eşleştiren ürünleri arayın.

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

Performansla ilgili dikkat edilmesi gerekenler

Seyrek sütunların depolama faydası sağladığında değerlendirin, genel gider maliyetlerini değerlendirin ve seyrek sütun tasarımınızı optimize etmek için performans izleme kullanın.

Seyrek sütunlar ne zaman kullanılır

Sade köşe yazıları için iyi adaylar şunlardır:

60-70'in üzerinde% NULL değeri olan sütunlar.
Birçok isteğe bağlı sütunlu geniş tablolar.
Depolama optimizasyonunun öncelik olduğu iş yükleri.

Seyrek sütunları kullanmaktan kaçının:

Çoğu satırın değerleri vardır (her NULL olmayan değer 4 bayt ek yük ekler).
Sütun, WHERE maddelerinde sıkça kullanılır.
Sütun, kümelenmiş indeksin parçasıdır.

Depolama tasarrufunu kontrol et

Aynı tablonun seyrek ve seyrek olmayan sürümlerinin depolama boyutunu karşılaştırın.

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

Endeks konuları

Seyrek sütunları indeksleyebilirsiniz. Filtrelenmiş indeksler, NULL satırları atladıkları için seyrek veriler için iyi çalışır:

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

Toplu işlemler

Satırları bulkcopy()’a iletmeden önce Python’da sütun kümesi XML’ini oluşturarak, seyrek sütunlara sahip birden çok ürünün eklenmesini optimize edin.

Seyrek sütunlarla toplu ekleme

Toplu kopyalama kullanarak seyrek özelliklere sahip birden fazla ürün verimli şekilde ekleyin.

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

En iyi uygulamalar

Doğrulama kalıplarını takip edin, esneklik için sütun setleri kullanın ve sütun seyrekliğini izleyin; böylece sparse sütun tasarımınız performans ve sürdürülebilirlik hedeflerini karşılayabilir.

Seyrek sütun değerlerini doğrula

Veri eklemeden önce tüm seyrek özelliklerin izin verilen kümeyle eşleşip eşleşmediğini doğrulayın.

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

Esneklik için sütun setleri kullanın

Sütun setleri, seyrek sütunlarla çalışmayı kolaylaştırır:

Kod değişikliği olmadan yeni seyrek sütunlar ekleyin.
Dinamik özellikleri depolayın.
XML'i otomatik olarak serileştirip serilikten çıkar.

Bir sütun seti olmadan, açık sütun listelerine ihtiyacınız var. Bir sütun seti olduğunda, XML sütunu dinamik öznitelikleri otomatik olarak işler.

NULL yüzdelerini izleyin

Bir sütunun NULL yüzdesini analiz ederek seyrek sütun optimizasyonu için iyi bir aday olup olmadığını belirleyin.

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

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2026-07-22