블로그 이미지
불쥐의 눈으로 본세상 ㏈ª ☞ β┖υΕJini.κR

카테고리

분류 전체보기 (234)
By β┖υΕJini (131)
㏈ª By β┖υΕJini (103)
Total302,466
Today14
Yesterday15


버젼 확인

SELECT @@VERSION

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46  
Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64>
(Build 3790: Service Pack 2)


SQL Server 2008 R2 서비스 팩 1에서 수정 된 버그 목록
http://support.microsoft.com/kb/2528583



서비스 팩 1 다운로드
http://www.microsoft.com/downloads/ko-kr/details.aspx?FamilyID=b9aa2dba-7f20-4c0c-9afd-1eebee5a94ea#AdditionalInfo


변경된 버젼
10.50.2500.0

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요





SQL Server 2008 R2 SSMS 에서 JOB 을 수정하거나 만들때 버그 발생으로 인해

창이 열리 않는 문제

Error Message


CLSID가 {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} 에서 해당 IClassFactory COM 구성 요소의 인스턴스를 만들어 오류 때문에 실패했습니다: c001f011. (Microsoft.SqlServer.ManagedDTS)



관련 링크 : http://support.microsoft.com/kb/2315727
핫픽스 내용 : http://support.microsoft.com/kb/2261464
핫픽스 다운로드 : http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2261464&kbln=ko
Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

 

USE MASTER

GO

 

--CHECKPOINT 발생하지않도록 INTERVAL 를최대치로변경

EXEC SP_CONFIGURE ' INTERVAL (MIN)', 32767

GO

RECONFIGURE WITH OVERRIDE

GO


--
데이터베이스생성

CREATE DATABASE FULLDB ON  PRIMARY

( NAME = N'FULLDB', FILENAME = N'D:\FULLDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'FULLDB_LOG', FILENAME = N'D:\FULLDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

CREATE DATABASE SIMPLEDB ON  PRIMARY

( NAME = N'SIMPLEDB', FILENAME = N'D:\SIMPLEDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'SIMPLEDB_LOG', FILENAME = N'D:\SIMPLEDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

CREATE DATABASE BULKLOGGEDDB ON  PRIMARY

( NAME = N'BULKLOGGEDDB', FILENAME = N'D:\BULKLOGGEDDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'BULKLOGGEDDB_LOG', FILENAME = N'D:\BULKLOGGEDDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

--전체복구모델변경

ALTER DATABASE FULLDB SET RECOVERY   FULL

GO

 

--단순복구모델변경

ALTER DATABASE SIMPLEDB SET RECOVERY   SIMPLE

GO

 

--BULK LOGGED 모델데이터베이스변경

ALTER DATABASE BULKLOGGEDDB SET RECOVERY  BULK_LOGGED

GO

 

DBCC SQLPERF(LOGSPACE)

GO

 

/*

DATABASE NAME          LOG SIZE (MB) LOG SPACE USED (%) STATUS

---------------------- ------------- ------------------ -----------

FULLDB                           9.992188            2.946638     0

SIMPLEDB                         9.992188            2.961298     0

BULKLOGGEDDB               9.992188            2.961298     0

*/

 

--전체복구모델데이터입력

CREATE TABLE FULLDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO FULLDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

 

--단순복구모델데이터입력

CREATE TABLE SIMPLEDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO SIMPLEDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

--BULK LOGGED 모델데이터입력

CREATE TABLE BULKLOGGEDDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO BULKLOGGEDDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

DBCC SQLPERF(LOGSPACE)

GO

/*

DATABASE NAME          LOG SIZE (MB) LOG SPACE USED (%) STATUS

---------------------- ------------- ------------------ -----------

FULLDB                           9.992188            52.92709     0

SIMPLEDB                         9.992188            52.94175     0

BULKLOGGEDDB               9.992188            52.94175     0

*/

 

 

 

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지

Linked Server 를 구성 할때 SSMS , EM 를 통해서들 많이 구성 하시죠.


하지만  MS-SQL 시스템 프로시져를 통해 구성할수도 있습니다.


그 부분에 대해서 정리해 보았습니다.


우선 구성 관리자를 통해 BLUEJINIPC 라는 별칭으로 지정 하고 시작 하겠습니다.

제목 없음.jpg

 

--LINKSERVER를통해적용할데이터베이스테이블데이터조회

 

SELECT TEST.* FROM OPENROWSET('SQLOLEDB', 'BLUEJINIPC';

'SA';'1234',

'SELECT * FROM ADVENTUREWORKS.HUMANRESOURCES.EMPLOYEE')

AS TEST

 제목 없음1.jpg

--아래와같이등록한다. SQL SERVER인경우

EXEC SP_ADDLINKEDSERVER 'BLUEJINIPC', 'SQL SERVER'

 

 

-- LINKED 서버삭제

EXEC SP_DROPSERVER 'BLUEJINIPC'  --삭제하는경우

 

 

--로그인할계정등록(로컬서버로그인과원격서버로그인매핑)

EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='BLUEJINIPC',

@USESELF='FALSE', @LOCALLOGIN='SA', @RMTUSER='SA', @RMTPASSWORD='1234'

 

SP_DROPLINKEDSRVLOGIN 'BLUEJINIPC', 'SA'      --계정을삭제하는경우

 

--로그인할계정등록(다음보안컨텍스트를사용하여연결)

EXEC SP_ADDLINKEDSRVLOGIN 'BLUEJINIPC'

, 'FALSE', NULL, @RMTUSER='SA', @RMTPASSWORD='1234' 

 

-- FOUR-PART NAME

SELECT *

FROM [BLUEJINIPC].ADVENTUREWORKS.HUMANRESOURCES.EMPLOYEE

 

 

USE MASTER

--LINKED SERVER 옵션설정

EXEC SP_SERVEROPTION [BLUEJINIPC], 'COLLATION COMPATIBLE' , 'TRUE' --데이터정렬호환

EXEC SP_SERVEROPTION [BLUEJINIPC], 'RPC'     , 'TRUE' --RPC 사용

EXEC SP_SERVEROPTION [BLUEJINIPC], 'RPC OUT'    , 'TRUE' --RPC 내보내기사용

EXEC SP_SERVEROPTION [BLUEJINIPC], 'USE REMOTE COLLATION' , 'TRUE' --원격정렬사용

EXEC SP_SERVEROPTION [BLUEJINIPC], 'DATA ACCESS'   , 'TRUE' --데이터액세스

EXEC SP_SERVEROPTION [BLUEJINIPC], 'CONNECT TIMEOUT'  , 120  --연결시간제한(SEC)

EXEC SP_SERVEROPTION [BLUEJINIPC], 'QUERY TIMEOUT'   , 120  --쿼리제한시간(SEC)

 

--OPENQUERY를통해PASS-THROUGH쿼리를실행하는경우

SELECT * FROM OPENQUERY

([BLUEJINIPC], 'SELECT * FROM ADVENTUREWORKS.HUMANRESOURCES.EMPLOYEE')

  

 

-- 링크드이름을모를경우EM 이나SSMS 열어보지않고아래명령으로쉽게찾을수있습니다.

SP_HELPSERVER

 

3.jpg

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


이번에는 뷰에 대해서 간략하게 정리 해보았습니다.  뷰를 사용 할때 테이블의 컬럼이

추가 되었는데 데이터가 안나오는 경우를 보셨을 것입니다. 테스트를 통해 한번 알아 보겠습니다.


USE TESTDB

--테스트테이블생성

CREATE TABLE [TABLE1]

(

       [COL1] INT,

       [COL2] INT,

       [COL3] INT,

       [COL4] INT

)

-- 테스트데이터입력

INSERT INTO [TABLE1] VALUES (1,2,3,4)

 

-- 현재데이터조회

SELECT * FROM [VIEW1]

 

 

-- 뷰생성

CREATE VIEW [VIEW1] AS SELECT * FROM [TABLE1]

 

 

-- 테이블컬럼추가

ALTER TABLE TABLE1 ADD [COL5] INT DEFAULT 0 NOT NULL

 

-- 조회

SELECT * FROM [VIEW1]
테이블의 컬럼을 추가 했지만 뷰의 데이터는 이전 테이블의 데이터만

보여 지는걸 알수 있습니다.

 1.jpg

-- 실제테이블조회

SELECT * FROM [TABLE1]

실제 테이블은 컬럼이 추가 되고 데이터가 정상적으로 들어가

있는걸 알수 있습니다.

 2.jpg

 

결론

뷰의 경우 SELECT * 를 사용 하여 만들고

테이블 컬럼을 추가 하게 되면 메타 데이터 갱신이 되지 않아

이전 컬럼의 데이터만 불러 오게 됩니다.

 

-- 뷰메타데이터갱신

EXEC SP_REFRESHVIEW [VIEW1]

 

-- 뷰조회

SELECT * FROM [VIEW1]

2.jpg


Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지



RANK() 함수에 대해서 한번 정리 해보았습니다.

-- 순위함수정리에대해서한번정리해보았습니다.

 

순위함수는 우리가 데이터를 입력한 순으로 순위를 정하는  것이 아니라

정렬한 순서대로 순위를 정하게 됩니다.

 

 

USE TESTDB

GO

 

CREATE TABLE 성적( 번호 INT, 이름 CHAR(8), 점수 INT )

GO

 

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 1, '김경진', 79 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 2, '강산아', 19 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 3, '김희선', 29 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 4, '홍길동', 49 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 5, '고현정', 39 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 6, '하만철', 59 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 7, '김대우', 79 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 8, '강성욱', 69 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 9, '김민석', 99 )

INSERT INTO 성적(번호, 이름, 점수) VALUES ( 10, '티파니', 89 )

 

 

 

-- 점수에따른순위를구하는경우 ROW_NUMBER()  이용해서사용할수있습니다.

SELECT 번호, 이름, 점수, ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS '등수' FROM 성적

 

-- 동일한점수의경우같은랭킹을정희할경우

-- 이렇게하면상관관계SUB QUERY를사용하지않아도손쉽게등수를정의할수있습니다.

SELECT 번호, 이름, 점수, RANK() OVER (ORDER BY 점수 DESC) AS '등수' FROM 성적

 

-- 중복되는값이있어도순위를건너뛰지않고순위를낼수있다.

SELECT 번호, 이름, 점수, DENSE_RANK() OVER (ORDER BY 점수 DESC) AS '등수' FROM 성적

 

-- 점수에따른부류NTITLE()

-- 성적상, , 하로구분할수있다

SELECT 번호, 이름, 점수, NTILE(3) OVER (ORDER BY 점수 DESC) AS '등분' FROM 성적

 

 

 

-- ROW_NUMBER(), RANK(), DENSE_RANK() 함수의차이점

SELECT 번호, 이름, 점수,

             ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS 'ROW_NUMBER',

             RANK() OVER (ORDER BY 점수 DESC) AS 'RANK',

             DENSE_RANK() OVER (ORDER BY 점수 DESC) AS 'DENSE_RANK',

             NTILE(3) OVER (ORDER BY 점수 DESC) AS 'NTITLE'

FROM 성적

 

 

-- PARTITION BY 키워드사용, 매출

-- 년도별로매출에따른순위

 

CREATE TABLE 매출통계( 년도 INT, 분기 INT, 매출액 BIGINT )

GO

 

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2009, 1, 10000000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2009, 2, 11000000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2009, 3, 10200000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2009, 4, 10030000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2010, 1, 10004000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2010, 2, 10000500 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2010, 3, 10004000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2010, 4, 10030000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2011, 1, 10200000 )

INSERT INTO 매출통계(년도, 분기, 매출액) VALUES ( 2011, 2, 11000000 )

 

 

SELECT * FROM 매출통계

 

SELECT 년도, 분기, 매출액,

             ROW_NUMBER() OVER (PARTITION BY 년도 ORDER BY 매출액 DESC) AS 'PARTITIONED'

FROM 매출통계

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


SQL 2005 부터는 오라클의 데이터를 MS-SQL 로 복제를 구성 할수 있다.


오라클 게시자 복제의 제한 사항이 있습니다. 테이블, 인덱스, IOT , 구체화된 뷰에 대한 복제를 지원 합니다.


오라클 게시자 트랜잭션 복제를  위한 기본 세팅에 대한 내용과 오라클 복제에 대한 내용을 두 파트로 나누어 연재 할까 합니다.


1. 기본세팅

 

1.1  오라클 복제 테스트 환경

SQL Server 2005 Oracle(8.05 버전 이상)을 복제의 게시자로 지정하여 Oracle 이 게시한 개체의 행에 대한 변경 내용을 SQL Server 구독자로 적용 할수 있습니다. 지원하는 복제는 스냅숏 복제와 표준 트랜젝션 복제 입니다.

* 오라클 게시자 – MSSQL2005 구독자 복제 테스트 복제 테스트

1. 오라클 9.0 환경에서 TEST

- 복제 구성은 정상적으로 동작하지만 복제 모니터 활성화시 오라클 9.2부터 가능 하다는 오류 로그 발생 후 실패

2. 오라클 9.2 환경에서 TEST

  - 복제 구성과 복제 모니터 활성화시 정상적으로 테이블 복제 확인

Etc) 오라클 복제 지원 : 테이블 ,인덱스 ,IOT,구체화된 뷰에 대한 복제를 지원

및 오라클 복제 관련 오류나 기술지원 사이트

http://technet.microsoft.com/ko-kr/library/ms152516.aspx

 

1.2 오라클 기본 Setting 확인

 

오라클 구성된 서버 확인 사항(게시자)

-      설치된 서버의 SID , IP , 포트 정보 (MSSQL 설치된 서버에서 SQL*PLUS 로 접근 확인)

-      오라클 클라이언트가 서버에 접속하기 위한 리스너 실행 유무 확인
à TNSLSNR 실행 여부 확인

1.png

 

 

 

1.3 MSSQL2005 기본 Setting 확인

MSSQL2005 구성된 서버 확인 사항(구독자)

-      MSSQL 서버의 오라클 클라이언트 SQLPLUS 설치 확인한다.

-      오라클 설치 경로/NETWORK/ADMIN/tnsname.ora 파일에 접속할 DB 서버의 정보 확인

 

2.png  

TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 서버IP)(PORT = 서버PORT))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

)

- TEST 라는 이름으로 DB서버의 정보를 사용할수 있습니다(사용자 맘대로 이름정의)

- SERVICE_NAME = orcl 의 내용은 DB서버에 설정된 서비스 이름입니다.

 

 

 

à MSSQL2005 서버에서 오라클 접속 확인 방법

 4.png


여기 까지 오라클 게시자 트랜젝션 복제를 하기 위한 기본 세팅해야 할 내용입니다.


개인적으로 구축을 하면서  기본세팅을 했던 내용을 정리 한것이기 때문에


다른 메뉴얼이라 조금 틀릴수도 있습니다.^^


실제 복제 하는 건 조만간에 올리도록 하겠습니다. ^^

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


보통 ANSI 표준 에 대해서 많이들 이야기 합니다.


그리고 NULL 값을 비교 할때 값이 이상하게 나오는 경우도 보실것입니다. 이런 이유는


ANSI 표준 옵션을 사용했는지 아닌지에따라 달라 지게 됩니다.


-- TEST 테이블생성

 

IF(OBJECT_ID('TBL_TEST') IS NOT NULL)

       DROP TABLE TBL_TEST

GO

 

CREATE TABLE TBL_TEST (A INT NULL)

INSERT INTO TBL_TEST VALUES (NULL)

INSERT INTO TBL_TEST VALUES (0)

INSERT INTO TBL_TEST VALUES (1)

GO

 

PRINT '----  (MS-SQL 기본세팅) ------'

DECLARE @VARNAME INT

SELECT @VARNAME = NULL

SELECT * FROM TBL_TEST WHERE A = @VARNAME 

--NULL

SELECT * FROM TBL_TEST WHERE A <> @VARNAME

--0

--1

SELECT * FROM TBL_TEST WHERE A IS NULL

--NULL

GO

 

-- SET ANSI_NULLS TO ON AND TEST.

PRINT 'TESTING ANSI_NULLS ON'

SET ANSI_NULLS ON

GO

DECLARE @VARNAME INT

SELECT @VARNAME = NULL

SELECT * FROM TBL_TEST WHERE A = @VARNAME

--값없음

SELECT * FROM TBL_TEST WHERE A <> @VARNAME

--값없음

SELECT * FROM TBL_TEST WHERE A IS NULL

--NULL

GO

-- SET ANSI_NULLS TO OFF AND TEST.

PRINT 'TESTING SET ANSI_NULLS OFF'

SET ANSI_NULLS OFF

GO

DECLARE @VARNAME INT

SELECT @VARNAME = NULL

SELECT * FROM TBL_TEST WHERE A = @VARNAME

SELECT * FROM TBL_TEST WHERE A <> @VARNAME

SELECT * FROM TBL_TEST WHERE A IS NULL

GO

-- 테스트테이블삭제

DROP TABLE TBL_TEST

GO

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

 

사용자 삽입 이미지

-- 통계정보를수동으로갱신할경우

CREATE STATISTICS NAMES

ON CUSTOMERS (COMPANYNAME, CONTACTNAME)

WITH SAMPLE 5 PERCENT

GO

 

SP_HELPINDEX CUSTOMERS

 

--테이블이나인덱스의조각난정도를볼경우

DBCC SHOW_STATISTICS (CUSTOMERS, PK_CUSTOMERS)

SP_SPACEUSED ORDERS

 

 

-- EMPLOYEE 테이블의모든인덱스에대한통계업데이트

 

USE TESTDB;

UPDATE STATISTICS EMPLOYEE;

GO

 

-- EMPLOYEE 테이블의PK_EMPLOYEE_EMPLOYEEID 인덱스에대한통계업데이트

UPDATE STATISTICS EMPLOYEE PK_EMPLOYEE_EMPLOYEEID;

GO

 

-- TESTDB 데이터베이스내의모든내부테이블의통계업데이터

USE TESTDB;

EXEC SP_UPDATESTATS

 

GO

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


/*-----------------------------------------------------------

목표: 컬럼추가시NULL 성능테스트

전제조건

       1. 테이블의40만건의데이터존재

       2. CLUSTRED INDEX 존재

       3. NON-CLUSSTERED INDEX 2개존재

----------------------------------------------------------- */

 

USE TEMPDB

GO

 

/*---------------------------------------------------------

 조건1 ALTER TABLE 문을이용한컬럼추가

        NOT NULL DEFAULT : ''

---------------------------------------------------------*/

-- 기존테이블존재하면삭제

IF EXISTS(SELECT *  FROM SYSOBJECTS WHERE ID = OBJECT_ID('T1'))

   DROP TABLE T1

GO

 

-- 테이블생성

CREATE TABLE T1(

  ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

, 주소 CHAR(50) NULL

, 전화 CHAR(20) NOT NULL DEFAULT ''

)

-- 40만건의데이터추가

SET NOCOUNT ON

DECLARE @I INT

SET @I = 1

WHILE (@I <= 400000)

       BEGIN

        INSERT T1(주소, 전화) VALUES ('ABC', 'ABCD')

        SET @I = @I + 1

       END

SET NOCOUNT OFF

 

-- NON-CLUSTRED INDEX 생성

CREATE INDEX NC_ADDR ON T1(주소)

CREATE INDEX NC_ADDR_TEL ON T1(주소, 전화)

 

ALTER TABLE T1

ADD 주소 CHAR(50) NOT NULL DEFAULT ''

 

-- 12

/*---------------------------------------------------------

 조건2 새로운테이블생성후INSERT SELECT 문을이용하여

             기존테이블의데이터추가

---------------------------------------------------------*/

-- 기존테이블존재하면삭제

IF EXISTS(SELECT *  FROM SYSOBJECTS WHERE ID = OBJECT_ID('T2'))

   DROP TABLE T2

GO

 

-- 테이블생성

CREATE TABLE T2(

  ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

, 주소 CHAR(50) NULL

, 전화 CHAR(20) NOT NULL DEFAULT ''

)

-- 40만건의데이터추가

SET NOCOUNT ON

DECLARE @I INT

SET @I = 1

WHILE (@I <= 400000)

       BEGIN

        INSERT T2(주소, 전화) VALUES ('ABC', 'ABCD')

        SET @I = @I + 1

       END

SET NOCOUNT OFF

 

-- NON-CLUSTRED INDEX 생성

CREATE INDEX NC_ADDR ON T2(주소)

CREATE INDEX NC_ADDR_TEL ON T2(주소, 전화)

 

IF EXISTS(SELECT *  FROM SYSOBJECTS WHERE ID = OBJECT_ID('T2_COPY'))

   DROP TABLE T2_COPY

GO

--새로운테이블생성

CREATE TABLE T2_COPY(

ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

, 주소 CHAR(50) NULL

, 전화 CHAR(20) NOT NULL DEFAULT ''

)

 

-- NON-CLUSTRED INDEX 생성

CREATE INDEX NC_ADDR ON T2_COPY(주소)

CREATE INDEX NC_ADDR_TEL ON T2_COPY(주소, 전화)

 

-- T2의내용을T2_CPOY 테이블에이동

INSERT T2_COPY(주소, 전화)

SELECT 주소, 전화 FROM T1

 

 

/*-----------------------------------------------

 성능테스트결과

 

-- 조건1

             DURATION     CPU          READS  WRITES

1            2490         1188   72291  0

2            2681         1125   77293  0

3            2560         1156   77293  0

평균   2577         1156.3 75625.6      0

 

 

-- 조건2

             DURATION     CPU          READS  WRITES

1            39117        35469  4870443      5264

2            38014        35282  4887947      6216

3            38566        35359  4888387      3337

평균   38565.6             35370  4882259      4939

 

-------------------------------------------------*/

 

/*-----------------------------------------------

 결론

새로운테이블을생성하여테이터를이동시키는방법

이성능부하가더높다.

-------------------------------------------------*/

 

-- 테이블삭제

DROP TABLE T1

DROP TABLE T2

DROP TABLE T2_COPY


Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

[각주:1]SQL 버젼이 올라 가면서 SQL 2000 로 구축된 DB 를 SQL 2005 , 2008 로 업데이트가
많이 이루어 지고 있다.

얼마전 현재 SQL 2000의 서비스를 SQL2005 로 업데이트 해서 현재 운영 중이기도 하다.

하지만 가끔 SQL 2005로 구축 된 DB를 SQL 2000 으로 내릴려고 하면 어떻게 해야 할까?

SQL 2005 나 SQL 2008 의 경우 호환성 수준 변경을 통해 SQL 2000 , 2005 로 서비스를 할수 있다.

그래서 보통은 호환성 수준을 내려 놓고 백업을 해서 SQL 2000 이나 SQL 2005 에서 복원하면

되지 않을까 생각 하는 사람들이 있다. 결론 백업 파일을 상위버젼에서 백업을 하게 되면

하위 버젼에서 복원이 되지 않는 다는 것이다.

사용자 삽입 이미지


상위 버젼에서 위와 같은 방법으로 수준을 낮추어 TESTDB.BAK 라는 백업 파일을 만들어 보았다.

