SQL Server 2012 SP1
看到這個標題一定覺得這有什麼好講的，直接加計算行欄位不就可以了嗎?
我當時也是覺得如此，但是還真的不可以~~
情境:有一張表TESTTB01有一create的欄位，資料形態是datetimeoffset，時區有+08:00，也有+07:00。需要用日期來判斷資料是哪一天。但是在不同時區造成日期的差異。
因此想利用計算型欄位來找出資料是屬於哪一天，並優化查詢效能。
資料約5億筆
1.直接在資料表參考同一欄位，建立3個計算行欄位。
command:
ALTER TABLE [dbo].[TESTTB01] ADD CreateDate AS (CONVERT(date, Created)) PERSISTED;
GO
ALTER TABLE [dbo].[TESTTB01] ADD CreateUTC AS (Created AT TIME ZONE 'UTC') PERSISTED;
GO
ALTER TABLE [dbo].[TESTTB01] ADD CreateUTCDate AS (CONVERT(date, (Created AT TIME ZONE 'UTC'))) PERSISTED;
GO
Msg 4936, Level 16, State 1, Line 4
Computed column 'CreateUTC' in table 'TESTTB01' cannot be persisted because the column is non-deterministic.
Msg 4936, Level 16, State 1, Line 6
Computed column 'CreateUTCDate' in table 'TESTTB01' cannot be persisted because the column is non-deterministic.
2.嘗試重新建立空測試表:TESTTB01_NEW
command:
CREATE TABLE [dbo].[TESTTB01_NEW](
[Id] [uniqueidentifier] NOT NULL,
[Status] [int] NOT NULL,
[Created] [datetimeoffset](7) NOT NULL,
CONSTRAINT [PK_TESTTB01_NEW] PRIMARY KEY NONCLUSTERED
(
[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] TEXTIMAGE_ON [PRIMARY]
GO
3.建立function並設定計算型欄位
command:
CREATE FUNCTION dbo.FnCreateUTC(@DATE datetimeoffset)
RETURNS datetimeoffset
AS
BEGIN
DECLARE @CreateUTC DATETIMEOFFSET;
SELECT @CreateUTC= (Created AT TIME ZONE 'UTC') FROM dbo.TESTTB01_NEW
RETURN(@CreateUTC);
END;
GO
GO
錯誤訊息，不可以使用PERSISTED
Msg 4936, Level 16, State 1, Line 49
Computed column 'CreateUTC' in table 'TESTTB01_NEW' cannot be persisted because the column is non-deterministic
4.不使用PERSISTED
command:
ALTER TABLE [dbo].[TESTTB01_NEW] ADD CreateUTC AS dbo.FnCreateUTC(created) ;
GO
5.再補上兩個計算型欄位，一個使用function，一個不使用function
command:
ALTER TABLE [dbo].[TESTTB01_NEW] ADD CreateUTC AS dbo.FnCreateUTC(created);
GO
ALTER TABLE [dbo].[TESTTB01_NEW] ADD CreateUTCDate AS (CONVERT(date, (Created AT TIME ZONE 'UTC')));
GO
結論:每一個欄位只能被一個計算型欄位參考，但是不侷限被function使用
在步驟3是一個不好的寫法，這支function效能很差，function內容改成以下效能好很多
DECLARE @CreateUTCDate DATETIMEOFFSET;
SET @CreateUTCDate= (CONVERT(date, (@DATE AT TIME ZONE 'UTC')))
