384

At least on my local instance, when I create tables, they are all prefixed with "dbo.". Why is that?

AakashM
  • 62,551
  • 17
  • 151
  • 186
Pablo Fernandez
  • 279,434
  • 135
  • 377
  • 622
  • 24
    it is [Database Owner](http://msdn.microsoft.com/en-us/library/aa905208%28v=sql.80%29.aspx) – AminM May 10 '13 at 12:46

6 Answers6

298

dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.

Daniel
  • 4,847
  • 1
  • 23
  • 19
  • 31
    As a best practice, I always add the "dbo." prefix even though it is not necessary. Most of the time in SQL it's good to be explicit. – SurroundedByFish Jun 30 '09 at 13:56
  • 5
    @SurroundedByFish: Probably not a best practice, but I could be wrong as I'm not a SQL expert. http://stackoverflow.com/a/769639/602245 – Brett Jan 20 '12 at 19:19
  • 16
    [This article](http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server) from a [different answer](http://stackoverflow.com/a/10118197/39396) claims that it is in fact a best practice: "The code would not have to use the fully qualified name, though there is a slight performance gain in doing so and is considered a best practice. " – Carl G Oct 09 '12 at 16:33
  • 7
    Also, [the answer](http://stackoverflow.com/a/769639/602245) you linked also recommends including the "dbo." so that the optimizer doesn't have to look up the schema. – Carl G Oct 09 '12 at 16:42
  • 9
    dbo is not a good practice. Create your own schema and always use it. dbo exists only a a migration trick so pre-SQL Server 2005 continues to work. Yes, always use a schema for perf. No, not the migration one (e.g. dbo). – David Betz Nov 05 '15 at 22:02
  • @SurroundedByFish Can you provide an example where *Most of the time* fits? – Imad Sep 06 '16 at 09:06
  • @Imad: I'm made a general observation about many best practices in SQL coding. Being explicit is usually good. The example here is that using "dbo" is being more explicit (and is good). Another example is not using SELECT * and instead listing out all column names. Listing the columns is more explicit and is a well known best practice. – SurroundedByFish Sep 07 '16 at 16:42
  • 1
    I'm made a general observation about many best practices: they serve a purpose. If you can't make a case for why you've chosen to do X instead of Y, don't tell me X is a "best practice". Based on Manngo's answer I would guess that a "schema" is simply a namespace, so its name should be based somehow on the purpose of the tables within it. (Since "dbo" is the default, perhaps it's analogous to the global namespace in other programming languages?) – Qwertie Mar 11 '21 at 21:55
105

If you are using Sql Server Management Studio, you can create your own schema by browsing to Databases - Your Database - Security - Schemas.

To create one using a script is as easy as (for example):

CREATE SCHEMA [EnterSchemaNameHere] AUTHORIZATION [dbo]

You can use them to logically group your tables, for example by creating a schema for "Financial" information and another for "Personal" data. Your tables would then display as:

Financial.BankAccounts Financial.Transactions Personal.Address

Rather than using the default schema of dbo.

StepUp
  • 36,391
  • 15
  • 88
  • 148
Fenton
  • 241,084
  • 71
  • 387
  • 401
  • Do you know if that causes any problem when we use Entity framework? – Emil May 24 '13 at 02:36
  • 7
    You can use schemas with Entity Framework - even with code first if you like: `[Table("Customer", Schema = "MySchema")]` – Fenton May 24 '13 at 08:36
  • 1
    Does the 'AUTHORIZATION [dbo]' grant the same permissions as dbo on the schema, or do I still need to grant permissions to the users? – Mark Micallef Apr 03 '14 at 03:14
30

Microsoft introduced schema in version 2005. For those who didn’t know about schema, and those who didn’t care, objects were put into a default schema dbo.

dbo stands for DataBase Owner, but that’s not really important.

Think of a schema as you would a folder for files:

  • You don’t need to refer to the schema if the object is in the same or default schema
  • You can reference an object in a different schema by using the schema as a prefix, the way you can reference a file in a different folder.
  • You can’t have two objects with the same name in a single schema, but you can in different schema
  • Using schema can help you to organise a larger number of objects
  • Schema can also be assigned to particular users and roles, so you can control access to who can do what.

You can generally access any object from any schema. However, it is possible to control which users have which access to particular schema, so you can use schema in your security model.

Because dbo is the default, you normally don’t need to specify it within a single database:

SELECT * FROM customers;
SELECT * FROM dbo.customers;

mean the same thing.

I am inclined to disagree with the notion of always using the dbo. prefix, since the more you clutter your code with unnecessary detail, the harder it is to read and manage.

For the most part, you can ignore the schema. However, the schema will make itself apparent in the following situations:

  1. If you view the tables in either the object navigator or in an external application, such as Microsoft Excel or Access, you will see the dbo. prefix. You can still ignore it.

  2. If you reference a table in another database, you will need its full name in the form database.schema.table:

    SELECT * FROM bookshop.dbo.customers;

  3. For historical reasons, if you write a user defined scalar function, you will need to call it with the schema prefix:

    CREATE FUNCTION tax(@amount DECIMAL(6,2) RETURNS DECIMAL(6,2) AS
BEGIN
    RETURN @amount * 0.1;
END;
GO
SELECT total, dbo.tax(total) FROM pricelist;

    This does not apply to other objects, such as table functions, procedures and views.

You can use schema to overcome naming conflicts. For example, if every user has a personal schema, they can create additional objects without having to fight with other users over the name.

Manngo
  • 14,066
  • 10
  • 88
  • 110
25

It's new to SQL 2005 and offers a simplified way to group objects, especially for the purpose of securing the objects in that "group".

The following link offers a more in depth explanation as to what it is, why we would use it:

Understanding the Difference between Owners and Schemas in SQL Server

AminM
  • 1,658
  • 4
  • 32
  • 48
Jaans
  • 4,598
  • 4
  • 39
  • 49
1

Something from Microsoft (Documentation)

The dbo user is a special user principal in each database. All SQL Server administrators, members of the sysadmin fixed server role, sa login, and owners of the database, enter databases as the dbo user. The dbo user has all permissions in the database and cannot be limited or dropped. dbo stands for database owner, but the dbouser account is not the same as the db_owner fixed database role, and the db_owner fixed database role is not the same as the user account that is recorded as the owner of the database.
The dbo user owns the dbo schema. The dbo schema is the default schema for all users, unless some other schema is specified. The dbo schema cannot be dropped. The dbo user owns the dbo schema. The dbo schema is the default schema for all users, unless some other schema is specified. The dbo schema cannot be dropped.

Irf
  • 4,285
  • 3
  • 36
  • 49
0

DBO is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace. As a best practice, I always add the "DBO." prefix even though it is not necessary. Most of the time in SQL it's good to be explicit.

suraj
  • 1