December 21, 2012 at 6:38 am
Hi,
Since i am newbie to SQL Server, i am finding some new things.
1. I have a table in dbo schema named as Demo
2. I tried creating SP with the same name dbo and Demo.
i thought that since the objects are different i.e. table are SP, SQL Server should allow me to do it but it did not allowed me to do it. I want to know what is the reason behind this.
December 21, 2012 at 6:49 am
Shadab Shah (12/21/2012)
Hi,Since i am newbie to SQL Server, i am finding some new things.
1. I have a table in dbo schema named as Demo
2. I tried creating SP with the same name dbo and Demo.
i thought that since the objects are different i.e. table are SP, SQL Server should allow me to do it but it did not allowed me to do it. I want to know what is the reason behind this.
Please see the error message which is self-explanatory 🙂
December 21, 2012 at 6:49 am
Shadab Shah (12/21/2012)
Hi,Since i am newbie to SQL Server, i am finding some new things.
1. I have a table in dbo schema named as Demo
2. I tried creating SP with the same name dbo and Demo.
i thought that since the objects are different i.e. table are SP, SQL Server should allow me to do it but it did not allowed me to do it. I want to know what is the reason behind this.
I'll try life example:
Lets say you have 2 children. 1 boy and 1 girl. Would you give them both the same name?
:hehe:
December 21, 2012 at 6:55 am
That is an expected error that you will get used to once you get started with SQL.
object names must be unique, otherwise the system cannot map your queries to the objects they reference.
the underlying tables under the system view sys.objects have a unique constraint against schema_name + objectname.
as a result, no two objects can have the the same combination.
you will have to create one or the other (table or procedure) with a different name.
Lowell
December 21, 2012 at 7:02 am
Al right .But they are totally different object . I mean to say one is a table and another SP. So that means you are saying that i cannot give the same name to table & SP . What are other objects along with table & SP that i cannot name the same.
December 21, 2012 at 7:19 am
Shadab Shah (12/21/2012)
Al right .But they are totally different object . I mean to say one is a table and another SP. So that means you are saying that i cannot give the same name to table & SP . What are other objects along with table & SP that i cannot name the same.
Back to my sample, boy and girl would be totally different too (I guess you know some visible differences), still you wouldn't name them the same at least in one family. Because it would lead to any kind of practical problems...
ALL database objects must have unique names within the same schema.
Views, stored procs, tables, indexes, constraints, UDF's, triggers etc.
You can have the same object name in different schemas.
Actually, why does it bother you so much?
December 21, 2012 at 7:31 am
Shadab Shah (12/21/2012)
Al right .But they are totally different object . I mean to say one is a table and another SP. So that means you are saying that i cannot give the same name to table & SP . What are other objects along with table & SP that i cannot name the same.
Lowell has already answered your question, please reread his post.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2012 at 7:48 am
Shadab Shah (12/21/2012)
Al right .But they are totally different object . I mean to say one is a table and another SP. So that means you are saying that i cannot give the same name to table & SP . What are other objects along with table & SP that i cannot name the same.
they might be different TYPES of objects, but they are stored in the same table (sys.objects).
take a look at this article from MS:
http://msdn.microsoft.com/en-us/library/ms190324.aspx
and notice the TYPE column for all the different object types.
All those objects must be uniquely named across the entire database...not unique on type.
Lowell
December 21, 2012 at 9:47 am
There are two major points here.
1) Think about why the names can't be the same. Consider the following.
select * from Demo
exec Demo
Is the select referring to the proc or the table? What about the exec statement?
2) Naming in sql server is just as important as it is in any programming language. You want the name to give you some sort of indication of what it is. Typically a proc performs an action (like a method in programming) so often having a verb in the name gives it some clarity. If you try to name everything the same you will drive yourself nuts trying to figure out what is what.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 10:04 am
reminds me of horrors like this:
CREATE TABLE [SELECT]([SELECT] INT IDENTITY(1,1) NOT NULL PRIMARY KEY )
SELECT [SELECT].[SELECT] FROM [SELECT] WHERE [SELECT] = 1
Lowell
December 21, 2012 at 10:18 am
Lowell (12/21/2012)
reminds me of horrors like this:
CREATE TABLE [SELECT]([SELECT] INT IDENTITY(1,1) NOT NULL PRIMARY KEY )
SELECT [SELECT].[SELECT] FROM [SELECT] WHERE [SELECT] = 1
OMG my eyes are bleeding!!! That is an awesome representation Lowell. Kudos!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 10:31 am
Lowell (12/21/2012)
reminds me of horrors like this:
CREATE TABLE [SELECT]([SELECT] INT IDENTITY(1,1) NOT NULL PRIMARY KEY )
SELECT [SELECT].[SELECT] FROM [SELECT] WHERE [SELECT] = 1
CREATE TABLE [FROM]([SELECT * FROM] INT, [WHERE = 1] CHAR(1) );
SELECT [SELECT * FROM] FROM [FROM] WHERE [WHERE = 1] = 1;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply