Nate's questions

风景

CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS 

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM 
    products as p 
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

为简单起见，假设productMetadatahas 列ProductId, MetaId, Value有大约 87m 行，并且表中有大约 400k 行products。

针对此视图的一般查询非常有效：

SELECT * FROM vProductList WHERE CalculatedPrice > 500

查询结果在 2-4 秒内（通过 vpn 和远程，所以我很擅长）。

将上述更改为计数同样快：

SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

与原始选择大约在同一时间运行，我再次同意。大约有 10k 种产品符合此标准。

我遇到了两个不同的情况，事情变得非常奇怪并且永远持续下去。

第一的

对视图中基表中的一列进行查询：

SELECT * FROM vProductList WHERE Name = 'Hammer' 

这个查询需要一点时间来运行（20-30 秒）并返回大约 30k 的结果；但是，对所述查询稍作更改：

SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer' 

需要 13 分钟才能返回一个说明 ~30k 的计数。

第二

执行WHERE IN子查询

SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

此查询返回约 300k 行，需要两分钟才能返回（我相信大部分时间只是花在将数据下载到 SSMS 中）；但是，将其更改为SELECT COUNT(*)需要 20 分钟的查询。

SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

为什么它SELECT *比 快SELECT COUNT？

我将 SSMS 提供的总执行时间用于此处列出的所有时间。

执行计划

计划SELECT 1 FROM v WHERE IN (...)

计划SELECT COUNT(0) FROM v WHERE IN (...)

注意：我尝试使用 PasteThePlan，但它一直告诉我的计划是无效的 xml。

我正在尝试取消透视表中的几列。这是一个报告工具。源数据已经标准化。我可以通过一系列 UNION ALL 语句获得我想要的结果。我可以使用 UNPIVOT 接近我想要的，但我缺少一部分。我想在我的 UNPIVOT 结果中获取源列名称。

Table structure: |  itemNumber  |  type  |  code  |  description  |
                 |  BN3466774   |  AUD1  |  444   |  desc--text   |
                 |  1238udd74   |  AUD1  |  331   |  fdsafdsafdsa |
                 |  AA34fg774   |  MAN3  |  874   |  asdfasdfasdf |

Desired Output:  | itemNumber   | SourceName | SourceValue |
                 | BN3466774    | type       | AUD1        |
                 | BN3466774    | code       | 444         |
                 | BN3466774    | description| desc--text  |
                 | 1238udd74    | type       | AUD1        |
                 | 1238udd74    | code       | 331         |
                 | 1238udd74    | description| fdsafdsafdsa|
                 ... you get the idea

如前所述，我可以使用一系列 UNION ALL 语句得到这个结果

SELECT * 
FROM
(
SELECT itemNumber, 'type' as SourceName, type as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'code  ' as SourceName, code as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'description  ' as SourceName, description  as SourceValue FROM myTable
) as myValues

我使用 UNPIVOT 的查询如下所示：

SELECT itemNumber, Value
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT 
(
  Value FOR attributes IN ( type, code, description)
) AS myValues

此查询产生以下数据：

Desired Output:  | itemNumber   | SourceValue |
                 | BN3466774    | AUD1        |
                 | BN3466774    | 444         |
                 | BN3466774    | desc--text  |
                 | 1238udd74    | AUD1        |
                 | 1238udd74    | 331         |
                 | 1238udd74    | fdsafdsafdsa|

我想使用 UNPIVOT 的原因是因为它的执行速度往往比 UNION ALL 方法快 50%，并且在一个查询中，我返回了大约 1600 万行。

我想将源列放入我的 UNPIVOT 结果中，但我无法生成执行此操作的查询。

我有一些使用 Entity Framework Code First 创建的数据库；这些应用程序正在运行，总的来说，我对 Code First 让我做的事情感到非常满意。我首先是程序员，其次是 DBA，这是必要的。我正在阅读 DataAttributes 以进一步用 C# 描述我希望数据库做什么；我的问题是：在我的桌子上放这些字符串会吃什么惩罚nvarchar(max)（见下面的例子）？

