November 16, 2005 at 9:17 pm
I am trying to save my user db and objects to a secondary file and indeces to another secondary file i have created, but i have 2 secondary files in my secondary file group i can only see where to select the filegroup but not the specific file in the secondary file group, where am i gowing wrong?
also is it faster perfomance to save non clustered indices to a secondary file on another drive?
one more questrion.
are view actually written to the hard drive, or just in memeory when ran??? i have be debating this, if it is(the virtual table) written to the hard drive, where is it written????
November 18, 2005 at 3:19 am
You explained your one question yourself, you can only assign the creation of an object to the FileGroup, not the file. Example:
CREATE DATABASE MyDB
ON PRIMARY
(NAME = MyDB_Data, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Data.MDF', SIZE= 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP TEXTIMAGE
( NAME = MyDB_Text, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Text.NDF', SIZE = 1 MB, MAXSIZE = UNLIMITED,
FILEGROUP INDICES
(NAME = MyDB_Idx, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Idx.NDF', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
(NAME = MyDB_Log, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Log.LDF', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
CREATE TABLE Document (
DocumentNo_PK INT IDENTITY NOT NULL,
Title varchar(50) NOT NULL,
FileNameExt varchar(255) NULL,
ContentType varchar(50) NULL,
Revision varchar(25) NULL,
RevisionDate datetime NULL,
Document image NULL,
Active BIT NOT NULL) ON 'PRIMARY' TEXTIMAGE_ON 'TEXTIMAGE'
GO
ALTER TABLE Document
ADD CONSTRAINT Document_PK PRIMARY KEY clustered (DocumentNo_PK)
GO
CREATE UNIQUE INDEX Document_AK ON Document (Title) ON 'INDICES'
GO
ALTER TABLE Document ADD CONSTRAINT Document_UC UNIQUE (Title)
GO
As your title states this is the one question, answered! OK here are the other 2: Views are written to the system tables, use INFORMATION_SCHEMA.VIEWS to access. Temporary tables and table variables as well as runtime cached objects are written to the tempdb.
Andy
November 18, 2005 at 11:46 am
super respose thanks, hate ask another question based on your answer but here goes. What is benefit to saving indeces to another file? it you do so, can you put on different drive to spread on i/o speed . if this is case , would you also do with a clustered index which (writes the whole table to hard drive based on clustered index ,I read), or can/would you only put non clustered to different filegroup on different hard drive?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply