728x90

조각화 검색

다음 예에서는 sys.dm_db_index_physical_stats 동적 관리 함수를 쿼리하여 Production.Product 테이블의 모든 인덱스에 대한 평균 조각화를 반환합니다. 이전 테이블을 사용하여 PK_Product_ProductID를 다시 구성하고 다른 인덱스를 다시 작성하는 것이 좋습니다.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO



1. 인덱스 다시 작성

다음 예에서는 단일 인덱스를 다시 작성합니다.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

2. 테이블의 모든 인덱스 다시 작성 및 옵션 지정

다음 예에서는 ALL 키워드를 지정합니다. 이 키워드는 테이블에 연결된 인덱스를 모두 다시 작성합니다. 3개의 옵션이 지정됩니다.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

3. 인덱스 다시 구성과 LOB 압축

다음 예에서는 단일 클러스터형 인덱스를 다시 구성합니다. 인덱스에 리프 수준의 LOB 데이터 형식이 포함되어 있으므로 해당 문은 LOB(Large Object) 개체 데이터를 포함하는 페이지도 모두 압축합니다. 기본값이 ON이기 때문에 WITH (LOB_Compaction) 옵션을 지정할 필요가 없습니다.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

[출처] 인덱스 다시 구성 및 다시 작성 mssql2005 |작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28
MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
특정 기간(datetime형식)을 주어 조회할 경우 주의  (0) 2008.04.28
728x90

☞ CTE(Common Table Expression)란?

임시로 이름이 지정된 결과 집합이다.

하위에서 서브쿼리 형식으로 사용할 부분을 미리 정의해 놓은것이다.


☞ 구문

WITH CTE명(컬럼, 컬럼) AS
(
    CTE 쿼리 정의

)
CTE를 포함하여 실행할 쿼리


☞ 주의

CTE를 정의하면 쿼리에서 여러 차례 참조할 수 있다.

참조 여부와 상관없이 CTE 정의 다음에 외부 쿼리가 나와야 한다.

하나의 WITH절에 여러개의 CTE를 정의할수 있다. (컴마로 구분)

이전에 정의된 CTE를 참조하는 CTE를 정의할 수 있다.

그러나 이후에 정의된 CTE는 참조할 수 없다. (훔.... 예제에서 다시 설명하겠다!)


☞ 사용 예제

1. 한개의 CTE 정의

USE AdventureWorks;
GO

WITH DirReps_CTE(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports

FROM DirReps_CTE

WHERE ManagerID <= 50

ORDER BY ManagerID;


CTE를 정의하고 하위에서 검색조건, 정렬조건을 다시 지정하거나

데이터를 가공하여 조회 할 수 있다.

사용자 삽입 이미지

 
 
 




2. 두개 이상의 CTE 정의

USE AdventureWorks;
GO

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader

  GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE


EmpOrdersCTE : EmployeeID별 주문수 목록

MinMaxCTE : EmpOrdersCTE를 참조하여 최소, 최대 주문수와 차이


마지막 쿼리에서 MinMaxCTE를 참조하여 최소, 최대, 차이를 구할수 있다.


사용자 삽입 이미지
 



이전에 정의된 CTE는 하위에서 참조할 수 있지만 반대로 이후에 정의된 CTE는 참조할 수 없다.

예를 들어 WITH 문에 CTE1, CTE2, CTE3를 정의할때

CTE2는 CTE1과 CTE2를 참조할 수 있지만 CTE3는 참조할 수 없다.

다음 쿼리를 실행하믄 위와같은 이유로 오류가 발생한다.


WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader

  GROUP BY EmployeeID
),

MinCTE(MN)
AS
(

 -- MinMaxCTE가 정의되기 전에 참조할수 없다.
  SELECT MN
  FROM MinMaxCTE
),
MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE


재귀쿼리나... 다양한 예제를 보려면 다음을 참조하자 (귀차니즘.. -_-)

참조 : http://msdn2.microsoft.com/ko-kr/library/ms175972.aspx

[출처] [MSSQL2005] CTE(공통 테이블 식)|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
특정 기간(datetime형식)을 주어 조회할 경우 주의  (0) 2008.04.28
[MSSQL2005] LIKE 검색시 [ ] 와일드카드사용하기  (0) 2008.04.28
728x90

