|
테이블 생성하기 |
| |
수칙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 타입이 여러 개 있는 경우에 레코드의 길이를 줄일 수 있습니다. 자세한 내용은 온라인 설명서를 참조하십시오. [참고] 데이터 타입
[참고]
주요 키 컬럼에 대해서는 사용자 정의 데이터 타입을 활용하면 편리합니다.
수칙3. 데이터 무결성을 보장할 수 있도록 제약 조건을 적절하게 정의합니다.
데이터 무결성을 유지하여 데이터베이스의 품질을 보장할 것을 권고합니다. 테이블을 계획할 때 필요한 두 가지 주요 단계가 컬럼에 대하여 유효한 값이 무엇인지 확인하고 컬럼에 저장되는 데이터의 무결성을 유지하기 위한 방법을 결정하는 것입니다. 데이터 무결성은 엔터티 무결성, 도메인 무결성, 참조 무결성, 사용자 정의 무결성의 네 개의 범주로 구성되며, PRIMARY KEY 제약 조건, UNIQUE 제약 조건, FOREIGN KEY 제약 조건, CHECK 제약 조건, DEFAULT 정의, NOT NULL 정의, RULE 정의 등을 통하여 저장되는 값의 범위를 제한함으로써 무결성을 보장할 수 있습니다. 데이터 무결성에 대한 자세한 내용은 온라인 설명서에서“데이터 무결성”에 기술되어 있는 내용을 참조하십시오. SQL Server 온라인 설명서에서 [검색] 탭을 클릭한 다음에“검색할 단어 입력”란에“데이터 형식”을 입력하여 검색하면 쉽게 찾을 수 있습니다.
보다 자세한 내용과 예제는 SQL Server 2000 온라인 설명서에서 제목“CREATE TABLE”의 내용을 참조하십시오.
|
 |
