▶ 개요

테이블 반환 함수는 뷰 대신해서 table 데이터 형식을 반환 할 수 있다.

테이블 반환 함수는 inline TVFs 와 MultiStatement TVFs 으로 구분가능하다.

▶ 구성

1) inline TVFs

CREATE FUNCTION [함수명]

(@_param1 [parameter type], @_param2 [parameter type] ...)

RETURNS TABLE 

AS

RETURN 

(

SELECT *

 FROM [table_1]

WHERE [column_1] = @_param1

  AND ...

);

GO

2) MultiStatement TVFs

CREATE FUNCTION [함수명]

(@_param1 [parameter type], @_param2 [parameter type] ...)

RETURNS [반환할 테이블변수명] TABLE ( column_1 [column_1 type], column_2 [column_2 type] ... )

AS

BEGIN

DECLARE @_var int

...

INSERT [반환할 테이블변수명]

SELECT column_1

, column_2

 FROM [table_1]

WHERE [column_3] = @_param1

  AND ...

RETURN 

END

GO

inline TVFs와 MultiStatement TVFs 차이는 BEGIN ~ END 선언부가 없다.

inline TVFs는 바로 RETURN 절에서 반환 집합을 계산한다.

MultiStatement TVFs는 반환테이블의 정의 및 다중 T-SQL 구문 사용 가능하다.

▶ 뷰, inline TVFs, MultiStatement TVFs 비교

▶ 출처

https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

http://stackoverflow.com/questions/4960137/table-valued-functiontvf-vs-view

http://www.mssql.org/LectureSQL/02_sql2k/ufn_04.htm

▶ 개요

단일 값(single value) 반환

ex) GETDATE(), SUBSTRING()

▶ 구성

create function [함수명](@_param1 [parameter type], @_param2 [parameter type] ...)

returns [반환 type]

begin

declare @_rVal [반환 type] --임시변수

...

return @_rVal

end

▶ 예시 (정규식 문자열 치환)

- 구현sql

- 실행결과

▶ 출처

https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/

http://hwoarang757.tistory.com/entry/MSSQL-%EC%96%B4%EC%84%A4%ED%94%88-RegEx-Replace-Function-%EC%97%AD%ED%95%A0-%ED%95%A8%EC%88%98-%EC%98%88%EC%A0%9C-0

▶ 구성

MSSQL에서 지원하는 사용자정의 함수는 크게 두가지로 구분된다.

- 스칼라 반환함수(Scalar function)(SF)

- 테이블 반환함수(Table Valued Function)(TVF)

▶ 사용자정의함수를 사용하는 이유는 무엇인가?

1) 중복적으로 사용되는 코드를 함수화 함으로써 재사용성을 높힙니다. 이를 통해서 생산성 향상과 무결성 유지에 도움을 줍니다.

2) 간결한 코드작성을 가능토록 하여서 코드품질을 향상시킵니다.

3) 변경사항 발생시 유지보수를 용이하게 합니다.

그 외에도 생성한 인덱스를 인위적으로 사용하기 위해서, 임시테이블사용을 피함으로서 성능향상 등의 사유로 사용되어집니다.

▶ 출처

https://technet.microsoft.com/ko-kr/library/ms191165(v=sql.105).aspx

http://warehouseofdev.tistory.com/entry/mssql-%EC%8A%A4%EC%B9%BC%EB%9D%BC-%ED%95%A8%EC%88%98%EC%99%80-%ED%85%8C%EC%9D%B4%EB%B8%94-%EB%B0%98%ED%99%98-%ED%95%A8%EC%88%98

http://blog.naver.com/PostView.nhn?blogId=rocabilly&logNo=140133522180

http://www.mssql.org/LectureSQL/02_sql2k/ufn_05.htm

http://purumae.tistory.com/179

▶ Key

개체에서 유일하게 식별토록하는 하나이상의 속성

A key is one or more data attributes that uniquely identify an entity

▶ Composite key

두개 이상의 속성으로 구성된 키

A key that is composed of two or more attributes. 

▶ Natural key

실제 비즈니스상에서 사용되어지는 속성으로 구성된 키

ex) 주민번호

A key that is formed of attributes that already exist in the real world.  

For example, U.S. citizens are issued a Social Security Number (SSN)  that is unique to them (this isn't guaranteed to be true, but it's pretty darn close in practice).  

SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.). 

▶ Surrogate key

비즈니스상에서는 의미를 지니고 있지 않는 키

A key with no business meaning.

▶ Candidate key

논리적 데이터 모형에서의 개체타입은 0개 이상의 후보키를 가지고 있지만, 한편으로는 유일 식별자를 가질 수 있다.

An entity type in a logical data model will have zero or more candidate keys, 

also referred to simply as unique identifiers (note: some people don't believe in identifying candidate keys in LDMs, so there's no hard and fast rules). 

For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type 

and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key. 

Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, 

an alternate key or perhaps not even a key at all within a physical data model. 

▶ Primary key

개체타입에서 참조되는 키

The preferred key for an entity type.

▶ Alternate key

잘 알려진 부차적 키로서, 테이블 내 행에서 또다른 유일 식별자 

Also known as a secondary key, is another unique identifier of a row within a table. 

▶ Foreign key

개체타입내에서 하나이상의 속성으로, 다른 개체타입 내에서 우선적 또는 부차적으로 대표되는 키

One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.

▶ 출처

http://www.agiledata.org/essays/keys.html

-- create temporary table
create table #temp (seq int identity(1,1), note text)

-- insert dummy data
insert into #temp(note)
select 'This is text dataType data!'

-- Error
select seq
	 , note
	 , note+' add varchar dataType.' as add_note
  from #temp

-- Success
select seq
	 , note
	 , convert(text, convert(varchar(max),note)+' add varchar dataType.') as add_note
  from #temp

▶ Error 인 경우

▶ Success 인 경우

CREATE TRIGGER [트리거명] ON [트리거 생성할 테이블명]
FOR UPDATE 
AS
BEGIN 
	IF UPDATE([특정 필드명]) BEGIN
		-- PK, FK등 순회 조건을 알맞게 변수Name과 변수Type을 선언해주세요.
		DECLARE @_조건1	VARCHAR(5)
		DECLARE @_조건2	VARCHAR(5)
		DECLARE @_조건3	VARCHAR(5)
		
		DECLARE @_특정필드 VARCHAR(1)

		DECLARE UPDATE_CURSOR CURSOR FOR
			SELECT [순회조건1], [순회조건2], [순회조건3], [특정 필드명] FROM INSERTED
		
		OPEN UPDATE_CURSOR
		FETCH NEXT FROM UPDATE_CURSOR INTO @_조건1, @_조건2, @_조건3, @_특정필드
		
		WHILE @@FETCH_STATUS = 0 BEGIN
			-- 구현로직 입력
			
			FETCH NEXT FROM UPDATE_CURSOR INTO @_조건1, @_조건2, @_조건3, @_특정필드
		END
		
		CLOSE UPDATE_CURSOR;
		DEALLOCATE UPDATE_CURSOR;
	END
END

- SET NOCOUNT ON/OFF

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

예시1)

CREATE PROCEDURE dbo.getRecordSet

BEGIN

    SELECT *

      FROM #LIST

END

예시2)

CREATE PROCEDURE dbo.getRecordSet

BEGIN

SET NOCOUNT ON

     SELECT *

 FROM #LIST

END

EXEC dbo.getRecordSet 호출 시 예시1은 RecordSet을 넘겨 받지 못하며, 예시2은 RecordSet을 넘겨 받는다.

프로시저는 항상 자신의 마지막 로직(SELECT, UPDATE, INSERT, DELETE)에서 영향받은 record 수를 반환한다. 

이를 통해서, 'N개의 행이 적용되었습니다.' 메세지를 뿌려주게 된다. SET NOCOUNT ON 할 경우, record 수를 반환하지 않는다.

- SET ANSI_NULLS ON/OFF

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2016.

예시전 기초 Table 생성)

SELECT '1' AS SEQ, NULL AS VALUE

INTO #LIST

UNION ALL

SELECT '2' AS SEQ, 'A' AS VALUE

UNION ALL

SELECT '3' AS SEQ, 'B' AS VALUE

SET ANSI_NULLS ON

GO

SELECT SEQ, VALUE

  FROM #LIST

 WHERE VALUE = NULL

SET ANSI_NULLS OFF

GO

SELECT SEQ, VALUE

  FROM #LIST

 WHERE VALUE = NULL

예시1은 조회된 record 수가 없으며, 예시2은 seq=1의 record 조회 된다.

SELECT *

  FROM #LIST

 WHERE VALUE IS NULL

SET ANSI_NULLS 과 무관하게 VALUE=NULL인 record를 조회하고 싶다면, 다음과 같이 Sql 문을 작성하자.

- SET QUOTED_IDENTIFIER ON/OFF

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

예시1-1)

SET QUOTED_IDENTIFIER ON

GO

select "실행" as seq

예시1-2)

SET QUOTED_IDENTIFIER OFF

GO

select "실행" as seq

예시2-1)

SET QUOTED_IDENTIFIER ON

GO

select CHAR(39)+'실행'+CHAR(39) as seq

예시2-1)

SET QUOTED_IDENTIFIER OFF

GO

select "'실행'" as seq

예시1-1은 Error가 발생하며, 예시1-2은 Error가 발생되지 않는다.

예시2-1, 예시2-2은 같은 결과를 return 한다.

예시3-1은 seq=1의 record 조회되며, 예시3-2은 조회된 record 수가 없다. 

옵션이 ON으로 설정되면 식별자를 큰따옴표(")로 구분할 수 있고, 리터럴을 작은따옴표(')로 구분해야 합니다.

OFF(기본값)로 설정되면 식별자가 따옴표 안에 있을 수 없고 모든 Transact-SQL 식별자 규칙을 따라야 합니다. ex) [VALUE] (O), "VALUE" (X)

리터럴은 작은따옴표(') 또는 큰따옴표(")으로 구분할 수 있습니다.

* 참조

https://msdn.microsoft.com/en-us/library/ms190356.aspx

※ MS SQL Server Management Studio 단축키 모음

* 출처

http://www.sqlserver-training.com/sql-server-management-studio-ssms-keyboard-shortcut-keys/-

http://www.sqler.com/342401

1. SQL Server Management Studio 2008 인 경우

C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

2. SQL Server Management Studio 2012 인 경우

C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

3. SQL Server Management Studio 2014 인 경우

C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

* 출처

http://stackoverflow.com/questions/349668/removing-the-remembered-login-and-password-list-in-sql-management-studio