테스트 환경
- SQL 2008 에서 생성한 DB 를 호환성 80 모드로 변경하여 백업
- SQL 2008 에서 SQL2000 80모드로 백업한 파일은 SQL 2000 복원 테스트

백업후 정상적으로 백업이 되었는지 SQL 2008 에서 파일의 유효성 검사

사용자 삽입 이미지

SQL 2000 에서 파일의 유효성 검사
사용자 삽입 이미지
이런 에러가 발생한다.


 그럼 이제 SQL 2008에서 80호환성으로 백업한 데이터를 복원해보자 ~~~

  이런 에러가 발생하게 된다.
사용자 삽입 이미지
MS SQL 은 하위 호환성은 지원되지만, 상위 버젼 백업 파일을 하위 버젼에서 복구는
안되는것을 볼수 있다.



Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

SQL 2008 Policy Based Management

SQL 2008을 통해 현재 우리 회사에서 운영중인 데이터 베이스를 관리함에 있어서 편리한 기능에

대해서 알아 보도록 하자.

1.정책기반 관리

2.리소스 관리자

3.데이터 컬렉션

4.데이터 변경 내용 추적

4개의 각각의 세션을 통해 SQL2008을 이용하여 다른 SQL2008   SQL2005, SQL2000 관리를

자동화 하고 집중화 하여 성능 최적화에 도움을 받을수 있을 것이다.

 

 

정책 기반 관리

정책 기반 관리라 함은 일관성 서버관리의 필요성으로 나오게 되었습니다. 예로 현재 우리회사에서 사용하고 있는 데이터베이스 서버의 복구 모델은 전체(Full)로 설정한다. 저장 프로시져는 USP_ 시작해야 하며 , 테이블은 TBL_ 로 시작해야 한다. 등등 정책을 결정하여 배포함으로써 일관성 있는 서버관리를 할수 있게 됩니다. 이로인해 중요 시스템에 대한 변경을 모니터링하거나 변경을 방지 할수 있으며 손쉬운 정책 평가 및 정책 반영으로 인해 관리작업이 감소화 하여 궁극적으로는 기업 환경의 일관된 구성 정책을 가져올수 있게 됩니다.

 

관련 용어

- 패싯(Facets) : 특정 유형의 관리 대상에 대한 동작 또는 특징을 갖는 논리적 속성들의 그룹
-
조건(Conditions) : 패싯과 관련하여 정책 기반 관리에 의해 관리되는 대상에게 허용될 상태
                  
집합을 지정하는 판별식

- 정책(Policies) : 평가모드, 대상 필터 및 일정과 같은 정책 기반 관리 조건 및 예상 동작으로 하나의 조건만을 포함할수 있음

 

정책 기반 관리 만드는 순서

-       조건  --> 정책 (식을 먼저 정의 하고 정책을 구성한다.)



[그림1] SSMS 에서 관리 < 정책 관리 < 정책,조건,패식 세 항목이 보이실겁입니다.

사용자 삽입 이미지

                                          [그림1]



패싯(Facets)
패싯은 정책을 통해 관리 할수 있는 SQL Server 의 다양한 기능이라고 말할수 있습니다.
테이블의 정의 하는 테이블 패싯, 데이터베이스의 정책을 정의 하는 데이터베이스 패싯, 저장 프로시져의 정책을 정의 할수 있는 저장프로시져 패싯등이 있다.

 

사용자 삽입 이미지

조건(Conditions)
정책을 정의하기 위해서는 먼저 조건을 정의 해야만 합니다. 조건을 통해 어떤 패싯에 대한 속성을 어떻게 설정 할지는 정의 하는 것입니다. 예를 들어 테이블 이름을 ‘TBL_’로 시작되게 하겠다면 여러 부분으로 구성된 이름 패싯을 정하고 조건 항목에서 새조건을 선택하여 조건을 만들수 있습니다.

 

 

사용자 삽입 이미지

새 조건 만들기 창에서 테이블 이름을 TBL로 시작하도록 정의 하는 화면의 예를 보여 줍니다. 조건 이름을 테이블 이름 조건 이라고 했으면 패싯(F) 부분에서 여러부분으로 구성된 이름을 선택했음을 알수 있습니다. 또한 필드 부분에서 @Name 을 선택하고 연산자에 LIKE 를 선택했으며 값에는 ‘TBL%’을 입력해 테이블 이름이 ‘TBL’ 시작됨을 정의 합니다.

사용자 삽입 이미지
 테이블 이름 조건이라는 조건이 만들어 졌음을 알수 있습니다.

 

 

사용자 삽입 이미지

조건 확인(H) 부분에 적용할 조건 을 선택 합니다.


사용자 삽입 이미지
사용자 삽입 이미지
사용자 삽입 이미지



위와 같은 과정을 거쳐 sqlDB 라는 데이터베이스에 테이블 조건 정책을 정함으로써 정책 만들기가 완료 되었음을 알수 있습니다.


사용자 삽입 이미지
 


요청 시 실행 모드는 정책을 자동으로 실행하지 않고 필요 시 수동으로 평가하도록 하는 방법입니다. 평가에 대한 결과는 로그로 기록됩니다.

 

예약 시 실행 모드는 일정을 설정해 예약하면 해당 일정으로 정책에 대한 평가가 실행되도록 하는 방법입니다. 평가에 대한 결과를 로그로 기록됩니다.

 

변경 시 - 로그만 실행 모드는 개체에 대한 변경(생성 및 변경) 작업에 대해 실시간으로 정책이 평가되어 정책에 위배되는 경우 이에 대한 내용을 로그로 남기도록 하는 방법입니다.

 

변경 시 - 방지 실행 모드는 개체에 대한 변경(생성 및 변경) 작업에 대해 실시간으로 정책이 평가되어 정책에 위배되는 경우 해당 작업이 수행되지 못하도록 방지하는 방법입니다. 물론 로그도 기록됩니다.

 

실행 모드(E) 부분에 변경 시 - 방지를 선택하고 사용(E) 부분을 체크해 실시간으로 평가되어 정책 위반 시 해당 작업이 수행되지 못하도록 하겠다고 설정한 예입니다.



정책 실행 여부 테스트


모든 과정을 마친후 ‘TBL_’ 시작하는 테이블이 아닌 DDDD 라는 테이블을 만들어 보았습니다.
정책에 위반되기 때문에 만들지지 않고 에러가 발생함을 알수 있습니다.

 

사용자 삽입 이미지

 

 

수동으로 정책 평가

사용자 삽입 이미지

현재 구성된 데이터의 베이스의 정책을 수동으로 평가 할수 있습니다.

 

SQL 2008 이전의 DB 에서는 DDL 트리거를 코딩해서 관리해야 했지만 이제는 정책 기반 관리 기능을 통해서 더욱더 효율적 관리를 할수 있게 되었습니다. 한서어에서 만들어진 정책은 다은 서버또는 서버 그룹에 쉽게 반영 할수 있는 정책 가져오기, 내보내기 기능이 제공되어 관리효율 향상 및 비용 절감을 꾀할수 있게 되었습니다.


정책 저장소

정책은 msdb 저장되기 때문에 정책/조건 변경시 msdb 는 백업이 이루어져야 한다.
정책 평가 기록
- msdb 시스템 테이블 유지
- 다른 데이터베이스 엔젠, 기타 서비스 보존되지 않음
기본적인 정책 XML 파일
- 기본 설치 위치에서 " 정책 가져오기" 가능
- C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1042
정책 위한 경고
- 자동화된 평가 모드 중 위반이 발생하면 로그에 기록
- 메시지 번호로 경고 생성, 운영자에게 알림 가능

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


가끔 들어 오는 업무 중에 시점 복원을 해야 하는 경우가 있다. 이럴 경우 trn 백업 데이터를 다 적어
줘야 한다. 하지만 이 쿼리를 통해 스크립트로 복원 스크립트를 자동으로 생성해 낸다.

