开发者

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

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

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