@@OPTIONS (T-SQL)

Artikel
10/16/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Mengembalikan informasi tentang opsi SET saat ini.

Sintaks

@@OPTIONS

Tipe hasil

Integer

Keterangan

Opsi dapat berasal dari penggunaan SET perintah atau dari sp_configure user options nilai . Nilai sesi yang dikonfigurasi SET dengan perintah mengambil alih sp_configure opsi. Banyak alat, seperti Management Studio, secara otomatis mengonfigurasi opsi yang ditetapkan. Setiap pengguna memiliki @@OPTIONS fungsi yang mewakili konfigurasi.

Anda dapat mengubah opsi bahasa dan pemrosesan kueri untuk sesi pengguna tertentu dengan menggunakan pernyataan .SET @@OPTIONS hanya dapat mendeteksi opsi yang diatur ke AKTIF atau NONAKTIF.

Fungsi mengembalikan @@OPTIONS bitmap opsi, dikonversi ke bilangan bulat dasar 10 (desimal). Pengaturan bit disimpan di lokasi yang dijelaskan dalam tabel dalam artikel Mengonfigurasi opsi pengguna Opsi Konfigurasi Server.

Untuk mendekode @@OPTIONS nilai, konversikan bilangan bulat yang dikembalikan oleh @@OPTIONS ke biner, lalu cari nilai pada tabel di Konfigurasi opsi pengguna Opsi Konfigurasi Server. Misalnya, jika SELECT @@OPTIONS; mengembalikan nilai 5496, gunakan kalkulator pemrogram Windows (calc.exe) untuk mengonversi desimal 5496 ke biner. Hasilnya adalah 1010101111000. Karakter paling kanan (biner 1, 2, dan 4) adalah 0, menunjukkan bahwa tiga item pertama dalam tabel nonaktif. Berkonsultasi dengan tabel, Anda melihat bahwa itu adalah DISABLE_DEF_CNST_CHK, IMPLICIT_TRANSACTIONS, dan CURSOR_CLOSE_ON_COMMIT. Item berikutnya (ANSI_WARNINGS dalam 1000 posisi) aktif. Lanjutkan bekerja ke kiri melalui peta bit, dan turun dalam daftar opsi. Saat opsi paling kiri adalah 0, opsi tersebut dipotong oleh konversi jenis. Peta 1010101111000 bit sebenarnya 001010101111000 untuk mewakili semua 15 opsi.

Contoh C menyediakan kueri yang secara otomatis memetakan @@OPTIONS bitmask ke opsi pengguna.

Contoh

J. Demonstrasi tentang bagaimana perubahan memengaruhi perilaku

Contoh berikut menunjukkan perbedaan perilaku perangkaian dengan dua pengaturan opsi yang CONCAT_NULL_YIELDS_NULL berbeda.

SELECT @@OPTIONS AS OriginalOptionsValue;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;
  
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON;

B. Menguji pengaturan NOCOUNT klien

Contoh berikut menetapkan NOCOUNT``ON lalu menguji nilai @@OPTIONS. Opsi NOCOUNT``ON mencegah pesan tentang jumlah baris yang terpengaruh dikirim kembali ke klien yang meminta untuk setiap pernyataan dalam sesi. Nilai @@OPTIONS diatur ke 512 (0x0200). Ini mewakili opsi NOCOUNT. Contoh ini menguji apakah opsi NOCOUNT diaktifkan pada klien. Misalnya, ini dapat membantu melacak perbedaan performa pada klien.

SET NOCOUNT ON
IF @@OPTIONS & 512 > 0
RAISERROR ('Current user has SET NOCOUNT turned on.', 1, 1)

C. Meninjau bitmask @@OPTIONS dengan kueri PIVOT

Contoh berikut menggunakan konstruktor bernilai tabel untuk menghasilkan referensi daftar angka lalu membandingkan nilai @@OPTIONS dengan operator bitwise. Klausa APPLY melakukan perangkaian string untuk menghasilkan bitmask karakter, dan yang lain menghasilkan alias untuk ditinjau terhadap nilai yang didokumenkan dari Konfigurasi opsi pengguna Opsi Konfigurasi Server.

SELECT S.Bits,
    Flags.*
