From: esquel@sommarskog.se   
      
    (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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)