Computed columns sometimes missing from SELECT *

Question

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

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.

Can this somehow be attributed to Azure transient faults?

Answer 1

Please, refer this article: SELECT * AND SQL Azure.

Its author strongly recommend to replace

SELECT *
FROM TableName

with

SELECT [Column1], [Column2], ... [ColumnN]
FROM TableName

because Using SELECT * can cause extra paging, RFID lookups, unneeded table locking and hinders any future attempts to create a covered index. In summary, it is bad for performance.

By The Way: here you've got a set of interesting articles:

1. How to get to SQL Azure Query Performance Data
2. Query Performance
3. Getting Started with the Windows Azure Tools for Visual Studio
4. Getting Started with SQL Azure Development
5. Improving Your I/O Performance
6. Analyzing Query Performance just got easier with SQL Azure.

I suspect that GetOrdinary("IsExpired") causes System.IndexOutOfRangeException because of above behaviour of MS SQL Azure framework.

Conclusion? Use SELECT statement with defined list of columns to improve performance of SQL Azure database and avoid IndexOutOfRange exception.