곰탱이의 개인블로그

일전에 모임 했을 때 Collation 이야기가 나왔었는데요.

이에 대해 간단히 이야기해 볼까 합니다.

당연히… Collation을 아시는 분들이 많을 텐데요.

Collation을 그대로 번역하면 “책 페이지 순서”입니다.

우리나라 한글로 하면 “가나다”순 인데요.

이것을 각 나라 언어를 어떤 기준으로 정렬할 지 나타낸 것이라고 보면 되겠군요.

한글용어로는 “데이터 정렬”이라는 용어를 사용합니다.

SELECT SERVERPROPERTY(N'Collation')

명령으로 현재 기본 Collation을 확인할 수 있습니다. 

물론 DB마다, Table마다 다른 정렬을 사용할 수 있습니다.

보통 Latin1_General_ CI_AS_KI_WI 이런 식으로 되어 있는데요.

이것을 나눠서 살펴 보도록 하겠습니다.

1.     Latin1_General : 언어구성을 말합니다. 각 나라의 언어를 영문으로 표기하는 부분입니다.

-  한글은 Korean_Wansung (한글 완성형, 흠… 완성형/조합형 에 대한 설명은 패스~)

-  중국어는 Chinese_PRC

보통 이런 식입니다.

2.     CI : 대/소문자 구분 부분을 말합니다.

-  CI : Case Insensitive - 대/소문자를 구분하지 않음을 말합니다.

-  CS : Case Sensitive – 대/소문자를 구분합니다.

대문자와 소문자가 구분되어 있는 나라가 아니면 CI 를 사용한다고 보시면 됩니다.

즉, 영어의 A와 a 를 같은 것으로 볼 것인지 결정하는 것이죠.

CI일 경우 A와 a를 같게 인식합니다.

-- 혹자는 MSSQL이 대소문자 구분을 하지 않아 이상한 DB라는 분이 계시는데,

   이건 MSSQL 특성을 전혀 모르고 하는 이야기이죠. -_- 설정값을 바꾸면 되는데 말이죠.

3.     AS : 악센트(성조)에 대한 구분입니다.

-  AI : Accent Insensitive – 악센트를 구분하지 않습니다.

-  AS : Accent Sensitive – 악센트를 구분합니다.

이것을 구분할 경우 'a'와 'ấ'는 서로 다르게 인식합니다. 

중국어에도 성조가 있듯, 언어의 높낮이를 구분할 경우 AI가 아닌 AS를 사용합니다.

4.     KI : 일본어 가나 구분입니다.

-  KI : Kana Insensitive – 가나를 구분하지 않습니다.

-  KS : Kana Sensitive – 가나를 구분합니다.

새삼 일본의 위력(?)을 느끼게 하네요. 가나구분이라니요.

가나란 영어/라틴의 대/소문자 구분과 비슷한 히라가나와 가타가나를 구분함을 의미합니다.

あいうえお 와 アイウエオ를 각각 다른단어로 인식할 지 여부를 결정하는 것이죠.

… 일본어의 히라가나/가타가나에 대한 상세한 설명은 패스합니다.

5.     WS : 전자/반자 구분입니다.

-  WI : Width Insensitive – 전자/반자 구분을 하지 않습니다.

-  WS : Width Sensitive – 전자/반자를 구분합니다.

전자와 반자는 같은 글자라도 2Byte를 차지하는 전자인지, 1Byte를 차지하는 반자인지 구분해 준다는 거죠.

예를 들어 보겠습니다.

DECLARE @A NVARCHAR(10)

DECLARE @B NVARCHAR(10)

SET @A = N'０1234567890'

SET @B = N'01234567890'

SELECT CASE WHEN @A = @B THEN '=' ELSE '<>' END

è  결과는 “=”으로 같다고 나옵니다.

    물론 설정하지 않을 경우 Default가 WI이기 때문입니다.

l  어느 사이트에 보면 한자의 전자와 반자, 간체 구분 등으로 설명하는데,  조금 슬프네요.-_-

MSSQL 한글버젼의 경우 기본값은 Korean_Wansung_CI_AS 입니다.

즉, “한글완성형_대/소문자구분안함_악센트구분함”인데요.

우리나라 고어의 경우엔 성조가 있었으나, 현재는 사실상 성조가 없습니다.

그럼에도 불구하고, AS가 기본값인 것은 아이러니입니다.

만약 DB마다 Collation이 다르면 서로 JOIN이 되지 않아서 일일이 JOIN 시 Collation을 지정해 줘야 하는 등 여러 가지 제약조건이 따르겠죠?

