파란하늘의 지식창고
[MSSQL][tip] 암호화
Study/MSSQL 2010. 3. 21. 02:37

MSSQL에서는 아래와 같은 암호화를 지원했었다. PwdEncrypt - 암호화 PwdEncrypt('암호화문자') ex) INSERT INTO members(user_id, password) VALUES('myid', PwdEncrypt('mypwd')) PwdCompare - 비교 PwdCompare('비교대상문자', '암호화 된 문자') ->양쪽이 같으면 1을 리턴 다르면 0 리턴, 둘중하나가 NULL 이면 NULL 을 리턴 ex) SELECT PwdCompare('1234', password) FROM members WHERE user_id = 'myid' 하지만 PwdEncrypt는 오래된 암호화 모듈이라 차후 버전에서는 사용하지 않을 수 있다. (http://msdn.microsoft.com/..

[MSSQL][advanced] 데이터의 물리적 구조
Study/MSSQL 2009. 7. 16. 22:28

데이터 파일 주 데이터 파일[.mdf]과 보조데이터 파일[.ndf]로 구분 기본[디스크가 꽉 찰 때까지 10%씩 자동 증가]로그 파일 가상 로그파일 [.ldf] 최소 단위 256kb로 구성 기본[최대 2TB (2008 기준) 10%씩 자동 증가] page page에 들어가는 항목 8가지 : [msdn 링크] 페이지 유형 내용 데이터 text in row가 ON으로 설정된 경우에 text, ntext, image, nvarchar(max), varchar(max), varbinary(max) 및 xml 데이터를 제외한 모든 데이터가 있는 데이터 행 인덱스 인덱스 항목 텍스트/이미지 큰 개체 데이터 형식: text, ntext, image, nvarchar(max), varchar(max), varbinar..

[MSSQL][basic] Primary Key, Unique Key, Clustered Index, NonClustered Index의 차이
Study/MSSQL 2009. 7. 13. 00:26

테이블을 생성할 때 제목에 언급한 4가지 설정이 헷갈리게 되는 경우가많다. Key 와 Index로 나누어 생각하면 된다. index는 물리적인 관점의 데이터 저장에 대한 설정이고 key 는 논리적인 관점의 데이터 저장에 대한 설정이다. (foreign key도 마찬가지) 데이터가 입력될 때 생성되는 index를 위주로 data를 정렬할 것인가 아니면 그냥 data정렬은 하지 않고 index만 추가할 것인가가 바로 Clustered Index와 NonClustered Index의 차이이다. Primary Key는 논리적으로 데이터를 고유하게 식별할 수 있도록 제약조건을 걸어두는 것이다. Primary Key의 제약조건은 다음과 같다. Not Null 중복된 값은 허용하지 않음 하나의 table에는 한개의 ..

[MSSQL][advanced] index 통계보기 및 업데이트 하기
Study/MSSQL 2009. 5. 25. 10:45

데이터가 있는 컬럼에 대해 인덱스가 만들어질 떄 이 컬럼 값의 히스토그램 및 관련 정보로 구성되는 통계(statistics)가 만들어진다. 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 최적화기가 결정하게 되는데 이때 판단의 근거자료로 활용하는 것이 통계이다. 따라서 통계가 업데이트 되지 않은 시점에서 검색조건을 판단하게 되면 최적화된 검색이 이루어지지 않게 된다. 인덱스에 관한 통계는 DBCC SHOW_STATISTICS 명령을 통해 확인할 수 있다. (msdn 참조) 구문은 다음과 같다. DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) [ WITH [ NO_INFOMSGS ] [ , n ] ] <..

[MSSQL][tip] datetime과 smalldatetime과 unixtime의 변환
Study/MSSQL 2009. 5. 20. 20:16

db에 시간에 대해 저장할 때 datetime과 unixtime 두 가지 방식 중 하나를 이용하여 저장한다. 다음과 같은 query가 datetime 컬럼에 대해 실행되면 어떻게 될까? INSERT 테이블 (날짜컬럼) values(0) 위의 경우 datetime 형식 컬럼에는 '1900-01-01 00:00:00.000'이 저장된다. 만약 int값을 0이 아닌 값으로 실행하면 어떻게 될까? INSERT 테이블 (날짜컬럼) values(1) 위의 경우 datatime 형식 컬럼에는 '1900-01-02 00:00:00.000'이 저장된다. 즉 숫자형의 값을 datetime 컬럼에 입력하면 '1900-01-01 00:00:00.000'을 기준으로 day가 증가 또는 감소한 값이 저장된다. unixtime은 ..

[MSSQL][advanced] index 단편화 현상
Study/MSSQL 2009. 5. 19. 13:45

쿼리 튜닝을 아무리 잘 하였다고 하더라도 데이터의 양에 따라 성능이 좌우될 수 있다. 많은 양의 insert, delete 및 update가 이루어지는 테이블의 경우 쉽사리 clustered index를 걸기 힘들다. 이는 인덱스의 재 갱신에 대한 부담이 크기 때문이며, 이런 경우 clustered index는 단순히 identity 컬럼으로 지정하고, 기타 컬럼을 index 지정하여 호출하게 된다. 하지만 이렇게 지정한 index도 문제가 발생할 소지가 있다. 많은 양의 delete와 insert, update가 이루어지면 점차적으로 index가 조각나버리게 된다. 이러한 인덱스 단편화 현상에 대한 체크가 가능하다. dbcc 명령어중 show contig라는 명령어이다. (msdn 참조) 문법은 다음과..

[MSSQL][tip] varchar 타입 컬럼 검색 SP 작성 시 주의사항
Study/MSSQL 2009. 5. 8. 15:10

어떤 테이블의 varchar 타입 컬럼을 검색하는 SP를 작성할 경우 아래와 같이 작성하곤 한다. CREATE PROCEDURE [dbo].[searchA] @searchColumnA NVARCHAR(12) AS SET NOCOUNT ON SELECT * FROM A WITH (READUNCOMMITTED) WHERE columnA = @searchColumnA 이 쿼리를 직접 테스트 할 때 이상없더라도 SP로 호출하면 급격히 느려질 수 있다. 이유는 varchar로 된 고정 길이의 columnA을 검색하기 위해 입력한 @searchColumnA에 대해 매번 테이블을 조회하는 경우 CONVERT 과정이 더해지기 때문이다. 이는 데이터 형식의 선행 규칙에 의해 이루어지며 자세한 내용은 아래 링크를 통해 알..

[MSSQL][advanced] 성능 향상을 위한 query 작성과 tuning
Study/MSSQL 2009. 4. 7. 18:38

쿼리 구문 작성 필요한 column만 명시한다. SELECT * 을 사용하는 것은 피한다. 사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다. 특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다. data type의 byte가 적은 컬럼을 주로 사용하는 것이 좋다. COUNT(*)을 사용하라. COUNT(특정column) 으로 호출하는 경우가 있다. 이 경우 해당 컬럼의 NULL값을 제외한 COUNT를 가져오게 된다. NULL값을 일일이 체크하면 호출 속도가 저하되게 된다. NULL을 체크해야 하는 경우가 아닌 대부분의 경우 COUNT(*)을 체크한다. COUNT(*)는 NULL값의 경우도 모두 count에 추가하지만 그로 인해 성능의 저하가 많이 줄어든다. List 호출..