主页 / user-9406

AakashM's questions

Martin Hope
AakashM
Asked: 2020-10-23 04:56:35 +0800 CST
  • 2

在 SQL Server 中，sys.sql_expression_dependencies系统目录视图“包含对当前数据库中用户定义实体的每个按名称依赖的一行”，以​​引用文档。返回的列之一是referencing_class，其中文档是这样说的：

referencing_class tinyint 引用实体的类。

  • 1 = 对象或列

  • 12 = 数据库 DDL 触发器

  • 13 = 服务器 DDL 触发器

不可为空。

但是，我现在正在查看一个数据库，其中有一行referencing_class等于 7。7 是什么意思，这里的可能值的正确列表是什么？

sql-server metadata
Martin Hope
AakashM
Asked: 2019-10-26 13:32:21 +0800 CST
  • 0

我正在调查的一个数据库在其中定义了一个视图admin.View_PartitionRanges，该视图主要基于2009PartitionRanges年这篇 MSDN 博客文章中的视图。那里的视图定义如下：

CREATE VIEW [dbo].[PartitionRanges] AS
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       1 as [partition_number],
       case when prv.value is null then NULL else '<' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = 1 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = 1
 where pf.boundary_value_on_right = 1
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       prv.boundary_id + cast(pf.boundary_value_on_right as int) as [partition_number],
       case when pf.boundary_value_on_right = 0 then '<=' else '>=' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_range_values prv on
       prv.function_id = pf.function_id and prv.parameter_id = 1
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id = prv.boundary_id + cast(pf.boundary_value_on_right as int)
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       pf.fanout as [partition_number],
       case when prv.value is null then NULL else '>' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = pf.fanout 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = pf.fanout - 1
 where pf.boundary_value_on_right = 0
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       NULL, NULL, NULL, NULL,
       fg.name as [filegroup],
       case when dds.destination_id = pf.fanout + 1
            then N'NEXT USED' else N'NOT USED'
       end as [status]
  from sys.partition_functions pf
  join sys.partition_schemes ps on ps.function_id = pf.function_id
  join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id > pf.fanout
  join sys.filegroups fg on fg.data_space_id = dds.data_space_id

但是，当我SELECT *在 SSMS 中对此视图执行操作时，出现错误

An error occurred while executing batch. Error message is: 
Input string was not in a correct format.

怎么了？

sql-server partitioning
Martin Hope
AakashM
Asked: 2013-06-11 08:31:29 +0800 CST
  • 9

使用 SQL Server 2008 R2 附带的 SQL Server Management Studio，我使用许多不同的 SQL Server 身份验证凭据集连接到同一台服务器。在我的开发机器上，我Remember password广泛使用复选框以避免每次都查找密码。

当我选择我之前连接过的登录名（使用记住密码）时，密码输入字段会填充星号，然后单击连接允许我进入 -大多数情况下。但有时，我无法辨别模式，在选择以前使用的登录时，密码字段会预先填充星号，但单击连接告诉我登录失败。我输入密码，单击连接，然后进入，然后通常下次我使用相同的登录名时它会起作用。

我的怀疑是 SSMS 准备记住的凭据的最大数量，并且实际上n只记住最近使用的凭据，但我在文档中找不到任何提及此类行为的内容。

SSMS 将记住多少凭据是否存在已知限制？

sql-server sql-server-2008-r2
Martin Hope
AakashM
Asked: 2012-09-21 06:29:26 +0800 CST
  • 8

我正在修复 SQL Server 中多语句存储过程的性能问题。我想知道我应该花时间在哪些部分。

我从如何阅读查询成本中了解到，它总是一个百分比吗？即使告诉 SSMS包括实际执行计划，“查询成本（相对于批次）”数字仍然基于成本估算，这可能与实际情况相去甚远

我从Measurement Query Performance : “Execution Plan Query Cost” vs “Time Taken”中了解到，我可以使用语句围绕存储过程的调用SET STATISTICS TIME，然后我将在Messages窗格中获得如下列表：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

[etc]

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 206 ms.

每个语句都有一个输出消息。

我可以“轻松”（虽然不方便）将时间统计输出与“执行计划”窗格中的逐语句执行计划相关联，方法是对它们进行计数：第四条SQL Server Execution Times消息输出对应Query 4于“执行计划”窗格中的输出，依此类推。

但是有更好的方法吗？

sql-server performance