SQL-SERVER SYSTEM TABLE2

시스템 테이블은 SQL Server 데이터베이스의 구조와 작동 방식에 대한 중요한 정보를 제공합니다. 이러한 테이블은 데이터베이스의 관리, 성능 모니터링 및 다양한 시스템 작업에 필요한 핵심 정보를 포함하고 있습니다.

SQL Server 시스템 테이블 개요

SQL Server 시스템 테이블은 주로 내부적으로 사용되며, 데이터베이스 엔진에 의해 관리되고 각종 메타데이터(데이터에 대한 데이터)를 저장합니다. 여기에는 테이블 정의, 인덱스, 사용자 권한, 트랜잭션 정보 등 다양한 데이터가 포함되어 있습니다. 기본적으로 이러한 시스템 테이블은 사용자에게 노출되지 않지만, SQL Server의 여러 시스템 뷰를 통해 접근할 수 있습니다.

시스템 테이블의 종류

1. 메타데이터 테이블: 테이블, 열, 인덱스 및 기타 객체에 대한 정보를 포함합니다. 예를 들어 sys.tables, sys.columns 등이 있습니다.
2. 보안 테이블: 사용자와 역할에 대한 정보를 제공합니다. sys.database_principals와 같은 테이블이 포함됩니다.
3. 성능 테이블: 시스템 성능 및 동작 상태에 대한 정보를 저장합니다. 예를 들어 sys.dm_exec_requests와 같은 뷰가 있습니다.

TRUNCATE와 DELETE의 차이

SQL Server에서 데이터를 삭제할 때 일반적으로 DELETE와 TRUNCATE 명령을 사용하게 됩니다. 이 두 명령어는 모두 데이터를 삭제하지만 그 동작 방식과 결과는 상당히 다릅니다.

• TRUNCATE TABLE: 이 명령어는 지정된 테이블의 모든 행을 빠르고 효율적으로 삭제합니다. WHERE 절을 사용할 수 없어서 모든 데이터가 일괄 삭제됩니다. 또한 TRUNCATE는 각 행에 대한 로그를 기록하지 않기 때문에 대량의 데이터를 삭제할 때 성능이 뛰어납니다. 하지만 이 작업은 롤백이 불가능하므로 주의해야 합니다. 예를 들어, 트랜잭션 로그에 기록되지 않은 상태에서 모든 행이 영구적으로 제거됩니다.
• DELETE FROM: 반면, 이 명령어는 특정 조건에 따라 데이터를 삭제할 수 있습니다. WHERE 절을 사용하여 특정 조건을 만족하는 데이터만 삭제할 수 있게 해줍니다. 이는 더 유연하지만, 각 행을 삭제할 때마다 로그가 기록되므로 성능 저하를 초래할 수 있습니다.

ALTER 명령어의 사용

SQL Server에서 ALTER 명령어는 기존 데이터베이스 객체의 속성을 변경하는 데 사용됩니다. ALTER TABLE을 사용하여 테이블 구조를 수정할 수 있으며, 예를 들어 열(column)의 추가, 삭제, 또는 수정이 가능합니다. 다음은 기본적인 구문 예시입니다:

-- 열 추가
ALTER TABLE Employee ADD Birthday DATE;

-- 열 삭제
ALTER TABLE Employee DROP COLUMN Birthday;

이와 같이 SQL Server 시스템 테이블과 관련된 정보는 데이터베이스 관리와 성능 운영에 필수적입니다. 시스템 테이블은 데이터베이스의 상태를 이해하고, 최적화 및 유지 관리 작업을 수행하는 데 필요한 기초 자료를 제공합니다. 

syscomments (all databases)

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text field contains the original SQL definition statements. Since text is often longer than 255 characters, entries often span rows. Each object can occupy as many as 255 rows.

Column	Datatype	Description
id	int	Object ID to which this text applies.
number	smallint	Number within procedure grouping, if grouped. 0 for nonprocedure entries.
colid	tinyint	Row sequence number for object definitions longer than 255 characters.
texttype	smallint	0    User-supplied comment (users can add     entries that describe an object or column) 1    System-supplied comment (for views, rules,     defaults, triggers, and stored procedures) 4    Encrypted comments (applies to     procedures, triggers and views)
language	smallint	Reserved.
text	varchar(255)	Actual text of the SQL definition statement.

 

 

sysconfigures (master database only)

Contains one row for each configuration option that can be set by a user. The sysconfigures table contains the configuration options that were defined before the latest SQL Server startup, plus any dynamic configuration options that were set since the latest SQL Server startup.

Column	Datatype	Description
config	smallint	Configuration variable number
value	int	User-modifiable value for the variable (being used by SQL Server only if RECONFIGURE has been executed)
comment	varchar(255)	Explanation of the configuration option
status	smallint	Bitmap indicating the status for the option: 1    Dynamic (The variable takes effect     when the RECONFIGURE statement     is executed.) 2    Advanced (The variable is displayed     only when the 'show advanced     option' is set.)

 

 

sysdatabases (master database only)

Contains one row for each database on SQL Server. When SQL Server is initially installed, sysdatabases contains entries for the master database, the model database, and the tempdb database. The status column values are additive when more than one option is set or database condition applies.

