Sfrgttk

Optimising a list searching algorithm

Wrapping homogeneous Python objects

How does 取材で訪れた integrate into this sentence?

Is it insecure to send a password in a `curl` command?

Have the tides ever turned twice on any open problem?

A Ri-diddley-iley Riddle

Is there a hypothetical scenario that would make Earth uninhabitable for humans, but not for (the majority of) other animals?

Can a wizard cast a spell during their first turn of combat if they initiated combat by releasing a readied spell?

Relation between independence and correlation of uniform random variables

Do native speakers use "ultima" and "proxima" frequently in spoken English?

Does multi-classing into Fighter give you heavy armor proficiency?

Existence of a celestial body big enough for early civilization to be thought of as a second moon

Print last inputted byte

Are dual Irish/British citizens bound by the 90/180 day rule when travelling in the EU after Brexit?

Maths symbols and unicode-math input inside siunitx commands

How to define limit operations in general topological spaces? Are nets able to do this?

Turning a hard to access nut?

How is the partial sum of a geometric sequence calculated?

What (if any) is the reason to buy in small local stores?

Can a medieval gyroplane be built?

Geography in 3D perspective

Writing in a Christian voice

Comment Box for Substitution Method of Integrals

Knife as defense against stray dogs

Automatically have Nonclustered indexes in a Different Filegroup

How to move all non-unique and non-clustered indexes to a different filegroup?general memory requirements for sql server 2008 r2Restore backup to different filegroupError: 'PRIMARY' filegroup is fullMultiple filegroups vs. multiple files in a single filegroupwhat would be the right permission to allow everything else but overwriting or creating a database?Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?How to force users to specify a filegroup when creating tables/indexesCannot remove filegroup with no files associatedFilegroup per Table partition - remove filegroup and re-add filegroup

4

0

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

sql-server performance sql-server-2016 filegroups

share|improve this question

edited Feb 11 at 15:49

Martin Smith51

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You can apply the following script in Post Deployment: stackoverflow.com/questions/55187160/…
  
  – Joe Smith
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can apply the following script in Post Deployment: stackoverflow.com/questions/55187160/…
  
  – GregThomas582
  2 mins ago
  

  
  
  
  

add a comment | 

4

0

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

sql-server performance sql-server-2016 filegroups

share|improve this question

edited Feb 11 at 15:49

Martin Smith51

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You can apply the following script in Post Deployment: stackoverflow.com/questions/55187160/…
  
  – Joe Smith
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can apply the following script in Post Deployment: stackoverflow.com/questions/55187160/…
  
  – GregThomas582
  2 mins ago
  

  
  
  
  

add a comment | 

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

sql-server performance sql-server-2016 filegroups

share|improve this question

edited Feb 11 at 15:49

Martin Smith51

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

share|improve this question

edited Feb 11 at 15:49

Martin Smith51

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

share|improve this question

edited Feb 11 at 15:49

Martin Smith51

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

asked Feb 11 at 5:48

Martin Smith51Martin Smith51

434

2 Answers
2

active

oldest

votes

4

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

share

edited Feb 11 at 7:09

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  interesting, how would I write the index maintenance script?
  
  – Martin Smith51
  Feb 11 at 7:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will share script
  
  – Rajesh Ranjan
  Feb 11 at 7:54
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  >>>So moving clustered index means you are moving table to different file group<<< If you'll try to use CREATE INDEX ... ON<ANOTHER_FG> syntax it won't move LOB data to ANOTHER_FG
  
  – sepupic
  Feb 11 at 9:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, I would like to have it automatically move it to appropriate filegroup when writing ddl statement
  
  – Martin Smith51
  Feb 11 at 15:49
  

  
  
  
  

add a comment | 

0

The following script will work in post deployment, it will edit the publish profile accordingly utilizing powershell.

https://stackoverflow.com/questions/55187160/add-words-to-end-of-line-have-indexes-in-different-filegroup

share

answered 1 min ago

GregThomas582GregThomas582

445

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229380%2fautomatically-have-nonclustered-indexes-in-a-different-filegroup%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

share

edited Feb 11 at 7:09

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  interesting, how would I write the index maintenance script?
  
  – Martin Smith51
  Feb 11 at 7:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will share script
  
  – Rajesh Ranjan
  Feb 11 at 7:54
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  >>>So moving clustered index means you are moving table to different file group<<< If you'll try to use CREATE INDEX ... ON<ANOTHER_FG> syntax it won't move LOB data to ANOTHER_FG
  
  – sepupic
  Feb 11 at 9:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, I would like to have it automatically move it to appropriate filegroup when writing ddl statement
  
  – Martin Smith51
  Feb 11 at 15:49
  

  
  
  
  

add a comment | 

4

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

share

edited Feb 11 at 7:09

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  interesting, how would I write the index maintenance script?
  
  – Martin Smith51
  Feb 11 at 7:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will share script
  
  – Rajesh Ranjan
  Feb 11 at 7:54
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  >>>So moving clustered index means you are moving table to different file group<<< If you'll try to use CREATE INDEX ... ON<ANOTHER_FG> syntax it won't move LOB data to ANOTHER_FG
  
  – sepupic
  Feb 11 at 9:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, I would like to have it automatically move it to appropriate filegroup when writing ddl statement
  
  – Martin Smith51
  Feb 11 at 15:49
  

  
  
  
  

add a comment | 

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

share

edited Feb 11 at 7:09

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

share

edited Feb 11 at 7:09

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

answered Feb 11 at 6:59

Rajesh RanjanRajesh Ranjan

845421

0

The following script will work in post deployment, it will edit the publish profile accordingly utilizing powershell.

https://stackoverflow.com/questions/55187160/add-words-to-end-of-line-have-indexes-in-different-filegroup

share

answered 1 min ago

GregThomas582GregThomas582

445

add a comment | 

0

The following script will work in post deployment, it will edit the publish profile accordingly utilizing powershell.

https://stackoverflow.com/questions/55187160/add-words-to-end-of-line-have-indexes-in-different-filegroup

share

answered 1 min ago

GregThomas582GregThomas582

445

add a comment | 

The following script will work in post deployment, it will edit the publish profile accordingly utilizing powershell.

https://stackoverflow.com/questions/55187160/add-words-to-end-of-line-have-indexes-in-different-filegroup

share

answered 1 min ago

GregThomas582GregThomas582

445

share

answered 1 min ago

GregThomas582GregThomas582

445

answered 1 min ago

GregThomas582GregThomas582

445

answered 1 min ago

GregThomas582GregThomas582

445