Bagikan melalui

BUAT INDEKS PENYIMPAN KOLOM (Transact-SQL)

  • Artikel

Berlaku untuk: Database SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database di Microsoft Fabric

Konversi tabel rowstore menjadi indeks penyimpan kolom berkluster, atau buat indeks penyimpan kolom non-kluster. Gunakan indeks penyimpan kolom untuk menjalankan analitik operasional real time secara efisien pada beban kerja OLTP, atau untuk meningkatkan kompresi data dan performa kueri untuk beban kerja pergudangan data.

Ikuti Apa yang baru dalam indeks penyimpan kolom untuk peningkatan terbaru pada fitur ini.

  • Indeks penyimpan kolom berkluster yang diurutkan diperkenalkan di SQL Server 2022 (16.x). Untuk informasi selengkapnya, lihat MEMBUAT INDEKS PENYIMPAN KOLOM. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Indeks penyimpan kolom: Gambaran Umum.

  • Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat tabel sebagai indeks penyimpan kolom berkluster. Tidak perlu lagi membuat tabel rowstore terlebih dahulu lalu mengonversinya menjadi indeks penyimpan kolom berkluster.

  • Untuk informasi tentang panduan desain indeks penyimpan kolom, lihat Indeks penyimpan kolom - Panduan desain.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server dan Azure SQL Database:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ ORDER (column [ , ...n ] ) ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Sintaks untuk Azure Synapse Analytics, Gudang Data Paralel, SQL Server 2022 (16.x) dan versi yang lebih baru:

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

Argumen

Beberapa opsi tidak tersedia di semua versi mesin database. Tabel berikut ini memperlihatkan versi saat opsi diperkenalkan di CLUSTERED COLUMNSTORE dan indeks COLUMNSTORE NONCLUSTERED:

Opsi CLUSTERED TIDAK TERKLUSTER
COMPRESSION_DELAY SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DATA_COMPRESSION SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DARING SQL Server 2019 (15.x) SQL Server 2017 (14.x)
Klausul WHERE T/A SQL Server 2016 (13.x)

Semua opsi tersedia di Azure SQL Database.

CREATE CLUSTERED COLUMNSTORE INDEX

Buat indeks penyimpan kolom berkluster di mana semua data dikompresi dan disimpan menurut kolom. Indeks menyertakan semua kolom dalam tabel, dan menyimpan seluruh tabel. Jika tabel yang ada adalah indeks timbunan atau berkluster, maka tabel tersebut dikonversi ke indeks penyimpan kolom berkluster. Jika tabel sudah disimpan sebagai indeks penyimpan kolom berkluster, maka indeks yang ada akan dihilangkan dan dibangun kembali.

index_name

Menentukan nama untuk indeks baru.

Jika tabel sudah memiliki indeks penyimpan kolom berkluster, Anda dapat menentukan nama yang sama dengan indeks yang ada, atau Anda bisa menggunakan opsi DROP EXISTING untuk menentukan nama baru.

ON [ database_name . [ schema_name ] . | schema_name . ] table_name

Menentukan nama satu, dua, atau tiga bagian tabel yang akan disimpan sebagai indeks penyimpan kolom berkluster. Jika tabel adalah timbunan atau memiliki indeks berkluster, maka tabel dikonversi dari rowstore ke penyimpan kolom. Jika tabel sudah menjadi penyimpan kolom, pernyataan ini membangun kembali indeks penyimpan kolom berkluster.

ORDER untuk penyimpan kolom berkluster

column_store_order_ordinal Gunakan kolom di sys.index_columns untuk menentukan urutan kolom untuk indeks penyimpan kolom berkluster. Penyimpan kolom memesan bantuan dengan eliminasi segmen, terutama dengan data string. Untuk informasi selengkapnya, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan dan indeks Penyimpan Kolom - Panduan desain.

Untuk mengonversi ke indeks penyimpan kolom berkluster yang diurutkan, indeks yang ada harus berupa indeks penyimpan kolom berkluster. Gunakan DROP_EXISTING opsi tersebut.

Jenis data LOB (jenis data panjang (maks) tidak dapat menjadi kunci indeks penyimpan kolom berkluster yang diurutkan.

Saat membuat indeks penyimpan kolom berkluster yang diurutkan, gunakan OPTION(MAXDOP = 1) untuk pengurutan kualitas tertinggi, dengan imbalan durasi CREATE INDEX pernyataan yang jauh lebih lama. Untuk membuat indeks secepat mungkin, jangan batasi MAXDOP. Kualitas pemadatan dan pengurutan tertinggi dapat membantu kueri pada indeks penyimpan kolom.

Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Indeks penyimpan kolom: Gambaran Umum.

Opsi WITH

DROP_EXISTING = [NONAKTIF] | Di atas

DROP_EXISTING = ON menentukan untuk menghilangkan indeks yang ada, dan membuat indeks penyimpan kolom baru.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

Default, DROP_EXISTING = OFF, mengharapkan nama indeks sama dengan nama yang ada. Kesalahan terjadi jika nama indeks yang ditentukan sudah ada.

MAXDOP = max_degree_of_parallelism

Opsi ini dapat mengambil alih tingkat maksimum konfigurasi server paralelisme yang ada selama operasi indeks. Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

nilai max_degree_of_parallelism dapat berupa:

  • 1, yang berarti menekan pembuatan rencana paralel.
  • >1, yang berarti membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke angka yang ditentukan, atau lebih sedikit, berdasarkan beban kerja sistem saat ini. Misalnya, ketika MAXDOP = 4, jumlah prosesor yang digunakan adalah 4 atau kurang.
  • 0 (default), yang berarti menggunakan jumlah prosesor aktual, atau lebih sedikit, berdasarkan beban kerja sistem saat ini.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Untuk informasi selengkapnya, lihat Konfigurasi server: tingkat paralelisme maksimum, dan Mengonfigurasi Operasi Indeks Paralel.

COMPRESSION_DELAY = 0 | penundaan [ MENIT ]

Untuk tabel berbasis disk, penundaan menentukan jumlah menit minimum grup baris delta dalam status tertutup harus tetap berada di grup baris delta. SQL Server kemudian dapat memadatkannya ke dalam grup baris terkompresi. Karena tabel berbasis disk tidak melacak waktu sisipkan dan perbarui pada baris individual, SQL Server menerapkan penundaan ke grup baris delta dalam status tertutup.

Defaultnya adalah 0 menit.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

Untuk rekomendasi tentang kapan menggunakan COMPRESSION_DELAY, lihat Mulai menggunakan Columnstore untuk analitik operasional real time.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Menentukan opsi kompresi data untuk tabel, nomor partisi, atau rentang partisi yang ditentukan. Opsinya meliputi:

  • COLUMNSTORE adalah default, dan menentukan untuk memadatkan dengan kompresi penyimpan kolom yang paling berkinerja. Opsi ini adalah pilihan umum.
  • COLUMNSTORE_ARCHIVE lebih lanjut memadatkan tabel atau partisi ke ukuran yang lebih kecil. Gunakan opsi ini untuk situasi seperti pengarsipan, yang membutuhkan ukuran penyimpanan yang lebih kecil dan mampu lebih banyak waktu untuk penyimpanan dan pengambilan.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Untuk informasi selengkapnya tentang pemadatan, lihat Pemadatan data.

ONLINE = [AKTIF | NONAKTIF]
  • ON menentukan bahwa indeks penyimpan kolom tetap online dan tersedia, sementara salinan baru indeks sedang dibangun.
  • OFF menentukan bahwa indeks tidak tersedia untuk digunakan saat salinan baru sedang dibangun.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Opsi AKTIF

Dengan opsi ini, Anda dapat menentukan opsi untuk penyimpanan data, seperti skema partisi, grup file tertentu, atau grup file default. Jika opsi ON tidak ditentukan, indeks menggunakan pengaturan partisi pengaturan atau pengaturan grup file dari tabel yang ada.

partition_scheme_name ( column_name ) menentukan skema partisi untuk tabel. Skema partisi harus sudah ada dalam database. Untuk membuat skema partisi, lihat MEMBUAT SKEMA PARTISI (Transact-SQL).

column_name menentukan kolom tempat indeks yang dipartisi dipartisi. Kolom ini harus cocok dengan tipe data, panjang, dan presisi argumen fungsi partisi yang partition_scheme_name gunakan.

filegroup_name menentukan grup file untuk menyimpan indeks penyimpan kolom berkluster. Jika tidak ada lokasi yang ditentukan dan tabel tidak dipartisi, indeks menggunakan grup file yang sama dengan tabel atau tampilan yang mendasar. Grup file harus sudah ada.

Untuk membuat indeks pada grup file default, gunakan "default" atau [default]. Jika Anda menentukan "default", QUOTED_IDENTIFIER opsinya harus ON untuk sesi saat ini. QUOTED_IDENTIFIER adalah ON secara default. Untuk informasi selengkapnya, lihat SET QUOTED_IDENTIFIER (Transact-SQL).

BUAT [NONCLUSTERED] INDEKS PENYIMPAN KOLOM

Buat indeks penyimpan kolom non-kluster pada tabel rowstore yang disimpan sebagai tumpukan atau indeks berkluster. Indeks dapat memiliki kondisi yang difilter, dan tidak perlu menyertakan semua kolom tabel yang mendasar. Indeks penyimpan kolom memerlukan cukup ruang untuk menyimpan salinan data. Anda dapat memperbarui indeks, dan diperbarui saat tabel yang mendasar diubah. Indeks penyimpan kolom non-kluster pada indeks berkluster memungkinkan analitik real time.

index_name

Menentukan nama indeks. index_name harus unik dalam tabel, tetapi tidak harus unik dalam database. Nama indeks harus mengikuti aturan pengidentifikasi.

( kolom [ ,...n ] )

Menentukan kolom yang akan disimpan. Indeks penyimpan kolom noncluster dibatasi hingga 1.024 kolom.

Setiap kolom harus dari jenis data yang didukung untuk indeks penyimpan kolom. Lihat Batasan dan batasan untuk daftar jenis data yang didukung.

ON [ database_name . [ schema_name ] . | schema_name . ] table_name

Menentukan nama satu, dua, atau tiga bagian tabel yang berisi indeks.

ORDER untuk penyimpan kolom non-kluster

Kolom yang ditentukan dalam ORDER klausul untuk indeks penyimpan kolom non-kluster harus merupakan subset kolom kunci untuk indeks.

column_store_order_ordinal Gunakan kolom di sys.index_columns untuk menentukan urutan kolom untuk indeks penyimpan kolom non-kluster. Penyimpan kolom memesan bantuan dengan eliminasi segmen, terutama dengan data string. Untuk informasi selengkapnya, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan dan indeks Penyimpan Kolom - Panduan desain. Pertimbangan desain dan performa dalam artikel ini umumnya berlaku untuk indeks penyimpan kolom berkluster dan non-kluster.

Jenis data LOB (jenis data panjang (maks) tidak dapat menjadi kunci indeks penyimpan kolom tanpa kluster yang diurutkan.

Saat membuat indeks penyimpan kolom nonclustered yang diurutkan, gunakan OPTION(MAXDOP = 1) untuk pengurutan kualitas tertinggi, dengan imbalan durasi CREATE INDEX pernyataan yang jauh lebih lama. Untuk membuat indeks secepat mungkin, jangan batasi MAXDOP. Kualitas pemadatan dan pengurutan tertinggi dapat membantu kueri pada indeks penyimpan kolom.

Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks kolom yang diurutkan.

Opsi WITH

DROP_EXISTING = [NONAKTIF] | Di atas

DROP_EXISTING = ON Indeks yang ada dihilangkan dan dibangun kembali. Nama indeks yang ditentukan harus sama dengan indeks yang ada saat ini; namun, definisi indeks dapat dimodifikasi. Misalnya, Anda dapat menentukan kolom atau opsi indeks yang berbeda.

DROP_EXISTING = NONAKTIF
Kesalahan ditampilkan jika nama indeks yang ditentukan sudah ada. Jenis indeks tidak dapat diubah dengan menggunakan DROP_EXISTING. Dalam sintaksis kompatibel mundur, WITH DROP_EXISTING setara dengan WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism

Mengambil alih konfigurasi Server: tingkat maksimum opsi konfigurasi paralelisme selama operasi indeks. Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

nilai max_degree_of_parallelism dapat berupa:

  • 1, yang berarti menekan pembuatan rencana paralel.
  • >1, yang berarti membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke angka yang ditentukan, atau lebih sedikit, berdasarkan beban kerja sistem saat ini. Misalnya, ketika MAXDOP = 4, jumlah prosesor yang digunakan adalah 4 atau kurang.
  • 0 (default), yang berarti menggunakan jumlah prosesor aktual atau lebih sedikit berdasarkan beban kerja sistem saat ini.

Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Catatan

Operasi indeks paralel tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

ONLINE = [AKTIF | NONAKTIF]
  • ON menentukan bahwa indeks penyimpan kolom tetap online dan tersedia, sementara salinan baru indeks sedang dibangun.
  • OFF menentukan bahwa indeks tidak tersedia untuk digunakan saat salinan baru sedang dibangun. Dalam indeks nonclustered, tabel dasar tetap tersedia. Hanya indeks penyimpan kolom non-kluster yang tidak digunakan untuk memenuhi kueri hingga indeks baru selesai.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | penundaan [ MENIT ]

Menentukan batas yang lebih rendah tentang berapa lama baris harus tetap berada di grup baris delta, sebelum memenuhi syarat untuk migrasi ke grup baris terkompresi. Misalnya, Anda dapat mengatakan bahwa jika baris tidak berubah selama 120 menit, baris tersebut memenuhi syarat untuk dikompresi ke dalam format penyimpanan kolom.

Untuk indeks penyimpan kolom pada tabel berbasis disk, waktu saat baris dimasukkan atau diperbarui tidak dilacak. Sebagai gantinya, waktu tertutup grup baris delta digunakan sebagai proksi untuk baris. Durasi default adalah 0 menit. Baris dimigrasikan ke penyimpanan kolumnar setelah 1 juta baris terakumulasi dalam grup baris delta, dan ditandai tertutup.

DATA_COMPRESSION

Menentukan opsi kompresi data untuk tabel, nomor partisi, atau rentang partisi yang ditentukan. Hanya berlaku untuk indeks penyimpan kolom, termasuk nonclustered dan clustered. Opsinya meliputi:

  • COLUMNSTORE adalah default, dan menentukan untuk memadatkan dengan kompresi penyimpan kolom yang paling berkinerja. Opsi ini adalah pilihan umum.
  • COLUMNSTORE_ARCHIVE lebih lanjut memadatkan tabel atau partisi ke ukuran yang lebih kecil. Anda dapat menggunakan opsi ini untuk pengarsipan, atau untuk situasi lain yang memerlukan ukuran penyimpanan yang lebih kecil dan dapat membayar lebih banyak waktu untuk penyimpanan dan pengambilan.

Untuk informasi selengkapnya tentang pemadatan, lihat Pemadatan data.

WHERE <filter_expression> [ AND <filter_expression> ]

Disebut predikat filter, opsi ini menentukan baris mana yang akan disertakan dalam indeks. SQL Server membuat statistik yang difilter pada baris data dalam indeks yang difilter.

Predikat filter menggunakan logika perbandingan sederhana. Perbandingan yang menggunakan NULL literal tidak diizinkan dengan operator perbandingan. IS NULL Gunakan operator dan IS NOT NULL sebagai gantinya.

Berikut adalah beberapa contoh predikat filter untuk Production.BillOfMaterials tabel:

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
  • WHERE ComponentID IN (533, 324, 753)
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Untuk panduan tentang indeks yang difilter, lihat Membuat indeks yang difilter.

Opsi AKTIF

Opsi berikut menentukan grup file tempat indeks dibuat.

partition_scheme_name ( column_name )

Menentukan skema partisi yang menentukan grup file tempat partisi indeks yang dipartisi dipetakan. Skema partisi harus ada dalam database dengan menjalankan CREATE PARTITION SCHEME.

column_name menentukan kolom tempat indeks yang dipartisi dipartisi. Kolom ini harus cocok dengan tipe data, panjang, dan presisi argumen fungsi partisi yang partition_scheme_name gunakan. column_name tidak dibatasi untuk kolom dalam definisi indeks. Saat mempartisi indeks penyimpan kolom, Mesin Database menambahkan kolom partisi sebagai kolom indeks, jika belum ditentukan.

Jika tabel dipartisi, dan partition_scheme_name atau grup file tidak ditentukan, maka indeks ditempatkan dalam skema partisi yang sama dan menggunakan kolom partisi yang sama dengan tabel yang mendasar.

Indeks penyimpan kolom pada tabel yang dipartisi harus diratakan partisi. Untuk informasi selengkapnya tentang mempartisi indeks, lihat Tabel dan indeks yang dipartisi.

filegroup_name

Menentukan nama grup file untuk membuat indeks. Jika filegroup_name tidak ditentukan dan tabel tidak dipartisi, indeks menggunakan grup file yang sama dengan tabel yang mendasar. Grup file harus sudah ada.

"default"

Membuat indeks yang ditentukan pada grup file default.

Istilah default, dalam konteks ini, bukan kata kunci. Ini adalah pengidentifikasi untuk grup file default dan harus dibatasi, seperti dalam ON "default" atau ON [default]. Jika "default" ditentukan, opsi QUOTED_IDENTIFIER harus AKTIF untuk sesi saat ini, yang merupakan pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER.

Izin

Memerlukan izin UBAH pada tabel.

Keterangan

Anda dapat membuat indeks penyimpan kolom pada tabel sementara. Saat tabel dihilangkan atau sesi berakhir, indeks juga dihilangkan.

Dalam database Fabric SQL, tabel dengan indeks penyimpan kolom berkluster tidak dicerminkan ke Fabric OneLake.

Indeks yang difilter

Indeks yang difilter adalah indeks non-kluster yang dioptimalkan, cocok untuk kueri yang memilih persentase kecil baris dari tabel. Ini menggunakan predikat filter untuk mengindeks sebagian data dalam tabel. Indeks terfilter yang dirancang dengan baik dapat meningkatkan performa kueri, mengurangi biaya penyimpanan, dan mengurangi biaya pemeliharaan.

Opsi SET yang diperlukan untuk indeks yang difilter

Opsi SET di kolom nilai yang diperlukan diperlukan setiap kali salah satu kondisi berikut terjadi:

  • Anda membuat indeks yang difilter.
  • Operasi INSERT, UPDATE, DELETE, atau MERGE memodifikasi data dalam indeks yang difilter.
  • Pengoptimal kueri menggunakan indeks yang difilter untuk menghasilkan rencana kueri.
ATUR opsi Nilai yang diperlukan Nilai server default Nilai OLE DB dan ODBC default Nilai Pustaka DB default
ANSI_NULLS AKTIF AKTIF AKTIF TIDAK AKTIF
ANSI_PADDING AKTIF AKTIF AKTIF TIDAK AKTIF
ANSI_WARNINGS 1 AKTIF AKTIF AKTIF TIDAK AKTIF
ARITHABORT AKTIF AKTIF TIDAK AKTIF TIDAK AKTIF
CONCAT_NULL_YIELDS_NULL AKTIF AKTIF AKTIF TIDAK AKTIF
NUMERIC_ROUNDABORT TIDAK AKTIF TIDAK AKTIF TIDAK AKTIF TIDAK AKTIF
QUOTED_IDENTIFIER AKTIF AKTIF AKTIF TIDAK AKTIF

1 Pengaturan ANSI_WARNINGS ke AKTIF secara implisit mengatur ARITHABORT ke AKTIF saat tingkat kompatibilitas database diatur ke 90 atau yang lebih baru. Jika tingkat kompatibilitas database diatur ke 80 atau yang lebih lama, Anda harus secara eksplisit mengatur opsi ARITHABORT ke AKTIF.

Jika opsi SET salah, kondisi berikut dapat terjadi:

  • Indeks yang difilter tidak dibuat.

  • Mesin Database menghasilkan kesalahan, dan mengembalikan pernyataan INSERT, UPDATE, DELETE, atau MERGE yang mengubah data dalam indeks.

  • Pengoptimal kueri tidak mempertimbangkan indeks dalam rencana eksekusi untuk pernyataan Transact-SQL apa pun.

Untuk informasi selengkapnya tentang indeks yang difilter, lihat Membuat indeks yang difilter.

Pembatasan dan batasan

Setiap kolom dalam indeks penyimpan kolom harus dari salah satu jenis data bisnis umum berikut:

  • datetimeoffset [ ( n ) ]
  • datetime2 [ ( n ) ]
  • datetime
  • smalldatetime
  • date
  • time [ ( n ) ]
  • float [ ( n ) ]
  • nyata [ ( n ) ]
  • desimal [ ( presisi [ , skala ] ] ]
  • numerik [ ( presisi [ , skala ] ] ]
  • uang
  • smallmoney
  • bigint
  • int
  • smallint
  • kecil
  • bit
  • nvarchar [ ( n ) ]
  • nvarchar(maks) 1
  • nchar [ ( n ) ]
  • varchar [ ( n ) ]
  • varchar(maks) 1
  • char [ ( n ) ]
  • varbinary [ ( n ) ]
  • varbinary(max) 1
  • biner [ ( n ) ]
  • pengidentifikasi unik 2

1 Berlaku untuk SQL Server 2017 (14.x), dan Azure SQL Database pada tingkat Premium, tingkat Standar (S3 ke atas), dan semua tingkat penawaran vCore, hanya dalam indeks penyimpan kolom berkluster.

2 Berlaku untuk SQL Server 2014 (12.x) dan versi yang lebih baru.

Jika tabel yang mendasar memiliki kolom tipe data yang tidak didukung untuk indeks penyimpan kolom, Anda harus menghilangkan kolom tersebut dari indeks penyimpan kolom non-kluster.

Data objek besar (LOB) yang lebih besar dari 8 kilobyte disimpan dalam penyimpanan LOB off-row, hanya dengan pointer ke lokasi fisik yang disimpan dalam segmen kolom. Ukuran data yang disimpan tidak dilaporkan dalam sys.column_store_segments, sys.column_store_dictionaries, atau sys.dm_db_column_store_row_group_physical_stats.

Kolom yang menggunakan salah satu tipe data berikut ini tidak dapat disertakan dalam indeks penyimpan kolom:

  • ntext, teks, dan gambar
  • nvarchar(max), varchar(max), dan varbinary(max) 1
  • rowversion (dan tanda waktu)
  • aql_variant
  • Jenis CLR (jenis hierarkis dan spasial)
  • xml
  • pengidentifikasi unik 2

1 Berlaku untuk SQL Server 2016 (13.x) dan versi sebelumnya, dan indeks penyimpan kolom yang tidak terdaftar.

2 Berlaku untuk SQL Server 2012 (11.x).

Indeks penyimpan kolom nonclustered:

  • Tidak dapat memiliki lebih dari 1.024 kolom.
  • Tidak dapat dibuat sebagai indeks berbasis batasan. Anda dapat memiliki batasan unik, batasan kunci utama, dan batasan kunci asing pada tabel dengan indeks penyimpan kolom. Batasan selalu diberlakukan dengan indeks penyimpanan baris. Batasan tidak dapat diberlakukan dengan indeks columnstore (terkluster atau non-kluster).
  • Tidak dapat menyertakan kolom jarang.
  • Tidak dapat diubah dengan menggunakan pernyataan ALTER INDEX. Untuk mengubah indeks nonclustered, Anda harus menghilangkan dan membuat ulang indeks penyimpan kolom sebagai gantinya. Anda dapat menggunakan ALTER INDEX untuk menonaktifkan dan membangun kembali indeks penyimpan kolom.
  • Tidak dapat dibuat dengan menggunakan kata kunci INCLUDE.
  • Tidak dapat menyertakan kata kunci ASC atau DESC untuk mengurutkan indeks. Indeks penyimpan kolom diurutkan sesuai dengan algoritma pemadatan. Pengurutan akan menghilangkan banyak manfaat performa. Di Azure Synapse Analytics, dan dimulai dengan SQL Server 2022 (16.x), Anda dapat menentukan urutan untuk kolom dalam indeks penyimpan kolom. Untuk informasi selengkapnya, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan.
  • Tidak dapat menyertakan kolom LOB jenis nvarchar(max), varchar(max), dan varbinary(max) dalam indeks penyimpan kolom non-kluster. Hanya indeks penyimpan kolom berkluster yang mendukung jenis LOB, mulai dari versi SQL Server 2017 (14.x), Azure SQL Database (dikonfigurasi pada tingkat Premium, tingkat Standar (S3 ke atas), dan semua tingkat penawaran vCore). Versi sebelumnya tidak mendukung jenis LOB dalam indeks penyimpan kolom berkluster dan non-kluster.
  • Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat indeks penyimpan kolom noncluster pada tampilan terindeks.

Indeks penyimpan kolom tidak dapat dikombinasikan dengan fitur berikut:

  • Kolom komputasi. Dimulai dengan SQL Server 2017 (14.x), indeks penyimpan kolom berkluster dapat berisi kolom komputasi yang tidak bertahan. Namun, di SQL Server 2017 (14.x), indeks penyimpan kolom berkluster tidak boleh berisi kolom komputasi yang dipertahankan, dan Anda tidak dapat membuat indeks non-kluster pada kolom komputasi.
  • Pemadatan halaman dan baris, dan format penyimpanan vardecimal . (Indeks penyimpan kolom sudah dikompresi dalam format yang berbeda.)
  • Replikasi dengan indeks penyimpan kolom berkluster. Indeks penyimpan kolom tidak berkluster didukung. Untuk informasi selengkapnya, lihat sp_addarticle.
  • Aliran file.

Anda tidak dapat menggunakan kursor atau pemicu pada tabel dengan indeks penyimpan kolom berkluster. Pembatasan ini tidak berlaku untuk indeks penyimpan kolom nonclustered. Anda dapat menggunakan kursor dan pemicu pada tabel dengan indeks penyimpan kolom yang tidak terkluster.

Batasan spesifik SQL Server 2014 (12.x):

Batasan berikut hanya berlaku untuk SQL Server 2014 (12.x). Dalam rilis ini, Anda dapat menggunakan indeks penyimpan kolom berkluster yang dapat diperbarui. Indeks penyimpan kolom non-kluster masih bersifat baca-saja.

  • Pelacakan perubahan. Anda tidak dapat menggunakan pelacakan perubahan dengan indeks penyimpan kolom.
  • Mengubah pengambilan data. Fitur ini tidak dapat diaktifkan pada tabel dengan indeks penyimpan kolom berkluster. Dimulai dengan SQL Server 2016 (13.x), mengubah tangkapan data dapat diaktifkan pada tabel dengan indeks penyimpan kolom yang tidak terdaftar.
  • Sekunder yang dapat dibaca. Anda tidak dapat mengakses indeks penyimpan kolom terkluster (CCI) dari sekunder grup ketersediaan yang dapat dibaca AlwaysOn. Anda dapat mengakses indeks penyimpan kolom non-kluster (NCCI) dari sekunder yang dapat dibaca.
  • Beberapa Set Hasil Aktif (MARS). SQL Server 2014 (12.x) menggunakan fitur ini untuk koneksi baca-saja ke tabel dengan indeks penyimpan kolom. Namun, SQL Server 2014 (12.x) tidak mendukung fitur ini untuk operasi bahasa manipulasi data bersamaan (DML) pada tabel dengan indeks penyimpan kolom. Jika Anda mencoba menggunakan fitur untuk tujuan ini, SQL Server mengakhiri koneksi dan membatalkan transaksi.
  • Indeks penyimpan kolom yang tidak terdaftar tidak dapat dibuat pada tampilan atau tampilan terindeks.

Untuk informasi tentang manfaat performa dan batasan indeks penyimpan kolom, lihat Indeks penyimpan kolom: Gambaran Umum.

Metadata

Semua kolom dalam indeks penyimpan kolom disimpan dalam metadata sebagai kolom yang disertakan. Indeks penyimpan kolom tidak memiliki kolom kunci. Tampilan sistem berikut ini menyediakan informasi tentang indeks penyimpan kolom:

Contoh: mengonversi tabel dari rowstore ke columnstore

J. Mengonversi timbunan menjadi indeks penyimpan kolom berkluster

Contoh ini membuat tabel sebagai timbunan, lalu mengonversinya menjadi indeks penyimpan kolom berkluster bernama cci_Simple. Pembuatan indeks penyimpan kolom berkluster mengubah penyimpanan untuk seluruh tabel dari rowstore ke columnstore.

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

B. Mengonversi indeks berkluster menjadi indeks penyimpan kolom berkluster dengan nama yang sama

Contoh ini membuat tabel dengan indeks berkluster, lalu menunjukkan sintaks mengonversi indeks berkluster menjadi indeks penyimpan kolom berkluster. Pembuatan indeks penyimpan kolom berkluster mengubah penyimpanan untuk seluruh tabel dari rowstore ke columnstore.

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

C. Menangani indeks non-kluster saat mengonversi tabel rowstore ke indeks penyimpan kolom

Contoh ini memperlihatkan cara menangani indeks non-kluster saat Anda mengonversi tabel rowstore menjadi indeks penyimpan kolom. Dimulai dengan SQL Server 2016 (13.x), tidak ada tindakan khusus yang diperlukan. SQL Server secara otomatis menentukan dan membangun kembali indeks nonclustered pada indeks penyimpan kolom berkluster baru.

Jika Anda ingin menghilangkan indeks nonclustered, gunakan pernyataan DROP INDEX sebelum membuat indeks penyimpan kolom. Opsi DROP EXISTING hanya menghilangkan indeks berkluster yang sedang dikonversi. Ini tidak menghilangkan indeks nonclustered.

Di SQL Server 2012 (11.x) dan SQL Server 2014 (12.x), Anda tidak dapat membuat indeks nonclustered pada indeks penyimpan kolom.

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

Hanya untuk SQL Server 2012 (11.x) dan SQL Server 2014 (12.x), Anda harus menghilangkan indeks non-kluster untuk membuat indeks penyimpan kolom.

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

D. Mengonversi tabel fakta besar dari rowstore ke columnstore

Contoh ini menjelaskan cara mengonversi tabel fakta besar dari tabel rowstore ke tabel penyimpan kolom.

  1. Buat tabel kecil untuk digunakan dalam contoh ini.

    --Create a rowstore table with a clustered index and a nonclustered index.
CREATE TABLE dbo.MyFactTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL
INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
);

--Add a nonclustered index.
CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );

  2. Hilangkan semua indeks non-klusster dari tabel rowstore. Anda mungkin ingin membuat skrip indeks untuk membuatnya kembali nanti.

    --Drop all nonclustered indexes
DROP INDEX my_index ON dbo.MyFactTable;

  3. Konversi tabel rowstore menjadi tabel penyimpan kolom dengan indeks penyimpan kolom berkluster.

    Pertama, cari nama indeks rowstore berkluster yang ada. Di Langkah 1, kami mengatur nama indeks ke IDX_CL_MyFactTable. Jika nama indeks tidak ditentukan, nama indeks unik yang dihasilkan secara otomatis diberikan. Anda dapat mengambil nama yang dibuat secara otomatis dengan kueri sampel berikut:

    SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'MyFactTable';

    Opsi 1: Hilangkan indeks IDX_CL_MyFactTableberkluster yang ada, dan konversi MyFactTable ke penyimpan kolom. Ubah nama indeks penyimpan kolom berkluster baru.

    --Drop the clustered rowstore index.
DROP INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable;
GO
--Create a new clustered columnstore index with the name MyCCI.
CREATE CLUSTERED COLUMNSTORE
INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
GO

    Opsi 2: Konversi ke penyimpan kolom, dan gunakan kembali nama indeks berkluster rowstore yang ada.

    --Create the clustered columnstore index,
--replacing the existing rowstore clustered index of the same name
CREATE CLUSTERED COLUMNSTORE
INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable
WITH (DROP_EXISTING = ON);