Column	Datatype	Description
name	varchar(30)	Name of the database.
dbid	smallint	Database ID.
suid	smallint	Server user ID of database creator.
mode	smallint	Used internally for locking a database while it is being created.
status	smallint	Status bits, some of which can be set by the user with the sp_dboption system stored procedure (READ ONLY, DBO USE ONLY, SINGLE USER, and so on): 2    Database is in transition 4    select into/bulkcopy; set with     sp_dboption 8    trunc. log on chkpt; set with     sp_dboption 16    no chkpt on recovery; set     with sp_dboption 32    Crashed while the database     was being loaded; instructs     recovery not to proceed 64    Database not recovered yet 128    Database is in recovery 256    Database is suspect; cannot be     opened or used in its present     state 1024    read only; set with     sp_dboption 2048    dbo use only; set with     sp_dboption 4096    single user; set with     sp_dboption 8192    Database being checkpointed 16384    ANSI null default; set with     sp_dboption 32768    Emergency mode
version	smallint	Internal version number of the SQL Server code with which the database was created.
logptr	int	Pointer to the transaction log.
crdate	datetime	Creation date.
dumptrdate	datetime	Date of the last DUMP TRANSACTION.
category	int	Used for publication and subscription databases.

 

 

sysdevices (master database only)

Contains one row for each disk dump device, diskette dump device, tape dump device, and database device. When SQL Server is installed initially, sysdevices has four entries: one for the MASTER database device (for databases), one for a disk dump device, and two for diskette dump devices.

Column	Datatype	Description
low	int	First virtual page number on a database device (not used for dump devices).
high	int	Last virtual page number on a database device (not used for dump devices).
status	smallint	Bitmap indicating the type of device: 1        Default disk 2        Physical disk 4        Logical disk 8        Skip header 16        Dump device 32        Serial writes 64        Device mirrored 128    Reads mirrored (reserved) 256    Half-mirror only (reserved) 512    Mirror enabled 4096    Read Only 8192    Deferred
cntrltype	smallint	Controller type: 0        Non CD-ROM database device 2        Disk dump device 3 - 4    Diskette dump device 5        Tape dump device 6        Named pipe device
name	varchar(30)	Logical name of the dump device or database device.
phyname	varchar(127)	Name of the physical device.
mirrorname	varchar(127)	Name of the mirror device.
stripeset	varchar(30)	Reserved for future use.

 

 

sysreferences (all databases)

Contains mappings of FOREIGN KEY constraint definitions.

Column	Datatype	Description
constid	int	Constraint ID
fkeyid	int	ID of referencing table
fkeydbid	smallint	Reserved
rkeyid	int	ID of referenced table
rkeydbid	smallint	Reserved
rkeyindid	smallint	Reserved
keycnt	smallint	Number of columns in key
fkey1	tinyint	Column ID of referencing column
fkey2	tinyint	Column ID of referencing column
fkey3	tinyint	Column ID of referencing column
fkey4	tinyint	Column ID of referencing column
fkey5	tinyint	Column ID of referencing column
fkey6	tinyint	Column ID of referencing column
fkey7	tinyint	Column ID of referencing column
fkey8	tinyint	Column ID of referencing column
fkey9	tinyint	Column ID of referencing column
fkey10	tinyint	Column ID of referencing column
fkey11	tinyint	Column ID of referencing column
fkey12	tinyint	Column ID of referencing column
fkey13	tinyint	Column ID of referencing column
fkey14	tinyint	Column ID of referencing column
fkey15	tinyint	Column ID of referencing column
fkey16	tinyint	Column ID of referencing column
rkey1	tinyint	Column ID of referenced column
rkey2	tinyint	Column ID of referenced column
rkey3	tinyint	Column ID of referenced column
rkey4	tinyint	Column ID of referenced column
rkey5	tinyint	Column ID of referenced column
rkey6	tinyint	Column ID of referenced column
rkey7	tinyint	Column ID of referenced column
rkey8	tinyint	Column ID of referenced column
rkey9	tinyint	Column ID of referenced column
rkey10	tinyint	Column ID of referenced column
rkey11	tinyint	Column ID of referenced column
rkey12	tinyint	Column ID of referenced column
rkey13	tinyint	Column ID of referenced column
rkey14	tinyint	Column ID of referenced column
rkey15	tinyint	Column ID of referenced column
rkey16	tinyint	Column ID of referenced column

 

 

sysservers (master database only)

Contains one row for each remote SQL Server on which this SQL Server can call remote stored procedures.

Column	Datatype	Description
srvid	smallint	ID number (for local use only) of the remote server
srvstatus	smallint	Bitmap of options
srvname	varchar(30)	Name of the server
srvnetname	varchar(32)	Reserved (currently the same as srvname)
topologyx	int	Used by the SQL Enterprise Manager server topology diagram
topologyy	int	Used by the SQL Enterprise Manager server topology diagram

 

 

sysusers (all databases)

Contains one row for each user allowed to use the database and one row for each group.

When SQL Server is initially installed, master..sysusers contains three entries: dbo, with an suid of 1 and a uid of 1; guest, with a suid of -1 and uid of 2; and public, with an suid of -2 and a uid of 0. The sysusers table in the model database (and thus in all user databases) initially contains two entries: dbo and public.

The guest user provides access to the database to users not explicitly listed in sysusers, with a restricted set of permissions. The guest entry in master means that any user with an account on SQL Server (that is, with an entry in syslogins) can access master.

The public user refers to all users. The keyword PUBLIC is used with the GRANT and REVOKE statements to signify that permission is granted to or taken away from all users.

Column	Datatype	Description
suid	smallint	Server user ID, copied from syslogins. A suid of 1 is the system administrator, -1 is a guest account.
uid	smallint	User ID, unique in this database. A uid 1 is the database owner.
gid	smallint	Group ID to which this user belongs. If uid = gid, this entry defines a group. The public group has a suid equal to -2. All other groups have a suid equal to -gid.
name	varchar(30)	Username or group name, unique in this database.
environ	varchar(255)	Reserved.