Interview Question - Can we have 2 tables with same name in a SQL database ?

This question was put forward in the recent interviews that I was part of. The interviewee had 3+ years of experience in SQL.

Interviewer - What is collation in SQL server ?

Job Aspirant - It defines what all characters are supported  by SQL and in what order they will be sorted. 

Interviewer - Usually, what is the default Collation for SQL server ?

Job Aspirant  - SQL_Latin1_General_CP1_CI_AS

Interviewer - What does CI stands for ?

Job Aspirant - Case Insensitive

Interviewer - Can we have different collation for a SQL server and Database created on it?

Job Aspirant - Yes.

Interviewer - Suppose I have a table X in the database Y then is there any difference between

Select * from x  & Select * from X ?

Job Aspirant  - No, there isn't. Both will point to same table.

Till this point it was all theory so aspirant was up for it and then came the conceptual question which was  ...

Can we have 2 tables with same name in a SQL database ?

When this question was asked, in a fraction of seconds Aspirant rallied "NO !! It is not possible." 

To this Interviewer replied "Well my dear friend you are wrong. We can create 2 tables with the same name in the same schema and in the same database."

Let me show you how ...

Let us Create one TEST database using 

CREATE DATABASE TestCollation

The collation for the database is SQL_Latin1_General_CP1_CI_AS which is default for my SQL server.

Lets try to execute create statements for two tables with same names one after another against TestCollation database that we just created.

CREATE TABLE Test
(ID TINYINT
)
CREATE TABLE TEST
(ID TINYINT
)

We got an error

Msg 2714, Level 16, State 6, Line 1

There is already an object named 'Test' in the database.

This means that we can not have 2 tables with same name in a one database. At this point Job Aspirant had a winning smile on his face and his look which suggested "see I told you" . 

But interviewer wasn't done yet...It is not over till it's over. 

The interviewer said ..Let us drop the table that got created 

DROP TABLE Test;

Now, let's try to change default collation of the TestCollation database using

ALTER DATABASE TestCollation
COLLATE SQL_Latin1_General_CP1_CS_AS ;

As you might have noticed I have changed CI to CS in the collation which signifies Case Sensitive..

Now let us try to create tables once again 

CREATE TABLE Test(ID TINYINT)
CREATE TABLE TEST(ID TINYINT)

To Interviewee's surprise tables were created without any issues that too in the same schema and same database. And it was time for Interviewer to wear a naughty smile with see I told you looks. 

Bottom line  - Yes, Tables with same name can exists in the same database provided that database has case sensitive collation set on it.