A-K's questions

Eu tenho um banco de dados SQL Server 2008 R2 de 1,2 TB que está mais de 50% vazio, porque migrei muitas funcionalidades para o PostgreSql. O banco de dados ainda está em uso e ainda está crescendo um pouco, mas não acho que crescerá para usar todos os 1,2 TB nos próximos 3 a 5 anos.

No ritmo atual de crescimento, não vamos consumir 1,2 Tb em mais de 14 anos. Mais especificamente, continuamos migrando a funcionalidade para o PostgreSql sempre que precisamos fazer mudanças sérias. Em um ambiente ágil muito dinâmico, isso significa que frequentemente migramos dados para fora desse banco de dados do SQL Server. Portanto, é provável que esse banco de dados cresça ainda mais devagar no futuro: ele está sendo eliminado gradualmente.

Como tal, preferimos usar esse espaço vazio não utilizado para outros bancos de dados e tal - provavelmente não será necessário para o crescimento de tabelas/índices.

Lembro que a regra geral costumava ser "nunca reduza os bancos de dados". Aplica-se neste caso? Atualmente, o servidor está executando o 2008 R2 EE, mas planejamos atualizar para 2012 em breve. Acho que, como essa funcionalidade de redução existe, deve haver casos de uso válidos em que faça sentido reduzir. Caso contrário, por que ele existe?

Podemos nos dar ao luxo de algumas horas de inatividade durante um fim de semana.

Editar: o problema que estamos resolvendo é como eu disse acima: "preferimos usar esse espaço vazio não utilizado para outros bancos de dados". Além disso, preferimos uma solução que não exija investir muito tempo no aprendizado da tecnologia da qual estamos migrando.

A consulta a seguir parece funcionar, mas não tenho certeza se é a melhor/sempre correta forma:

select 
parameter_name, 
CASE WHEN data_type='ARRAY' 
    THEN SUBSTRING(udt_name, 2)||'[]' 
    ELSE data_type 
END AS data_type
from information_schema.parameters 
where specific_schema='my_schema'
and parameter_mode='IN'
and specific_name like 'my_proc_16831'
order by ordinal_position

De qualquer forma, parece meio hacky para mim. Existe uma maneira melhor de recuperar, por exemplo, numeric[] como um tipo se meu parâmetro for uma matriz de numeric?

Preciso permitir que meus usuários especifiquem a lista de colunas que desejam selecionar. Até agora, conheço duas maneiras de fazer isso.

1. Usando refcursores

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying, 
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE 
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE 
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

Essa função é muito fácil de invocar do meu cliente Ado.Net. Tudo o que preciso fazer é passar os parâmetros. No entanto, se eu quiser testar esta função do pgAdmin, o conjunto de resultados será aberto na tela apenas se eu mantiver minha transação aberta. Isso é inconveniente. Claro, é fácil expor os dados como uma tabela HTML ou uma planilha do Excel, mas isso é um pequeno inconveniente.

2. Usando conjunto de registros

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

Infelizmente, isso complica meu código de cliente. Não consigo emitir um SELECT simples assim:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

Devo fornecer explicitamente a estrutura do meu conjunto de resultados:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') 
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

Isso é factível, mas inconveniente.

Existem outras maneiras de retornar listas de colunas dinâmicas?

Edite para proteger contra injeção de SQL, normalmente divido a lista separada por vírgulas e a uno em uma exibição do sistema. Qualquer coisa que não seja um nome de coluna real não é retornado. Eu não mencionei isso originalmente, apenas para manter a pergunta curta.

A documentação a seguir descreve como ver o refcursor retornado de uma função, aqui , assim:

    CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

Isso funciona para mim. Porém, se eu quiser manter os resultados na minha tela, tenho que manter a transação aberta. Quando executo o COMMIT, meu conjunto de resultados é descartado. Quando executo FETCH e COMMIT ao mesmo tempo, o primeiro conjunto de resultados é descartado.

Existe uma maneira de confirmar a transação, mas manter o conjunto de resultados? A versão do PgAdmin é 1.18.1.

De acordo com o MSDN, Getdate(), GetUtcDate() e CURRENT_TIMESTAMP todos retornam DATETIME. Fiz um pequeno teste, que confirma que:

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(CURRENT_TIMESTAMP) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t 
FROM #t 
ORDER BY t ;

---

2013-01-28 13:23:19.4930000
2013-01-28 13:23:19.4970000
2013-01-28 13:23:19.5000000
2013-01-28 13:23:19.5030000
2013-01-28 13:23:19.5070000
2013-01-28 13:23:19.5100000
2013-01-28 13:23:19.5130000

(recorte)

Existe uma função semelhante que retorna DATETIME2(7)?

O MSDN diz que o intervalo de números REAIS é - 3,40E + 38 a -1,18E - 38, 0 e 1,18E - 38 a 3,40E + 38. Aparentemente, o verdadeiro limite inferior é muito menor. O script a seguir preenche uma coluna REAL com 1.401298E-45:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 1.18E-37, 10 ) ;
GO
DECLARE @i INT ;
SET @i = 1 ;

WHILE @i < 20 
  BEGIN ;

    UPDATE  a
    SET     r1 = r1 / r2 ;

    SELECT  r1 ,
            r2
    FROM    a ;

    SET @i = @i + 1 ;

  END ;
GO
DROP TABLE a ;

r1            r2
------------- -------------
1.18E-38      10

(snip)

r1            r2
------------- -------------
1.401298E-45  10

Alguém pode me dizer qual é o menor número positivo real possível?

Estou brincando com HierarchyId e não descobri uma maneira baseada em conjunto para fazer o seguinte:

• inserir todas as subárvores de uma vez
• recuperar todas as subárvores de uma vez

Esta questão está relacionada à minha anterior e suspeito que a única maneira de realizar essas duas tarefas com HierarchyId seja um nó ou um nível por vez. Se eu estiver usando o caminho materializado, ambas as ações são facilmente realizadas por um único (e trivial) comando baseado em conjunto.

o que estou perdendo?

Editar: também perdi uma maneira de mover uma subárvore, mas aprendi com o comentário de Mikael Eriksson

Alguém está usando HierarchyId em produção real com tabelas de tamanho razoável, mais do que alguns milhares de linhas? É confiável/desempenho? Até agora, não encontrei ninguém não afiliado ao fornecedor que o recomendasse, e Paul Nielsen desaconselha isso aqui .

Qual é a sua experiência com o uso do HierarchyId em sistemas de produção reais?

Quais critérios você usou ao escolher HierarchyId em vez de suas alternativas?