MCQ King

MS SQL Server – Set Commands

Start MCQ

In Microsoft SQL Server, “Set Commands” refer to a group of SQL statements and options that allow you to configure various aspects of the SQL Server session and query behavior. These commands enable you to control settings that affect the execution and results of SQL statements. Here are some key set commands in SQL Server:

  1. SET TRANSACTION ISOLATION LEVEL:
    • This command allows you to set the isolation level for a transaction. Isolation levels control how concurrent transactions interact with each other in terms of locking and data visibility.
    • Common isolation levels include READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
    • SET TRANSACTION ISOLATION LEVEL isolation_level;
  2. SET NOCOUNT:
    • SET NOCOUNT is used to suppress the message indicating the number of rows affected by a Transact-SQL statement.
    • It’s often used in stored procedures to improve performance by reducing the network traffic associated with returning row count messages.
    • SET NOCOUNT { ON | OFF }
  3. SET DATEFORMAT:
    • This command sets the format for date literals in SQL statements.
    • It specifies how SQL Server should interpret date strings.
    • SET DATEFORMAT format;
  4. SET ANSI_NULLS:
    • SET ANSI_NULLS controls whether NULL values are treated as equal in comparisons.When SET ANSI_NULLS ON, NULL values are not considered equal. When SET ANSI_NULLS OFF, NULL values are considered equal.
    • SET ANSI_NULLS { ON | OFF }
  5. SET ANSI_WARNINGS:
    • This command controls whether certain warning messages are generated during query execution.It can affect how SQL Server handles divide-by-zero errors and other potential issues.
    • SET ANSI_WARNINGS { ON | OFF }
  6. SET QUOTED_IDENTIFIER:
    • SET QUOTED_IDENTIFIER specifies whether double quotation marks (") should be treated as delimiters for identifiers (such as table or column names).It can influence how SQL Server interprets and parses SQL statements.
    • SET QUOTED_IDENTIFIER { ON | OFF }
  7. SET ARITHABORT:
    • SET ARITHABORT controls whether SQL Server terminates a query when an overflow or divide-by-zero error occurs.Setting it to ON causes SQL Server to abort the query, while OFF allows the query to continue.
    • SET ARITHABORT { ON | OFF }
  8. SET CONCAT_NULL_YIELDS_NULL:
    • This command determines whether concatenating a NULL value with a non-NULL value results in a NULL or the non-NULL value.
    • SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

Set commands in SQL Server provide a way to customize the behavior of your SQL sessions and queries to ensure they operate according to your specific requirements. Properly configuring these settings can impact the results and performance of your SQL code.