x
login about faq Site discussion (meta-askssc)

Forcing schema objects in a new file group?

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

more ▼

asked May 17 '10 at 08:24 PM in Default

Manoj 1 gravatar image

Manoj 1
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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;
more ▼

answered May 18 '10 at 10:47 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k 56 63 87

Nice Solution. I hadn't considered preventing an object creation if it is not in the correct filegroup.

May 18 '10 at 12:42 PM CirqueDeSQLeil

Thanks a lot. Although not the ideal solution i was looking for(rather than the warning the object creation itself), this will do my job. Excellent! Cheers Manoj

May 19 '10 at 03:22 AM Manoj 1

@Manoj, no worries - glad to help...

May 19 '10 at 03:09 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 18 '10 at 01:11 AM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
3.9k 6 11 15

without doubt, create the object with the schema specified, every time you create an object

May 18 '10 at 03:22 AM Fatherjack ♦♦

Thanks. However Matt's solution will be ideal for me.

May 19 '10 at 10:36 PM Manoj 1
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 18 '10 at 08:41 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
62.2k 12 20 66

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x29
x28

asked: May 17 '10 at 08:24 PM

Seen: 1790 times

Last Updated: May 17 '10 at 08:24 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.