Have you ever wished that you could create a macro with TSQL that you use quite frequently, while working interactively via the query editor, well I have. So I was very pleased to see a nice feature within the new MS Management Studio. This is how it works.
You create a procedure with your TSQL. You create an association with the keyboard keys of your choice, from those available to you, via the dialog accessed via Tools\Options\Keyboard.
That's it, job done.
Wednesday, September 21, 2005
Monday, September 19, 2005
Watch those Schema's
One of the new features of SQL 2005 is the use of Schemas. These can be perceived of as adding value to the life of a DBA in various ways including the following two:-
One view of Schemas
One of the benefits derived from them is the ability to group (securable) objects together to facilitate ease of security management (allocation of permissions). The other end of the security management link is the use of principles (pre-authorised groupings of resources).
For example
A principle (Salesperson1) who belongs to a higher principle (SalesRole) can have select permission on all tables within the schema (SalesSchema), one of which is 'Sale' by the one entry in the system tables corresponding to the instruction
GRANT SELECT ON SCHEMA::SalesSchema TO SalesRole
This security permission can be exercised either by referring to the object name using the full schema path for the object
SELECT * FROM SalesSchema.Sale
or by ensuring that the principle is associated with the schema as part of its specification.
View two of schemas
Another benefit of them is that within a schema we don't have to worry about the order in which objects are created, they are checked as a group after their creation rather than individually. Do I hear a cheer from all those case tool users who have been affected by the errors caused by bad ordering of automatically generated TSQL, no, then maybe you should find out about case tools, they have many good aspects as well.
However, be careful about this one feature within SQL Server. Because although it is the case that all objects created within a schema at the time of schema creation, are checked as a group. There is no concept of dropping a group of objects belonging to a schema by dropping the schema itself. You must drop all objects within the schema first. Otherwise you will get an error message.
So ensure that you create the tables in the order Parent -> Child -> NextChild -> etc in your scripts, even if they are being created within a schema. Then if you need to remove them at a later date, using a dynamically generated script, you can order them correctly using their id.
One view of Schemas
One of the benefits derived from them is the ability to group (securable) objects together to facilitate ease of security management (allocation of permissions). The other end of the security management link is the use of principles (pre-authorised groupings of resources).
For example
A principle (Salesperson1) who belongs to a higher principle (SalesRole) can have select permission on all tables within the schema (SalesSchema), one of which is 'Sale' by the one entry in the system tables corresponding to the instruction
GRANT SELECT ON SCHEMA::SalesSchema TO SalesRole
This security permission can be exercised either by referring to the object name using the full schema path for the object
SELECT * FROM SalesSchema.Sale
or by ensuring that the principle is associated with the schema as part of its specification.
View two of schemas
Another benefit of them is that within a schema we don't have to worry about the order in which objects are created, they are checked as a group after their creation rather than individually. Do I hear a cheer from all those case tool users who have been affected by the errors caused by bad ordering of automatically generated TSQL, no, then maybe you should find out about case tools, they have many good aspects as well.
However, be careful about this one feature within SQL Server. Because although it is the case that all objects created within a schema at the time of schema creation, are checked as a group. There is no concept of dropping a group of objects belonging to a schema by dropping the schema itself. You must drop all objects within the schema first. Otherwise you will get an error message.
So ensure that you create the tables in the order Parent -> Child -> NextChild -> etc in your scripts, even if they are being created within a schema. Then if you need to remove them at a later date, using a dynamically generated script, you can order them correctly using their id.