SQL Server Tables Named With dbo Schema

Question

I've been finding SQL Server schemas to be very confusing. Maybe you guys can help me clear up some confusion. I'm using SQL Server 2008 Express and MS SQL Server 2008 Management Studio but I think my questions are relevant to any MS SQL Server 2008 version. I've been experimenting with different databases and different instances of SQL Server on different servers. For example, I've been doing some work on our company server but I've also been working quite a bit with some databases on my shared web hosting server. This might be part of the reason I've entered into some of the confusion.

1) Where did this schema name come from? I thought when I first started messing with SQL Server several weeks ago I didn't see any schema names appended to the table names. I started seeing them when a database was imported or when I used the MS Access Migration Assistant. Is it possible to not use schemas at all? Do they not show up under some circumstances?

2) Schema name not "dbo"? On a recent project my schemas have been showing up as MyLongUserName.tablename instead of dbo.tablename. I'm confused as to why sometimes a user account appears to default to the dbo schema and other times it's assigned to a schema with the same name as the user account. Does this have anything to do with logging in using NT Authentication versus SQL Server authentication?

3) User named dbo? I don't remember creating a user named dbo in the database that I'm currently working in but I see it in the list of users. Does the user account "dbo" have to exist for the schema "dbo" to exist? Do usernames and schema names always parallel?

4) To Use Schemas or Not - Can you not use them? Do you use schemas for small projects such as a two or three table database for a basic website? Can you avoid using schemas entirely or simply use only the default "dbo" schema? Would you create additional schema(s) and assign certain permissions and user accounts to it/them even on very small projects?

I've been using the book Murach's SQL Server 2008 for Developers and I haven't found it to be overly helpful in clearing up my confusion. I'm reading sentences like: "If you don't specify a schema when you create the objects, they're stored in the default schema." Wait, how do I know what "the default schema" is? Are we talking about the user account's default schema or is there "a default schema" for every database? Is it always "dbo"? Why when I create objects is it assigning them to schema MyLongUserName instead of "dbo"?

Answer 1

You might want to start by reading this: What good are SQL Server schemas?

From SQL Server 2005 onwards, schemas were separated from users http://msdn.microsoft.com/en-us/library/ms190387.aspx.

Prior to that, each user owns several tables, which will be in their "space". That "space" is now a schema, which is a way to group tables.

SQL Server Objects have 4 parts to their names

• server.database.schema.objectname

Whenever you omit one or more of them, you are naming it from the right

• database.schema.objectname - implied current server
• schema.objectname - implied current database
• objectname - implied default schema. each user can be assigned a default schema, but by default this will be "dbo"

"dbo" is a special schema, it is the database-owner. It exists in every database, but you can add schemas (like folders) to databases

If you migrate from older installations of SQL Server 2000 dbs to 2005 or beyond, you may bring along the schemas-named-as-users, because the users "own"ed the tables.