FROM (
    SELECT optRef,
        posRef,
        flagCheck
    FROM (
        SELECT ones.n + tens.n * 10
        FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) ones(n),
            ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) tens(n)
        ) f1(powRef)
    CROSS APPLY (
        SELECT POWER(2, powRef)
        WHERE powRef <= 16
        ) f2(binRef)
    CROSS JOIN (
        VALUES (@@OPTIONS)
        ) f3(optRef)
    CROSS APPLY (
        SELECT (optRef & binRef) / binRef
        ) f4(flagRef)
    CROSS APPLY (
        SELECT RIGHT(CONVERT(VARCHAR(2), CAST(powRef AS VARBINARY(1)), 2), 1) [posRef],
            CAST(flagRef AS INT) [flagCheck]
        ) pref
    ) TP
PIVOT( MAX( flagCheck ) FOR posRef IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] )) P
CROSS APPLY (
    SELECT CONCAT ( '', [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] ),
        CONCAT ( '', [F], [E], [D], [C], [B], [A], [9], [8], [7], [6], [5], [4], [3], [2], [1], [0] )
    ) S (stib, Bits)
CROSS APPLY (
    SELECT
          CAST(P.[0] AS BIT) /* 1     */ [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking.
        , CAST(P.[1] AS BIT) /* 2     */ [IMPLICIT_TRANSACTIONS] -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
        , CAST(P.[2] AS BIT) /* 4     */ [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed.
        , CAST(P.[3] AS BIT) /* 8     */ [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings.
        , CAST(P.[4] AS BIT) /* 16    */ [ANSI_PADDING] -- Controls padding of fixed-length variables.
        , CAST(P.[5] AS BIT) /* 32    */ [ANSI_NULLS] -- Controls NULL handling when using equality operators.
        , CAST(P.[6] AS BIT) /* 64    */ [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
        , CAST(P.[7] AS BIT) /* 128   */ [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
        , CAST(P.[8] AS BIT) /* 256   */ [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression.
        , CAST(P.[9] AS BIT) /* 512   */ [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected.
        , CAST(P.[A] AS BIT) /* 1024  */ [ANSI_NULL_DFLT_ON] -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
        , CAST(P.[B] AS BIT) /* 2048  */ [ANSI_NULL_DFLT_OFF] -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
        , CAST(P.[C] AS BIT) /* 4096  */ [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
        , CAST(P.[D] AS BIT) /* 8192  */ [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression.
        , CAST(P.[E] AS BIT) /* 16384 */ [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
    ) AS Flags;

D. Tinjau bitmask @@OPTIONS dengan GET_BIT

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru.

Contoh berikut menggunakan fungsi GET_BIT untuk mendapatkan nilai dari setiap bit tertentu di @@OPTIONS.

SELECT
      GET_BIT(@@OPTIONS, 0)  /* 1     */ AS [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking.
    , GET_BIT(@@OPTIONS, 1)  /* 2     */ AS [IMPLICIT_TRANSACTIONS] -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
    , GET_BIT(@@OPTIONS, 2)  /* 4     */ AS [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed.
    , GET_BIT(@@OPTIONS, 3)  /* 8     */ AS [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings.
    , GET_BIT(@@OPTIONS, 4)  /* 16    */ AS [ANSI_PADDING] -- Controls padding of fixed-length variables.
    , GET_BIT(@@OPTIONS, 5)  /* 32    */ AS [ANSI_NULLS] -- Controls NULL handling when using equality operators.
    , GET_BIT(@@OPTIONS, 6)  /* 64    */ AS [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
    , GET_BIT(@@OPTIONS, 7)  /* 128   */ AS [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
    , GET_BIT(@@OPTIONS, 8)  /* 256   */ AS [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression.
    , GET_BIT(@@OPTIONS, 9)  /* 512   */ AS [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected.
    , GET_BIT(@@OPTIONS, 10) /* 1024  */ AS [ANSI_NULL_DFLT_ON] -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
    , GET_BIT(@@OPTIONS, 11) /* 2048  */ AS [ANSI_NULL_DFLT_OFF] -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
    , GET_BIT(@@OPTIONS, 12) /* 4096  */ AS [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
    , GET_BIT(@@OPTIONS, 13) /* 8192  */ AS [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression.
    , GET_BIT(@@OPTIONS, 14) /* 16384 */ AS [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
GO

Bagikan melalui