When I think of improvements in the relational database engine in SQL Server 2005, the first thing that comes in my mind is schemas. SQL Server 2005 finally makes the owner-schema separation. Remember that in previous versions object owner was used in place of schema. In a database with hundreds of objects, developers always wanted to group objects into meaningful groups, i.e. namespaces. Nevertheless, with different owners you can easily get into troubles with broken ownership chains. Now, with properly implemented schemas, you can develop more natural data models. You should also not forget that permissions are now hierarchical.
Therefore, it is enough to give end user permissions on a schema, and the object inside this schema automatically inherit the permissions. All together, with schemas, you can develop applications that are more natural and you can administer permissions in a more productive way.
Now you have probably noticed that the most advertised feature in the SQL Server 2005 database engine is CLR integration. I will talk about it in a future blog. Imo schemas are even more important. Still, they are not announced so loudly by MS. I guess this is because some competitive systems properly implement schemas for years, so it would not be so commercially effective to announce schemas too loudly. However, if you need a reason for upgrading you application from SQL Server 2000 to 2005, schemas might well be the decisive factor.
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018