计算列有时SELECT *失踪 [英] Computed columns sometimes missing from SELECT *

问题描述

在SQL Azure中，我有一个表或多或少设立这样的，有两个计算列（ IsExpired 和 IsDeadlineExpired ），简单地比较非可空datetime列到当前时间：

In SQL Azure, I have a table more or less set up like this, with two computed columns (IsExpired and IsDeadlineExpired) that simply compare non-nullable datetime columns to the current time:

CREATE TABLE [dbo].[Stuff]
(
   [StuffId] int NOT NULL IDENTITY(1,1),
   [Guid] uniqueidentifier NOT NULL,
   [ExpirationDate] datetime NOT NULL,
   [DeadlineDate] datetime NOT NULL,
   [UserId] int NOT NULL,
   [IsExpired] AS CAST((CASE WHEN [ExpirationDate] < GETUTCDATE() THEN 1 ELSE 0 END) AS bit),
   [IsDeadlineExpired] AS CAST((CASE WHEN [DeadlineDate] < GETUTCDATE() THEN 1 ELSE 0 END) AS bit),
   CONSTRAINT [PK_StuffId] PRIMARY KEY ([StuffId]),
   CONSTRAINT [UNQ_Guid] UNIQUE([Guid]),
)
GO

我有几个结果集的存储过程，其中一个拉：

I have a stored procedure with several result sets, one of which pulls:

SELECT * FROM [dbo].[Stuff] WHERE [Guid] = @guid

我最近发现表明，有时当结果集读取 SqlDataReader的， SqlDataReader.GetOrdinal（IsExpired），错误日志失败 IndexOutOfRangeException 。我知道preceding列，即使在这种情况下很好地工作，因为他们在没有任何错误$ C $的C preceding线正在阅读。我也相信，从过程的结果集是正确的顺序，因为它们不共享列名（否则读取早期专栏将同样失败）。

I've recently noticed error logs indicating that sometimes when the result set is read with SqlDataReader, SqlDataReader.GetOrdinal("IsExpired") fails with IndexOutOfRangeException. I know the preceding columns work fine even in those cases, since they're read in preceding lines of code with no errors. I also believe the result sets from the procedure are in proper sequence since they don't share column names (otherwise reading the earlier columns would similarly fail).

另外：大多数时候一切似乎很好地工作。

Also: most of the time everything seems to work perfectly.

可这在某种程度上归因于Azure的瞬时故障？

Can this somehow be attributed to Azure transient faults?

推荐答案

看着有些旧日志，看中的结论是，这个错误只发生时，而DACPAC兼任正在部署的查询正在运行（作为其一部分我们自动部署到这个特殊的测试环境下）。

Looking at some old logs, settled on the conclusion that this error was only happening when the queries were running while a DACPAC was concurrently being deployed (as part of our automated deployments to this particular test environment).

我假设模式不​​一定是DACPAC部署过程中一个可靠的状态。

I assume that the schema is not necessarily in a dependable state during DACPAC deployment.

此后，我们添加了一些code把应用程序变成了维护模式的部署过程中，（即使这些自动化的）。这似乎减轻该问题。

Since then, we've added some code to put the app into a "maintenance mode" during deployments, (even these automated ones). This seems to mitigate the issue.

这篇关于计算列有时SELECT *失踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！