Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

File Groups Expand / Collapse
Author
Message
Posted Tuesday, October 20, 2009 2:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
I knew the data would be with the clustered index, but I wasn't sure whether the inner reference or outer reference would win, or whether an error would be thrown. I spent 10 minutes setting up a test environment to make sure. I would have been guessing among my three options otherwise.
Post #806098
Posted Wednesday, October 21, 2009 5:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:43 AM
Points: 1,083, Visits: 597
GabyYYZ (10/19/2009)
SanjayAttray (10/19/2009)
Very good question. I was asked this question in an interview.

Q). What's the easiest and fastest way to move data of a table from file1 to file2?

A). Move the clustered index from file1 to file2.

Why would that be faster than say, creating a target table on file2 and inserting all into it from the source table?


I suppose part of the question was easiest and fastest...

Otherwise nice questions after all.


What you don't know won't hurt you but what you know will make you plan to know better
Post #806397
Posted Monday, October 26, 2009 3:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:26 AM
Points: 908, Visits: 2,804
I knew this one because I've been "bit" by it before. Would indeed be better if there was a warning.

And for the why not create another table and move the data into that, there's DDL issues and possibly space issues, too. It's also many more steps.
Post #809014
Posted Thursday, November 5, 2009 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
Ouch -you got me - I didn't notice "clustered".

And I've complained about people not reading questions carefully!


Tom
Post #814182
Posted Friday, November 13, 2009 8:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:44 PM
Points: 121, Visits: 373
Got this one wrong. I figured since there was a contradiction in the statement, SQL should complain. Why does it choose FG1 over FG2? Seems arbitrary to me.
Post #818593
Posted Thursday, December 10, 2009 7:21 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 26, 2011 4:13 PM
Points: 689, Visits: 410
Hi,

I accept that others have tested and found that everything is created on FG1.

>> from SQL 2008 BOL
If CLUSTERED is specified or the constraint otherwise creates a clustered index, and a <partition_scheme> is specified that differs from the <partition_scheme> or filegroup of the table definition, or vice-versa, only the constraint definition will be honored, and the other will be ignored.
<<

I read that and figured that both would be created on FG2, that is, the filegroup specification on the clustered indexed is ignored.

But looking at it again, it could be read either way ...

Cheers
David
Post #832674
Posted Saturday, February 27, 2010 3:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
Any DBCC PAGE output on this.

Regards
Vinay
Post #873946
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse