SQL SERVER TIP

############ SQL2005버전확인 ################################
SELECT
 @@VERSION AS [@@VERSION]
,  SERVERPROPERTY('ProductVersion') AS ProductVersion
,  SERVERPROPERTY('ProductLevel') AS ProductLevel
GO

############ 데이타베이스특성보기 ################################
select DATABASEPROPERTYEX('ting' , 'IsQuotedIdentifiersEnabled' )
exec sp_MSforeachtable "exec sp_spaceused '?'" -->각 테이블별 용량

############ 모니터링할 것들 ################################
DISK I/O
Processor
Memory
User connections
Network

############ 백업히스토리삭제 #############################
Declare  @PurgeDate datetime
set @PurgeDate = DATEADD(d, -1, CURRENT_TIMESTAMP)
 EXEC msdb..sp_delete_backuphistory @PurgeDate

############ MDF, LDF 복구 ################################
EXEC sp_attach_db AAA,
   'E:\SQLDAT\AAA.MDF',
   'E:\SQLDAT\AAA.LDF'

EXEC sp_attach_db AAA,
   'E:\AAA.MDF',
  'E:\AAA.ldf'

############백업한DB 리스토어################################
RESTORE FILELISTONLY FROM disk = 'c:\AAA.SQLDAT'

RESTORE DATABASE AAA
from disk = 'e:\AAA.SQLDAT'
with MOVE 'AAA_Data' to 'c:\AAA.MDF',
MOVE 'AAA_Log' to 'c:\AAA.LDF'

############로그전달복원################################
--디비복원
RESTORE DATABASE "AAA"
WITH NORECOVERY
--로그복원
restore log "AAA"
 FROM DISK =  'c:\aaa\AAA.TRN' 
with norecovery

--로그복원라스트
restore log "AAA"
 FROM DISK =  'c:\aaa\AAA.TRN' 
with recovery

############ 프로시저 제거와등록 ################################
--제거
sp_dropextendedproc 'xp_cmdshell'

--이름 등록
sp_addextendedproc 'xp_cmdshell' ,  'xplog70.dll'
sp_addextendedproc 'xp_regread' ,  'xpstar.dll'
sp_addextendedproc xp_dirtree,'xpstar.dll' -->맞는지 아닌지 한번 확인해볼것

--보안상 회수해야할 권한
--master database
--확장된 저장형 프로시저 퍼미션 삭제
REVOKE EXECUTE ON xp_regread FROM public
REVOKE EXECUTE ON xp_dirtree FROM public
REVOKE EXECUTE ON xp_instance_regread FROM public
--sp_runwebtask 접근을 통한 web task 실행 권한 획득 방지
REVOKE EXECUTE ON dbo.sp_runwebtask FROM public

############### 단일사용자모드로 접속 #############################
alter database AAA set single_user with ROLLBACK IMMEDIATE
sp_renamedb AAA, AAA_OLD
alter database AAA_OLD set multi_user with ROLLBACK IMMEDIATE
############### 계정만들기 프로시저 #############################
--sa계정으로 해야함
use community2

//로그인만들기 아이디, 암호, 디폴트 디비
sp_addlogin 아이디, 암호, 디폴트 디비

//사용자만들기
create user 사용자 from LOgin 로그인이름

//sysadmin권한을 갖도록 한다.
sp_addsrvrolemember '계정', 'sysadmin'

//db_owner 권한을 준다.
sp_addrolemember  'db_owner', '디비'

--현재 부여한 역할 삭제
sp_droprolemember  'db_owner', 'testUser'

--현재 user삭제
sp_dropuser 'testUser'

--현재 login 삭제
sp_droplogin 'testUser'

############### 속도빠른카운트 #############################
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('테이블이름') AND indid < 2

 

############### 모든테이블카운트하기 #############################
sp_MSforeachtable 'select count(*) "?" from ?'

############### 2. 스토어드 프로시져에 대한 파라미터로 테이블 이름을 사용하는 방법
Create proc dbcc_table
    @tabname varchar(30)
    as
    BEGIN
    Exec ("dbcc checktable (" + @tabname + ")")
    END
    go
 
############### 컬럼의선택성체크(낮을수록좋다. #############################
SELECT 1.0 / COUNT(DISTINCT 컬럼명) * 100 FROM 테이블

############### I/O많이 일으키는 순으로 데이타베이스 정리. #############################
WITH DBIO AS
(
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
)
SELECT db, file_type,
CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
CAST(100. * io_stall / SUM(io_stall) OVER()
AS DECIMAL(10, 2)) AS io_stall_pct,
ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;
 
############### 숫자칼럼에서 이빨빠진 최초값 구하기 #############################
SELECT
CASE
WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
ELSE (SELECT MIN(keycol + 1)
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT *
FROM dbo.T1 AS B
WHERE B.keycol = A.keycol + 1))
END;

############### 2005에서2000으로 LinkedServer #############################
2000서비스 팩내의 instcat.sql 스크립트 실행

############### LinkedServer 만들기 #############################
EXEC sp_addlinkedserver @server='DEVSERVER',@srvproduct='', -- 연결이름.
@provider='SQLOLEDB', @datasrc='목적지IP'
EXEC sp_addlinkedsrvlogin 'DEVSERVER', 'false', NULL, '아이디', '암호'

############### lock정보 보기 #############################
select request_session_id As spid,
  resource_type  as restype,
  resource_database_id as dbid,
  resource_description as res,
  resource_associated_entity_id as resid,  
  request_mode as mode,
  request_status as status
 from sys.dm_tran_locks

############### Num값으로 증가하는 테이블 만들기 #############################
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
############################BCP로 데이타 다루기#################################
D:\>bcp "select * from DB이름.dbo.TABLE" queryout d:\test.txt -c -S
서버이름 -U 아이디 -P 암호
--쿼리분석기에서 테이블로 올리기
BULK INSERT mytemp
FROM "d:\test.txt"
WITH (FIRSTROW = 1,ROWTERMINATOR ='\n');
--bcp로 올리기
bcp tempdb.dbo.mytemp in d:\test.txt -c -S 서버이름 -U 아이디-P 암호
bcp "etempdb.dbo.mytemp" in "파일이름" -c -q -S"서버이름" -U"아이디"
-P"암호"
-S : 서버이름을 적습니다.
-U : SQL 서버인증으로 접속할시 계정명을 적습니다.
-P : SQL 서버인증으로 접속할시 암호를 적습니다.
-T : NT인증을 사용하여 접속합니다. 이를 사용하면 -U, -P는 사용하지 않습니다.
-f : 포맷파일의 위치를 지정합니다.
-t : 필드터미네이터라고 부르며 각 컬럼들간의 구분자를 지정합니다.
-r : 로우터미네이더라고 부르며 각 행을 구분짓는 구분자를 지정합니다.
-m : BCP작업이 취소되는 최대 에러갯수를 지정합니다.
-F : 전송이 시작될 첫행의 지시합니다. 기본은 처음부터 입니다.
-L : 전송의 마지막이될 행을 지시합니다. 기본은 맨 마지막 행입니다.
-c : 데이터들을 모두 문자형으로 취급하여 전송합니다.

##############################2005랜덤 데이타 뽑기##############################
단, 테이블 자체를 저장하는 페이지 단위로 추출하기 때문에 대량을 레코드가 있을 시에만 효과적
select * from  테이블 tablesample(0.2 percent)

##############################특정SPID가 날린 쿼리확인##############################
dbcc inputbuffer(SPID)