March 4, 2015 at 6:34 am
Hi,
Can we create the Database with two schema and having the separate file group for each schema.
March 4, 2015 at 9:19 am
If I understand correctly, you want objects in a particular schema to go to a dedicated filegroup. Unfortunately, there's no such feature.
Your best bet is a DDL trigger (example here: https://ask.sqlservercentral.com/questions/7706/forcing-schema-objects-in-a-new-file-group.html).
-- Gianluca Sartori
March 4, 2015 at 9:53 am
nilesh_dalvi (3/4/2015)
Hi,Can we create the Database with two schema and having the separate file group for each schema.
You cannot, but ...
Why would you like to do that? Are you trying to accomplish some type of performance gain ?
If you have fast disks and you want improve I/O performance on heavy queries via FGs, relocate critical table(s) to an specific ndf file, which has been already allocated to an specific FG. That FG will be hosted on the fast LUN/disk and you may see some I/O improvement. You can also do that for Indexes.
Schemas are more useful for security purposes, like segregate the people who access some tables, not really for performance.
March 4, 2015 at 11:12 am
Why he cannot? He can.
1. Create 2 schemas
2. Alter them and transfer all needed tables.
3. Create 2 Fg's.
4. Create files for them.
5. For all tables in these schemas: create clustered index with drop_existing ON FG_NAME
March 4, 2015 at 11:16 am
SQL Guy 1 (3/4/2015)
Why he cannot? He can.1. Create 2 schemas
2. Alter them and transfer all needed tables.
3. Create 2 Fg's.
4. Create files for them.
5. For all tables in these schemas: create clustered index with drop_existing ON FG_NAME
True. But that's not exactly what he asked, at least, not how I read it.
You cannot directly assign FG to schema. You can, as you said, assign schemas to a tables and then, as I posted, assign ndf files inside the FGs. Not exactly 1:1 step, not directly.
And still, it would be beneficial for the OP to mention what's he is trying to accomplish doing this. He does not mention anything about his disk's layout. That's very important to understand 1st if he wants to gain any performance benefit from this. If he has one drive only or all LUNs are same tier, there is little or no benefit of doing all this and most likely, it will require a major app redesign. Changing schemas will affect how TSQL queries resolve table names. It can also break connectivity if permissions are not properly created for new schemas. So it's a lot of work and more complicated than using default schemas.
March 4, 2015 at 10:35 pm
Thanks you all
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply