SQL Statement Format

[Gordon Padwick]

While attempting to become familiar with using C# to connect with databases,
I came across this SQL statement in one of Microsoft's Help topics:

command = new OldDbCommand("SELECT * FROM dbo.Customers " +
"WHERE Country = ? AND City = ?", connection);

I'm puzzled by "dbo.Customers". I don't recall seeing this type of thing
elsewhere. Can someone suggest the significance?

Gordon

 

[henry.lee.jr]

While attempting to become familiar with using C# to connect with databases,
I came across this SQL statement in one of Microsoft's Help topics:

command = new OldDbCommand("SELECT * FROM dbo.Customers " +
    "WHERE Country = ? AND City = ?", connection);

I'm puzzled by "dbo.Customers". I don't recall seeing this type of thing
elsewhere. Can someone suggest the significance?

Gordon

This is really a database question moreso than a C# question, but the
explanation is as such: In SQL Server, all objects have an owner when
you create them. DBO stands for database owner I believe, and often
you want your objects to be dbo.[object]. You can also create objects
like johndoe.Customers where the SQL Server user johndoe is the owner
of the object. This works for procedures and other objects as well.

When you create the object in SQL Server, you would say:

CREATE TABLE dbo.MyTableName ( .... )
or
CREATE TABLE johndoe.MyTableName ( .... )

When you build your SQL statement, the dbo prefix is not required as
long as you are already in the database you need to be in. But if you
are in a different database and need to make a cross database call, it
would be:

SELECT * FROM DatabaseName.dbo.TableName

You could also get away with:

SELECT * FROM DatabaseName..TableName

because dbo is usually the default ... but that is not a best
practice.

Make sense?

 

[henry.lee.jr]

While attempting to become familiar with using C# to connect with databases,
I came across this SQL statement in one of Microsoft's Help topics:

command = new OldDbCommand("SELECT * FROM dbo.Customers " +
    "WHERE Country = ? AND City = ?", connection);

I'm puzzled by "dbo.Customers". I don't recall seeing this type of thing
elsewhere. Can someone suggest the significance?

Gordon

This is really a database question moreso than a C# question, but the
explanation is as such: In SQL Server, all objects have an owner when
you create them. DBO stands for database owner I believe, and often
you want your objects to be dbo.[object]. You can also create objects
like johndoe.Customers where the SQL Server user johndoe is the owner
of the object. This works for procedures and other objects as well.

When you create the object in SQL Server, you would say:

CREATE TABLE dbo.MyTableName ( .... )
or
CREATE TABLE johndoe.MyTableName ( .... )

When you build your SQL statement, the dbo prefix is not required as
long as you are already in the database you need to be in. But if you
are in a different database and need to make a cross database call, it
would be:

SELECT * FROM DatabaseName.dbo.TableName

You could also get away with:

SELECT * FROM DatabaseName..TableName

because dbo is usually the default ... but that is not a best
practice.

Make sense?

in summary, standard syntax is [DatabaseName].[Owner].[Object]

SELECT * FROM Sales.dbo.Customers
EXEC Sales.dbo.GetCurrentCustomerList (procedure)
SELECT * FROM Accounting.dbo.Accounts

 

[Joe Cool]

This is really a database question moreso than a C# question, but the
explanation is as such: In SQL Server, all objects have an owner when
you create them. DBO stands for database owner I believe, and often
you want your objects to be dbo.[object]. You can also create objects
like johndoe.Customers where the SQL Server user johndoe is the owner
of the object. This works for procedures and other objects as well.

When you create the object in SQL Server, you would say:

CREATE TABLE dbo.MyTableName ( .... )
or
CREATE TABLE johndoe.MyTableName ( .... )

When you build your SQL statement, the dbo prefix is not required as
long as you are already in the database you need to be in. But if you
are in a different database and need to make a cross database call, it
would be:

SELECT * FROM DatabaseName.dbo.TableName

You could also get away with:

SELECT * FROM DatabaseName..TableName

because dbo is usually the default ... but that is not a best
practice.

Make sense?

in summary, standard syntax is [DatabaseName].[Owner].[Object]

SELECT * FROM Sales.dbo.Customers
EXEC Sales.dbo.GetCurrentCustomerList (procedure)
SELECT * FROM Accounting.dbo.Accounts

Or if the table is on a remote linked server:

[remoteservername].[databasename].[owner].[tablename]

 

[Alberto Poblacion]

in summary, standard syntax is [DatabaseName].[Owner].[Object]

While this was true in SQL Server 2000 and older, it has been obsolete
since SQL Server 2005. Now it is [DatabaseName].[Schema].[Object]. You can
create schemas independently of their owner, and you can have a single
schema be the default for several users. This decouples the database objects
from the users, since the users may come and go but we will generally want
to preserve the database objects.

When you execute a "Select * from [Object]", without specifying a
schema, the server will first try to find that object in the default schema
for the user that issues the query, and if it is not found, then it will
attempt to find the object in the [dbo] schema.

 

[henry.lee.jr]

in summary, standard syntax is [DatabaseName].[Owner].[Object]

   While this was true in SQL Server 2000 and older, it has been obsolete
since SQL Server 2005. Now it is [DatabaseName].[Schema].[Object]. You can
create schemas independently of their owner, and you can have a single
schema be the default for several users. This decouples the database objects
from the users, since the users may come and go but we will generally want
to preserve the database objects.

    When you execute a "Select * from [Object]", without specifying a
schema, the server will first try to find that object in the default schema
for the user that issues the query, and if it is not found, then it will
attempt to find the object in the [dbo] schema.

Unfortunately we're still working with SQLServer 2000 here (and the
same was true at my last gig), so wasn't aware of the schema details.
I had heard of schemas, but thought maybe they were just for grouping
and naming within SQL, didn't realize that they extended outside of
SQL via code references like that. Thanks.

 

[Jeff Johnson]

I had heard of schemas, but thought maybe they were just for grouping
and naming within SQL, didn't realize that they extended outside of
SQL via code references like that. Thanks.

There's no "outside of SQL" going on. Schemas do pretty much exactly what
you described in your first sentence. The difference is that in 2000 and
earlier, schema = user. (Oops, C# group...schema == user.) In 2005 schemas
can be created independently of users.