E. Mengonversi tabel penyimpan kolom menjadi tabel rowstore dengan indeks berkluster

Untuk mengonversi tabel penyimpan kolom ke tabel rowstore dengan indeks berkluster, gunakan pernyataan CREATE INDEX dengan opsi DROP_EXISTING.

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. Mengonversi tabel penyimpan kolom menjadi timbunan rowstore

Untuk mengonversi tabel penyimpan kolom menjadi timbunan rowstore, letakkan indeks penyimpan kolom berkluster. Ini biasanya tidak disarankan, tetapi dapat beberapa memiliki penggunaan yang sempit. Untuk informasi selengkapnya tentang timbunan, lihat Timbunan (tabel tanpa indeks berkluster).

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. Defragmentasi dengan mengatur ulang indeks penyimpan kolom

Ada dua cara untuk mempertahankan indeks penyimpan kolom berkluster. Dimulai dengan SQL Server 2016 (13.x), gunakan ALTER INDEX...REORGANIZE alih-alih PEMBANGUNAN ULANG. Untuk informasi selengkapnya, lihat Grup baris indeks penyimpan kolom. Di versi SQL Server sebelumnya, Anda dapat menggunakan CREATE CLUSTERED COLUMNSTORE INDEX dengan DROP_EXISTING=ON, atau ALTER INDEX (Transact-SQL) dan opsi REBUILD. Kedua metode mencapai hasil yang sama.

Mulailah dengan menentukan nama indeks penyimpan kolom berkluster di MyFactTable.

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

Hapus fragmentasi dengan melakukan REORGANIZE pada indeks penyimpan kolom.

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

Contoh untuk indeks penyimpan kolom nonclustered

J. Membuat indeks penyimpan kolom sebagai indeks sekunder pada tabel rowstore

Contoh ini membuat indeks penyimpan kolom nonclustered pada tabel rowstore. Hanya satu indeks penyimpan kolom yang dapat dibuat dalam situasi ini. Indeks penyimpan kolom memerlukan penyimpanan tambahan, karena berisi salinan data dalam tabel rowstore. Contoh ini membuat tabel sederhana dan indeks berkluster rowstore, lalu menunjukkan sintaks pembuatan indeks penyimpan kolom non-kluster.

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

B. Membuat indeks penyimpan kolom dasar yang tidak berkluster dengan menggunakan semua opsi

Contoh berikut menunjukkan sintaksis pembuatan indeks penyimpan kolom nonclustered pada grup file DEFAULT, menentukan tingkat paralelisme maksimum (MAXDOP) sebagai 2.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable (OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON,
    MAXDOP = 2)
ON "DEFAULT";
GO

C. Membuat indeks penyimpan kolom non-kluster dengan predikat yang difilter

