SQL Server: Tetapkan tanggal di server SQL, tetapi tetap deterministik

Dugaan saya, ini semacam bug. Di SQL 2005 saya dapat membuat tampilan yang diindeks tanpa masalah (kode ada di bawah). Ketika saya mencoba menjalankannya di SQL 2000, saya mendapatkan kesalahan yang sama seperti yang Anda dapatkan.

Berikut ini tampaknya berfungsi pada SQL 2000, tetapi saya mendapat peringatan bahwa indeks akan diabaikan DAN Anda harus mengonversi setiap kali Anda memilih dari tampilan.

CONVERT(CHAR(8), datetime_column, 112)

Bekerja di 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

Apakah kolom [datetime_column] Anda memiliki nilai default yang disetel ke "getDate()" ??

Jika demikian, karena fungsi getdate() non-deterministik, ini akan menyebabkan kesalahan ini...

Apakah fungsi yang ditentukan pengguna bersifat deterministik atau nondeterministik bergantung pada cara fungsi tersebut dikodekan. Fungsi yang ditentukan pengguna bersifat deterministik jika:

1. Fungsinya terikat skema.
2. Semua fungsi bawaan atau yang ditentukan pengguna yang dipanggil oleh fungsi yang ditentukan pengguna bersifat deterministik.
3. Badan fungsi tidak mereferensikan objek database di luar cakupan fungsi. Misalnya, fungsi deterministik tidak bisa mereferensikan tabel selain variabel tabel yang bersifat lokal pada fungsi tersebut.
4. Fungsi ini tidak memanggil prosedur tersimpan yang diperluas.

Fungsi buatan pengguna yang tidak memenuhi kriteria ini ditandai sebagai nondeterministik. Fungsi nondeterministik bawaan tidak diperbolehkan dalam isi fungsi yang ditentukan pengguna.

Coba ini:

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

Ini seharusnya berjalan lebih cepat daripada opsi CONVERT.

Inilah jawaban terbaik saya untuk menjawab pertanyaan awal:

Coba ini:

/* 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

Untuk mengujinya, coba yang berikut ini. Harap perhatikan penggunaan "PERSISTED" untuk kolom terhitung dan penggunaan [dbo.] ketika mengacu pada fungsi

/*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) 
) 

Anda sekarang seharusnya dapat menambahkan indeks pada kolom yang dihitung (tetapi lihat gotcha nanti)

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

Masukkan beberapa data acak sebanyak yang Anda inginkan tetapi lebih banyak (1000+) lebih baik jika Anda ingin menguji rencana penggunaan/eksekusi indeks

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

Dapatkan hasilnya

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

Sekarang inilah GOTCHA... Indeks yang telah dibuat pada kolom terhitung tidak digunakan! Sebaliknya, bahkan ketika memilih data pada bidang TestFloorDate yang bertahan, SQLServer (atau setidaknya versi saya) lebih memilih indeks pada TestDate.

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

Saya cukup yakin (dari ingatan) bahwa indeks pada kolom yang dihitung dan dipertahankan bermanfaat dari perspektif kinerja - saya kira Anda hanya perlu mencoba/menguji untuk penggunaan spesifik Anda sendiri

(Semoga saya telah membantu!)

Saya menyarankan yang lebih sederhana:

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

tapi saya curiga Anda akan mengalami masalah yang sama.

Sekarang jika masalahnya adalah mengembalikan ke waktu tertentu, Anda mungkin ingin mempertimbangkan untuk menggunakan cast([datetime_column] as int) saja sebagai bidang terpisah, hanya untuk indeks.

Lihat pertanyaan yang diajukan dan dijawab oleh Cade Roux. Mungkin solusinya adalah dengan membuat fungsi menggunakan WITH SCHEMABINDING dan kemudian menggunakannya di kolom terhitung

EDIT

Saya memahami bahwa tujuan Anda adalah untuk dapat memiliki indeks pada kolom itu.

Jika hal itu tidak dapat dilakukan dengan kolom terhitung maka mungkin satu-satunya pilihan adalah membuat kolom biasa dan mengubah data di kolom tersebut setiap kali Anda memperbarui kolom yang menjadi dasarnya. (katakan pada trigger)