iotworks.co.kr

MS-SQL Server 날짜에 대한 함수

시스템의 현재의 시간과 날짜를 가져온다.

Select Getdate()

Go

Style 유형

위의 style형태를 이용하여,

Ms(밀리 세컨드)까지 데이터를 가져오고 싶다면 다음과 같이 해야한다.

Select Convert(Varchar(30), Getdate(), 9)

go

또는 우리가 친숙한 yy.mm.dd 형식으로 표현하고 싶다면 그에 해당하는 유형 변호만 주면 된다.

Select Convert(Varchar(30), Getdate(), 2)

Go

Select Convert(Varchar(30), Getdate(), 102)

Go

첫번째 유형과 두번째 유형과의 차이는 직접해보면 아시겠지만, 년도 펴시에서 세기가 붙는지 않붙는지가 틀리다.

DatePart 내용

위의 표를 참고하여, 원하는 ‘월’만 가져오려면 다음과 같이 하면 된다.

Select Datepart(mm, Getdate())

Go

MS-SQL Server Version이 7.0 이후부터는 위의 방법 말고도 더 쉬운 방법이 생겼다.

Select Month(Getdate())

Go

오늘부터 20개월 후는 언제인지 알려면 다음과 같이 하면 된다.

Select Dateadd(mm, 20, Getdate())

Go

오늘부터 100일 후에는 언제일까 ?

Select  Dateadd(dd, 100, Getdate())

Go

역시 7.0 이후에서는 이 방법 말고도 더 쉬운 방법이 생겼다.

Select Getdate() + 100

Go

그럼 3000년까지는 몇 일이나 남았을까 ?

Select Datediff(dd, Getdate(), ‘3000.1.1’)

go

다음 예제도 한번 보자. 한글 요일명이 출력된다.

Select DateName(dw, Getdate()), Datename(nm, Getdate())

Go

이상으로 쿼리문에서 자주 사용되는 날짜 관련 함수에 대해서 알아봤다.

1. 가능한 윈도우 인증을 사용한다.
Windows Authentication if you Can

2. sa계정 사용을 주의한다.
Watch your SA Account Usage

3. BUILTIN/Administrators 그룹을 제거한다.
Remove BUILTIN/Administrators

4. 서비스 시작 계정을 변경한다.
Change the Startup Account

5. 실패한 로그인과 거부된 로그인을 감사한다.
Auditing of Failed Logins and Denied Access

6. 최신 서비스팩과 핫픽스를 적용하고 유지한다.
Stay Up on Service Packs and Hot Fixes

7. 확장 저장 프로시져를 보호한다.
Protect Your Extended Stored Procedures

8. SQL서버 포트를 변경하고 보호한다.
Change the SQL Server Port and Block it

9. 저장 프로시져를 통한 접근을 제어한다.
Control Access Through Stored Procedures

10. OS를 보호한다.
Protect Your OS

좋은 테이블 설계의 4가지 규칙

1. 테이블의 각 필드는 고유한 종류의 정보를 나타내야 한다.

2. 각 테이블은 테이블에서 하나나 여러개의 필드로 구성된 고유 식별자나 기본키를 갖고 있어야 한다.

3. 각 고유한 기본키 값에 대해 데이터 열의 값은 테이블의 주제와 관련이 있어야 하고 그 주제를 완전히 설명할 수 있어야 한다.

4. 어떤 필드(기본키로 지정한 필드)의 데이터를 바꿀 때 다른 필드의 데이터에 영향을 끼치지 않고 바꿀 수 있어야 한다.

1. 로그 파일들의 크기를 확인한다.
DBCC SQLPERF(LOGSPACE)

2. 로그파일을 지운다.
BACKUP LOG 디비명 WITH NO_LOG

3. 로그파일 크기를 설정한다. M단위로.. (아래꺼는 10메가)
USE weddingra DBCC SHRINKFILE(디비명_LOG, 10)

4. 로그 파일들의 크기를 다시 확인한다.
DBCC SQLPERF(LOGSPACE)

5. 조금씩 반복적으로...

http://www.devbank.co.kr/skyoh_board/board.php?start=0&idx=13&mode=view&num=66&t_search=&code=tip_db

======================================================================================

SQL 쿼리 분석기에서 아래의 명령어를 실행 시킨다.

--데이터베이스의 로그 비우기
backup log 디비명 With TRUNCATE_ONLY

--DB 정보보기
sp_helpdb 디비명

--요기의 논리적인 로그 화일을 아래에서 사용합니다.

