Microsoft SQL Server Management Studio

1. 메뉴 > 쿼리 > InteliSense 사용

2. 메뉴 > 편집 > InteliSense > 로컬 캐시 새로고침

3. 도구 > 옵션 > 텍스트 편집기 > Transat-SQL > 일반

   > [체크] 멤버 목록 자동 표시

   > [체크] 매개 변수 정보

관련 내용 참고

http://blog.sqlauthority.com/2009/03/31/sql-server-2008-intellisense-does-not-work-enable-intellisense/

위 처럼 해도 안돼 다시 구글링 하여 보니
VS2010 SP1과 충돌이 돼어 안된다는 결론을 얻게 되었다.

현재는 64bit로 갈아탄 상태인데 그래서 안되는 거 일수도...

SQL Server 2008 r2에 대한 누적 업데이트 패키지 7 을 설치하라는 내용을 보게되었다.

http://support.microsoft.com/kb/2507770

에서 SQLServer2008R2_RTM_CU7_2507770_10_50_1777_x64 이 파일을 다운받는 중이다.(헐 300MB 세시간 걸린다)
(사실 뭘 받아야할지 판단이 안선다. 일단 해보고 안되면... 걍 써야겠다.)

오늘 MSSQL에 대한 정말 좋은 글을 보았다.

작년에 MSSQL교육에 나왔던 내용중 많은 부분이 담겨있어 많이 공감하며 읽어 내려갔다.

개발자로써 옆에 끼고 두고두고 명심해야 될 사항이 아닌가 하는 생각이 들어 옮겨놓는다.

출처 : debop.egloos.com/2652763

1. SELECT 절에 명시적으로 필요한 컬럼만을 지정하라.
SELECT * FROM TABLE_NAME 과 같이 모든 컬럼 정보를 가져와야 할 경우도 있지만, 대부분은 그렇지 않을 것이다.
"개발 시에 필요에 따라 컬럼 정보도 변경 될 수 있고, 조회할 컬럼이 바뀔 수도 있는데요?"
맞습니다. 그건 개발 시에 그렇죠. 그러니 최종 Release 할 때, 점검해서 필요한 컬럼만으로 줄여야 합니다.
이게 귀찮거든 TEXT, NTEXT, IMAGE 수형의 컬럼을 VARCHAR(MAX), VARBINARY(MAX)로 변경해라. 이중의 IO 작업을 줄일 수 있다.

2. WHERE 조건이 최적인지 확인하라
  우선 WHERE 조건이나 TABLE의 JOIN 에 쓰이는 컬럼이 인덱스로 잡혔는지 확인하라. 잡혀있어야 JOIN 성능이나 필터링이 좋다.
여러 필터링 중에 예상되는 결과 레코드 수가 적은 것부터 WHERE절에 써라 (이건 SQL Server 2000 부터는 SQL 실행계획이 알아서 해준다.)
다만, 여러번의 쿼리를 통해 임시테이블을 만들고, 조작해야 할 때는 SQL 작성자가 유의해서 작성해야 한다.

3. 컬럼 값의 변형을 WHERE절에 쓰는 경우를 최소화 해라.
  SELECT * FROM Customer WHERE Age + 1 > 20 보다는 WHERE Age > 19 이 빠르다.
물론 ISNULL 등도 마찮가지다.

4. Ctrl + L (SQLServer2008 기준) 를 이용해 실행계획을 확인해라.
  SQL 문의 실행계획을 보면, 처음에는 무슨 말인지 모를 수도 있다. 하지만 자꾸 보고, 쿼리 문을 변경하면서 실행계획이 변경되는 것을 보면서 익혀야 한다.

