When looking at SQL Server Management Studio (SSMS), which one is better and why? Are there cases where we should use one over the other?
I can’t tell the difference between them.
Advertisement
Answer
INFORMATION_SCHEMA is there for compatibility, it doesn’t expose all the information about objects on the instance.
sys however, fully exposes any relevant information, though you do need to write more SQL. INFORMATION_SCHEMA is “easier” to use for new users, as something like INFORMATION_SCHEMA.COLUMNS contains the names of the table, schema, the column and the data type in objects. To get that with sys you would have to use sys.schemas, sys.tables, sys.columns and sys.types.
There used to be a note on SQL Server’s documentation on the column TABLE_SCHEMA to suggest it could be wrong. This was changed earlier this year as I questioned it on their Github. The note now states that the information may be incomplete, not incorrect. Again, this is because INFORMATION_SCHEMA doesn’t expose all the information about the objects, which sys does.