Structure of T-SQL tables

Question

I am switching from MySQL to SQL Server for a new job and I have encountered something I don't quite understand as I haven't encountered something like this before.

I am using the WideWorldImporters Microsoft Sample DB for reference.

Looking at the table structures I can see that it is SCHEMA_NAME.X.TABLE_NAME

Example

[WideWorldImporters].[Application].[Cities]

In MySQL this would just be schema.tablename

Example

[WideWorldImporters].[Cities]

What is this middle part (the [Application] part in the example) exactly? I can't seem to find anything about it but that's likely because I don't know what it is to look for. It's obviously important as a select query won't work if it's removed. Can anyone explain or even name this so I can research it?

The three part naming is **not** `SCHEMA_NAME.X.TABLE_NAME` - it is `DATABASE_NAME.SCHEMA_NAME.TABLE_NAME` - just use `SCHEMA_NAME.TABLE_NAME` unless you are actually doing cross database queries — Martin Smith, Aug 31 '21 at 08:18
This gets a little confusing, but what MySQL calls a "schema" SQL Server calls a database. SQL Server has its own, separate notions of schema that is loosely tied to ownership. So `WorldWideImports.Application.Cities` is the table `Cities` in the schema `Application` in the database `WorldWideImports`. Three-part names should not normally be used since databases should ideally not depend on each other. (They should especially not be used within the database *itself*, since then code will break in a most ugly and unnecessary way if the DB is renamed.) — Jeroen Mostert, Aug 31 '21 at 08:19
Relevant Documentation: [Transact-SQL Syntax Conventions (Transact-SQL) - Multipart Names](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver15#multipart-names) — Thom A, Aug 31 '21 at 08:22
@jeroenMostert Ah Ok that makes sense thank you ! I suspect this wont be the last puzzling difference between the two that I will encounter. — Jamie McManus, Aug 31 '21 at 08:45

score 2 · Accepted Answer · answered Aug 31 '21 at 13:36

Microsoft uses a three-part naming pattern of Database_Name.Schema_Name.Table_Name that differs from the MySQL convention. In general terms, a MySQL "schema" is roughly equivalent to a SQL Server "database."

In practice, one should avoid explicitly referencing the database name unless specifically needed.

Microsoft documentation: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver15#multipart-names

score 0 · Answer 2 · answered Apr 24 '22 at 23:10

I would like to add some extra details on paneerakbari Accepted Answer

First: About SQL Server according to Microsoft Doc Transact-SQL has different formats to define Object name (Table name), Here are supported formats

Object reference format	Description
server.database.schema.object	Four-part name
server.database..object	Schema name is omitted.
server..schema.object	Database name is omitted
server...object	Database and schema name are omitted.
database.schema.object	Server name is omitted.
database..object	Server and schema name are omitted.
schema.object	Server and database name are omitted.
object	Server, database, and schema name are omitted.

For case you can use Two Formats, database.schema.object OR database..object Examples:

[WideWorldImporters].[Application].[Cities]

OR

[WideWorldImporters]..[Cities]

Second: About MySQL, the Schema name is Synonymous with the Database name So There is two SQL formats database.object OR object, Here are useful links describe Whats differents between Schema and Database in MySQL Stackoverflow Question And TutorialsPoint Question

Structure of T-SQL tables

2 Answers2