SQL Server 시작하기
박용준 강사가 진행하는 데브렉 SQL Server 강좌에 오신 것을 환영합니다.
현재 다양한 SQL Server 관련 아티클과 동영상 자료들을 열심히 준비 중입니다.
강좌 업로드가 조금 느릴 수 있으니 양해 부탁드립니다.
데브렉 사이트에서 이미 완성된 SQL Server 강좌들을 이용하실 수 있습니다.
아래 링크를 통해 빠르게 참조하실 수 있습니다.
http://www.devlec.com/?_pageVariable=strdown_v2&code=PT001TB1297&method=D
SQL Server 강좌 게시판
VisualAcademy Docs의 SQL Server 강좌만으로도 충분한 학습이 가능합니다.
게시판 형태의 추가 강좌를 원하시면 다음 링크를 참고하세요.
https://www.visualacademy.com/BoardIndex?BoardName=Database
SQL Server 유료 강의
VisualAcademy Docs에서는 시간이 허락하는 대로 강좌를 업데이트하고 있습니다.
완성된 동영상 강의를 원하신다면, 아래 링크를 통해 SQL Server 유료 강의를 이용하실 수 있습니다.
월 3만원대의 가격으로 데브렉 사이트의 모든 강좌를 포함한 SQL Server 강의를 즐기실 수 있습니다.
SQL Server 2022 소개
SQL Server 2022는 Microsoft가 개발한 데이터 관리 및 분석을 위한 통합 플랫폼입니다. 이 전문적인 데이터베이스 관리 시스템(DBMS)은 기업이나 개인이 데이터 저장, 검색, 및 분석을 수행할 수 있게 해줍니다. SQL Server 2022는 이전 버전들의 기능을 향상시키고, 혁신적인 기능들을 도입하여 사용자가 데이터를 더 효율적으로 관리하고 활용할 수 있게 도와줍니다.
주요 기능 및 업데이트 사항은 다음과 같습니다. 모든 기능 및 용어들이 처음 듣는 단어이기에 가볍게 읽고 넘어갑니다.
- 높은 가용성 및 성능: SQL Server 2022는 Intelligent Query Processing 및 Accelerated Database Recovery와 같은 기능을 통해 높은 성능과 가용성을 제공합니다.
- 고급 보안 기능: Always Encrypted, Data Classification, 및 Transparent Data Encryption과 같은 기능을 통해 기업의 중요한 데이터를 보호하고 규정 준수를 보장합니다.
- 큰 데이터 및 분석 통합: SQL Server 2022는 PolyBase를 통해 빅 데이터 및 분석 기능을 통합하여, 사용자가 Hadoop 및 NoSQL 데이터 소스와 상호 작용할 수 있게 해줍니다.
- 혁신적인 인공지능(AI) 및 머신러닝(ML) 기능: SQL Server 2022는 기계 학습 서비스 및 R, Python 프로그래밍 언어와의 통합을 통해 AI 및 ML을 지원합니다.
- 클라우드 확장성 및 하이브리드 지원: Azure Arc와 같은 기능을 통해 SQL Server 2022는 온-프레미스 환경과 클라우드 간에 하이브리드 시나리오를 지원하며, 데이터 및 애플리케이션을 유연하게 확장할 수 있게 해줍니다.
- 개발자 친화적인 통합: SQL Server 2022는 다양한 개발 언어 및 프레임워크와의 호환성을 강화하여, 개발자가 더 쉽게 애플리케이션을 개발하고 배포할 수 있게 합니다.
이 외에도 SQL Server 2022는 다양한 업데이트와 개선 사항을 포함하고 있어, 데이터베이스 관리와 분석 작업을 보다 효율적으로 수행할 수 있습니다.
SQL Server 버전
아래는 SQL Server 버전별 요약 표입니다.
| 버전 | 출시 연도 | 주요 변경 내용 |
|---|---|---|
| SQL Server 1.0 | 1989 | 초기 버전 |
| SQL Server 4.2 | 1992 | 기본적인 저장 프로시저 및 트리거를 지원 |
| SQL Server 6.0 | 1995 | OLAP 서비스, DTS(데이터 변환 서비스) 등 추가 |
| SQL Server 7.0 | 1998 | OLAP, OLTP의 통합, T-SQL에 일부 객체 지향적인 기능 추가 등 |
| SQL Server 2000 | 2000 | XML 데이터 처리, 데이터 마이닝(data mining) 등 추가 |
| SQL Server 2005 | 2005 | CLR 통합, XML 및 XQuery 지원, SSIS(SQL Server Integration Services) 등 추가 |
| SQL Server 2008 | 2008 | 새로운 데이터 타입, 개선된 공유된 데이터베이스, 인텔리센스 등 추가 |
| SQL Server 2008 R2 | 2010 | PowerPivot, StreamInsight 등 추가 |
| SQL Server 2012 | 2012 | Columnstore index, 상용화된 Data Quality Services 등 추가 |
| SQL Server 2014 | 2014 | In-Memory OLTP, Azure 연동 등 추가 |
| SQL Server 2016 | 2016 | Stretch Database, R Services 등 추가 |
| SQL Server 2017 | 2017 | Machine Learning Services, 그래프 데이터 처리 등 추가 |
| SQL Server 2019 | 2019 | Big Data Cluster, 데이터 가상화 등 추가 |
SQL Server 2022 설치 옵션
SQL Server 2022 설치 과정에서는 사용자의 요구 사항과 환경에 따라 다양한 옵션을 선택할 수 있습니다. 주요 설치 옵션은 다음과 같습니다.
Edition 선택: SQL Server 2022는 다양한 Edition을 제공하며, 각 Edition은 특정 기능 및 성능 요구 사항을 충족합니다. 주요 Edition에는 Enterprise, Standard, Developer, Express 등이 있습니다. 사용자의 요구에 맞는 Edition을 선택하세요.
인스턴스 구성: SQL Server 2022는 기본 인스턴스와 명명된 인스턴스를 지원합니다. 기본 인스턴스는 하나만 설치할 수 있고, 명명된 인스턴스는 여러 개 설치할 수 있습니다. 인스턴스에 적절한 이름을 지정하고 구성하세요.
서버 구성: 설치 과정에서 SQL Server 서비스 계정, 인증 모드, 관리자 계정 설정 등을 구성할 수 있습니다. 보안 및 운영 요구 사항을 고려하여 서버 구성을 선택하세요.
- 인증 모드: Windows 인증 모드 또는 SQL Server 및 Windows 인증 모드(혼합 모드) 중 하나를 선택할 수 있습니다.
- 서비스 계정: SQL Server 서비스들이 실행되는 계정을 설정하고, 필요한 권한을 부여하세요.
데이터베이스 엔진 구성: 데이터베이스 엔진에 대한 설정을 구성할 수 있습니다. 이에는 다음과 같은 옵션이 있습니다.
- 데이터 파일 및 로그 파일 저장 위치: 데이터베이스 파일들이 저장될 경로를 설정하세요.
- TempDB 설정: TempDB 데이터베이스의 파일 수, 초기 크기, 자동 증가 설정 등을 구성하세요.
기능 선택: SQL Server 2022는 다양한 기능을 제공합니다. 데이터베이스 엔진, 분석 서비스, 통합 서비스, 보고서 서비스 등 필요한 기능을 선택하여 설치하세요.
클라우드 통합: SQL Server 2022는 Azure와의 통합을 지원합니다. 필요에 따라 Azure Arc, Azure Synapse Analytics, Azure Machine Learning 등과의 연동을 설정하세요.
설치 옵션을 선택한 후, 설치를 진행하면 SQL Server 2022가 사용자의 요구 사항에 맞게 구성됩니다. 이러한 옵션들을 통해 SQL Server 2022를 최적화하여 사용할 수 있습니다.
SQL Server 2022 Developer 버전 설치 절차
SQL Server 2022 Developer 버전은 개발자를 위한 무료 버전으로, 비상업적 사용에 적합하며 모든 기능을 포함하고 있습니다. 설치 절차는 다음과 같습니다.
설치 파일 다운로드: SQL Server 2022 Developer 버전의 설치 파일을 다운로드합니다. 다운로드는 공식 Microsoft SQL Server 다운로드 페이지에서 진행할 수 있습니다. (https://www.microsoft.com/en-us/sql-server/sql-server-downloads)
설치 프로그램 실행: 다운로드한 설치 파일을 실행하여 SQL Server 2022 설치 프로그램을 시작합니다.
기본 설치 절차 진행: 설치 프로그램의 지침에 따라 다음 단계를 진행합니다.
- 기본 체크: 시스템 구성 체크 및 업데이트 확인을 수행합니다.
- 설치 유형 선택: 'New SQL Server stand-alone installation or add features to an existing installation' 옵션을 선택하여 새로운 SQL Server 설치를 진행합니다.
Edition 선택: 설치 프로그램이 제공하는 Edition 목록에서 'Developer'를 선택하고, 라이선스 약관에 동의한 후 계속 진행합니다.
기능 선택: 필요한 기능들을 선택합니다. 기본적으로 'Database Engine Services'를 선택하되, 분석 서비스, 통합 서비스, 보고서 서비스 등 추가 기능이 필요하다면 선택합니다.
인스턴스 구성: 기본 인스턴스 또는 명명된 인스턴스를 선택합니다. 기본 인스턴스를 선택하거나 새로운 명명된 인스턴스를 생성하실 수 있습니다.
서버 구성: 서비스 계정, 인증 모드, SQL Server 관리자 계정 등 서버 설정을 구성합니다.
- 인증 모드: 'Windows Authentication Mode' 또는 'Mixed Mode (SQL Server Authentication and Windows Authentication)' 중에서 선택합니다.
- 서비스 계정: 서비스들이 실행되는 계정을 설정하고, 필요한 권한을 부여합니다.
데이터베이스 엔진 구성: 데이터 파일 저장 위치, TempDB 설정 등 데이터베이스 엔진 관련 구성을 설정합니다.
설치 확인 및 시작: 선택한 구성을 검토한 후, 설치를 시작합니다. 설치가 완료되면, SQL Server 2022 Developer 버전이 설치된 상태가 됩니다.
설치가 완료된 후에는 SQL Server Management Studio(SSMS) 또는 Azure Data Studio와 같은 도구를 사용하여 SQL Server에 연결하고 데이터베이스를 관리할 수 있습니다. 필요하다면 이러한 도구들을 별도로 설치해야 합니다.
SQL Server Management Studio(SSMS) 설치 절차
설치 파일 다운로드: SQL Server Management Studio(SSMS) 설치 파일을 다운로드합니다. 다운로드는 공식 Microsoft SSMS 다운로드 페이지에서 진행할 수 있습니다. (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms)
설치 프로그램 실행: 다운로드한 설치 파일을 실행하여 SSMS 설치 프로그램을 시작합니다.
설치 절차 진행: 설치 프로그램의 지침에 따라 다음 단계를 진행합니다.
- 라이선스 동의: 라이선스 약관에 동의한 후 계속 진행합니다.
- 설치 위치 선택: SSMS 설치 경로를 선택하고, 설치 공간이 충분한지 확인합니다.
- 설치 시작: 설치를 시작하며, 설치 과정에서 필요한 구성 요소들이 자동으로 설치됩니다.
설치 완료 및 실행: 설치가 완료되면, SSMS를 실행하여 SQL Server 인스턴스에 연결하고 데이터베이스를 관리할 수 있습니다.
Azure Data Studio 설치 절차
설치 파일 다운로드: Azure Data Studio 설치 파일을 다운로드합니다. 다운로드는 공식 Microsoft Azure Data Studio 다운로드 페이지에서 진행할 수 있습니다. (https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio)
설치 프로그램 실행: 다운로드한 설치 파일을 실행하여 Azure Data Studio 설치 프로그램을 시작합니다.
설치 절차 진행: 설치 프로그램의 지침에 따라 다음 단계를 진행합니다.
- 라이선스 동의: 라이선스 약관에 동의한 후 계속 진행합니다.
- 설치 위치 선택: Azure Data Studio 설치 경로를 선택하고, 설치 공간이 충분한지 확인합니다.
- 설치 옵션 선택: 필요한 경우 추가 설치 옵션을 선택하고 진행합니다.
- 설치 시작: 설치를 시작하며, 설치 과정에서 필요한 구성 요소들이 자동으로 설치됩니다.
설치 완료 및 실행: 설치가 완료되면, Azure Data Studio를 실행하여 SQL Server 인스턴스에 연결하고 데이터베이스를 관리할 수 있습니다.
SQL Server Management Studio (SSMS)
개요
SQL Server Management Studio (SSMS)는 Microsoft SQL Server 데이터베이스를 관리하고 개발하는 데 사용되는 통합 환경입니다. 이 강좌에서는 SSMS의 주요 기능과 사용 방법에 대해 알아보겠습니다.
SSMS의 기능은 강의 전반에 걸쳐 진행하기에 아래 내용은 목록만 읽어보세요.
1. SSMS 실행 및 서버 연결
- 프로그램 목록에서 SQL Server Management Studio를 찾아 클릭하거나, 검색창에 "SQL Server Management Studio"를 입력하여 실행하세요.
- "Connect to Server" 대화 상자에서 서버 유형을 "Database Engine"으로 선택하고, 서버 이름을 입력하세요 (예: localhost, 127.0.0.1, 또는 인스턴스 이름).
- 올바른 인증 정보를 입력한 후 "Connect" 버튼을 클릭하여 서버에 연결하세요.
2. Object Explorer
- "Object Explorer"는 SSMS의 주요 구성 요소로, 데이터베이스 객체를 계층 구조로 표시합니다.
- 서버, 데이터베이스, 테이블, 뷰, 저장 프로시저, 함수, 사용자 등을 확인하고 관리할 수 있습니다.
- 오른쪽 마우스 버튼을 클릭하여 새로운 객체를 생성하거나 기존 객체를 수정하고 삭제할 수 있습니다.
3. 쿼리 편집기
- SSMS의 쿼리 편집기에서 SQL 쿼리를 작성하고 실행할 수 있습니다.
- 쿼리 편집기를 열려면, "Object Explorer"에서 데이터베이스를 선택한 다음 오른쪽 마우스 버튼을 클릭하고 "New Query"를 선택하세요.
- 쿼리를 작성한 후 F5 키를 누르거나 툴바의 "Execute" 버튼을 클릭하여 쿼리를 실행하세요.
4. 테이블 및 뷰 작업
- "Object Explorer"에서 테이블 및 뷰를 만들거나 수정하고 삭제할 수 있습니다.
- 새 테이블을 생성하려면, "Tables" 폴더에서 오른쪽 마우스 버튼을 클릭하고 "New Table"을 선택하세요.
- 새 뷰를 생성하려면, "Views" 폴더에서 오른쪽 마우스 버튼을 클릭하고 "New View"을 선택하세요.
5. 저장 프로시저 및 함수
- "Object Explorer"에서 저장 프로시저 및 사용자 정의 함수를 만들거나 수정하고 삭제할 수 있습니다.
- 새 저장 프로시저를 생성하려면, "Programmability" 폴더 아래의 "Stored Procedures" 폴더에서 오른쪽 마우스 버튼을 클릭하고 "New Stored Procedure"를 선택하세요.
- 새 사용자 정의 함수를 생성하려면, "Programmability" 폴더 아래의 "Functions" 폴더에서 오른쪽 마우스 버튼을 클릭하고 적절한 함수 유형을 선택하세요 (예: "New Scalar-valued Function" 또는 "New Table-valued Function").
6. 데이터베이스 백업 및 복원
- SSMS를 사용하여 데이터베이스의 백업 및 복원 작업을 수행할 수 있습니다.
- 데이터베이스를 백업하려면, "Object Explorer"에서 해당 데이터베이스를 선택한 다음 오른쪽 마우스 버튼을 클릭하고 "Tasks" > "Back Up..."을 선택하세요. 백업 옵션을 설정한 후 "OK" 버튼을 클릭하여 백업을 진행하세요.
- 데이터베이스를 복원하려면, "Object Explorer"에서 "Databases" 폴더를 선택한 다음 오른쪽 마우스 버튼을 클릭하고 "Restore Database..."을 선택하세요. 복원 옵션을 설정한 후 "OK" 버튼을 클릭하여 복원을 진행하세요.
7. 데이터베이스 성능 모니터링
- SSMS를 사용하여 데이터베이스 성능을 모니터링하고 문제를 진단할 수 있습니다.
- "Object Explorer"에서 서버를 선택한 다음 오른쪽 마우스 버튼을 클릭하고 "Activity Monitor"를 선택하세요. 여기에서 프로세스, 자원 사용률, 데이터 파일 I/O, 최근의 비용이 많은 쿼리 등을 확인할 수 있습니다.
- 실행 계획을 사용하여 쿼리 성능을 분석하고 최적화할 수 있습니다. 쿼리 편집기에서 쿼리를 선택한 다음 툴바의 "Display Estimated Execution Plan" 또는 "Include Actual Execution Plan" 버튼을 클릭하세요.
8. SQL Server 에이전트
- SQL Server 에이전트는 일정에 따라 작업을 자동으로 실행하거나 이벤트 기반 알림을 보내는 기능을 제공합니다.
- "Object Explorer"에서 "SQL Server Agent"를 확장하여 "Jobs", "Alerts", "Operators" 등을 관리할 수 있습니다.
- 새 작업, 경고 또는 운영자를 만들려면 해당 폴더에서 오른쪽 마우스 버튼을 클릭하고 "New Job", "New Alert" 또는 "New Operator"를 선택하세요. 필요한 설정을 완료한 후 "OK" 버튼을 클릭하여 객체를 생성하세요.
SSMS에서 데이터베이스 만들기
SQL Server Management Studio (SSMS)를 사용하여 VisualAcademy라는 이름으로 데이터베이스를 만드는 절차는 다음과 같습니다.
SQL Server Management Studio 실행: SSMS를 실행합니다. 프로그램 목록에서 SQL Server Management Studio를 찾아 클릭하거나, 검색창에 "SQL Server Management Studio"를 입력하여 실행하세요.
서버에 연결: SSMS가 실행되면 "Connect to Server" 대화 상자가 나타납니다. 서버 유형을 "Database Engine"으로 선택하고, 서버 이름을 입력하세요 (예: localhost, 127.0.0.1, 또는 인스턴스 이름). 올바른 인증 정보를 입력한 후 "Connect" 버튼을 클릭하여 서버에 연결하세요.
새 데이터베이스 생성: 연결된 서버의 "Object Explorer" 창에서 "Databases" 폴더를 찾아 마우스 오른쪽 버튼을 클릭하고, "New Database"를 선택하세요.
데이터베이스 이름 설정: "New Database" 대화 상자에서 "Database name" 영역에 "VisualAcademy"를 입력하세요.
데이터베이스 옵션 구성 (선택 사항): 필요한 경우, "Options" 페이지에서 데이터베이스 옵션을 구성할 수 있습니다. 예를 들어, 복구 모델, 자동 확장 설정, 사용자 정의 파일 그룹 등을 설정할 수 있습니다.
데이터베이스 생성 완료: "OK" 버튼을 클릭하여 VisualAcademy 데이터베이스를 생성하세요. 생성이 완료되면 "Object Explorer" 창의 "Databases" 폴더 아래에 VisualAcademy 데이터베이스가 나타납니다.
이제 VisualAcademy 데이터베이스를 사용하여 테이블, 뷰, 저장 프로시저 등을 생성하고 관리할 수 있습니다.
SSMS에서 VisualAcademy 데이터베이스의 속성 변경하기
이 아티클에서는 SQL Server Management Studio (SSMS)를 사용하여 생성한 VisualAcademy 데이터베이스의 속성을 변경하는 방법을 살펴봅니다.
1. 데이터베이스 속성 열기
데이터베이스 속성을 열려면 다음 단계를 따르세요:
- SSMS의 "Object Explorer"에서 VisualAcademy 데이터베이스를 찾습니다.
- 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고, "Properties"를 선택합니다.
2. 일반 옵션 변경
"Database Properties" 대화 상자에서 "General" 페이지를 선택하여 다음과 같은 옵션을 변경할 수 있습니다:
- Owner: 데이터베이스 소유자를 변경하려면 "Owner" 영역에서 새 소유자를 선택하거나 직접 입력하세요.
- Database Read-Only: 데이터베이스를 읽기 전용으로 설정하려면 "Database Read-Only" 체크박스를 선택하세요.
- Recovery model: 복구 모델을 변경하려면 "Recovery model" 드롭다운 목록에서 적절한 옵션을 선택하세요. (예: Simple, Full, Bulk-logged)
3. 파일 및 파일 그룹 옵션 변경
"Database Properties" 대화 상자에서 "Files" 및 "Filegroups" 페이지를 선택하여 데이터베이스 파일 및 파일 그룹 옵션을 변경할 수 있습니다.
Files 페이지에서 다음 옵션을 변경할 수 있습니다:
- Initial Size: 데이터베이스 파일의 초기 크기를 변경하려면 "Initial Size (MB)" 열에서 새 값으로 변경하세요.
- Autogrowth / Maxsize: 파일 자동 확장 옵션을 변경하려면 해당 파일의 "Autogrowth / Maxsize" 열에서 오른쪽 버튼으로 클릭하고 "..." 버튼을 선택한 다음, 새 창에서 옵션을 변경하세요.
Filegroups 페이지에서 새 파일 그룹을 추가하거나 기존 파일 그룹을 수정하거나 삭제할 수 있습니다.
4. 옵션 페이지 변경
"Database Properties" 대화 상자에서 "Options" 페이지를 선택하여 다음과 같은 옵션을 변경할 수 있습니다:
- Compatibility level: 데이터베이스 호환성 수준을 변경하려면 "Compatibility level" 드롭다운 목록에서 적절한 옵션을 선택하세요.
- Containment type: 컨테이너 타입을 변경하려면 "Containment type" 드롭다운 목록에서 적절한 옵션을 선택하세요 (예: None, Partial).
- Cursor Options, SET Options 등: 다양한 데이터베이스 옵션을 설정하려면 해당 옵션 영역에서 필요한 값을 선택하거나 입력하세요.
5. 변경 사항 저장
모든 변경 사항을 적용하려면 "Database Properties" 대화 상자의 하단에 있는 "OK" 버튼을 클릭하세요. 변경 사항이 적용되면 SSMS에서 변경된 데이터베이스 속성을 확인할 수 있습니다.
이제 SSMS에서 VisualAcademy 데이터베이스의 속성을 변경하는 방법에 대해 알게 되었습니다. 각 속성에 대해 시간을 들여 살펴보고 필요한 변경 사항을 적용해보세요.
샘플 데이터베이스 가져오기
SQL Server 및 데이터베이스 프로그래밍 연습을 위한 샘플 데이터베이스를 가져오는 강의는 다음 링크를 참고하세요.
SQL Server 데이터 형식
SQL Server에서 사용되는 주요 데이터 형식은 다음 표와 같습니다.
| 데이터 형식 | 설명 |
|---|---|
| int | 4바이트 정수 값, 범위: -2,147,483,648 ~ 2,147,483,647 |
| bigint | 8바이트 정수 값, 범위: -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 |
| smallint | 2바이트 정수 값, 범위: -32,768 ~ 32,767 |
| tinyint | 1바이트 정수 값, 범위: 0 ~ 255 |
| bit | 이진 값(0 또는 1) |
| float | 근사 숫자(부동 소수점) 데이터 형식 |
| real | 근사 숫자(부동 소수점) 데이터 형식의 하위 집합 |
| decimal | 고정 소수점 숫자 데이터 형식 |
| numeric | decimal과 동일한 데이터 형식 |
| money | 통화 데이터 형식, 범위: -922,337,203,685,477.5808 ~ 922,337,203,685,477.5807 |
| smallmoney | 통화 데이터 형식의 하위 집합, 범위: -214,748.3648 ~ 214,748.3647 |
| char | 고정 길이 문자 데이터 형식 |
| varchar | 가변 길이 문자 데이터 형식 |
| text | 대용량 문자 데이터 형식 (최대 2^31-1 자) |
| nchar | 고정 길이 유니코드 문자 데이터 형식 |
| nvarchar | 가변 길이 유니코드 문자 데이터 형식 |
| ntext | 대용량 유니코드 문자 데이터 형식 (최대 2^30-1 자) |
| binary | 고정 길이 이진 데이터 형식 |
| varbinary | 가변 길이 이진 데이터 형식 |
| image | 대용량 이진 데이터 형식 (최대 2^31-1 바이트) |
| date | 날짜 데이터 형식 (범위: 0001-01-01 ~ 9999-12-31) |
| time | 시간 데이터 형식 |
| datetime | 날짜 및 시간 데이터 형식 |
| smalldatetime | 날짜 및 시간 데이터 형식의 하위 집합 |
| datetimeoffset | 날짜 및 시간 데이터 형식에 표준 시간대 오프셋 추가 |
| timestamp | 데이터베이스에서 자동으로 생성되는 고유한 이진 숫자 |
| uniqueidentifier | 글로벌 고유 식별자(GUID) 데이터 형식 |
| sql_variant | 다양한 데이터 형식을 저장할 수 있는 데이터 형식 |
| xml | XML 데이터 형식 |
tempdb를 사용하여 집계 함수 연습하기
이 아티클에서는 SQL Server의 tempdb 데이터베이스를 사용하여 집계 함수 연습을 해보겠습니다. 이를 위해 샘플 테이블을 생성하고, 데이터를 입력한 다음 집계 함수를 적용해보겠습니다.
목차
- 샘플 테이블 생성
- 샘플 데이터 입력
- 전체 출력
- 국어점수 짝수 점수의 총점/평균/카운트
- 최댓값 및 최솟값
- Count 함수에서 NULL 값 처리
1. 샘플 테이블 생성
Create Table dbo.Score
(
Num Int Identity(1, 1) Primary Key, -- 일련번호
Kor Int Not Null, -- 국어점수
Eng Int Null -- 영어점수
)
Go
2. 샘플 데이터 입력
Insert Score Values(100, 90)
Insert Score Values(80, 75)
Insert Score Values(85, 90)
Insert Score Values(85, NULL)
3. 전체 출력
Select * From Score
Go
4. 국어점수 짝수 점수의 총점/평균/카운트
Select Sum(Kor) From Score Where Kor % 2 = 0 -- Sum() : 합계
Select Count(Kor) From Score Where Kor % 2 = 0 -- Count() : 건수
Select Avg(Kor) From Score Where Kor % 2 = 0 -- Avg() : 평균
5. 최댓값 및 최솟값
Select Max(Kor) From Score -- Max() : 최댓값
Select Min(Kor) From Score -- Min() : 최솟값
6. Count 함수에서 NULL 값 처리
Select Count(*) From Score -- 4
Select Count(Kor) From Score -- 4
Select Count(Eng) From Score -- 3 : NULL 값은 제외해서 카운트
이제 SQL Server의 tempdb 데이터베이스를 사용하여 집계 함수를 연습해보았습니다. 다양한 집계 함수를 사용하여 데이터를 분석하고 관리하는 데 익숙해지세요.
SQL Server에서 수학 관련 함수 연습하기
이 아티클에서는 SQL Server에서 사용할 수 있는 수학 관련 함수에 대해 설명하고 예제를 제공합니다.
목차
- 절대값
- PI
- 배승
- 반올림
- 절삭, 절하
- 랜덤
1. 절대값
Select Abs(-10) -- Abs() 함수 : 절대값
Go
2. PI
Select PI() -- 3.14 : 파이값
Go
3. 배승
Select Power(2, 10) -- 2의 10제곱근
Go
Select Power(2, 20)
GO
4. 반올림
Select Round(3.1415, 3) -- 반올림 : 3.1415를 소수점 3째자리까지(반올림했을 때)
Go
5. 절삭, 절하
Select Ceiling(-123.56) -- 가장 가까운 큰 정수값
Go
Select Ceiling(123.56) -- 가장 가까운 큰 정수값
Go
6. 랜덤
Select Rand() -- 랜덤값 : 임의 수 : 0~1까지 임의의 float 값을 반환
Go
이제 SQL Server에서 수학 관련 함수를 사용하여 데이터를 처리하는 데 익숙해지세요.
SQL Server에서 문자열 관련 함수 연습하기
이 아티클에서는 SQL Server에서 사용할 수 있는 문자열 관련 함수에 대해 설명하고 예제를 제공합니다.
목차
- 문자열의 길이
- 공백 제거
- 대소문자 변환
- 부분 문자열 추출
- 문자열 뒤집기
- 문자열 치환
- 문자열 반복
- 공백 출력
- 정수형을 문자열로 변환
- 문자열 검색
- 문자열 위치 검색
- 파일명과 확장자 분리
Declare @s VarChar(20)
Set @s = ' Abc Def Fed Cba '
-- 전체출력
Select @s
-- 문자열의 길이
Select Len(@s) -- 뒤에 오는 공백 제외
-- 공백 제거
Select LTrim(@s) -- 선행 공백 제거
Select RTrim(@s) -- 후행(오른쪽) 공백 제거
-- 모두 대문자로 표시
Select Upper(@s)
-- 모두 소문자로 표시
Select Lower(@s)
-- 왼쪽에서 6자 출력 : Abc D
Select Left(@s, 6)
-- 오른쪽에서 6자 출력 : d Cba
Select Right(@s, 6)
-- 거꾸로 출력
Select Reverse(@s)
-- 특정 문자열을 찾아서 있으면 변환(치환)
Select Replace(@s, 'Abc', '초콜릿')
-- 특정 문자열 반복
Select Replicate('안녕', 10)
-- 공백(Space)을 여러개 출력
Select '[' + Space(10) + ']'
-- 정수형을 문자열로 변환
Select Str(12345) + '6789'
-- 문자열 검색 : 어디부터 몇자 검색
Select SubString(@s, 6, 3)
-- 특정 문자열의 위치값 검색
Select CharIndex('Def', @s) -- 6번째 위치에 존재한다.
--퀴즈) 아래와 같이 출력하도록???
--파일명 : test
--확장자 : gif
Declare @dir VarChar(255)
Set @dir = 'c:\Home\test.gif'
Select '[1 ]' + @dir -- 전체 출력
Declare @fullname VarChar(255)
Set @fullname = @dir
Set @fullname = Reverse(@dir)
Select '[2 ]' + @fullname -- 거꾸로 출력 : fig.tset\emoH\:c
Set @fullname = Left(@fullname, CharIndex('\', @fullname) - 1)
Set @fullname = Reverse(@fullname) -- 전체파일명
Select '[3 ]' + @fullname
Declare @name VarChar(255)
Declare @ext VarChar(255)
Set @name = Left(@fullname, CharIndex('.', @fullname) - 1) -- .(점) 앞에까지 Left
Select '[4] ' + @name
Set @ext = Right(@fullname, Len(@fullname) - CharIndex('.', @fullname)) -- 전체길이 .까지길이
Select '[5] ' + @ext
이제 SQL Server에서 문자열 관련 함수를 사용하여 데이터를 처리하는 데 익숙해지세요.
SQL Server에서 형식 변환 관련 함수 연습하기
이 아티클에서는 SQL Server에서 사용할 수 있는 형식 변환 관련 함수에 대해 설명하고 예제를 제공합니다.
목차
- 문자열 결합 시 오류 발생
- 정수형을 문자열
예제
-- 1.47 -- 형식 변환 관련 함수
--[0] 에러 발생
--Select 1234 + '안녕'
--[1] 정수형 -> 문자열
Select '1234' + '안녕'
--[2] 정수형 -> 문자열
Select Str(1234) + '안녕'
--[3] Cast(As)
Select Cast(1234 As VarChar) + '안녕'
Select Cast(1234 As VarChar(10)) + '안녕'
--[4] Convert(,)
Select Convert(VarChar, 1234) + '안녕'
Select Convert(VarChar(10), 1234) + '안녕'
이제 SQL Server에서 형식 변환 관련 함수를 사용하여 데이터를 처리하는 데 익숙해지세요.
관계연산자
다음은 두 개의 변수를 선언하고, 관계 연산자를 사용하여 그 관계를 판단한 후, 그 결과를 출력하는 SQL Server 코드입니다.
DECLARE @first_num INT = 3
DECLARE @second_num INT = 5
DECLARE @greater BIT = IIF(@first_num > @second_num, 1, 0)
DECLARE @smaller BIT = IIF(@first_num < @second_num, 1, 0)
SELECT 'first_num = ' + CAST(@first_num AS VARCHAR)
+ ' second_num = ' + CAST(@second_num AS VARCHAR)
+ ' first_num > second_num = ' + CAST(@greater AS VARCHAR)
+ ' first_num < second_num = ' + CAST(@smaller AS VARCHAR)
위 코드에서는 먼저 @first_num과 @second_num 변수를 선언하고, 각각 3과 5의 값을 대입합니다. 그리고 관계 연산자를 사용하여 두 변수 간의 관계를 판단한 후, 그 결과를 BIT 자료형의 @greater와 @smaller 변수에 대입합니다. 마지막으로, SELECT 문을 사용하여 변수들의 값을 출력합니다.
위 코드를 실행하면 다음과 같은 결과가 출력됩니다.
first_num = 3 second_num = 5 first_num > second_num = 0 first_num < second_num = 1
결과에서는 @first_num 변수의 값이 @second_num 변수의 값보다 작으므로, @first_num < @second_num은 True가 됩니다. 반면, @first_num > @second_num은 False가 됩니다. 따라서 @greater 변수의 값은 False, @smaller 변수의 값은 True가 됩니다.
저장 프로시저 (Stored Procedure)
저장 프로시저는 데이터베이스 관리 시스템에서 데이터베이스 서버와 함께 저장되어 있는 연산을 말합니다. 일반적으로 SQL로 작성되며, 클라이언트는 저장 프로시저를 호출하여 처리 결과를 얻을 수 있습니다. 이를 통해 클라이언트와 서버 간에 주고받는 통신량이 감소되고 통신망의 부하가 경감되며, 처리 속도가 향상됩니다.
저장 프로시저의 장점
저장 프로시저는 다음과 같은 장점을 가지고 있습니다:
- 특정 절차를 하나의 이름으로 관리: 긴 SQL문을 짧은 저장 프로시저명으로 대체할 수 있습니다.
- 성능 향상: 저장 프로시저는 속도가 빠릅니다.
저장 프로시저 생성 및 실행 예시
다음 코드는 저장 프로시저를 생성하고 실행하는 예시입니다.
-- 저장 프로시저 생성
Create Procedure GetGreetingMessage
As
Begin
Select '사이트에 오신 걸 환영합니다.'
End
Go
-- 저장 프로시저 실행
Execute GetGreetingMessage
Go
위 코드를 실행하면 다음과 같은 결과가 출력됩니다.
사이트에 오신 걸 환영합니다.
Set NoCount On
프로시저 실행 시 Select 문 등의 실행 결과가 얼마나 많은 행에 영향을 주었는지 메시지를 출력하지 않도록 설정할 때 사용됩니다.
저장 프로시저 사용 이유
저장 프로시저를 사용하면 모듈화된 프로그래밍을 할 수 있고, 업무를 분배해서 작업할 수 있으며, 데이터베이스 보안을 좀 더 증가시킬 수 있습니다. 또한 실행 속도가 빠르고, 많은 양의 T-SQL을 한 줄의 저장 프로시저로 사용하기에 네트워크 트래픽을 줄일 수 있습니다.
저장 프로시저의 처리 순서
저장 프로시저(SP)는 속도가 빠른 이유를 알아봅시다. 저장 프로시저는 다른 쿼리문과 달리 처리하는 순서가 다르며, 컴파일돼서 처리됩니다. 아래 내용은 일반 SQL문과 SP를 처리하는 순서를 나타냅니다. SP는 처음 만들어놓고 처음 실행할 때는 일반 SQL문과 크게 속도차이가 없지만, 하루에 몇만명이 똑같은 일반 SQL문을 실행하면 매번 반복해서 처리하겠지만, 저장 프로시저는 단계가 일반 SQL문보다 적기 때문에 실행속도에서의 차이점을 보이는 것입니다.
일반 SQL 구문을 처리하는 순서
일반 SQL구문을 처음 실행할 때
- 구문 분석단계 - SQL구문의 문법 검사.
- 표준화 - DB의 개체들(테이블 등)에 대해서 검사.
- 보안 점검 - 해당하는 DB개체들에 대한 사용자의 사용 권한 검사.
- 최적화 - 최적의 성능을 내기위한 사항 적용.
- 컴파일
일반 SQL구문을 반복 실행할 때
- 처음 수행한 일반 SQL구문의 실행 계획이 캐싱되어 있는지 확인 후 이를 수행.
- 캐싱되어 있지 않을 경우 처음수행시의 5단계를 다시 수행함.
저장 프로시저를 처리하는 순서
처음에 저장 프로시저를 만들 때
- 구문 분석 단계 - 프로시져 생성 구문의 SQL구문들을 검사.
- 표준화 - DB의 개체들(테이블 등)에 대해서 검사.
- 보안 점검 - 프로시져에 대해서 생성이 가능한지 검사.
- 해당 프로시져의 구문과 생성 정보를 저장.
저장 프로시저를 처음 실행할 때
- 보안 점검 - 해당하는 DB개체들에 대한 사용자의 사용 권한을 검사.
- 최적화 - 최적의 성능을 내기위한 사항을 적용.
- 컴파일후 수행 계획을 생성후 캐시에 저장후 실행.
저장 프로시저를 반복해서 실행할 때
- 실행 계획이 캐싱이 된지 확인후 실행.
- 캐시에 실행 계획이 없을 경우 - 처음 실행 과정을 반복.
저장 프로시저의 종류
저장 프로시저는 크게 3가지 종류로 분류됩니다.
- 시스템 저장 프로시저 : 이미 sp_로 시작하는 시스템 저장 프로시저에 대해서는 몇가지 알아본적이 있다. 따로 설명하지는 않고 그때 그때 필요한 내용을 다루도록하겠다.
- 사용자 정의 저장 프로시저 : 이번장과 다음장에서 주로 다룰 내용이다. 저장 프로시저를 사용자가 직접 만들어서 사용할 수 있다.
- 확장 시스템 저장 프로시저 : 주로 xp_로 시작하는데 SQL서버로 외부 프로그램(프로그램 언어로 만들어진)을 실행하고자할 때 사용하는 방법이다.
인덱스(Index)
TIP
색인순차접근방식은 레코드에 순차적으로 접근하거나 색인을 통해 선택적으로 접근하는 방식 둘 모두를 제공하는 파일 관리 시스템이다. 이 방식은 레코드들을 블록 단위로 나누어 한 블록 안에서는 순차적으로 수록하고, 블록별로 시작 레코드의 위치를 기록한 색인 파일을 구성하여 키 값이 주어지면 이 색인을 기준으로 그 레코드를 빨리 찾아낼 수 있도록 한다.
1. 인덱스(Index)란?
인덱스는 SQL Server의 성능을 향상시키는 데 도움이 되는 객체로, 테이블의 검색 속도를 향상시키기 위해 데이터 정보를 저장합니다. 하나의 테이블에 한 개 이상의 인덱스를 만들 수 있으며, SELECT, INSERT, UPDATE 문을 사용할 때 WHERE 절을 사용하여 조건에 따른 결과 값을 얻고자 할 때 인덱스를 사용하면 더 빨리 처리됩니다. 하지만 인덱스는 물리적으로 저장되어 있기 때문에 생성에 시간이 걸리고 공간도 필요합니다. 따라서 인덱스를 무턱대고 만드는 것보다는 필요한 곳에 적절하게 사용하면 좋은 성능을 유지할 수 있습니다.
인덱스를 책의 목차 또는 찾아보기와 비교할 수 있습니다. 목차 또는 찾아보기를 만드는 이유는 원하는 정보를 빠르게 찾아가기 위한 도움의 역할을 하는 것입니다. SQL Server에서의 인덱스도 이와 동일한 역할을 합니다. 인덱스를 만들면 정보 검색을 효율적으로 빠르게 할 수 있지만, 그만큼의 공간과 시간이 필요합니다.
2. 인덱스의 특징
- 데이터베이스는 대량의 데이터를 보조기억장치에 저장해 두고 관리하기 때문에 데이터들을 보다 효율적으로 관리하고 빠르게 검색할 수 있도록 데이터의 위치정보와 관련된 추가적인 정보 저장이 필요합니다.
- 검색의 수를 줄이기 위하여 특정 속성이나 필드에 지정하는 기능으로 갱신 속도는 떨어질 수 있습니다.
3. 인덱스의 종류
- 클러스터드 인덱스 : 물리적인 필드의 순서와 인덱스의 순서가 동일한 인덱스 구조. 책의 차례
- 넌클러스터드 인덱스 : 인덱스에는 실제 데이터의 위치 정보를 가지는 인덱스 구조. 책의 찾아보기
- 유니크 인덱스 : 인덱스된 필드값이 중복되지 않는 인덱스 구조
- 복합 인덱스 : 여러 개의 필드를 바탕으로 생성된 인덱스 구조
4. 클러스터드 인덱스와 넌클러스터드 인덱스
인덱스는 클러스터드 인덱스와 넌클러스터드 인덱스와 넌클러스터드 인덱스로 구분됩니다. 인덱스를 만드는 목적이 테이블의 검색 속도를 높이기 때문에 이 두 가지 인덱스가 필요합니다. 클러스터드 인덱스는 책의 앞부분에 나오는 차례와 비슷하고, 넌클러스터드 인덱스는 책의 뒷부분에 나오는 찾아보기와 같은 개념입니다. 좀 더 자세히 살펴보면 클러스터 인덱스는 테이블의 물리적인 행 순서와 인덱스의 순서가 동일하고, INSERT/UPDATE/DELETE문을 실행할 때 레코드를 다시 정렬시킵니다. 넌클러스터 인덱스는 실제 데이터가 저장되어 있는 테이블 외에 인덱스 테이블을 새로 생성합니다. 그래서 INSERT/UPDATE/DELETE문을 실행할 때 매번 새로 정렬시키는 것이 아니라 데이터 테이블에 값을 넣어놓고 인덱스 테이블에는 그 위치값을 기록하는 방식을 사용합니다. 일반적으로 넌클러스터 인덱스가 클러스터 인덱스에 비해 검색 속도가 느리며 저장 공간도 많이 필요합니다. 그렇지만 위 2개의 인덱스 방식을 적절히 사용하면 최상의 서버 성능을 낼 수 있습니다.
5. 클러스터 인덱스와 넌클러스터 인덱스가 필요한 곳과 필요하지 않는 곳
- 클러스터 인덱스가 주로 필요한 필드
- SELECT문의 WHERE 절에서 범위 검색의 대상이 되는 필드
- ORDER BY 절에 의해 자주 사용되는 필드
- 넌클러스터 인덱스가 주로 필요한 필드
- SELECT문의 WHERE 절에서 포인트 검색(특정값 검색)의 대상이 되는 필드
- PRIMARY KEY 및 FOREIGN KEY 등의 제약 조건이 걸리는 필드
- JOIN을 자주 사용하는 필드
- 인덱스가 필요하지 않는 필드
- 검색에 사용되지 않는 필드
- 같은 값들이 많이 들어오는 필드 (예: 성별)
6. 인덱스 만들기 문법
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX 인덱스이름
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
온라인 설명서에 나오는 인덱스 만드는 구문을 옮겨 적어봤습니다. 유니크 인덱스를 만들 때는 UNIQUE, 클러스터드 인덱스를 만들 때는 CLUSTERED, 넌클리스터드 인덱스를 만들 때는 NONCLUSTERED를 지정하고 인덱스를 만들면 됩니다. 단, 하나의 테이블에는 하나의 클러스터 인덱스만이 생성될 수 있고 넌클러스터드 인덱스는 여러 개(249개) 생성될 수 있습니다.
7. 인덱스 삭제하기 문법
DROP INDEX 인덱스이름
예제1. 클러스터 인덱스와 넌클러스터 인덱스 만들기(index.sql)
--[0] 테스트용 임시테이블 작성
USE master
go
CREATE DATABASE TestDB -- DROP DATABASE TestDB
go
use TestDB
go
--DROP TABLE dbo.FrontBoard
CREATE TABLE dbo.FrontBoard -- 웹사이트 전광판 기능 구현할 테이블
(
ItemID int IDENTITY(1,1),--PRIMARY KEY 및 NOT NULL
Title varchar(150) NULL,
Url varchar(50) NULL,
Target varchar(10) NULL,
CreatedDate datetime default(getdate())
)
go
--[1] FrontBoard의 ItemID필드에 idxFrontBoard란 넌클러스터 인덱스 생성
CREATE INDEX idxFrontBoard
ON FrontBoard(ItemID)
go
--[2] sp_helpindex 시스템 저장 프로시저를 이용해서 인덱스 정보 확인
EXEC sp_helpindex FrontBoard
--[3] 인덱스 지우기
DROP INDEX FrontBoard.idxFrontBoard
go
--[4] Title필드에 유니크한 클러스터 인덱스 생성
CREATE UNIQUE CLUSTERED INDEX idxFrontBoard
ON FrontBoard(Title)
go
--[5] sp_helpindex 시스템 저장 프로시저를 이용해서 인덱스 정보 확인
EXEC sp_helpindex FrontBoard
--[6] 인덱스 지우기
DROP INDEX FrontBoard.idxFrontBoard --인덱스 지우기
go
[1]번 항목을 보면 인덱스를 생성할 때 기본값인 CREATE INDEX로 생성하는데, PRIMARY KEY를 지정하게 되면 기본값으로 클러스터 인덱스가 만들어집니다. [2]번은 sp_helpindex라는 시스템 저장프로시저를 사용해서 FrontBoard라는 테이블에 있는 인덱스의 정보를 볼 수 있습니다. 인덱스는 수정이 불가능합니다. 그래서 인덱스는 삭제하고 새로 만들어야 합니다. 인덱스를 삭제할 경우에는 DROP INDEX문을 사용하면 됩니다. [4]번은 UNIQUE와 CLUSTERED라는 키워드를 앞에 붙여서 유니크하고 클러스터된 인덱스를 만들 수 있습니다. 하지만 하나의 데이터베이스에 클러스터 인덱스는 하나만 만들 수 있습니다.
트랜잭션(Transaction) 및 트리거(Trigger)
소개
이번 장에서는 트랜잭션과 트리거를 다루겠습니다. 둘 다 관련성이 깊지는 않으나 함께 다뤄보도록 하겠습니다. 트랜잭션은 모든 T-SQL을 수행할 때 완전하게 수행하기 위함이고, 트리거는 테이블 제약조건과 같은 역할을 하는 테이블의 데이터가 수정될 때만 실행되는 특정한 유형의 저장 프로시저입니다.
트랜잭션(Transaction)이란?
트랜잭션은 이론적으로 하나의 논리적 기능을 수행하기 위한 작업의 단위를 말합니다. 트랜잭션을 말할 때 가장 많이 드는 실례가 있다면 역시 은행업무 얘기입니다. 만약에 여러분들이 은행에서 여러분들의 통장에서 다른 사람의 통장으로 100만원을 계좌이체를 하는데 여러분 통장에서는 제대로 빠져나갔는데 다른 사람의 통장에는 입금이 되질 않았다. 이런 경우 여러분의 통장에서 빠져나감과 동시에 다른 사람의 통장에 이체되기까지가 트랜잭션이라고 보면 이 두가지 행위가 모두 일어나면 트랜잭션이 성공한 것이고 둘 중 하나라도 일어나지 않으면 트랜잭션이 실패했기 때문에 처음으로 되돌려야 합니다. 이런걸 “데이터 일관성을 유지한다”라고 합니다. SQL에서 데이터베이스에 대한 일련의 처리를 하나로 모은 작업 단위로 관리할 수 있는데, 이 작업 단위가 바로 트랜잭션(Transaction)입니다.
데이터베이스 관련 연산의 가장 기본적인 단위
트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 작업의 단위로, 데이터베이스 관련 연산의 가장 기본적인 단위이다.
트랜잭션은 데이터베이스 시스템에서 복구 및 병행 수행 시 처리되는 작업의 논리적 단위이다.
트랜잭션은 일괄적으로 수행되어야 할 일련의 데이터베이스 연산들을 가리킵니다. 병행 제어 및 회복 작업에서 논리적 단위가 됩니다.
트랜잭션은 원자성, 일관성, 격리성, 영속성의 특성을 가집니다.
커밋(COMMIT)은 SQL 명령어로 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려주는 명령이다.
트랜잭션의 특징
트랜잭션의 특징은 ACID로 표현됩니다.
(1) Atomicity(원자성) : 트랜잭션으로 처리되는 모든 것이 한꺼번에 처리되어야 하며 하나라도 에러가 발생되면 한꺼번에 취소되어야 합니다. (2) Consistency(일관성) : 트랜잭션을 수행하기 전과 트랜잭션이 수행된 후의 시스템의 고정요소는 같아야 합니다. (3) Isolation(독립성) : 둘 이상의 트랜잭션이 동시에 수행중일 때는 하나의 트랜잭션이 수행중일 때는 다른 트랜잭션 연산이 끼어들면 안됩니다. (4) Durability(지속성) : 트랜잭션이 완료되면 영구적으로 지속됩니다.
트랜잭션 관련 키워드
- BEGIN TRAN : 트랜잭션을 시작합니다.
- COMMIT TRAN : 트랜잭션을 처리합니다.
- ROLLBACK TRAN : 트랜잭션을 취소합니다.
예제1. 트랜잭션 처리(트랜잭션.sql)
--[0] 실습용 테이블 생성
USE tempdb
GO
CREATE TABLE dbo.FrontBoard
(
ItemID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Title varchar(25) NULL,
Url varchar(50) NULL,
Target varchar(10) NULL CHECK(Target IN('_top','_blank','_parent','_self')),
CreatedDate datetime DEFAULT(GETDATE())
)
GO
--[1] 트랜잭션을 시작
BEGIN TRAN
--[2] 테이블에 값을 INSERT 후 SELECT
INSERT FrontBoard VALUES('첫번째입력','첫번째입력','_blank',getdate()) --입력됨.
SELECT * FROM FrontBoard
--[3] 롤백해본 후 SELECT
ROLLBACK TRAN
SELECT * FROM FrontBoard -- 입력된 값이 취소된다.
--[4] 다시 입력해본 후 COMMIT
BEGIN TRAN
INSERT FrontBoard VALUES('첫번째입력','첫번째입력','_blank',getdate()) --입력됨.
COMMIT TRAN
SELECT * FROM FrontBoard -- 제대로 입력됨
--[5] UPDATE/DELETE문에는 반드시 트랜잭션 만들자.
BEGIN TRAN
UPDATE ...
DELETE ...
SELECT ...
--COMMIT TRAN
ROLLBACK TRAN
롤백(Rollback)
롤백은 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 이전 상태로 복구하는 명령어입니다. 변경된 모든 내용을 취소하고 데이터베이스를 이전 상태로 되돌립니다.
트리거(Trigger)란?
트리거(Trigger)는 데이터베이스에서 하나의 구문에 값이 입력될 때 자동적으로 다른 테이블의 값을 변경하는 등의 업무를 할 수 있도록 하는 SQL Server의 데이터베이스 객체입니다.
트리거 만들기 문법(온라인 설명서 참조)
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
트리거 만드는 문법은 CREATE TRIGGER문을 사용하며 나머지 구문들은 저장 프로시저 생성 구문과 비슷합니다.
트리거 수정하기 문법
트리거를 수정하는 문법은 CREATE TRIGGER 대신에 ALTER TRIGGER문을 사용합니다.
트리거 삭제하기 문법
DROP TRIGGER 트리거이름
예제2. 트리거 생성/수정/삭제(트리거.sql)
--[0] 트리거 연습용 테이블 생성
--DROP TABLE dbo.[구매테이블]
CREATE TABLE dbo.[구매테이블]
(
CustomerID int,
ProductID int,
Quantity int
)
GO
--DROP TABLE dbo.[재고테이블]
CREATE TABLE dbo.[재고테이블]
(
ProductID int,
Quantity int
)
GO
--[1] 재고테이블에 1번, 2번 제품을 10개 넣어놓음(가상)
INSERT [재고테이블] VALUES(1,10)
INSERT [재고테이블] VALUES(2,10)
SELECT * FROM [재고테이블]
--[2] 구매가 한번이루어지면 자동으로 구매테이블에서 감소시키는 트리거 작성
CREATE TRIGGER [자동재고감소]
ON [구매테이블]
FOR INSERT
AS
UPDATE [재고테이블] SET Quantity = Quantity - 1
PRINT '재고테이블에서 1개 감소했습니다.'
--[3] 구매테이블에 INSERT문을 실행시켜본다.
INSERT [구매테이블] VALUES(1,1,1)
--[4] 트리거에의해 재고테이블의 모든 필드값이 자동으로 감소됨을 알 수 있다.
SELECT * FROM [재고테이블]
--[5] 트리거를 수정해보자: 구매개수만큼 줄이도록 수정
ALTER TRIGGER [자동재고감소]
ON [구매테이블]
FOR INSERT
AS
DECLARE @ProductID int, @Quantity int
SET @ProductID = 0
SET @Quantity = 0
SELECT @ProductID = ProductID, @Quantity = Quantity
FROM INSERTED --INSERTED에 의해서 입력된 데이터 받아옴
UPDATE [재고테이블] SET Quantity = Quantity - @Quantity
WHERE ProductID = @ProductID
GO
--[6] 1번 제품만을 1개 감소
INSERT [구매테이블] VALUES(1,1,1)
SELECT * FROM [재고테이블]
--[7] 2번 제품만을 3개 감소
INSERT [구매테이블] VALUES(1,2,3)
SELECT * FROM [재고테이블]
--[8] 트리거 삭제
DROP TRIGGER [자동재고감소]
[5]번 항목을 실행한 후에 [6]번과 같이 [구매테이블]에 1번 제품을 1개 구매하는 가상 입력을 해보면, [재고테이블]에서는 당연히 1번 제품이 1개 감소됨을 알 수 있습니다. 마찬가지로 [7]번 항목에서는 [구매테이블]에 2번 제품이 3개 구매된다면, [재고테이블]에서 2번 제품이 3개가 감소함을 알 수 있습니다.
마지막으로 [8]번과 같이 DROP TRIGGER문으로 트리거를 삭제할 수 있습니다.
SQL Server에서 임시 테이블 활용하기
임시 테이블은 SQL Server에서 효율적으로 데이터를 처리하고 분석하는 데 도움이 되는 훌륭한 도구입니다. 이 글에서는 임시 테이블의 개념과 사용 방법을 설명하고, 임시 테이블을 사용하여 데이터 처리 작업을 개선하는 방법을 살펴봅니다.
- 임시 테이블의 개요
임시 테이블은 일시적으로 사용되는 테이블로, 주로 중간 결과를 저장하거나 데이터 처리 작업을 단순화하기 위해 사용됩니다. 임시 테이블은 두 가지 유형이 있습니다.
1.1. 로컬 임시 테이블: 테이블 이름 앞에 '#' 기호가 붙어있으며, 해당 테이블을 생성한 세션에서만 사용할 수 있습니다. 세션이 종료되면 테이블은 자동으로 삭제됩니다.
1.2. 전역 임시 테이블: 테이블 이름 앞에 '##' 기호가 붙어있으며, 모든 세션에서 사용할 수 있습니다. 마지막 연결이 종료되면 테이블은 자동으로 삭제됩니다.
- 임시 테이블 생성 및 사용 임시 테이블을 만들고 사용하는 방법은 다음과 같습니다.
2.1. 임시 테이블 생성: CREATE TABLE 문을 사용하여 임시 테이블을 만듭니다. 로컬 임시 테이블의 경우 테이블 이름 앞에 '#' 기호를 붙이고, 전역 임시 테이블의 경우 '##' 기호를 붙입니다.
예시:
-- 로컬 임시 테이블 생성
CREATE TABLE #LocalTempTable (ID INT, Name NVARCHAR(50))
-- 전역 임시 테이블 생성
CREATE TABLE ##GlobalTempTable (ID INT, Name NVARCHAR(50))
2.2. 데이터 삽입 및 조회: 임시 테이블에 데이터를 삽입하거나 조회하기 위해 일반적인 INSERT, UPDATE, DELETE, SELECT 문을 사용합니다.
예시:
-- 데이터 삽입
INSERT INTO #LocalTempTable (ID, Name) VALUES (1, '홍길동')
-- 데이터 조회
SELECT * FROM #LocalTempTable
- 임시 테이블의 활용
임시 테이블을 사용하면 다음과 같은 이점이 있습니다.
3.1. 쿼리 성능 향상: 데이터 처리 작업의 중간 결과를 임시 테이블에 저장하여 전체 작업의 성능을 향상시킬 수 있습니다. 예를 들어, 복잡한 조인이나 집계 함수를 사용하는 쿼리에서는 중간 결과를 임시 테이블에 저장한 후 이를 기반으로 추가 작업을 수행할 수 있습니다.
3.2. 코드 가독성 및 유지 관리: 임시 테이블을 사용하면 긴 쿼리를 여러 단계로 분할하여 코드의 가독성과 유지 관리성을 높일 수 있습니다.
3.3. 데이터베이스 객체 간의 상호 작용: 저장 프로시저, 사용자 정의 함수, 트리거 등 다양한 데이터베이스 객체에서 임시 테이블을 사용할 수 있습니다. 이를 통해 데이터베이스 객체 간의 상호 작용을 단순화하고 효율성을 높일 수 있습니다.
- 임시 테이블 사용 시 주의사항
임시 테이블을 사용할 때 다음과 같은 주의사항을 염두에 두어야 합니다.
4.1. 스코프: 로컬 임시 테이블은 해당 테이블을 생성한 세션에서만 사용할 수 있으며, 세션이 종료되면 자동으로 삭제됩니다. 전역 임시 테이블은 모든 세션에서 사용할 수 있지만, 마지막 연결이 종료되면 삭제됩니다.
4.2. 동시성: 동시에 여러 사용자가 동일한 이름의 로컬 임시 테이블을 만들 수 있습니다. SQL Server는 테이블 끝에 임의의 숫자를 추가하여 서로 다른 연결에 대해 동일한 이름을 가진 테이블을 구별합니다.
4.3. 임시 테이블 과도한 사용: 임시 테이블을 과도하게 사용하면 TempDB의 공간을 많이 차지할 수 있으며, 이로 인해 전체 시스템의 성능에 영향을 줄 수 있습니다. 따라서 임시 테이블 사용 시 필요한 경우에만 사용하고, 사용 후에는 명시적으로 DROP TABLE 문을 사용하여 임시 테이블을 삭제하는 것이 좋습니다.
- 전역 임시 테이블 사용
전역 임시 테이블은 로컬 임시 테이블과 달리 여러 세션에서 사용할 수 있습니다. 전역 임시 테이블을 생성하려면 테이블 이름 앞에 '##' 기호를 붙여야 합니다. 전역 임시 테이블은 마지막 연결이 종료될 때 자동으로 삭제됩니다.
-- Create Global temporary table
Create Table ##myGlobalTable (id Int, Name nvarchar(20))
--Insert data into Temporary Tables
Insert into ##myGlobalTable Values (1,'Saurabh');
Insert into ##myGlobalTable Values (2,'Darshan');
Insert into ##myGlobalTable Values (3,'Smiten');
-- Select Data from the Temporary Tables
Select * from ##myGlobalTable
위의 쿼리는 전역 임시 테이블을 생성하고 데이터를 삽입한 후, 전역 임시 테이블에서 데이터를 선택합니다.
- 테이블 변수 사용
테이블 변수는 임시 테이블과 유사한 기능을 제공하지만, 일부 사용 사례에서 성능이 더 좋습니다. 테이블 변수는 DECLARE 문을 사용하여 선언되며, 일반적으로 사용되는 데이터 처리 작업에 적합합니다.
-- Declare table variable
DECLARE @myTableVariable TABLE (id INT, Name NVARCHAR(20))
-- Insert data into table variable
INSERT INTO @myTableVariable VALUES (1, 'Saurabh')
INSERT INTO @myTableVariable VALUES (2, 'Darshan')
INSERT INTO @myTableVariable VALUES (3, 'Smiten')
-- Select data from table variable
SELECT * FROM @myTableVariable
위의 쿼리는 테이블 변수를 선언하고 데이터를 삽입한 후, 테이블 변수에서 데이터를 선택합니다.
- 임시 테이블과 테이블 변수 비교
임시 테이블과 테이블 변수는 모두 일시적인 데이터 저장소로 사용되지만, 몇 가지 중요한 차이점이 있습니다.
7.1. 스코프: 임시 테이블은 세션 전체에서 사용할 수 있으며, 로컬 임시 테이블은 해당 세션에서만 사용할 수 있습니다. 테이블 변수는 선언된 배치 또는 저장 프로시저에서만 사용할 수 있습니다.
7.2. 트랜잭션 로그: 임시 테이블은 TempDB에서 트랜잭션 로그를 기록하므로 롤백이 가능합니다.
- 인덱스 사용
임시 테이블에서 인덱스를 사용하여 쿼리 성능을 향상시킬 수 있습니다. 테이블 변수에는 기본 키 또는 고유한 제약 조건을 사용하여 인덱스를 생성할 수 있지만, 임시 테이블에는 명시적으로 인덱스를 생성할 수 있습니다.
예를 들어, 다음 쿼리에서는 로컬 임시 테이블에 인덱스를 추가하고 사용합니다.
-- Create Local temporary table with index
CREATE TABLE #myTempTableWithIndex (
id INT,
Name NVARCHAR(20)
)
-- Insert data into Temporary Table
INSERT INTO #myTempTableWithIndex VALUES (1, 'Saurabh')
INSERT INTO #myTempTableWithIndex VALUES (2, 'Darshan')
INSERT INTO #myTempTableWithIndex VALUES (3, 'Smiten')
-- Create an index on the temporary table
CREATE NONCLUSTERED INDEX ix_myTempTableWithIndex ON #myTempTableWithIndex (id)
-- Select data from the Temporary Table using the index
SELECT * FROM #myTempTableWithIndex WHERE id = 2
위의 쿼리에서 로컬 임시 테이블에 데이터를 삽입한 후, 인덱스를 생성하고 인덱스를 사용하여 데이터를 선택합니다.
- CTE(Common Table Expression) 사용
CTE는 일시적인 결과 집합을 정의하는 데 사용되는 T-SQL 구문입니다. CTE는 재귀 쿼리를 작성할 수 있으며, 복잡한 쿼리를 여러 단계로 분해하여 가독성과 유지 관리성을 높이는 데 도움이 됩니다. CTE는 임시 테이블이나 테이블 변수와 비슷한 목적을 제공하지만, 더 간결한 구문을 사용합니다.
예를 들어, 다음 쿼리에서는 CTE를 사용하여 데이터를 선택합니다.
-- Define a CTE
WITH myCTE (id, Name) AS (
SELECT 1, 'Saurabh'
UNION ALL
SELECT 2, 'Darshan'
UNION ALL
SELECT 3, 'Smiten'
)
-- Select data from the CTE
SELECT * FROM myCTE WHERE id = 2
위의 쿼리에서 CTE를 정의하고 데이터를 선택합니다. CTE는 선언 즉시 사용되어야 하며, 세션 전체에서 사용할 수 없습니다.
웹사이트 회원별 포인트 관리
이번 아티클에서는 웹사이트 회원별 포인트 관리 시스템을 구현하는 방법을 설명합니다. 이 시스템은 두 개의 테이블을 사용하여 각 회원의 종합 포인트와 관련 로그를 관리합니다.
-- 웹사이트의 회원별 포인트 관리
-- 종합 포인트
Create Table dbo.Points
(
Id Int Identity(1, 1) Not Null Primary Key, -- 일련번호
UserId Int Not Null, -- 사용자ID
Username NVarChar(25) Null, -- 사용자이름
TotalPoint Int Default(0) -- 종합포인트
)
Go
-- 종합 포인트 로그
Create Table dbo.PointLogs
(
Id Int Identity(1, 1) Not Null Primary Key, -- 일련번호
UserId Int Not Null, -- 사용자ID
Username NVarChar(25) Null, -- 사용자이름
NowPoint Int Default(1), -- 종합포인트
Created DatetimeOffset(7) Default(GetDate()) -- 등록일시
)
Go
테이블 설명:
- Points (종합 포인트) 테이블:
- 사용자 ID별로 종합 포인트를 저장합니다.
- 필드: 일련번호, 사용자 ID, 사용자 이름, 종합 포인트
- PointLogs (종합 포인트 로그) 테이블:
- 각 사용자의 포인트 증가 또는 감소에 대한 로그를 기록합니다.
- 필드: 일련번호, 사용자 ID, 사용자 이름, 종합 포인트, 등록 일시
예제 작업:
회원 추가 및 초기 포인트 설정
회원을 추가할 때 Points 테이블에도 해당 회원에 대한 레코드를 추가해야 합니다. 회원의 초기 포인트는 0으로 설정됩니다.
INSERT INTO dbo.Points (UserId, Username)
VALUES (1, 'John Doe')
회원 포인트 업데이트 및 로그 생성
회원의 포인트를 업데이트하려면 Points 테이블의 해당 회원 레코드의 TotalPoint 값을 변경하고, PointLogs 테이블에 변경 내용을 기록해야 합니다.
-- 포인트 증가
DECLARE @UserId INT = 1
DECLARE @Username NVARCHAR(25) = 'John Doe'
DECLARE @NewPoints INT = 10
UPDATE dbo.Points
SET TotalPoint = TotalPoint + @NewPoints
WHERE UserId = @UserId
INSERT INTO dbo.PointLogs (UserId, Username, NowPoint)
VALUES (@UserId, @Username, @NewPoints)
회원별 포인트 조회
각 회원의 현재 포인트를 조회하려면 Points 테이블에서 해당 회원의 레코드를 선택하면 됩니다.
SELECT UserId, Username, TotalPoint
FROM dbo.Points
WHERE UserId = 1
회원별 포인트 로그 조회
회원별 포인트 변경 내역을 조회하려면 PointLogs 테이블에서 해당 회원의 로그를 선택하면 됩니다.
SELECT UserId, Username, NowPoint, Created
FROM dbo.PointLogs
WHERE UserId = 1
ORDER BY Created DESC
이렇게 웹사이트의 회원별 포인트 관리를 위한 시스템을 구축할 수 있습니다.
SQL Server 전용 쿼리문
반별 학생 수 관리를 위한 SQL Server 아티클
이번 아티클에서는 학교에서 각 학년의 반별 학생 수를 관리하는 방법을 설명합니다. 이 시스템은 School 테이블을 사용하여 각 학년과 반에 대한 학생 수를 저장하고 관리합니다.
테이블 설명:
School 테이블:
각 학년과 반에 대한 학생 수를 저장합니다.
필드: 학년, 반, 학생수
예제 작업:
학년별 및 반별 학생 수 조회
각 학년별 및 반별 학생 수를 조회하려면 School 테이블에서 해당 학년과 반의 레코드를 선택하면 됩니다.
SELECT 학년, 반, 학생수
FROM School
학년별 학생 수 집계
각 학년별 학생 수를 집계하려면 PIVOT 문을 사용하여 반별 학생 수를 학년별로 집계할 수 있습니다.
SELECT 학년, [1반], [2반], [3반], [4반]
FROM School
PIVOT (
SUM(학생수) FOR 반 IN ([1반], [2반], [3반], [4반])
) AS PVT
피봇 테이블을 원래 형태로 변환
PIVOT 문으로 변환된 테이블을 원래 형태로 되돌리려면 UNPIVOT 문을 사용할 수 있습니다.
SELECT 학년, 반, 학생수
FROM (
SELECT 학년, [1반], [2반], [3반], [4반]
FROM School
PIVOT (
SUM(학생수) FOR 반 IN ([1반], [2반], [3반], [4반])
) AS PVT
) AS PVT2
UNPIVOT (
학생수 FOR 반 IN ([1반], [2반], [3반], [4반])
) AS UnPvt
학생 수에 따른 순위 매기기
학생 수를 기준으로 순위를 매길 때 사용할 수 있는 다양한 함수들이 있습니다. 이번 섹션에서는 RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE() 함수의 사용법을 설명합니다.
-- RANK(): 같은 학생 수를 가진 경우 동일한 순위를 부여하고, 다음 순위는 건너뛴다.
SELECT *, RANK() OVER (ORDER BY 학생수 DESC) AS 순위
FROM School
-- DENSE_RANK(): 같은 학생 수를 가진 경우 동일한 순위를 부여하고, 다음 순위는 건너뛰지 않는다.
SELECT *, DENSE_RANK() OVER (ORDER BY 학생수 DESC) AS 순위
FROM School
-- ROW_NUMBER(): 각 행에 고유한 순위를 부여한다. 같은 학생 수라도 서로 다른 순위를 갖는다.
SELECT *, ROW_NUMBER() OVER (ORDER BY 학생수 DESC) AS 순위
FROM School
-- NTILE(n): 결과를 n 개의 그룹으로 분할하고, 각 그룹에 순서대로 1부터 n까지의 순위를 부여한다.
SELECT *, NTILE(5) OVER (ORDER BY 학생수 DESC) AS 순위
FROM School
각 학년별 순위를 매기려면 PARTITION BY 절을 사용하여 학년별로 결과를 분할할 수 있습니다.
-- RANK(): 학년별로 같은 학생 수를 가진 경우 동일한 순위를 부여하고, 다음 순위는 건너뛴다.
SELECT *, RANK() OVER (PARTITION BY 학년 ORDER BY 학생수 DESC) AS 순위
FROM School
-- DENSE_RANK(): 학년별로 같은 학생 수를 가진 경우 동일한 순위를 부여하고, 다음 순위는 건너뛰지 않는다.
SELECT *, DENSE_RANK() OVER (PARTITION BY 학년 ORDER BY 학생수 DESC) AS 순위
FROM School
-- ROW_NUMBER(): 학년별로 각 행에 고유한 순위를 부여한다. 같은 학생 수라도 서로 다른 순위를 갖는다.
SELECT *, ROW_NUMBER() OVER (PARTITION BY 학년 ORDER BY 학생수 DESC) AS 순위
FROM School
-- NTILE(n): 학년별로 결과를 n 개의 그룹으로 분할하고, 각 그룹에 순서대로 1부터 n까지의 순위를 부여한다.
NTILE(n) 함수는 결과를 학년별로 n 개의 동일한 크기의 그룹으로 나눈 다음, 각 그룹에 1부터 n까지의 순위를 부여합니다. 예를 들어, NTILE(4) 함수를 사용하면 결과가 4개의 그룹으로 나뉘고, 각 그룹의 학생 수에 따라 1에서 4까지의 순위가 부여됩니다.
-- NTILE(n): 학년별로 결과를 n 개의 그룹으로 분할하고, 각 그룹에 순서대로 1부터 n까지의 순위를 부여한다.
SELECT *, NTILE(4) OVER (PARTITION BY 학년 ORDER BY 학생수 DESC) AS 순위
FROM School
이렇게 NTILE(n) 함수를 사용하면, 학년별로 학생 수에 따라 그룹화된 순위를 쉽게 계산할 수 있습니다. 이 함수는 학급별 성적 분포나 등급을 나눌 때 유용하게 사용할 수 있습니다.
SQL Server 정규화
이 아티클에서는 SQL Server에서 데이터베이스를 설계할 때 중요한 개념인 정규화에 대해 설명합니다.
목차
- 정규화 개요
- 정규화의 목적
1. 정규화 개요
정규화는 데이터베이스 설계 과정에서 데이터 속성의 중복을 최소화하여 일치성을 보장하며 데이터 모델을 단순하게 구성하는 과정입니다. 개체 내 존재하는 함수적 종속 관계를 이용하여 데이터베이스 구조를 안정화시키는 작업이며, 이를 통해 데이터베이스의 성능과 관리를 향상시킬 수 있습니다.
2. 정규화의 목적
정규화는 다음과 같은 목적으로 수행됩니다:
- 자료 저장 공간의 최소화 및 데이터 구조의 안정성 최대화: 중복 데이터를 제거함으로써 저장 공간을 절약하고, 데이터 구조의 안정성을 향상시킬 수 있습니다.
- 데이터베이스 내부 자료의 무결성 유지 극대화: 정규화를 통해 데이터베이스 내 데이터의 일관성과 무결성을 유지하고, 데이터 간의 관계를 명확하게 정의할 수 있습니다.
- 이상 현상 최소화: 데이터베이스 사용자가 의도하지 않은 삽입, 삭제, 갱신으로 인한 이상 현상이 발생하는 것을 최소화하여 데이터베이스의 정확성을 보장합니다.
정규화를 이해하고 적용하면 데이터베이스 설계와 관리에 있어 효율성과 성능을 크게 향상시킬 수 있습니다. 따라서 데이터베이스를 다루는 데 필수적인 기술 중 하나로 꼽히며, 효과적인 데이터베이스 관리를 위해 꼭 알아두어야 할 개념입니다.
