Ordem da chave primária

A ideia desses 2 scripts é fazer uma simples análise e sugerir uma possível melhoria na ordem da chave primária de uma tabela específica.

Estes scripts não são para serem seguidos a ferro e fogo, existem outros fatores para serem considerados na ordem de uma PK, mas para quem não tem nada, e quer ter pelo menos uma ideia para onde ir montei eles como procedure para o SQL e como função para o Postgresql.

Versão SQL Server:

ALTER PROCEDURE usp_SugerirNovaOrdemChavePrimaria
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128)
AS
BEGIN
	SET ARITHABORT OFF 
	SET ANSI_WARNINGS OFF
    DECLARE @ColumnName NVARCHAR(128);
    DECLARE @Sql NVARCHAR(MAX) = '';
    DECLARE @OrderSuggestions NVARCHAR(MAX) = '';
    DECLARE @DynamicSQL NVARCHAR(MAX);
    DECLARE @Densidade FLOAT;
    DECLARE @OrderTable TABLE (ColumnName NVARCHAR(128), Densidade FLOAT);
    DECLARE @CurrentOrder NVARCHAR(MAX) = '';

    -- Cursor para iterar sobre as colunas da chave primária
    DECLARE ColumnCursor CURSOR FOR
        SELECT COL_NAME(ic.object_id, ic.column_id) 
        FROM sys.indexes AS i 
        INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id 
        WHERE i.is_primary_key = 1 
        AND OBJECT_NAME(ic.object_id) = @TableName;

    OPEN ColumnCursor;
    FETCH NEXT FROM ColumnCursor INTO @ColumnName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Constrói e executa a consulta SQL dinâmica para calcular a densidade para cada coluna
        SET @DynamicSQL = 'SELECT @DensidadeOUT = (COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') * 1.0 / COUNT(*)) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
        EXEC sp_executesql @DynamicSQL, N'@DensidadeOUT FLOAT OUTPUT', @Densidade OUTPUT;

        -- Adiciona os resultados em uma tabela temporária
        INSERT INTO @OrderTable (ColumnName, Densidade) VALUES (@ColumnName, @Densidade);

        -- Constrói a ordem atual
        SET @CurrentOrder += @ColumnName + ', ';

        FETCH NEXT FROM ColumnCursor INTO @ColumnName;
    END

    CLOSE ColumnCursor;
    DEALLOCATE ColumnCursor;

    -- Remove a última vírgula e espaço da ordem atual
    IF LEN(@CurrentOrder) > 0
    BEGIN
        SET @CurrentOrder = LEFT(@CurrentOrder, LEN(@CurrentOrder) - 1);
    END

    -- Constrói a sugestão de ordem com base na densidade
    SELECT @OrderSuggestions += ColumnName + ', '
    FROM @OrderTable
    ORDER BY Densidade ASC, ColumnName;

    -- Remove a última vírgula e espaço
    IF LEN(@OrderSuggestions) > 0
    BEGIN
        SET @OrderSuggestions = LEFT(@OrderSuggestions, LEN(@OrderSuggestions) - 1);
    END

    -- Compara a ordem atual com a sugerida
    IF @CurrentOrder = @OrderSuggestions
    BEGIN
        SELECT @TableName as [Object], 'A ordem atual já é a melhor.' AS SuggestedOrder;
    END
    ELSE
    BEGIN
        -- Retorna a sugestão de ordem
        SELECT @TableName as [Object], @OrderSuggestions AS SuggestedOrder;
    END
END

Versão para Postgresql:

CREATE OR REPLACE FUNCTION mc1_sugerir_nova_ordem_chave_primaria(schema_name text, nome_tabela text)
RETURNS text AS $$
DECLARE
    coluna_atual record;
    ordem_sugerida text := '';
    ordem_atual text := '';
    query text;
BEGIN
    -- Prepara a query para obter a ordem atual das colunas da chave primária
    FOR coluna_atual IN
        SELECT kcu.column_name
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        WHERE tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_name = nome_tabela
            AND tc.table_schema = schema_name
        ORDER BY kcu.ordinal_position
    LOOP
        ordem_atual := ordem_atual || coluna_atual.column_name || ', ';
    END LOOP;

    -- Remove a última vírgula e espaço da ordem atual
    IF LENGTH(ordem_atual) > 0 THEN
        ordem_atual := substr(ordem_atual, 1, LENGTH(ordem_atual) - 2);
    END IF;

    -- Prepara a query para calcular a densidade das colunas da chave primária
    query := format($f$
        SELECT 
            kcu.column_name,
            (COUNT(DISTINCT %I) * 1.0 / COUNT(*)) AS densidade
        FROM 
            information_schema.table_constraints AS tc
        JOIN 
            information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        JOIN 
            %I.%I AS t
            ON true
        WHERE 
            tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_name = %L
            AND tc.table_schema = %L
        GROUP BY 
            kcu.column_name
        ORDER BY 
            densidade DESC, kcu.column_name
    $f$, 'column_name', schema_name, nome_tabela, nome_tabela, schema_name);

    -- Executa a consulta e processa os resultados
    FOR coluna_atual IN EXECUTE query
    LOOP
        ordem_sugerida := ordem_sugerida || coluna_atual.column_name || ', ';
    END LOOP;

    -- Remove a última vírgula e espaço
    IF LENGTH(ordem_sugerida) > 0 THEN
        ordem_sugerida := substr(ordem_sugerida, 1, LENGTH(ordem_sugerida) - 2);
    END IF;

    -- Compara a ordem atual com a sugerida
    IF ordem_atual = ordem_sugerida THEN
        RETURN 'A ordem atual já é a melhor.';
    ELSE
        RETURN ordem_sugerida;
    END IF;
END;
$$ LANGUAGE plpgsql;