CODEWITHSUNDEEP
sql-server
Siraj Samsudeen
Siraj Samsudeen

Reputation: 1690

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. Is this true?

Upvotes: 15

Views: 20436

Answers (5)

RredCat
RredCat

Reputation: 5421

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453618

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;

Upvotes: 18

gbn
gbn

Reputation: 432431

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"

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135111

They are 2 different objects, check the object_id

Upvotes: 2

Randy
Randy

Reputation: 16677

myschema.table1 is different than yourschema.table1

Upvotes: 1

Related Questions