--DB의 로그데이터베이스 크기를 5M로 줄인다.
DBCC SHRINKFILE (로그화일이름, 5)
ex) ==> DBCC SHRINKFILE(ITZONE_LOG, 5)


또는


로그 파일을 줄이는 방법입니다.

1. backup log db_name with no_log 로 로그내용을 지워줍니다.

2. 엔터프라이즈 관리자 - 보기 - 작업창으로 해당 데이터베이스의 사용중인 공간을 확인합니다.

3. 아래 온라인 설명서의 dbcc shrinkfile 명령을 이용하여 ldf 파일의 크기를 줄이시면 됩니다.

다음은 UserDB 사용자 데이터베이스에서 DataFil1이라는 파일의 크기를 7MB로
줄이는 예제 입니다.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO


또는


우선 만약을 위해 DB를 풀백업 받으세요.

그후.

EXEC sp_detach_db 'pubs', 'true'

식으로 DB를 detach 하세요.

그후

잠시 ldf화일을 rename 시키시고

mdf 화일만 가지고 첨부 시키시는 겁니다.

EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

식으로요.
http://misokorea.co.kr/miso/board4/view.php?mode=view&sidit=D101&sn=176

EXEC  sp_addlinkedserver 
@server        = N'서버명',
@srvproduct = N'',
@provider     = N'SQLOLEDB',
@provstr       = N'SERVER=아이피;Integrated Security=True'

EXEC sp_addlinkedsrvlogin '서버명', 'false', NULL, '아이디', '비번'

개발자를 위한 튜닝 가이드 - 쿼리 디자인

쿼리 디자인

스티브 맥코넬이 이런 말을 했습니다.
뛰어난 디자이너는 습득한 지식을 사용하지 않는 것과 그 지식을 처음부터 확보하지 못한 것을 동일하게 봅니다.
이말 뜻을 다음과 같이 해석하고 싶습니다. 여러분은 쿼리 분석기의 기능들이 어떤 것이 있고, 단축키가 메뉴우측에 작게 표시되어 있다는 것을 대부분 알고 있습니다. 하지만 잘 사용하지는 않고 있을 것입니다. 라고 말입니다. 그래서 먼저 단축키와 그 사용법에 대해 안내하는 시간을 우선 가지도록 하겠습니다.
다음을 실습해보고 자세한 것은 표를 참조합시다

-- CTRL + E , F5

-- 실행하기
----------------------------------------------------------------
use pubs
go

select * from titles

-- CTRL + T => 결과 Text로 보기

select * from titles

-- CTRL + D => 결과 Text로 보기

select * from titles

-- CTRL + K => 실행계획 보기

select * from titles

-- F8 => 개체브라우저 보이기/감추기

-- CTRL + R => 결과창 보이기/감추기

-- 그외 CTRL + C , CTRL + V , CTRL + X

-- CTRL + SHIFT + C => 주석달기

select * from titles

다음의 표를 참조합시다.

주요 단축키 사용 안내 입니다.

수칙1.SELECT는 필요한 결과값만을 요구하는가?

select title , price from titles where title_id = 'BU1032'

Select 하는 내용도 필요한 항목만을 가지고 오도록 되어 있어서 리소스가 전혀 낭비되지 않고 있습니다.

select title , price from titles

필요한 칼럼을 가져오기는 하지만 불필요한 전체 행(Row)들을 가져오고 있습니다.

select * from titles

불필요한 칼럼정보, 행(Row) 데이터를 가져오고 있습니다.

따라 하기 - 3개의 쿼리를 한번에 실행하기

1.3개의 쿼리를 한 Session에서 실행하여 결과3개를 동시에 살펴봅니다.

2.결과 값으로 출력되는 데이터량의 차이를 확인합니다. 어느 쿼리가 가장 간결한 결과를 반환합니까?
( 반드시 꼭 필요한 결과만 반환하게 하는 것이 좋습니다.
select title , price from titles where title_id = 'BU1032' 가 적절합니다.)

수칙2.적절한 WHERE 조건을 사용하는가?

인덱스란 데이터를 빨리 찾기 위해서 사용됩니다. 인덱스가 없다면 특정데이터를 찾기 위해서 모든 데이터페이지를 검색(Table Scan)해야만 합니다 그에 비해 인덱스가 존재하고 그 인덱스가 사용되는 것이 효과적이라면 SQL서버는 해당 인덱스 페이지를 사용하여 쉽게 데이터를 가져올 수 있는데 이를 인덱스 검색(Index Seek)이라 합니다.

