본문 바로가기

MSSQL13

대소문자 구별하여 조회하기 테이블에 데이터를 대소문자를 구분하여 조회해야 하는 경우 만약, DB 나 테이블이 Collation(데이터 정렬)이 Case Insensitive 로 하여 생성되었다면 단순 조회로는 대소문자 구분하여 조회가 되질 않습니다. (테이블의 경우 기본적으로 DB 의 Collation 을 따라갑니다.) 이럴 경우 DB 또는 테이블의 Collation 을 변경하거나 재생성하지 않고 쿼리에 아래와 같이 WHERE 정의 컬럼명 뒤에 COLLATE 옵션을 줘서 조회할 수 있습니다. 여기서는 Korean_WanSung_CS_AS 를 사용하였으며 테이블의 Collation 은 Korean_WanSung_CI_AS 입니다. 위 Collation 명을 보시면 뒤에 CI/CS 와 AS 가 있는데 이에 대한 설명은 아래와 같습니다.. 2013. 11. 5.
Login 계정과 DB 사용자 계정 강제 연결하기 Login 계정과 DB 사용자 계정이 연결이 되어 있지 않는 경우가 간혹 발생합니다. 주로 DB 복원 시에 자주 발생하는 문제인 것 같습니다. DB 복원 시 해당 DB 의 사용자 정보도 같이 복원이 되는데 간혹, Login 계정과 연결이 되어 있지 않는 경우가 발생합니다. 이럴 경우, 아래와 같이 강제로 연결을 시켜주는 SP를 실행해주면 간단하게 해결이 됩니다. 2013. 3. 27.
yyyymmddhhmmss 형식으로 된 시간 문자열을 datetime 형식으로 변환하는 방법 간혹 보면 날짜 및 시간을 저장할 때, yyyymmddhhmmss 형식으로 저장하거나, yyyymmdd 와 hhmmss 로 나눠서 두 컬럼에 저장하는 경우가 있었습니다. 이 저장된 값들을 불러서 datetime 형식으로 변환하여야 할 경우에 약간의 가공작업이 필요한데 방법은 아래와 같습니다. stuff() 를 사용하여 중간 중간에 공백과 : 를 넣어 yyyymmdd hh:mm:ss 로 만들어 datetime 형식으로 변환 가능하도록 하는 방법입니다. SELECT CAST(STUFF(STUFF(STUFF('yyyymmddhhmmss', 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME) yyyymmddhhmmss : 날짜 문자열. 예제 SELECT CAST(STUFF(.. 2013. 3. 27.
[2000/2005] MD5 암호화 사용하기 MSSQL 2005 에서는 MD5 를 사용한 암호화를 지원하지만 MSSQL 2000 은 이를 지원하지 않습니다. 그래도 방법은 있더군요. MD5 암호화를 지원하는 DLL 을 등록하여 사용하는 방법입니다. 아래는 MSSQL 2000 과 2005 에서 MD5 암호화 사용방법입니다. [MSSQL 2000] 등록방법은 아래와 같습니다. 1. MSSQL 2000 이 깔린 서버의 C:\Program Files\Microsoft SQL Server\MSSQL\Binn 과 같은 MSSQL 실행파일 디렉토리에 해당 DLL 파일을 복사합니다. 2. SQL 에서 아래 Query 를 실행합니다. USE master; EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll' 3. 이를 그대로 사용할 .. 2013. 3. 27.
DB 개체들의 의존성 확인하기 sp_depends [Object Name] 해당 Object 가 참조하는 혹은 참조당하고 있는 다른 Object 들을 보여줍니다. sp_helpconstraint [Object Name] 제약 조건을 보여주며 여기서 테이블의 참조키나 트리거 같은 의존성을 확인할 수 있습니다. 2013. 3. 26.
서버에서 제공하는 Password 암호화 함수 MSSQL 2000 버전부터 서버 자체적으로 PwdEncrypt() 라는 Password 암호화 함수와 암호화된 Password를 판별할 수 있게 해주는 PwdCompare() 함수를 제공하고 있습니다. 이 두 함수들은 문서화되지 않은 함수이기 때문에 어떻게 사용해야 하는지에 대한 설명이 없습니다. 그렇지만 원래 MSSQL 에서 사용자의 패스워드를 저장할 때 사용하던 함수라고 하기 때문에 master.sysusers password 필드 데이터 타입과 같은 varbinary(256)으로 하면 될 것 같습니다. 일단, PwdEncrypt() 함수의 사용 예제를 보면, 위와 같이 1234를 함수를 이용해 암호화하면 16진수값이 나오게되는데요. 위의 문장을 여러번 실행하면 그때마다 나오는 결과값이 달라지는걸로.. 2009. 1. 16.
Object 존재 여부 판단하기 OBJECT_ID() 라는 시스템 함수를 사용하면 object 의 존재 여부를 판단할 수 있습니다. 파라미터로는 검사할 object 의 이름을 String 으로 건네주면 됩니다. 리턴값은 object 가 존재하지 않으면 NULL 이고 존재하면 해당 object 의 ID 를 반환합니다. 예를 들어, 임시 테이블이 존재하면 지우고 다시 만들 때 IF OBJECT_ID('#tmpTest') IS NOT NULL DROP TABLE #tmpTest 이런 식으로 쓸수가 있습니다. 2009. 1. 16.
테이블 변수의 장점과 사용하기 mssql 2000 부터 table 이라는 변수가 생겼는데요. 이게 바로 테이블 변수입니다. 테이블 변수가 뭐냐? 즉.. 테이블을 변수처럼 사용하는 것이겠죠 ㅡㅡ;; 결과 집합을 이 테이블 변수에 저장할 수 있습니다. 널리 사용되고 있는 임시테이블과 비슷하지만, 임시 테이블에 비해 몇가지 장점이 있고 또 이때문에 여러 서적이나 관련 사이트에서는 테이블 변수의 사용이 권장되고 있다고 합니다. 아래는 SQL Server BOL(Books On-Line) 에 적힌 테이블 변수의 장점입니다. 가능할 때마다 테이블 변수를 임시 테이블 대신 사용하십시오. table 변수는 다음과 같은 편의를 제공합니다. * table 변수는 지역 변수처럼 작동합니다. table 변수는 함수, 저장 프로시저 또는 일괄 처리 등 제대.. 2009. 1. 16.
디비 서버 메모리 초기화하기 dbcc dropcleanbuffers 서버를 종료했다가 다시 시작하지 않아도 버퍼캐시의 메모리를 지워줍니다. (Data cache 로 사용된 메모리) dbcc freeproccache 프로시저 캐쉬에서 모든 메모리를 비웁니다. 위의 두 명령을 사용하면 서버를 재부팅하지 않고도 처음 상태로 돌릴수가 있습니다. 2009. 1. 16.
테이블 용량 체크하기 sp_spaceused [테이블명] 이렇게 하면 해당 테이블의 row 갯수와 사이즈 정보를 알 수 있습니다. 2009. 1. 16.
SET XACT_ABORT 에 관해.. SET XACT_ABORT 옵션은 Transact-SQL 에서 사용이 되며 사용법은 SET XACT_ABORT ON|OFF 로 사용됩니다. SET XACT_ABORT ON 으로 했다는 것은 이 다음부터 사용되는 쿼리문을 실행할때 런타임 오류(컴파일 오류는 해당되지 않음)가 발생하였을 경우 전체를 롤백시킨다는 뜻입니다. SET XACT_ABORT OFF 로 했다는 것은 그 반대인 전체 롤백안하고 오류가 발생한 부분만 롤백한다는 뜻입니다. 예를 들면, 10 개의 데이터를 insert 하는 query 가 있다고 할 때, insert 하기 전에 SET XACT_ABORT ON 을 하게 될 경우, 4개까지 insert 하다가 5번째에서 도메인 오류, 제약 조건 오류(참조키,주키기타 등등..) 등.. 런타임 오류가.. 2009. 1. 16.
서버 이름과 인스턴스 이름이 다른 문제 간혹 실제 서버 네임과 인스턴스 네임이 다른 경우가 있습니다. 이럴 경우 replication 을 위해 해당 서버가 subscriber로 접속해야 할 경우 replication 은 실제 서버 네임을 이용하여 접속 하도록 되어있기 때문에 연결에 문제가 발생합니다. 첨에 서버를 세팅하고 sql 을 올린 후 이 sql 서버의 이름을 바꿔서 쓸 경우 이런 문제가 발생할 수 있으며 이유는 master.sysservers 시스템 테이블에 잘못된 인스턴스 네임이 들어가 있어서 그렇습니다. 이 네임을 실제 서버 네임과 동일하게 수정해줘야 합니다. sql 서버는 server id 가 0인 자기 자신의 인스턴스를 가지고 있습니다. 그러므로 아래와 같은 sp를 사용하여 서버를 재등록시켜주면 됩니다. sp_dropserver.. 2009. 1. 16.
replication 때문에 로그가 지워지지 않는 경우 간혹.. Replication 을 돌릴 때 log 가 지워지지 않아서 서버의 용량이 꽉 차는 문제가 생깁니다. 큰일이죠. 해당 서버에서 다른 작업들을 수행할 수 없게 되니 말입니다. 이 문제는 만들어진 snapshot 데이터들이 모든 subscriber 에게 읽혀지지 않아서 발생하는 문제입니다. 최소 하나의 subscriber라도 동기화 되기 전까지는 log 를 지우지 않고 계속 기다리는 것이죠. 그렇지 않으면 데이터의 일관성이 깨져 버리니까요. backup log 를 with no_log 나 truncate 같은 것을 사용하여 줄이려해도 줄여지지 않는 경우 아래와 같은 쿼리를 돌려 해당 서버의 데이터베이스들을 확인해보세요. select name, is_published, is_subscribed, is.. 2009. 1. 16.

티스토리툴바