1. Introduction

Microsoft works so hard to release new versions of SQL Server with all the improvements and new features every 2 years. In this article, I want to review 5 features that haven’t been utilized by us. I don’t want to call them new features because some of them exist for a long time, we just don’t realize they are there or we just don’t know how to use them.

2. Features

2.1 Feature #1: Table-Valued Parameter

When is it introduced?

Table-Valued Parameter is introduced in SQL Server 2008

What is Table-Valued Parameter?

Table-valued parameters are declared by using user defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set based operations.

Where to use it?

One place to use it is to pass unlimited parameters to a stored procedure. For example, we have such stored procedure usp_process_all. It is designed to let user can delete selected documents. There is a @document_ids parameter that is in varchar(max) type. A better design is to store all selected document_id in a table value parameter and pass it into the stored procedure.

CREATE PROCEDURE usp_process_all

@document_ids varchar(max),

BEGIN

...

END

2.2 Feature #2: CLR in SQL Server

When is it introduced?

CLR in SQL Server is introduced in SQL Server 2008

What is CLR in SQL Server?

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

You can create stored procedure, trigger, user-defined function in SQL Server. But, SQL has very limited ability to handle complicate situations, such as check if date is in a string. The solution is to create a stored procedure, trigger, or function with C#.NET

Where to use CLR in SQL?

To search something in memo field

SELECT TOP 1000 memo

FROM [product]

WHERE memo MATCH('$abc.*')

2.3 Feature #3: Improved Date and Time Types

When are they introduced?

Date, Time, DateTime2, and DateTimeOffset are introduced in SQL Server 2008

What are improved Date and Time types?

There are Date, Time, DateTime2 and DateTimeOffSet added in SQL Server 2008. time, datetime2 and datetimeoffset provide more seconds precision. Datetimeoffset provides time zone support for globally deployed applications.

Date

Defines a date in SQL Server

Time

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

DateTime2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

DateTimeOffset

SQL Server added a new data type named “datetimeoffset”. This is similar to the old datetime data type, with the following significant differences:

- Internally, the time is stored in unambiguous UTC format

- The local time zone offset is stored along with the UTC time, which allows the time to be displayed as a local time value (or converted to any other time zone offset)

- The data type is capable of storing more precise times than datetime

Where to use improved Date and Time Types?

Sometimes, we need to store datetime in UTC format. And then, we can use DateTimeOffset.

2.4 Feature #4: Table Partition

When is it introduced?

Table Partition is introduced in SQL Server 2005 Enterprise Edition

What is table partition?

In order to answer this question, we need to answer following questions.

What are partitions and why use them? The simple answer is: To improve the scalability and manageability of large tables and tables that have varying access patterns. Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger.

What constitutes a large table? While the size of a very large database (VLDB) is measured in hundreds of gigabytes, or even terabytes, the term does not necessarily indicate the size of individual tables within the database. A large database is one that does not perform as desired or one in which the operational or maintenance costs have gone beyond predefined maintenance or budgetary requirements. These requirements also apply to tables; a table can be considered large if the activities of other users or maintenance operations have a limiting affect on availability. For example, the sales table is considered large if performance is severely degraded or if the table is inaccessible during maintenance for two hours each day, each week, or even each month. In some cases, periodic downtime is acceptable, yet it can often be avoided or minimized by better design and partitioning implementations. While the term VLDB applies only to a database, for partitioning, it is more important to look at table size.

In addition to size, a table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns. Although usage patterns may not always vary (and this is not a requirement for partitioning), when usage patterns do vary, partitioning can result in additional gains in management, performance, and availability. Again, to use the example of a sales table, the current month's data might be read-write, while the previous month's data (and often the larger part of the table) is read-only. In a case like this, where data usage varies, or in cases where the maintenance overhead is overwhelming as data moves in and out of the table, the table's ability to respond to user requests might be impacted. This, in turn, limits both the availability and the scalability of the server.

Additionally, when large sets of data are being used in different ways, frequent maintenance operations are performed on static data. This can have costly effects, such as performance problems, blocking problems, backups (space, time, and operational costs) as well as a negative impact on the overall scalability of the server.

