SQL Server: กำหนดวันที่ในเซิร์ฟเวอร์ SQL แต่คงไว้ซึ่งการกำหนดไว้

ฉันเดาว่านี่เป็นข้อผิดพลาดบางอย่าง ใน SQL 2005 ฉันสามารถสร้างมุมมองที่จัดทำดัชนีได้โดยไม่มีปัญหา (โค้ดอยู่ด้านล่าง) เมื่อฉันพยายามเรียกใช้บน SQL 2000 แม้ว่าฉันจะพบข้อผิดพลาดเดียวกันกับที่คุณได้รับก็ตาม

ดูเหมือนว่าสิ่งต่อไปนี้จะใช้ได้กับ SQL 2000 แต่ฉันได้รับคำเตือนว่าดัชนีจะถูกละเว้น และคุณจะต้องแปลงทุกครั้งที่คุณเลือกจากมุมมอง

CONVERT(CHAR(8), datetime_column, 112)

ทำงานใน SQL 2005:

CREATE TABLE dbo.Test_Determinism (
    datetime_column DATETIME    NOT NULL    DEFAULT GETDATE())
GO

CREATE VIEW dbo.Test_Determinism_View
WITH SCHEMABINDING
AS
    SELECT
        DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) AS EffectiveDate
    FROM
        dbo.Test_Determinism
GO

CREATE UNIQUE CLUSTERED INDEX IDX_Test_Determinism_View ON dbo.Test_Determinism_View (EffectiveDate)
GO

คอลัมน์ของคุณ [datetime_column] มีการตั้งค่าเริ่มต้นเป็น "getDate()" หรือไม่ ??

หากเป็นเช่นนั้น เนื่องจากฟังก์ชัน getdate() ไม่สามารถกำหนดได้ จะทำให้เกิดข้อผิดพลาดนี้...

ไม่ว่าฟังก์ชันที่ผู้ใช้กำหนดจะเป็นแบบกำหนดหรือไม่ขึ้นอยู่กับการกำหนดรหัสฟังก์ชัน ฟังก์ชันที่ผู้ใช้กำหนดจะถูกกำหนดหาก:

1. ฟังก์ชันนี้ผูกกับสคีมา
2. ฟังก์ชันในตัวหรือที่ผู้ใช้กำหนดทั้งหมดที่เรียกใช้โดยฟังก์ชันที่ผู้ใช้กำหนดนั้นถูกกำหนดไว้
3. เนื้อความของฟังก์ชันอ้างอิงไม่มีวัตถุฐานข้อมูลอยู่นอกขอบเขตของฟังก์ชัน ตัวอย่างเช่น ฟังก์ชันที่กำหนดขึ้นไม่สามารถอ้างอิงตารางอื่นนอกจากตัวแปรตารางที่อยู่ภายในฟังก์ชันได้
4. ฟังก์ชันไม่เรียกขั้นตอนการจัดเก็บเพิ่มเติมใดๆ

ฟังก์ชันที่ผู้ใช้กำหนดซึ่งไม่ตรงตามเกณฑ์เหล่านี้จะถูกทำเครื่องหมายว่าไม่สามารถกำหนดได้ ไม่อนุญาตให้ใช้ฟังก์ชันที่ไม่กำหนดไว้ในตัวในเนื้อความของฟังก์ชันที่ผู้ใช้กำหนด

ลองสิ่งนี้:

CAST(FLOOR(CAST([datetime_column] as FLOAT)) AS DateTime)

มันควรจะเร็วกว่าตัวเลือก CONVERT มาก

นี่คือคำตอบที่ดีที่สุดของฉันในการตอบคำถามเดิม:

ลองสิ่งนี้:

/* create a deterministic schema bound function */
CREATE FUNCTION FloorDate(@dt datetime)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN 
    RETURN CONVERT(datetime,  FLOOR(CONVERT(float, @dt)))
END
GO

หากต้องการทดสอบ ให้ลองทำดังนี้ โปรดสังเกตการใช้ "PERSISTED" สำหรับคอลัมน์จากการคำนวณและการใช้ [dbo.] เมื่ออ้างถึงฟังก์ชัน

/*create a test table */
CREATE TABLE [dbo].[TableTestFloorDate](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestDate] [datetime] NOT NULL,
    [TestFloorDate]  AS ([dbo].[FloorDate]([TestDate])) PERSISTED,
 CONSTRAINT [PK_TableTestFloorDate] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 

ตอนนี้คุณควรจะสามารถเพิ่มดัชนีในคอลัมน์ที่คำนวณได้ (แต่ดู gotcha ในภายหลัง)

CREATE INDEX IX_TestFloorDate ON  [dbo].[TableTestFloorDate](TestFloorDate)

แทรกข้อมูลสุ่มหลาย ๆ ครั้งตามที่คุณต้องการ แต่มากกว่า (1,000+) จะดีกว่าหากคุณต้องการทดสอบแผนการใช้งาน / การดำเนินการดัชนี

INSERT INTO TableTestFloorDate (TestDate) VALUES( convert(datetime, RAND()*50000))

รับผลลัพธ์

SELECT * FROM TableTestFloorDate WHERE TestFloorDate='2013-2-2'

นี่คือ GOTCHA... ไม่ได้ใช้ดัชนีที่สร้างขึ้นในคอลัมน์จากการคำนวณ! แม้ว่าเมื่อเลือกข้อมูลในฟิลด์ TestFloorDate ที่มีอยู่แล้ว SQLServer (หรืออย่างน้อยเวอร์ชันของฉัน) ก็ชอบดัชนีบน TestDate

CREATE INDEX IX_TestFloorDate ON  [dbo].[TableTestFloorDate](TestDate)

ฉันค่อนข้างแน่ใจว่า (จากหน่วยความจำ) ว่าการจัดทำดัชนีในคอลัมน์ที่คำนวณและคงอยู่นั้นมีประโยชน์จากมุมมองของประสิทธิภาพ - ฉันเดาว่าคุณจะต้องลอง / ทดสอบสำหรับการใช้งานเฉพาะของคุณเอง

(หวังว่าฉันได้ช่วย!)

ฉันขอแนะนำสิ่งที่ง่ายกว่านี้:

 cast(cast([datetime_column] as int) as datetime)

แต่ฉันสงสัยว่าคุณจะพบปัญหาเดียวกัน

ตอนนี้หากปัญหาอยู่ที่การส่งกลับไปยังวันที่และเวลา คุณอาจต้องการพิจารณาใช้เพียง cast([datetime_column] as int) เป็นฟิลด์แยกต่างหาก สำหรับดัชนีเท่านั้น

ดูที่ คำถามนั้นถามและตอบโดย Cade Roux. บางทีวิธีแก้ปัญหาอาจเป็นการสร้างฟังก์ชันโดยใช้ WITH SCHEMABINDING แล้วใช้ในคอลัมน์ที่คำนวณ

แก้ไข

ฉันเข้าใจว่าเป้าหมายของคุณคือการมีดัชนีในคอลัมน์นั้น

หากไม่สามารถทำได้ด้วยคอลัมน์จากการคำนวณ ตัวเลือกเดียวอาจเป็นการสร้างคอลัมน์ธรรมดาและแก้ไขข้อมูลในคอลัมน์นั้นทุกครั้งที่คุณอัปเดตคอลัมน์ที่ใช้คอลัมน์นั้น (พูดในทริกเกอร์)