자세한 사항은 아래 내용을 참고하세요.

http://msdn.microsoft.com/ko-kr/library/ms144250.aspx

쓸 때에는 몰랐는데,  써 놓고 나니 이상한 데가 많네요. 좀 고쳤어요. -_-

안녕하세요? [쓸만한게없네] 윤선식입니다.

인덱스를 생성할 때 옵션들이 많은데요.

오늘은 이에 대해 간단하게 알아볼까 합니다.

대용량 데이터베이스를 관리할 때에는 특히 이러한 옵션들이 중요합니다.

아래 CREATE INDEX 옵션들은 2008 기준입니다. 2008 이전 버전과는 사용법의 차이가 있으니 주의하세요.

<relational_index_option> ::=

{

    PAD_INDEX = { ON | OFF }

  | FILLFACTOR = fillfactor

  | SORT_IN_TEMPDB = { ON | OFF }

  | IGNORE_DUP_KEY = { ON | OFF }

  | STATISTICS_NORECOMPUTE = { ON | OFF }

  | DROP_EXISTING = { ON | OFF }

  | ONLINE = { ON | OFF }

  | ALLOW_ROW_LOCKS = { ON | OFF }

  | ALLOW_PAGE_LOCKS = { ON | OFF }

  | MAXDOP = max_degree_of_parallelism

  | DATA_COMPRESSION = { NONE | ROW | PAGE}

     [ ON PARTITIONS ( { <partition_number_expression> | <range> }

     [ , ...n ] ) ]

}

1.     PAD_INDEX & FILLFACTOR

-       PAD_INDEX는 FILLFACTOR에서 지정한 인덱스의 채우기 비율을 사용할 것인지 여부를 결정합니다.

-       FILLFACTOR는 1과 100 사이를 사용합니다.

l  혹 어떤 분들은 FILLFACTOR를 얼마나 사용해야 하는지 물어보시는 분들이 많은데요, 이것은 분석 여부에 따라 달라질 수 있습니다. 데이터 변경이 자주 일어난다면 숫자를 낮게 주고, 그렇지 않다면 높게 주어도 될 것입니다.

l  참고로, Oracle의 PCT_FREE와는 정 반대의 개념입니다. PCT_FREE 는 얼마나 남겨둘 지를 결정하고, FILLFACTOR는 얼마나 채워둘 것인지를 결정합니다.

2.     SORT_IN_TEMPDB

-       간단히 말해서 인덱스 생성을 tempdb에서 하고 그 최종본만 실제 인덱스에 반영하는 것입니다.

-       장점은 인덱스 생성속도가 적게 걸린다는 것이고, 단점은 tempdb 가 커진다는 것입니다.

3.     IGNORE_DUP_KEY

-       UNIQUE INDEX를 생성할 때 키 중복(UNIQUE)에 대한 검사 여부를 지정합니다.

-       사실 이 기능을 ON으로 하면 UNIQUE하지 않은 행만 실패하게 되므로… 사용하시지 않는 것을 권장합니다.

4.     STATICS_NORECOMPUTE

-       통계를 다시 생성할 지 여부를 결정합니다. 기본값이 OFF인데요. 굳이 ON으로 해서 통계를 생성하지 않을 필요는 거의 없습니다.

5.     DROP_EXISTING

-       인덱스를 전체적으로 삭제하고 다시 작성할 지 여부를 결정합니다. 기본값은 OFF입니다만,  인덱스 명명 규칙이 있을 경우 이미 기존 명칭이 있을 것이므로, 삭제하고 다시 생성하기 위애 ON 으로 주어야 합니다.

6.     ONLINE

-       단순하게 바라보면, 인덱스 생성 중에 테이블을 사용할 수 있는지 여부를 지정하는 것이지만, 다시 말하자면, 인덱스를 작성하면서 TABLE LOCK을 걸지 않도록 합니다.

-       SQL 2005부터 지원되면서 DBA들에겐 큰 힘을 주었던 옵션이며 기본값이 OFF이므로 사용하려면 ON 으로 별도 지정해 주어야 합니다.

7.     ALLOW_ROW_LOCKS & ALLOW_PAGE_LOCKS

-       행이나 페이지 LOCK 여부를 결정하며 기본값이 ON입니다. 많이 사용하는 옵션은 아닙니다.

8.     MAXDOP

-       테이블의 크기가 클 경우 부하를 줄이기 위해 CPU 병렬작업을 수행할 지 결정하며 CPU 개수 64라는 값까지 줄 수 있습니다.

-       참고로 Standard Edition은 지원하지 않습니다. 엄밀히 따지면 평가판을 빼면 Enterprise Edition만 된다고 하는 편이 낫겠네요. ㅋ.

9.     DATA_COMPRESSION / ON PARTITIONS

-       DATA_COMPRESSION은 테이블을 압축하는 것과 같이 데이터 압축 여부를 선택합니다.

-       ON PARTITIONS 옵션은 DATA_COMPRESSION 옵션을 사용할 때에만 적용됩니다.

-       DATA_COMPRESSION 옵션은 NONE / ROW / PAGE 등의 옵션이 있습니다.

이상입니다. ^^.

안녕하세요?

MSSQL 에서 와일드카드라 하면 흔히들 "%"를 많이 생각하시는데요,

이거 이외에도 크게 두가지가 더 있고, 이로 인해서 문제가 발생할 수도 있습니다.

CREATE TABLE LIKE_TEST

(

       PKEY INT PRIMARY KEY,

       DETAIL VARCHAR(200)

)

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (1, '나의 살던 고향은 꽃피는 산골')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (2, '나의 살던 [고향은] 꽃피는 산골')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (3, '나의 살던 고향은 꽃피는 시골')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (4, '나의 살_ 고향은 꽃피는 사골')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (5, '13579')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (6, 'ZACEF')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (7, '1D3FB')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (8, '3^F57')

INSERT INTO LIKE_TEST (PKEY, DETAIL) VALUES (9, 'CA10%')

1. %

가장 많이 사용하는 와일드카드 입니다. 모든 문자를 포함한다고 보시면 됩니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '나의%';

 결과

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

PKEY   DETAIL

1      나의 살던 고향은 꽃피는 산골

2      나의 살던 [고향은] 꽃피는 산골

3      나의 살던 고향은 꽃피는 시골

4      나의 살_ 고향은 꽃피는 사골

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

 * 아시다시피 INDEX가 있는 컬럼인경우 '~%'와 같이 사용하시면 인덱스를 사용하실 수 있지만, '%~'와 같이 사용하시면 인덱스를 사용할 수 없습니다.

2. _  

 해당하는 글자만큼의 와일드카드입니다. "_"를 사용한 만큼의 문자만 적용됩니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '나의 살_ 고향은 꽃피는 _골';

 결과--> 앞부분의 "_"는 "던"과 "_"가 뒷부분의 "_"는 "산골"과 "시골"이 이에 해당합니다.

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

PKEY   DETAIL

1      나의 살던 고향은 꽃피는 산골

3      나의 살던 고향은 꽃피는 시골

4      나의 살_ 고향은 꽃피는 사골

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

3. [ ] 패턴일치

해당하는 패턴(정규식) 집합 안에 포함되는 하나의 문자와 일치합니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '[0-9][0-9][0-9]%';

 결과--> 앞의 세글자가 각각 숫자인 것을 찾아냅니다.

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

PKEY   DETAIL

5      13579

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

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%[A-Z]%';

 결과--> 문자열 중 A-Z 사이, 즉 영문자가 있는 것을 찾아냅니다.

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

PKEY   DETAIL

6      ZACEF

7      1D3FB

8      3^F57

9      CA10%

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

4. [ ] 패턴 제외

 해당하는 패턴(정규식) 집합 안에 포함되지 않는 하나의 문자와 일치합니다.

제가 쓰고 나서 봐도 좀 어렵네요.

쉽게 말하면 그 글자를 포함하지 않는 문자열을 찾아낸다고 보시면 될 듯.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '[^0-9]%';

 결과--> 앞의 첫 글자가 숫자가 아닌 것을 찾아냅니다.

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

PKEY   DETAIL

1      나의 살던 고향은 꽃피는 산골

2      나의 살던 [고향은] 꽃피는 산골

3      나의 살던 고향은 꽃피는 시골

4      나의 살_ 고향은 꽃피는 사골

6      ZACEF

9      CA10%

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

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '[^A-Z]%';

 결과--> 시작 문자열 중 A-Z 사이가 아닌것, 즉 영문자로 시작하지 않는 것을 찾아냅니다.

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

PKEY   DETAIL

1      나의 살던 고향은 꽃피는 산골

2      나의 살던 [고향은] 꽃피는 산골

3      나의 살던 고향은 꽃피는 시골

4      나의 살_ 고향은 꽃피는 사골

5      13579

7      1D3FB

8      3^F57

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

아래 내용은 Help에 있는 내용입니다.

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

·         LIKE 'Mc%'는 Mc로 시작하는 모든 문자열을 검색합니다(예: McBadden).

·         LIKE '%inger'는 inger로 끝나는 모든 문자열을 검색합니다(예: Ringer, Stringer).

·         LIKE '%en%'는 문자열의 어느 위치에든 en이 포함된 모든 문자열을 검색합니다(예: Bennet, Green, McBadden).

·         LIKE '_heryl'는 heryl로 끝나고 6자로 된 모든 이름을 검색합니다(예: Cheryl, Sheryl).

·         LIKE '[CK]ars[eo]n'는 Carsen, Karsen, Carson, Karson을 검색합니다.

·         LIKE '[M-Z]inger'는 M에서 Z 사이의 한 문자로 시작하고 inger로 끝나는 모든 이름을 검색합니다(예: Ringer).

·         LIKE 'M[^c]%'는 M으로 시작하고 두 번째 문자가 c가 아닌 모든 이름을 검색합니다(예: MacFeather).

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

알고 나면 참 편리하죠?

참으로 편리해 보이지만, 한 가지 문제가 있습니다.

바로... 와일드카드 문자를 검색할 때입니다.

이는 DB 검색을 할 때 흔히 실수하는 부분입니다.

실례로 제목에 대괄호([~~~~])를 사용하는 경우가 많은데요.

여기 샘플에서는 "2, 나의 살던 [고향은] 꽃피는 산골" 이 그러합니다.

