SQL Server functions-File

1. Set memory options -- Configure min Server Memory
Exec Sp_configure n ' Min server memory (MB) ' , 0

-- Set max server memory configuration items
Exec Sp_configure n ' Max server memory (MB) ' , 256

-- Make updates take effect
Reconfigure   With Override

2. Use Files and file groups/ *-- Function Description

The following code creates a database named mydb on SQL Server 2000
The database includes one primary data file, three user-defined file groups, and one log file.
The alter database statement specifies the User-Defined file group as the default file group.
Then, create a table by referring to the default file group and place the image data and indexes in the specified file group.
Finally, delete the specified data file in the file group.
--*/

-- Switch to master database
Use Master
Go

-- A. Create a database named mydb.
Create   Database Mydb
On   Primary -- Main file group and main data file
(Name = ' Mydb_primary ' ,
Filename =   ' C: \ mydb_prm.mdf ' ),
Filegroup mydb_fg1 -- User-Defined File Group 1
(Name =   ' Mydb_fg1_dat1 ' ,
Filename =   ' C: \ mydb_fg1_1.ndf ' ), -- Secondary data file 1
(Name =   ' Mydb_fg1_dat2 ' ,
Filename =   ' D: \ mydb_fg1_2.ndf ' ), -- Secondary data file 2
Filegroup mydb_fg2 -- User-Defined File Group 2
(Name =   ' Mydb_fg1_dat ' ,
Filename =   ' E: \ mydb_fg2.ndf ' ) -- Secondary data file
Log   On -- Log Files
(Name = ' Mydb_log ' ,
Filename = ' D: \ mydb. LDF ' )
Go

-- B. Modify the default data file group
Alter   Database Mydb modify filegroup mydb_fg1 Default
Go

-- Switch to the new database mydb
Use Mydb

-- C. Create a table in the default file group mydb_fg1 and save the specified image data in the User-Defined file group mmydb_fg2.
Create   Table Mytable
(Cola Int Primary   Key ,
COLB Char ( 8 ),
Colc Image )
Textimage_on mydb_fg2

-- Create an index on mydb_fg2
Create   Index Ix_mytable On Mytable (COLA) On Mydb_fg2
Go


-- D. Move the data from mydb_fg1_dat1 to another data file and clear the data file mydb_fg1_dat1.
DBCC Shrinkfile (mydb_fg1_dat1, emptyfile)
-- Delete the data file mydb_fg1_dat1
Alter   Database Mydb remove File Mydb_fg1_dat1

3. Adjust the file attributes of the tempdb Database -- A. Set the size of the master data file of the tempdb database to 10 MB.
Alter   Database Tempdb
Modify File (
Name = Tempdev,
Size = 100 MB)
Go

-- B. Move the master data file of the tempdb database to the specified disk partition and add a data file to it.
-- Move master data files
Alter   Database Tempdb modify File
(Name = ' Tempdev ' ,
Filename = ' D: \ tempdb. MDF ' )

-- Add secondary data file
Alter   Database Tempdb Add   File
(Name = ' Tempdata_1 ' ,
Filename = ' D: \ tempdb_data_1.ndf ' )