SARG(Search Argument)란 쿼리가 반환하는 결과를 제한하기 위하여 옵티마이저가 인덱스와 결합해서 사용할 수 있는 쿼리 내의 조건절을 말하는데 다음의 형태를 가집니다.

컬럼 연산자/변수

옵티마이저가 쓸모 있게 변환하는 것은 CTRL+K 실행 계획 상부 표시에서 관찰할 수 있습니다.

set showplan_all on

select * from authors
where au_lname like 'Ma%'

-- OBJECT:([pubs].[dbo].[authors].[aunmind]), SEEK:([authors].[au_lname] >= 'Ma' AND
[authors].[au_lname] < 'MB'), WHERE:(like([authors].[au_lname], 'Ma%', NULL))
ORDERED FORWARD

-- set showplan_all off

따라 하기

1.인덱스 찾기(Index Seek)를 확인합니다.

2.다음과 같이 약간의 조건절(where) 변형만으로 인덱스페이지가 사용되지 않음을 확인합니다.

3.그렇다면 조건절(where) 변형하고 싶을 땐 어떻게 해야 할까요?

4.다음 예제도 복습해 봅시다.

5.항상 실행 계획을 참조하여 재차 쿼리를 확인해야 합니다.

[참고] 쿼리 계획 은 다음의 몇 가지 단계로 이뤄집니다.

1. 평범한 계획을 식별
2. 획을 단순화 - having를 where로 != @param을 < @param OR > @param으로 변환하는 것 같은 작업을 수행합니다
3. 로드 한다 - 쿼리 옵티마이저가 인덱스와 컬럼 통계, 다른 지원정보를 로드한다
4. 근거하여 계획들을 평가한다 - 실행하는 비용이 충분히 저렴하다고 생각될 때 그 계획을 실행하도록 내놓는다
5. 병렬화를 위해 최적화한다 - SMP

수칙3.COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?

COUNT(*) 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT(*)는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다.

[참고] NULL을 처리하는 방법

use pubs
go

-- 돈 받고 파는 책을 출력하세요

select * from titles where price is not null

-- 비매품인 책을 출력하세요

select * from titles where price is null

-- 비매품책을 제외한 모든 책의 평균가격?

select avg(price) from titles

-- 비매품책을 0원으로 두고 계산한 평균가격?

select avg(isnull(price,0)) from titles

[유용한 관용구]

칼럼의 중복 행의 수를 찾아봅시다

use pubs
go

-- 중복 칼럼이 각각 몇 개 항목인지를 찾아보자
-- type별로 몇 개의 책이 있을까?

select type,count(*) as [중복 행의 수]
from titles group by type having count(*)> 1

수칙4.커서 및 임시 테이블의 내용을 최대한 자제하는가?

결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 단 SQL2000에서만 테이블 변수가 가능합니다.

따라 하기 - 다음은 테이블 변수를 사용하여 기존 커서를 대체하는 것을 구현했습니다.

1.훌륭하게 커서를 대신하는 문장입니다.

CTRL + K로 확인하면 테이블 변수로 사용할 경우 실제 테이블에 잠금을 전혀 걸지않는 것을 알 수 있습니다(중요) 그와 반대로 커서를 사용할 경우 프로시저 시작부터 끝까지 지속적으로 사용 부분을 계속해서 잠그고 있어서 다른 작업들이 대기해야 되는 문제가 생깁니다.

수칙5.VIEW의 총사용을 줄였는가?

VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다,
그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다.

select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다.

수칙6.저장 프로시저를 사용하는가?

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

저장 프로시저의 역할 7가지

1. 데이터 무결성의 시행
2. 복잡한 비즈니스 규칙과 제약의 강화
3. 캡슐형 설계
4. 유지보수
5. 네트워크 트래픽 감소(오고 가는 긴SQL 구문을 축소)
6. 보다 빠른 실행(컴파일을 하지 않습니다)
7. 보안강화

저장 프로시저의 생성과 반복사용 시 발생하는 일

쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.

수칙7.저장 프로시저를 적절하게 리컴 파일 하는가?

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다.

저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.

CREATE PROCEDURE [WITH RECOMPILE]
EXECUTE [WITH RECOMPILE]
sp_recompile

CREATE PROCEDURE [WITH RECOMPILE]

는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).

EXECUTE [WITH RECOMPILE]

는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.

sp_recompile

는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다.

[문서화되지 않은 DBCC 명령어]

-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자

select db_id('pubs')

dbcc flushprocindb(5)

-- 모든 인덱스를 재구축한다
-- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다

dbcc dbreindexall('pubs')

수칙8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다.

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다.

따라하기
일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다.

1. 사용자 정의 저장 프로시저는 master데이터베이스에 존재하더라도 master내용만 참고합니다.

2.그러나 다음과 같이 시스템 저장 프로시저화 한다면

3.부연하자면 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한후 sp_MS_marksystemobject로 시스템 프로시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다.

수칙9. 모든 개체의 소유자는 DBO 이다

소유자가 다르면 복잡한 소유권 체인문제가 발생합니다.

lucia가 테이블의 소유자 입니다. lucia는 뷰를 만들었는데 maria에게 뷰를 볼 수 있게 했습니다. maria는 이를 Pierre가 볼 수 있게 했는데 Pierre는 Maria가 만든 뷰를 select권한을 받았음에도 불구하고 실행이 안됩니다. 이는 소유권 체인이 중간에 분실 됐기 때문입니다. 불필요한 이런 시스템은 시스템의 성능저하를 가져다 줍니다. 모든 소유자는 dbo로 통일하는 것을 권장합니다.

따라 하기 - 소유자를 dbo로 바꿔보자

1.소유자를 dbo로 바꿀 때는 다음의 저장 프로시저를 사용하면 됩니다.

2.추가로 시스템테이블을 업데이트하는 방법을 통해 데이터베이스 차원에서 소유자를 바꾸는 방법도 있으며 커서를 사용하는 방법도 존재합니다.

[참고] 소유자가 dbo가 아닌 객체를 출력해봅시다.

select name from sysobjects
where uid <> user_id('dbo')

수칙10. 데드락이 발생하는 부분을 라이브락 형태로 변경했는가?

데드락이란 라이브락과 반대되는 개념입니다. 둘 이상의 트랜잭션이 서로가 실행해야 될 내용을 이미 잠그고 있어 마치 교차로에서 서로 엉켜 꼼짝할 수 없는 상황을 의미 합니다. 이를 해결하기 위한 SQL서버의 노력은 한쪽을 일방적으로 취소 시키는 것인데 이는 시스템의 성능저하로 나타납니다. 이를 해결하기 위한 가장 좋은 방법은 일방통행 방식으로 변경하는 것입니다. 이것이 라이브락 입니다.

따라 하기

1.우선 준비를 위해 테이블을 만들고 데이터를 넣습니다.

2.창을 두 개 열어서 동시에 실행합니다. CTRL+TAB으로 잽싸게 창을 바꿔서 실행해 봅니다.

3.위의 데드락의 가장 바른 해결방법은 순차적인 라이브락 형태로 변경하는 것입니다.

수칙11. SET NOCOUNT ON을 사용하는가?

불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히'몇 개 행이 적용 되었습니다' 같은 메시지가 그런 대표적인 예입니다.

따라 하기

1. set nocount on을 실행하고 쿼리를 실행합니다.

프로시저를 작성할 때도 set nocount on과 같은 환경설정은 먼저 실행해 두고 프로시저를 작성하면 환경이 저장된 채로 프로시저가 제작되므로 편리합니다.

수칙12.실무 사례: 저장 프로시저 관리 방법

저장 프로시저 관리방법

---------------------------
-- 객체이름 :
-- 파라미터 :
-- 제작자 :
-- 이메일 :
-- 버젼 :
-- 제작일 :
-- 변경일 :
-- 그외 :
---------------------------

use 데이터베이스명
-- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다.
go

-- 소스

create proc dbo.저장 프로시저명
-- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소
as
begin
-- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐)

end

-- 실행예제

exec 데이터베이스명.dbo.저장 프로시저명
-- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다.

출처명 : 한국마이크로소프트

제공 : DB포탈사이트 DBguide.net

 

 

원문 : http://www.dbguide.net/know/know102001.jsp?mode=view&pg=1&idx=2632

[MS SQL 2005]

출처 : Julian Kuiters

참고 : MSDN::fn_listextendedproperty (Transact-SQL)

         MSDN::Transact-SQL Forum       .Net Heaven

SELECT OBJECT_NAME(EXP.major_id) AS TableName,
       C.name                    AS ColumnName,
       EXP.name                  AS PropertyName,
       EXP.value                 AS PropertyValue
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON    C.object_id = EXP.major_id
AND   C.column_id = EXP.minor_id
WHERE EXP.class_desc = 'OBJECT_OR_COLUMN';

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'MUXX1', default, default);

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'MUXX2', 'column', default);

[Oracle]

출처: Oracle SQL

SELECT table_name, comments FROM user_tab_comments;
SELECT table_name, column_name, comments FROM user_col_comments;