开发者

T-SQL schemata to organize code

I have a ms sql server database with a growing number of stored procedures and user defined functions and I see some need to organize the code better. My idea was to split sps and functions over several schemata. The default schema would hold the the sps called from the outside. The API of the database in other words. A second schema would hold internal code, that should not be called from the outside. I would probably do the same for tables: Some contain "raw" data, some hold precalculated data for optimizations, ...

As I have never used schema, I have sev开发者_如何学Ceral questions:

  • Does this make sense at all?
  • Are there any implications that I'm not aware of? For example performance issues when a sp in Schema A is using a table in Schema X?
  • Is it possible to restrict the "outer world" to use only sps in a certain schema? For example: User A is only allowed to call objects in schema A, but sps in schema A are still allowed to use tables in schema B?

As this question is somewhat subjective, I have marked it as "community wiki". Hope that is ok.


  • yes, it makes sense

  • no difference in performance if all schemas have the same owner (ownership chaining)

  • yes, permission schemas explicitly per client or have some check internally

We uses schemas to separate data, internals SPs, internal functions, and then SPs per client.

One advantage is we GRANT permissions on the schema not on objects, which is what I personally needed to clarify in my question, before we started using them.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