此特定表中有几列；在 C# 中，它们被定义为：

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Message { get; set; }
    public string Source { get; set; }
    public DateTime Generated { get; set; }
    public DateTime Written { get; set; }

我希望根据 Name、Source、Generated 和 Written 进行查询和/或排序。我希望 Name 和 Source 的字符长度为 0-50，偶尔可达 150。我希望这个表开始时非常小（<100k 行），但随着时间的推移会显着增长（>1m 行）。显然消息可能很小或很大，并且可能不会被查询。

我想知道的是，我的 Name 和 Source 列被定义为nvarchar(max)我从不期望它们大于 150 个字符时是否会影响性能？

作为对这个问题的跟进，我想知道是否有更好和/或更有效的方法来收集相关数据。

如前所述，这个查询确实返回了我需要的 95% 的数据——

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';

有没有办法实现相同的结果（对于每个设备，选择按 DateEntered 排序的 TOP Heartbeat 排序 DESC）但也从表中选择整行[Heartbeats]？现在，我只得到那DateTime一行。

如果我在子句中包含其他列，则GROUP BY可以将它们添加到选择中；但后来我每[Devices]行得到多行，这是我不想要的。这听起来很奇怪，但我基本上想做的是做一个查询[Devices]，然后for...each在那个集合上做一个，并为该行添加第一[Heartbeats]行[Devices]。那可能吗？

更新 这是心跳表的结构：

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsFMSFMPUp] [bit] NOT NULL,
    [IsFMSWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats] 
