Hi,
I need to create a new schema and a new file group on SQL 2005. Is there a possibility we can force all objects on the new schema be created on the new file group?
Thanks Manoj
Your best bet is through training and policy - if you are looking for a default to force just the new schema objects to be forced to the new filegroup.
Setting default filegroup will affect all new objects and force them to be created on the default filegroup.
You would therefore need to specify the filegroup at creation time of the new object.
In addition to setting the defualt filegroup as CirqueDeSQLeil says, you can alter the user to give them a default schema. This example is from Books Online:
USE AdventureWorks;
ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;
GO
But, just because you have a default schema doesn't mean that you can't override that schema when you create an object if that user has access to the ability to create objects on more than one schema.
Yes, there is a way to do exactly what you want with a DDL trigger. This one ensures objects in the dbo schema store their data in TestPartitionGroup1.
CREATE TRIGGER SchemaBasedFilegroupLock
ON DATABASE
FOR CREATE_TABLE, CREATE_INDEX, ALTER_INDEX
AS
DECLARE @schemaName [sysname]
DECLARE @allowedDataSpaceName [sysname]
SET @schemaName = 'dbo'
SET @allowedDataSpaceName = 'TestPartitionGroup1'
DECLARE @objectName [sysname]
SELECT @objectName =
CASE WHEN EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') = 'INDEX'
THEN
QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')) + '.' +
QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(max)'))
ELSE
QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')) + '.' +
QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'))
END
IF OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName)) = @schemaName AND EXISTS
(SELECT * FROM [sys].[indexes] WHERE [object_id] = OBJECT_ID(@objectName) AND [data_space_id] !=
(SELECT [data_space_id] FROM [sys].[data_spaces] WHERE [sys].[data_spaces].[name] = @allowedDataSpaceName))
BEGIN
DECLARE @errorMessage [nvarchar](MAX)
SET @errorMessage = 'Storage objects in the ''' + @schemaName + ''' schema can only place their data in the ''' + @allowedDataSpaceName + ''' data space.'
RAISERROR (@errorMessage, 16, 1)
ROLLBACK
END;
No one has followed this question yet.