사용 하면 편할듯~~~~~~~

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = '복원할DB명'

SELECT @backup_set_id_start = MAX(backup_set_id
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
          AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

[원본사이트]
http://www.mssqltips.com/tip.asp?tip=1243
           http://www.mssqltips.com/tip.asp?tip=1243

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지
Problem
This is an opportunity for me to geek out on my favorite topic in Microsoft SQL Server: mining SQL Server metadata.  If you've read my tips over the past two years you may have seen that I am a metadata junkie.  This was developed from a need to automate as many tasks as possible over the past 9 years as the solo Database Administrator in my organization.  At a high point, we had 2,000 databases hosted across 80 SQL Server instances (all on separate servers).  We had one Database Administrator, Me. 

Thankfully, through consolidation, we've pared down the number of instances by 50% and I now have help through access to 30% of one of our Oracle DBAs.  Though things have improved, I still need to rely on automation and creativity in order to accomplish all the administrative tasks that confront a DBA on any given day.  We all know the unexpected issues that arise will do their best to destroy any forward progress you make on a daily basis. 

In this case it is the use of the System Tables from the msdb database to identify any databases, running under Full recovery, that have no log backups listed in the system's backup history.  In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.

Solution
Usually I advocate utilizing the Dynamic Management Objects to mine system data from Microsoft SQL Server.  However, backup history is not something stored in the Dynamic Management Views and Dynamic Management Functions (DMVs and DMFs, respectively.)  This information is stored in the msdb database. 

The msdb database is used to host information pertaining not to just backup and recovery metrics, but also all things SQL Server Agent: job history, job schedules, and so forth.  It is this backup information we're most-interested in here.

We can get all the information for this task out of a single msdb System Table:  msdb.dbo.backupset.  The important columns of note for this object are itemized below.  Asterisks denote the columns we'll be using in this query:

  • *database_name - name of the database the backup file pertains to.
  • *type - Type of backup process performed:
    • D = Database
    • F = File or Filegroup
    • G = Differential File
    • I = Differential (database)
    • L = Transaction Log
  • *backup_finsh_date - time when the backup process completed.
  • backup_start_date - time the backup process was initiated.
  • name - name of the backupset
  • user_name - user performing the backup process
  • expiration_date - date the backup expires
SELECT D.[name] AS [database_name], D.[recovery_model_desc]
FROM sys.databases D LEFT
JOIN 
   
(
   
SELECT BS.[database_name]

       
MAX(BS.[backup_finish_date]) AS
[last_log_backup_date]
   
FROM
msdb.dbo.backupset BS 
   
WHERE BS.type =
'L'
   
GROUP BY
BS.[database_name]
   
) BS1 ON D.[name] =
BS1.[database_name]
WHERE D.[recovery_model_desc] <>
'SIMPLE'
   
AND BS1.[last_log_backup_date] IS
NULL
ORDER BY D.[name];

This code queries msdb.dbo.backupset for a listing of databases that have recorded log backups and when the latest log backup occurred.  The results are then joined via an outer join construct back to the sys.databases System Catalog View on database_name.  Any database that exists on the SQL Server (as presented through the sys.databases view) running in Full or Bulk-Logged recovery, that does not exist in the list of databases with associated transaction log backups, is returned as a result of the query.

Query 2

Here is another query that will show you any databases that are in the Full or Bulk-Logged recovery model and have had a full backup without any transaction log backups after the last full backup.

SELECT
D.[name] AS [database_name],
D.[recovery_model_desc] 
FROM
sys.databases D LEFT
JOIN 
(
   
SELECT BS.[database_name]

   
MAX(BS.[backup_finish_date]) AS
[last_log_backup_date] 
   
FROM
msdb.dbo.backupset BS 
   
WHERE BS.type =
'L' 
   
GROUP BY
BS.[database_name] 
   
)
BS1 
ON D.[name] =
BS1.[database_name]
LEFT
JOIN 
(
   
SELECT BS.[database_name]

   
MAX(BS.[backup_finish_date]) AS
[last_data_backup_date] 
   
FROM
msdb.dbo.backupset BS 
   
WHERE BS.type =
'D' 
   
GROUP BY
BS.[database_name] 
)
BS2 
ON D.[name] =
BS2.[database_name]
WHERE
D.[recovery_model_desc] <>
'SIMPLE'
AND BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] <
BS2.[last_data_backup_date]
ORDER BY D.[name];

What are the implications of not issuing log backups against a database running in Full or Bulk-Logged recovery?  Without log backups, transaction logs are not going to be check-pointed and space will not be re-used.  Log files will grow (if Autogrowth is enabled) and space will eventually be consumed to a point that the SQL Server is unable to allocate space as needed where databases are set to Autogrow.  If Autogrowth is not enabled on identified databases, your users will eventually receive an error stating that their transactions could not be fulfilled because the transaction log is full.

What impacts the results?  The interval at which you delete your backup history will directly influence what results are presented through this query.  If you clear backup history metadata on a daily basis you may return false positives.  I strongly suggest clearing backup history information from your instances for any records over 31 days.  This not only will allow you to run queries like the one I present here and obtain decent, usable information; but it also ensures you do not encounter situations where your msdb database has grown to a significant size that could impact backup and recovery performance or space issues on your SQL Server instance.

[원본사이트]http://www.mssqltips.com/tip.asp?tip=1895

Posted by ㏈ª ☞ β┖υΕJini.κR
TAG SQL

댓글을 달아 주세요

