분류없음2010.01.28 08:00
MSSQL 2005 이상부터 사용이 가능합니다.

IF OBJECT_ID('TEMPDB..#TMP_BACKUPHISTORY') IS NOT NULL
    DROP TABLE #TMP_BACKUPHISTORY

SELECT *
  INTO #TMP_BACKUPHISTORY
  FROM (
select s.server_name, s.database_name, workname = s.name, s.backup_set_id, s.backup_finish_date,
mdf_file=f.physical_name , backup_file = m.physical_device_name ,f.file_size
from msdb..backupset s, msdb..backupmediafamily m , msdb..backupfile f
where s.media_set_id=m.media_set_id and f.backup_set_id=s.backup_set_id
and s.type='D' and f.file_type='D' and has_dbaccess(s.database_name)=1
and s.database_name not in (N'tempdb', N'model', N'msdb')
              and s.backup_finish_date > DATEADD(dd,-7,CONVERT (char(10), CURRENT_TIMESTAMP, 20))
) A
select * from #TMP_BACKUPHISTORY

DROP TABLE #TMP_BACKUPHISTORY
Posted by Zasfe
컴퓨터2010.01.28 04:00
MSSQL 2005 이상부터 사용이 가능합니다.

SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage
FROM
(
    SELECT sysMas.database_ID, sysMas.size, (sum(size)*8/1024)-2  AS DBSize8KBPage
    FROM sys.master_Files sysMas
WHERE sysMas.file_guid is Not Null and sysMas.type=0
    GROUP BY sysMas.DataBase_ID, sysMas.size
)
temp
INNER JOIN
Sys.DATABASES sysDa
ON temp.Database_ID = sysDa.DataBase_ID
WHERE sysDa.Name Not In (N'tempdb', N'model', N'master', N'msdb', N'ReportServerTempDB',N'ReportServer')
Posted by Zasfe
컴퓨터2009.08.11 18:00



좋은 저장프로시저를 위한 21가지 팁

  1. 키워드
    SQL 명령어는 대문자로 알아보기 쉽게 표시한다.

  2. SQL-92
    항상 ANSI 92 를 사용하도록 노력해야 합니다.

  3. 변수
    가능한 최소한으로 사용하여 캐쉬 공간에 여유를 주어야합니다.

  4. 동적 쿼리
    최소한으로 사용하여야한다. 동적쿼리에 따라 재컴파일이 된다.

  5. 친숙한 전체 이름 사용
    database_name.schema_name.table_name 으로 사용을 해야 하며, CREATE PROCEDURE dbo.Your_Proc_name 처럼 사용해여 합니다.

  6. SET NOCOUNT OFF
    실행 결과 행은 네트워크 트래픽을 사용하게 되므로 주의해여 한다.

  7. sp_ 접두어를 사용하지 않는다
    시스템데이터베이스(MASTER) 와 사용자 데이터베이스에 같은 이름의 저장프로시져가 있게 되면 사용자 데이터베이스는 결코 실행되지 않는다.

  8. sp_executeSQL 과 KEEPFIXED PLAN 옵션
    sp_executeSQL 과 KEEPFIXED PLAN 옵션은 저장프로시저를 재컴파일합니다. 만약 동적 SQL 을 사용하려면 EXEC(proc_name) 대신에 sp_eecutesql 을 사용해야 합니다.

  9. SELECT 와 SET
    SELECT는 서로 다른 여러 변수를 사용할수 있습니다. SET 은 하나의 변수만을 사용합니다.

    SELECT @var1 = @var1 + 1, @var2 = @var2 - 1

    SET @var1 = @var1 + 1
    SET @var2 = @var2 - 1

  10. WHERE 연산자
    =, >, <, >=, <=, <>, !=, !>, ! 의 사용을 줄여야 합니다.
    SQL Server 2008 Books Online (July 2009) Operator Precedence (Transact-SQL)

  11. WHERE 연산자 추가
    쉽게 LOWER 등의 연산자를 이용해서 대소문자를 구별하곤 하는데, OR 명령을 이용하는 편이 보다 빠른 처리를 할수 있습니다.


    SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'


    SELECT emp_name FROM table_name WHERE emp_name = 'EDU' OR emp_name = 'edu'


    또한 EXISTS  대신에 IN 연산자를 사용하면 보다 빠른 처리가 가능합니다


    SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
    SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)

  12. CAST 와 CONVERT
    CONVERT 보다 CAST 를 쓰도록 합니다. CAST 는 ANSI-92 지만 CONVERT 는 MSSQL 에서만 사용이 가능합니다. 다만 DATETIME 데이타타입의 변형에는 CONVERT 를 사용해야 합니다. CAST 는 안되거든요.

  13. DISTINCT 와 ORDER BY
    추가적인 데이터베이스작업이 요구되기 때문에 굳이 필요하지 않다면 사용하지 않는 것이 좋습니다. 성능상 좋지 않습니다.

  14. 커서 사용
    되도록 가상테이블이나 테이블을 이용하여야 합니다.

  15. SELECT 구문
    컬럼을 지정해야 합니다. 그렇지 않으면 모든 컬럼을 가져오게 되어서 레코드셋이 불필요하게 가득차게 됩니다.

  16. 서브쿼리 와 조인
    서비쿼리와 조인은 거의 비슷합니다. 추가설명 필요;;

  17. CREATE TABLE 와 SELECT INTO
    SELECT * INTO 는 작은 테이블을 만들기에는 좋지만 큰 레코드가 있거나, 오래걸리는 쿼리 테이블에는 많은 시간이 걸립니다.

  18. 임시테이블 대신에 사용자 테이블변수를 사용하라
    임시 테이블은 저장프로시저를 재컴파일하는 원인이 됩니다. 하지만 테이블 변수는 실행하는 동안 저장프로시저를 다시 설계합니다.
    추가 설명 필요;;

  19. 인덱스의 사용
    인덱스를 사용하면 보다 빠른 결과를 얻을수 있고, 작은 테이블보다는 큰 테이블에 효과적이다.

  20. 프로파일러를 사용하라

원문 : Twenty tips to write a good stored procedure


Posted by Zasfe
컴퓨터2009.05.09 10:12
 SELECT spid, loginame, cpu, physical_io
    INTO #Temp1 FROM master..sysprocesses
    WAITFOR DELAY '0:00:05'  
    SELECT P.spid, P.cpu-T.cpu AS 'cpu 사용량(ms)',
    P.physical_io-T.physical_io AS 'Disk R/W량(Page)',
    P.loginame AS '사용자명', P.nt_username ' NT 사용자명', P.program_name '응용프로그램',
    P.hostname, P.cmd
    FROM master..sysprocesses P, #Temp1 T
    WHERE P.spid=T.spid
    AND P.cpu-T.cpu+P.physical_io-T.physical_io+P.memusage > 0
    AND P.loginame <> 'sa'
    ORDER BY 'cpu 사용량(ms)' DESC,'Disk R/W량(Page)' DESC
    DROP TABLE #Temp1
Posted by Zasfe
컴퓨터2008.09.04 13:36
; ------------------------------------------------------------------------------
;
; AutoIt Version: 3.1.1++
; Language:       English
; Description:    Functions for ODBC/SQL databases.
;
; ------------------------------------------------------------------------------

; Retrieve registerered DSN resources, default type USER and SYSTEM
Func _SQLGetDSN($s_DSNType=""
)
    Local Const $s_ODBCregUser   =
"HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local Const $s_ODBCregSystem =
"HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local
$s_DSNList
    
    $s_DSNList=
""
    if $s_DSNType <> "SYSTEM" then  $s_DSNList = _SQLGetDSNregister($s_ODBCregUser
)
    if $s_DSNType <> "USER"   then  $s_DSNList = _SQLGetDSNregister($s_ODBCregSystem
)
    return
$s_DSNList
EndFunc

; Retrieve register entries, used by _SQLGetDSN
Func _SQLGetDSNregister($s_RegEntry
)
    Local $s_Entries[10000], $s_Nm,
$i
    $i =
1
    While
1
        $var = RegEnumVal($s_RegEntry, $i
)
        If @error <> 0 then
ExitLoop
        $s_Entries[$i] =  
$var
        $i = $i +
1
    
WEnd
    $s_Entries[0] =
$i
    ReDim $s_Entries[$s_Entries[0
]]
    return
$s_Entries
EndFunc

; Open database connection, on empty DSN a ADO will ask for specification
Func _SQLOpen($s_DSN=""
)
    Local $o_ADOcn
; Create ADO connection
    $o_ADOcn = ObjCreate ("ADODB.Connection")
; Open ADO connection, only prompting for missing params adPromptComplete=2
;$o_ADOcn.Properties ("Prompt") = 4
    $o_ADOcn.ConnectionTimeout =
100
    $o_ADOcn.Open ($s_DSN
)
    if @error then return
0
    return
$o_ADOcn
EndFunc

; Close database connection
Func _SQLClose($o_ADOcn
)
    $o_ADOcn.Close
EndFunc

; Send a schema request, optional pass a filter
Func _SQLschema($o_ADOcn, $i_SchemaID, $as_Filter=0)  
; 1="", $s_filter2="", $s_filter3="", $s_filter4="", $s_filter5="" )
    Local $o_ADOrs
    if not isObj($o_ADOcn)
Then
        SetError(1
)
    
else
        if IsArray($as_Filter) Then
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID, $as_Filter
)
        
else
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID
)
        
EndIf
    
EndIf
    if @error Then return
0
    return
$o_ADOrs
EndFunc

; Send a query, optional pass a max number of records to retrieve
Func _SQLQuery($o_ADOcn, $s_Query, $i_MaxRecords=0
)
    
;ClipPut($s_Query)
    Local
$o_ADOrs
    if not isObj($o_ADOcn)
Then
        SetError(1
)
    
else
        $o_ADOrs = ObjCreate ("ADODB.Recordset"
)
        $o_ADOrs.CursorType = 0                    
; adOpenForwardOnly = 0
        $o_ADOrs.LockType   = 3                    
; adLockOptimistic  = 3
        $o_ADOrs.MaxRecords = $i_MaxRecords        
; maximum records returned by query
        $o_ADOrs.Open($s_Query, $o_ADOcn
)
    
Endif
    if @error Then return
0
    return
$o_ADOrs
EndFunc

; --- retrieve fieldnames from the given qryid
Func _SQLGetFields($o_ADOrs, $s_Seperator="|"
)
    Local $i,
$s_Fields
    $s_Fields=""                               
; Get information about Fields collection
    With
$o_ADOrs
        For $i = 0 To .Fields.Count -
1
            $s_Fields &= .Fields($i).Name &
$s_Seperator
        
Next
    
EndWith
    return
$s_Fields
EndFunc

; --- retrieve fieldvalues and move to next row of given qryid
Func _SQLGetRow($o_ADOrs, $s_Seperator="|"
)
    Local
$s_RowValues
    $s_RowValues =
""
    With
$o_ADOrs
        If Not .EOF
Then
            For $i = 0 To .Fields.Count -
1
                $s_RowValues &= .Fields($i).Value &
$s_Seperator
            
Next
            .
MoveNext
        
Else
            setError(1
)
        
EndIf
    
EndWith
    return
$s_RowValues
EndFunc

; --- retrieve Array fieldvalues and move to next row of given qryid
Func _SQLGetRowArray($o_ADOrs, $s_Seperator="|"
)
    Local
$s_RowValues
    $s_RowValues =
""
    With
$o_ADOrs
        If Not .EOF
Then
            For $i = 0 To .Fields.Count -
1
                $s_RowValues &= .Fields($i).Value &
$s_Seperator
            
Next
            .
MoveNext
        
Else
            setError(1
)
        
EndIf
    
EndWith
    $s_RowValues = StringSplit($s_RowValues,$s_Seperator
)
    return
$s_RowValues
EndFunc

Func
_SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=900
)
    Local $o_ADOCmd,
$o_ADOrs
    $o_ADOCmd = ObjCreate("ADODB.Command"
)
    $o_ADOCmd.ActiveConnection =
$o_ADOcn
    $o_ADOCmd.CommandText =
$s_Query
    $o_ADOCmd.CommandTimeOut =
$cmd_TimeOut
    $o_ADOrs = $o_ADOCmd.
Execute

    if @error Then return
0
    return
$o_ADOrs
EndFunc

Posted by Zasfe