5. COUNT를 셀 것인가? exists 를 사용할 것인가?
  테이블에 조건에 맞는 레코드가 있음을 확인하는 방법은 count를 세는 방식과 exists 로 검사하는 방식이 있다.
  1. COUNT(*) 로 확인
      select @count = count(*) from Customers where name='aaaa'
      if @count > 0
      begin
  -- xxxxx
      end

  2. exists 사용
      if exists (select * from Customers where name='aaaa')
      begin
           --- xxxx
      end
   어떻게 좋을까? exists 가 좋다. count는 모든 레코드 중 관련된 것을 필터링 한 후 count 함수를 수행하지만, exists는 필터링 시 하나라도 레코드가 있음을 인지했을 때, 반환한다.
   테이블의 전체 레코드 수가 작을 때는 구별 안되지만, 많을 때는 exists 가 효과적이다.

6. count(컬럼명) 을 쓰지말고, count(*) 를 써라.
  count(컬럼명) 은 해당 컬럼 값이 NULL인 경우, count 수에 포함되지 않는다. (여러 집합함수가 다 그렇다). DB에서 NULL값은 오묘하니, 잘 이해해야 한다. DB에서 NULL은 산술 식에서 0을 의미하는 것이 아니라 N/A를 의미한다. 그러므로 비교도 않한다.

7. 커서를 최대한 자제하라.
  RDBMS 에서는 Set 방식을 사용하라 라는 말이 있다. 이 말은 레코드 단위로 addressing을 하는 커서 사용을 자제하라는 것이다.
커서 사용은 쿼리 문 실행을 느리가 하는 주범이다.
어쩔 수 없이 사용해야 할 경우도 있지만, 이 경우도 잘 생각하면, 여러 단계의 SET 방식을 사용해서 해결 할 수 도 있다.
타 시스템의 조직 정보를 재구성하는데 기존 커서 방식이 30초가 걸리던 것을 SET 방식으로 병경해서 3초 이하로 실행시간을 줄인 경우도 있다. 사실 더 줄일 수도 있다.

  또 하나는 임시 테이블을 사용하는 것인데, 이 때 주의할 점은 SQLServer tempdb의 SIZE 설정을 운영 시스템의 환경에 맞게 적절히 설정해야 한다. 기본적으로 data=3Mb(10%), log=1Mb(10%) 일 것이다. 이를 data=10~50Mb(10Mb), log=10Mb(10Mb) 로 변경해주면, 임시테이블 사용시 성능 향상을 가져 올 것이다. (참고 : Temp DB 최적화)
  더 좋은 방법은 테이블 변수를 사용하는 것입니다. 개인적으로 컬럼이 너무 많은 테이블 변수를 정의하는 게 싫어서...
꽁수로 필요한 최소한의 것만 테이블 변수에 정의하여 작업하고, 필요 시 실제 TABLE과 join을 수행하여, 결과 SET을 반환하도록 합니다.

8. VIEW 사용을 자제하라.
개발 편의상, 보안상 여러가지 이유로 VIEW는 참 좋은 개념이고, 사용할만한 가치가 있다.
다만 성능 상의 문제 때문에 자제하라는 것이다.
2번 WHERE 조건절 관련해서 언급했듯이, 결과 레코드가 가장 작게 가져오는 (HIT RATIO 가 높은) 문장이 먼저 실행되어야 하는데, VIEW를 먼저 가져오는 작업이 그 뒤에 실행되는 WHERE 절보다 더 많은 결과를 가져오게 하는 것이 대부분이므로, 성능에는 불리할 수 밖에 없다.
또한 여러 개의 테이블이 조인되는 경우 인덱스 사용에 제약이 많다. 물론 INDEXED VIEW가 있어서 어느정도 성능에는 효과가 있지만, 관련 테이블 변경 시 문제가 발생한다.

9. Table Hints 를 사용해라.
SELECT * FROM Customers with (nolock) 에서 nolock 같은 것이 table hint라 한다.
FASTFIRSTROW, IGNORE_CONSTRAINT, IGNORE_TRIGGERS, NOWAIT, PAGELOCK, TABLELOCK, ROWLOCK, UPDLOCK, XLOCK 등 여러가지가 있다.
일반적인 쿼리문에 적절한 Table Hint를 사용해서, 성능을 높힐 수 있다.

