views:

63

answers:

1

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 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?

+1  A: 

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.

RBarryYoung
@RBarry: Thanks, I am actually not seeing any consistency in SQL server naming. I guess this is something I just suck it up and memorize... It's just annoying that I just spotted "ALTER DATABASE AdventureWorksLT SET ALLOW_SNAPSHOT_ISOLATION ON;" where the statement uses the verb "ALLOW" in the option instead of "enable"... This is just getting out of hand..
Sung Meister