테이블 관리
테이블 생성하기
| 번호 | 수칙 | 체크 |
| 1 | 동일한 속성의 데이터 타입은 동일하게 할당합니다. | |
| 2 | 컬럼에 저장되는 데이터의 값과 특성을 고려하여 가장 적합한 데이터 타입을 선택합니다. | |
| 3 | 데이터 무결성을 보장할 수 있도록 적절하게 제약 조건을 정의합니다. | |
| 4 | 항상 값이 저장되는 컬럼에 대해서는 반드시 NOT NULL로 정의합니다. |
수칙1. 동일한 속성의 데이터 타입은 일관되게 동일하게 할당합니다.
동일한 속성을 가진 데이터를 서로 다른 테이블들에서 다른 데이터 타입으로 선언한 경우에는 데이터의 불일치 뿐 아니라 성능 저하를 유발할 수도 있으므로 유의하기 바랍니다. 동일한 속성임에도 불구하고 테이블에 따라 데이터 타입이 다른 경우도 있고 데이터 타입은 동일하지만 길이가 다른 경우도 있습니다. 또한 예를 들어, 주민등록번호나 계좌번호와 같은 성격의 데이터들에 대해서 어떤 컬럼은 char(13)으로 선언하고 '-' (하이픈) 없이 데이터를 저장하고 어떤 컬럼은 char(14)로 선언하여 '-'을 추가하여 저장하는 경우가 있는데, 동일한 속성에 대해서는 동일한 데이터 타입, 동일한 데이터 포맷, 동일한 길이를 가지는 동일한 데이터 타입을 일관되게 사용해야 합니다.
수칙2. 컬럼에 저장되는 데이터의 값, 특성 등을 고려하여 적합한 데이터 타입을 선택합니다.
컬럼에 숫자만 저장되고 계산에 사용할 가능성이 있다면 숫자 데이터 타입을 할당합니다. 숫자 데이터 타입의 경우에는 tinyint, smallint, int, bigint의 네 가지 데이터 타입이 지원되므로 저장될 데이터 값의 범위를 확인하여 데이터 타입을 선택합니다. 예를 들어 0에서 255까지의 정수를 저장할 컬럼이라면 저장소 측면에서 int 대신 tinyint를 사용하는 것이 효율적이며, 21억이 넘는 큰 값이 저장될 컬럼이라면 bigint를 사용해야 오버플로우 오류가 발생하는 것을 방지할 수 있습니다.
소수점 이하 값이 없는 컬럼에 불필요하게 numeric, decimal 타입을 사용하는 경우를 볼 수 있는데, 소수점 이하 값이 없는 숫자형 데이터에 대해서는 numeric, decimal 대신 정수형 타입을 사용할 것을 권고합니다.
문자가 저장되는 컬럼은 문자 데이터 타입을 할당하며, 저장되는 값의 길이가 일정하거나 길이의 차이가 적은 경우에는 고정 길이 문자형(char)을 사용하는 것이 성능적인 측면에서 유리합니다.
On/Off 또는 0/1, Yes/No와 같은 성격의 데이터는 bit 데이터 타입으로 설정하면, 하나의 테이블에 bit 타입이 여러 개 있는 경우에 레코드의 길이를 줄일 수 있습니다. 자세한 내용은 온라인 설명서를 참조하십시오.
[참고] 데이터 타입
| 분류 | 데이터 타입 | 범위 | 저장소크기 |
| 정수 | Bit | O 또는 1 | bit |
| Int | -2,147,483,648 ~ 2,147,483,647 | 4 바이트 | |
| Smallint | -32,768 ~ 32,767 | 2 바이트 | |
| Tinyint | 0 ~ 255 | 1 바이트 | |
| Bigint | -2^63 ~ 2^63-1 | 8 바이트 | |
| 부동소수점 | Float[n] | -1.79E+308 ~ 1.79E+308 n = 1~24 |
4 바이트 |
| Float[n] | -1.79E+308 ~ 1.79E+308 n = 25~53 |
8 바이트 | |
| Real | -3.40E + 38 ~ 3.40E + 38 | 4 바이트 | |
| 문자데이터 | char[n] | n = 1~8000 | n 바이트 |
| Varchar[n] | n = 1~8000 | 입력한 데이터의 길이 | |
| Text | 최대 2,147,483,647자의 가변길이 | ||
| 유니코드 문자데이터 |
Nchar | n = 1~4000 | n*2 바이트 |
| nvarchar | n = 1~4000 | 입력한 데이터의 길이*2 바이트 | |
| Ntext | 최대 1,073,741,823자의 가변길이 | ||
| 이진데이터 | binary | n = 1~8000 | n+4 바이트 |
| varbinary | n = 1~8000 | 입력한 데이터의 길이+4 바이트 | |
| Image | 최대 2,147,483,647자의 가변길이 | ||
| 날짜와시간 | datetime | 1753/1/1~9999/12/31 | 8 바이트 |
| smalldatetime | 1900/1/1~2079/6/6 | 4 바이트 | |
| 화폐 | money | -922,337,203,685,477.5808~ +922,337,203,685,477.5807 | 8 바이트 |
| smallmoney | -214,748.3648~214,748.3647 | 4 바이트 |
[참고] 주요 키 컬럼에 대해서는 사용자 정의 데이터 타입을 활용하면 편리합니다.
수칙3. 데이터 무결성을 보장할 수 있도록 제약 조건을 적절하게 정의합니다.
데이터 무결성을 유지하여 데이터베이스의 품질을 보장할 것을 권고합니다. 테이블을 계획할 때 필요한 두 가지 주요 단계가 컬럼에 대하여 유효한 값이 무엇인지 확인하고 컬럼에 저장되는 데이터의 무결성을 유지하기 위한 방법을 결정하는 것입니다. 데이터 무결성은 엔터티 무결성, 도메인 무결성, 참조 무결성, 사용자 정의 무결성의 네 개의 범주로 구성되며, PRIMARY KEY 제약 조건, UNIQUE 제약 조건, FOREIGN KEY 제약 조건, CHECK 제약 조건, DEFAULT 정의, NOT NULL 정의, RULE 정의 등을 통하여 저장되는 값의 범위를 제한함으로써 무결성을 보장할 수 있습니다. 데이터 무결성에 대한 자세한 내용은 온라인 설명서에서 "데이터 무결성"에 기술되어 있는 내용을 참조하십시오. SQL Server 온라인 설명서에서 [검색] 탭을 클릭한 다음에 "검색할 단어 입력" 란에 "데이터 형식"을 입력하여 검색하면 쉽게 찾을 수 있습니다.
PRIMARY KEY 제약 조건테이블 생성 시에는 PRIMARY KEY 제약 조건을 지정합니다. PRIMARY KEY 제약 조건은 테이블을 생성할 때에 생성하는 것이 바람직하지만, PK 제약 조건을 정의하지 않았더라도 테이블 생성 후에 추가로 생성할 수 있습니다. PRIMARY KEY 제약 조건을 설정할 컬럼은 NOT NULL 속성을 가지고 있어야 라며, 고유한 데이터를 가지는 컬럼이어야 합니다. 두 개 이상의 열에 PRIMARY KEY가 정의될 때에는, 한 열에 중복된 값이 있을 수 있지만, 열을 조합한 각 값은 고유해야 합니다.
CREATE TABLE 문에서 PRIMARY KEY를 정의하는 구문에 인덱스의 종류를 지정하지 않으면 디폴트로 PK 키 컬럼(들)에 Clustered Index가 생성됩니다. 어떤 시스템의 경우에는 모든 테이블들의 PK가 무조건 clustered index로 만들어져 있고 또 어떤 시스템의 경우에는 모든 테이블들의 PK가 무조건 nonclustered index로 만들어져 있는 경우를 볼 수가 있는데, PK 제약 조건은 Clustered Index와 Nonclustered Index 두 가지 중 성능적인 측면에서 보다 효율적인 인덱스를 사용해야 한다는 것에 유의하기 바랍니다. PK를 정의할 때에는 테이블을 만들기 전에 쿼리를 종합적으로 분석하여 어떤 인덱스를 사용하는 것이 가장 효율적일지 고려하여 인덱스 유형을 정의하기 바랍니다.
[따라하기]
테이블 생성 시 PRIMARY KEY 제약 조건을 Nonclustered Index로 설정하기
UNIQUE 제약 조건
PRIMARY KEY에 참여하지 않는 컬럼에 항상 고유한 값이 저장된다면 UNIQUE 제약 조건을 생성합니다. 예를 들어, 주민등록번호 컬럼이 PRIMARY KEY가 아닌 경우에 중복값이 저장되지 않도록 하기 위해서는 주민등록번호 컬럼에 UNIQUE 제약 조건을 추가하면 됩니다.
FOREIGN KEY 제약 조건
참조 무결성이 보장되어야 하는 경우에는 FOREIGN KEY 제약 조건을 생성합니다. FK 제약 조건이 없는 상태에서 응용 프로그램이 운영되는 상황에서 나중에 FK 제약 조건을 추가하게 되면 응용 프로그램을 수정해야 하는 경우가 발생하므로, FOREIGN KEY 제약 조건은 최초에 테이블을 생성할 때 만드는 것이 좋습니다.
CHECK 제약 조건
CHECK 제약 조건을 사용하면, 컬럼에 저장되는 값이나 포맷을 제한할 수 있습니다. CHECK 제약 조건을 추가하면 데이터 무결성을 보장할 수 있을 뿐 아니라, CHECK 제약 조건에 위배되는 범위의 값을 조건절에서 검색하는 경우에는 실제로 테이블을 액세스하지 않고 결과를 바로 반환하므로 성능에도 도움이 됩니다. 이와 같이 성능에 도움이 되도록 하기 위해서는 CHECK 제약 조건을 WITH CHECK 옵션으로 생성해야 합니다.
동일한 테이블 내의 여러 컬럼에 대해서도 CHECK 제약 조건 설정이 가능합니다.예를 들어, 어떤 테이블에 MaxTemp(최고온도)와 MinTemp(최저온도)의 두 컬럼이 있을 때 항상 MaxTemp의 값이 MinTemp의 값보다 크도록 보장해야 한다면 컬럼 레벨이 아닌 테이블 레벨에 CHECK 제약 조건을 추가하면 됩니다. 만약 트리거로 무결성을 보장하고자 하는 경우가 발생하면 먼저 CHECK 제약 조건으로 구현 가능한지 점검한 다음에 CHECK 제약 조건으로 불가한 경우에 트리거를 사용하기 바랍니다.
DEFAULT 제약 조건
사용자가 컬럼에 저장되는 값을 명시적으로 지정하지 않은 경우에 디폴트로 어떤 값이 컬럼에 저장되도록 해 주는 기능입니다.
[권고사항] 제약 조건별로 명명 규칙을 정하고 규칙에 의거하여 이름을 부여할 것을 권고합니다. 제약 조건 외에도 모든 사용자 오브젝트들에 대해서는 표준화된 명명 규칙을 수립하고 그 규칙에 의거하여 오브젝트의 이름을 부여하기 바랍니다. 다음은 제약 조건별 접두어 규칙 예입니다.
| 제약 조건 | 접두어 | 이름 예제 |
| PRIMARY KEY 제약 조건 | PK_ | PK_Orders |
| FOREIGN KEY 제약 조건 | FK_ | FK_Jobs_JobID |
| UNIQUE 제약 조건 | UK_ | UK_SSN |
| CHECK 제약 조건 | CK_ | CK_Quantity CK_MaxTemp_MinTemp |
| DEFAULT 제약 조건 | DF_ | DF_CheckDate |
수칙4. 항상 값이 저장되는 컬럼에 대해서는 반드시 NOT NULL로 정의합니다.
NULL이라는 값은 알 수 없는 값이라는 의미를 가지는 특수한 값입니다. NULL은 공백 문자나 0, 빈 문자열과는 전혀 다른 알 수 없는 값입니다. 항상 값이 저장되어야 하는 컬럼을 NULL 허용으로 정의하면 응용 프로그램의 오류로 NULL 값이 저장될 수 있으며, 그로 인하여 NULL 데이터로 인하여 논리적 비교가 더욱 복잡해지거나 오류 데이터로 인한 프로그램의 오동작을 유발할 수 있습니다. 그러므로 항상 명시적으로 값이 저장되는 컬럼에 대해서는 반드시 NOT NULL을 지정하기 바랍니다.
[참고] Identity 컬럼은 tinyint, smallint, int, bigint, decimal(p,0) 또는 numeric(p,0) 열에 할당될 수 있습니다. Identity 컬럼은 자동으로 값이 증가 또는 감소하는 속성을 가지고 있으므로 overflow 또는 underflow가 발생하지 않도록 주기적으로 데이터 타입을 점검합니다. 다음은 Identity 컬럼 목록을 추출하는 예제 스크립트입니다.
<PRE>SELECT object_name(c.id) AS TableName, c.name AS Identity_ColumnName, CASE WHEN t.name IN ('decimal', 'numeric') THEN t.name + '(' + CAST(c.xprec AS varchar(5)) + ',' + CAST(c.xscale AS varchar(5)) + ')' ELSE t.name END AS DataTypeFROM sysobjects o JOIN syscolumns c ON o.id = c.id JOIN master..systypes t ON c.xtype = t.xtype WHERE o.type = 'U' AND c.colstat & 1 = 1 ORDER BY o.nameGO</PRE>테이블 삭제하기
- 구문 : DROP TABLE table_name
테이블을 삭제하면, 테이블과 해당 데이터 및 인덱스를 삭제합니다. Foreign key 제약 조건에 의해 참조되는 테이블은 삭제할 수 없습니다. 참조하는 Foreign key 제약 조건을 삭제한 후, 테이블을 삭제합니다. 삭제된 테이블을 참조하는 뷰나 저장 프로시저는 DROP VIEW나 DROP PROCEDURE를 사용하여 삭제 합니다.
[따라하기] 다른 데이터베이스에 존재하는 테이블 삭제하기
<PRE>USE NorthwindGODROP TABLE Sample.dbo.Tab_sampleGO</PRE>
Sample 데이터베이스에 있는 Tab_sample 테이블을 삭제합니다.
테이블 변경하기
- 컬럼 추가하기
테이블에 컬럼을 추가할 경우, NOT NULL 속성 컬럼을 추가할 수는 있지만, 이 경우에는 반드시 DEFAULT를 지정해야 합니다. DEFAULT를 지정할 수 없는 경우에는 NOT NULL 속성으로 컬럼을 추가한 후, 데이터를 UPDATE하고, NOT NULL 속성으로 컬럼을 변경합니다. 대용량 테이블인 경우에는, NOT NULL 속성으로 컬럼을 추가하면 Sch-M Lock으로 문제를 유발 시킬 가능성이 있으므로, 주의합니다.
[따라하기]
<PRE>USE SampleGOALTER TABLE LargeTabAddNotNullColADD NotNullCol char(50) NULLGOCREATE INDEX IDX_1 ON LargeTabAddNotNullCol (NotNullCol)GOSET ROWCOUNT 1000UPDATE LargeTabAddNotNullCol SET NotNullCol = 'default value'WHERE NotNullCol IS NULLWHILE @@ROWCOUNT = 1000 UPDATE LargeTabAddNotNullCol SET NotNullCol = 'default value' WHERE NotNullCol IS NULLSET ROWCOUNT 0GODROP INDEX LargeTabAddNotNullCol.IDX_1GOALTER TABLE LargeTabAddNotNullColALTER COLUMN NotNullCol CHAR(50) NOT NULLGO</PRE>
정해진 시간 내에 작업을 끝내야 하는 경우, 테이블 크기가 큰 테이블에 컬럼을 NOT NULL로 추가하였는데, 정해진 시간 내에 ALTER TABLE의 수행이 완료되지 않아서 장애로 이어지는 경우가 간혹 있습니다. 테스트 데이터베이스에서 소요시간을 미리 확인하고 소요시간이 제한된 시간을 초과한다면 다음의 팁을 활용하십시오.
대용량 LargeTabAddNotNullCol 테이블에, 데이터 타입이 char(50), NOT NULL 속성을 가진 NotNullCol 컬럼을 추가하는 스크립트입니다. - 컬럼 삭제하기
[따라하기]
<PRE>USE SampleGOALTER TABLE LargeTabAddNotNullColADD Addcol CHAR(100) NOT NULL DEFAULT 'default value'GOALTER TABLE LargeTabAddNotNullColDROP CONSTRAINT DF__LargeTabl__Addco__0D6417DAGOALTER TABLE LargeTabAddNotNullColDROP COLUMN AddcolGO</PRE>
LargeTabAddNotNullCol 테이블에 DEFAULT를 설정한 Addcol 컬럼을 추가 한 다음에, 다시 그 컬럼을 삭제하는 예제입니다. 제약 조건이 설정된 컬럼은 제약 조건을 삭제한 후, 컬럼을 삭제합니다. - Owner가 dbo가 아닌 사용자 테이블 목록 확인하기
SELECT name FROM sysobjects WHERE type='U' AND uid <> 1
ORDER BY name
GO
-- Owner 변경 스크립트 생성하기
SELECT 'EXEC sp_changeobjectowner ''' + USER_NAME(uid) + '.' + name + ''', ''dbo''' FROM sysobjects WHERE type='U' AND uid <> 1
ORDER BY name
GO - 소유권 변경 <PRE>EXEC sp_changeobjectowner 'testuser.IncorrectOwner', 'dbo'GO/* 결과 창의 메시지주의: 개체 이름 부분을 변경하면 스크립트나 저장 프로시저를 손상시킬 수 있습니다.*/</PRE>
테이블 정보 확인하기
- Foreign key 제약 조건 정보 확인하기
[따라하기]
<PRE>USE NorthwindGOEXEC sp_fkeys N'Customers' GO</PRE>
Customers 테이블을 Foreign key로 참조하고 있는 테이블과 컬럼 등의 기본 정보를 반환합니다. - 테이블의 컬럼 Privilege 정보 확인하기
[따라하기]
<PRE>USE NorthwindGOEXEC sp_column_privileges EmployeesGO</PRE>
Employees 테이블의 각 컬럼의 INSERT, UPDATE, DELETE, REFERENCES등의 Permission 정보를 반환합니다. - 테이블의 인덱스 정보 확인하기
[따라하기]
<PRE>USE NorthwindGOEXEC sp_helpindex EmployeesGO</PRE>
Employees 테이블의 인덱스 목록을 반환합니다. - 테이블의 제약 조건 정보 확인하기
[따라하기]
<PRE>USE NorthwindGOEXEC sp_helpconstraint EmployeesGO</PRE>
Employees 테이블에 관련된 모든 제약 조건의 정보를 반환합니다. - 테이블의 모든 정보 확인하기
[따라하기]
<PRE>USE NorthwindGOEXEC sp_help EmployeesGO</PRE>
Employees 테이블에 관련된 컬럼, 인덱스, 제약 조건 등의 정보를 반환합니다. - 테이블이 사용하는 공간 확인하기 <PRE>[구문] sp_spaceused [[@objname =] 'objname'] [,[@updateusage =] 'updateusage']</PRE>
[따라하기] 데이터베이스 내의 모든 테이블의 사용 공간 확인하기
/ 방법1. 기존의 시스템 SP를 단순히 활용한 예제 */
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
/* 방법2. 기존의 시스템 SP를 활용하여 결과를 테이블에 저장한 예제 */
USE DBAdmin
GO
CREATE TABLE spaceused_pubs (
TableName sysname,
Rows int,
Reserved varchar(20),
Data varchar(20),
Index_size varchar(20),
Unused varchar(20))
GO
USE pubs
GO
INSERT INTO DBAdmin..spaceused_pubs
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
SELECT * FROM DBAdmin..spaceused_pubs
GO
/* 방법3. sp_spaceused의 소스 코드를 수정하여 사용하기 */
CREATE PROCEDURE sp_spaceused_all
AS
/*
작성자 : 전현경
내용 : 데이터베이스내의 모든 테이블의 크기
계산 근거 :
reserved: sum(reserved) where indid in (0, 1, 255)
data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
indexsize: sum(used) where indid in (0, 1, 255) - data
unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
SET NOCOUNT ON
DECLARE @Low bigint
SELECT @Low = low FROM mASter.dbo.spt_values
WHERE number = 1 and type = 'E'
SELECT OBJECT_NAME(tmp.id) AS Name
,convert(char(11),sum(rows)) AS Rows
,ltrim(str(sum(tmp.reserved) * @Low / 1024.,15,0) + ' ' + 'KB') AS Reserved
,ltrim(str(sum(tmp.data) * @Low / 1024.,15,0) + ' ' + 'KB') AS Data
,ltrim(str((sum(tmp.used) - sum(tmp.data)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Index_Size
,ltrim(str((sum(tmp.reserved) - sum(tmp.used)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Unused
FROM (
SELECT obj.id AS id, sum(ind.rows) AS rows, sum(ind.reserved) AS reserved, sum(dpages) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid < 2
GROUP BY obj.id
UNION
SELECT obj.id AS id, 0 AS rows, sum(ind.reserved) AS reserved, isnull(sum(used), 0) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid = 255
GROUP BY obj.id ) tmp
GROUP BY tmp.id
ORDER BY sum(tmp.data) desc, sum(tmp.used) desc
SET NOCOUNT OFF
GO
테이블 옵션 설정하기
사용자 정의 테이블의 옵션 값을 설정합니다.
<PRE>[구문] sp_tableoption [ @TableNamePattern = ] 'table' [@OptionName = ] 'option_name' [@OptionValue = ] 'value'</PRE>[따라하기]
A. Orders 테이블에 'text in row' 옵션 설정하기
'text in row' 옵션을 설정하면, Text, ntext, image 컬럼의 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이고, 값의 범위는 24에서 7000바이트입니다. 다음은 Orders 테이블의 text 컬럼에 저장할 데이터를 1000바이트로 지정합니다. <PRE>EXEC sp_tableoption 'orders', 'text in row', '1000'GO-- 설정값 확인USE NorthwindGOSELECT OBJECTPROPERTY(OBJECT_ID('orders'),'TableTextInRowLimit')GO</PRE>B. Orders 테이블에 'pintable' 옵션 설정하기
'pintable' 옵션을 설정하면, 지정한 테이블의 데이터가 메모리에 상주합니다. 테이블 크기가 작고, 자주 사용하는 코드 테이블을 대상으로 사용할 수 있습니다. <PRE>USE NorthwindGOEXEC sp_tableoption 'Orders', 'pintable', 'on'GO--메모리 고정 테이블 확인SELECT OBJECTPROPERTY (OBJECT_ID('Orders'), 'TableIsPinned')GO</PRE>[참고] 메모리에 테이블의 데이터를 상주시키기 위해 DBCC PINTABLE을 사용할 수도 있습니다. <PRE>-- 메모리 고정DECLARE @objid int, @dbid intSELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID('Northwind..Orders')DBCC PINTABLE (@dbid, @objid)GO-- 메모리 고정 해제DECLARE @objid int, @dbid intSELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID(' Northwind..Orders')DBCC UNPINTABLE (@dbid, @objid)GO-- 데이터베이스내의 메모리 고정 테이블의 전체 크기 확인SELECT sum(i.used * 8) AS [pin table space used (KB)] FROM sysindexes i JOIN sysobjects o ON i.id = o.idWHERE o.status & 1048576 <> 0 AND indid < 2GO</PRE>[주의]
이 기능은 성능을 향상시킬 수 있지만 주의해서 사용해야 합니다. 커다란 테이블을 고정할 경우 많은 용량의 버퍼 캐시를 사용하기 때문에 다른 테이블에서 사용할 캐시가 부족하게 됩니다. 버퍼 캐시보다 용량이 큰 테이블을 고정하면 전체 버퍼 캐시를 채울 수도 있습니다. 이런 경우 sysadmin 고정 서버 역할의 구성원이 SQL Server를 중지한 후 다시 시작한 다음, 테이블을 고정 해제해야 합니다. 너무 많은 테이블을 메모리에 고정해도 이와 같은 문제가 발생할 수 있습니다.시스템 오브젝트 생성
시스템 저장 프로시저 생성하기
시스템 저장 프로시저는 master 데이터베이스에 있으며 이름이 sp_ 라는 접두사로 시작하며, 모든 데이터베이스에서 master 데이터베이스라고 지정하지 않고 저장 프로시저의 이름 만으로 실행이 가능합니다. 그리고 master 데이터베이스가 아닌 데이터베이스에서 실행하면 그 데이터베이스의 컨텍스트 내에서 수행되는 특징을 가지고 있습니다. 예를 들어 저장 프로시저가 sysobjects 테이블을 참조한다고 가정하면 이 프로시저는 실제로는 master에 있음에도 불구하고 그 프로시저를 실행할 때 연결되어 있던 데이터베이스에 있는 sysobjects를 액세스합니다.
[따라하기] 시스템 저장 프로시저를 생성하기
1. master 데이터베이스에 연결합니다.
2. sp_ 로 시작하는 이름으로 저장 프로시저를 생성합니다.시스템 함수 조회 및 생성하기
SQL Server가 설치되는 동안 시스템 UDF(사용자 정의 함수)들이 생성되며, 이 시스템 함수는 모든 데이터베이스에서 함수의 이름만 사용하여 액세스가 가능합니다.
시스템 뷰를 만드는 것과 유사하게 master 데이터베이스에 존재하지만 모든 데이터베이스에서 데이터베이스를 지정하지 않고 쿼리를 수행할 수 있는 함수를 사용자가 직접 생성할 수 있습니다. 시스템 함수가 되기 위해서는 함수를 생성할 때 소유자를 system_function_schema로 생성합니다.[따라하기] 시스템 함수 생성, 조회하기
<PRE>/* 시스템 함수 생성하기 */-- 시스템 테이블을 직접 수정할 수 있도록 설정합니다.EXEC sp_configure 'allow updates',1RECONFIGURE WITH OVERRIDEGO-- 함수는 master 데이터베이스에 생성하고,-- 소유자는 system_function_schema로 지정합니다.USE masterGOCREATE FUNCTION system_function_schema.fn_greatest (@x bigint, @y bigint)RETURNS bigintASBEGIN RETURN(CASE WHEN @x>@y THEN @x ELSE @y END)ENDGOCREATE FUNCTION system_function_schema.fn_least (@x bigint, @y bigint)RETURNS bigintASBEGIN RETURN(CASE WHEN @x<@y THEN @x ELSE @y END)ENDGO-- 시스템 테이블을 직접 수정할 수 없도록 0으로 변경합니다. (반드시 수행 요망)EXEC sp_configure 'allow updates',0RECONFIGURE WITH OVERRIDEGO-- 생성한 시스템 함수는 모든 데이터베이스에서 호출 가능합니다.USE NorthwindGOSELECT fn_greatest(989, 998), fn_least(989, 998)GO/* 시스템 함수 조회하기 */USE masterGOSELECT name FROM sysobjectsWHERE uid=USER_ID('system_function_schema')AND (OBJECTPROPERTY(id, 'IsScalarFunction')=1 OR OBJECTPROPERTY(id, 'IsTableFunction')=1 OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)GO</PRE>INFORMATION 스키마 뷰 생성하기
SQL Server 2000에서 메타데이터 정보를 가져오는 방법은 시스템 저장 프로시저를 사용하는 방법과 INFORMATION 스키마 뷰를 사용하는 방법의 두 가지가 있습니다.
[참고] 메타데이터를 가져오기 위해서는 시스템 저장 프로시저, 시스템 함수 또는 시스템 제공 뷰를 사용할 것을 권고합니다. 시스템 테이블을 직접 쿼리하는 경우, 시스템 테이블이 이후 버전에서 변경될 때 정확한 정보를 제공하지 못할 수도 있습니다. 정보 스키마 뷰를 참조할 때는 다음과 같이 사용자 이름을 지정하는 위치에 INFORMATION_SCHEMA를 지정해야 합니다.
<PRE>SELECT *FROM Northwind.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = N'Customers'[SQL Server 2000에서 제공하는 INFORMATION 스키마 뷰 목록]CHECK_CONSTRAINTSCOLUMN_DOMAIN_USAGECOLUMN_PRIVILEGESCOLUMNSCONSTRAINT_COLUMN_USAGECONSTRAINT_TABLE_USAGEDOMAIN_CONSTRAINTSDOMAINSKEY_COLUMN_USAGEPARAMETERSREFERENTIAL_CONSTRAINTSROUTINESROUTINE_COLUMNSSCHEMATATABLE_CONSTRAINTSTABLE_PRIVILEGESTABLESVIEW_COLUMN_USAGEVIEW_TABLE_USAGEVIEWS</PRE>[따라하기]
<PRE>-- 기본으로 제공되는 INFORMATION 스키마 뷰 활용하기USE PubsGOSELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTSGO-- 사용자가 INFORMATION 스키마 뷰 생성하기USE masterGOEXEC sp_configure 'allow updates', 1RECONFIGURE WITH OVERRIDEGOEXEC sp_MS_upd_sysobj_category 1GO CREATE VIEW INFORMATION_SCHEMA.DEFAULT_CONSTRAINTSASSELECT db_name() as CONSTRAINT_CATALOG ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA ,c_obj.name as CONSTRAINT_NAME ,com.text as DEFAULT_CLAUSEFROM sysobjects c_obj, syscomments comWHERE c_obj.uid = user_id() AND c_obj.id = com.id AND c_obj.xtype = 'D'GOEXEC sp_MS_upd_sysobj_category 2GOEXEC sp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO-- 생성한 INFORMATION 스키마 뷰 활용하기USE pubsGOSELECT *FROM INFORMATION_SCHEMA.DEFAULT_CONSTRAINTSGO</PRE>
CHECK 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 있지만 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 기본적으로 제공되지 않습니다. 이 예제에서는 기존의 CHECK_CONSTRAINTS라는 INFORMATION 스키마 뷰의 소스를 수정하여 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰를 만들어 보고자 합니다.사용자 관리
로그인과 사용자 이해하기
SQL Server에 접근하기 위해서는 로그인 계정이 필요하고, 데이터베이스에 접근하기 위해서는 사용자(user)가 필요합니다.
권한 이해하기
사용자는 권한을 받아 작업을 수행할 수 있습니다. 권한에는 문장을 실행할 수 있는지에 따라 권한을 제한하는 명령문(Statement) 사용권한과 테이블, 색인, 뷰, 프로시저에 따라 권한을 제한하는 개체(Object) 사용권한이 있습니다.
역할 이해하기
역할은 로그인 또는 사용자들의 집합이고, 역할에 권한이 설정되어 있습니다. 그러므로, 각 사용자에게 권한을 주는 것이 아니라, 그 권한이 있는 역할에 사용자를 추가시키는 것이 좋습니다. 시스템에서 미리 정의된 역할에는 로그인에게 사용할 수 있는 서버 역할과 사용자에게 사용할 수 있는 데이터베이스 역할 이 있습니다.
로그인 계정 생성하고 권한 부여하기
[따라하기]- SQL Server 계정을 생성하여, Sample 데이터베이스에 사용자를 등록하고, 데이터베이스내의 테이블 읽는 권한을 주고, 서버내의 서버 차원의 설정을 구성할 수 있는 권한을 설정합니다. 고정 데이터베이스 역할 db_datareader은 데이터베이스내의 테이블을 읽는 권한을 가지고 있고, 서버 데이터베이스 역할 serveradmin은 서버차원을 설정을 구성합니다. <PRE>EXEC sp_addlogin 'dbadmin','ad1234','sample'GOEXEC sp_addsrvrolemember 'dbadmin', 'serveradmin'GOUSE SampleGOEXEC sp_adduser 'dbadmin', 'dbadmin', 'db_datareader'GO</PRE>
- NT 로그인 계정을 생성하여, sample 데이터베이스에 사용자로 등록하고, 데이터베이스내의 테이블 읽는 권한을 설정합니다.
NT 로그인 계정은 윈도우의 [내 컴퓨터 관리]나 [액티브 디렉터리 사용자 및 컴퓨터]를 이용하여 생성합니다. 생성된 계정은 Admin\winadmin이라고 가정합니다. <PRE>EXEC sp_grantlogin 'Admin\winadmin'GOEXEC sp_defaultdb 'Admin\winadmin', 'sample'GOUSE SampleGOEXEC sp_grantdbaccess 'Admin\winadmin', 'winadmin'GOEXEC sp_addrolemember 'db_datareader', 'winadmin'GO</PRE> - SQL Server 로그인은 'backupAdmin', 암호는 '1234'로 설정하여, sample 데이터베이스의 사용자로 등록하고, 데이터베이스 백업을 수행할 수 있는 권한을 부여합니다. 그 후, backupAdmin 로그인 계정을 삭제합니다. 고정 데이터베이스 역할 중에서 db_backupoperator 역할은 데이터베이스 백업을 수행할 수 있는 권한을 가지고 있습니다. <PRE>EXEC sp_addlogin 'backupAdmin','1234','sample'GOUSE SampleGOEXEC sp_adduser 'backupAdmin', 'backupAdmin', 'db_backupoperator'GO-- 로그인 계정 삭제EXEC sp_droprolemember 'db_backupoperator', 'backupAdmin'GO EXEC sp_revokedbaccess 'backupAdmin'GOEXEC sp_droplogin 'backupAdmin'GO</PRE>
- NETDOMAIN의 John. Sarah, Diane 계정을 courses 데이터베이스의 사용자로 등록하고, Student 역할에 등록합니다. 그 후, Student 역할에서 Diane 계정을 삭제하고, 로그인 계정에서도 Diane을 삭제합니다. <PRE>USE masterGOEXEC sp_grantlogin 'NETDOMAIN\John'GOEXEC sp_defaultdb 'NETDOMAIN\John', 'courses'GOEXEC sp_grantlogin 'NETDOMAIN\Sarah'GOEXEC sp_defaultdb 'NETDOMAIN\Sarah', 'courses'GOEXEC sp_grantlogin 'NETDOMAIN\Diane'GOEXEC sp_defaultdb 'NETDOMAIN\Diane', 'courses'GOUSE coursesGOEXEC sp_grantdbaccess 'NETDOMAIN\John'GOEXEC sp_grantdbaccess 'NETDOMAIN\Sarah'GOEXEC sp_grantdbaccess 'NETDOMAIN\Diane'GOEXEC sp_addrole 'Student'GOEXEC sp_addrolemember 'Student','NETDOMAIN\John'GOEXEC sp_addrolemember 'Student','NETDOMAIN\Sarah'GOEXEC sp_addrolemember 'Student','NETDOMAIN\Diane'GO EXEC sp_droprolemember 'Student','NETDOMAIN\Diane'GO EXEC sp_revokedbaccess 'NETDOMAIN\Diane'GOEXEC sp_revokelogin 'NETDOMAIN\Diane'GO</PRE>
[참고]
A. sp_revokelogin
sp_grantlogin 또는 sp_denylogin으로 만든 NT 사용자 또는 로그인 항목을 SQL Server에서 제거합니다. NT 로그인 계정이 SQL Server에 연결하는 것이 금지됩니다.B. sp_denylogin
NT 로그인 계정이 SQL Server에 연결하는 것을 금지합니다.기존 로그인과 사용자의 확인하기
[따라하기] <PRE>EXEC sp_helploginsGOEXEC sp_helpuserGO</PRE>암호 변경하기
[따라하기]
<PRE>EXEC sp_password 'ad1234','ad5678', 'dbadmin'GO</PRE>
로그인 계정 'dbadmin'의 암호 'ad1234'를 'ad5678'로 변경합니다. - Foreign key 제약 조건 정보 확인하기
"쇼핑몰·홈페이지·오픈마켓
블로그·페이스북·이메일 등의 각종 마케팅 글쓰기, 각종 광고, 영업, 판매, 제안서, 전단지 반응율 3배×10배 이상 높이는 마법의 8단계 공식" |
|
| ☞자세히보기 |
|
|
|