10. 왠만한 Data-Centric Product에서는 Procedure를 사용해라.

Procedure의 장점은 여러가지가 있지만, 몇가지만 나열하면
-. 데이터 무결성 시행
-. 복잡한 비지니스 규칙 표현
-. 캡슐화 가능
-. 네트웍 트래픽 감소 (긴 SQL 구문 축소)
-. 실행 계획 및 컴파일 캐시를 통한 실행 속도 향상
-. 보안 강화 (SQL Injection 방지) - 다른 방안도 있지만.

data-centric한 제품은 가능하면 Procedure를 제작해서 사용하는 것이 좋다. (개인적으로는 NHibernate를 사용하므로, procedure 쓸 일은 거의 없다.)
한가지 주의할 점은 procedure 이름의 접두사에 'sp_' 를 사용하지 말라는 것이다. 이 접두사는 SQLServer가 시스템 Procedure인 줄 알고, 거기부터 뒤진다. 대신 'usp_' 나 제품약어를 사용하는 것이 좋다.

11. 시스템 Procedure, 시스템 View를 잘 활용해라.

제품 자체는 아니지만 SQLServer 시스템의 정보를 활용하거나, 참조해야 할 때 sp_xxxx나 sysXXXX 등 master db에 있는 procedure나 view를 활용하는 것이 좋다.
특히 sql injection도 피하고, 컴파일 및 캐시도 지원하는 sp_executesql 를 잘 활용해라. 한마디로 동적 procedure라 할 수 있다.
다만 parameter가 있는 경우, 실제 값의 크기가 아닌 해당 parameter의 크기로 직접 설정하는 것이 더 성능이 좋다.

예 : 
      1. sp_executesql 'update name=''aaa'' where customers where name=@p0', '@p0 varchar(3)' @p0='bbb'
      2. sp_executesql 'update name=''aaa'' where customers where name=@p0', '@p0 varchar(5)' @p0='bbbbb'

위 두개의 쿼리 문은 똑 같지만 parameter 의 datatype이 달라, 전혀 다른 procedure로 컴파일된다.
가능하면 customer의 name 수형과 같은 수형을 parameter 수형으로 지정해 줘야 한다.

12. 기본 스키마 정보를 SQL 문장에 적어줘라.
select * from Customers 말고 select * from Northwind.dbo.Customers 를 쓰라는 말인데,
이는 sys, INFORMATION_SCHEMA 등 시스템용 schema와 구분짓기 위해서이고, 시스템이 객체 검색 시 효율적이기 때문이다.

13. SET NOCOUNT ON / OFF 를 사용해라.
Procedure의 경우 결과 셋으로 보내는 게 아니라, 내부적으로 중간 단계에서 사용하기 위해 조회를 수행하는 경우가 많다.
이 때 SET NOCOUNT ON 을 지정하고 수행해야, Client에게 조회 결과가 전달되지 않는다.
최종 결과만을 client에 보내기 위해서 SET NOCOUNT OFF 를 설정한 후, 최종 결과 조회 문을 실행해야한다.
이렇게 하는 것이 쓸데 없는 정보가 네트웍을 통해 반환되는 것을 방지한다.
많이 들어 봤겠지만 Network round-trip 이 성능에 가장 큰 적임을 잊지 마라.

14. 되도록 한꺼번에 SQL 문장을 실행하라.
Connection Pool 이 있어, Connection resource에 의한 성능 저하는 그리 없겠지만,
계속된 network round-trip은 성능에 많은 영향을 미친다. 되도록 한꺼번에 요청하고, 반환받아 round-trip을 최소화 해야 한다.

네이버 검색결과

http://blog.naver.com/ttltweety?Redirect=Log&logNo=60107917647

위의 링크에서 내용을 발췌해 복원에 시도하였지만 기존에 DB명이 무엇인지 데이터, 로그 파일명이 무엇인지 모르는 상태에서는

스크립트로 처리하는 부분에 어려움이 있었다.

