Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL Server (Files and filegroups (For each DB:
Primary folder (logical)
…
SQL Server
Files and filegroups
For each DB:
- Primary folder (logical)
- log file .ldf
- we can add more files with .ndf extension
CREATE DATABASE --- db name --
ON
(
NAME = ------
FILENAME = ------path --- :fire: .mdf for the first file and ndf for pther files
SIZE = -----
MAXSIZE = --------
FILEGROWTH = ------- step ---
), :red_flag: we can add another filegroup here and give it a name
(
SAME ...
)
LOG ON
(
SAME ...
)
FILEGROUPS
ALTER DATABASE --- db name ---
ADD FILEGROUP -- filegroup name ---
__
ALTER DATABASE --- db name ---
:explode: MODIFY FILEGROUP --- filegroup name --
DEFAULT
ADD TO FILEGROUPS
ALTER DATABASE -- db name --
ADD FILE
(
same ..
)
TO FILEGROUP -- filegroup name --
__
CREATE TABLE -- table name --
(
)
ON -- filegroup name --
-
TABLES
-
CONSTRAINTS
:fire:ALTER TABLE FIRST AND FOR ALLADD CONSTRAINT -- name -- CHECK ( --- )
- check ( --- between -- and ---)
- check ( --- in ( - , - , -) )
- check ( -- > #)
ADD CONSTRAINT -- name -- FOREIGN KEY ( -- ) REFRENCES ---- ( -- )ADD CONSTRAINT -- constraint name -- UNIQUE ( -- )
-
ALTER TABLE
ADD COLUMN
ALTER TABLE -- table name --
ADD -- col name -- type ... etc
DROP COLUMN
ALTER TABLE -- table name --
DROP COLUMN -- col name --
ALTER COLUMN
ALTER TABLE -- table name --
ALTER COLUMN -- col name -- type ... etc
DATA
INSERT INTO --name -- (col1, col2, col3, ...) VALUES ( val1, val2, val3, ...) WHERE condition;
- If empty value .. insert NULL :fire:
- I can use the word DEFAULT
UPDATE -- name -- SET -- col= val, col2= val2, .. WHERE condition;
-
Indexes
Non clustered index
- many
- use pointer to heap or row
- allow null and repeating values
CREATE NONCLUSTERED INDEX -- name -- ON -- attribute name( -- ) --ALTER TABLE -- name --
CREATE CONSTRAINT -- anme -- PRIMARY KEY NONCLUSTERED ( -- )
Unique index
- No duplicate
- when define primary key or unique constraint
CREATE UNIQUE INDEX -- name -- ON -- attribute name ( -- ) --
Clustered Index
- Only one
- Primary key is clustered by default
- based on row if not use the heap
CREATE CLUSTERED INDEX -- name -- ON -- attribute name ( -- ) --ALTER TABLE -- name --
ADD CONSTRAINT -- name -- PRIMARY KEY CLUSTERED ( -- )ALTER TABLE -- name --
DROP CONSTRAINT -- name --
-
-
-
Build in functions
- substring( var, start index, length)
- DateDiff( month, --- , GETDATE())
-
-