자, 아래와 같이 검색을 해보겠습니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%[%';

 결과--> ????

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

PKEY   DETAIL

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

슬프게도 결과가 나오지 않습니다.

그럼, 와일드카드 문자는 검색을 할 수 없단 말인가요? ㅜ.ㅜ

아닙니다! 기본적으로는 대괄호로 묶어 주면 됩니다.

이제 바꿔보겠습니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%[[]%';

 결과--> 짜잔~

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

PKEY   DETAIL

2      나의 살던 [고향은] 꽃피는 산골

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

나옵니다. ㅋ.

이제, 와일드카드 문자 검색을 정리해 보면 다음과 같습니다.

1. ESCAPE 문자지정

- ESCAPE 문자를 지정해 줍니다. 그 문자는 예약어만 아니면 다 가능합니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%10#%%' ESCAPE '#';

 결과--> 10#%를 10%로 찾아냅니다.

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

PKEY   DETAIL

9      CA10%

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

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%10/%%' ESCAPE '/';

 결과--> 10/%를 10%로 찾아냅니다.

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

PKEY   DETAIL

9      CA10%

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

SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%/^%' ESCAPE '/';

 결과--> /^를 ^로 찾아냅니다.

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

PKEY   DETAIL

8      3^F57

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

2. 대괄호 사용

 - [] 안에 와일드카드를 넣습니다.

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%[%]%';

 결과--> % 가 포함된 문자열을 찾아냅니다.

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

PKEY   DETAIL

9      CA10%

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

 SELECT * FROM LIKE_TEST WHERE DETAIL LIKE '%[[]%';

 결과--> [ 가 포함된 문자열을 찾아냅니다.

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

PKEY   DETAIL

2      나의 살던 [고향은] 꽃피는 산골

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

* 참고로 ^가 포함된 문자는 ESCAPE 로 찾아내야 합니다.

특히, 제목에 대괄호[~~~]를 사용하는 경우가 많다는 것을 잊지 마시고,

PROCEDURE를 만들면서 제목 검색을 할 때에는 반드시 유의해야 할 듯 합니다.^^

이상 [쓸만한게없네]였습니다.

안녕하세요?

지난 번에 이어 마지막으로 세 번째 Function 비교 시간입니다.

MSSQL 2005, Oracle 9i 기준입니다.

20.   지금(Right Now) 가져오기

è  MSSQL 2008에서는 “SYSDATETIME()” 을 통해서 더 상세하게 사용이 가능합니다.(DATETIME2)

21.   일자 더하기 / 빼기

è  위 결과를 보시면 아시겠지만, Oracle에서는 소수점 단위로 일자계산이 가능합니다. MSSQL에서는 일자에 소수점을 사용해도 인식이 불가능합니다.

22.   일자 차이 계산

è  위 결과에도 나타나듯이, MSSQL에서는 일자로 지정하면 딱 떨어지지만, Oracle에서는 특별히 지정하지 않는 한 소수점까지 나타냅니다.

23.   해당 월의 마지막 날 가져오기

è  MSSQL에서는 아래와 같이 처리할 수 있습니다.

24.   Time Zone에 의한 시간 변환

è  AST : 대서양 표준시(캐나다 동부, 푸에르토리고, 버진아일랜드 등, 그리니치 표준시보다 4시간 늦음)
MST : 산악 표준시(로키 산맥에 가까운 미국과 캐나다 일부 지역의 동계 표준시, 그리니치 표준시보다 7시간 늦음)

è  Oracle Zone 표

è  MSSQL에 DATETIMEOFFSET이라는 것이 있지만, 이것은 표준시간대 인식일 뿐 구조는 다릅니다.

25.   해당일자 다음에 오는 해당 요일 반환

è  보시다시피 2010년 11월 7일 이후에 처음 오는 월요일을 반환합니다.
하지만, 주의할 사항은 국가설정에 따라 일자설정이 다릅니다.
미국으로 되어 있으면 SELECT NEXT_DAY('10-Nov-07', 'MONDAY') FROM DUAL; 으로 하셔야 합니다.

26.   지정한 날짜의 특정 부분을 나타내는 문자열을 반환합니다.

è  MSSQL DATENAME의 경우 2005 이전 버전에 맞춰져 있습니다. 그 이상의 버전에서는 용례 아랫부분을 참조하시면 됩니다.
Oracle의 경우엔 TO_CHAR를 만능으로 써서 할 수 있습니다.

다만 실제로 숫자처럼 쓰시려면 TO_NUMBER를 통해서 숫자형으로 변환해 주셔야 합니다.

27.   문자형을 날짜형으로 변환

è  Oracle의 경우엔 Format_Mask를 지정해 주시는 것이 관례입니다.

28.   날짜형을 문자형으로 변환

è  문자형과 날짜형 변환은 위처럼 사용이 가능한데요,

MSSQL에서의 자세한 사항은 강산아님의 아티클

(http://www.sqler.com/?mid=bColumn&page=4&document_srl=265068)

을 참조해 주세요.

일단, Oracle 과 MSSQL 함수 비교는 여기서 마칠까 합니다.

물론, MSSQL 2008과 Oracle 10g/11g 로 올라가면서 추가되거나 변경된 것들도 많은데요, 제가 조금 더 공부해서 알려드리도록 하겠습니다.

이상 [쓸만한게없네]였습니다.

안녕하세요?

지난 번에 이어 두 번째 Function 비교 시간입니다.

또다른 9가지 나갑니다. ^^.

11.   문자열 변환

è  MSSQL의 STUFF라는 함수가 있는데요. 이것은 엑셀의 REPLACE와 같습니다.

12.   음절의 첫 글자만 대문자로 변환(Pascal Case)

14.   나열한 인수 중 가장 큰 값 / 작은 값을 반환

è  비교하는 것은, 문자순위(A보다는 Z가 크다. 한글은 자모순서에 의함), 숫자순위(0보다는 9가 크가), 글자수단위(같은 문자로 시작하더라도 결국 글자수가 많은 것이 크다.)

15.   길이 가져오기, 또는 BYTE 단위 길이 가져오기

è  위에서 보시면 알겠지만, BYTE 단위로 길이를 확인하시려면 LEN 이 아닌 DATALENGTH 를 사용하셔야 합니다.

16.   NULL 일 경우 대체값 표시.

17.   숫자형을 문자형으로 변환

è  MSSQL의 STR는 기본 자릿수가 10자리입니다.

è  Oracle에서는 위에서 보시다시피 숫자형태를 Concat하더라도 자동적으로 문자로 나옵니다.

è  물론 MSSQL에서는 CONVERT나 CAST를 훨씬 더 많이 사용합니다.

18.   문자형을 숫자형으로 변환

19.   조건처리 구문

è  MSSQL CASE문은 여러 개의 조건 중 맞는 결과를 표시하는 것이고, Oracle의 DECODE는 참/거짓에 따라 결과를 표시합니다.

1)     MSSQL CASE SAMPLE

2)     같은 자료를 Oracle DECODE를 사용할 경우

è  Oracle 8.1.7부터는 MSSQL과 거의 동일한 CASE문을 제공합니다.

위 구문을 MSSQL 구문과 동일하게 하셔도 결과는 같습니다.

오늘은 여기까지이고요, 조만간 또 3탄 올리겠습니다.

이상 [쓸만한게없네]였습니다.