Contoh berikut membuat indeks penyimpan kolom yang difilter dan tidak berkluster pada Production.BillOfMaterials tabel dalam AdventureWorks2022 database sampel. Predikat filter dapat menyertakan kolom yang bukan kolom kunci dalam indeks yang difilter. Predikat dalam contoh ini hanya memilih baris di mana EndDate non-NULL.

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

D. Mengubah data dalam indeks penyimpan kolom yang tidak terkluster

Berlaku untuk: SQL Server 2012 (11.x) melalui SQL Server 2014 (12.x).

Di SQL Server 2014 (12.x) dan versi yang lebih lama, setelah Anda membuat indeks penyimpan kolom yang tidak terdaftar pada tabel, Anda tidak dapat langsung memodifikasi data dalam tabel tersebut. Kueri dengan INSERT, UPDATE, DELETE, atau MERGE gagal dan mengembalikan pesan kesalahan. Berikut adalah opsi yang bisa Anda gunakan untuk menambahkan atau mengubah data dalam tabel:

  • Nonaktifkan atau letakkan indeks penyimpan kolom. Anda kemudian dapat memperbarui data dalam tabel. Jika Anda menonaktifkan indeks penyimpan kolom, Anda dapat membangun kembali indeks penyimpan kolom saat Anda selesai memperbarui data. Contohnya:

    ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Muat data ke dalam tabel penahapan yang tidak memiliki indeks penyimpan kolom. Buat indeks penyimpan kolom pada tabel penahapan. Alihkan tabel penahapan ke dalam partisi kosong tabel utama.

  • Alihkan partisi dari tabel dengan indeks penyimpan kolom ke dalam tabel penahapan kosong. Jika ada indeks penyimpan kolom pada tabel penahapan, nonaktifkan indeks penyimpan kolom. Lakukan pembaruan apa pun. Membangun (atau membangun kembali) indeks penyimpan kolom. Alihkan tabel penahapan kembali ke partisi (sekarang kosong) dari tabel utama.

Contoh: Azure Synapse Analytics, Analytics Platform System (PDW)

J. Mengubah indeks berkluster menjadi indeks penyimpan kolom berkluster

Dengan menggunakan pernyataan CREATE CLUSTERED COLUMNSTORE INDEX dengan DROP_EXISTING = ON, Anda dapat:

  • Ubah indeks berkluster menjadi indeks penyimpan kolom berkluster.

  • Membangun kembali indeks penyimpan kolom berkluster.

Contoh ini membuat xDimProduct tabel sebagai tabel rowstore dengan indeks berkluster. Kemudian contoh menggunakan CREATE CLUSTERED COLUMNSTORE INDEX untuk mengubah tabel dari tabel rowstore menjadi tabel penyimpan kolom.

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

Cari nama indeks terkluster yang secara otomatis dibuat untuk tabel baru dalam metadata sistem, menggunakan sys.indexes. Contohnya:

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

Sekarang, Anda dapat memilih untuk:

  1. Hilangkan indeks penyimpan kolom berkluster yang ada dengan nama yang dibuat secara otomatis, lalu buat indeks penyimpan kolom berkluster baru dengan nama yang ditentukan pengguna.
  2. Hilangkan dan ganti indeks yang ada dengan indeks penyimpan kolom berkluster, dengan mempertahankan nama yang dihasilkan sistem yang sama, seperti ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Contohnya:

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