(
    [DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Default [DF_Heartbeats_DateEntered]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD  CONSTRAINT [DF_Heartbeats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object:  ForeignKey [FK_Heartbeats_Devices]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats]  WITH CHECK ADD  CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO

如果我这样做——

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name

我得到这个错误 -

消息 207，级别 16，状态 1，第 17 行无效的列名称“DE”。

如果我这样做——

SELECT Name, DE FROM (
    SELECT dv.Name
          ,MAX(hb.[DateEntered]) as DE
    FROM 
        [Devices] as dv
        INNER JOIN 
        [Heartbeats] as hb ON hb.DeviceID = dv.ID
    GROUP BY dv.Name
) as tmp WHERE tmp.DE < '2013-03-04'

它按预期工作。

有人可以解释为什么我需要将我的主查询嵌套为子查询来限制我的数据集吗？

另外，是否有更好的方法来实现这里的目标？从一个表中检索所有记录，以及按[DateEntered]降序排列的单个“顶部”相关记录？

作为这个关于提高查询性能的问题的后续行动，我想知道是否有办法让我的索引默认使用。

此查询运行大约 2.5 秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

这个运行大约 33 毫秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

[ID] 字段 (pk) 上有一个聚集索引，[DateEntered],[DeviceID] 上有一个非聚集索引。第一个查询使用聚集索引，第二个查询使用我的非聚集索引。我的问题是两个部分：

• 为什么，因为两个查询在 [DateEntered] 字段上都有 WHERE 子句，服务器是否在第一个而不是第二个上使用聚集索引？
• 即使没有orderby，如何在此查询中默认使用非聚集索引？（或者我为什么不想要这种行为？）

我有一个查询，目前平均需要 2500 毫秒才能完成。我的表很窄，但有 4400 万行。我有哪些选择来提高性能，或者这是否已经达到了最好的水平？

查询

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'; 

桌子

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

指数

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

添加其他索引会有所帮助吗？如果是这样，它们会是什么样子？当前的性能是可以接受的，因为查询只是偶尔运行，但我想知道作为一个学习练习，我能做些什么来让它更快吗？

更新

当我更改查询以使用强制索引提示时，查询在 50 毫秒内执行：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 

添加正确选择性的 DeviceID 子句也会达到 50 毫秒的范围：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

如果我添加ORDER BY [DateEntered], [DeviceID]到原始查询，我在 50 毫秒范围内：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

这些都使用我期望的索引（CommonQueryIndex）所以，我想我现在的问题是，有没有办法强制这个索引用于这样的查询？或者我的表的大小是否过多地抛弃了优化器，我必须只使用一个ORDER BY或一个提示？

让我们假设我在单个 SQL Server 数据库中有下表。

mySchema.Users
mySchema.Products
-- 
secondSchema.Users
secondSchema.Contracts
secondSchema.ContractItems
--
oldSchema.People
oldSchema.Table1
oldSchema.Table2
oldSchema.Table3
oldSchema.Table4
-- 
and so on and so forth

每个“模式”都完全独立于其他“模式”。它们位于单个数据库中的唯一原因是托管和成本限制。在一个完美的世界中，这些本来应该在单独的数据库中。

当（如果有的话）我需要将 secondSchema 移动到一个新的数据库中时，我需要做什么，因为它的使用率上升，现在需要放在它自己的数据库中。

我已经查看了关于何时使用非 dbo 架构与新数据库的决策标准，正如我上面所说的，它们在同一个数据库中没有关系或事务的理由。出于成本原因，他们在那里。

我有一张桌子int identity作为PK。有什么东西导致它增加，我想知道那是什么。

举个简单的例子，昨天我插入了一条记录并得到了 ID 41,773。今天我插入了一条新记录，得到了 ID 44,898。这些之间没有带 ID 的记录。

有很多应用程序可以访问数据库，所以我正在检查它们的日志是否有错误；但是，我想知道是否有一种方法可以在不插入记录的情况下找出导致 IDENTITY 增加的查询。

这张表上也没有明确的删除，所以我知道它不是插入 + 删除组合，导致这种情况，它一定是失败了。

我在 SSMS 周围闲逛，注意到我的INT列的“大小”是 4 个字节（预期），但看到我的BIT列是一个完整的字节，我有点震惊。

我误解了我在看什么吗？

我刚刚向表中添加了一个计算列，它显示为一个int，我想让它成为一个bit这可能吗？我很难找到这方面的任何信息。下面是计算列：

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED 

我正在研究将 IP 的字符串表示形式转换为其 varbinary(4/16) 形式的函数。

现在，我有这个：

@stringIP = '192.168.0.3'
select 
    CAST(PARSENAME(@stringIP, 4) as tinyint),
    CAST(PARSENAME(@stringIP, 3) as tinyint),
    CAST(PARSENAME(@stringIP, 2) as tinyint),
    CAST(PARSENAME(@stringIP, 1) as tinyint)

这很好地分解了我的 IPv4 地址；但我需要弄清楚如何将它插入到 varbinary(4) 中，以便我可以将数据插入到我的表中。我找不到任何 TSQL 语法来将这四个组合tinyint成一个varbinary(4)——我只能找到使用参数化 SQL 的 C#/VB 示例。这很好，但我正在尝试创建一个 SQL 函数在 INSTEAD OF INSERT 触发器中使用。

我有这个现有的 IP 存储表：

CREATE TABLE [dbo].[IPAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IPv4Address] [varchar](15) NULL,
    [IPv6Address] [varchar](45) NULL,
 CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED 
(
    [IPv4Address] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我想将此表转换为使用一个整数（或多个整数）来存储此 IP 信息。我想根据我的新存储列制作IPv4Address和计算列。IPv6Address

我面临的问题是我有一些遗留客户访问这个数据库，他们需要直接将“192.168.1.54”写入我的IPv4Address专栏。我希望数据库拦截它，转换为整数，并将其存储在定义为int.

是否有可能

• 编写我现有表的转换脚本，将我所有的“字符串”IP 转换为它们的整数值并将它们存储在一个 int 列中，并计算这些“字符串”列
• 允许遗留客户端“写入”这些计算列并拦截该数据，将其转换为 int，并将其透明地存储给遗留客户端？

我相当确定我可以编写一个脚本来转换数据，但如果我不能使它对我的数据库的旧客户端透明，我不想浪费我的时间。所以任何指导都会有很长的路要走。

我想做的是将生产数据库中的数据副本安排到我的开发/测试数据库中。

就架构而言，开发/测试数据库将比生产数据库更新，但生产数据库具有当前数据。我有点纠结，因为我需要根据生产规模数据测试我的数据库更改，拥有当前数据会非常有帮助。

我在生产和开发环境中使用 SQL Server 2008 R2 Standard；有没有一种方法可以在我的开发服务器上创建一个作业，以只读方式从我的生产数据库中“吸取”数据？我想要一个可以批量复制表数据、忽略丢失的列并忽略目标表中的任何约束的脚本。我真正需要一个为一个表执行此操作的脚本，我可以修改它以适合我的表并复制它以运行我的所有表。我遇到的问题是发现任何与此类似的东西。

这是对我最后一个相同性质的问题的后续跟进；从那以后，我将我的数据库纳入源代码控制，我更喜欢它。问题是我在我的开发服务器上仍然面临一个过时的数据问题，所以我想找到一种方法来安排更新。

我愿意接受替代方法来实现这一点，但它需要是我可以按计划运行的东西，并且我可以使用记事本和/或 SSMS 或标准版 SQL Server 提供的其他工具构建的东西。

我有一个已经用了几个月的生产数据库。我一直在对测试/开发服务器上的副本进行修改。我在几个表中添加了一些额外的字段（允许为空），并且在新表和现有表中添加了一些新表和索引。

我的问题是：我已准备好使用我的测试/开发数据库并将其投入生产。问题是我需要将所有数据从我的生产数据库迁移到我的测试/开发数据库。我的第一个想法是使用 SSMS 将数据从生产“导入”到测试/开发，我遇到的问题是即使使用“启用身份插入”我仍然会遇到所有类型的外键约束错误。这有点像二人组

1. 有没有比我刚才概述的过程更好的方法将架构更改从测试/开发迁移到生产？
2. 如果没有，有没有办法在导入期间暂时禁用所有插入约束？

我很乐意只将我的测试/开发模式迁移到我的生产服务器，但我还没有找到任何方法来做到这一点，而无需手动逐个表、逐个索引、逐个视图、逐个存储过程。 . 等等 我见过一些声称能够做到这一点的工具，但我正在寻找一个免费/开源的解决方案，但还没有找到任何东西。

注意：数据库很小 < 10gb，test/dev 数据库被完全截断，每个表中的记录为零。

它几乎都在标题中。我有一个 SQL Server，它显示 90% 以上的 CPU 使用率和执行的逻辑 IO 是由于“临时查询”引起的，其余的使用率显示在实际数据库名称下。这全部来自“服务器仪表板”报告。

我在这台服务器上有几个数据库，这些数据库由我无法控制的供应商应用程序直接访问。一般来说，它们的使用率非常低；我有两个数据库，它们由用 Entity Framework 和 Linq-To-SQL 编写的内部应用程序访问和使用。我想知道这是否就是为什么我的“即席查询”显示出如此高的使用率而不是我的内部数据库名称的原因。

我问的原因是因为我注意到性能有些下降，我的服务器上的 CPU 在 sqlserver.exe 进程上运行了 95% 的时间很长一段时间，最小的“下降峰值”到 10-15% 的使用。

我有 Log 和 LogItem 表；我正在编写一个查询以从两者中获取一些数据。有数千个，Logs每个Log最多可以有 125 个LogItems

有问题的查询很复杂，所以我跳过它（如果有人认为它很重要，我可能会发布它），但是当我运行 SSMS 估计查询计划时，它告诉我一个新的非聚集索引可以将性能提高 100% .

Existing Index: Non-clustered
Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified

Query Plan Recommendation
CREATE NONCLUSTERED INDEX [LogReportIndex]
ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])

只是为了好玩，我创建了这个新索引并运行了查询，令我惊讶的是，我的查询现在需要大约 1 秒才能运行，而之前是 10 多秒。

我假设我现有的索引将涵盖这个新查询，所以我的问题是为什么在我的新查询中使用的唯一列上创建一个新索引会提高性能？我应该为我的where子句中使用的每个独特的列组合建立一个索引吗？

注意：我不认为这是因为 SQL Server 正在缓存我的结果，我在创建索引之前运行了大约 25-30 次查询，并且始终花费了 10-15 秒，在索引之后它现在始终是 ~1或更少。