Repository: tobymao/sqlglot

Issue:
Please add additional TSQL support for ALTER TABLE syntax on temporal tables
SQLGlot supports the full CREATE TABLE syntax for temporal tables, but there's some cases missing around the ALTER TABLE syntax to add them after-the-fact.

I've included code to create and clean up the temporal table both ways below, testing each statement and whether SQLGlot can parse it.

Running manually against Azure SQL:
```
/* All-in-one create */
CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY, [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History));
/* Clean up */
ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)
DROP TABLE dbo.SimpleTable
DROP TABLE dbo.SimpleTable_History;

/* Adding after-the-fact */
CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY)
ALTER TABLE dbo.SimpleTable ADD [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History));
/* Clean up */
ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)
DROP TABLE dbo.SimpleTable
DROP TABLE dbo.SimpleTable_History
```

Testing against sqlglot:

```
import sqlglot

test_cases = [
# All-in-one create
"CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY, [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History));",
# Clean up 
"ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)",
"DROP TABLE dbo.SimpleTable",
"DROP TABLE dbo.SimpleTable_History",
# Adding after-the-fact
"CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY)",
"ALTER TABLE dbo.SimpleTable ADD [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)",
"ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History))",
# Clean up 
"ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)",
"DROP TABLE dbo.SimpleTable",
"DROP TABLE dbo.SimpleTable_History",
]

for statement in test_cases:
   print(f"Trying: {statement}")
   try:
     result = sqlglot.parse_one(statement, read="tsql")
     print("  Ok!")
   except:
     print("  Error")
```

Output:

```
Trying: CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY, [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History));
  Ok!
Trying: ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)
  Ok!
Trying: DROP TABLE dbo.SimpleTable
  Ok!
Trying: DROP TABLE dbo.SimpleTable_History
  Ok!
Trying: CREATE TABLE dbo.SimpleTable ([Id] INT IDENTITY PRIMARY KEY)
  Ok!
Trying: ALTER TABLE dbo.SimpleTable ADD [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
'ALTER TABLE dbo.SimpleTable ADD [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START, [ValidTo] DATET' contains unsupported syntax. Falling back to parsing as a 'Command'.
  Ok! (but actually not okay)
Trying: ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimpleTable_History))
  Error
Trying: ALTER TABLE dbo.SimpleTable SET (SYSTEM_VERSIONING = OFF)
  Ok!
Trying: DROP TABLE dbo.SimpleTable
  Ok!
Trying: DROP TABLE dbo.SimpleTable_History
  Ok!
```

Syntax references: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16

Discussion:
@georgesittas: Thanks, we'll take a look.

Solution patch:
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 4ec1fb5867..43d98fa92a 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -637,6 +637,9 @@ class Parser(parser.Parser):
             else self.expression(exp.ScopeResolution, this=this, expression=to),
         }
 
+        def _parse_alter_table_set(self) -> exp.AlterSet:
+            return self._parse_wrapped(super()._parse_alter_table_set)
+
         def _parse_wrapped_select(self, table: bool = False) -> t.Optional[exp.Expression]:
             if self._match(TokenType.MERGE):
                 comments = self._prev_comments
@@ -921,6 +924,7 @@ class Generator(generator.Generator):
         COPY_PARAMS_EQ_REQUIRED = True
         PARSE_JSON_NAME = None
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        ALTER_SET_WRAPPED = True
         ALTER_SET_TYPE = ""
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 6453a94eb6..8b6b9da16f 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -460,6 +460,9 @@ class Generator(metaclass=_Generator):
     # Whether UNIX_SECONDS(timestamp) is supported
     SUPPORTS_UNIX_SECONDS = False
 
+    # Whether to wrap <props> in `AlterSet`, e.g., ALTER ... SET (<props>)
+    ALTER_SET_WRAPPED = False
+
     # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
     PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 
@@ -3430,6 +3433,9 @@ def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
 
     def alterset_sql(self, expression: exp.AlterSet) -> str:
         exprs = self.expressions(expression, flat=True)
+        if self.ALTER_SET_WRAPPED:
+            exprs = f"({exprs})"
+
         return f"SET {exprs}"
 
     def alter_sql(self, expression: exp.Alter) -> str:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 540c25ee6c..4f4c8604de 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -7391,7 +7391,8 @@ def _parse_alter_table_set(self) -> exp.AlterSet:
             if self._match_text_seq("SERDE"):
                 alter_set.set("serde", self._parse_field())
 
-            alter_set.set("expressions", [self._parse_properties()])
+            properties = self._parse_wrapped(self._parse_properties, optional=True)
+            alter_set.set("expressions", [properties])
 
         return alter_set
 


Test patch:
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e093be52ed..ff512c157a 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -904,18 +904,18 @@ def test_ddl(self):
 
         self.validate_identity("CREATE SCHEMA testSchema")
         self.validate_identity("CREATE VIEW t AS WITH cte AS (SELECT 1 AS c) SELECT c FROM cte")
-        self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF")
-        self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'")
-        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON")
-        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF")
+        self.validate_identity("ALTER TABLE tbl SET (SYSTEM_VERSIONING=OFF)")
+        self.validate_identity("ALTER TABLE tbl SET (FILESTREAM_ON = 'test')")
+        self.validate_identity("ALTER TABLE tbl SET (DATA_DELETION=ON)")
+        self.validate_identity("ALTER TABLE tbl SET (DATA_DELETION=OFF)")
         self.validate_identity(
-            "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)"
+            "ALTER TABLE tbl SET (SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS))"
         )
         self.validate_identity(
-            "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE)"
+            "ALTER TABLE tbl SET (SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE))"
         )
         self.validate_identity(
-            "ALTER TABLE tbl SET DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS)"
+            "ALTER TABLE tbl SET (DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS))"
         )
 
         self.validate_identity("ALTER VIEW v AS SELECT a, b, c, d FROM foo")