B. Membangun kembali indeks penyimpan kolom berkluster

Membangun pada contoh sebelumnya, contoh ini menggunakan CREATE CLUSTERED COLUMNSTORE INDEX untuk membangun kembali indeks penyimpan kolom berkluster yang ada, yang disebut cci_xDimProduct.

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

C. Mengubah nama indeks penyimpan kolom berkluster

Untuk mengubah nama indeks penyimpan kolom berkluster, hilangkan indeks penyimpan kolom berkluster yang ada, lalu buat ulang indeks dengan nama baru.

Kami menyarankan agar Anda membatasi operasi ini ke tabel kecil atau kosong. Dibutuhkan waktu lama untuk menghilangkan indeks penyimpan kolom besar dan berkluster, dan membangun kembali dengan nama yang berbeda.

Contoh ini mereferensikan cci_xDimProduct indeks penyimpan kolom berkluster dari contoh sebelumnya. Contoh ini menghilangkan cci_xDimProduct indeks penyimpan kolom berkluster, lalu membuat ulang indeks penyimpan kolom berkluster dengan nama mycci_xDimProduct.

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

D. Mengonversi tabel penyimpan kolom menjadi tabel rowstore dengan indeks berkluster

Mungkin ada situasi di mana Anda ingin menghilangkan indeks penyimpan kolom berkluster, dan membuat indeks berkluster. Saat Anda menghilangkan indeks penyimpan kolom berkluster, tabel diubah ke format rowstore. Contoh ini mengonversi tabel penyimpan kolom menjadi tabel rowstore dengan indeks berkluster dengan nama yang sama. Tidak ada data yang hilang. Semua data masuk ke tabel rowstore, dan kolom yang tercantum menjadi kolom kunci dalam indeks berkluster.

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

E. Mengonversi tabel penyimpan kolom kembali ke timbunan rowstore

Gunakan DROP INDEX untuk menghilangkan indeks penyimpan kolom berkluster, dan konversi tabel menjadi tumpuk rowstore. Contoh ini mengonversi cci_xDimProduct tabel menjadi timbunan rowstore. Tabel terus didistribusikan, tetapi disimpan sebagai tumpukan.

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. Membuat indeks penyimpan kolom berkluster yang diurutkan pada tabel tanpa indeks

Indeks penyimpan kolom yang tidak diurutkan mencakup semua kolom secara default, tanpa perlu menentukan daftar kolom. Indeks penyimpan kolom yang diurutkan memungkinkan Anda menentukan urutan kolom. Daftar tidak perlu menyertakan semua kolom.

Untuk informasi selengkapnya, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. Mengonversi indeks penyimpan kolom berkluster menjadi indeks penyimpan kolom berkluster yang diurutkan

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. Menambahkan kolom ke urutan indeks penyimpan kolom berkluster yang diurutkan

Anda dapat menentukan urutan untuk kolom dalam indeks penyimpan kolom. Indeks penyimpan kolom berkluster asli diurutkan pada SHIPDATE kolom saja. Contoh berikut menambahkan PRODUCTKEY kolom ke urutan. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Indeks penyimpan kolom: Gambaran Umum.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

I. Mengubah ordinal kolom yang diurutkan

Indeks penyimpan kolom berkluster asli diurutkan pada SHIPDATE, PRODUCTKEY. Contoh berikut mengubah pengurutan menjadi PRODUCTKEY, SHIPDATE. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Indeks penyimpan kolom: Gambaran Umum.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

j. Membuat indeks penyimpan kolom berkluster yang diurutkan

Anda dapat membuat indeks penyimpan kolom berkluster dengan kunci pengurutan. Saat membuat indeks penyimpan kolom berkluster yang diurutkan, Anda harus menerapkan petunjuk MAXDOP = 1 kueri untuk kualitas maksimum pengurutan dan durasi terpendek. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Indeks penyimpan kolom: Gambaran Umum.

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

Konten terkait