------Study------/SQL2011. 4. 21. 17:13
테이블 :   Atable
중복 체크 할 컬럼 : A


ex)
select A from
Atable 
group by A
having count(*) > 1
Posted by 혀비

댓글을 달아 주세요

------Study------/SQL2011. 1. 13. 09:58
dbcc opentran
열려있는 활성화 트렌젝션 검색 

해당 키값을 확인 뒤 아래 sp_lock 뒤에 키값을 입력하면 해당 키를 확인 할 수 있음 
뒤에 값 없이 실행 하면 전체 리스트를 화면에 보여 줌

exec sp_LOCK XX

해당 키값을 확인한 뒤 kill XX 를 실행하면 해당 키값의 트렌젝션을 죽임




Posted by 혀비

댓글을 달아 주세요

------Study------/SQL2011. 1. 10. 16:31

SELECT O.name
FROM   sysobjects O JOIN syscomments C
ON     O.id = C.id
WHERE  O.type = 'P' AND C.text LIKE '% 검색할 단어 %'
Posted by 혀비

댓글을 달아 주세요

------Study------/SQL2009. 11. 30. 10:59

nolock where  [CreateDate] BETWEEN DATEADD(HH, -9, GETDATE()) AND GETDATE()

HH는 시간, DD는 날짜, mm은 분  .....
Posted by 혀비

댓글을 달아 주세요

------Study------/SQL2008. 8. 27. 11:15

**변수사용

로컬변수의 특징


1. @
라는 첨자를 가진다
2.
선언된 로컬 변수는 현제 커넥션에서 같은 배치내에서만 사용
3.
값을 초기화하기 위해 set, select문 사용

 

DECLARE @YMD DATETIME //선언
SET @YMD=GETDATE()  //
초기화
SELECT @YMD


DECLARE @SAL INT
SET @SAL=5000
SELECT ENAME, SAL FROM EMP
WHERE SAL>@SAL

 

DECLARE @SAL INT
SELECT @SAL=AVG(SAL) FROM EMP
SELECT @SAL

 

**조건문

 

<IF>

IF 조건 
      
실행문
ELSE
      
실행문

 


DECLARE @I INT
SET @I=200
IF @I <100
 PRINT '@I
100보다 작습니다.'
ELSE 
 PRINT '@I
100이상입니다.'

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

DECLARE @SAL INT
SET @SAL=2000
IF @SAL >=3000
         BEGIN    //
실행문이 2개 이상일때는 BEGIN END로 묶어주어야함..


         PRINT '3000
이상인 사원몰록'
         SELECT * FROM EMP
         WHERE SAL>=3000
       

          END
ELSE 
        PRINT '3000
미만인 사원목록'
        SELECT * FROM EMP
        WHERE SAL<3000

 

 


<CASE
(END로 끝남. JOIN한 것과 같은 결과)>

 

SELECT EMPNO, ENAME,DEPTNO,
            (CASE 
  WHEN DEPTNO=10 THEN '
전산실'
  WHEN DEPTNO=10 THEN '
총무부'
  WHEN DEPTNO=10 THEN '
영업부'
 ELSE
   '
기타'
 END) AS
부서명
FROM EMP

 

**반복문
DECLARE @A INT
SET @A=1

WHILE @A <100    // BEGIN .. END를 쓰지 않으면 무한루프에 빠짐~!!
BEGIN
SELECT @A
SET @A=@A+1
END


**저장 프로시져(STRORED PROCEDURE)

--서버에 컴파일 시켜 저장시켜놓은 SQL코드의 집합

 

시스템 저장프로시져(SP_, MASTER DB)
확장 저장 프로시져(XP_, MASTER DB)
사용자 저장 프로시져

 

--장점: VIEW의 장점 +a

 

 -처리속도가 빠르다
 -
네트워크 트래픽을 줄인다
 -
확장이 용이하다.

[출처] <MS-SQL> 변수사용, 조건문, 반복문, 저장프로시져|작성자 아이쿠야

Posted by 혀비

댓글을 달아 주세요

------Study------/SQL2008. 8. 22. 15:12
-- 1.저장프로시져별 실행수 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = 'proc'
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc

--2. CPU소모량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype='proc'
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

--3. IO량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc


--4. 처리시간이 긴 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc




참고 : http://www.databasejournal.com/features/mssql/article.php/3687186
Posted by 혀비

댓글을 달아 주세요