SQL Server: Can the same table exist in multiple schemas

I thought that the schemas are namespace instances and hence the same table created under 2 different schemas are 2 different objects from the perspective of the database. One of my colleagues claim that schemas are nothing but a security container, hence we can create the same table in different schemas开发者_StackOverflow. Is this true?

You are correct.

CREATE TABLE foo.T
(
c int
)

and

CREATE TABLE bar.T
(
c int
)

creates 2 separate objects. You could create a synonym bar.T that aliases foo.T though.

CREATE SCHEMA foo
GO
CREATE SCHEMA bar
GO
CREATE TABLE foo.T(c INT)
GO
CREATE SYNONYM bar.T FOR foo.T;
INSERT INTO foo.T VALUES (1);
SELECT * FROM bar.T;

They are 2 different objects, check the object_id

Yes, it can. Just try it

CREATE SCHEMA OneSchema AUTHORIZATION dbo;
CREATE SCHEMA TwoSchema AUTHORIZATION dbo;
CREATE TABLE dbo.SomeTable (foo int);
CREATE TABLE OneSchema.SomeTable (foo int);
CREATE TABLE TwoSchema.SomeTable (foo int);

A schema is both a securable and part of the "namespace"

I guess that you are trying to solve an issue by dividing data with the same data-structure between different tenants. You don't want to use different databases to reduce costs.

To my mind, it is better to use row-level security in this case. In this case, data are stored in one table, but one tenant can't access data that were created by another tenant.

You could read more in the next article - Row-Level Security

myschema.table1 is different than yourschema.table1