-- 테이블 목록
EXEC SP_TABLES


-- 테이블이 컬럼 목록

EXEC SP_COLUMNS @TABLE_NAME

 

-- 스키마 테이블에서 직접 컬럼정보 가져오기~

SELECT  *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME


-- PK, FK, INDEX등 정보 가져오기

SELECT  *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE K.TABLE_NAME = @TABLE_NAME


-- 컬럼에 추가해둔 Description 가져오기

SELECT  *
FROM SYS.EXTENDED_PROPERTIES XP 
WHERE XP.CLASS = 1
 AND  XP.MINOR_ID > 0
 AND XP.MAJOR_ID = OBJECT_ID(@TABLE_NAME) 
 AND XP.NAME IN (N'MS_DESCRIPTION') 
ORDER BY XP.MINOR_ID


위의 정보를 이용하여 테이블 정보를 출력해 보자~

프로젝트 산출물 작업시 유용하게 쓰일것이다.


포맷이 다음과 같다면...

컬럼 명 컬럼 ID Type & Length NOT
NULL		 PK FK IDX 비고
　 　 　 　 　 　 　 　
　 　 　 　 　 　 　 　
　 　 　 　 　 　 　 　
　 　 　 　 　 　 　 　


DECLARE @TABLE_NAME AS VARCHAR(30)

SET @TABLE_NAME = 'TEM_BOARD'

 

SELECT

         [컬럼명] = D.COLUMN_DESC

       , [컬럼ID] = C.COLUMN_NAME

       , [Type & Length] = COLUMN_TYPE

       , [NOT NULL] = CASE WHEN C.IS_NULLABLE = 'NO' THEN 'Y' ELSE '' END

       , PK = ISNULL(K.PK, '')

       , FK = ISNULL(K.FK, '')

       , INX = ISNULL(K.INX, '')

FROM

(

 SELECT 

    TABLE_NAME

  , COLUMN_NAME

  , IS_NULLABLE

  , COLUMN_TYPE = CASE WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION)) + ')'

      WHEN DATA_TYPE IN ('DECIMAL') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'      

      ELSE DATA_TYPE END

  , ORDINAL_POSITION

 FROM INFORMATION_SCHEMA.COLUMNS

 WHERE TABLE_NAME = @TABLE_NAME

) AS C

LEFT JOIN -- 키정보

(

       SELECT COLUMN_NAME

             , PK = MAX(PK)

             , FK = MAX(FK)

             , INX = MAX(INX)

       FROM

       (

             SELECT

                      COLUMN_NAME

                    , PK = CASE WHEN K.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Y' END

                    , FK = CASE WHEN K.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'Y' END

                    , INX = CASE WHEN K.CONSTRAINT_TYPE = 'INDEX' THEN 'Y' END

              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K

                    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME

              WHERE K.TABLE_NAME = @TABLE_NAME

       ) AS K

       GROUP BY COLUMN_NAME

) AS K ON C.COLUMN_NAME = K.COLUMN_NAME

LEFT JOIN -- Description 정보

(

 SELECT

    COLUMN_NM = COL_NAME(XP.MAJOR_ID, XP.MINOR_ID) 

  , COLUMN_DESC = CAST(XP.[VALUE] AS NVARCHAR(4000))

 FROM SYS.EXTENDED_PROPERTIES XP 

 WHERE XP.CLASS = 1

  AND  XP.MINOR_ID > 0

  AND XP.MAJOR_ID = OBJECT_ID(@TABLE_NAME) 

  AND XP.NAME IN (N'MS_DESCRIPTION') 

) AS D ON C.COLUMN_NAME = D.COLUMN_NM

ORDER BY C.ORDINAL_POSITION

[출처] [MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기..|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
특정 기간(datetime형식)을 주어 조회할 경우 주의  (0) 2008.04.28
[MSSQL2005] LIKE 검색시 [ ] 와일드카드사용하기  (0) 2008.04.28
[MSSQL2005] 순위함수 (ROW_NUMBER, RANK, DENSE_RANK, NTILE)  (0) 2008.04.28

+ Recent posts

티스토리툴바