bak파일이 있다면 아래 그림처럼 데이터베이스 복원에서 처리 하면 되긴하겠지만

왠지 스크립트로 처리 하고 싶은 욕구가 생겨서 시도 해 보았다.

다음은 처리한 스크립트의 예제이다.

--Check .bak FileInfo
RESTORE HEADERONLY FROM DISK = 'D:\MSSQL\DB_BACKUP\XXXXX.bak'   -- DB 백업파일이 있는 위치
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\DB_BACKUP\XXXXX.bak'  -- DB 백업파일이 있는 위치
  

--Restore the Database
RESTORE DATABASE DB_NAME   -- 백업받은 DB의 명칭
FROM DISK= 'D:\MSSQL\DB_BACKUP\XXXXX.bak'   -- DB 백업파일이 있는 위치
WITH Move 'MDF_DATA' TO 'D:\MSSQL\DB_BACKUP\XXXXX.mdf',      -- 백업받은 DB의 MDF 데이터 파일명
 Move 'LDF_LOG' TO 'D:\MSSQL\DB_BACKUP\XXXX.ldf'   -- 백업받은 DB의 LDF 로그 파일명

DB복원에 앞서 백업할때 어떤 정보의 DB내용을 갖고 있는지 잘 확인해서 관리하는 것이 중요할거 같단 생각이 많이 들었다.

사실 오늘 이 작업도 다른 팀에서 백업을 받아와 복원한 상황이었던 터라 사전에 미리 말하지 못한 부분도 있고

사실 이전엔 백업, 복원 뭐 이런거 굳이 신경을 안써서 그랬을수도 있고 암튼 오늘 시원하게 복원할 수 있어 개운하다. ㅋㅋ

SELECT * FROM TABLE1 A WITH (NOLOCK)
            LEFT OUTER JOIN TABLE2 B WITH (INDEX = [INDEX_NAME], NOLOCK)
                                       ON A.[COL1] = B.[COL2]

-- 사실 MSSQL에서는 HINT사용을 그다지 추천하지 않는다고 합니다.(인터넷 여기저기 찾아본결과)

-- 하지만 하나의 테이블에 여러개의 HINT가 생성이 된 경우 의도하지 않는 결과값이 나온다고 판단된다면

-- 위와 같이 테이블에 HINT를 사용해 어느 인덱스를 사용할것인지 지정해 주는 것도 좋은 방법이 되지 않을까 생각이 됨.

------------------------------------------------------------------------------------------------------------------------------------------
-- MSSQL 모든테이블 통계 업데이트
------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL VARCHAR(5000)
SELECT @SQL = ''

DECLARE TAB_CUR CURSOR FOR
      SELECT A = 'UPDATE STATISTICS ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
      
OPEN TAB_CUR
FETCH NEXT FROM TAB_CUR INTO @SQL
WHILE(@@FETCH_STATUS = 0)
BEGIN
   EXEC (@SQL)
   FETCH NEXT FROM TAB_CUR INTO @SQL
END      

CLOSE TAB_CUR
DEALLOCATE TAB_CUR

DB Lock관련해서 sp_lock부분을 조금 이용하여 아래 LOCK걸린구문 찾는 쿼리를 한번 만들어 보았다.

TOP 1 부분이 좀 애석하긴 한데 그래도 이정도가 어디야~~~

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

declare @spid int = 0

select top 1 @spid = spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status 

from (

select convert (smallint, req_spid) As spid,

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

substring (v.name, 1, 4) As Type,

substring (rsc_text, 1, 32) as Resource,

substring (u.name, 1, 8) As Mode,

substring (x.name, 1, 5) As Status

from master.dbo.syslockinfo,

master.dbo.spt_values v,

master.dbo.spt_values x,

master.dbo.spt_values u

where   master.dbo.syslockinfo.rsc_type = v.number

and v.type = 'LR'

and master.dbo.syslockinfo.req_status = x.number

and x.type = 'LS'

and master.dbo.syslockinfo.req_mode + 1 = u.number

and u.type = 'L') a where a.Mode = 'X'

group by spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status

PRINT '@spid > ' + CONVERT(VARCHAR(5), @spid)

if @SPID > 0

begin

   DBCC INPUTBUFFER(@spid)

end

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

@spid > 0

혹은

Language Event | 0 | exec up_tRecvOrdSys 'RBC', '',1

요로케 나올것이다.

MSSQL 사용하면서 주로 즐겨쓰는 쿼리 단축키에 대해서 한번 정리해 볼까한다.

뭐 정리라 할건 없고 단순히 등록해 단축키로 간단히 사용하는 방법으로 

지난 5월 한국DB진흥회 무상 MSSQL교육시 정원혁 강사가 소개해 준 내용이기도 하다.

먼저 Tools > Options 로 이동한다.

Options에서 Environment > Keyborad 이동한 다음 오른쪽 표에 나오는 Ctrl + 3, 4, 5 에 위 항목을 각각 입력하고 OK누르면 끝이다.

참고로 Ctrl + 1, 2는 기본 내장된 쿼리로 수정이 되지 않는다.(적어도 내가 했을땐)

자 그럼 실제로 단축키를 이용했을때 나온 결과를 확인해 보도록 하자.

1. Ctrl + 3(선택한 테이블의 TOP 100 항목을 조회한다. [SELECT TOP 100 * FROM])

2. Ctrl + 4(선택한 테이블의 ROWS, 저장용량, 인덱스 사이즈등이 표시된다.[sp_spaceused]. 테이블 앞에 dbo가 붙으면 조회 안됨)

3. Ctrl + 5(선택한 테이블의 인덱스 상황등이 표시된다.[sp_helpindex]. 테이블 앞에 dbo가 붙으면 조회 안됨)

위와 같이 나온 결과를 각각 확인할 수 있다.

처음엔 손에 안익어 적응하기까지 하루이틀 정도가 걸렸던거 같은데 적응하고 나니 쿼리 짜는 속도 그리고

해당 테이블이 어떤 컬럼을 갖고 있는지 총 데이터는 얼마나 있는지 그리고 인덱스는 어떻게 걸려있는지 등을 빠르게 이해할수있어서

정말 편하게 잘 이용하고 있다.

강추 하고 싶다 ^^;

2023-07-25 내용추가

sp_helptext : 프로시저 내용을 표시함, 개인적으로는 Ctrl + 6 으로 사용중임

아래와 같이 날려주면 된다.

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

select a.name, b.text 

from sys.sysobjects a inner join sys.syscomments b on a.id = b.id

where a.xtype = 'P'

and a.category = 0

and b.text like '%검색할내용%'

--Create a target table

CREATE TABLE Products

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

) 

GO

--Insert records into target table

INSERT INTO Products

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 20.00),

(3, 'Muffin', 30.00),

(4, 'Biscuit', 40.00)

GO

--Create source table

CREATE TABLE UpdatedProducts

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

) 

GO

--Insert records into source table

INSERT INTO UpdatedProducts

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 25.00),

(3, 'Muffin', 35.00),

(5, 'Pizza', 60.00)

GO

SELECT * FROM Products

SELECT * FROM UpdatedProducts

GO

MERGE Products AS TARGET

USING UpdatedProducts AS SOURCE 

ON (TARGET.ProductID = SOURCE.ProductID) 

WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 

OR TARGET.Rate <> SOURCE.Rate THEN 

UPDATE SET TARGET.ProductName = SOURCE.ProductName, 

TARGET.Rate = SOURCE.Rate

WHEN NOT MATCHED BY TARGET THEN 

INSERT (ProductID, ProductName, Rate) 

VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

WHEN NOT MATCHED BY SOURCE THEN 

DELETE;

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

Merge 테이블 사용할 기회가 생겨 구글링 해보았음.

좋은 예제인거 같아서 올려봄.

[참고] http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

[참고] http://msdn.microsoft.com/en-us/library/bb510625.aspx