Design | data | database | Database design
MCDBA Database Design Learning Blog
2004-4-14
Planning Storage Management
1 has and can only have one master data file
2 data and transaction logs can never be in the same file
Cannot share a filegroup between 32 databases
4 when estimating the size of a database, you should use tables and indexes.
5 Unlimited file growth is limited only by operating system and physical disk capacity.
A 6 filegroup is a group of files that are treated like a single file, which is called a filegroup.
It enables a database to be stored on multiple disks, possibly through exercises that understand the concept of filegroups.
Practice:
Use master
Go
Create Database Examnotes
On Primary
(Name = ' examnotes_data ', filename= ' d:\testdata\ExamNotes_data.mdf ',
SIZE=4MB,
MAXSIZE=16MB,
FILEGROWTH=2MB),
FILEGROUP SUCCESS
(Name = ' examnotes1_data1 ', filename= ' d:\testdata\ExamNotes1_data1.ndf ',
SIZE=4MB,
MAXSIZE=8MB,
FileGrowth =500kb),
(Name = ' examnotes1_data2 ', filename= ' d:\testdata\ExamNotes2_data2.ndf ',
size=4mb,filegrowth=10%)
Log on (NAME = ' Examnotes1_log ',
Filename= ' D:\testdata\ExamNotes_Log.ldf ',
SIZE=4MB,MAXSIZE=16MB)
To add a filegroup composed of two files to the database
ALTER DATABASE Exam
ADD FILEGROUP ExamFG1
Go
ALTER DATABASE Exam
ADD FILE
(NAME = Exam_data3,
FILENAME = ' d:\testData\exam_data3.ndf ',
SIZE = 5MB,
MAXSIZE = 100MB,
FileGrowth = 5MB),
(NAME = Exam_data4,
FILENAME = ' d:\testData\exam_data4.ndf ',
SIZE = 5MB,
MAXSIZE = 100MB,
FileGrowth = 5MB)
To FILEGROUP ExamFG1
ALTER DATABASE Exam
MODIFY FILEGROUP examFG1 DEFAULT
Go
To delete a filegroup, you must first delete the group file
ALTER DATABASE EXAM
REMOVE FILE EXAM_DATA4
ALTER DATABASE EXAM
REMOVE FILE exam_data3
ALTER DATABASE EXAM
REMOVE FILEGROUP EXAMFG1
A useful SP:
EXEC sp_spaceused Usertablename
Find out the number of records and the amount of space used in a table, because a table can be very large in the actual application.
In the database, specify the use of filegroups for tables such as on, and note that [PRIMARY] in [PRIMARY] is the primary filegroup rather than the primary key.
CREATE TABLE [Acc_paymethod_mstr] (
[Paymethodid] [nvarchar] (a) COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Paymethodname] [nvarchar] (COLLATE) SQL_Latin1_General_CP1_CI_AS not NULL,
[Stampusername] [Stampusername] Null
[Stampdatetime] [Stampdatetime] Null
) on [PRIMARY]
The corresponding syntax for changing the default file is:
ALTER DATABASE <database name>
MODIFY FILEGROUP <filegroup name> DEFAULT
Ensure data integrity
6 Kinds of implementation constraints
Alter Table <table name>
ADD constraint <constraint name>
<constraint Type><constraint define>
1 Unique Key
ALTER TABLE product
Add Constraint Uk_product
UNIQUE (Supplierid,productname)
2 Primary Key
Alter Table Product
Add Constraint Pl_product
PRIMARY KEY (ProductID)
3 Foreign Key
Alter Table Product
Add Constraint Fk_product_suppliers
Foreign KEY (ProductID) References suppliers (ID)
4 CHECK Constraint
Alter Table Product
Add Constraint Df_product_unitsinstock
CHECK (UnitsInStock >=0 or UnitsInStock is NULL)
5 Not NULL constraint
Alter Table Product
ALTER COLUMN discontinued BIT not NULL
6 DEFAULT constraint
ALTER TABLE PRODUCT
ADD CONSTRAINT Df_products_unitprice
DEFAULT 0 for UnitPrice
