SQL Server 2012 has plenty of new features for everyone, from developers to administrators. There are several features for high availability and database administration that will improve DBA operations. We're going to look at four of them: increased options for online indexing, adding new columns without locking tables, increased limit in number of partitions and user-defined server roles.
More data types supported for online indexing
Online indexing is, no doubt, a great feature for databases that require non-stop uptime, because you can rebuild or add an index without locking a table. However, there are many data types not supported by the online indexing feature.
While this is great addition, I am sure many SQL Server users would've preferred Microsoft to instead make the online indexing features available in the Standard Edition, as they did with backup compression between SQL Server 2008 and SQL Server 2008 R2. Unfortunately, that didn't happen and online indexing remains available only in the Enterprise Edition.
Adding a column with a default is now a metadata-only operation
If you have ever tried adding a column that does not allow nulls to a very large table, you probably know that this operation takes a lot of time. SQL Server has to expand each row and insert the default value. If a data page does not have a lot of room, SQL Server has to split the page into two and move some data to the new page. This can take a very long time on large tables, even hours on very large tables. During this operation, the table is locked and unavailable; therefore, something like this can be done only when the table is not used.
This can be very complicated for shops that run 24/7, allowing you to complete such operations only during maintenance windows. In SQL Server 2012, though, adding an index with a default value becomes a metadata-only operation, meaning it executes fast under certain conditions. The default value has to be a runtime constant, meaning it has to return the same value every time the expression is evaluated. The other restriction is that it only works for basic data types, and not for these data types -- varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography and CLR UDTS. Moreover, this feature only works in Enterprise Edition.
Number of allowed partitions increased to 15,000
Table partitioning is a useful scalability feature, especially suited for large tables with billions of rows. Managing massive amounts of data by adding and removing smaller partitions makes management of very large tables much easier. The previous versions of SQL Server limited the number of table partitions to 1,000. While this may seem like a lot, the limit could still be restrictive. For example, if you partition by day, your partitioned table can only keep data for slightly less than three years. Increasing the 1,000 to 15,000 greatly extends the possible range of keeping daily data partitions.
You may recall that one of the new features of the SQL Server 2012 data engine is a column-store index, which, under certain conditions, can result in queries running hundreds times faster. One of the limitations, though, is that once you create a column-store index on a table, that table become read-only. The only way to update the table is to create a partition, insert data into that partition and then switch it into the table. Since partitioning is used as a workaround to bypass the read-only restriction, the need to use partitioning will be greater and increasing the number of available partitions will come in handy.
User-defined server roles
In previous versions of SQL Server, you could create custom database roles, but not server roles. SQL Server came with a set of roles defined by Microsoft, but they did not always suit specific needs in certain environments. You can now create a new role and include one or more permissions to perform certain operations. You can then add members to the new custom role, as well as include memberships in the already existing server roles.
For more on SQL Server 2012 features
Check out the SQL Server 2012 guide
Little-know features of SQL Server 2012
One good use for something like this would be creating several custom server-wide roles with limited number of permissions for junior DBAs. Then, as DBAs become more experienced and more familiar with the environment and can move to the next level of database administration, you can change their privileges by simply making them members of custom server-wide roles with greater privileges than they had at the previous level.
All these new features in SQL Server contribute to having more options and more flexibility for keeping SQL Server highly available for applications, as well as allowing more flexibility for defining server-side permissions.
This was first published in April 2013