Cải tiến trong lệnh T-SQL của SQL Server 2008

Cải tiến trong lệnh T-SQL của SQL Server 2008 Cải tiến trong lệnh T-SQL của SQL Server 2008 SQL Server 2008 được tích hợp nhiều tính năng mới đáng chú ý. Một trong số những tính năng này là những cải tiến trong câu lệnh T-SQL giúp giảm thời gian làm việc với những câu lệnh này. Simpo PDF Merge and Split Unregistered Version - Trong phần đầu tiên của loạt bài viết này chúng ta sẽ tìm hiểu một số cải tiến của T-SQL. Cải tiến trong Intellisense (trình cảm ứng thông minh) Với những cải của Intellisense giờ đây người dùng có thể lưu dữ liệu, tìm những thông tin cần thiết, chèn trực tiếp các thành phần ngôn ngữ T-SQL vào mã và trình cảm ứng thông minh này có thể giúp rút ngắn thời gian nhập lệnh từ bàn phím. Chức năng này có thể rút ngắn thời gian phát triển phần mềm nhờ làm giảm thời gian thao tác với bàn phím đồng thời giảm thiểu những tham chiếu tới những tài liệu ngoài. Những cải tiến của Intellisense bao gồm vùng ngôn ngữ T-SQL được mở rộng và tích hợp một hệ thống mầu sắc. Tính năng này hoạt động giống như tính năng kiểm Simpo PDF Merge and Split Unregistered Version - tra cú pháp tự động trong Visual Studio. Khi nhập lệnh T-SQL nó sẽ tự động hoàn thiện cú pháp cho lệnh, và cho các đối tượng trong cơ sở dữ liệu, dù các biến đã được khai báo trước đó. Người dùng có thể có thể lựa chọn sử dụng tính năng này hoặc tắt bỏ nó nếu thấy không cần thiết. Simpo PDF Merge and Split Unregistered Version - Cải tiến trong cú pháp lệnh T-SQL Trong SQL Server 2008, cú pháp của lệnh T-SQL có ba cải tiến chính sau đây: 1. Khởi tạo biến cùng thời điểm khai báo. Giờ đây người dùng có thể khởi tạo biến ngay khi khai báo thay vì sử dụng hai lệnh riêng biệt là DECLARE và SET như trước đây. Nó có thể làm việc với hầu hết các kiểu dữ liệu bao gồm kiểu dữ liệu SQLCLR, nhưng sẽ không làm việc với kiểu dữ liệu TEXT, NTEXT hay IMAGE. 2. Compound Operators. Compound Operators (toán tử kết hợp) là dạng toán tử giống như trong C++ và C#. Chúng thực thi một số thao tác và cài đặt một giá trị gốc cho kết quả của thao tác. Chúng giúp tránh một số lỗi và cho phép viết tắt khi làm việc với code. Simpo PDF Merge and Split Unregistered Version - Ngoài ra toán tử này có thể sử dụng trong mệnh đề SET của lệnh UPDATE. Dưới đây là danh sách các toán tử được hỗ trợ trong SQL Server 2008:  += Add and assign  -= Subtract and assign  *= Multiply and assign  /= Divide and assign  %= Modulo and assign  &= Bitwise AND and assign  ^= Bitwise XOR and assign Simpo PDF Merge and Split Unregistered Version -  |= Bitwise OR and assign Ví dụ: 3. Row Constructor. T-SQL được cải tiến cho phép chèn nhiều giá trị bằng một lệnh INSERT duy nhất. Điều đó có nghĩa là người dùng có thể đưa nhiều thuộc tính hàng vào trong mệnh đề VALUE. Ví dụ: Simpo PDF Merge and Split Unregistered Version - Những cải tiến trong thành phần phụ thuộc của đối tượng Những cải tiến trong những thành phần phụ thuộc của đối tượng giúp cung cấp những thông tin đáng tin cậy của nhiều thành phần phụ thuộc giữa các đối Simpo PDF Merge and Split Unregistered Version - tượng thông qua view catalog mới được giới thiệu và các chức năng quản lý động. Những thông tin của thành phần phụ thuộc luôn được cập nhật cho phạm vi lược đồ đối tượng(nơi đối tượng A không thể bị xóa do đối tượng B phụ thuộc vào nó) và ngoài lược đồ đối tượng (nơi đối tượng A có thể bị xóa hoặc thậm chí không thể được tạo tuy nhiên đối tượng B vẫn phụ thuộc vào nó). Những thành phần phụ thuộc được kiểm tra cho các thủ tục lưu, bảng biểu, view, chức năng, trigger, kiểu người dùng định nghĩa, tập hợp lược đồ XML, … SQL Server 2008 giới thiệu ba đối tượng mới cung cấp những thông tin thành phần phụ thuộc của đối tượng, bao gồm: 1. sys.sql_expression_dependenciescatalog view: Hiển thị tên các thành phần phụ thuộc của đối tượng. Nó bao gồm một bản ghi cho mỗi thành phần phụ Simpo PDF Merge and Split Unregistered Version - thuộc trên đối tượng do người dùng định nghĩa trong cơ sở dữ liệu hiện thời. 2. sys.dm_sql_referenced_entitiesDMF: Cung cấp nọi thực thể mà thực thể nhập phụ thuộc vào, trả về một hàng cho mỗi đối tượng được người dùng định nghĩa quy chiếu theo tên trong định nghĩa của thực thể tham chiếu được chỉ định. 3. sys.dm_sql_referencing_entitiesDMF: Cung cấp mọi đối tượng phụ thuộc vào thực thể nhập, nó sẽ trả về một bản ghi cho mội đối tượng được người dùng định nghĩa trong cơ sở dữ liệu hiện tại được tham chiếu theo tên của một đối tượng khác được cũng được người dùng định nghĩa. Có hai phương pháp người dùng có thể sử dụng để xem những đối tượng phụ thuộc đó là sử dụng SSMS Simpo PDF Merge and Split Unregistered Version - (phải chuột lên đối tượng rồi chọn View Dependencies) hay bằng cách viết những truy vấn theo view và DMF vừa kể ra ở trên. Simpo PDF Merge and Split Unregistered Version - Sử dụng bảng gợi ý FORCESEEK Simpo PDF Merge and Split Unregistered Version - Bảng gợi ý FORCESEEK khá hữu dụng trong trường hợp Query Plan (các bước được sử dụng để truy cập hay hiệu chỉnh thông tin trong hệ thống quản lý cơ sở dữ liệu liên quan tới SQL) sử dụng một bảng hay toán tử Index Scan (quét chỉ mục) trên một bảng hay view, tuy nhiên toán tử (Index Seek) tìm kiếm chỉ mục có thể hiệu quả hơn (ví dụ như trong trường hợp có quá nhiều sự lựa chọn). Gợi ý bảng FORCESEEK buộc Query Optimizer (trình tối ưu truy vấn) chỉ sử dụng các thao tác Index Seek như đường dẫn truy cập vào dữ liệu trong bảng hay view được tham chiếu trong truy vấn. Chúng ta có thể sử dụng bảng gợi ý này để ghi đè lên Query Plan mặc định được Query Optimizer lựa chọn để tránh các vấn đề thực thi gây ra do Query Plan không hiệu quả. Ví dụ, nếu một Plan chứa bảng hay các toán tử Index Scan, và những bảng tương ứng liên tục được truy cập trong khi thực thi truy vấn thì việc áp dụng một thao tác Index Seek có thể sẽ hiệu quả hơn sử dụng truy vấn. Khả năng Simpo PDF Merge and Split Unregistered Version - này sẽ xảy ra trong trường hợp các thành phần trong tập hợp không chính xác hay tính đến lượng thời gian cần sử dụng để viết một Query Plan mà Query Optimizer sử dụng để thực hiện tìm kiếm. Simpo PDF Merge and Split Unregistered Version - Một trong những tình huống mà bảng gợi ý này trở nên hữu dụng đó là khả năng làm việc với Parameter Sniffing (một công cụ mà trình tối ưu truy vần của SQL Server sử dụng để tìm kiếm giá trị biến từ truy vấn trong khi thực hiện lệnh lần đầu tiên và tạo một Plan thực thi tối ưu dựa trên giá trị đó). Chúng ta hãy kiểm chứng điều này bằng cách chạy những truy vấn dưới đây trong cơ sở dữ liệu AdventureWorks và phân tích những điểm khác biệt. Trường hợp 1: Truy vấn đầu tiên trả về 450 hàng và có ít khả năng chọn lọc hơn so với truy vấn thứ hai chỉ trả về 16 bản ghi. Do đó cần sử dụng Index Scan cho truy vấn đầu tiên hơn là sử dụng Index Seek và Lookup (tra cứu) của truy vấn thứ hai. Simpo PDF Merge and Split Unregistered Version - Trường hợp 2: Chúng ta sẽ chạy lại các truy vần ở trên, nhưng trong trường hợp này chúng ta sẽ sử dụng các biến để gán các giá trị cho truy vấn thay vì nhập trực tiếp các giá trị. Nếu kiểm tra các Plan thực thi được tạo chúng ta sẽ thấy cả hai truy vấn đang sử dụng cùng Index Scan mặc dù các giá trị của tham số hoàn toàn khác nhau. Theo trường hợp 1 thì truy vấn thứ hai có khả năng chọn lọc cao hơn và cần sử dụng Index Seek và Lookup. Đó là do trong khi khi truy Simpo PDF Merge and Split Unregistered Version - vấn đầu tiên chạy, trình tối ưu truy vấn SQL không nhận biết được giá trị biến cho đến khi chạy thực. Vì đã sử dụng biến và nhập tùy chọn lọc trên bộ lọc và tạo Plan thực thi trên cơ sở của bộ lọc này và lưu trữ lại, trong khi đó truy vấn thứ hai cũng sẽ sử dụng Plan thực thi tương tự được lưu trữ. Trường hợp 3: Nếu những truy vấn sử dụng tham số và mọi thời điểm những giá trị biến này trả về với Simpo PDF Merge and Split Unregistered Version - khả năng lọc cao thì chúng ta phải áp dụng phương pháp nào để buộc Query Optimizer thực hiện Index Seek thay vì Index Scan? Chúng tra có hai lựa chọn ở đây, hoặc là sử dụng gợi ý FORCESEEK hay sử dụng tùy chọn RECOMPILE. Simpo PDF Merge and Split Unregistered Version - Simpo PDF Merge and Split Unregistered Version - FORCESEEK áp dụng cho các thao tác tìm kiểm chỉ mục theo nhóm và không theo nhóm. Nó có thể được chỉ định cho mọi bảng hay view trong mệnh đề FROM của lệnh STATEMENT và trong mệnh đề FROM của lệnh UPDATE hay DELETE. Lưu ý: Do Query Optimizer của SQL Server chỉ lựa chọn Plan thực thi tốt nhất cho một truy vấn nên Microsoft đề xuất rằng các nhà phát triển có kinh nghiệm và các quản trị viên cở sở dữ liệu chỉ sử dụng những gợi ý như một phương pháp cuối cùng vì Optimizer luôn thực hiện tốt tác vụ này. GROUPING SETS GROUPING SETS cho phép người dùng viết một truy vấn để tạo nhiều nhóm sau đó chỉ trả về một tập giá trị. Tập giá trị này tương đương với một UNION Simpo PDF Merge and Split Unregistered Version - ALL của những hàng được nhóm khác nhau. Sử dụng GROUPING SETS chúng ta có thể tập trung vào các cấp độ thông tin khác nhau cần sử dụng ngoài việc sử dụng phương pháp kết hợp một số kết quả tru yvaans. Với khả năng thục thi truy vấn được cải tiến, GROUPING SETS cho phép chúng ta lập báo cáo với nhiều nhóm một cách dễ dàng. VÌ số lượng nhóm luôn có thể tăng lên, nên sự đơn giản và những tiện ích trong khả năng thực thi mà GROUPING SETS mang lại sẽ trở nên hữu dụng hơ nhiều. Simpo PDF Merge and Split Unregistered Version - Nói cách khác, mệnh đề GROUP BY sử dụng GROUPING SETS có thể kết xuất một nhóm kết quả tương ứng với kết quả được tạo bởi một UNION ALL của nhiều mệnh đề GROUP BY đơn vì mệnh đề GROUP BY chỉ có tác dụng khi kết hợp. Kết luận Simpo PDF Merge and Split Unregistered Version - Trong phần đầu này chúng ta đã tìm hiểu một số cải tiến trong câu lệnh T-SQL trong SQL Server 2008 hỗ trợ cho quá trình làm việc của các nhà lập trình. Trong phần tiếp theo của loạt bìa viết này chúng ta sẽ tìm hiểu chi tiết hơn về các kiểu dữ liệu được giới thiệu trong SQL Server 2008. Trong phần trước chúng ta đã tìm hiểu một số cải tiến trong câu lệnh T-SQL của SQL Server 2008, gồm IntelliSense, Grouping Set, bảng gợi ý FORCESEEK, … Trong phần này chúng ta sẽ tiếp tục tìm hiểu những kiểu dữ liệu mới được giới thiêu trong SQL Server 2008. Simpo PDF Merge and Split Unregistered Version - SQL Server 2008 đã giới thiệu một số kiểu dữ liệu mới giúp mở rộng đối tượng làm việc và cải tiến khả năng thực thi của SQL Server. Ví dụ, kiểu User- Defined Table (bảng do người dùng định nghĩa) và Table-Valued Parameter cho phép người dùng đưa một nhóm kết quả vào một thủ tục và lưu nhiều giá trị trả về lên máy chủ, trong khi kiểu dữ liệu Date và Time có thể giúp tiết kiệm tài nguyên bộ nhớ vì chiếm dụng ít bộ nhớ hơn trong trường hợp người dùng chỉ cần lưu ngày hay giờ, và giúp thao tác dễ dàng hơn khi làm việc với chúng. Trong phần này chúng ta sẽ tập trung tìm hiểu hai kiểu dữ liệu này, về phạm vi sử dụng và khả năng ứng dụng. User-Defined Table Type (UDTT) và Table-Value Parameter (TVP) Với SQL Server 2008 chúng ta có thể tạo một UDTT (kiểu bảng do người dùng định nghĩa) theo định Simpo PDF Merge and Split Unregistered Version - nghĩa cấu trúc bảng. Để đảm bảo rằng mọi dữ liệu trong UDTT đáp ứng được mọi tiêu chí, chúng ta có thể tạo những rằng buộc riêng và những khóa chính trên kiểu bảng này. Ngoài ra, chúng ta có thể sử dụng một UDTT để khai báo nhiều TVP (tham số do bảng định giá trị) cho các công cụ hay thủ tục lưu trữ để gửi nhiều bản ghi dữ liệu tới một thủ tục lưu trữ hay một công cụ mà không phải tạo một bảng tạm thời hay nhiều tham số. TVP giờ đây linh hoạt hơn rất nhiều, và trong một số trường hợp nó còn có khả năng thực thi tốt hơn những bảng tạm thời hay cung cấp nhiều phương pháp khác nhau để không phải sử dụng đến các tham số. Sử dụng TVP có các lợi ích sau, không sử dụng khóa cho mẫu dữ liệu ban đầu từ máy trạm, không cần phải biên tập lại lệnh, giảm truy cập tới máy chủ, cho phép máy trạm chỉ định thứ tự sắp xếp và những khóa chính, … Simpo PDF Merge and Split Unregistered Version - Khi các biến của bảng được được chuyển tác như những tham số thì bảng này sẽ được hiên thực hóa trong cơ sở dữ liệu hệ thống tempdb hơn là chuyển tác toàn bộ nhóm dữ liệu trong bộ nhớ, điều này giúp việc xử lý với lượng dữ liệu lớn hiệu quả hơn. Mọi máy chủ thực hiện chuyển tác các tham số biến thiên của bảng được chuyển tác bởi tham chiếu, sử dụng tham chiếu này như một con trỏ tới bảng đó trong tempdb để tránh tạo bản sao cho dữ liệu đầu vào. Ứng dụng Các lập trình viên luôn gặp phải rắc rối khi chuyển nhiều bản ghi trong cơ sở dữ liệu để tận dụng tối đa khả năng thực thi. Ví dụ, khi một lập trình viên cần lập trình cho trang chấp nhận thứ tự gồm nhiều mục thì họ sẽ phải viết theo logic riêng để nhóm mọi lệnh chèn thàng một chuỗi định giới hay kiêu XML Simpo PDF Merge and Split Unregistered Version - (OPENXML trong SQL Server 2000) rồi chuyển những giá trị văn bản đó sang một thủ tục hay lệnh. Thao tác này yêu cầu thủ tục hay lệnh đó phải có logic cần thiết cho việc tháo nhóm những giá trị và làm cho những cấu trúc dữ liệu có hiệu lực, sau đó tiến hành chèn các bản ghi. Quá nhiều thao tác cần thực hiện nhưng lại không tối ưu, trong trường hợp này chúng ta có thể sử dụng TVP để chuyển một bảng dữ liệu gồm nhiều bản ghi từ ứng dụng .NET sang SQL Server và chèn trực tiếp vào bảng mà không phải thực hiện thêm bất kì thao tác nào tại máy chủ này. Hạn chế 1. UDTT tồn tại một số hạn chế, ví dụ nó không thể sử dụng như một cột trong bản, định dạng bảng không thể thay đổi sau khi đã được tạo, một giá trị mặc định không thể được chỉ định trong định dạng Simpo PDF Merge and Split Unregistered Version - của UDTP, … 2. SQL Server không duy trì thống kê các cột của TVP. 3. TVP phải được chuyển như những tham số READONLY nhập vào sang những lệnh T-SQL. Chúng ta không thể thực hiện các tác vụ DML như UPDATE (cập nhật), DELETE (xóa), hay INSERT (chèn) trên một TVP trong phần chính của lệnh. Nếu cần phải thay đổi dữ liệu được chuyển tới một thủ tục lưu trữ hay lệnh biểu hiện tham số trong TVP thì chúng ta phải chèn dữ liệu đó vào một bảng tạm thời bay một biến của bảng. Ngoài ra, chúng ta không thể sử dụng các biến của bảng như các tham số OUTPUT (kết xuất), mà chỉ có thể sử dụng như các tham số INPUT (nhập vào). Ví dụ Simpo PDF Merge and Split Unregistered Version - Trong ví dụ này chúng ta sẽ tìm hiểu phương pháp tạo một kiểu bảng do người dùng định nghĩa, tạo một biến của kiểu bảng này, chèn các bản ghi vào bảng và chuyển nó tới thủ tục lưu trữ như một tham số TVP. Trước tiên chúng ta sẽ tạo một bảng và chèn các bản gi vào đó. Cấu trúc lệnh tạo bảng này như sau: --Tạo bảng lưu trữ thông tin khách hàng CREATE TABLE [Customers] ( [ID] [int] NOT NULL PRIMARYKEY IDENTITY, [FirstName] [varchar](100)NOT NULL, [LastName] [varchar](100)NOT NULL, [Email] [varchar](200) NOTNULL ) GO Simpo PDF Merge and Split Unregistered Version - --Chèn bản ghi vào bảng Customer INSERT INTO [Customers] (FirstName, LastName, Email) VALUES('AAA','XYZ', 'aaa@test.com') INSERT INTO [Customers] (FirstName, LastName, Email) VALUES('BBB','XYZ', 'bbb@test.com') INSERT INTO [Customers] (FirstName, LastName, Email) VALUES('CCC','XYZ', 'bbb@test.com') GO Tiếp theo chúng ta sẽ tạo một UDTT khi đã tạo thành công chúng ta có thể xem chi tiết bảng sử dụng hai System Catalog View: Simpo PDF Merge and Split Unregistered Version - --Tạo một UDTT lưu trữ các bản ghi khách hàng CREATE TYPE [CustomersUDT] AS TABLE ( [FirstName] [varchar](100)NOT NULL, [LastName] [varchar](100)NOT NULL, [Email] [varchar](200) NOTNULL ) GO --Chúng ta có thể sử dụng các Catalog View để xem bảng được tạo SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type FROM SYS.TYPES WHERE is_table_type = 1 SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type FROM SYS.TABLE_TYPES GO Simpo PDF Merge and Split Unregistered Version - Chúng ta cũng có thể sử dụng SQL Server Management Studio (SSMS) để xem các bảng người dùng định nghĩa đã được tạo trong một cơ sở dữ liệu, truy cập vào node User-Defined Table Types trong node Types cửa bảng Object Explorer. Sau đó tạo một thủ tục lưu trữ để chuyển tác biến của UDTT như một TVP. Cần nhớ quy tắc phạm vi cho Simpo PDF Merge and Split Unregistered Version - một biến được áp dụng trong trường hợp này cũng như với biến của UDTT vì biến này sẽ tự động vượt quá phạm vi khi bảng được tạo. Chuyển tác TVP bằng ứng dụng .NET Trước tiên chúng ta cần cài đặt phần mềm .NET Framework 3.5, phần mềm này cung cấp một kiểu cơ sở dữ liệu SQL mới được gọi là Structure bên trong vùng tên System.Data.SQLClient. Đảm bảo rằng DataTable mà chúng ta tạo trong ứng dụng .NET phù hợp với lược đồ của UDTT, nói cách khác thì tên của cột, số lượng cột và các kiểu dữ liệu phải giống nhau. Mặc dù trong một số trường hợp nhất định, nếu kiểu dữ liệu không giống nhau nhưng Simpo PDF Merge and Split Unregistered Version - nếu nó vẫn tương thích với nhau thì vẫn được phép vận hành. //Tạo một bảng dữ liệu cục bộ lưu trữ các bản ghi của khách hàng DataTable dtCustomers = new DataTable("Customers"); DataColumn dcFirstName = new DataColumn("FirstName", typeof(string)); DataColumn dcLastName = new DataColumn("LastName", typeof(string)); DataColumn dcEmail = new DataColumn("Email", typeof(string)); dtCustomers.Columns.Add(dcFirstName); dtCustomers.Columns.Add(dcLastName); dtCustomers.Columns.Add(dcEmail); //Chèn customer 1 DataRow drCustomer = dtCustomers.NewRow(); drCustomer["FirstName"] = "AAA"; Simpo PDF Merge and Split Unregistered Version - drCustomer["LastName"] = "XYZ"; drCustomer["Email"] = "aaa@test.com"; dtCustomers.Rows.Add(drCustomer); //Chèn customer 2 drCustomer = dtCustomers.NewRow(); drCustomer["FirstName"] = "BBB"; drCustomer["LastName"] = "XYZ"; drCustomer["Email"] = "bbb@test.com"; dtCustomers.Rows.Add(drCustomer); //Chèn customer 3 drCustomer = dtCustomers.NewRow(); drCustomer["FirstName"] = "CCC"; drCustomer["LastName"] = "XYZ"; drCustomer["Email"] = "ccc@test.com"; dtCustomers.Rows.Add(drCustomer); //Tạo đối tượng Connection để kết nối tới máy chủ/cơ sở dữ liệu SqlConnection conn = new SqlConnection("Data Simpo PDF Merge and Split Unregistered Version - Source=ARALI-LAPTOP;Initial Catalog=tempdb;Integrated Security=true"); conn.Open(); //Tạo một đối tượng Command gọi thủ tục lưu trữ SqlCommand cmdCustomer = new SqlCommand("AddCustomers", conn); cmdCustomer.CommandType = CommandType.StoredProcedure; //Tạo một tham số sử dụng SQL DB type viz. Structured mới để chuyển đổi như tham số giá trị bảng SqlParameter paramCustomer = cmdCustomer.Parameters.Add("@CustomersTVP", SqlDbType.Structured); paramCustomer.Value = dtCustomers; //Chạy truy vấn cmdCustomer.ExecuteNonQuery(); Simpo PDF Merge and Split Unregistered Version - Kiểu dữ liệu Date và Time mới Trường hợp ứng dụng thực Không ai có thể phủ nhận tầm quan trọng của một kiểu dữ liệu chỉ có thể lưu trữ ngày tháng mà không có thời gian hoặc thời gian mà không có ngày tháng. Ví dụ, để lưu trữ ngày sinh của một nhân viên chúng ta sẽ chỉ cần lưu trữ ngày, còn thời gian là không cần thiết. Tương tự, để lưu trữ những thông tin theo từng giai đoạn trong ngày như từ 00:01 tới 08:00 (giai đ