SQLER(http://www.sqler.com)에서 SQL서버 기술 세미나를 개최합니다.


행사 제목
SQLER 2
회 정기 오프라인 기술 나눔 세미나 

제목 :  DBA 와 보안 (SQL Server 2005/2008 기준)

부제목 : 보안에서 솔루션(접근제어+암호화)이 차지하는 범위는?
대상 : 초중급 SQL서버 개발자 및 관리자


행사 소개글

점점 더 기업의 데이터에 대한 보안 위협이 심각해지고 있습니다. 2010년 IT업계의 10대 화두가 최근 속속 발표되고 있으며 그 중 빠지지 않는 항목이 바로 데이터 보안인데요, 지난 국내 탑 오픈마켓들이나, 통신사, 유수의 기업들이 보안과 사용자 정보에 대한 노출 등으로 기사화 되었던 일들을 살펴보면 올해에도 데이터 보안은 분명 중요하게 다뤄질 것입니다. 이에, SQLER에서는 먼저, SQL서버 2008의 보안에 대한 내용으로 어떻게 우리와 고객의 소중한 데이터와 개인정보를 보호할 수 있을지에 대해, 2010년 첫번째 세미나를 열어 보려고 합니다. SQLER분들의 많은 관심을 부탁 드립니다.


- 보안 체크리스트가 있습니다.

- 보안 솔루션을 소개합니다. (보안 체크리스트에서 어떤 부분을 담당할까요?)

- DBA 직접 보안 VS 보안 솔루션

 

세션 제목 + 아젠다
19:00 ~ 19:30
등록
19:30 ~ 20:30 SQL
서버 보안 체크 리스트 – SQL 프론티어 / 차주언 (저서: 윈도우 웹서버 보안, MSSQL 튜닝 가이드)

   - 방화벽++DB 각항목에서의 위험요소와 솔루션들

   - 20가지 SQL Server 2005/2008 권장 보안 + α

   - 25가지 프로그래밍 오류

   - 데모 – 유용한 보안 스크립트

20:30 ~ 20:45 휴식

20:45 ~ 21:45 우리의 데이터 우리가 지켜보자 - SQL 프론티어 / 이승연

   - 간단한 SQL 인젝션 데모 와SQL 인젝션 해결책

   - 그리고 SQL 데이터 암호화 까지 할 수 있는 건 다 해보자!!

21:45 ~ 22:00 Q&A 및 경품추첨


세미나 참석 회비

이번에 처음으로 회비로 기부금 모금을 진행하게 되었습니다.  회비는 세미나 당일 현장에서 모금되며 1,000원 입니다. - 모금된 회비는 SQLER의 이름으로, 전액 굿네이버스(http://www.goodneighbors.kr) 결식아동 돕기 기금으로 기부됩니다. SQLER는 운영자들 모두의 열정으로 움직이는 비영리 커뮤니티 입니다.


시간, 장소, 날짜
2010년 1월 27일 수요일 / 오후 7시 30분 /  포스코센터 서관 5층 한국 마이크로소프트 대회의실

본 세미나는 커뮤니티가 주최하는 비영리세미나입니다. 죄송하게도 주차권은 제공되지 않으니 대중교통을 이용해 주시길 바랍니다.

사용자 삽입 이미지

세미나 등록

http://www.onoffmix.com/e/konan94/1309

중간의 "등록"을 진행해 주세요.

경품
마이크로소프트 무선 옵티컬 데스크톱 700 키보드 마우스 세트 1개

SQL Server 2008 MDX 1권
#경품은 사정에 의해 변경될 수 있습니다.


현재 모집 상태 

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지

SQLER.COM 기술 시삽으로 활동중이라 SQL 관련 질문이 올라 오면 답변을 달아 주곤 한다.

CROSS APPLY 를 활용해서 카테고리 별로 N개 데이터를 가져 올때 어떻게 하는지에 대한

질문이 올라와 CROSS APPLY 를 이용하여 한번 만들어 보았다.

CREATE TABLE MyTempTable (

       [카테고리] VARCHAR(10),

       [날짜] SMALLDATETIME,

       [내용] VARCHAR(10)

);

 

INSERT INTO MyTempTable VALUES ('일반','2009-12-01','내용');

INSERT INTO MyTempTable VALUES ('질문','2009-12-02','내용');

INSERT INTO MyTempTable VALUES ('질문','2009-12-03','내용');

INSERT INTO MyTempTable VALUES ('질문','2009-12-04','내용');

INSERT INTO MyTempTable VALUES ('일반','2009-12-05','내용');

INSERT INTO MyTempTable VALUES ('일반','2009-12-06','내용');

INSERT INTO MyTempTable VALUES ('일반','2009-12-07','내용');

INSERT INTO MyTempTable VALUES ('질문','2009-12-08','내용');

INSERT INTO MyTempTable VALUES ('자료실','2009-12-09','내용');

INSERT INTO MyTempTable VALUES ('자료실','2009-12-10','내용');

INSERT INTO MyTempTable VALUES ('자료실','2009-12-11','내용');

 

-- CROSS 를 이용하여 카테고리별 TOP 3개의 데이터를 가져와 보자.

SELECT C.카테고리, A.날짜, A.내용

FROM(

     SELECT 카테고리

     FROM MyTempTable

     GROUP BY 카테고리

     ) AS C

CROSS APPLY(

     SELECT TOP(3) 날짜, 내용

     FROM MyTempTable AS O

     WHERE O.카테고리 = C.카테고리

     ORDER BY 날짜 DESC

     ) AS A;


-- 테이블 함수를 이용하여 N 개를 지정 할수 있도록 해보자.

CREATE FUNCTION dbo.fn_Top

  (@카테고리 VARCHAR(10),@n AS INT)

  RETURNS TABLE

AS

RETURN

  SELECT TOP(@n) 날짜, 내용

  FROM MyTempTable

  WHERE 카테고리 = @카테고리

  ORDER BY 날짜 DESC;

GO

 

-- CROSS 를 이용하여 카테고리별 TOP N개의 데이터를 가져와 보자. 

SELECT C.카테고리, A.날짜, A.내용

FROM(

        SELECT 카테고리

        FROM MyTempTable

        GROUP BY 카테고리

        ) AS C

  CROSS APPLY dbo.fn_Top(C.카테고리, 3) AS A;

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

SQL 2005 부터 인덱스와 관련하여 개선된 기능 중 하나인 온라인 인덱스 작업에 대하여 알아 보겠습니다. 온라인 인덱스 작업은 1년 365일 운영되어야 하는 시스템을 관리하는 DBA들에게 획기 적인 기능입니다. 온라인 인덱스를 할수 있게 됨으로써 다운 타임을 감소할수 있게 되었으며 인덱스를 재구성할려고 할때도 소요 시간 때문에 많은 고민을 해야 하는 수고를 덜어 줄수 있게 되었습니다. Create Index , Alter Index , Drop index Alter Table 명령어에서 ONLINE 옵션을 사용할수 있습니다.

온라인 인덱스 구성 vs. 오프라인 인덱스 구성
ONLINE 옵션은 이러한 인덱스 작업을 수행하는 동안에도 사용자가 테이블이나 클러스터형(Clustered) 인덱스와 관련된 비클러스터형(Nonclustered) 인덱스에 접근할수 있습니다. 오프라인으로 클러스터형 인덱스를 구성하거나 재구성하는 등의 DDL 작업을 하면 이는 해당 데이터와 관련 인덱스에 배타적 잠금을 보유하게 되고 이로 인해 다른 사용자가 데이터나 인덱스에 접근하지 못하도록 방해하게 된다.

Example:

Create index idx_t ON t(c1, c2)

WITH (ONLINE = ON)

일반 인덱스 구성 vs. 병렬 인덱스 구성
멀티 프로세서를 가진 SQL서버에서 인덱스 구문 또한 다른 쿼리를 실행할 때처럼, 스캔, 정렬, 그리고 구성 작업을 수행하는데 병렬 처리가 가능 해졌습니다. 병렬 처리 수는 최대 병렬 처리 수(sp_configure로 설정한), MAXDOP 인덱스 옵션, 현재 작업부하의 크기, 파티션되지 않은 경우, 첫 번째 키 칼럼의 데이터 분포등에 의해서 결정될 수 있다.

Example:

Create index idx_t ON t(c1, c2)

WITH (MAXDOP = 2)

-- 인덱스 구성에 2개의 프로세서 사용


온라인 인덱스 가능한 SQL 버전
Microsoft SQL Server 2005 Enterprise Edition


온라인 인덱스는 어떻게 동작 할까?

사용자 삽입 이미지

인덱스 DDL(데이터 정의 언어) 작업 중에 동시 사용자 작업이 가능하도록 하려면 온라인 인덱스 작업 중에 원본 및 기존 인덱스, 대상 및 임시 매핑 인덱스(클러스터형 인덱스의 경우) 로 구성됨으로써 온라인 인덱스 작업 수행을 할수 있도록 합니다.

원본 및 기존 인덱스
원본은 원래 테이블이거나 클러스터형 인덱스 데이터입니다. 기존 인덱스는 원본 구조와 관련된 모든 비클러스터형 인덱스입니다. 예를 들어 온라인 인덱스 작업이 네 개의 관련 비클러스터형 인덱스가 있는 클러스터형 인덱스를 재구성하는 것이라면 원본은 기존 클러스터형 인덱스이고 기존 인덱스는 비클러스터형 인덱스입니다.
기존 인덱스는 여러 사용자의 동시 선택, 삽입, 업데이트 및 삭제 작업에 사용됩니다. 여기에는 대량 삽입(지원되지만 권장하지 않음)과 트리거 및 참조 무결성 제약 조건에 의한 암시적 업데이트가 포함됩니다. 쿼리와 검색에 모든 기존 인덱스를 사용할 수 있습니다. 즉, 기존 인덱스를 쿼리 최적화 프로그램에서 선택할 수 있으며 필요한 경우 인덱스 힌트에 지정할 수 있습니다.

대상
대상은 만들거나 다시 작성하는 새 인덱스(또는 힙)이거나 새 인덱스 집합입니다. SQL Server 데이터베이스 엔진에서는 인덱스 작업을 수행하는 동안 원본에 대한 사용자 삽입, 업데이트 및 삭제 작업을 대상에 적용합니다. 예를 들어 온라인 인덱스 작업이 클러스터형 인덱스를 다시 작성하는 것이라면 대상은 다시 작성되는 클러스터형 인덱스입니다. 데이터베이스 엔진에서는 클러스터형 인덱스를 다시 작성할 때 비클러스터형 인덱스를 다시 작성하지 않습니다.
대상 인덱스는 인덱스 작업이 커밋될 때까지 SELECT 문을 처리하는 동안 검색되지 않습니다. 내부적으로 인덱스가 쓰기 전용으로 표시됩니다.

임시 매핑 인덱스
클러스터형 인덱스를 만들거나 삭제하거나 다시 작성하는 온라인 인덱스 작업에는 임시 매핑 인덱스도 필요합니다. 이러한 임시 인덱스는 동시 트랜잭션이 기본 테이블의 행이 업데이트되거나 삭제될 때 작성되는 새 인덱스에서 삭제할 레코드를 결정하는 데 사용합니다. 이러한 비클러스터형 인덱스는 새 클러스터형 인덱스(또는 힙)와 같은 단계에서 만들어지므로 별도의 정렬 작업이 필요 없습니다. 동시 트랜잭션의 모든 삽입, 업데이트 및 삭제 작업에서도 임시 매핑 인덱스가 유지됩니다.

참고 사이트

 

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/08/index-build-strategy-in-sql-server-introduction-i.aspx

http://technet.microsoft.com/ko-kr/library/ms191261.aspx

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

이전 버전에서는 아래와 같은 작업을 할때 파일의 공간이 부족해서 파일의 공간을 늘릴려고 할때 일반적인 경우는 파일을 0 으로 초기화 한후 일련의 작업이 발생하기 때문에 OverHead 가 발생하게 됩니다.그리하여 서비스 중이 아닌 점검 중이거나 사용자의 많이 없는 시간을 통해 아래와 같은 작업을 수행 하였습니다.  

 

   - Create a database.
    - Add files, log or data, to an existing database.

    - Increase the size of an existing file (including autogrow operations).
    - 
Restore a database or filegroup.

 

하지만  SQL 2005부터는 위와 같은 작업을 할 때 데이터 파일을 즉시 초기화 할 수 있습니다. 기존에 삭제된 데이터 파일을 0으로 채우지 않고 그 위에 덮어 씀으로써 운영체제에서 초기화 과정을 거치지 않게 되었습니다. 그리하여 위와 같은 작업을 신속하게 수행할수 있게 되었습니다.

사용 제한 조건
Microsoft Windows XP Professional 또는 Windows Server 2003이상의 버전


보안 고려 사항
기본적으로
SQL Server 서비스 계정과 포컬 관리자에게만 파일 엑세스를 허용 하기 때문에 Administrators 그룹을 권한을 가지고 있어야 합니다. 만약 SQL Server 서비스 계정이 바뀌었다면 아래의 보안 설정의 권한도 변경 해주셔야 합니다.

관리도구 > 로컬 보안 정책 > 로컬 정책 > 사용자 권한 할당

사용자 삽입 이미지


참고 사이트

http://msdn.microsoft.com/en-us/library/ms175935.aspx
http://sqlserverpedia.com/blog/sql-server-management/vldbs-and-instant-file-initialization/

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지


                       컴파일 해보실수 없는 분들 위해 DLL 파일 첨부 합니다. SQL 2005부터 .NET CLR 을 SQL 에서 사용 할수 있게 됨으로써 복잡한 계산이나

복잡한 쿼리를 짤 필요 없이 프로그램을 이용하 가능 해져서 유용하게 사용 할수

있게 되었다.

'Inside Microsoft SQL Server 2005: T-SQL Programming'에 나온 예제를 
이용

SQL
에서 사용 할수 있는 CLR 정규식 함수를 한번 만들어 보자.

  

우선 비쥬얼 스튜디오에서 프로젝트 생성을 한다.

 

New Project -> Visual C# -> Windows -> 클래스 라이브러리

C# 아래 소스를 컴파일 하여 DLL 파일로 만든다.

using
System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
//using System.Diagnostics;
//using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace ClrUtility
{
    public partial class ClrUtility
    {
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlBoolean fn_RegExMatch(SqlString inpStr, SqlString regExStr)
        {
            if (inpStr.IsNull || regExStr.IsNull)
                return SqlBoolean.Null;
            else
                return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value, RegexOptions.CultureInvariant);
        }
    }
}

만들어진 DLL 파일을 SSMS 에서 아래 쿼리를 실행하여 등록 한다.

SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
USE AdventureWorks;

CREATE ASSEMBLY ClrUtility
    FROM 'C:\SqlServerProject1.dll'
    WITH
PERMISSION_SET = SAFE;

  • SAFE: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 없다. 기본값이다.
  • EXTERNAL_ACCESS: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 있다.
  • UNSAFE: EXTERNAL_ACCESS 권한 + unmanaged 코드 실행 권한


    EXTERNAL_ACCESS 이상의 권한을 주기 위해서는 아래와 같은 명령어를 통해 TRUSTWORTHY
    속성을 변경 해야 한다.


    USE AdventureWorks;

    CREATE FUNCTION dbo.fn_RegExMatch
        (@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX))
    RETURNS BIT
    EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch;


    등록된 DLL 파일을 사용자 정의 함수로 등록 한다.

    USE AdventureWorks;

    SELECT
     dbo.fn_RegExMatch(FirstName, '[a-z]+')
    FROM Person.Contact;


    사용자 삽입 이미지

    SQL 쿼리로 구현하기 힘들었거나 복잡했던 걸 .NET 환경 CLR 를 통해 쉽게 구현
    할수 있는 좋은 기능인 듯 하다. 프로그램을 조금 할수 있는 사람이면 누구나 쉽게 구현하고
    SQL 에서 가져다 쓸수 있는 좋은 기능 인듯 하다.
    참고 : Inside Microsoft SQL Server 2005

  • Posted by ㏈ª ☞ β┖υΕJini.κR
    TAG CLR

    댓글을 달아 주세요

    1. 2009.12.02 16:14 악당  댓글주소  수정/삭제  댓글쓰기

      경진아 하이, 하이

    2. 2009.12.02 16:17 β┖υΕJini.κR  댓글주소  수정/삭제  댓글쓰기

      네 저도 하이 예요..ㅎㅎ

    사용자 삽입 이미지
    연계 참조 무결성 제약 조건 (Cascading Referential Integrity Constraint)

    FOREIGN KEY 제약 조건이 적용된 컬럼들에 대한 데이터 변경이 자주 있는 경우, 이러한 데이터 변경을 처리 하기 위해서는 트리거나 저장 프로시저, 배치 쿼리와 같은 형식으로 관계된 모든 테이블을 처리해야만 하였다. 하지만 SQL 2005부터 새롭게 추가된 기능인 연계 참조 무결성 제약 조건을 통하여 외부키가 잡힌 데이터의 수정 혹은 삭제시 관련된 테이블의 데이터를 동시에 삭제하거나 수정이 가능 하게 되었다.

     

    CREATE TABLE ALTER TABLE 문의 REFERENCES 절에는 ON DELETE ON UPDATE 절을 사용할 수 있고 외래 키 관계 대화 상자를 사용하여 연계 작업을 정의할 수도 있다.

     

    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

     

    ON DELETE 또는 ON UPDATE를 지정하지 않으면 NO ACTION이 기본값이 되며 ON DELETE NO ACTION는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 오류가 발생하고 DELETE 문이 롤백된다. ON UPDATE NO ACTION는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행에서 키 값을 업데이트하려고 하면 오류가 발생하고 UPDATE 문이 롤백된다.

     

    CASCADE, SET NULL SET DEFAULT를 사용하면 내용이 수정된 테이블로 역추적할 수 있는 외래 키 관계를 갖도록 정의된 테이블에 키 값의 삭제 또는 업데이트가 적용되도록 할 수 있으며 대상 테이블에서도 연계 참조 작업이 정의되어 있다면 테이블에서 삭제 또는 업데이트된 행에 대해서도 지정된 연계동작이 적용된다. 예외로 timestamp열이 있는 외래키나 기본키에는 CASCADE를 지정할 수 없으며 Instead If 트리거가 존재 하는 테이블에 대해서도 지정할수 없다.  

     

    ON DELETE CASCADE는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제가 된다.

     

    ON UPDATE CASCADE는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트하려고 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트된다. .

     

    그럼 이제 어떤식으로 사용되는지 알아 보도록 하자.

     

    CASCADE 옵션

     

    CREATE TABLE [마미테이블_CASCADE]

    (

           [컬럼] INT PRIMARY KEY,

           [컬럼] VARCHAR(20)

    )

    GO

    CREATE TABLE [자식테이블_CASCADE]

    (

           [컬럼] INT IDENTITY(1,1),

           [컬럼] INT

    )

    GO

     

    ALTER TABLE [자식테이블_CASCADE]

           ADD CONSTRAINT [포린키_캐스캐이드테스트_CASCADE]

           FOREIGN KEY ([컬럼]) REFERENCES [마미테이블_CASCADE]([컬럼])

                 ON DELETE CASCADE

                 ON UPDATE CASCADE         

    GO

     

    --ALTER TABLE [자식테이블_CASCADE] DROP  CONSTRAINT [포린키_캐스캐이드테스트_CASCADE]

     

    INSERT [마미테이블_CASCADE] VALUES(1,'테스트')

    INSERT [마미테이블_CASCADE] VALUES(2,'테스트')

    INSERT [마미테이블_CASCADE] VALUES(3,'테스트')

    INSERT [마미테이블_CASCADE] VALUES(4,'테스트')

    INSERT [마미테이블_CASCADE] VALUES(5,'테스트')

     

    SELECT * FROM [마미테이블_CASCADE]

     

    INSERT [자식테이블_CASCADE](컬럼) VALUES(1)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(1)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(2)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(2)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(4)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(5)

    INSERT [자식테이블_CASCADE](컬럼) VALUES(5)

     

     

    SELECT * FROM [마미테이블_CASCADE]

    SELECT * FROM [자식테이블_CASCADE]

     

    DELETE FROM [마미테이블_CASCADE]  WHERE [컬럼] = 1

    -- 부모테이블에서삭제를하면자식테이블도영향을받는다.

    SELECT * FROM [마미테이블_CASCADE]

    SELECT * FROM [자식테이블_CASCADE]

     

    DELETE FROM [자식테이블_CASCADE]  WHERE [컬럼] = 2

    -- 자식테이블에서삭제를하면부모테이블만영향을받지않는다.

    SELECT * FROM [마미테이블_CASCADE]

    SELECT * FROM [자식테이블_CASCADE]

     

    UPDATE [마미테이블_CASCADE] SET [컬럼] = 33 WHERE [컬럼] = 3

    SELECT * FROM [마미테이블_CASCADE]

    SELECT * FROM [자식테이블_CASCADE]

    -- 부모테이블에서수정을하면자식테이블도영향을받는다.

     

    UPDATE [자식테이블_CASCADE] SET [컬럼] = 44 WHERE [컬럼] = 4

    -- 자식테이블에서수정으로제약조건에따라오류가발생한다.

     

    NO ACTION 옵션

    CREATE TABLE [마미테이블_NOACTION]

    (

           [컬럼] INT PRIMARY KEY,

           [컬럼] VARCHAR(20)

    )

    GO

    CREATE TABLE [자식테이블_NOACTION]

    (

           [컬럼] INT IDENTITY(1,1),

           [컬럼] INT

    )

    GO

     

    ALTER TABLE [자식테이블_NOACTION]

           ADD CONSTRAINT [포린키_캐스캐이드테스트_NOACTION]

           FOREIGN KEY ([컬럼]) REFERENCES [마미테이블_NOACTION]([컬럼])

                 ON DELETE NO ACTION

                 ON UPDATE NO ACTION

    GO

     

    ALTER TABLE [자식테이블_NOACTION] DROP  CONSTRAINT [포린키_캐스캐이드테스트_NOACTION]

     

    INSERT [마미테이블_NOACTION] VALUES(1,'테스트')

    INSERT [마미테이블_NOACTION] VALUES(2,'테스트')

    INSERT [마미테이블_NOACTION] VALUES(3,'테스트')

    INSERT [마미테이블_NOACTION] VALUES(4,'테스트')

    INSERT [마미테이블_NOACTION] VALUES(5,'테스트')

     

    SELECT * FROM [마미테이블_NOACTION]

     

    INSERT [자식테이블_NOACTION](컬럼) VALUES(1)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(1)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(2)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(2)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(4)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(5)

    INSERT [자식테이블_NOACTION](컬럼) VALUES(5)

     

    SELECT * FROM [자식테이블_NOACTION]

     

    DELETE FROM [마미테이블_NOACTION]  WHERE [컬럼] = 1

    -- 자식테이블에데이터가있기때문에제약조건에따라오류가발생한다.

     

     

    DELETE FROM [자식테이블_NOACTION]  WHERE [컬럼] = 1

     

    DELETE FROM [마미테이블_NOACTION]  WHERE [컬럼] = 1

     

    SELECT * FROM [마미테이블_NOACTION]

    SELECT * FROM [자식테이블_NOACTION]

     

    다중 CASCADE 옵션

     

    CREATE TABLE [다중부모_CASCADE]

    (

           [컬럼] INT PRIMARY KEY,

           [컬럼] VARCHAR(20)

    )

    GO

    CREATE TABLE [다중자식_CASCADE]

    (

           [컬럼] INT PRIMARY KEY,

           [컬럼] INT

    )

    GO

     

    CREATE TABLE [다중손자_CASCADE]

    (

           [컬럼] INT PRIMARY KEY,

           [컬럼] INT

    )

    GO

     

    ALTER TABLE [다중자식_CASCADE]

           ADD CONSTRAINT [포린키_캐스트]

           FOREIGN KEY ([컬럼]) REFERENCES [다중부모_CASCADE]([컬럼])

                 ON DELETE CASCADE

                 ON UPDATE CASCADE         

    GO

     

    ALTER TABLE [다중손자_CASCADE]

           ADD CONSTRAINT [포린키_다중캐스트]

           FOREIGN KEY ([컬럼]) REFERENCES [다중자식_CASCADE]([컬럼])

                 ON DELETE CASCADE

                 ON UPDATE CASCADE         

    GO

     

    INSERT [다중부모_CASCADE] VALUES(1,'테스트')

    INSERT [다중부모_CASCADE] VALUES(2,'테스트')

    INSERT [다중부모_CASCADE] VALUES(3,'테스트')

     

    SELECT * FROM [다중부모_CASCADE]

     

    INSERT [다중자식_CASCADE] VALUES(1,1)

    INSERT [다중자식_CASCADE] VALUES(2,2)

    INSERT [다중자식_CASCADE] VALUES(3,3)

     

    INSERT [다중손자_CASCADE] VALUES(1,1)

    INSERT [다중손자_CASCADE] VALUES(2,1)

    INSERT [다중손자_CASCADE] VALUES(3,2)

    INSERT [다중손자_CASCADE] VALUES(4,3)

     

     

    DELETE FROM [다중부모_CASCADE] WHERE [컬럼] = 1


    참고 : MSDN  

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요