A-K's questions

我有一个 1.2Tb SQL Server 2008 R2 数据库，其中 50% 以上是空的，因为我将很多功能迁移到 PostgreSql。该数据库仍在使用中，并且仍在增长，但我认为在未来 3-5 年内它不会增长到使用全部 1.2Tb。

按照目前的增长速度，我们不会在 14 年多的时间里用完 1.2Tb。更重要的是，每当我们需要进行重大更改时，我们都会将功能迁移到 PostgreSql。在一个非常动态的敏捷环境中，这意味着我们经常将数据迁移出这个 SQL Server 数据库。因此，该数据库在未来的增长速度可能会更慢：它最终会被逐步淘汰。

因此，我们宁愿将这个未使用的空白空间用于其他数据库等 - 可能永远不需要它来增长表/索引。

我记得曾经的经验法则是“永远不要缩小数据库”。它适用于这种情况吗？目前服务器运行的是 2008 R2 EE，但我们计划很快升级到 2012。我认为因为存在这种收缩功能，所以应该有有效的用例可以进行收缩。否则为什么它存在呢？

我们可以承受周末几个小时的停机时间。

编辑：我们正在解决的问题就像我上面所说的：“我们宁愿将这个未使用的空白空间用于其他数据库”。此外，我们更喜欢不需要花费太多时间来学习我们正在迁移的技术的解决方案。

以下查询似乎有效，但我不确定它是否是最佳/始终正确的方法：

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

无论如何，它对我来说看起来有点老套。例如，如果我的参数是数字数组，是否有更好的方法来检索 numeric[] 作为类型？

我需要让我的用户指定他们想要选择的列列表。到目前为止，我知道实现这一目标的两种方法。

1. 使用反射器

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

这个函数很容易从我的 Ado.Net 客户端调用。我需要做的就是传递参数。但是，如果我想从 pgAdmin 测试这个函数，只有当我保持我的事务打开时，结果集才会在屏幕上打开。这很不方便。当然，将数据公开为 HTML 表格或 Excel 电子表格很容易，但这有点不便。

2.使用记录集

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;

不幸的是，这使我的客户端代码复杂化。我不能像这样发出一个简单的 SELECT：

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

我必须明确提供我的结果集的结构：

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

这是可行的，但不方便。

还有其他方法可以返回动态列列表吗？

编辑以防止 SQL 注入，我通常拆分逗号分隔的列表并将其加入到系统视图中。不返回任何不是实际列名的内容。我最初没有提到这一点，只是为了让问题简短。

以下文档描述了如何查看从函数返回的 refcursor，here，如下所示：

    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;

这对我有用。但是，如果我想在我的屏幕上保留结果，我必须保持交易开放。当我执行 COMMIT 时，我的结果集被丢弃。当我同时执行 FETCH 和 COMMIT 时，第一个结果集被丢弃。

有没有办法提交事务但保留结果集？PgAdmin 的版本是 1.18.1。

根据 MSDN，Getdate()、GetUtcDate() 和 CURRENT_TIMESTAMP 都返回 DATETIME。我进行了一个简短的测试，证实了这一点：

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

（剪断）

是否有类似的函数返回 DATETIME2(7)？

MSDN 说实数的范围是 - 3.40E + 38 到 -1.18E - 38、0 和 1.18E - 38 到 3.40E + 38。显然，真正的下限要低得多。以下脚本使用 1.401298E-45 填充 REAL 列：

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

谁能告诉我实际最低可能的正数是多少？

我正在玩 HierarchyId，但我还没有想出一种基于集合的方法来执行以下操作：

• 一次插入所有子树
• 一次检索所有子树

这个问题与我之前的问题有关，我怀疑使用 HierarchyId 完成这两个任务的唯一方法是一次一个节点或一个级别。如果我使用的是物化路径，这两个操作都可以通过一个（简单的）基于集合的命令轻松完成。

我错过了什么？

编辑：我也错过了移动子树的方法，但我是从 Mikael Eriksson 的评论中学到的

是否有人在实际生产中使用 HierarchyId 并使用合理大小的表格，超过几千行？它可靠/高效吗？到目前为止，我还没有发现任何不隶属于供应商的人推荐它，Paul Nielsen在这里建议不要这样做。

您在实际生产系统中使用 HierarchyId 有何经验？

当您选择 HierarchyId 而不是其替代方案时，您使用了哪些标准？