Repository: sqlfluff/sqlfluff
Pull request: TSQL temp tables
Fixes #1572 

TSQL temp tables are identified by a single-hash or double-hash prefix.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#temporary-tables

TSQL changes:
+hash_prefix RegexLexer
+inline_comment RegexLexer patch to remove hash commenting
+HashIdentifierSegment to represent a hash-prefixed temp table identifier
Adjust SingleIdentifierGrammar to add HashIdentifierSegment

Test case changes:
+tsql/temp_tables.sql to test temp table identification



Problem statement:
TSQL: Support for temp tables
TSQL uses hashes to represent temp tables, not comments.


## Expected Behaviour

The following parses:
```
SELECT  a
		,b
		,c
FROM #UnionA;

Select	d
		,e
		,f
FROM ##UnionB;

```

## Observed Behaviour

The above code is unparseable because the temp table names are identified as comments.

Hints from issue discussion:

Commit message: TSQL temp tables

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_tsql.py b/src/sqlfluff/dialects/dialect_tsql.py
index 051415620b4..06c6bc581d9 100644
--- a/src/sqlfluff/dialects/dialect_tsql.py
+++ b/src/sqlfluff/dialects/dialect_tsql.py
@@ -23,6 +23,7 @@
     BaseFileSegment,
     Indent,
     AnyNumberOf,
+    CommentSegment,
 )
 
 from sqlfluff.core.dialects import load_raw_dialect
@@ -51,14 +52,34 @@
         ),
         # T-SQL unicode strings
         RegexLexer("single_quote_with_n", r"N'([^'\\]|\\.)*'", CodeSegment),
+        RegexLexer(
+            "hash_prefix",
+            r"[#]+[a-zA-Z0-9_]+",
+            CodeSegment,
+        ),
     ],
     before="back_quote",
 )
 
+tsql_dialect.patch_lexer_matchers(
+    [
+        # Patching comments to remove hash comments
+        RegexLexer(
+            "inline_comment",
+            r"(--)[^\n]*",
+            CommentSegment,
+            segment_kwargs={"trim_start": ("--")},
+        ),
+    ]
+)
+
 tsql_dialect.add(
     BracketedIdentifierSegment=NamedParser(
         "square_quote", CodeSegment, name="quoted_identifier", type="identifier"
     ),
+    HashIdentifierSegment=NamedParser(
+        "hash_prefix", CodeSegment, name="hash_identifier", type="identifier"
+    ),
     BatchDelimiterSegment=Ref("GoStatementSegment"),
     QuotedLiteralSegmentWithN=NamedParser(
         "single_quote_with_n", CodeSegment, name="quoted_literal", type="literal"
@@ -70,6 +91,7 @@
         Ref("NakedIdentifierSegment"),
         Ref("QuotedIdentifierSegment"),
         Ref("BracketedIdentifierSegment"),
+        Ref("HashIdentifierSegment"),
     ),
     LiteralGrammar=OneOf(
         Ref("QuotedLiteralSegment"),
diff --git a/test/fixtures/parser/tsql/temp_tables.sql b/test/fixtures/parser/tsql/temp_tables.sql
new file mode 100644
index 00000000000..fa34f4ce1ca
--- /dev/null
+++ b/test/fixtures/parser/tsql/temp_tables.sql
@@ -0,0 +1,9 @@
+SELECT  a
+		,b
+		,c
+FROM #UnionA;
+
+Select	d
+		,e
+		,f
+FROM ##UnionB;
diff --git a/test/fixtures/parser/tsql/temp_tables.yml b/test/fixtures/parser/tsql/temp_tables.yml
new file mode 100644
index 00000000000..f12a66a6360
--- /dev/null
+++ b/test/fixtures/parser/tsql/temp_tables.yml
@@ -0,0 +1,54 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 703e4f2adecf825f2059b2e2a4bf5c176acedc6ac378f659c9bbd7d14ca58585
+file:
+  batch:
+  - statement:
+      select_statement:
+        select_clause:
+        - keyword: SELECT
+        - select_clause_element:
+            column_reference:
+              identifier: a
+        - comma: ','
+        - select_clause_element:
+            column_reference:
+              identifier: b
+        - comma: ','
+        - select_clause_element:
+            column_reference:
+              identifier: c
+        from_clause:
+          keyword: FROM
+          from_expression:
+            from_expression_element:
+              table_expression:
+                table_reference:
+                  identifier: '#UnionA'
+  - statement_terminator: ;
+  - statement:
+      select_statement:
+        select_clause:
+        - keyword: Select
+        - select_clause_element:
+            column_reference:
+              identifier: d
+        - comma: ','
+        - select_clause_element:
+            column_reference:
+              identifier: e
+        - comma: ','
+        - select_clause_element:
+            column_reference:
+              identifier: f
+        from_clause:
+          keyword: FROM
+          from_expression:
+            from_expression_element:
+              table_expression:
+                table_reference:
+                  identifier: '##UnionB'
+  - statement_terminator: ;


Code review comments:
diff_hunk: @@ -51,14 +52,34 @@
         ),
         # T-SQL unicode strings
         RegexLexer("single_quote_with_n", r"N'([^'\\]|\\.)*'", CodeSegment),
+        RegexLexer(
+            "hash_prefix",
+            r"[#]+[a-zA-Z0-9_]+",
path: src/sqlfluff/dialects/dialect_tsql.py
text: @user1:
Isn’t this too greedy? Would this not identify ####table as a valid temp table?

@author:
I've adjusted to match for 1 or 2 instead.

@user2:
So `##table` is a temp table? Is there any reason `####table` isn’t then?

P. S. TSQL is weird 😁

@author:
Single hash is a session-scoped temp table. Double hash is a cross-session temp table - rarely good practice.