开发者

SQL Server TSQL conventions/patterns on SET, WITH and ENABLE

开发者 https://www.devze.com 2022-12-08 03:33 出处:网络
After years of using TSQL, I still cannot figure out when to use SET, WITH or ENABLE. When you read TSQL statement like,

After years of using TSQL, I still cannot figure out when to use SET, WITH or ENABLE.

When you read TSQL statement like,

ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING 
WITH (TRACK_COLUMNS_UPDATED = ON);

It looks more intuitive and readable if 开发者_JAVA百科it was written like (invalid query below),

ALTER TABLE Person.Person
SET CHANGE_TRACKING = ON,
    TRACK_COLUMNS_UPDATED = ON

I am always getting confused between when to use SET, ENABLE, or WITH options

When are those options being used in TSQL? Is TSQL just being inconsistent?


SET is only ever used:

  1. As a standalone statement to modify a variables value, or..

  2. .. to modify certain connection settings.

  3. Or, as a clause of the DML statement, UPDATE.

ENABLE is only ever a Clause of DDL or DAL statements

WITH is always a dependent clause of other statements (though this is confusing for CTE's where it is a prefixing dependent clause)

So, SET should be clear, however, admittedly there does not seem to be any consistent rule about WITH vs ENABLE.

0

精彩评论

暂无评论...
验证码 换一张
取 消