How can partitioning help? When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections. The table partition feature focuses on horizontal partitioning, in which large groups of rows will be stored in multiple separate partitions. The definition of the partitioned set is customized, defined, and managed by your needs. Microsoft SQL Server 2005 allows you to partition your tables based on specific data usage patterns using defined ranges or lists. SQL Server 2005 also offers numerous options for the long-term management of partitioned tables and indexes by the addition of features designed around the new table and index structure.

Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations. The performance of large-scale operations across extremely large data sets (for instance many million rows) can benefit by performing multiple operations against individual subsets in parallel. An example of performance gains over partitions can be seen in previous releases with aggregations. For example, instead of aggregating a single large table, SQL Server can work on partitions independently, and then aggregate the aggregates. In SQL Server 2005, queries joining large datasets can benefit directly from partitioning; SQL Server 2000 supported parallel join operations on subsets, yet needed to create the subsets on the fly. In SQL Server 2005, related tables (such as Order and OrderDetails tables) that are partitioned to the same partitioning key and the same partitioning function are said to be aligned. When the optimizer detects that two partitioned and aligned tables are joined, SQL Server 2005 can join the data that resides on the same partitions first and then combine the results. This allows SQL Server 2005 to more effectively use multiple-CPU computers.

History of partition

Partitioning Objects in Releases before SQL Server 7.0 (This is designed by application designer. We are still doing this today)

Partitioned View in SQL Server 7.0 (Only allow SELECT)

Partitioned View in SQL Server 7.0 (Also allow INSERT, UPDATE, DELETE)

Partition Table in SQL Server 2005

Where to use Table Partition?

Application may stores a lot of data. For example, one of our application has a table that contains more 1 million records. The application performance becomes unbearable when user tries to review data in those big tables. We did a lot of table optimization and designed archive data to store old data. If we use table partition, then we don’t need to create archive database and manually move old data into it anymore.

2.5 Feature #5: Pagination

When is it introduced?

Table Partition is introduced in SQL Server 2005 Enterprise Edition

What is pagination?

Pagination is a common use case throughout client and web applications everywhere. Google shows you 10 results at a time, your online bank may show 20 bills per page, and bug tracking and source control software might display 50 items on the screen.

Based on the indexing of the table, the columns needed, and the sort method chosen, paging can be relatively painless. If you're looking for the "first" 20 customers and the clustered index supports that sorting (say, a clustered index on an IDENTITY column or DateCreated column), then the query is going to be pretty efficient. If you need to support sorting that requires non-clustered indexes, and especially if you have columns needed for output that aren't covered by the index (never mind if there is no supporting index), the queries can get more expensive. And even the same query (with a different @PageNumber parameter) can get much more expensive as the @PageNumber gets higher – since more reads may be required to get to that "slice" of the data.

Some will say that progressing toward the end of the set is something that you can solve by throwing more memory at the problem (so you eliminate any physical I/O) and/or using application-level caching (so you're not going to the database at all). Let's assume for the purposes of this post that more memory isn't always possible, since not every customer can add RAM to a server that's out of memory slots, or just snap their fingers and have newer, bigger servers ready to go. Especially since some customers are on Standard Edition, so are capped at 64GB (SQL Server 2012) or 128GB (SQL Server 2014), or are using even more limited editions such as Express (1GB) or whatever they're calling Azure SQL Database this week (many different servicing tiers).

SQL Server 2012 added OFFSET / FETCH that provides more linear paging performance across the entire set, instead of only being optimal at the beginning.

Where to use pagination?

We have a long history of trying to tackle the pagination challenge in a web application.

In the beginning, there is ASP.NET default pagination. We retrieve all the data from database and load them into DataGrid. If the search result has 100K records, even we just want to show the first page, we will transfer the 100K records from database server to application, load them into DataGrid, then transfer the 100K records from application to client browner. This generates a lot of network traffic and makes application very slow.

Then, we start to use custom paging in DataGrid, so we will only load the first data into DataGrid and tell DataGrid what’s the total number of records. In order to do so, we created two routings to get current page and total number. Because there is not always a record number to let us get the current page, so we need to load data into a temp table in SQL Server, and then based on the auto-generated record number to get the current page.

SQL Server 2005 introduced Window function, it can be used to avoid using temp table get record number, so we are able to get the current page much quicker. However, window page is not optimized for pagination, so there is still performance impact.