-3

I don't understand what's the difference between table name / schema name? Because I have connected to AdventureWorks database and then I wrote this query. So in this does this HR.Employee mean a table or something else?

select * from HR.Employee
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Akash Mukherjee
  • 11
  • 1
  • 2
    `HR` is the schema, `Employee` the table. [Multipart names](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16#multipart-names) – Thom A Jul 09 '22 at 16:25
  • so what's the difference between them? – Akash Mukherjee Jul 09 '22 at 16:27
  • In theory you can have a table in more than one schema: `select * from dbo.Employee`, `select * from HR.Employee`, `select * from other.Employee` ( in reality don't confuse yourself this way unless you are sure you know what you are doing) – topsail Jul 09 '22 at 16:27
  • 2
    A schema hold tables, just like a database holds schemas. [a-walkthrough-of-sql-schema](https://www.sqlshack.com/a-walkthrough-of-sql-schema/) – topsail Jul 09 '22 at 16:28
  • If Schema holds table just like databases then why do we need them? We could have used Adventure_DB.Employee ? What's the problem here? I 'm really confused of Schema's and data bases – Akash Mukherjee Jul 09 '22 at 16:31
  • *"so what's the difference between them?"* Everything; you don't query `HR`, it's not an object. The only similarity, in truth, is that they exist within a database. – Thom A Jul 09 '22 at 16:32
  • *"We could have used Adventure_DB.Employee "* no, this would attempt to reference the *schema* `Adventure_DB`. Schemas are within a database, they don't replace them. You want want `Adventure_DB.HR.Employee` if you want to include the database name. – Thom A Jul 09 '22 at 16:33
  • 1
    Tables live in files inside a database. Why have files when you can just have tables? The reality is this: a schema is just a container, and every object lives in one (but most people just use `dbo.` whether they type it or not). – Aaron Bertrand Jul 09 '22 at 16:34
  • Anyway this is a research topic, not a technical Q&A and therefore a really bad fit for this site. Please read tutorials (like [this one](https://www.sqlservertutorial.net/sql-server-basics/sql-server-create-schema/)) and read more about how to ask questions [here](https://sqlblog.org/ask). – Aaron Bertrand Jul 09 '22 at 16:36
  • @AaronBertrand So in SQL we have a database inside which are schemas which contains the tables . – Akash Mukherjee Jul 09 '22 at 16:38
  • Not just tables, @AkashMukherjee, but other objects too, such as (but not limited to) Procedures, Views, and Functions. – Thom A Jul 09 '22 at 16:48
  • 1
    In **SQL Server** - this is **very much** vendor-/product-specific, and **NOT** a general concept of **SQL** - the Structured Query Language - per se ... – marc_s Jul 09 '22 at 16:48
  • 1
    Think of schema as a namespace. – Peter L - MSFT Jul 09 '22 at 18:03

1 Answers1

-1

I think the question is valid and far from trivial. Here's a big picture perspective.

In general, a database object is identified by a 4 part name. The "servername" is really an instance name. Brackets are optional if the naming follows the rules for naming identifiers. If a named instance, brackets are required because "\" is not legal. A default instance using a FQDN for the machine also needs brackets because of the periods. You don't have to specify all the parts if the defaults are okay; however, it is a best practice to at least specify the schema_name even if it is dbo. I added a links re the 4 part name, identifiers, and ownership chaining.

[server_name].[database_name].[schema_name].[object_name]

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188676(v=sql.105)?redirectedfrom=MSDN

To simplify, think of a "servername" as an instance that contains databases. Access is controlled via server logins and login mappings to database users.

The database has objects to store and manipulate data. The security to access any objects in the database is defined in the database. A 4 part name used in a database can be used to access an object in a database on a different instance. A 3 part name can be used to access a object in a different database on the same server. In either case, a distributed transaction is used. (We have code that does this and it's a nightmare to support. Joining tables from different databases on different servers or the same server each has it's support problems.)

Now for schema_name. Suppose you have two related businesses that share a large amount of data and workflow but require guaranteed isolation of some data. A contrived example: a court application that stores data for both the DA and the public defender. They don't trust each other, but lets say they agree to not use separate servers and separate databases because they trust IT to isolate and protect their data. And IT is more than happy to avoid the need to code the application to use 2 databases which might avoid extra work (e.g., replication, distributed transactions, BizTalk, Mulesoft,...). A developer might rather use linked servers and distributed queries to move the complexity from development to support. Instead, we can use one database with a "DA" schema for DA tables and procedures while using a "PD" schema for public defender tables and code. Access to each schema can be controlled.

If everything was in the default "dbo" schema, any "dbo" procedure would have access to all "dbo" tables because of ownership chaining. Ownership chaining does not apply across schemas. The DA coder might reference a PD table or procedure in a DA procedure, but they won't have access to PD unless it's specifically granted. The schema allows isolation of groups of objects from each other and adds a layer of security to control access from one schema to another. If you use schemas thinking it's just a container to organize objects, it becomes quickly painful because of the lack of ownership chaining. As with most things SQL, it depends. This avoids pain if this is what you need to meet security requirements.

If you want a really short answer, the HR schema exists to secure HR data from unauthorized access by code in other schemas.

Randy in Marin
  • 1,108
  • 4
  • 9