Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1610

Re: Inadvertently moving table to a new filegroup? (2005, 2012)

Show all headers | View raw

 (rja.carnegie@gmail.com) writes:
> So this script (as I see) puts the table in "INDEX" although
> I specifically said "PRIMARY".
> 
> CREATE TABLE rjac20131105a 
>     (
>       i int
>         NOT NULL
>         CONSTRAINT rjac20131105b PRIMARY KEY ON [INDEX]
>     , j int NULL
>     )
> ON [PRIMARY]
> ;
> 

Yes, but not that if the table has a LOB column, then the data for this
column (except anything that is saved in row), is placed on PRIMARY. Check this:

CREATE DATABASE rja ON (NAME = 'PRIMARY', FILENAME = 'C:\temp\rja.mdf'),
FILEGROUP [INDEX]  (NAME = 'INDEX', FILENAME = 'C:\temp\rja_index.ndf')
go
USE rja
go
CREATE TABLE rja (id int NOT NULL,
                  data char(24) NOT NULL,
                  blob nvarchar(MAX) NOT NULL,
                  CONSTRAINT pk PRIMARY KEY CLUSTERED(id) ON [INDEX]
)
ON [PRIMARY]
go
INSERT rja (id, data, blob)
  VALUES (1, 'Data', replicate(convert(nvarchar(MAX), 'blob'), 8000))
go
SELECT ds.name, ds.type, au.total_pages
FROM   sys.data_spaces ds
JOIN   sys.allocation_units au ON ds.data_space_id = au.data_space_id
JOIN   sys.partitions p ON au.container_id = p.hobt_id
WHERE  p.object_id = object_id('rja')
go
USE tempdb
go
DROP DATABASE rja

(You can move the LOB data elsewhere with the TEXTIMAGE_ON clause.)


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Inadvertently moving table to a new filegroup? (2005, 2012) rja.carnegie@gmail.com - 2013-11-04 07:03 -0800
  Re: Inadvertently moving table to a new filegroup? (2005, 2012) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2013-11-04 12:52 -0500
  Re: Inadvertently moving table to a new filegroup? (2005, 2012) Erland Sommarskog <esquel@sommarskog.se> - 2013-11-04 22:49 +0100
    Re: Inadvertently moving table to a new filegroup? (2005, 2012) rja.carnegie@gmail.com - 2013-11-05 08:25 -0800
      Re: Inadvertently moving table to a new filegroup? (2005, 2012) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2013-11-05 15:56 -0500
      Re: Inadvertently moving table to a new filegroup? (2005, 2012) Erland Sommarskog <esquel@sommarskog.se> - 2013-11-07 00:02 +0100

csiph-web