Đại cương SQL Server

HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU 1. Dữ liệu và cơ sở dữ liệu: - Dữ liệu có nghĩa là thông tin và nó là thành phần quan trọng trong bất kỳ lĩnh vực, công việc nào. Trong công việc thường ngày bao gồm cả việc sử dụng đã có và tạo ra những dữ liệu mới. Khi dữ liệu được tạo ra và được phân tích thì chúng trở thành thông tin. Thông tin giúp ta tiên định được các sự kiện. - CSDL là một tập hợp dữ liệu được tổ chức sao cho nội dung của nó có thể dễ dàng truy cập, quản lý và cập nhật. 2. Quản trị dữ liệu: chỉ việc quản lý lượng lớn thông tin, bao gồm cả việc lưu trữ thông tin và cơ chế thao tác thông tin trên các thông tin đó. Ngoài ra, hệ thống phải đảm bảo tính an toàn thông tin được lưu trữ trong nhiều tình huống khác nhau. Có 2 phương pháp quản trị dữ liệu khác nhau: Hệ thống dựa trên tập tin Hệ thống cơ sở dữ liệu + Hệ thống này lưu trữ dữ liệu trong các tập tin riêng biệt. Trong đó, một nhóm tập tin được lưu trên một máy tính và được truy cập bằng các thao tác máy tính. Tập tin lưu dữ liệu được gọi là bảng, các dòng được gọi là các bản ghi, các cột gọi là các trường. + Nhược điểm của hệ thống này: những chương trình khác nhau trong cùng một ứng dụng tương tác với các file dữ liệu khác nhau. Nó không tồn tại bất kỳ một hệ thống nào ép buộc theo một chuẩn nào đó trong việc lưu trữ và cấu trúc những file dữ liệu. ● Dư thừa và mâu thuẫn dữ liệu. ● Khó khăn khi phải truy xuất bất ngờ. ● Cô lập dữ liệu. ● Khó khăn trong việc xử lý và truy cập đồng thời bất thường. ● Vấn đề bảo mật và tính toàn vẹn dữ liệu phải lập trình trong từng chương trình. + Được phát triển vào cuối những năm 1960 để giải quyết những vấn đề cơ bản trong các ứng dụng có dữ liệu lớn và khắc phục được những nhược điểm của hệ thống quản lý theo file. + CSDL được sử dụng để lưu trữ dữ liệu có hệ thống và có tổ chức. CSDL giúp quản lý dữ liệu nhanh chóng và dễ dàng. Dữ liệu lưu trữ có thể được truy xuất ngay cả khi lưu trữ trên những file đơn giản và thời gian lưu trữ lâu hơn. + Ưu điểm của hệ thống này là quản lý dữ liệu tập trung. ● Giảm dư thừa dữ liệu. ● Giảm mâu thuẫn dữ liệu. ● Dữ liệu lưu trữ có thể được chia sẻ. ● Các chuẩn có thể được thiết lập và duy trì. ● Toàn vẹn dữ liệu được duy trì. ● Bảo mật dữ liệu có thể được cài đặt. 3. Hệ quản trị cơ sở dữ liệu (DBMS – DataBase Management System): - Khái niệm: Một hệ quản trị CSDL có thể được định nghĩa là một tập các bản ghi có liên quan với nhau và tập hợp các chương trình cho phép cập nhật và thao tác trên các bản ghi đó. Hệ quản trị CSDL tập trung, CSDL được lưu trũ tập trung tại một nơi, mọi người có thể truy cập đến CSDL tập trung từ máy tính của họ. CSDL là tập hợp các dữ liệu có liên quan với nhau và hệ quản trị CSDL là một tập hợp các chương trình được thêm vào để thêm vào và sửa đổi dữ liệu đó. Do đó, hệ quản trị CSDL là phần mềm cho phép định nghĩa, xây dựng và bảo trì CSDL. Hệ quản trị CSDL cung cấp một môi trường thuận tiện và hiệu quả để khi có một lượng lớn dữ liệu và giao dịch được xử lý. Các loại hệ quản trị CSDL được sử dụng từ hệ thống nhỏ chạy trên máy tính cá nhân cho đến các hệ thống lớn chạy trên các máy chủ lớn. Các hệ quản trị CSDL khác nhau hỗ trợ các câu lệnh truy vấn khác nhau, mặc dù có một ngôn ngữ truy vấn chuẩn được gọi là ngôn ngữ vấn tin có cấu trúc (SQL). Hiện nay đang ở thế hệ thứ tư. Không cần phải sử dụng hệ quản trị CSDL phổ biến để thiết lập một CSDL. Người viết có thể tự viết các chương trình để tạo ra và quản lý CSDL của họ, nó sẽ hiệu quả nếu họ tạo ra phần mềm quản lý CSDL theo mục đích riêng. Môi trường hệ thống CSDL đơn giản được mô tả ở bên. - Lợi ích của hệ quản trị CSDL + Lưu trữ dữ liệu + Định nghĩa dữ liệu + Thao tác trên dữ liệu + Bảo mật và toàn vẹn dữ liệu + Truy vấn đồng thời và phục hồi dữ liệu + Hiệu năng + Điều khiển đa truy cập + Ngôn ngữ truy xuất CSDL và giao diện lập trình ứng dụng API - Các mô hình CSDL: một mô hình dữ liệu mô tả kho chứa dữ liệu và xử lý việc lưu trữ và truy xuất dữ liệu từ kho đó. Người phân tích và thiết kế các mô hình dữ liệu phải dựa vào quá trình phát triển của CSDL. Mỗi mô hình được phát triển dựa vào mô hình trước đó. + Mô hình dữ liệu file phằng: chỉ chứa một bảng hay một file. Nó được dùng cho những CSDL đơn giản mà không thể quản lý lượng lớn dữ liệu. Nó có thể tạo ra dư thừa dữ liệu được mô tả nhiều lần. + Mô hình phân cấp: các bản ghi liên kết với nhau thông qua các cấp bậc giống như cấu trúc cây. Trong đó các quan hệ được gọi là quan hệ cha-con. Một bản ghi cha có thể có một vài bản ghi con, nhưng một bản ghi con chỉ có một bản ghi cha. Để tìm dữ liệu trong đó, người dùng cần phải biết được cấu trúc của cây. Ưu điểm của mô hình này: ● Dữ liệu được lưu trữ trên CSDL, vì vậy dữ liệu được chia sẻ dễ dàng và được bảo mật bởi hệ quản trị CSDL. ● Độc lập giữ liệu được hệ quản trị CSDL giúp giúp giảm được chi phí bảo trì chương trình. + Mô hình mạng: tương tự như mô hình phân cấp. Thực chất mô hình phân cấp là tập con của mô hình mạng. Tuy nhiên, nó sử dụng lý thuyết tập để tạo ra một cây phân cấp mà trong đó mỗi nút con có thể có nhiều hơn một nút cha. Dữ liệu được lưu trữ trên các tập thay vì định dạng theo phân cấp. Nó giúp hạn chế dư thừa dữ liệu. Nó cho phép một nút con có nhiều hơn một nút cha. Vì vậy, các bản ghi vật lý liên quan với nhau thông qua một danh sách liên kết. Mô hình mạng kết hợp với mô hình phân cấp là mô hình dữ liệu cơ sở để c ài đặt nhiều hệ thống CSDL thương mại. Ngôn ngữ và cấu trúc của mô hình mạng được định nghĩa bởi Hội thảo về ngôn ngữ và hệ thống dữ liệu (CODASYN). Đối với mọi CSDL, tên CSDL, tên bản ghi và các thành phần tạo nên bản ghi đều được lưu trữ. Nó gọi là một sơ đồ mạng, một phần của chúng được nhìn thấy bởi các chương trình ứng dụng, các chương trình này lấy thông tin cần thiết từ dữ liệu trong CSDL được gọi là sơ đồ con. Những thành phần của ngôn ngữ được sử dụng trong mô hình mạng: ● Ngôn ngữ định nghĩa dữ liệu ● Ngôn ngữ thao tác dữ liệu ● Ngôn ngữ điều khiển dữ liệu. Ưu điểm Nhược điểm - Các quan hệ dễ dàng được thiết lập trên mô hình mạng hơn so với mô hình phân cấp. - Mô hình này đảm bảo sự toàn vẹn dữ liệu. - Mô hình này đạt được sự độc lập dữ liệu cần thiết. - Khó thiết lập CSDL theo mô hình này. - Người lập trình phải nắm rõ cấu trúc bên trong CSDL để truy cập. - Nó cung cấp môi trường truy cập CSDL nên để di chuyển từ A sang E thì phải qua B, C, D. + Mô hình quan hệ: Tất cả dữ liệu được chứa trong các bảng, các bảng chứa các dòng và các cột. Dữ liệu trên 2 bảng được quan hệ với nhau thông qua các cột thay cho liên kết vật lý. Những phép toán được cung cấp để thực hiện trên các dòng của bảng dữ liệu. Mô hình này mô tả CSDL là một tập hợp các quan hệ. Trong thuật ngữ mô hình, một dòng được gọi là Tuple, cột được gọi là thuộc tính, bảng được gọi là quan hệ. Danh sách các giá trị trong một trường gọi là miền. Có thể có một vài thuộc tính có chung miền. Số thuộc tính của một quan hệ gọi là bậc của quan hệ. Số Tuple xác định số thành viên trong quan hệ. Ưu điểm của nó cho phép lập trình viên tập trung vào góc nhìn logic hơn hơn là tập trung vào góc nhìn vật lý. Nhược điểm của nó là thực thi châm hơn so với các mô hình CSDL khác. 4. Hệ quản trị CSDL quan hệ (Relational DataBase Management System - RDBMS): + Thực thể: là một đối tượng cụ thể được nhận biết rõ ràng, nó có các đặc tính gọi là các thuộc tính, nhóm các thực thể gọi là tập thực thể, mỗi tập thực thể được đặt một tên, tên của nó phảm ánh nội dung của tập thực thể. + Bảng: chứa một nhóm các thực thể có liên quan với nhau hay gọi là một tập các thực thể. Một bảng còn được gọi là một quan hệ (Relation), các dòng được gọi là các bộ (Tuples), các cột được gọi là các thuộc tính (Attributes). Thành viên của quan hệ là số bộ trong một quan hệ, bậc của quan hệ là số thuộc tính trong một quan hệ. - Người dùng của hệ quản trị CSDL: + Quản trị viên CSDL. + Thiết kế viên CSDL. + Chuyên viên phân tích hệ thống và lập trình viên ứng dụng. + Chuyên viên cài đặt và thiết kế viên quản trị CSDL. + Người dùng cuối. - Phân biệt giữa hệ quản trị CSDL và hệ quản trị CSDL quan hệ: DBMS RDBMS - Không cần có dữ liệu theo cấu trúc dạng bảng và không ép buộc các mối quan hệ dạng bảng giữa các mục dữ liệu. - Lượng nhỏ dữ liệu được lưu trữ và truy xuất. - Bảo mật kém hơn RDBMS. - Là hệ thống đơn người dùng. - Hầu hết không hỗ trợ kiến trúc client/server. - Thực thể là quan trọng hơn và không có quan hệ được thiết lập giữa các thực thể này. - Phải là cấu trúc dạng bảng và các mối quan hệ bảng được đảm bảo bởi hệ thống. - Có thể lưu và truy xuất lượng lớn dữ liệu. - Bảo mật mạnh hơn so với DBMS. - Là hệ thống đa người dùng. - Hỗ trợ kiến trúc client/server. - Người dùng có thể thiết lập nhiều ràng buộc toàn vẹn cho các bảng để dữ liệu sau cùng được sử dụng bởi người dùng vẫn giữ đúng đắn do quan hệ quan trọng nhất. ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – MÔ HÌNH DỮ LIỆU 1. Mô hình hóa dữ liệu (Data Modeling) là tiến trình áp dụng mô hình thích hợp cho dữ liệu thô. Nó trải qua ba bước chính. 2. Mô hình quan hệ thực thể (Entity- Relationship): xem toàn bộ thế giới thực như một tập hợp các đối tượng cơ bản và các mối quan hệ giữa chúng. - Năm thành phần chính của mô hình quan hệ thực thể: + Thực thể (Entity): là một đối tượng tồn tại trong thế giới thực và được phân biệt với các đối tượng khác. + Mối quan hệ (Relationship): là sự kết hợp hay gắn kết giữa một hay nhiều thực thể. + Thuộc tính (Attributes): là các đặc tính hay đặc trưng của một thực thể, nó phân biệt thực thể này với thực thể khác. + Tập thực thể (Entity Set): là một tập các thực thể tương tự nhau. - Các loại quan hệ: - Một số khái niệm khác: + Khóa chính (primary key): là một thuộc tính có thể định rõ duy nhất một thực thể trong tập thực thể. + Tập thực thể yếu (Weak entity sets): khi nhiều tập thực thể không đủ các thuộc tính để thiết lập khóa chính. + Tập thực thể mạnh (Strong entity sets): là các tập thực thể có đủ các thuộc tính để thiết lập khóa chính. - Lược đồ quan hệ thực thể: + Các ký hiệu biểu diễn: Đa trị (Multi-valued) Phức hợp (Composite) Dẫn xuất (Derived) Có nhiều hơn một giá trị với ít nhất một thể hiện của thực thể chứa nó. Có thể chứa hai hay nhiều thuộc tính, nhiều biểu diễn hơn các thuộc tính cơ bản Các thuộc tính có giá trị phụ thuộc hoàn toàn vào thuộc tính khác. 3. Chuẩn hóa: là tiến trình gỡ bỏ các dữ liệu dư thừa từ các bảng của CSDL quan hệ. Địa chỉ Số điện thoại Đường Vùng Tuổi Bảng ban đầu BẢNG CHI TIẾT DỰ ÁN CỦANHÂN VIÊN manv maduan tenduan tennv loai luong 142 113, 124 Sach, Xay Thach A 20000 168 113 Sach Dai B 15000 263 113 Sach Ngoc C 10000 109 124 Xay Vu C 10000 Dạng chuẩn 1: First Normal Form - Tạo ra các bảng riêng biệt cho mỗi nhóm dữ liệu liên quan. - Các cột của bảng phải có các giá trị nguyên tố. - Tất cả các thuộc tính khóa phải được nhận dạng. BẢNG CHI TIẾT DỰ ÁN maduan tenduan 113 Sach 124 Xay BẢNG CHI TIẾT NHÂN VIÊN manv tennv loai luong 142 Thach A 20000 168 Dai B 15000 263 Ngoc C 10000 109 Vu C 10000 Dạng chuẩn 2: Second Normal Form - Chúng đã thỏa mãn các yêu cầu ở dạng chuẩn một. - Chúng không phụ thuộc một phần trong bảng. - Các bảng quan hệ thông qua các khóa ngoại. BẢNG CHI TIẾT DỰ ÁN maduan tenduan 113 Sach 124 Xay BẢNG CHI TIẾT DỰ ÁN CỦA NHÂN VIÊN manv maduan 142 113 142 124 168 113 263 113 109 124 BẢNG CHI TIẾT NHÂN VIÊN manv tennv loai luong 142 Thach A 20000 168 Dai B 15000 263 Ngoc C 10000 109 Vu C 10000 Dạng chuẩn 2: Third Normal Form - Chúng đã thỏa mãn các yêu cầu ở dạng chuẩn hai. - Không tồn tại phụ thuộc bắc cầu trong bảng. BẢNG CT DỰ ÁN maduan tenduan 113 Sach 124 Xay BẢNG CT DỰ ÁN CỦA NHÂN VIÊN manv maduan 142 113 142 124 168 113 263 113 109 124 BẢNG CT LƯƠNG loai luong A 20000 B 15000 C 10000 BẢNG CHI TIẾT NHÂN VIÊN manv tennv loai 142 Thach A 168 Dai B 263 Ngoc C 109 Vu C 4. Các loại toán tử: mô hình quan hệ được xâu dựng dựa trên cơ sở đại số quan hệ (gồm tập hợp các phép toán hoạt động trên các quan hệ. Mỗi toán tử giữ một hoặc 2 quan hệ như đầu vào của nó và kết quả quan hệ mới là đầu ra). - Phép chọn (SELECT δ) - Phép chiếu (PROJECT π) - Phép nhân (PRODUCT x) - Phép kết hợp (UNION U) - Phép giao (INTERSECT ) - Phép hiệu (DIFFERENCE) - Phép nối (JOIN) - Phép chia (DIVIDE) ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – Cơ sở dữ liệu: là một tập các dữ liệu được lưu trữ trong các tập tin dữ liệu trên một đĩa hoặc các thiết bị lưu trữ có thể di chuyển được. Một cơ sở dữ liệu để giữ các thiết bị thật. CƠ SỞ DỮ LIỆU HỆ THỐNG (System Databases) CƠ SỞ DỮ LIỆU NGƯỜI DÙNG ĐỊNH NGHĨA CƠ SỞ DỮ LIỆU MẪU Adventure Works - Các loại CSDL hệ thống ● master: lưu trữ tất cả các thông tin hệ thống của một phiên bản cải đặt SQL Server. ● msdb: được sử dụng bởi SQL Server Agent để nhắc nhở các thao tác thực hiện theo lịch biểu và các công việc khác. ● model: được sử dụng như CSDL mẫu cho tất cả các CSDL được tạo ra trong phiên bản cài đặt cụ thể. ● resource: CSDL chỉ đọc, nó gồm các đối tượng hệ thống. ● tempdb: lưu trữ các đối tượng tạm thời và các tập kết quả trung gian. - Thay đổi dữ liệu của CSDL hệ thống: Người dùng không được phép cập nhật thông tin trực tiếp vào các đối tượng CSDL hệ thống: các bảng, thủ tục, khung nhìn; nhưng có thể sử dụng một tập đầy đủ các công cụ quản trị cho phép họ quản trị đầy đủ hệ thống và quản lý tất cả người sử dụng và các đối tượng CSDL: + Các tiện ích quản trị SQL Server Management Studio. + Giao diện lập trình ứng dụng với các đối tượng quản lý SQL Server. + Kịch bản Transact-SQL và thủ tục lưu trữ. - Xem dữ liệu của CSDL hệ thống: + Khung nhìn danh mục hệ thống. + Các đối tượng quản lý SQL Server. + Các hàm danh mục, các phương thức, các thuộc tính, các tính chất của giao diện lập trình ứng dụng CSDL ● Đối tượng dữ liệu ActiveX (ADO). ● CSDL liên kết và nhúng đối tượng (OLEDB). ● Các thủ tục lưu trữ và các hàm. - Gồm các phần + AdventureWorks: xử lý giao dịch trực tuyến (Online Transaction Processing - OLTP). + AdventureWorksDW: kho (Data warehouse). + AdventureWorksAS: dịch vụ phân tích (Analysis Services). - Tạo cơ sở dữ liệu: CREATE DATABASE tên_CSDL ON PRIMARY (NAME = N’tên_luận_lý’, FILENAME = N’tên_vật_lý’, SIZE = ‘kích_thước_ban_đầu’, MAXSIZE = ‘kích_thước_lớn_nhất’, FILEGROWTH = ‘kích_thước_tăng_trưởng’ ) LOG ON ( NAME = N’tên_luận_lý’, FILENAME = N’tên_vật_lý’, SIZE = ‘kích_thước_ban_đầu’, MAXSIZE = ‘kích_thước_lớn_nhất’, FILEGROWTH = ‘kích_thước_tăng_trưởng’ ) -- Lệnh mở CSDL: USE tên_CSDL - Thay đổi cơ sở dữ liệu: ALTER DATABASE tên_CSDL -- Đổi tên Cơ sở dữ liệu MODIFY NAME = tên_CSDL_mới -- Thêm file vào CSDL ADD FILE ( NAME = N’tên_luận_lý’, FILENAME = N’tên_vật_lý’, SIZE = ‘kích_thước_ban_đầu’, MAXSIZE = ‘kích_thước_lớn_nhất’, FILEGROWTH = ‘kích_thước_tăng_trưởng’ ) -- Chọn kiểu sắp xếp COLLATE tên_kiểu_sắp_xếp - Quyền sở hữu của CSDL EXEC sp_changedbowner ‘tên_tài_khoản’ - Hủy bỏ CSDL DROP DATABASE tên_CSDL F IL E C Ơ S Ở D Ữ L IỆ U tên file luận lý: các file vật lý trong các câu lệnh Transact- SQL được tham chiếu bởi tên file luận lý. Tên file logic phải tuân theo các luật cho các định danh SQL Server và sẽ là duy nhất nằm trong số các tên file luận lý trong CSDL. tên file vật lý: là đường dẫn thư mục. Nó phải tuân theo các luật cho các tên file hệ điều hành. Các file CSDL gồm: *.MDF File dữ liệu sơ cấp: là điểm bắt đầu của CSDL và trỏ đến các file khác của CSDL *.NDF File dữ liệu thứ cấp: chứa đựng tất cả các file dữ liệu, trừ các file dữ liệu chính. Có thể không cần. *.LDF File chứa đựng tất cả các thông tin nhật ký được sử dụng để phục hồi CSDL. Mỗi CSDL có ít nhất một.. - Bản chụp CSDL là một khung nhìn tĩnh, chỉ đọc của một CSDL CREATE DATABASE tên_bản_chụp ON (NAME = tên_luận_lý, FILENAME =’tên_vật_lý’,) [,..] AS SNAPSHOT OF tên_CSDL_cần_chụp Lưu ý: việc tạo một bản chụp yêu cầu chỉ ra tên luận lý của mọi tập tin CSDL của CSDL gốc. - Sao lưu dữ liệu ra tập tin BACKUP DATABASE Tên_CSDL TO DISK = 'Tên_tập_tin_bak' [ WITH INIT , NOUNLOAD , NAME = ‘đặt tên cho bản backup’, SKIP , STATS = 10, NOFORMAT ] - Khôi phục CSDL: RESTORE DATABASE Đặt_tên_cho_csdl_sau_khi_khôi_phục FROM DISK = 'Ten_tap_tin_bak' [ WITH FILE = 1, UNLOAD , STATS = 10, RECOVERY , REPLACE ] ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – Ngôn ngữ định nghĩa dữ liệu DDL – Data definition language là ngôn ngữ dùng để tạo, thay đổi và xóa cấu trúc đối tượng Ngôn ngữ thao tác dữ liệu DML – Data manipulation language là ngôn ngữ dùng để thao tác với dữ liệu trong bảng Ngôn ngữ điều khiển dữ liệu DCL – Data control language là ngôn ngữ dùng để gán hay xóa quyền trên các đối tượng - Thao tác với Cơ sở dữ liệu: (xem ở trên phần tạo cơ sở dữ liệu) - Thao tác với bảng: + Tạo bảng CREATE TABLE tên_bảng ( tên_cột_1 kiểu_dữ_liệu(độ_rộng) ràng_buộc, tên_cột_2 kiểu_dữ_liệu(độ_rộng) ràng_buộc, ... ) Các ràng buộc trong dữ liệu cột: ● NULL | NOT NULL: giá trị null có được phép sử dụng trong cột hay không. ● DEFAULT (): gán giá trị mặc định ban đầu cho các dữ liệu. Thông thường nếu không đặt đối với kiểu số là 0 và kiểu chuỗi là N/A hoặc Unknown. Nó không được sử dụng khi cột có kiểu dữ liệu timestamp ; có thuộc tính IDENTITY, ROWGUIDCOL hay các đối tượng mặc định. ● IDENTITY([,]): dùng để tạo ra các cột có giá trị tự động phát sinh tuần tự để định danh duy nhất mỗi hàng trong một bảng. ● DEFAUT NEWID(): tạo ra cột định danh duy nhất tổng thể. ● Các ràng buộc Constraint gồm các kiểu sau: ► PRIMARY KEY([,cột_thứ_2,...]): được sử dụng để tạo ra một khóa chính, đảm bảo tính toàn vẹn thực thể bảng, mỗi bảng chỉ có một khóa chính và cột khóa chính không được chứa giá trị NULL. Trong trường hợp hai cột giữ vai trò khóa chính ta dùng thêm dấu phẩy ở sau. ► UNIQUE([,cột_thứ_2,...]): để tạo ra ràng buộc khóa duy nhất, đảm bảo các giá trị duy nhất được nhập trên cột hay tập hợp các cột. Một bảng có thể có nhiều hơn một ràng buộc UNIQUE. ► FOREIGN KEY REFERENCES .: là một cột trong bảng này để chỉ đến một khóa chính trong một bảng khác nhằm đảm bảo toàn vẹn tham chiếu. ► CHECK điều_kiện : dùng để kiểm tra giới hạn giá trị có thể được nhập vào thỏa mãn điều kiện quy định. + Thay đổi cấu trúc của bảng: ALTER TABLE tên_bảng ALTER COLUMN tên_cột kiểu_dữ_liệu_mới - - Đổi kiểu dữ liệu của cột ADD tên_cột kiểu_dữ_liệu - - Thêm cột mới DROP COLUMN tên_cột, - - Xóa cột EXEC sp_rename ‘tên_bảng.tên_cột’, ‘tên_mới’,’COLUMN’ –Đổi tên cột EXEC sp_rename ‘tên_bảng’, ‘tên_mới’ --Đổi tên bảng ADD CONSTRAINT [] - - Thêm constraint DROP CONSTRAINT - - Xóa constraint + Xóa bảng khỏi cơ sở dữ liệu: DROP TABLE tên_bảng - Rút trích dữ liệu từ cơ sở dữ liệu: SELECT cột_hiển_thị FROM bảng_truy_xuất WHERE điều_kiện_lọc CÁC_LỆNH_KHÁC + Một số lệnh định dạng trong cột_hiển_thị: ● Để hiển thị nhiều cột trong bảng, ta liệt kê tên các cột được phân cách bởi dấu phẩy; để liệt kê tất cả các cột trong bảng ta dùng ký tự * hoặc ALL ; để hiển thị các cột trong nhiều bảng khác nhau, đối với các cột có trong nhiều bảng ta phải dùng . để tránh lỗi không tường minh. ● Để đổi tên cột trong hiển thị ta dùng = hoặc AS . ● Lệnh DISTNCT dùng để ngăn chặn hiển thị các bản ghi trùng nhau. ● Lệnh TOP [PERCENT] [WITH TIES] :hiển thị một tập các dòng đầu tiên của kết quả. Trong đó: ► : là số hàng hay phần trăm số hàng trong bảng được trả về trong tập kết quả. ► PERCENT: trả về số hàng bị giới hạn bởi tỷ số phần trăm. ► WITH TIES: nếu bản ghi cuối trong TOP có cùng giá trị thì kết quả bản ghi đó cũng được trả về. + Một số lệnh trong bảng_truy_xuất ● Khi truy xuất từ một bảng, ta chỉ ra tên bảng cần truy xuất đến. ● Khi truy xuất từ nhiều bảng , ta có thể sử dụng lệnh: TỪ_KHÓA_NỐI ON Trong đó TỪ_KHÓA_NỐI có thể là: ► INNER JOIN : để nối các bảng vào, không phân biệt bảng bên trái với bảng bên phải. ► LEFT JOIN : để nối các bảng vào, lấy tất cả các giá trị bảng bên trái nối với bảng bên phải. ► RIGHT JOIN : để nối các bảng vào, lấy tất cả các giá trị bảng bên phải nối với bảng bên trái. + Một số lệnh trong điều_kiện_lọc: có thể sử dụng các toán tử >; =; ; !; OR; AND; NOT; BETWEEN a TO b (để lấy từ a đên b); LIKE (tìm các ký tự đại diện: _ ký tự bất kỳ; % bất kỳ ký tự nào; [...] có ký tự đơn trong ngoặc; [^...] không có ký tự đơn trong ngoặc); IN[a-b] (bên trong một khoảng). + Một số câu lệnh xắp xếp và tổng hợp: ● Mệnh đề ORDER BY [ASC | DESC]: xác định trật tự các cột được sắp xếp trong tập kết quả là tăng dần ASC hay giảm dần DESC. Khi có nhiều trường cần xắp xếp, nó sẽ thực hiện từ trái sang phải. ● Mệnh đề GROUP BY để chia tập kết quả thành một hoặc nhiều tập con, mỗi tập con có giá trị và biểu thức chung GROUP BY HAVING tìm kiếm nhóm theo một điều kiện quy định. Mỗi nhóm chỉ trả về một bản ghi duy nhất. GROUP BY WITH CUBE CUBE kết hợp với GROUP BY vừa trả về thông tin chi tiết, vừa trả về thông tin thống kê. Nó cho phép nhóm các trường được chỉ ra trong danh sách ngoài trừ trường chứa dữ liệu thống kê. GROUP BY WITH ROLLUP nó sinh ra tập kết quả hiển thị theo nhóm được sắp xép theo một thứ tự phân cấp từ thấp đến cao, trật tự nhóm trong kết quả phụ thuộc vào trật tự các cột được nhóm đã chỉ định. - Chèn dữ liệu vào bảng: + Chèn vào một hàng: INSERT INTO tên_bảng VALUES các_giá_trị_tương_ứng_cột_của_bảng + Chèn nhiều hàng từ một bảng khác: INSERT INTO (Danh_sách_cột) SELECT Danh_sách_cột FROM Bảng_dữ_liệu_nguồnWHERE Điều_kiện_lọc - Thay đổi dữ liệu trong bảng: UPDATE tên_bảng SET tên_cột = giá_trị WHERE điều_kiện_thay_đổi - Xóa các hàng dữ liệu trong bảng: DELETE FROM tên_bảng WHERE điều_kiện_xóa Tài khoản có thể là một trong 4 loại sau: + Người dùng SQL Server + Người dùng có quyền với SQL Server + Tài khoản Windows + Nhóm tài khoản Windows - Gán quyền các đối tượng cơ sở dữ liệu cho người dùng: GRANT [ALL | các_câu_lệnh] ON tên_bảng TO các_tài_khoản - Xóa quyền đã gán trên các đối tượng của người dùng: REVOKE [ALL | các_câu_lệnh] ON tên_bảng FROM các_tài_khoản - Ngăn quyền của người dùng: DENY [ALL | các_câu_lệnh] ON tên_bảng TO các_tài_khoản Ngôn ngữ Transact-SQL là ngôn ngữ mở rộng của 2 ngôn ngữ ANSI-SQL và ISO-SQL ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – KIỂU DỮ LIỆU: là một thuộc tính - xác định kiểu dữ liệu - xác định dung lượng lưu trữ dữ liệu KIỂU DỮ LIỆU HỆ THỐNG (System-defined data types) KIỂU DỮ LIỆU NGƯỜI DÙNG (Alias data types) KIỂU DỮ LIỆU ĐƯỢC ĐỊNH NGHĨA SỐ NGUYÊN - bit: giá trị 1 hoặc 0. - tinyint: từ 0 đến 255. - smallint: từ (-215) đến (215-1) - int: từ (-231) đến (231-1) - bigint: từ (-263) đến (263-1) - numeric: từ (-1038+1) đến (1038-1)* - decimal: từ (-1038+1) đến (1038-1)* - smallmoney: từ (-231-1) đến (231-1) chia cho 10000 dạng tiền tệ. - money: từ (-263-1) đến (263-1) chia cho 10000 dạng tiền tệ. SỐ THỰC - float: sử dụng 8 byte bộ nhớ. - real: sử dụng 4 byte bộ nhớ. NGÀY VÀ GIỜ - datetime: 1/1/1753 - 31/12/9999, đúng đến phần nghìn của miligiây. - smalldatetime: 1/1/1900 - 6/6/2079, đúng đến phút. - time: thời gian trong 1 ngày. - date: ngày trong một năm. - datetimeoffset: ngày UTC, múi giờ - datetime2: giờ hiện tại. CHUỖI + Không hỗ trợ Unicode: - char: kích thước cố định, tối đa 8000 ký tự. - varchar: kích thước thay đổi, tối đa 8000 ký tự. - text: kích thước thay đổi, tối đa 231 - 1 ký tự. + Hỗ trợ Unicode: gồm nchar, nvarchar, ntext tương tự trên nhưng số ký tự tối đa giảm một nửa. CÁC KIỂU KHÁC + Chuỗi nhị phân: - binary - varbinary - image + Các kiểu khác: - sql_variant - timestamp - uniqueisentifier - xml + Kiểu dữ liệu CLR - hierarchyid + Kiểu dữ liệu không gian - geometry - geography Cú pháp: CREATE TYPE [tên_giản_đồ] tên_kiểu { FROM kiểu_hệ_thống [(độ_chính_xác [,độ_co_giãn_với_số])] [NULL|NOT NULL]} [,] Ví dụ: CREATE TYPE ten FROM varchar(20) - Tạo kiểu dữ liệu mới: EXEC sp_addtype tên_kiểu, ‘kiểu_hệ_thống [(độ_chính_xác [,độ_co_giãn_với_số])]’, [NULL|NOT NULL]} [,] - Xóa kiểu dữ liệu: EXEC sp_droptype tên_kiểu Lưu ý: chỉ xóa được khi không có cột nào trong bảng CSDL dùng nó. LÀM VIỆC VỚI XML – EXTENSIBLE MARKUP LANGUAGE Thuận lợi khi làm việc với cơ sở dữ liệu XML tự nhiên trong SQL Server là: - Dễ dàng quản lý và tìm kiếm dữ liệu: tất cả các dữ liệu XML được lưu trữ cục bộ ở một nơi, vì thế dễ dàng tìm kiếm và quản lý - Sự thực thi tốt hơn: Các truy vấn từ một cơ sở dữ liệu XML được thi hành tốt sẽ nhanh hơn các truy vấn bằng tài liệu đã lưu trữ trong hệ thống tập tin. Ngoài ra, về bản chất, cơ sở dữ liệu sẽ phân tách mỗi tài liệu khi lưu trữ chúng - Dễ dàng xử lý dữ liệu: các tài liệu lớn có thể được xử lý dễ dàng Tạo bảng với cột kiểu XML: CREATE TABLE ( [ column_list,] xml [, column_list]) Ví dụ: -- Tạo bảng CREATE TABLE Person.PhoneBilling (Bill_ID int PRIMARY KEY, MobileNumber bigint UNIQUE, CallDetails xml) -- Chèn dữ liệu vào bảng: INSERT INTO Person.PhoneBilling VALUES (100,9833276605, ‘Local45 minutes200’) SELECT CallDetails FROM Person.PhoneBilling –truy vấn dữ liệu XML định kiểu và không định kiểu - Có hai cách lưu trữ dữ liệu XML trong các cột kiểu dữ liệu XML và các biến: được phân loại; không phân. - Một thể hiện XML mà nó được kết hợp với một lược đồ được gọi là thể hiện XML được phân loại (Typed XML). Ở đây, dữ liệu có thể được xác định tính hợp lệ trong khi nó được lưu trữ vào trong cột dữ liệu XML. Một thể hiện XML không được phân loại (Untyped XML) không có sự kết hợp với một lược đồ XML. SQL Server không thực hiện việc kiểm tra tính hợp lệ của dữ liệu nhập vào trong cột. - Ví dụ giản đồ XML: CREATE XML SCHEMA COLLECTION CricketSchemaCollection AS N’ ’ - Vi dụ sử dụng XML đã định kiểu: CREATE TABLE CricketTeam (TeamID int IDENTITY NOT NULL, TeamInfo xml(CricketSchemaCollection) ) INSERT INTO CricketTeam (TeamInfo) VALUES (‘ ’) DECLARE @team xml(CricketSchemaCollection) SET @team = ‘’ SELECT @team XQUERY: - Cho phép truy vấn và khôi phục dữ liệu XML bằng cách sử dụng một ngôn ngữ có tên là XQuery - Kết hợp các cú pháp quen thuộc với người lập trình, người làm việc việc với cơ sở dữ liệu quan hệ, và ngôn ngữ Xpath, để chọn các đoạn hoặc các tập hợp riêng lẻ của các phần tử từ tài liệu XML - Để truy vấn một thể hiện XML được lưư trong một biến hoặc cột kiểu xml, các phương thức kiểu xml được sử dụng. - Các phương thức: + .EXIST(): được sử dụng để xác định nếu một hoặc nhiều nút được chỉ định xuất hiện trong tài liệu XML. + .QUERY(): được sử dụng để truy lục hoặc toàn bộ nội dung của tài liệu XML hoặc các đoạn có chọn lựa trong tài liệu. + .VALUE():rút ra các giá trị vô hướng từ một kiểu dữ liệu. ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – TỔNG QUAN VỀ SQL SERVER Đầu những năm 1970, hãng IBM đã sáng tạo ra phần mềm SEQUEL (Structure English Query Language). Sau đó, nó được rút ngắn lại thành SQL (Structure Query Language). SQL Server là một hệ quản trị CSDL quan hệ được phát triển bởi Microsoft. Nó cung cấp nền tảng quản trị dữ liệu ở mức xí nghiệp. Ngoài ra nó còn hỗ trợ cho việc phát triển mạnh mẽ các ứng dụng phía Server. Nó không đơn thuần là một RDBMS mà còn cung cấp các khả năng báo cáo, phân tích dữ liệu và khai phá dữ liệu và các chức năng cho xử lý dữ liệu trong khi đợi dữ liệu đồng bộ hóa với ứng dụng đầu cuối (front-end). 1. Khái quát chung về SQL Server: - Chức năng: + Dễ dàng cài đặt + Tích hợp với Interrnet + Tính trải rộng và tính sẵn sàng + Hỗ trợ cho mô hình client/server + Tương thích hệ điều hành + Kho dữ liệu + Thực thi theo ANSI/ISO SQL-92 + Hỗ trợ nhân bản dữ liệu + Tìm kiếm full-text + Sách hướng dẫn trực tuyến + Dịch vụ thông báo + Dịch vụ báo cáo + Môi giới dịch vụ + Sự cải tiến máy SQL - Các công cụ SQL Server: Enterprise Standard Workgroup Developer Express - Công dụng: + Quản trị dữ liệu xí nghiệp mở rộng + Tính hiệu quả cho người phát triển + Tăng tính thông minh trong giao dịch + Tính năng cạnh tranh cao - Các thành phần + Máy CSDL quan hệ + Dịch vụ phân tích + Dịch vụ chuyển đổi dữ liệu + Dịch vụ thông báo + Hỗ trợ HTTP thuần + Sự tích hợp .NET CLR + Các dịch vụ báo cáo + Người môi giới dịch vụ + SQL Server Agent + Bản sao + Tìm kiếm toàn văn bản 2. Kiến trúc ứng dụng xí nghiệp: SQL Server đóng vai trò chính trong việc kiến trúc ứng dụng phân tán. Việc thiết kế SQL Server cho một giải pháp xí nghiệp dựa vào kiểu của kiến trúc và xử lý logic được phân tán qua các ứng dụng. Kiến trúc ứng dụng xí nghiệp chứa nhiều tầng logic khác nhau. Các tầng này mô tả sự trình bày dữ liệu, ứng dụng logic và các dịch vụ dữ liệu. Tầng dữ liệu trình bày Tầng logic ứng dụng Tầng dịch vụ dữ liệu 3. Kiến trúc CSDL: - Thành phần CSDL logic: + Các đối tượng của CSDL Đối tượng Mô tả Bảng - TABLE Một đối tượng trong một CSDL để lưu trữ dữ liệu như một tập hợp của các dòng và các cột. Kiểu dữ liệu DATA TYPE Một thuộc tính để chỉ ra kiểu của dữ liệu mà nó có thể lưu trữ được lưu trữ trong một cột, tham số, hoặc biến. SQL Server hỗ trợ cả dữ liệu được hệ thồng cung cấp cũng như là kiểu dữ liệu hệ thống. Khung nhìn VIEW Một đối tượng CSDL có thể được tham chiếu theo cách tương tự như một bảng trong các câu lệnh SQL. Các khung nhìn được tạo ra bởi việc sử dụng câu lệnh SELECT và có thể so sánh với một đối tượng chứa đựng tập kết quả của cùng câu lệnh SELECT. Thủ tục lưu trữ STORED PROCEDURE là một tập hợp của các câu lệnh đã viết và đã được biên dịch được lưu trữ dưới một tên và được xử lý như là một đơn vị trong CSDL. Nếu có cùng một truy vấn được tìm thấy được sử dụng lại nhiều lần, ta nên đặt nó thành một thủ tục được lưu trữ. SQL Server cung cấp các thủ tục lưu trữ cho việc quản lý SQL Server và cho việc hiển thị thông tin về các CSDL và những người dùng. SQL Server đã cung cấp các thủ tục lưu trữ được biết đến như là các thủ tục lưu trữ hệ thống. Hàm FUNCTION là một đoạn mã nguồn mà hoạt động như một đơn vị logic. Mỗi hàm có một tên và được định nghĩa để chấp nhận các tham số đầu vào và trả về một trạng thái hoặc tùy chọn các tham số đầu ra. Chỉ mục INDEX Một đối tượng CSDL cung cấp sự truy cập nhanh đến dữ liệu của một bảng dựa vào giá trị khóa của nó. Ràng buộc CONSTRAINT là một thuộc tính mà có thể được gán vào một cột hoặc gán vào các cột trong một bảng để tăng tính toàn vẹn dữ liệu. Các ràng buộc được thêm vào để ngăn chặn các dữ liệu không phù hợp nhập vào cho một bảng. Bẫy lỗi TRIGGER là các thủ tục được lưu trữ đặc biệt mà được thực hiện để phúc đáp cho bất kỳ sự thay đổi đã được thực hiện cho một bảng. + Đăng nhập, người dùng, vai trò và nhóm: Kỹ thuật bảo mật Mô tả Đăng nhập LOGIN Một người dùng phải được gán với một nhận dạng đăng nhập để kết nối vào SQL Server. SQL Server nhận dạng hai kỹ thuật chứng thực đăng nhập (Authentication): chứng thực dựa trên Windows (Windows Authentication) và chứng thực dựa trên SQL Server (SQL Server Authentication). Cả hai kỹ thuật có các kiểu tài khoản đăng nhập khác nhau. Người dùng USER Các tài khoản người dùng nhận dạng một người dùng trong phạm vi một CSDL. Sau khi một người dùng đã được chứng thực bởi Windows hoặc SQL Server và đã được cho phép đăng nhập vào SQL Server, thì người dùng đó phải có một tài khoản trong CSDL. Tất cả sự cho phép và quyền sở hữu của các đối tượng trong CSDL được điều khiển bởi tài khoản người dùng. Vai trò ROLE Các vai trò được sử dụng để nhúng các người dùng vào một hoặc vài nhóm, một vài hoặc nhóm tương ứng vơi tập hợp các quyền được cung cấp. Các quyền được cung cấp tự động khi những người dùng trở thành thành viên của vai trò. Nhóm GROUP Các nhóm SQL Servet cung cấp một cách thuận lợi để tổ chức một lượng lớn Server váo các nhóm có thể quản lý được. Một nhóm Server có thể được đăt tên và được mô tả để cho phép người sử dụng dễ dàng tìm kiếm các Server. + Thứ tự sắp xếp: chỉ ra các quy luật việc sắp xếp và so sánh các chuỗi dữ liệu ký tự, dựa vào các quy tắc của ngôn ngữ và địa phương cụ thể. Thứ tự sắp xếp điều khiển cách thức các chuỗi ký tự trong SQL Server được lưu trữ bên trong nó. - Kiến trúc vật lý của CSDL: + Trang (PAGE) và phạm vi (EXTENTS): Trang là đơn vị nhỏ nhất của việc lưu trữ dữ liệu trong SQL Server. SQL Server cung cấp 8KB cho mỗi trang. Mỗi trang được bắt đầu với phần đầu trang chứa các thông tin về loại trang, không gian trống của trang và định danh (identifier - ID) của người dùng sở hữu trang. Dữ liệu trong mỗi hàng được chứa tuần tự trên các trang và chứa dữ liệu thực. Phạm vi là đơn vị gốc trong các không gian được cấp phát đến các bảng, tất cả các trang được lưu trữ và quản lý hiệu quả trong các phạm vi. Một phạm vi là tập hợp của 8 trang liên tiếp nhau và có kích thước 64KB. + Nhóm tập tin (FILEGROUP) và các tập tin CSDL (DATABASE FILES) + Sự cấp phát vùng trống và tái sử dụng. + Kiến trúc bảng và chỉ mục (TABLE AND INDEX ARCHITECTURE): trong mỗi bảng và chỉ mục, các hàng dữ liệu được lưu trữ trong các trang. Phần đầu trang chứa các nhận dạng là bảng chủ sở hữu của trang. Bảng Clustered: là các bảng có một chỉ mục Clustered. Heaps: là bảng không có chỉ mục Clustered. + Kiến trúc nhật ký giao dịch: hỗ trợ: Phục hồi các giao dịch riêng lẻ. Phục hồi tất cả các giao dịch chưa hoàn tất khi SQL Server đã được bắt đầu. Hoàn trả lại trạng thái CSDL ban đầu nếu lỗi xảy ra. - Kiến trúc bộ máy CSDL quan hệ: có hai phần chính phần quan hệ và phần lưu trữ. + Cấu trúc xử lý truy vấn: Xử lý từng câu lệnh SQL đơn Xử lý lô. Thực thi các thủ tục lưu trữ và bẫy lỗi. Thực thi kế hoạch lưu trữ tạm và tái sử dụng. Xử lý truy vấn song song. + Cấu trúc bộ nhớ. + Cấu trúc luồng và thao tác. + Cấu trúc vào/ra. + Cấu trúc truy vấn phân tán. ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – BIẾN – HÀM – BIỂU THỨC VÀ CHÚ THÍCH TRONG SQL SERVER - Khai báo biến: biến là vùng nhớ trong bộ nhớ được đặt tên để chứa giá trị dữ liệu. Biến cục bộ Biến toàn cục + là biến được khai báo và sử dụng tạm thời khi thực hiện câu lệnh SQL. DECLARE @tên_biến [AS] kiểu_dữ_liệu + Câu lệnh SET hoặc SELECT dùng để gán giá trị cho biến. SET @tên_biến = giá_trị SELECT @tên_biến = giá_trị + là biến có sẵn và hệ thống quản lý, chúng được đặt tên bắt đầu bởi hai ký tự @. - Kiểu dữ liệu: là thuộc tính định nghĩa loại dữ liệu mà đối tượng có thể chứa. Các đối tượng sử dụng kiểu dữ liệu gồm: cột trong bảng, tham số trong thủ tục lưu trữ, biến, hàm trả về giá trị, thủ tục lưu trữ trả về dữ liệu. - Chú thích: là các văn bản diễn giải trong chương trình (hay đoạn lệnh) mà trình biên dịch sẽ bỏ qua. Chú thích một dòng Chú thích nhiều dòng toàn bộ dòng lệnh hoặc một phần của dòng lệnh được chú thích. Nếu – được đặt ở đầu dòng thì toàn bộ dòng lệnh là chú thích toàn bộ các dòng lệnh và văn bản ở giữa cặp dấu /* ... */ được xem là chú thích. - Hàm: là tập lệnh Transact-SQL để thực hiện công việc nào đó. + Các hàm tập hợp dùng để thống kê dữ liệu: SUM(tên_cột) trả về tổng giá trị của cột COUNT(*) đếm số bản ghi trong bảng AVG(tên_cột) trả về giá trị trung bình của cột MAX(tên_cột) trả về giá trị lớn nhất của bản ghi COUNT(tên_cột) đếm số bản ghi trong bảng MIN(tên_cột) trả về giá trị nhỏ nhất của bản ghi + Hàm chuyển đổi kiểu dữ liệu: dùng để chuyển đổi kiểu dữ liệu, ngoài ra nó còn được dùng để hiển thị giá trị ngày tháng theo định dạng nào đó. CONVERT(datatype[(length)], expression[,style]) trong đó: datatype: kiểu dữ liệu cần chuyển sang. length: độ dài của dữ liệu. expression: chỉ ra tên cột, hằng số, hàm, biến, hoặc câu lệnh truy vấn con. style: chỉ ra kiểu hiển thị ngày tháng (dd/mm/yyyy hoặc mm/dd/yyyy). + Hàm ngày tháng: GETDATE() trả về ngày hiện tại của hệ thống DATEADD(datepart, number, date) cộng vào thêm number giá trị vào date DATEDIFF(datepart, date1, date2) so sánh giữa hai giá trị ngày tháng DATENAME(datepart, date) trả về giá trị ngày tháng dưới dạng chuỗi DATEPART(datepart, date) trả về một phần giá trị ngày tháng Trong các hàm ở trên dateparrt là một phần giá trị ngày tháng được kết hợp sử dụng trong hàm ngày tháng để lấy một phần nào đó giá trị ngày tháng. Ý nghĩa Giờ Phút Giây Miligiây Ngày của năm Ngày của tháng Tuần của năm Ngày của tuần Tháng Quý Năm datepart hh mi ss ms dy dd wk dw mm qq yy Giá trị 0-23 0-59 0-59 0-999 1-366 1-31 1-53 1-7 1-12 1-7 1753- 9999 + Hàm toán học ABS(num) trả về giá trị tuyệt đối của num ROUND(num,length) hàm làm tròn CEILING(num) trả về giá trị nhỏ nhất >=num SIGN(num) xác định dấu của num FLOOR(num) trả về giá trị lớn nhất >=num SQRT(num) hàm căn bậc hai POWER(num,y) hàm lũy thừa + Các hàm hệ thống DB_ID([‘tên_CSDL’]) trả về số định danh của CSDL OBJECT_ID (‘tên_đối_tượng’) trả về số định danh đối tượng DB_NAME ([‘id_CSDL’]) trả về tên CSDL OBJECT_NAME (‘id_đối_tượng’) trả về tên đối tượng HOST_ID() trả số định danh của máy trạm USER_ID(‘user’) Số định danh người dùng HOST_NAME() trả về tên của máy trạm USER_NAME(‘user’) trả về tên người dùng ISNULL(expr,value) nếu expr là Null thì thay thế bằng value + Hàm xếp hạng: ROW_NUMBER() hàm đánh số hàng, bắt đầu với 1 với hàng đấu tiên rồi sau đó tăng dần. DENSE_RANK() hàm trả về xếp hạng giữa các hàng mà không có khoảng cách giữa các hàng - Biểu thức: là kết hợp của nhiều phần tử toán tử, giá trị, định danh có thể ước tính và trả về kết quả. Trước khi thực hiện cần kiểm tra xem trường COUNTRY có nhập dữ liệu hay không bằng toán tử NULL + Biểu thức cơ bản: So sánh so sánh các giá trị dữ liệu Logic trả về đúng sai với AND, OR, NOT, LIKE, ANY, ALL hoặc IN Số học thực hiện phép tính số học +, -, *, /, ... Toán tử một ngôi toàn tử thực hiện trên một toàn hạng Toán tử nhị phân toán tử thực hiện trên các bit dữ liệu Hàm ghép chuỗi ghép các chuỗi thành một chuỗi Gán giá trị gán giá trị cho biến. + Biểu thức CASE: Biểu thức CASE so sánh: so sánh các biểu thức với giá trị và trả về kết quả tương ứng. CASE Biểu_thức WHEN Giá_trị_1 THEN Biểu_thức_kết_quả_1 [WHEN Giá_trị_2 THEN Biểu_thức_kết_quả_2 ...] [ ELSE Biểu_thức_kết_quả_N] END Biểu thức CASE tìm kiếm: tìm kiếm theo điều kiện, thỏa mãn để trả về kết quả tương ứng CASE WHEN bt_1 THEN kết_quả_bt_1 [WHEN bt _2 THEN kết_quả_bt_2 ...] [ ELSE biểu_thức_kết_quả_N] END THỰC THI LỆNH TRANSACT-SQL Xử lý từng câu lệnh + Câu lệnh SELECT là một câu lệnh phi thủ tục, nó không chỉ rõ các bước chính xác mà CSDL thực thi rút trích dữ liệu. + Server phải phân tích câu lệnh để xác định cách lấy dữ liệu hiệu quả nhất. Quá trình này gọi là tối ưu câu lệnh SELECT và đối tượng thực hiện tối ưu này gọi là bộ tối ưu truy vấn. Xử lý lô Là một hoặc nhiều câu lệnh Transact-SQL phân cách bởi từ GO được gửi cùng một lần để yêu cầu SQL Server xử lý. Các câu lệnh này được biên dịch thành một đơn vị thực thi (execution plan) và được thực thi cùng một lúc. Việc xử lý lô có ưu điểm: + Quản lý CSDL dễ dàng hơn; +Thủ tục lưu trữ (stored procedures) là nhóm các câu lệnh Transact-SQL được biên dịch thành một execution plan, vì thế tạo ra việc nhất quán trong việc lập trình ứng dụng như: những câu lệnh nào thực hiện lặp đi lặp lại thì ta xây dựng trong thủ tục lưu trữ để tăng hiệu quả thực thi, và các ứng dụng chỉ cần gọi tên thủ tục để thực thi. Thực thi dưới dạng kịch bản Các câu lệnh SQL có thể được thực thi dưới dạng tập tin được lưu trữ trong file với phẩn mở rộng của file thường được lưu dưới dạng *.sql. Thực thi sẽ được đọc khi được yêu cầu để thực thi. ĐẠI CƯƠNG SQL SERVER – Mai Đức Thạch 0165.859.7998 – TOÀN VẸN DỮ LIỆU (DATA INTEGRITY) ĐỊNH NGHĨA TOÀN VẸN DỮ LIỆU ► Trong CSDL, khi dữ liệu được lưu trữ hoặc cập nhật các giá trị nhập vào cần phải được kiểm tra tính hợp lệ và tính đúng đắn. ► Nếu có thao tác bất kỳ như sửa hay xóa nào trên dữ liệu, thì sự thay đổi đó cần phải được phản ánh đến tất cả các nơi khác trong CSDL có liên quan đến dữ liệu đó.tính hợp lệ và tính đúng đắn. ► Tính toàn vẹn của dữ liệu có thể được bảo quản (maintained) bằng cách đưa ra một số các kiểm tra nào đó tại thời điểm tạo bảng hoặc thời điểm chỉnh sửa bảng, sau đó các kiểm tra này được áp dụng khi quản lý dữ liệu. QUY TẮC TOÀN VẸN DỮ LIỆU Có một số các qui tắc(rule) cho toàn vẹn dữ liệu giúp đảm bảo tính đúng đắn và nhất quán của dữ liệu. Các qui tắc đó là: ► Trong một bảng không thể có hai bản ghi (record) mà giá trị ở tất cả các cột là như nhau. ► Chỉ có các giá trị dữ liệu hợp lệ mới có thể được thêm vào. ► Tính hợp lệ của dữ liệu vẫn phải được duy trì (đảm bảo) khi dữ liệu được chỉnh sửa. ► Với các bảng có quan hệ với nhau qua một cột chung, khi thay đổi dữ liệu trong cột chung ở bảng này nên được phản ánh phù hợp tới các bảng có liên quan. CÁC LOẠI TOÀN VẸN DỮ LIỆU CƠ CHẾ THỰC THI TOÀN VẸN DỮ LIỆU ► Constraints: là các thuộc tính, bạn có thể gắn cho các cột trong bảng để ngăn các dữ liệu không hợp lệ được nhập vào cột ► Default values: Bạn có thể định nghĩa các giá trị cho các cột không chấp nhận giá trị null. Khi thực hiện thêm hoặc sửa giá trị mặc định sẽ được chèn vào bản ghi tại các cột bị bỏ trống. ► Rules: là các constraint có thể được áp dụng để điều khiển các giá trị dữ liệu được nhập vào bảng. Các rule độc lập với việc định nghĩa bảng và có thể áp dụng cho nhiều bảng. ► Triggers: có chứa các đoạn code tạo thành các câu lệnh T-SQL được thi tự động khi có một sự kiện xác định xảy ra. TOÀN VẸN THỰC THỂ (Entity Integrity) Mỗi bảng trong CSDL biểu diễn cho một thực thể, trong đó mỗi record (dòng) bên trong bảng biểu diễn cho một thể hiện của thực thể. Toàn vẹn thực thể được đảm bảo bởi: - PRIMARY KEY: không cho phép chèn vào giá trị null hoặc giá trị trùng với các giá trị đã có. - UNIQUE: không cho phép nhập vào giá trị trùng nhau và cho phép nhập vào một giá trị null một lần. - INDEXES: có thể được dùng để ngăn cản nhập giá trị trùng nhau vào một cột để đảm bảo tính duy nhất của các giá trị - IDENTITY: được định nghĩa cho một cột định danh trong bảng. Cột định danh là cột có chứa các giá trị tuần tự do hệ thống phát sinh mỗi khi một record được thêm vào. Tất các giá trị trong cột này là duy nhất. TOÀN VẸN MIỀN GIÁ TRỊ (Domain Integrity) Một miền là một tập hợp các giá trị tạo ra những giá trị hợp lệ trong một cột. Để đảm bảo sự toàn vẹn về miền, tất cả các giá trị dữ liệu trong bảng nên thuộc về miền đã xác định. - FOREIGN KEY: Giá trị cho cột này có thể là null hoặc một giá trị tồn tại trong cột UNIQUE hoặc cột PRIMARY KEY mà cột có tham chiếu đến. - CHECK: chỉ ra một miền giá trị hợp lệ có thể được nhập vào cho cột. - DEFAULT: chỉ ra các giá trị cho những cột không chấp nhận giá trị null. Giá trị mặc định được tự động gán cho cột khi cột đó bị bỏ trống. - NOT NULL: chỉ ra rằng cột đó không chấp nhận giá trị NULL (không được để trống). - DATA TYPES: Kiểu dữ liệu hợp lệ đã được xác định cho nhiều cột khác nhau. Ví dụ: Khi cột được định nghĩa có kiểu dữ liệu là date thì chỉ chấp nhận các giá trị kiểu date. - RULES: là các ràng buộc xác định miền hoặc dạng giá trị hợp lệ cho một cột. Rule có thể định nghĩa độc lập sau đó áp dụng cho nhiều bảng TOÀN VẸN THAM CHIẾU (Referential Integrity) - Toàn vẹn tham chiếu đảm bảo tính nhất quán dữ liệu giữa các bảng có liên quan với nhau thông qua cột chung (trường chung). - Sử dụng khóa ngoại để thực hiện toàn vẹn tham chiếu. - Tính toàn vẹn tham chiếu được đảm bảo bởi các qui tắc sau: + Các giá trị chèn vào cột khóa ngoại là những giá trị bắt buộc phải có tồn tại cột UNIQUE hoặc khóa chính ở bảng tham chiếu. + Nếu giá trị trong cột UNIQUE hoặc khóa chính được sửa đổi, thì sự thay đổi tương tự cũng được thực hiện trên cột khóa ngoại. + Nếu một giá trị trong cột UNIQUE hoặc PRIMARY KEY bị xóa bỏ, sự xóa bỏ cũng nên được thực hiện trong các cột khóa ngoại. TOÀN VẸN DO NGƯỜI DÙNG ĐỊNH NGHĨA (User-defined Integrity) - Các ràng buộc toàn vẹn có sẵn trong SQL Server 2005 vẫn chưa đủ để đảm bảo các giá trị dữ liệu là đúng theo dạng (format) hoặc miền theo mong muốn. Do đó các ràng buộc do người dùng định nghĩa có thể được áp dụng cho các cột để duy trì tính toàn vẹn dữ liệu. - Ví dụ: Số điện thoại yêu cầu phải có dạng như (123) 456-789. - Như vậy có thể tạo kiểu dữ liệu do người dùng định nghĩa để nhận vào giá trị theo dạng này. RÀNG BUỘC TOÀN VẸN Các ràng buộc được sử dụng để đảm bảo tính hợp lệ của dữ liệu trong một bảng và đảm bảo tính nhất quán dữ liệu giữa các bảng liên quan. Bạn có thể định nghĩa các ràng buộc cho cột hoặc cho bảng. RÀNG BUỘC PRIMARY KEY - Mục đích của cột khóa chính (PRIMARY KEY) là để xác định tính duy nhất của mỗi record có trong bảng. - Ràng buộc PRIMARY KEY kiểm tra sự duy nhất của dữ liệu trong cột khóa chính, không cho đưa vào các giá trị trùng nhau. - Một PRIMARY KEY có thể là một cột hoặc là sự kết hợp của nhiều cột. - Ràng buộc PRIMARY KEY có thể được chỉ ra bên trong câu lệnh CREATE TABLE hoặc ALTER TABLE. CREATE TABLE ({ [CONSTRAINT tên_constraint] [PRIMARY KEY] } [,...n] ) RÀNG BUỘC UNIQUE - Ràng buộc UNIQUE có thể áp dụng cho một cột hoặc cho kết hợp nhiều cột để đảm bảo tính duy nhất của dữ liệu trong những cột này. Ràng buộc UNIQUE đảm bảo tính toàn vẹn thực thể trong bảng. Trong một bảng có thể có nhiều ràng buộc UNIQUE cho nhiều cột. Với cột áp dụng ràng buộc UNIQUE, Giá trị null chỉ được phép nhập vào một lần. - Ràng buộc UNIQUE có thể được định nghĩa bên trong câu lệnh CREATE TABLE hoặc ALTER TABLE. ALTER TABLE ADD CONSTRAINT tên_constraint UNIQUE (tên_cột) RÀNG BUỘC FOREIGN KEY - Ràng buộc khóa ngoại (FOREIGN KEY) được dùng khi bạn muốn kiểm tra tính tồn tại của dữ liệu. Nghĩa là giá trị nhập vào cho cột khóa ngoại bắt buộc phải có trong cột PRIMAY KEY hoặc UNIQUE của bảng tham chiếu. Cột FOREIGN KEY, PRIMARY KEY hoặc UNIQUE phải có cùng kiểu dữ liệu. - Các tùy chọn cascading được định nghĩa cùng với việc tạo ràng buộc khóa ngoại để đảm bảo tính nhất quán dữ liệu giữa các bảng liên khi thực hiện thay đổi trên cột PRIMARY KEY hoặc UNIQUE. + ON UPDATE [NO ACTION | CASCADE]: Chỉ cho SQL SERVER cách thi hành khi có hành động cập nhật xảy ra trên cột PRIMARY KEY hoặc UNIQUE của bảng tham chiếu. + NO ACTION: Báo lỗi khi bên cột khóa ngoại có các giá trị có liên quan tới giá trị bị sửa trong cột PRIMARY KEY hoặc UNIQUE.  CASCADE: Khi cập nhật giá trị ở cột PRIMARY KEY hoặc UNIQUE, các giá trị trong cột RÀNG BUỘC CHECK - Ràng buộc CHECK xác định một miền giá trị nhập vào cho cột. - Ràng buộc CHECK được tạo với biểu thức logic (là biểu thức có giá trị trả về True hay False) để kiểm tra giá trị nhập vào. - Bạn có thể áp dụng một ràng buộc CHECK tới một nhóm các cột. - Cú pháp: CREATE TABLE ({ [CONSTRAINT ] [CHECK(bt_logic)} [,n ] ) RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – - CƠ SỞ DỮ LIỆU .ndf .ndf .ndf Tập tin sơ cấp (Primary Data Files) Tập tin thứ cấp (Secondary Data Files) Tập tin ghi vết (Transaction Log Files) - Các dòng dữ liệu của bảng được lưu trong các trang dữ liệu (data page). Ngoài ra, còn có các trang index để lưu trữ index của bảng, - Kích thước của mỗi trang là 8KB, và được bắt đầu bằng 96 byte header lưu các thông tin của trang như: + số trang (page number), + kiểu trang (page type), + không gian còn trống trên trang, + con trỏ trỏ đến các trang trước hoặc kế tiếp. - Các trang index giúp truy cập trực tiếp dòng bất kỳ trong bảng. Có 2 phương pháp được sử dụng để tổ chức các trang của bảng. + Các bảng có một clustered index (bảng clustered) + Các bảng không có clustered index (heap). tổ chức dữ liệu HEAP SQL Server truy xuất dữ liệu bằng cách quét toàn bộ bảng (table scan). Một bảng được quét như sau: + Bắt đầu từ trang dữ liệu đầu tiên của bảng. + Quét từ trang này tới trang khác lần lượt tất cả các dòng có trong bảng. Index Allocation Maps (IAMs) được sử dụng để di chuyển từ trang này tới trang khác trong một heap. Với bảng có clustered index, các liên kết trang được sử dụng để di chuyển từ trang này tới trang khác + Đọc từng dòng trong bảng và rút(extract) ra các dòng thỏa mãn điều kiện của truy vấn. + Là cách tốt nhất cho việc truy xuất các bảng nhỏ. CLUSTERED SQL Server sử dụng truy xuất index (indexed access) để truy xuất dữ liệu. Truy xuất index được thực hiện như sau: - Duyệt cấu trúc cây index để tìm các dòng mà truy vấn yêu cầu. - Đọc và lấy(extract) ra các dòng thỏa mãn điều kiện của truy vấn. - Là cách tốt nhất để truy xuất các dòng và dãy các dòng từ một bảng lớn. Mục đích của việc sử dụng index: nhắm được tạo trên các bảng để sắp xếp dữ liệu; từ đó, các record được lấy dễ dàng hơn khi dữ liệu được sắp xếp. SQL Server sử dụng index tương tự như mục lục của quyển sách. Index có chứa thông tin cho phép bạn tìm dữ liệu được chỉ ra mà không phải quét toàn bộ bảng. Các truy vấn trên bảng index được thực thi nhanh hơn và chiếm ít tài nguyên hơn. TẠO FILE INDEX - Tăng tốc độ truy xuất dữ liệu. - Đảm bảo tính duy nhất của các dòng. ►Nên tạo file index: + Cho khóa chính, khóa ngoại. + Thường xuyên tìm kiếm theo một miền. + Thường xuyên truy cập theo thứ tự. KHÔNG TẠO FILE INDEX - Tiêu tốn không gian đĩa. - Incurs overhead. ►Không nên tạo file index: + Trên các cột hiếm khi được tham chiếu đến trong các câu truy vấn. + Cột có ít giá trị duy nhất. + Các cột có kiểu dữ liệu là bit, text, hoặc images. INDEX KIẾN TRÚC INDEX - Trong SQL Server, các chỉ mục được tổ chức giống như các cây nhị phân (B-tree). Mỗi trang trong cây chỉ mục index B-tree được gọi là nút chỉ mục (index node). + Nút lá có thể là một trang dữ liệu (data page) hoặc cũng có thể là trang chỉ mục (index page) chứa các chỉ mục trỏ tới các dòng dữ liệu. + Nút gốc và nút trung gian có chứa một trang chỉ mục (index page) nắm giữ các chỉ mục dòng (index row). + Mỗi Chỉ mục dòng có chứa một giá trị khóa và một con trỏ trỏ tới một nút trung gian khác, hoặc trỏ tới một dòng dữ liệu trong nút lá. - Phân vùng bảng(Table Partitionning) + Phân vùng là kỹ thuật phân chia logic bảng hoặc index thành một hoặc nhiều phân vùng(partition) nhằm quản lý hiệu quả cơ sở dữ liệu với dung lượng lớn. + Mỗi phân vùng chứa các dòng dữ liệu được tổ chức theo cấu trúc heap hoặc cấu trúc clustered index. + Phân vùng cho phép truy xuất dữ liệu nhanh chóng và hiệu quả, do thao tác quản lý chỉ thực hiện trên tập con dữ liệu thay vì trên toàn bộ bảng. Table partitioning là kỹ thuật phân chia bảng thành từng đoạn nhằm quản lý hiệu quả cơ sở dữ liệu với dung lượng lớn. Đối với các ứng dụng truy cập từ bên ngoài, bảng (table) vẫn là một bảng duy nhất, chỉ có cấu trúc vật lý của nó là khác so với các bảng không phân đoạn. Bảng được phân đoạn dựa vào giá trị một trường của nó (trường được chọn gọi là partition key). Ví dụ bạn có dữ liệu về các giao dịch bán hàng chứa trong bảng BanHang, bạn có thể phân đoạn theo năm của trường NgayGiaoDich (ngày giao dịch): các giao dịch xảy ra trong năm 2009 được nằm trong một đoạn riêng, tương tự với các giao dịch của năm 2010 Kỹ thuật này làm tăng khả năng mở rộng của SQL Server lên rất nhiều, và giúp cho việc quản trị các cơ sở dữ liệu lớn trở nên dễ dàng hơn. Thử hình dung với một bảng dữ liệu chứa vài trăm triệu bản ghi thường xuyên được cập nhật, các tác vụ như backup/restore, hoặc create/rebuild index đều rất tốn kém thời gian. Việc truy vấn hoặc sửa đổi dữ liệu cũng rất vất vả Table partitioning nhằm giải quyết các trở ngại đó, nó có các ưu điểm chính sau: 1. Tiện lợi về quản trị - Bạn có thể backup/restore một đoạn mà không ảnh hưởng đến các đoạn còn lại: ví dụ tại thời điểm năm 2010 thì các đoạn chứa dữ liệu của 2009 và các năm trước không còn tiếp nhận dữ liệu mới nữa, bạn không cần phải thường xuyên backup các đoạn này và chỉ cần backup đoạn 2010. - Bạn cũng có thể REBUILD lại index trên từng đoạn (những đoạn cần phải REBUILD do có nhiều thao tác xóa, sửa) thay vì trên toàn bộ bảng. - Nó cũng cho phép nhanh chóng loại bỏ dữ liệu nguyên một đoạn ra khỏi bảng thay vì phải dùng lệnh DELETE (thao tác này gọi là SWITCH-OUT). Tương tự nó cũng cho phép “nạp” dữ liệu từ một bảng khác vào thành một đoạn mới (SWITCH-IN). Tính năng này rất có giá trị đối với các ứng dụng ETL và Datawarehouse. Ví dụ bạn cần import dữ liệu của năm 2008, bạn có thể import vào một bảng riêng và sau đó switch-in bảng này vào bảng chính một cách tức thì. Trước khi có partitioning, bạn phải dùng lệnh INSERT để chuyển dữ liệu từ bảng riêng vào bảng chính. Quá trình này mất nhiều thời gian hơn và trong suốt quá trình đó bảng bị khóa và không thể truy cập được. 2. Cải tiến về hiệu năng - Khi một câu lệnh chỉ cần lấy dữ liệu ở một đoạn nào đó thì hệ thống chỉ cần truy nhập vào đoạn đó và bỏ qua các đoạn còn lại (tính năng này gọi là partition elimination) - Khi các đoạn dữ liệu được lưu trữ ở các ổ cứng khác nhau sẽ làm giảm tranh chấp vào/ra giữa các câu lệnh. Ví dụ hai câu lệnh SELECT và UPDATE hoạt động trên cùng một bảng nhưng ở hai đoạn khác nhau có thể thực hiện hoàn toàn song song với nhau. Việc phân đoạn bảng dựa trên hai khái niệm mới sau đây: · Partition function: qui định giá trị biên cho các đoạn. Hệ thống dựa vào hàm này để xác định đoạn mà mỗi bản ghi thuộc vào. · Partition scheme: ánh xạ các đoạn khai báo trong partition function vào các filegroup (mỗi đoạn được lưu trữ tại một filegroup). Nút cao nhất của cây gọi là nút gốc (root node) Nút bất kỳ nằm giữa nút lá và nút gốc gọi là nút mức trung gian (intermediate node) Nút ở mức thấp nhất gọi là nút lá (leaf node) CẤU TRÚC HEAP (HEAP STRUCTURES) - Trong cấu trúc heap, các trang dữ liệu và các dòng dữ liệu không được sắp xếp theo thứ tự cụ thể nào. Các dòng và các trang dữ liệu không liên kết(link) với nhau mà chỉ được kết nối(connenction) luận lý (logical) qua thông tin được ghi trong các trang IAM (Index Allocation Map - Bản đồ cấp phát chỉ mục). SQL Server 2005 sử dụng các trang IAM để duyệt một cấu trúc heap. Bạn có thể đọc một heap bằng cách duyệt các trang IAM để tìm các extent có chứa những trang cho heap đó. - Sự phân vùng của cấu trúc heap: mặc định, mỗi heap chỉ có một phân vùng; khi các phân vùng được tạo cho một bảng có cấu trúc heap, mỗi phân vùng sẽ chứa dữ liệu trong một cấu trúc heap riêng biệt. RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – CÁC LOẠI INDEX Clustered index lưu trữ dữ liệu theo kiểu sắp xếp. Nó thực thi như một cấu index B- tree. Một bảng chỉ có thể có một clustered index vì clustered index xác định thứ tự lưu trữ vật lý của dữ liệu. Nonclustered index không sắp xếp lại thứ tự vật lý của dữ liệu. Chúng chỉ tạo các con trỏ trỏ vị trí vật lý của các dòng dữ liệu. SQL Server 2005 hỗ trợ tới 249 nonclustered cho một bảng. Unique index có thể được định nghĩa trên một cột có chứa giá trị không trùng nhau. - Nếu bảng có một PRIMARY KEY, một unique clustered index được định nghĩa tự động trên cột đó. - Nếu bảng có một cột với ràng buộc UNIQUE, một unique nonclustered index được tự động tạo trên cột đó. Composite index (index kết hợp) là một index được tạo trên hai hay nhiều cột. Cả clustered index và nonclustered index đều có thể là composite index Full-Text index cho phép thực hiện các truy vấn phức tạp trên dữ liệu kí tự. Sử dụng tính năng Full-Text index, việc tìm kiếm có thể thực hiện trên từng từ riêng biệt, cụm từ, đoạn văn, một phần của từ, hoặc biến thể của từ (drunk biến thể của drink) DỰA THEO CẤU TRÚC DỰA THEO ĐẶC ĐIỂM XML Index được xem như là một index XML. Clusered index và nonclustered index đều có thể tạo trên cột dữ liệu XML. TẠO INDEX Các quy tắc khi tạo Index: - Một index có thể có tối đa 16 cột. - Quá nhiều index làm giảm hiệu suất thực thi của các câu lệnh INSERT, UPDATE và DELETE. - Các index nên sử dụng cho các bảng có khối lượng dữ liệu lớn nhưng có yêu cầu về cập nhật thấp để cải thiện hiệu suất truy vấn. - Ngay cả bảng có dữ liệu nhỏ vẫn nên duy trì các index, vì sau này dữ liệu vẫn còn được thêm vào bảng. Xem thông tin index: SQL Server 2005 cho phép bạn xem các thuộc tính, không gian được sử dụng bởi index của các tất cả index đã được định nghĩa trên bảng. Có thể xem thông tin bằng hai cách sau: - Dùng thủ tục nội tại sp_helpindex. sp_helpindex “” - Dùng SQL Server Management Studio. CREATE CLUSTERED INDEX ON (tên_cột) CLUSTERED INDEX INDEX NON CLUSTERED UNIQUE INDEX CREATE NONCLUSTERED INDEX ON (tên_cột) CREATE UNIQUE INDEX ON (tên_cột) SQL Server 2005 không cho phép tạo unique index trên các cột có chứa các giá trị trùng nhau COMPOSITE INDEX CREATE INDEX ON (tên_cột [ASC | DESC], [,.n]) COMPUTED INDEX Cột Computed là một cột ảo trong bảng, giá trị của nó được tính toán dựa trên biểu thức khi thực thi. - Cú pháp tạo bảng có cột computed CREATE TABLE ( [ AS ]) - Cú pháp tạo index trên cột computed CREATE INDEX ON (tên_cột_computed) CẤU TRÚC CLUSTERED INDEX - Clustered index sắp xếp dữ liệu trong một bảng theo thứ tự của khóa index. Toàn bộ bảng trở thành cây index. Các node lá chứa khóa index và đồng thời chứa tất cả các trường còn lại. Kích thước của một clustered index khoảng 5% kich thước của bảng. Trước khi tạo clustered index, bạn cần phải đảm bảo hệ thống của mình còn không gian trống tối thiểu gấp 1, 2 lần so với dữ liệu trong bảng. - Nguyên tắc (Guidelines) tạo clustered chỉ mục + Chỉ có thể tối đa một clustered index cho mỗi bảng. + Clustered index có thể chứa một hoặc nhiều trường. + Khi tạo khóa chính(Primary Key) cho bảng, mặc định một clustered index được tự động tạo cùng với nó cho bảng đó. + Nếu bảng không có khóa chính, thì clustered index nên được định nghĩa trên: ● Các cột khóa mở rộng thường được dùng cho việc tìm kiếm. ●Cột được sử dụng trong các truy vấn trả về kết quả tập hợp lớn. ● Các cột mà dữ liệu ít trùng nhau. ● Các cột được dùng để nối hai bảng. Không đánh chỉ mục các cột trong các trường hợp sau: - Cột chỉ chứa chỉ một số ít các giá trị duy nhất.. Bảng chỉ chứa một số ít hàng. Việc đánh chỉ mục trên các bảng nhỏ có thể không là tối ưu vì nó khiến cho SQL Server mất nhiều thời gian để duyệt chỉ mục để tìm kiếm dữ liệu hơn là thực hiện việc tìm kiếm tuần tự từng hàng trong bảng. Trường tĩnh: Trường clustered index không nên bị cập nhật thường xuyên, một khi đã có mặt trong bảng thì giá trị của nó cần được giữ nguyên. Khi nó bị cập nhật, bản thân clustered index cũng cần được cập nhật để sắp xếp bản ghi vào vị trí mới cho đúng thứ tự, và đồng thời các nonclustered index khác cũng phải cập nhật theo để cho con trỏ giờ phải chứa giá trị mới. Thao tác cập nhật trường clustered index do vậy rất tốn kém và nếu diễn ra thường xuyên, cũng làm cho clustered index bị phân mảnh. CẤU TRÚC NONCLUSTERED INDEX - Một chỉ mục non-clustered xác định cách sắp xếp logic của bảng. Vì vậy, một bảng có thể có rất nhiều chỉ mục non- clustered (có thể lên tới 249). Một chỉ mục non-clustered giống như một chỉ mục trong một cuốn sách giáo khoa. Dữ liệu được lưu trữ trong một nơi, chỉ mục ở một nơi khác cùng với các con trỏ trỏ tới vị trí lưu trữ của dữ liệu. + Các nhãn trong chỉ mục được lưu trữ theo thứ tự các giá trị khoá của chỉ mục, nhưng thông tin trong bảng không hề thay đổi. Nếu không có chỉ mục clustered nào được tạo ra trên bảng, các hàng không có một trật tự nhất định nào. Một chỉ mục non-clustered là một chỉ mục trong đó thứ tự logic của chỉ mục không trùng khớp với thứ tự vật lý, thứ tự lưu trữ của các hàng trên ổ đĩa. + SQL tìm kiếm một giá trị dữ liệu bằng cách tìm theo chỉ mục non-clustered để xác định được vị trí của dữ liệu trong bảng và sau đó nhận về trực tiếp dữ liệu từ vị trí đó. Qúa trình này giống như việc sử dụng chỉ mục trong một quyển sách. Nếu bảng dữ liệu phía dưới được sắp xếp sử dụng một chỉ mục clustered, vị trí là giá trị của khoá; Ngược lại, vị trí là ID của hàng bao gồm số hiệu file, số trang, và số hiệu của hàng. - Nonclustered index có cấu trúc B-tree giống như clustered index, nhưng có một số sự khác biệt sau: + Thứ tự vật lý các dòng dữ liệu trong bảng không lưu trữ theo thứ tự được qui định trong khóa nonclustered. + Trong cấu trúc nonclustered index, mỗi node lá chứa khóa index và con trỏ trỏ đến trang. ● Nếu bảng có clustered index, con trỏ này chính là khóa clustered index ● Nếu bảng không có clustered index, con trỏ này là RowID, một dạng định danh bản ghi kết hợp fileID + pageID+ offset. - Nonlustered index được sử dụng khi bạn muốn mở rộng thêm nhiều cách tìm kiếm dữ liệu khác nhau. Trước khi tạo, cần xem xét một số các nguyên tắc sau: + Khi một clustered index được tạo lại hoặc sử dụng tùy chọn DROP_EXISTING, SQL Server xây dựng lại các nonclustered index. + Tạo clustered index trước khi tạo nonclustered index, làm như vậy để không phải xây dựng lại nonclustered index khi tạo một clustered index. Tìm kiếm các dòng với Nonclustered index - Nonclustered index tương tự như một mục lục quyển sách. - Các con trỏ trong nút mức lá của index trỏ tới vị trí lưu trữ dữ liệu của bảng. - Khi thực hiện tìm kiếm các dòng bằng các nonclustered index, câu lệnh SELECT được dùng cùng với các cột nonclustered index trong mệnh đề WHERE. Tìm kiếm các dòng với clustered index - Các clustered index lưu trữ các dòng dữ liệu dựa trên các giá trị khóa của chúng. - Khi thực hiện tìm kiếm các dòng bằng các clustered index, câu lệnh SELECT được dùng cùng với các cột clustered index được chỉ ra trong mệnh đề WHERE. Tìm kiếm các dòng khi không có các index - Khi không có index tạo cho bảng, SQL Server sử dụng các view catalog để tìm các dòng. - SQL Server sử dụng sys.indexes để tìm ra trang IAM. Trang IAM có chứa danh sách tất cả các trang của một bảng, qua đó SQL Server có thể đọc tất cả các trang dữ liệu. - Khi view sys.indexes được sử dụng, query optimizer kiểm tra tất cả các dòng trong một bảng và chỉ rút ra các dòng được tham chiếu trong truy vấn. Điều đó làm phát sinh nhiều thao tác nhập/xuất và sử dụng nhiều tài nguyên. TÌM KIẾM CÁC DÒNG DỮ LIỆU Đơn vị cấp phát (Allocation Unit): là một tập hợp các trang nằm trong một heap hoặc B-tree, được sử dụng để quản lý dữ liệu dựa trên kiểu trang của chúng. Các kiểu đơn vị cấp phát được sử dụng để quản lý dữ liệu trong các bảng và index: - IN_ROW_DATA: được dùng để quản lý dữ liệu hoặc các dòng chỉ mục mà có chứa tất cả các loại dữ liệu ngoại trừ đối tượng dữ liệu lớn (LOB). - LOB_DATA: được dùng để quản lý các đối tượng dữ liệu lớn (LOB) như varbinary(max), varchar(max) , và xml. - ROW_OVERFLOW_DATA: được dùng để quản lý dữ liệu có độ dài biến đổi như dữ liệu có kiểu varchar, nvarchar, varbinary, hoặc sql_variant RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – Hệ số tùy chọn FILLFACTOR chỉ ra tỷ lệ điền đầy(dữ liệu vào) các trang mức lá của index. Khi tạo hoặc dựng lại(rebuild) index, SQL Server sẽ ghi đầy dữ liệu vào trang mức lá tới tỷ lệ được xác định trong FILLFACTOR, và phần còn lại của trang được bỏ trống để sau này sử dụng. Giá trị có chủa nó từ 1 đến 100 (mặc định và được sủ dụng cho các bảng chỉ đọc). CREATE INDEX ON (tên_cột) WITH (FILLFACTOR=n) Tùy chọn PAD_INDEX dùng để xác định khoảng trống để lại trên mỗi trang (page) mức trung gian (intermediate level) của index cho việc tăng trưởng sau này.Tùy chọn này chỉ có ích khi Fillfactor được xác định, bởi vì nó sử dụng tỷ lệ được xác định bởi hệ số FILLFACTOR. Nếu tùy chọn này không được chỉ định hoặc được thiết lập OFF. SQL Server vẫn đảm bảo rằng mỗi trang index mức trung gian có đủ khoảng trống để chứa ít nhất một dòng. CHỈNH SỬA INDEX Khi các index càng tăng trưởng, nó bắt đầu có sự phân mảnh. Sự phân mảnh làm giảm tốc độ truy vấn lấy dữ liệu, và hao tổn tài nguyên. Để giảm sự phân mảng, index cần được sửa lại. Câu lệnh ALTER INDEX cùng với các tùy chọn được sử dụng cho việc bảo trì các index. ALTER INDEX ON {REBUILD [WITH (PAD_INDEX= {on|off} | FILLFACTOR=n%) ] | Disable | REORGANIZE } THAO TÁC INDEX TRỰC TUYẾN - Các thao tác index trực tuyến bao gồm: tạo mới, tạo lại, và xóa các index. Khi có một thao tác index được thực hiện, các người dùng khác sẽ bị hạn chế truy xuất dữ liệu của bảng cho tới khi thao tác được hoàn thành. Để khắc phục hạn chế trên, SQL Server 2005 cung cấp tùy chọn ONLINE để cho phép nhiều người dùng vẫn có thể truy cập dữ liệu của bảng trong khi thao tác index đang thực hiện. - Các lệnh SQL cho thao tác index trực tuyến + Tạo: CREATE INDEX ON (tên_cột)[,n]) WITH (ONLINE={ON|OFF}) + Sửa: ALTER INDEX ON REBUILD WITH (ONLINE={ON|OFF}) + Xóa: DROP INDEX ON WITH (ONLINE={ON|OFF}) + Sửa bảng: ALTER TABLE DROP CONSTRAINT WITH (ONLINE= {ON|OFF}) THAO TÁC INDEX SONG SONG Với máy tính có đa bộ xứ lý, SQL Server 2005 có thể sử dụng nhiều hơn một bộ xử lý để thực thi các thao tác index. Điều này được gọi là thao tác index song song. Các index lớn sử dụng nhiều bộ xử lý, điều đó làm hạn chế tính sẵn sàng của các bộ xử lý cho những ứng dụng khác. Để khắc phục hạn chế, bạn cần xác định tùy chọn MAXDOP để giới hạn số bộ xử lý được dùng bởi các thao tác index. Cú pháp: ALTER INDEX ON WITH (MAXDOP=max_degree_of_parallelism) KHÓA LOCKING SQL Server cung cấp tính năng ngăn cản nhiều người dùng cùng đồng thời thực hiện cập nhật trên cùng dữ liệu. Sử dụng tính năng locking, một người dùng này có thể ngăn người dùng khác chỉnh sửa dữ liệu mà mình đang thực hiện cập nhật. Trong SQL Server 2005 cho phép khóa ở các mức sau: Dòng; Bảng; Trang; Cơ sở dữ liệu. Việc thực hiện khóa riêng từng dòng trong bảng sẽ làm gia tăng số lượng khóa, tốn tài nguyên để lưu trữ khóa. Query optimizer cho phép áp dụng khóa trang để giảm bớt tài nguyên được dùng cho việc lưu khóa. Hai tùy chọn sau được dùng trong câu lệnh CREATE INDEX và ALTER INDEX: ALLOW_ROW_LOCK; ALLOW_PAGE_LOCK. Cú pháp: CREATE INDEX ON (tên_cột) WITH (ALLOW_ROW_LOCKS={ON|OFF}, ALLOW_PAGE_LOCKS={ON|OFF} ) INDEX PHỨC HỢP Một index phức hợp (Composite index) không thể sử dụng nhiều hơn 16 cột khóa và kích thước tối đa là 900 byte. Để mở rộng thêm chức năng của các nonclustered index, SQL Server 2005 cho phép bạn có thể đưa thêm các cột không khóa vào những index này. Có thể thêm tối đa 1023 cột không khóa, nhưng vẫn bắt buộc phải có chứa một cột khóa. Cú pháp: CREATE NONCLUSTERED INDEX ON (tên_cột,) INCLUDE (tên_cột,) VÔ HIỆU HÓA INDEX Vô hiệu hóa index nhằm giới hạn người dùng truy xuất index. Nếu vô hiệu hóa một clustered index, người dùng chỉ bị giới hạn truy xuất dữ liệu của bảng đó, còn thông tin trong index không bị gỡ bỏ. Khi nâng cấp SQL Server lên phiên bản mới, các index đã được định nghĩa trên bảng sẽ tự động bị vô hiệu. ALTER INDEX Tên_index ON Tên_bảng DISABLE XÓA INDEX: DROP INDEX Tên_index ON Tên_bảng TỔ CHỨC INDEX Mỗi khi index được tạo, thông tin của index được lưu trong các trang index. Các trang index được sắp xếp liên tiếp và liên kết với nhau bởi các con trỏ. Khi thực hiện thêm hoặc xóa dữ liệu sẽ làm cho thông tin của index bị rải rác, điều này gọi là sự phân mảnh. Sự phân mảnh làm giảm tốc độ thực thi truy vấn và thời gian đáp ứng sẽ lâu hơn. SQL Server 2005 cho phép tổ chức lại index theo định kỳ để giảm sự phân mảnh và tăng tốc độ đáp ứng. SỰ PHÂN MẢNH INDEX Tổ chức lại Index là index trong các trang hiện có được sắp xếp lại thứ tự, các trang index trống bị loại bỏ, nhằm bỏ đi sự phân mảnh trong clustered index và nonclustered index. Cú pháp tổ chức lại index: ALTER INDEX Tên_index ON Tên_bảng REORGANIZE TỔ CHỨC LẠI INDEX Xây dựng lại index là xóa index đang tồn tại và tạo lại index mới có cùng tên. Việc xây dựng lại index, sự phân mảnh trong index trước được loại bỏ hoàn toàn. + Cú pháp tổ chức lại index bằng câu lệnh ALTER INDEX ALTER INDEX Tên_index ON Tên_bảng REBUILD + Cú pháp tạo index mới với từ khóa DROP_EXISTING để xây dựng lại Index. CREATE [UNIQUE | CLUSTERED | NONCLUSTERED] INDEX Tên_index ON Tên_bảng(tên_cột) WITH ( DROP_EXISTING= ON | OFF ) XÂY DỰNG LẠI INDEX PHÂN VÙNG INDEX (PARTITIONED INDEX) Phân vùng một bảng hay index là chia dữ liệu thành nhóm các dòng nhỏ hơn. Thực hiện thao tác quản lý trên các nhóm dòng sẽ hiệu quả hơn, vì chỉ thực hiện trên một khối nhỏ dữ liệu thay vì trên toàn bộ bảng hay index. Các bước tạo phân vùng index: - Bước 1: Tạo một hàm phân vùng (Partition Function) CREATE PARTITION FUNCTION Tên_hàm_phân_vùng(kiểudl_của_tham_số) AS RANGE [LEFT | RIGHT] FOR VALUES (Giá_trị_1, ,Giá_trị_n) - Bước 2: Tạo một lược đồ phân vùng (Partition Scheme) CREATE PARTITION SCHEME Tên_lược_đồ_phân_vùng AS PARTITION Tên_hàm_phân_vùng ALL TO (tên_file_group | [PRIMARY] , ) - Bước 3: Tạo bảng mới có phân vùng hoặc index phân vùng CREATE TABLE Tên_bảng (tên_cột kiểudl, ) ON Tên_lược_đồ_phân_vùng (tên_cột_phân_vùng) Cú pháp tạo index phân vùng CREATE [UNIQUE | CLUSTERED | NONCLUSTERED] INDEX Tên_index ON Tên_bảng(tên_cột) [ON Tên_lược_đồ_phân_vùng(Tên_cột_phân_vùng)] XML INDEX XML index sơ cấp (Primary XML index): được tạo cho mỗi cột XML để tăng tốc độ truy vấn trên những cột này. XMl index chỉ có thể tạo được nếu trên bảng đã có khóa chính và có thể tạo được một XML index sơ cấp cho mỗi cột XML. CREATE PRIMARY XML INDEX Tên_index ON Tên_bảng(tên_cột_xml) XML index thứ cấp (Secondary XML index): được dùng để: tìm kiếm các giá trị ở vị trí bất kỳ trong tài liệu XML; lấy các thuộc tính của đối tượng cụ thể trong tài liệu XML; XML index thứ cấp chỉ có thể tạo trên các cột đã có XML index sơ cấp. Có thể tạo nhiếu XML index thứ cấp trên cùng một bảng đã có XML sơ cấp. CREATE XML INDEX Tên_index ON Tên_bảng(tên_cột_xml) USING XML INDEX tên_index_xml FOR VALUE | PATH | PROPERTY RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – THỦ TỤC NỘI TẠI (STORED PROCEDURE) ĐỊNH NGHĨA - Stored Procedure là một khối các câu lệnh T-SQL thực hiện một tác vụ cụ thể, đã được biên dịch và lưu trữ trong SQL Server dưới một cái tên nào đó và được xử lý như một đơn vị. - Stored procedure được dùng cho những công việc được thực hiện nhiều lần. - Các câu lệnh T- SQL trong thủ tục nội tại gồm: khai báo biến, các cấu trúc điều khiển, - Thủ tục nội tại có thể được gọi từ phía client và kết quả trả về thông các qua tham số ra, hoặc trả về tập các bản ghi. LỢI ÍCH CỦA THỦ TỤC NỘI TẠI - Nâng cao độ bảo mật: :người quản trị CSDL có thể nâng cao tính bảo mật bằng cách kết hợp các đặc quyền CSDL với stored procedure. Người quản trị CSDL không cho phép người dùng có quyền truy xuất trực tiếp tới các bảng hoặc các view, nhưng cấp quyền thực thi các stored procedure có liên quan tới chúng cho người dùng. - Thực thi đoạn mã đã được biên dịch trước: Stored procedure được biên dịch trong lần thực thi đầu tiên. Mỗi lần thực thi về sau, SQL Server sử dụng lại bản đã biên dịch trước đó. - Giảm lưu lượng trao đổi giữa client và server: Tất cả các câu lệnh T-SQL trong stored procedured được thực thi cùng nhau như một khối. Do vậy mà đường truyền mạng sẽ không phải sử dụng riêng cho từng câu lệnh khi chúng được thực thi. - Tái sử dụng mã: Stored procedure có thể được sử dụng nhiều lần. Do vậy, mỗi khi thực hiện một bài toán tương tự bạn không cần phải gõ lại hàng trăm câu lênh T-SQL. THỦ TỤC NỘI TẠI HỆ THỐNG - Storered procedure hệ thống là các thủ tục đã được định nghĩa sẵn trong SQL Server. Bảng hệ thống mặc định được tạo lúc tạo một CSDL mới. Các bảng này lưu thông tin về các đối tượng người dùng định nghĩa như bảng, view Người dùng không thể dùng thủ tục nội tại hệ thống để truy xuất hoặc cập nhật bảng hệ thống trừ khi được người quản trị CSDL cấp quyền. Người quản trị CSDL là chủ sở hữu các thủ tục nội tại hệ thống - Storered procedure hệ thống được sử dụng trong quản trị CSDL. - Các storered procedure hệ thống cung cấp truy cập dễ dàng tới các thông tin metadata về các đối tượng CSDL như bảng hệ thống, bảng do người dùng định nghĩa, view, và index. - Storered procedure hệ thống xuất hiện trong lược đồ sys của hệ thống và các CSDL người dùng định nghĩa. Khi tham chiếu đến, bộ định danh lược đồ sys được sử dụng. - Storered procedure hệ thống được lưu trữ vật lý trong CSDL Resource và có tên bắt đầu bằng ‘sp_’ - Thủ tục nội tại hệ thống được phân thành các nhóm sau + Các thủ tục liệt kê danh mục (catalog): Danh mục hệ thống (system catalog) là một tập các bảng lưu trữ tất cả thông tin về các bảng có trong CSDL người dùng. Thủ tục danh mục được sử dụng để truy xuất các thông tin trong danh mục hệ thống. + Các thủ tục bảo mật: Là các thủ tục hệ thống giúp cho việc quản lý bảo mật CSDL. + Các thủ tục con trỏ (cursor): Là các thủ tục được sử dụng để thực hiện các chức năng của một con trỏ. + Các thủ tục truy vấn phân tán: Là các thủ tục được sử dụng để thực thi và quản lý các truy vấn phân tán. + Các thủ tục SQL Mail và CSDL Main: Là các thủ tục được sử dụng để thực hiện các thao tác e-mail bên trong SQL Server. THỦ TỤC NỘI TẠI MỞ RỘNG - Thủ tục nội tại mở rộng (extended SP) là các thủ tục không nằm trong SQL Server. - Chúng là các thủ tục được triển khai như một thư viện liên kết động (DLL), được thực thi bên ngoài môi trường SQL Server. - Các thủ tục nội tại mở rộng có tên bắt đầu bằng ‘xp_’ được chứa trong lược đồ dbo của CSDL master - Thủ tục nội tại mở rộng được dùng để thực thi các công việc phức tạp mà các câu lệnh T-SQL không thể thực hiện được. THỦ TỤC NỘI TẠI TẠM THỜI - Thủ tục nội tại tạm thời (temprorary sp) được tạo và dùng tạm thời trong một phiên làm việc(session). - Thủ tục nội tại tạm thời được lưu trữ trong CSDL temdb. - Có hai loại thủ tục tạm thời Thủ tục tạm thời cục bộ Thủ tục tạm thời toàn cục * Chỉ có người tạo mới có thể nhìn thấy và sử dụng. * Tự động bị xóa khi session kết thúc. * Tên thủ tục bắt đầu bằng # và không quá 116 kí tự. * Tất cả người dùng đều có thể nhìn thấy và sử dụng. * Bị xóa khi session cuối cùng kết thúc. * Tên thủ tục bắt đầu bằng ## và không quá 128 kí tự. THỦ TỤC NỘI TẠI TỪ XA - Thủ tục nội tại chạy trên SQL Server ở xa được gọi là remote stored procedure. - Remote stored procedure chỉ có thể dùng được khi server ở xa cho phép truy cập từ xa (remote access). THỦ TỤC NỘI TẠI CỤC BỘ - Các thủ tục nội tại cục bộ được tạo trong mỗi CSDL riêng biệt. - Thủ tục nội tại cục bộ chỉ được truy xuất bởi người tạo ra nó. THỦ TỤC NỘI TẠI NGƯỜI DÙNG Những hướng dẫn sau đây có thể được xem xét trong lúc tạo các thủ tục lưu: • Tên phải được đặt theo quy tắc nhận diện tên. • Tất cả các đối tượng dữ liệu có thể được tạo ra trong một thủ tục lưu, trừ các mặc định, các quy tắc, bắt lỗi, các thủ tục và các view. Đối tượng có thể được tham chiếu đến trong thủ tục mỗi khi nó đã được tạo ra. • Thủ tục lưu có thể tham chiếu đến như một bảng tạm thời. • Các thủ tục được gọi trong một thủ tục lưu có thể truy cập vào tất cả các đối tượng được tạo ra trong lời gọi thủ tục • Trong thủ tục lưu chúng ta có thể sử dụng tới 2100 tham số. • Chúng ta có thể tạo ra rất nhiều các biến số cục bộ trong thủ tục lưu mà bộ nhớ có thể cung cấp. • Kích cỡ lớn nhất của một thủ tục lưu là 128 MB Tạo thủ tục: CREATE [PROC | PROCEDURE] [@tên_tham_số Kiểu_dữ_liệu [=giá_trị_mặc_định] [OUTPUT] ] [WITH ENCRYTION] AS Các_câu_lệnh_T-SQL THAY ĐỔI THỦ TỤC NỘI TẠI - Có thể thay đổi nội dung và tên thủ tục bằng cách xóa đi tạo lại hoặc dùng lệnh ALTER PROCEDURE. Nếu tạo lại, toàn bộ quyền hạn gắn với thủ tục sẽ bị mất. Nếu dùng ALTER để sửa, các quyền hạn vẫn được giữ lại. Để thực hiện thay đổi, bạn phải là chủ sở hữu thủ tục hoặc có quyền thực hiện thay đổi. ALTER PROCEDURE [ @tên_tham_số Kiểu_dữ_liệu [=giá_trị_mặc_định] [OUTPUT] ] [WITH ENCRYTION | RECOMPILE] AS Các_câu_lệnh_T-SQL trong đó WITH RECOMILE: chỉ ra rằng thủ tục được biên dịch lại lúc chạy - Khi thay đổi thủ tục, sự thay đổi cần được phản ánh tới các đối tượng phụ thuộc (các đối tượng có liên quan) để chúng cập nhật. Một số lưu ý khi dùng lệnh ALTER PROCEDURE để sửa + Thủ tục nội tại được tạo có các tùy chọn như WITH ENCRYPTION, khi thực hiện sửa các tùy chọn này vẫn nên được giữ lại. + Các thành viên thuộc role sysadmin, db_ddladmin, và db_owner có quyền thực thi câu lệnh ALTER PROCEDURE XÓA THỦ TỤC NỘI TẠI Thủ tục nội tại có thể xóa nếu không dùng đến nó. Lỗi được hiển thị, nếu có thủ tục khác gọi đến thủ tục bị xóa. Trước khi xóa thủ tục, sử dụng thủ tục nội tại hệ thống sp_depend để xác định xem có các đối tượng nào phụ thuộc vào nó. DROP PROCEDURE RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – XEM THÔNG TIN THỦ TỤC NỘI TẠI - Xem thông tin thủ tục nội tại: + Cách 1 – sử dụng thủ tục hệ thống sp_helptext: sp_helptext ‘Tên_đối_tượng’ [, computed_column_name] + Cách 2 – sử dụng hàm OBJECT_DEFINITION: OBJECT_DEFINITION (‘tên_đối_tượng’) + Cách 3 – sử dụng view hệ thống sys.sql_modules: SELECT * | definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(‘tên_đối_tượng’) - Xem sự phụ thuộc: sp_depends ‘tên_thủ_tục’ + Một thủ tục khi thực thi có thể có tham chiếu đến nhiều đối tượng CSDL khác nhau, ngược lại, cũng có nhiều đối tượng khác tham chiếu đến nó. Điều đó gọi là sự phụ thuộc. + Khi có sự thay đổi trên thủ tục, sự thay đổi đó cần được phản ánh thích hợp tới các đối tượng phụ thuộc để đảm bảo sự thực thi đúng đắn của các truy vấn. + Sử dụng thủ tục nội tại hệ thống sp_depends để xem sự phụ thuộc của một thủ tục THỦ TỤC TRẢ VỀ GIÁ TRỊ - Một thủ tục nội tại khi được gọi thực hiện mặc định trả về 0 hoặc một giá trị nguyên khác 0. Giá trị trả về này được xem như là mã của sự trở về. Giá trị mã trả về cho biết thủ tục đó có thực hiện thành công hay không. - SQL Server 2005 cũng cho phép sử dụng lệnh RETURN để trả về một giá trị nguyên tường minh thay cho các giá trị mã trả về mặc định. Lệnh RETURN điều khiển quay trở lại chương trình gọi. Bất kỳ câu lệnh T-SQL đặt sau lệnh RETURN đều bị bỏ qua. Lệnh RETURN không thể trả về giá trị NULL. Nếu cố trả về NULL, một thông báo cảnh báo được phát sinh và giá trị 0(zero) được trả về. RETURN [] SỬ DỤNG THAM SỐ - Dữ liệu có thể được truyền từ chương trình gọi đến thủ tục được gọi bằng việc dùng các tham số. Tham số được chia thành hai loại + Tham số vào: cho phép chương trình gọi truyền các giá trị tới một thủ tục. Các giá trị này được gán cho các biến đã được định nghĩa bên trong thủ tục. + Tham số ra: cho phép thủ tục truyền các giá trị ngược trở lại chương trình gọi. Các giá trị này được gán cho các biến ở chương trình gọi. Tham số ra được định nghĩa khi tạo thủ tục. Để chỉ rõ là tham số ra, từ khóa OUTPUT được sử dụng khi khai báo tham số. Khi gọi thủ tục, trong câu lệnh gọi cũng chỉ rõ biến chứa kết quả ra bằng từ khóa OUTPUT. ALTER PROCEDURE @tên_tham_số Kiểu_dữ_liệu, @tên_tham_số Kiểu_dữ_liệu OUTPUT AS Các_câu_lệnh_T-SQL - Gọi thủ tục EXEC [OUTPUT] Nếu trong câu lệnh gọi quên từ khóa OUTPUT, thủ tục vẫn thực hiện nhưng không có giá trị trả về Đặc điểm của tham số ra + Kiểu dữ liệu của tham số ra không thể là kiểu image hoặc text + Trong câu lệnh gọi phải có biến nhận giá trị trả về + Biến tham số ra có thể được sử dụng trong các câu lệnh T-SQL sau đó + Tham số ra có thể là một con trỏ THỦ TỤC LỒNG NHAU Trong một thủ tục có thể gọi thực hiện một thủ tục khác. SQL Server 2005 cho phép lồng nhau tối đa 32 cấp. Sử dụng biến hệ thống @@NESTLEVEL để biết cấp độ lồng hiện tại. Khi @@NESTLEVEL được thực thi trong chuỗi T-SQL giá trị trả về của cấp độ lồng được + 1. Nếu sử dụng sp_executesql để thực thi @@NESTLEVEL, giá trị trả về của cấp độ lồng được + 2. QUẢN LÝ LỖI SQL Server 2005 giới thiệu cấu trúc TRYCATCH cho việc quản lý lỗi trong thủ tục. Khi một câu lệnh SQL trong khối TRY gây lỗi, điều khiển được chuyển tới khối CATCH, tại đây lỗi sẽ được xử lý. Sau khi lỗi được xử lý, điều khiển được chuyển tới câu lệnh SQL viết sau câu lệnh END CATCH. Cú pháp: BEGIN TRY Các_câu_lệnh_T-SQL END TRY BEGIN CATCH Các_câu_lệnh_T-SQL END CATCH Những câu lệnh trong khối TRY viết sau câu lệnh gây lỗi sẽ không được thực thi. - Trong SQL Server 2005, tất cả các lỗi đều có một mã số lỗi. View hệ thống sys.message chứa tất cả các thông báo lỗi mặc định và mã số của chúng. Việc quản lý lỗi trong thủ tục cũng được hỗ trợ bởi biến hệ thống @@ERROR. @@ERROR chứa mã số lỗi của câu lệnh T-SQL gây lỗi sau cùng. Nếu không có câu lệnh nào gây lỗi @@ERROR có giá trị 0. - Hàm ERROR_MESSAGE() được sử dụng trong khối CATCH để hiển thị thông báo lỗi xảy ra trong khối TRY - Hàm ERROR_NUMBER(): Trả về mã số của lỗi. - Hàm ERROR_SEVERITY(): - Hàm ERROR_STATE(): Trả về mã số của trạng thái lỗi. - Hàm ERROR_PROCUDURE(): Trả về tên của thủ tục nội có lỗi xảy ra - Hàm ERROR_LINE(): Trả về số của dòng mà tại đó gây ra lỗi. RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – TRIGGER KHÁI NIỆM Trigger là một thủ tục nội tại, được thực thi khi có các thao tác cập nhật dữ liệu trên bảng. Khác với thủ tục nội tại, trigger: không thể thực thi trực tiếp; không có tham số vào/ra. Các trigger được định nghĩa trên các bảng cụ thể. Trigger tự động kích hoạt khi các hành động INSERT, UPDATE, DELETE xảy ra trên bảng mà nó được định nghĩa. MỤC ĐÍCH: Các trigger có thể chứa các chuỗi xử lý phức tạp và dùng cho việc đảm bảo toàn vẹn dữ liệu ở mức thấp. - Thay đổi dây chuyền(cascading) tới các bảng có liên quan. - Kiểm tra các toàn vẹn dữ liệu phức tạp hơn ràng buộc(constraint) CHECK. - Định nghĩa thông báo lỗi theo ý muốn - Duy trì dữ liệu chưa chuẩn hóa - So sánh tình trạng dữ liệu trước và sau khi cập nhật DATA DEFINITION LANGUAGE(DDL) TRIGGER DDL trigger thực thi khi bảng hoặc view được tạo, xóa, thay đổi bằng các lệnh CREATE, DROP, ALTER. 1. Khái niệm DDL Trigger: - DDL trigger thực thi thủ tục nội tại khi các sự kiện DDL (như ALTER, CREATE, DROP) xảy ra trong CSDL. - DDL trigger được sử dụng cho các công việc quản trị như giám sát và điều chỉnh các hành động trên CSDL. - Sử dụng DDL trigger khi bạn muốn: + Ngăn cản các thay đổi trong lược đồ CSDL của mình. + Xảy ra điều gì đó trong CSDL để phản ứng (response) sự thay đổi trong lược đồ CSDL - DDL trigger được định nghĩa ở CSDL hoặc mức server. 2. Thao tác DDL Trigger: - Tạo DDL Trigger: CREATE TRIGGER ON {ALL SERVER | DATABASE} [WITH ENCRYTION] {FOR | AFTER} {} AS - Thay đổi DDL Trigger: ALTER TRIGGER ON {ALL SERVER | DATABASE} [WITH ENCRYTION] {FOR | AFTER} {} AS - Xóa DDL Trigger: DROP TRIGGER ON {ALL SERVER | DATABASE} 3. Phạm vi của DDL Trigger: DDL trigger được gọi bởi các lệnh SQL thực thi trong CSDL hiện hành hoặc server hiện hành. Phạm vi của DDL trigger phụ thuộc vào trigger thực thi cho các sự kiện CSDL hay các sự kiện server. DDL trigger chia thành hai loại + DDL trigger phạm vi CSDL (Database-scope): được gọi bởi các sự kiện thay đổi trong lược đồ CSDL. Các trigger này được lưu trữ trong CSDL và thực thi trên các sự kiện DDL, ngoại trừ liên quan đến các bảng tạm. + DDL trigger phạm vi Server (Sever-scope): được gọi bởi các sự kiện DDL ở mức server. Các trigger được lưu trong CSDL master. LOGON TRIGGER Logon trigger thực thi các thủ tục nội tại để đáp ứng sự kiện logon. Logon trigger kích hoạt sau khi giai đoạn chứng thực đăng nhập kết thúc, nhưng trước khi phiên làm việc người dùng thực được thiết lập. Logon trigger không kích hoạt nếu việc chứng thực thất bại. DATA MANIPUNATION LANGUAGE (DML) TRIGGER DML trigger thực thi khi dữ liệu trong một bảng hoặc view được cập nhật bởi các lệnh INSERT, UPDATE, DELETE. 1.Sử dụng bảng Inserted và Deleted: - Các câu lệnh trong DML trigger có thể truy cập đến hai bảng logic đặc biệt là Inserted và Deleted. - Bảng Inserted và Deleted chứa ảnh của các dòng dữ liệu bị tác động bởi các lệnh cập nhật. + Bảng Inserted chứa bản sao của tập các dòng dữ liệu được cập nhật bằng thao tác INSERT, UPDATE trong bảng. + Bảng Deleted chứa bản sao của tập các dòng dữ liệu được cập nhật bằng thao tác DELETE, UPDATE trong bảng. Tất cả các dòng dữ liệu bị tác động bởi câu lệnh insert, delete, update sẽ nằm trong bảng Inserted và Deleted. Viết một trigger cho cả ba hành động insert, delete, update. Trong trigger viết một câu lệnh select dữ liệu từ bảng Inserted, và một select từ bảng deleted. Sau đó biên dịch và thử trigger này 2. Trigger Insert: được thực thi khi một bản ghi mới được chèn vào bảng, đảm bảo các giá trị được nhập vào phải phù hợp với các ràng buộc định nghĩa cho bảng đó. CREATE TRIGGER ON [WITH ENCRYTION] FOR INSERT AS Khi insert một bản ghi vào bảng, INSERT trigger lưu một bản sao chép của bản ghi đó vào bảng Inserted. Sau đó kiểm tra các giá trị mới trong bảng Inserted có tuân theo các ràng buộc hay không. Nếu bản ghi đó hợp lệ, INSERT trigger sẽ thêm dòng đó vào bảng, ngược lại hiển thị thông báo lỗi. 3. Trigger Update: được thực thi khi một bản ghi được sửa. Khi có một bản ghi được update, trigger UPDATE sao chép bản ghi gốc vào bảng Deleted và bản ghi mới vào bảng Inserted. Nếu các giá trị mới hợp lệ, bản ghi từ bảng Inserted được copy vào bảng, ngược lại hiển thị thông báo lỗi và copy bản ghi gốc trong bảng Deleted quay lại bảng. Có thể sử dụng hàm UPDATE(tên_cột) để kiểm tra cột nào đó được cập nhật. Hàm trả về true|false. CREATE TRIGGER ON [WITH ENCRYTION] FOR UPDATE AS 4. Trigger Delete: dùng để giới hạn người dùng xóa một bản ghi trong một bảng. Nếu người dùng thực hiện xóa một bản ghi thì sẽ xảy ra như sau: Bản ghi bị xóa khỏi bảng được chèn vào bảng Deleted; Bản ghi xóa được kiểm tra lại ràng buộc cho việc xóa; Nếu có ràng buộc có trên bản ghi ngăn cản việc xóa, trigger DELETE hiển thị thông báo lỗi; Bản ghi bị xóa trong bảng Deleted được sao chép quay trở lại bảng. CREATE TRIGGER ON [WITH ENCRYTION] FOR DELETE AS 5. Trigger After: có thể được thực thi khi thao tác INSERT, DELETE, UPDATE hoàn tất. Chỉ có thể tạo AFTER Trigger cho bảng. Một bảng có thể có nhiều AFTER trigger cho mỗi thao tác INSERT, DELETE, UPDATE, chúng được thực thi lần lượt theo thứ tự mà chúng được tạo ra. AFTER trigger được thực thi sau khi các ràng buộc của bảng đã kiểm tra hoàn tất. CREATE TRIGGER ON [WITH ENCRYTION ] FOR | AFTER INSERT | DELETE | UPDATE AS 6. Trigger INSTEAD OF: có thể định nghĩa cho bảng hoặc view. Mỗi bảng hoặc view chỉ có một “INSTEAD OF” Trigger được tạo cho mỗi tao tác INSERT, DELETE, UPDATE. “INSTEAD OF” trigger thực thi trước khi các ràng buộc của bảng được thực hiện. Không thể tạo INSTEAD OF trigger cho thao tác delete hoặc update trên các bảng có thiết lập tùy chọn ON DELETE cascade và ON UPDATE cascade. CREATE TRIGGER ON [WITH ENCRYTION] INSTEAD OF { INSERT | DELETE | UPDATE } AS 7. Thay đổi Trigger: ALTER TRIGGER ON [WITH ENCRYTION] {FOR | AFTER | INSTEAD OF} {INSERT | DELETE | UPDATE} AS 8. Xóa Trigger: DROP TRIGGER tên_DML_trigger, ... 9. Xem định nghĩa của Trigger: sp_hepltext(tên_trigger) RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – KHÁI QUÁT KHUNG NHÌN VIEW ĐỊNH NGHĨA - View là cách để xem các dữ liệu từ một hay nhiều bảng trong cơ sở dữ liệu. - View là một bảng ảo, được tạo ra bởi các cột được lấy từ một hoặc nhiều bảng(table) khác nhau. Các bảng này được gọi là các bảng cở sở, chúng có thể trong cùng CSDL hoặc từ các CSDL khác. - View cũng có thể chứa các cột lấy từ một View khác trong cùng CSDL hoặc CSDL khác. Một View có thể có tối đa 1024 cột. - View không thể sắp xếp trừ khi bạn tạo một unique clustered index trên nó. - Có thể tạo nhiều nonclustered index trên View, nhưng với unique clustered index chỉ tạo một lần. - Một index có thể tạo trên view nếu + View không chứa các cột từ view khác. + Bảng cơ sở nằm trong cùng CSDL với view. PHÂN LOẠI Khung nhìn tiêu chuẩn (Standard View): là view được tạo với các cột lấy từ một hoặc nhiều bảng. Khung nhìn chỉ mục (Indexed View): là view có một unique clustered index. Indexed view rất có ích cho các truy vấn thống kê dữ liệu. Indexed view không thích hợp cho các bảng thường xuyên cập nhật. - Những lợi thế của kiểu view này là nó cung cấp một cơ chế tìm kiếm rất nhanh chóng để lấy ra thông tin của một view một cách liên tục. Chỉ mục đầu tiên được tạo ra trong một View chỉ mục phải là một chỉ mục duy nhất loại clustered. Mỗi khi chỉ mục đầu tiên được tạo ra, kéo theo một số chỉ mục loại non-clustered có thể được tạo ra. - Tốt nhất chúng ta nên xây dưng View chỉ mục trên dữ liệu không thường xuyên cập nhật, bởi vì việc duy trì view có chi phí cao hơn việc duy trì một chỉ mục của bảng. Nếu view chỉ mục được tạo ra trên những dữ liệu cập nhật thường xuyên thì chi phí để duy trì dữ liệu trong view chỉ mục có thể làm giảm những ưu điểm của việc sử dụng view chỉ mục. Khung nhìn phân tán (Partitioned View): là view dùng các dữ liệu được phân vùng từ một hoặc nhiều bảng. Nếu bảng cơ sở được trên cùng server, view được xem như là partitioned view cục bộ (local partitioned view ), ngược lại, nếu đặt trên sever khác được gọi là partition view phân tán (Distributed partitioned view). - Tính năng này cho phép phân chia và phân tán dữ liệu theo chiều ngang tới nhiều server và cơ sở dữ liệu và làm cho chúng ta cảm thấy vẫn như một cơ sở dữ liệu thống nhât. - Việc phân chia view cho phép chúng được phân tán thông qua nhiều máy khác nhau. Các bảng đã phân chia được phân tán qua nhiều server. Vì thế, mỗi server cần truy nhập tới mọi server khác. Do đó, bạn cần cài đặt cầu hình tất cả các server như các server liên kết (linked servers). LỢI ÍCH CỦA KHUNG NHÌN - Bảo mật thông qua cá nhân hóa truy xuất: Dữ liệu nhạy cảm có thể được che giấu khỏi những người dùng không có đủ thẩm quyền để truy xuất. Cá nhân hóa truy xuất dữ liệu được thực hiện bằng việc chỉ cho phép người dùng truy xuất CSDL thông qua một tập nhỏ các view - Tùy biến hiển thị dữ liệu: Sử dụng view, dữ liệu được lấy từ cùng một nguồn nhưng được hiển thị khác nhau theo yêu cầu của mỗi người dùng. - Trộn dữ liệu từ nhiều bảng hoặc view: View có thể ghép các cột từ nhiều bảng và view khác và chúng được hiển thị cùng nhau như lấy từ một bảng. - Thao tác trên các bản ghi (record): View cũng có thể được dùng để cập nhật dữ liệu cho bảng, nếu view đó có chứa tất cả các cột quan trọng của bảng (như cột khóa chính, cột NOT NULL). - Kiểm tra ràng buộc toàn vẹn: Nếu dữ liệu được thêm hoặc chỉnh sửa thông qua view. SQL Server tự động kiểm tra dữ liệu để đảm bảo ràng buộc toàn vẹn cho từng cột. KHUNG NHÌN HỆ THỐNG - View hệ thống là các view đã được định nghĩa sẵn trong SQL Server. - Thuộc tính của các đối tượng khác như table và view được lưu trữ trong các bảng hệ thống. Các thuộc tính này được xem như là siêu dữ liệu (metadata). - Siêu dữ liệu có thể xem được thông qua view hệ thống. - Các loại view hệ thống + Catalog view + Information Schema views + Compatibility views + Replication views + Dynamic Management views + Notification services views THAO TÁC TẠO , SỬA , XÓA VIEW – XEM THÔNG TIN KHUNG NHÌN 1. Tạo khung nhìn: CREATE VIEW AS Câu_lệnh_Select - Để thực thi câu lệnh tạo view, người thực hiện phải là thành viên của Role systemadmin, chủ sở hữu CSDL (db_owner), hoặc có quyền thực thi lệnh CREATE VIEW (db_DDLadmin). Trước khi thực hiện lệnh tạo view, nên chạy thử câu lệnh SELECT của view. - Một số giới hạn khi tạo view: View chỉ có thể được tạo trong cơ sở dữ liệu hiện tại; Câu truy vấn để định nghĩa View không thể bao gồm các mệnh đề COMPUTE, COMPUTE BY hoặc từ khóa INTO. Không sử dụng mệnh đề ORDER BY nếu không có từ khóa TOP; Mỗi một View có thể bao gồm tới 1024 cột từ một hay nhiều bảng hoặc từ nhiều View khác; Các bảng tạm thời không thể tham gia vào View; Không thể kết hợp câu lệnh CREATE VIEW với câu lệnh T-SQL trong cùng một khối (batch). 2. Sửa khung nhìn: Một view có thể sửa bằng cách xóa rồi tạo lại hoặc sử dụng câu lệnh ALTER VIEW. ALTER VIEW AS Câu_lệnh_Select 3. Xóa khung nhìn: DROP VIEW 4. Xem thông tin khung nhìn: - Xem thông tin định nghĩa khung nhìn: EXEC sp_helptext - Xem sự phụ thuộc của khung nhìn: một view có thể tham chiếu đến nhiều đối tượng khác nhau trong CSDL và ngược lại; trước khi xóa một view, cần phải biết được những đối tượng nào phụ thuộc vào nó; sử dụng thủ tục nội tại hệ thống sp_depends để xem sự phụ thuộc của một đối tượng bất kỳ trong CSDL. Cú pháp EXEC sp_depends - Dấu thông tin định nghĩa khung nhìn: CREATE VIEW WITH ENCRYPTION AS Câu_lệnh_Select - Phân tán khung nhìn (Distributed Partitioned views): DPVs cho phép truy vấn dữ liệu trên các Server và các CSDL khác nhau. Các Server cần phải được cấu hình như Linked Server để có thể truy cập được với nhau EXEC sp_dropserver [ @server=]'server' [, [ @droplogins=] {'droplogins' | NULL} ] TÙY CHỌN KHI TẠO KHUNG NHÌN 1. Tùy chọn CHECK OPTION: là một tùy chọn được kết hợp với câu lệnh CREATE VIEW để ngăn không cho cập nhật dữ liệu không thoả điều kiện của mệnh đề WHERE trong câu lệnh SELECT. CREATE VIEW AS Câu_lệnh_Select [WITH CHECK OPTION ] 2. Tùy chọn SCHEMABINDING: được sử dụng trong câu lệnh CREATE VIEW hoặc ALTER VIEW nhằm để gắn kết view với lược đồ(schema) của bảng cơ sở. - Khi được gắn kết với view, các bảng cơ sở không thể thay đổi được. Vì sự thay đổi (xóa, sửa) các đối tượng này sẽ làm ảnh hưởng tới view. - Chỉ có thể xóa hoặc sửa view trước để bỏ đi sự phụ thuộc, sau đó mới có thể thực hiện thay đổi bảng cơ sở. CREATE VIEW [WITH SCHEMABINDING] AS Câu_lệnh_Select 3. Tạo chỉ số khung nhìn: là view có một unique clustered index trên nó. Kết quả của các view thông thường chỉ được phát sinh khi thực thi, còn indexed view lưu kết quả trong CSDL. Bất kỳ sự thay đổi dữ liệu trên bảng cơ sở, chúng đều được tự động cập nhật tới index view. Khi tạo index cho view, bạn cần phải gắn view đó tới lược đồ bằng tùy chọn schemabinding. CREATE UNIQUE CLUSTERED ON (tên_cột,..) Lưu ý: Không nên sử dụng Index view cho các bảng có dữ liệu thường xuyên cập nhật THÊM – SỬA – XÓA KHUNG NHÌN - Đổi tên khung nhìn: Có thể thực hiện thay đổi view bẳng câu lệnh ALTER VIEW và đổi tên view bằng thủ tục nội tại hệ thống sp_rename. sp_rename ‘’, ‘’, ‘’ + Kiểu_đối_tượng: bao gồm COLUMN, DATABASE, INDEX, OBJECT, USERDATATYPE. + Một số chú ý khi đổi tên view: tên view cần đổi phải có trong CSDL hiện hành; chỉ có thể thực hiện đổi tên view nếu bạn có quyền chỉnh sửa (modify); chủ sở hữu CSDL có thể thay đổi tên tất cả các view. - Có thể sử dụng view để cập nhật (thêm, sửa, xóa) dữ liệu của bảng trong CSDL. + Một số điểm cần chú ý khi thực hiện thay đổi dữ liệu qua view: sự cập nhật chỉ có thể tiến hành trong các cột được lấy từ một bảng; không thể cập nhật dữ liệu trong các cột tính toán, thống kê; nếu view có sử dụng WITH CHECK OPTION, các giá trị cập nhật phải thỏa mãn điều kiện (trong mệnh đề WHERE); tất cả các bản ghi chỉ có thể cập nhật được nếu view có chứa tất cả các cột có constraint định nghĩa trên chúng của bảng cơ sở. + Một số chú ý dùng câu lệnh UPDATE để cập nhật dữ liệu trên view: không thể cập nhật các giá trị của cột có thuộc tính IDENTITY; không thể thực hiện cập nhật nếu bảng cơ sở có chứa cột TIMESTAMP; khi cập nhật dòng mà gặp lỗi về ràng buộc hay qui tắc (rule), lỗi được trả về và dòng khong được cập nhật; nếu trong view hoặc bảng cơ sở có kết nối đệ qui (self join), câu lệnh UPDATE không thực hiện. + Một số chú ý dùng câu lệnh INSERT trên view: chỉ ra giá trị cho tất cả các cột không nhận giá trị NULL; nếu trong view hoặc bảng cơ sở có kết nối đệ qui (self join), câu lệnh INSERT không thực hiện. CREATE VIEW [schema_name .] view_name [(column [ ,...n ] )] [WITH [ ,...n ] ] AS select_statement [ ; ] [WITH CHECK OPTION] ::= {[ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA]} RÀNG BUỘC DỮ LIỆU TRONG SQL SERVER – Mai Đức Thạch 0165.859.7998 – NHỮNG ĐIỂM MỚI CỦA SQL SERVER 2008 1. Giới thiệu: - Tầm nhìn nền tảng dữ liệu trong SQL Server 2008 giúp các tổ chức tạo các ứng dụng thế hệ kế tiếp thiên về thao tác dữ liệu. - SQL Server 2008 giới thiệu 4 lĩnh vực chính trong Tầm nhìn nền tảng dữ liệu : + Nền tảng dữ liệu cho các doanh nghiệp (Enterprise Data Platform) + Sự phát triển năng động (Dynamic Development) + Dữ liệu quan hệ mở rộng (Beyond Relational Data) + Xử lý thông minh(Business intelligence) - Các cải tiến trong SQL Server Management Studio cải thiện hiệu suất và giúp phát triển các ứng dụng nhanh hơn. - Tầm nhìn nền tảng dữ liệu trong SQL Server 2008 giúp các tổ chức tạo thế hệ kế tiếp của các ứng dụng thiên về thao tác dữ liệu. 2. Tầm nhìn nền tảng dữ liệu: - Nền tảng dữ liệu là:an toàn; dễ quản lý; đáng tin cậy cho việc tạo ra các ứng dụng quan trọng. - SQL Server 2008 nhắm đến 4 lĩnh vực chính: + Nền tảng dữ liệu doanh nghiệp. + Thông tin trong toàn bộ doanh nghiệp. + Sự phát triển động. + Dữ liệu quan hệ mở rộng. 3. Thông tin vị trí: - Với khả năng dữ liệu không gian trong SQL Server cho phép các chuyên gia xây dựng ứng dụng liên quan đến vị trí địa lý như thiết bị hệ thống định vị toàn cầu (GPS). - SQL Server 2008 giới thiệu các 2 kiểu dữ liệu không gian, giúp các nhà phát triển có thể dễ dàng phát triển các ứng dụng định vị. + Kiểu dữ liệu hình học(geometry) được sử dụng để biểu diễn cho các đối tượng trong một mặt phẳng với các tọa độ x và y nằm trên các cạnh của của các hình đa giác, các đường(line) ,. + Kiểu dữ liệu địa lý(geography) sử dụng vĩ độ(latitude) và kinh độ(longitude) để xác định các điểm trên trái đất. 4. Hỗ trợ cho bất kỳ loại dữ liệu nào: - Các kiểu dữ liệu người dùng lớn (Large User-Defined Types) + Sử dụng để lưu trữ dữ liệu không gian như các đói tượng geometry và geography. + Nếu thuộc tính SqlUserDefinedTypeAttribute.MaxByteSize được thiết lập là -1, kiểu dữ liệu