以 dbo 架构命名的 SQL Server 表 [英] SQL Server Tables Named With dbo Schema
问题描述
我一直发现 SQL Server 架构非常混乱.也许你们可以帮我澄清一些困惑.我正在使用 SQL Server 2008 Express 和 MS SQL Server 2008 Management Studio，但我认为我的问题与任何 MS SQL Server 2008 版本相关.我一直在不同的服务器上试验不同的数据库和不同的 SQL Server 实例.例如，我一直在我们公司的服务器上做一些工作，但我也一直在我的共享网络托管服务器上使用一些数据库.这可能是我陷入一些混乱的部分原因.
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) 这个架构名称从何而来? 我想当我几周前第一次开始使用 SQL Server 时，我没有看到任何附加到表名称的架构名称.当导入数据库或使用 MS Access Migration Assistant 时，我开始看到它们.是否可以根本不使用模式?它们在某些情况下不会出现吗?
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) 架构名称不是dbo"? 在最近的一个项目中，我的架构显示为 MyLongUserName.tablename 而不是 dbo.tablename.我很困惑为什么有时用户帐户似乎默认为 dbo 架构，而有时它被分配给与用户帐户同名的架构.这与使用 NT 身份验证和 SQL Server 身份验证登录有什么关系吗?
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) 用户名为 dbo? 我不记得在我当前使用的数据库中创建了名为 dbo 的用户，但我在用户列表中看到了它.用户帐户dbo"是否必须存在才能存在模式dbo"?用户名和架构名称是否总是并行的?
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) 是否使用架构 - 您可以不使用它们吗?您是否将架构用于小型项目，例如用于基本网站的两表或三表数据库?你能完全避免使用模式还是只使用默认的dbo"模式?即使在非常小的项目中，您也会创建额外的架构并为其/他们分配某些权限和用户帐户吗?
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?
我一直在使用Murach 的 SQL Server 2008 for Developers 这本书，但我没有发现它对解决我的困惑有太大帮助.我正在读这样的句子:如果您在创建对象时没有指定架构，它们将存储在默认架构中." 等等，怎么做我知道什么是默认架构"?我们是在谈论用户帐户的默认架构还是每个数据库都有默认架构"?它总是dbo"吗?为什么当我创建对象时，它会将它们分配给架构 MyLongUserName 而不是dbo"?
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"?
推荐答案
您可能想先阅读以下内容:SQL Server 模式有什么好处?
You might want to start by reading this: What good are SQL Server schemas?
从 SQL Server 2005 开始，架构与用户分离http://msdn.microsoft.com/en-us/library/ms190387.aspx.
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 对象的名称有 4 个部分
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 - 隐含的当前服务器
- schema.objectname - 隐含的当前数据库
- objectname - 隐含的默认架构.可以为每个用户分配一个默认架构，但默认情况下这将是dbo"
"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
如果您从较旧的 SQL Server 2000 dbs 安装迁移到 2005 或更高版本，您可能会带来模式命名为用户，因为用户拥有"这些表.
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.
这篇关于以 dbo 架构命名的 SQL Server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