|
|
| |
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로 설정하기 CREATE TABLE T1 ( Col1 int NOT NULL PRIMARY KEY Nonclustered, Col2 char(3) NULL, Col3 int NULL ) GO
테이블 생성 후, PRIMARY KEY 제약 조건 설정하기 CREATE TABLE T1 ( Col1 int NOT NULL, Col2 char(3) NULL, Col3 int NULL ) GO ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY Nonclustered (Col1) GO
|
|
|
|
| |
UNIQUE 제약 조건
PRIMARY KEY가 아닌 컬럼(들)에 항상 고유한 값이 저장된다면 UNIQUE 제약 조건을 생성합니다. 예를 들어, 주민등록번호 컬럼이 PRIMARY KEY가 아닌 경우에 해당 테이블에서는 주민등록번호 컬럼이 항상 고유하다면 UNIQUE 제약 조건을 추가하여 중복값이 저장되지 않도록 합니다. UNIQUE 제약 조건도 Clustered Index와 Nonclustered Index 모두 사용 가능하므로, PRIMARY KEY에서 어떤 인덱스를 사용하는지 확인하고 해당 테이블을 참조하는 쿼리를 종합적으로 분석한 다음에 결정합니다. 지면의 제약으로 이 책에는 인덱스 튜닝에 대한 내용은 포함되어 있지 않습니다. 인덱스 튜닝에 대한 내용은 웹 사이트에 게재된 아티클과 SQL Server 2000 온라인 설명서를 참조하십시오.
|
| |
[따라하기] UNIQUE 제약 조건 선언하기
CREATE TABLE Emp ( EmpId int NOT NULL PRIMARY KEY Clustered, SSN char(13) NOT NULL UNIQUE Nonclustered, EmpName varchar(50) NOT NULL ) GO
|
| |
FOREIGN KEY 제약 조건
참조 무결성이 보장되어야 하는 경우에는 FOREIGN KEY 제약 조건을 생성합니다. FK 제약조건이 없는 상태에서 응용 프로그램이 운영되는 상황에서 나중에 FK 제약 조건을 추가하게 되면 응용 프로그램을 수정해야 하는 경우가 발생하므로, FOREIGN KEY 제약 조건은 최초에 테이블을 생성할 때 만드는 것이 좋습니다.
|
| |
CHECK 제약 조건
CHECK 제약 조건을 사용하면, 컬럼에 저장되는 값이나 포맷을 제한할 수 있습니다. CHECK 제약 조건을 추가하면 데이터 무결성을 보장할 수 있을 뿐 아니라, CHECK 제약 조건에 위배되는 범위의 값을 조건절에서 검색하는 경우에는 실제로 테이블을 액세스하지 않고 결과를 바로 반환하므로 성능에도 도움이 됩니다. 이와 같이 성능에 도움이 되도록 하기 위해서는 CHECK 제약 조건을 WITH CHECK 옵션으로 생성해야 합니다.
동일한 테이블 내의 여러 컬럼에 대해서도 CHECK 제약 조건 설정이 가능합니다. 예를 들어, 어떤 테이블에 MaxTemp(최고온도)와 MinTemp(최저온도)의 두 컬럼이 있을 때 항상 MaxTemp의 값이 MinTemp의 값보다 크도록 보장해야 한다면 컬럼 레벨이 아닌 테이블 레벨에 CHECK 제약 조건을 추가하면 됩니다. 만약 트리거로 무결성을 보장하고자 하는 경우가 발생하면 먼저 CHECK 제약 조건으로 구현 가능한지 점검한 다음에 CHECK 제약 조건으로 불가한 경우에 트리거를 사용하기 바랍니다.
[따라하기] CHECK 제약 조건 선언하기 CREATE TABLE Jobs ( Job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, Reg_date smalldatetime NOT NULL DEFAULT (getdate()), min_lvl tinyint NOT NULL, max_lvl tinyint NOT NULL, CONSTRAINT CK_Min_Max CHECK (min_lvl < max_lvl) ) GO
|
|
|
|
| |
DEFAULT 제약 조건 |
| |
사용자가 컬럼에 저장되는 값을 명시적으로 지정하지 않은 경우에 디폴트로 어떤 값이 컬럼에 저장되도록 해 주는 기능입니다.
[권고사항]
제약 조건별로 명명 규칙을 정하고 규칙에 의거하여 이름을 부여할 것을 권고합니다. 제약 조건 외에도 모든 사용자 오브젝트들에 대해서는 표준화된 명명 규칙을 수립하고 그 규칙에 의거하여 오브젝트의 이름을 부여하기 바랍니다. 다음은 제약 조건별 접두어 규칙 예입니다.
|
| |
수칙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 컬럼 목록을 추출하는 예제 스크립트입니다.
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 DataType FROM 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.name GO
|
|
|
|
| |
테이블 삭제하기 |
| |
■ 구문 : DROP TABLE table_name
테이블을 삭제하면, 테이블과 해당 데이터 및 인덱스가 삭제됩니다. Foreign key 제약 조건에 의해 참조되는 테이블은 삭제할 수 없으며, 이 경우에는 참조하는 Foreign key 제약 조건을 삭제한 후 테이블을 삭제해야 합니다. 삭제된 테이블을 참조하는 뷰나 저장 프로시저는 DROP VIEW나 DROP PROCEDURE를 사용하여 삭제합니다. |
| |
[따라하기] 다른 데이터베이스에 존재하는 테이블 삭제하기
Sample 데이터베이스에 있는 T1 테이블을 삭제합니다. USE Northwind GO DROP TABLE Sample.dbo.T1 GO
|
| |
테이블 변경하기 |
| |
■ 컬럼 추가하기
테이블에 새로운 컬럼을 추가하는 경우에 NOT NULL 속성 컬럼을 추가할 수는 있지만, 이 경우에는 반드시 DEFAULT를 지정해야 합니다. NOT NULL 속성으로 컬럼을 추가하면 Sch-M Lock으로 인한 블로킹 문제가 발생할 수 있으므로 테이블의 크기가 큰 경우에는 사전에 테스트 서버에서 소요 시간을 확인하고 서비스 휴지 시간을 충분하게 확보한 다음에 작업할 것을 권고합니다. 당장 DEFAULT를 지정할 수 없는 경우에는 일단 NULL 속성으로 컬럼을 추가하고 NULL인 데이터들을 NULL이 아닌 값으로 업데이트한 다음에, 컬럼을 NOT NULL 속성으로 변경하면 됩니다. |
| |
[따라하기]
정해진 시간 내에 작업을 끝내야 하는 경우, 테이블 크기가 큰 테이블에 컬럼을 NOT NULL로 추가하였는데, 정해진 시간 내에 ALTER TABLE의 수행이 완료되지 않아서 장애로 이어지는 경우가 간혹 있습니다. 테스트 데이터베이스에서 소요시간을 미리 확인한 결과 소요시간이 제한된 휴지 시간을 초과한다면 다음의 팁을 활용해 보기 바랍니다. 미리 인덱스를 만들어 두면 서비스 중에 쪼개어 업데이트하면 블로킹 발생을 줄일 수 있습니다.
LargeTabAddNotNullCol 테이블에 데이터 타입이 tinyint NOT NULL DEFAULT (0) 속성을 가진 NotNullCol 컬럼을 추가한다는 가정 하에 작성된 예제 스크립트입니다. 다음의 스크립트를 자신의 시스템에 적합하도록 수정하여 활용하기 바랍니다. USE Sample GO -- 테스트 테이블을 생성합니다. SELECT IDENTITY(int, 1,1) AS SeqNo, o1.* INTO LargeTabAddNotNullCol FROM Northwind..Orders o1 CROSS JOIN Northwind..Orders o2 GO -- 일단 NULL 허용 컬럼을 추가합니다. ALTER TABLE LargeTabAddNotNullCol ADD NotNullCol tinyint NULL DEFAULT (0) GO -- 업데이트 성능을 위하여 서비스 휴지 시간에 인덱스를 추가합니다. CREATE INDEX IDX_NotNullCol ON LargeTabAddNotNullCol (NotNullCol) GO -- 전체 데이터를 한번에 업데이트하지 말고 분할하여 업데이트합니다. SET ROWCOUNT 1000 DECLARE @UpdatedRows smallint SET @UpdatedRows = 1000 WHILE @UpdatedRows = 1000 BEGIN UPDATE LargeTabAddNotNullCol SET NotNullCol = 0 WHERE NotNullCol IS NULL SET @UpdatedRows = @@ROWCOUNT END SET ROWCOUNT 0 GO -- NULL인 데이터가 없는지 확인합니다. SELECT count(*) FROM LargeTabAddNotNullCol WHERE NotNullCol IS NULL GO -- NOT NULL로 변경합니다. ALTER TABLE LargeTabAddNotNullCol ALTER COLUMN NotNullCol tinyint NOT NULL GO -- 인덱스가 필요하지 않다면 다음 서비스 휴지 시간에 인덱스를 삭제합니다. DROP INDEX LargeTabAddNotNullCol.IDX_NotNullCol GO
|
|
|
|
| |
■ 컬럼 삭제하기 |
| |
[따라하기]
LargeTabAddNotNullCol 테이블에 DEFAULT를 설정한 Addcol 컬럼을 추가 한 다음에, 다시 그 컬럼을 삭제하는 예제입니다. 제약 조건이 설정된 컬럼은 제약 조건을 삭제한 후, 컬럼을 삭제합니다. USE Sample GO ALTER TABLE LargeTabAddNotNullCol ADD AddDate smalldatetime NULL CONSTRAINT DF_AddDate DEFAULT getdate() WITH VALUES GO ALTER TABLE LargeTabAddNotNullCol DROP CONSTRAINT DF_AddDate GO ALTER TABLE LargeTabAddNotNullCol DROP COLUMN AddDate GO
|
| |
■ 컬럼 변경하기 |
| |
[따라하기] 컬럼의 데이터 타입을 nchar(10)에서 char(10)으로 변경하기 USE Northwind GO SELECT * INTO OrdersTest FROM Orders GO EXEC sp_columns OrdersTest GO ALTER TABLE OrdersTest ALTER COLUMN CustomerID char(10) GO
|
|
|
|
| |
오브젝트 이름 변경하기 |
| |
■ 테이블 이름 변경하기 |
| |
[따라하기] EXEC sp_rename 'Territories', 'Territs' GO
|
| |
■ 테이블의 인덱스 이름 변경하기 |
| |
[따라하기] EXEC sp_rename 'Customers.PostalCode', 'IX_ZipCode', 'INDEX' GO
[용도]
sp_rename을 사용하여 Customers 테이블을 Customers_Old로 테이블 이름을 변경한 후에, Customers 테이블을 새로 만든다고 가정합니다. 데이터베이스내에서 인덱스의 이름은 중복 가능하지만, Primary Key 제약 조건의 이름은 고유해야 합니다. 이런 경우에 Customers_Old 테이블의 Primary Key 제약 조건의 이름을 PK_Customers에서 PK_Customers_Old로 변경하면, 새로 만드는 Customers 테이블에 PK_Customers라는 이름의 제약조건을 만들 수 있습니다.
|
| |
■ 제약 조건 이름 변경하기 |
| |
[따라하기] EXEC sp_rename 'Customers_Old.PK_Customers', 'PK_Customers_Old' GO
[용도]
일반적으로 인덱스의 이름에 컬럼의 이름을 포함시키므로, 컬럼의 이름을 변경한 다음에 인덱스의 이름까지 변경하고자 하는 경우에 사용할 수 있습니다. 튜닝 컨설팅 경험에 의하면, 쿼리에서 강제로 어떤 인덱스를 사용하도록 인덱스 힌트를 사용한 경우에는 인덱스의 이름에 HINT 접두어를 추가해서 DBA가 임의로 인덱스를 변경하지 않도록 경고하는 것이 필요하다고 생각합니다. 사전에 명명 규칙을 정해서 통일된 이름체계를 세우는 것이 좋습니다. 이것을 위해, 인덱스의 이름을 규칙에 맞추어 통일시키고자 하는 경우에 유용합니다.
|
| |
■ 저장 프로시저, 뷰, 트리거 이름 변경하기 |
| |
[따라하기] EXEC sp_rename 'Sales by Year', 'SalesByYear' GO
[중요]
저장 프로시저 및 뷰의 이름을 변경하면, 프로시저 캐시를 플러시하여 모든 종속 저장 프로시저 및 뷰가 재컴파일 됩니다.
[주의 사항]
저장 프로시저, 뷰 또는 트리거의 이름을 변경하더라도 syscomments 테이블에 저장되어 있는 해당 개체의 이름은 변경되지 않습니다. 그러므로 개체의 스크립트를 생성하면 syscomments 테이블의 변경 전 이름이 CREATE 문으로 삽입되므로 문제가 발생할 수 있습니다. 이러한 개체 유형(저장 프로시저나 뷰)은 이름을 바꾸지 않는 것이 좋으며, 개체를 삭제한 다음에 새로운 이름으로 다시 만드는 것을 권고합니다. 그리고 이렇게 개체를 새로 만드는 경우에는 반드시 권한 설정을 이전과 동일하게 적용해야 애플리케이션에서 권한 문제로 실행이 실패하는 문제를 방지할 수 있습니다.
|
| |
■ 사용자 정의 데이터 형식의 이름 변경하기 |
| |
[따라하기] EXEC sp_addtype 'UT_CustID', 'nchar(5)', 'NOT NULL' GO EXEC sp_rename 'UT_CustID', 'UT_CustomerID', 'USERDATATYPE' GO
|
|
|
|
| |
테이블 소유자 변경하기 |
| |
[따라하기]
소유권 체인이 끊어지는 문제를 방지하기 위하여 오브젝트의 소유자를 소유자(owner)가 dbo가 아닌 오브젝트들의 소유자를 dbo로 변경하는 예제입니다. |
| |
1. 테스트를 위하여 소유자가 dbo가 아닌 테이블을 만듭니다. EXEC sp_addlogin testuser, testuser, pubs GO USE pubs EXEC sp_adduser testuser, testuser, db_ddladmin GO SETUSER 'testuser' GO CREATE TABLE IncorrectOwner (c1 int) GO /* IncorrectOwner 테이블의 소유자는 dbo가 아닌 testuser가 됩니다. */ SETUSER GO
2. 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
3. 소유권 변경 EXEC sp_changeobjectowner 'testuser.IncorrectOwner', 'dbo' GO /* 결과 창의 메시지 주의: 개체 이름 부분을 변경하면 스크립트나 저장 프로시저를 손상시킬 수 있습니다. */
|
|
|
|
| |
테이블 정보 확인하기 |
| |
■ Foreign key 제약 조건 정보 확인하기 |
| |
[따라하기]Customers 테이블을 Foreign key로 참조하고 있는 테이블과 컬럼 등의 기본 정보를 반환합니다. USE Northwind GO EXEC sp_fkeys N'Customers' GO
|
| |
■ 테이블의 컬럼 Privilege 정보 확인하기 |
| |
[따라하기]
Employees 테이블의 각 컬럼의 INSERT, UPDATE, DELETE, REFERENCES등의 Permission 정보를 반환합니다. USE Northwind GO EXEC sp_column_privileges Employees GO
|
| |
■ 테이블의 인덱스 정보 확인하기 |
| |
[따라하기]
Employees 테이블의 인덱스 목록을 반환합니다. USE Northwind GO EXEC sp_helpindex Employees GO
|
| |
■ 테이블의 제약 조건 정보 확인하기 |
| |
[따라하기]
Employees 테이블에 관련된 모든 제약 조건의 정보를 반환합니다. USE Northwind GO EXEC sp_helpconstraint Employees GO
|
| |
■ 테이블의 모든 정보 확인하기 |
| |
[따라하기]
Employees 테이블에 관련된 컬럼, 인덱스, 제약 조건 등의 정보를 반환합니다. USE Northwind GO EXEC sp_help Employees GO
|
|
|
|
| |
■ 테이블이 사용하는 공간 확인하기 |
| |
[구문] sp_spaceused [[@objname =] 'objname' ] [,[@updateusage =] 'updateusage'
[따라하기]
데이터베이스 내의 모든 테이블의 사용 공간 확인하기 /* 방법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 EXEC pubs..sp_spaceused_all GO EXEC Northwind..sp_spaceused_all GO EXEC sp_MSforeachDB 'EXEC [?]..sp_spaceused_all' GO
|
|
|
|
| |
테이블 옵션 설정하기
사용자 정의 테이블의 옵션 값을 설정합니다. |
| |
[구문] sp_tableoption [ @TableNamePattern = ] ' table' [@OptionName = ]' option_name' [@OptionValue = ] ' value'
[따라하기]
A. Orders 테이블에 'text in row' 옵션 설정하기
'text in row' 옵션을 설정하면, Text, ntext, image 컬럼의 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이고, 값의 범위는 24에서 7000바이트입니다. 다음은 Orders 테이블의 text 컬럼에 저장할 데이터를 1000바이트로 지정하는 예제입니다. EXEC sp_tableoption 'orders', 'text in row', '1000' GO -- 설정값 확인 USE Northwind GO SELECT OBJECTPROPERTY(OBJECT_ID('orders'),'TableTextInRowLimit') GO
B. Orders 테이블에 'pintable' 옵션 설정하기
'pintable' 옵션을 설정하면, 지정한 테이블의 데이터가 메모리에 상주합니다. 테이블 크기가 작고, 자주 사용하는 코드 테이블을 대상으로 사용할 수 있습니다. USE Northwind GO EXEC sp_tableoption 'Orders', 'pintable', 'on' GO --메모리 고정 테이블 확인 SELECT OBJECTPROPERTY (OBJECT_ID('Orders'), 'TableIsPinned') GO
[참고]
메모리에 테이블의 데이터를 상주시키기 위해 DBCC PINTABLE을 사용할 수도 있습니다. -- 메모리 고정 DECLARE @objid int, @dbid int SELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID('Northwind..Orders') DBCC PINTABLE (@dbid, @objid) GO -- 메모리 고정 해제 DECLARE @objid int, @dbid int SELECT @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.id WHERE o.status & 1048576 <> 0 AND indid < 2 GO
[주의]
이 기능은 성능을 향상시킬 수 있지만 주의해서 사용해야 합니다. 커다란 테이블을 고정할 경우 많은 용량의 버퍼 캐시를 사용하기 때문에 다른 테이블에서 사용할 캐시가 부족하게 됩니다. 버퍼 캐시보다 용량이 큰 테이블을 고정하면 전체 버퍼 캐시를 채울 수도 있습니다.
이런 경우 sysadmin 고정 서버 역할의 구성원이 SQL Server를 중지한 후 다시 시작한 다음, 테이블을 고정 해제해야 합니다. 너무 많은 테이블을 메모리에 고정해도 이와 같은 문제가 발생할 수 있습니다.
|
