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:
As a standalone statement to modify a variables value, or..
.. to modify certain connection settings.
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.
精彩评论