SELECT * should be avoided because it releases control of the returned columns and could therefore lead to errors and potentially to
performance issues.
Noncompliant code example
SELECT * -- Noncompliant
FROM persons
WHERE city = 'NEW YORK'
Compliant solution
SELECT firstname, lastname
FROM persons
WHERE city = 'NEW YORK'
Exceptions
The following cases are ignored by this rule:
-
SELECT from temporary tables: SELECT * FROM #temp1
-
SELECT using common table expressions: WITH A AS (SELECT C1 FROM T1) SELECT * FROM A;
- Inside another
SELECT: SELECT C1 FROM T1 WHERE C2 IN (SELECT * FROM T2)
- Inside
INSERT: INSERT INTO T1 SELECT * FROM T2
- Inside
CREATE TABLE: CREATE TABLE T1 WITH (C1 = C2) AS SELECT * FROM T2
-
SELECT from rowset providers: SELECT * FROM OPENXML (@idoc, '/ROOT/Customer',1)
-
SELECT INTO: SELECT * INTO NEW_TABLE FROM T1
-
SELECT from variable table: SELECT * FROM @table1
-
SELECT from derived table: SELECT A.* FROM (SELECT X FROM T1) A INNER JOIN B ON A.X = B.X
