扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
作者:Vaughn Washington 来源:code-magazine 2007年11月19日
关键字: SQL Server 2008 SQL Server 数据库
SQL Server Katmai, now officially announced as SQL Server 2008, introduces a significant amount of new and improved functionality, including enhanced data types and greater programming flexibility for database application developers.
In this article I’ll look at the range of data access technologies available for leveraging the power of SQL Server 2008. After I show you how to select the right technology for your needs, I’ll dive into the improvements, first examining a new construct for programming data. Then I’ll turn to expanding the set of building blocks-data types-available to developers. Taking a brief pause, I’ll discuss how SQL Server 2008 removes limitations for some scenarios. Then building on what the article has covered, I’ll examine new out-of-the-box solutions for common application scenarios. The article will hopefully leave you with a new understanding of the possibilities for taking advantage of Katmai: SQL Server 2008.
Choosing a Data Access Technology
SQL Server offers a wide range of data access technologies to developers. The best place to start the discussion on taking advantage of new programmability features is how to choose the right one. For new application development, using the .NET Framework and specifically the SQL Server provider in ADO.NET, System.Data.SqlClient, will be the best choice in most cases. If you’re working within an existing application with business logic and associated data access code in native C/C++, you can choose from the SQL Server Native Client ODBC driver or the OLE DB provider. Both options allow you to take advantage of the full set of features of SQL Server 2008 and the choice will usually be based on your application requirements.
Additional data access options include Windows Data Access Components (WDAC)-new in Windows Vista, previously named Microsoft Data Access Components (MDAC)-and the Microsoft SQL Server 2005 JDBC Driver for Java environments. For purposes of this discussion, I’ll focus on SqlClient and SQL Server Native Client; for more information on these and other data access technologies, visit http://msdn.com/data.
To get the most out of the new functionality, you’ll need to use .NET Framework 3.5 or SQL Server Native Client 10.0 which works side-by-side with previous versions. One important takeaway is that you don’t have to rewrite your application from the ground up to take advantage of SQL Server 2008. Instead, you can gain significant value from incremental changes to your existing data access layer. With that said, let’s dive in.
New Programming Constructs: Table-Valued Parameters
One of the most requested new features by developers is the ability to cleanly encapsulate tabular data in a client application, ship it to the server in a single command, and then continue to operate on the data as a table in T-SQL. The simplest such use case is the long-desired array data type. Traditionally, applications solve this need by doing one of the following:
None of these solutions is ideal. The pivoted parameter solution, in addition to being ungraceful, creates code that is difficult to maintain that’s also tough to move forward when the time comes to add a new “column” to the conceptual “table.” The bulk insert solution is clumsy when the application needs to do any additional filtering or apply more complex logic. The parameter array solution, while it may perform well for small data volumes, becomes untenable for larger batches both on the client where memory consumption may become a problem and on the server where per-row invocations of the procedure provide non-optimal performance.
Table-valued parameters (TVPs), believe it or not, address all of these problems. TVPs provide an improved programming model and significant performance benefits in certain scenarios.
Imagine a simple order processing scenario. Using TVP starts with defining a table type in T-SQL on the server:
-- TSQL to CREATE a TABLE TYPE tt_OrderItemsNext, create a stored procedure that uses the table type you just created and additionally takes the customer who placed the order as a parameter:
-- TSQL to CREATE a PROCEDURE sp_AcceptOrderThis example is fairly simple, but it illustrates a big win for developers, which is that if you can update an application to implement its business logic to use set-based operations on a batch of data, it should see significant performance gains.
Here are the application changes needed to use table-valued parameters. When using table-valued parameters, client applications generally have two possible programming models:
For small batches of data, the performance difference on the client will usually be negligible, so choosing the simpler programming model may be your best choice. On the server side there will be no performance difference between the models.
Diving into the details for a moment, ADO.NET accomplishes the first model by extending the SqlParameter class to take a DataTable as a value. DataTable’s ubiquity in data application programming makes it an ideal choice for this simple model. SQL Server Native Client OLE DB accomplishes the same model by leveraging the COM IRowset interface and by introducing an implementation that allows buffering. SQL Server Native Client ODBC uses a similar method modeled after parameter arrays where applications allocate and bind arrays of buffers. For the streaming model, ADO.NET supports specifying DbDataReader and IEnumerable<SqlDataRecord> as a parameter value, which provides a solution for both external and user-defined data sources. In much the same vein, SNAC OLE DB accepts a custom IRowset implementation and ODBC builds on the data-at-execution (DAE) paradigm by accepting DAE table-valued parameters. All providers also expose a rich set of services for discovering and describing table-valued parameter metadata.
" |
When you can update an application to use table-valued parameters, it should gain the benefits of having cleaner, more maintainable code in both the client and server application tiers; faster performance, particularly when you use set-based operations on the server side by leveraging the power of the SQL query processor; and better scalability for large data volumes. |
" |
When you can update an application to use table-valued parameters, it should gain the benefits of having cleaner, more maintainable code in both the client and server application tiers; faster performance, particularly when you use set-based operations on the server side by leveraging the power of the SQL query processor; and better scalability for large data volumes. In addition to enhancing the programming model, SQL Server 2008 introduces new intrinsic data types to better align with the precise needs of applications.
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。
现场直击|2021世界人工智能大会
直击5G创新地带,就在2021MWC上海
5G已至 转型当时——服务提供商如何把握转型的绝佳时机
寻找自己的Flag
华为开发者大会2020(Cloud)- 科技行者