코봉이와 오라클: msSql

레이블이 msSql인 게시물을 표시합니다. 모든 게시물 표시

2009년 7월 25일 토요일

msSql 기초

View 생성

테이블 내용 출력

스크립트 생성

Text 모드로 접속

Mssql,mysql,oracle차이점

mssql, mysql, oracle 차이점

mssql

: 마이크로 소프트에서 만든 데이터베이스로 윈도우즈에 특화되어있는 데이터베이스입니다.

기업체에서 사용을 할때에는 라이선스료를 지불을하고 사용을 하여야합니다.

mysql

: 현제 선마이크로시스템에서 운영하고있는 오픈소스계열의 데이터 베이스입니다.

각기 특징은 가지고 있습니다만 기본적인 기능은 mssql과 비슷하기에 착각을 하셨음직도 합니다.

오픈 소스진영인만큼 기본적으로는 무료로 사용하실수 있습니다.

oracle(sqlplus)

: 오라클에서 만들어 판매중인 기업용 데이터베이스입니다.

윈도우즈, 유닉스, 솔라리스 등 기업체에서 사용하는 대부분의 서버군에 설치를 할 수 있습니다.

mssql, mysql보다 대량의 정보관리를 할 때에 가장 좋은 성능을 보입니다.

sqlplus는 오라클과 연결을 해서 명령을 내릴 수 있는 프로그램입니다.

(상당히 거리가 있는 설명이 될진 모르지만 온라인 게임에 접속을 하기위해서 실행시키는 게임프로그램을 생각해보시면 이해하시기 편할 듯 합니다. 조작은 자기 컴퓨터에서 하지만 실제의 정보처리는 게임서버에서 한다는 점)

msAccess

: 소규모 데이터베이스를 활용하고자 할 경우 자주 사용되는 데이터베이스입니다.

엑셀등와 쉽게 연계를 할 수 있기에 소규모 사무실에서 쉽고 간단히 활용을 할 수 있습니다.

기본적으로 윈도우즈에서만 사용이가능합니다.

msSql 기초

쿼리 작업을 하다가 lastIndexOf 기능이 필요해서 찾아봤더니.. SQL 문자열 함수중에는 없더군요. 혹시 내가 모르는 다른 함수가 있는지는 모르겠지만.

촌각을 다투는 데이터 추출작업에 사용하기 위해서 단 1~2분 내에 해결방법을 찾아보았는데.

유용할지 모르겠습니다.

필요했던 기능은 다음입니다.

varchar(15) 로 정의된 ip 주소가 있습니다.

"192.168.1.22"

"61.38.79.23"

등의 형태로 들어옵니다.

여기서 특정 네트워크 대역에 있는 ip를 구분하기 위해서 맨 마지막의 22 나 23 값이 필요한거죠

아래는 특정 ip를 예를 들어서 SQL 문자열 함수를 이용하여 단계적으로결과가 어떻게 나오는지 확인합니다.

그리고 맨 마지막이 실제적으로 사용한 최종 쿼리 문입니다.

declare @vcIP varchar(15)

set @vcIP = '115.137.44.148'

select CHARINDEX('.', @vcIP)

select REVERSE(@vcIP)

select CHARINDEX('.', REVERSE(@vcIP))

select LEN(@vcIP)- CHARINDEX('.', REVERSE(@vcIP))

select CHARINDEX('.', @vcIP,LEN(@vcIP)- CHARINDEX('.', REVERSE(@vcIP)))

[최종 쿼리]

select SUBSTRING(@vcIP,CHARINDEX('.', @vcIP,LEN(@vcIP)- CHARINDEX('.', REVERSE(@vcIP))) + 1,

LEN(@vcIP) - CHARINDEX('.', @vcIP,LEN(@vcIP)- CHARINDEX('.', REVERSE(@vcIP)) + 1))

UPSERT Functionally in SQL Server 2008

UPSERT Functionality in SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

The UPSERT command inserts rows that don't exist and updates the rows that do exist. The Word UPSERT is a fusion of the words UPDATE and INSERT. UPSERT was officially introduced in the SQL:2003 standard.

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">IF FOUND THEN UPDATEELSE INSERT;</PRE>

