Naming syntax in 2005

[newtophp2000]

Here is a simple question. I am having difficulty accessing books
online at Microsoft so I thought I would ask here. What is the
standard naming syntax for Sqlserver 2005? Assuming I had the
following table, [proddb01].[details].[dbo].[daily_tranx], how would I
refer to it in the new version?

Thanks a lot!

[MGFoster]

ne**********@yahoo.com wrote:

Here is a simple question. I am having difficulty accessing books
online at Microsoft so I thought I would ask here. What is the
standard naming syntax for Sqlserver 2005? Assuming I had the
following table, [proddb01].[details].[dbo].[daily_tranx], how would I
refer to it in the new version?

What's the problem? If your example follows this syntax:

<server>.<database>.<owner>.<table>

then it should work.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Hugo Kornelis]

On 12 Mar 2006 14:26:55 -0800, ne**********@yahoo.com wrote:

Here is a simple question. I am having difficulty accessing books
online at Microsoft so I thought I would ask here. What is the
standard naming syntax for Sqlserver 2005? Assuming I had the
following table, [proddb01].[details].[dbo].[daily_tranx], how would I
refer to it in the new version?

Thanks a lot!

Hi newtophp2000,

In SQL Server 2000, namiing was <server>.<database>.<owner>.<table>. In
SQL Server 20005, this changes to <server>.<database>.<schema>.<table>.

The only change is that owner and schema are now seperated. On migration
from 2000 to 2005, all objects will automatically be placed in a schema
that matches the name of the owner. After that, you can decide to change
the schema, and you set the schema for new objects how you want.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

(ne**********@yahoo.com) writes:

Here is a simple question. I am having difficulty accessing books
online at Microsoft so I thought I would ask here. What is the
standard naming syntax for Sqlserver 2005? Assuming I had the
following table, [proddb01].[details].[dbo].[daily_tranx], how would I
refer to it in the new version?

The same as you always have done.

The example is a little funny. "proddb01" sounds like a database, but
it appears in the position of the server name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[newtophp2000]

My thanks to all who replied. So, if I understand it correctly, while
the syntax remains the same, the dbo part will really refer to a schema
within the database? (i.e., each user will have their own separate
area where they can create tables.)

(Erland, you are right. proddb01 is a server, we just name it that way
to distinguish it from test servers where we can play freely. Nothing
else runs on these servers other than the database; hence the name.)


Erland Sommarskog wrote:

The same as you always have done.

The example is a little funny. "proddb01" sounds like a database, but
it appears in the position of the server name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

[Erland Sommarskog]

(ne**********@yahoo.com) writes:

My thanks to all who replied. So, if I understand it correctly, while
the syntax remains the same, the dbo part will really refer to a schema
within the database? (i.e., each user will have their own separate
area where they can create tables.)

Yes and no, and maybe mainly no.

Schema is not really anything new in SQL Server. There has always been
schemas, and up to SQL 2000 any user had his own schema, and there was
no way to drop the schema for a user. Whether he actually could create
tables in tables in that schema depended on whether he had permissions
to create tables. The same was also true for roles - a role always came with
a schema.

The reason you did not always think of this as a schema, was because
the terminology talked about owner, and indeed, schema and ownership
were indeed the same thing.

In SQL 2005 schema and ownership are not tied to each other. This means
that you can create users without creating schemas for them, and the only
schema in the database used by an application will in many cases be DBO.

For more details on owner/schema separation, see
http://www.sommarskog.se/grantperm.html#ownerschema. (This is part
of a longer article.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx