title: SQL Server Tables Named With dbo Schema
body: <p>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. </p>
  
  <p><strong>1) Where did this schema name come from?</strong> 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?</p>
  
  <p><strong>2) Schema name not "dbo"?</strong> 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?</p>
  
  <p><strong>3) User named dbo?</strong> 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?</p>
  
  <p><strong>4) To Use Schemas or Not - Can you not use them?</strong> 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?</p>
  
  <p>I've been using the book <em>Murach's SQL Server 2008 for Developers</em> and I haven't found it to be overly helpful in clearing up my confusion. I'm reading sentences like: <strong><em>"If you don't specify a schema when you create the objects, they're stored in the default schema."</em></strong> 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"?</p>