In SQL Server 2008, Microsoft introduces the UPSERT functionality through the MERGE command.

Until now, this UPSERT functionality could only be made available in SQL Server through stored procedures and cursors, etc.

This article illustrates how to take advantage of the UPSERT functionality via the MERGE command.

Note: This article is written based on SQL Server 2008 CTP6.

Step 1

Let's assume that we have a database [MyDatabase] as shown below. [Refer Fig 1.0]

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [master]GO/****** Object: Database [MyDatabase] Script Date: 03/16/2008 10:55:35 ******/IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')DROP DATABASE [MyDatabase]GOcreate database [MyDatabase]go</PRE>

Fig 1.0

Step 2

Let's assume that we have the following three tables, as shown below. [Refer Fig 1.1]

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [MyDatabase]GO /****** Object: Table [dbo].[MyTable] Script Date: 03/16/2008 11:07:24 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))DROP TABLE [dbo].[MyTable]GO create table MyTable (id int primary key, name varchar(100),salary money)goinsert into MyTable select 1,'Catherine Donnel',200000insert into MyTable select 2,'Stacey Kost',150000insert into MyTable select 3,'Jason Leanos',36000insert into MyTable select 4,'Catherine O''Donnel',20000insert into MyTable select 5,'Rainbow Dance',20000 goUSE [MyDatabase]GO /****** Object: Table [dbo].[MyTable2] Script Date: 03/16/2008 11:07:36 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable2]') AND type in (N'U'))DROP TABLE [dbo].[MyTable2]GO create table MyTable2 (id int primary key, name varchar(100),salary money)goinsert into MyTable2 select 1,'Catherine O''Donnel',220000insert into MyTable2 select 2,'Stacey Kostue',230000insert into MyTable2 select 4,'Catherine Bonaparte',20000insert into MyTable2 select 9,'Irina Zolotrova',40000insert into MyTable2 select 5,'Eva Jane',40034go</PRE>

Fig 1.1

Step 3

Now let's query both of the tables to see the difference between them.

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [MyDatabase]GOSelect * from MyTableGo</PRE><PRE>Results:</PRE><PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine Donnel, 200000.002, Stacey Kost, 150000.003, Jason Leanos, 36000.004, Catherine O'Donnel, 20000.005, Rainbow Dance, 20000.00 (5 row(s) affected)</PRE><PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">USE [MyDatabase]GOSelect * from MyTable2GO</PRE>

Results:

<PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine O'Donnel, 220000.002, Stacey Kostue, 230000.004, Catherine Bonaparte, 20000.005, Eva Jane, 40034.009, Irina Zolotrova, 40000.00 (5 row(s) affected)</PRE>

From the results, we can easily understand that if we want to merge all of the rows from Mytable2 to Mytable, then the following updates and following inserts are supposed to be executed. [Refer Fig 1.2]

Fig 1.2

Step 4

Now let's UPSERT the table by using the following merge command to merge the content of the table Mytable2 to the table Mytable. [Refer Fig 1.3]

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [MyDatabase]GOmerge into mytable as Targetusing mytable2 as Sourceon Target.id=Source.idwhen matched then update set Target.name=Source.name,Target.Salary = Source.Salarywhen not matched theninsert (id,name,salary) values (Source.id,Source.name,Source.Salary);</PRE>

Fig 1.3

Step 5

Now let's query the target table, MyTable, as shown below. [Refer Fig 1.4]

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">USE [MyDatabase]GOSelect * from MyTableGO</PRE>

Results:

<PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine O'Donnel, 220000.002, Stacey Kostue, 230000.003, Jason Leanos, 36000.004, Catherine Bonaparte, 20000.005, Eva Jane, 40034.009, Irina Zolotrova, 40000.00 (6 row(s) affected)</PRE>

Fig 1.4

From the results, we see that all of the existing rows in the table, Mytable, have been updated with the data from the table, Mytable2. In addition, we see that any new rows that were present in the Mytable2 table have been inserted to the table, MyTable.

Basically, the merge command executed the following algorithm.

Note: The below syntax is not a transact SQL command. It is just an algorithm.

<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: blue" hasbox="2">If target.ID = 1 is found in Source.id = 1 {found}thenupdate target set target.name {Catherine Donnel} = source.name {Catherine O''Donnel}Target.salary {200000}=Source.salary {220000}end if If target.ID = 2 is found in Source.id = 2 {found}thenupdate target set target.name {Stacey Kost} = source.name {Stacey Kostue}Target.salary {150000.00} = Source.salary {230000.00}end if If target.ID=3 is found in Source.Id = NULL {not found} then end if If target.ID=4 is found in Source.id = 4 {found}thenupdate target set target.name {Catherine O''Donnel} = source.name {Catherine Bonaparte}Target.salary {20000.00} = Source.salary {20000.00}end if If target.ID=5 is found in Source.id = 5 {found}thenupdate target set target.name {Rainbow Dance} = source.name {Eva Jane}Target.salary {20000.00} = Source.salary {40034.00}end if If target.ID not found in Source.id = 9 {found}theninsert into target (id,name,salary) select source (9, Irina Zolotrova, 40000.00) end if</PRE>

Conclusion

This article has illustrated the functionality of UPSERT via the MERGE command in SQL Server 2008.

msSql 기초

뷰 확인

SQL Server 쿼리 프로세서에서는 인덱싱된 뷰와 인덱싱되지 않은 뷰가 다르게 처리됩니다.

인덱싱된 뷰의 행은 테이블과 동일한 형식으로 데이터베이스에 저장됩니다. 쿼리 프로세서에서 쿼리 계획에 인덱싱된 뷰를 사용하기로 결정하면 인덱싱된 뷰는 기본 테이블과 동일한 방법으로 처리됩니다.
인덱싱되지 않은 뷰의 정의만 저장되고 뷰의 행은 저장되지 않습니다. 쿼리 최적화 프로그램은 인덱싱되지 않은 뷰를 참조하는 SQL 문에 대해 작성하는 실행 계획에 뷰 정의의 논리를 추가합니다.

SQL Server 쿼리 최적화 프로그램에서 인덱싱된 뷰의 사용 시기를 결정하는 데 사용되는 논리는 테이블 인덱스의 사용 시기를 결정하는 데 사용되는 논리와 유사합니다. SQL 문의 전체나 일부가 인덱싱된 뷰의 데이터를 포괄하고 해당 뷰의 인덱스가 저렴한 비용의 액세스 경로로 확인되면 쿼리의 이름이 이 뷰를 참조하는지 여부와 관계없이 인덱스가 선택됩니다. 자세한 내용은 뷰의 인덱스 확인을 참조하십시오.

SQL 문에서 인덱싱되지 않은 뷰를 참조할 경우 파서와 쿼리 최적화 프로그램은 SQL 문의 원본과 뷰의 원본을 모두 분석하고 단일 실행 계획을 세웁니다. SQL 문과 뷰에 대해 별도의 계획이 있는 것은 아닙니다.

예를 들어 다음과 같은 뷰가 있습니다.

USE AdventureWorks;
GO
CREATE VIEW EmployeeName AS
SELECT h.EmployeeID, c.LastName, c.FirstName
FROM HumanResources.Employee AS h
JOIN Person.Contact AS c
ON h.ContactID = c.ContactID;
GO

이 뷰를 기반으로 두 SQL 문이 모두 기본 테이블에 대해 동일한 작업을 수행하고 동일한 결과를 생성합니다.

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks.dbo.EmployeeName AS EmpN
ON (soh.ContactID = EmpN.EmployeeID)
WHERE OrderDate > '20020531';

/* SELECT referencing the Contact and Employee tables directly. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks.HumanResources.Employee AS e
JOIN AdventureWorks.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.EmployeeID
JOIN AdventureWorks.Person.Contact AS c
ON e.ContactID =c.ContactID
WHERE OrderDate > '20020531';

SQL Server Management Studio 실행 계획 기능을 통해 관계형 엔진이 두 SELECT 문에 대해 동일한 실행 계획을 세우는 것을 확인할 수 있습니다.

뷰에 힌트 사용

쿼리의 뷰에 힌트를 넣으면 뷰가 확장되어 기본 테이블에 액세스할 때 발견되는 다른 힌트와 서로 충돌할 수 있습니다. 이러한 경우 쿼리에서 오류를 반환합니다. 예를 들어 다음과 같이 뷰 정의에 테이블 힌트가 포함되어 있습니다.

USE AdventureWorks;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

다음 쿼리를 입력한다고 가정합니다.

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

쿼리의 Person.AddrState 뷰에 적용되는 SERIALIZABLE 힌트가 뷰 확장 시 뷰의 Person.Address 테이블과 Person.StateProvince 테이블에 모두 전파되기 때문에 이 쿼리는 실패합니다. 그러나 뷰가 확장될 때 Person.Address의 NOLOCK 힌트도 나타납니다. SERIALIZABLE 힌트와 NOLOCK 힌트가 충돌하기 때문에 결과 쿼리가 올바르지 않습니다.

PAGLOCK, NOLOCK, ROWLOCK, TABLOCK 또는 TABLOCKX 테이블 힌트는 서로 충돌하며 HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD 및 SERIALIZABLE 테이블 힌트도 마찬가지입니다.

여러 수준의 중첩된 뷰를 통해 힌트가 전파될 수 있습니다. 예를 들어 v1 뷰에 HOLDLOCK 힌트를 적용하는 쿼리가 있다고 가정합니다. v1이 확장될 때 이 뷰의 정의에 v2 뷰가 포함되어 있음을 확인했습니다. v2의 정의에는 이 뷰의 기본 테이블 중 하나에 대한 NOLOCK 힌트가 있습니다. 그러나 이 테이블에는 v1 뷰의 쿼리로부터 HOLDLOCK 힌트도 상속됩니다. NOLOCK 힌트와 HOLDLOCK 힌트가 충돌하므로 쿼리가 실패합니다.

뷰를 포함하는 쿼리에 FORCE ORDER 힌트를 사용하면 정렬된 구조체에서의 뷰 위치에 따라 뷰 내의 테이블 조인 순서가 결정됩니다. 예를 들어 다음 쿼리는 세 개의 테이블과 한 개의 뷰에서 선택합니다.

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1
AND Table2.Col1 = View1.Col1
AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER)

View1은 다음과 같이 정의됩니다.

CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

쿼리 계획의 조인 순서는 Table1, Table2, TableA, TableB, Table3입니다.

원본 위치 <http://msdn.microsoft.com/ko-kr/library/ms190237.aspx>

뷰의 인덱스 확인

뷰의 인덱스 확인

인덱스와 마찬가지로 SQL Server에서는 쿼리 최적화 프로그램에서 쿼리 계획에 인덱싱된 뷰를 사용하는 것이 효과적이라고 판단한 경우에만 인덱싱된 뷰를 사용합니다.

모든 버전의 SQL Server에서 인덱싱된 뷰를 만들 수 있습니다. SQL Server Enterprise의 경우 쿼리 최적화 프로그램에서 인덱싱된 뷰를 자동으로 고려합니다. 다른 모든 버전에서 인덱싱된 뷰를 사용하려면 NOEXPAND 테이블 힌트를 사용해야 합니다.

다음 조건에 맞을 때 SQL Server 쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용합니다.

다음 세션 옵션이 ON으로 설정되어 있습니다.
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
NUMERIC_ROUNDABORT 세션 옵션이 OFF로 설정되어 있습니다.
쿼리 최적화 프로그램에서 쿼리의 요소와 뷰 인덱스 열 간의 일치 사항을 찾습니다. 예를 들어 다음과 같은 사항이 일치합니다.
WHERE 절의 검색 조건 조건자
조인 작업
집계 함수
GROUP BY 절
테이블 참조
인덱스 사용 시 예상 비용이 쿼리 최적화 프로그램에서 고려하는 액세스 메커니즘의 비용 중에서 가장 낮습니다.
인덱싱된 뷰의 테이블 참조에 해당하는 쿼리에서 뷰를 확장하여 기본 테이블에 액세스하는 방식으로 테이블을 참조하거나 직접 테이블을 참조하는 경우 쿼리에서 참조하는 모든 테이블에 같은 힌트 집합이 적용되어 있어야 합니다.

참고:
READCOMMITTED 및 READCOMMITTEDLOCK 힌트는 현재 트랜잭션 격리 수준에 관계없이 항상 이 컨텍스트에서 다르게 고려됩니다.

SET 옵션 및 테이블 힌트에 대한 요구 사항을 제외하고 위의 사항은 쿼리 최적화 프로그램에서 쿼리가 테이블 인덱스 범위에 해당하는지 즉, 테이블 인덱스로 쿼리를 처리할 수 있는지 여부를 확인하는 데 사용하는 규칙과 동일합니다. 인덱싱된 뷰를 사용하기 위해 쿼리에 아무 것도 추가로 지정할 필요가 없습니다.

쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용하도록 쿼리의 FROM 절에서 인덱싱된 뷰를 명시적으로 참조할 필요가 없습니다. 쿼리가 인덱싱된 뷰에도 있는 기본 테이블의 열에 대한 참조를 포함하고 쿼리 최적화 프로그램에서 해당 인덱싱된 뷰를 사용할 때 비용이 가장 저렴한 액세스 메커니즘을 제공할 수 있을 것으로 예상하는 경우 쿼리 최적화 프로그램은 기본 테이블 인덱스가 쿼리에서 직접 참조되지 않을 때 이러한 기본 테이블 인덱스를 선택하는 것과 유사한 방법으로 인덱싱된 뷰를 선택합니다. 쿼리에서 참조하지 않는 열을 포함하는 뷰의 경우 뷰가 쿼리에 지정된 하나 이상의 열을 포괄하기 위한 가장 저렴한 비용 옵션을 제공하면 쿼리 최적화 프로그램에서 이 뷰를 선택할 수 있습니다.

쿼리 최적화 프로그램은 FROM 절에서 참조하는 인덱싱된 뷰를 표준 뷰로 간주하고 처리합니다. 쿼리 최적화 프로그램은 최적화 프로세스 시작 시 뷰의 정의를 쿼리로 확장합니다. 그런 다음 인덱싱된 뷰 일치가 수행됩니다. 최적화 프로그램에서 선택하는 최종 실행 계획에 인덱싱된 뷰가 사용될 수 있습니다. 또는 계획이 뷰에서 참조하는 기본 테이블에 액세스하여 뷰에서 필요한 데이터를 구체화할 수 있습니다. 최적화 프로그램에서는 이 중 가장 저렴한 비용의 방법이 선택됩니다.

인덱싱된 뷰에 힌트 사용

EXPAND VIEWS 쿼리 힌트를 사용하여 쿼리에 뷰 인덱스가 사용되지 않도록 하거나 NOEXPAND 테이블 힌트를 사용하여 쿼리의 FROM 절에 지정된 인덱싱된 뷰에 인덱스가 사용되도록 할 수 있습니다. 그러나 쿼리 최적화 프로그램이 각 쿼리에 사용할 최상의 액세스 방법을 동적으로 결정하도록 해야 합니다. EXPAND와 NOEXPAND는 성능을 크게 향상시키는 것으로 확인된 특정 경우에만 사용합니다.

EXPAND VIEWS 옵션은 쿼리 최적화 프로그램이 전체 쿼리에 뷰 인덱스를 사용하지 않도록 지정합니다.

뷰에 NOEXPAND를 지정하면 쿼리 최적화 프로그램은 뷰에 정의된 인덱스의 사용을 고려합니다. 선택적 INDEX() 절을 사용하여 NOEXPAND를 지정하면 쿼리 최적화 프로그램은 지정된 인덱스를 사용합니다. NOEXPAND는 인덱싱된 뷰에만 지정할 수 있고 인덱싱되지 않은 뷰에는 지정할 수 없습니다.