Author: Saleem Hakani (Microsoft Corporation)

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.

Author: Saleem Hakani (Microsoft Corporation)

SQL Server 2012 introduces a brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

 

Many of the services companies run within a windows domain network uses Windows Authentication. However, there are many companies that still use Mixed Authentication. If you are allowing SQL Server standard logins for access to your SQL Server, then you must ensure that your SA account is locked down as completely as possible and that only key people in the team know the password.

Author: Saleem Hakani

IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

Author: Saleem Hakani (Microsoft Corporation)

Many users have hated the way SQL Server supported configuring startup parameters in previous versions. It was confusing as it was buried under the Advanced Tab of SQL Server services under Configuration Manager in a single text box. The only way you could separate different options was by using a semicolon(;) between the options.

SQL Server 2012 introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This eliminates the need of explicit data conversions when concatenating two values.

Author: Saleem Hakani (Microsoft Corporation)

SQL Server 2012 introduces a new feature called T-SQL Snippets. T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBA’s. Snippet templates in SQL Server 2012 are based on XML with predefined fields and values. When you use T-SQL snippet, these fields are highlighted and the user can tab through each fields and change the values as required.

Consider the following problem, how would you give someone access to parts of a table without giving them permissions on the table directly? Column level permissions or views can be used to project only the required columns; however, when a permission check is done, it is intuitive to check access to the view and the underlying base table.

OWNERSHIP CHAINING

In SQL Server, when an object is accessed through a chain, the owner of the object referenced and the calling object is checked. If both objects have the same owner, permissions on the referenced object is not evaluated. This is called ownership chaining. This idea is extended beyond just database objects to work across databases as well (called Cross-database ownership chaining).

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does it do, what are its advantages and disadvantages and how can you leverage this technology in SQL Server? Keep reading this tips and tricks article to learn how.

Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.

When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

A common scenario in data warehousing applications is knowing what source system records to update, what data needs to be loaded and which data rows can be skipped as nothing has changed since they were last loaded. Another possible scenario is the need to facilitate searching data that is encrypted using cell level encryption or storing application passwords inside the database.

Data Hashing can be used to solve this problem in SQL Server.

A hash is a number that is generated by reading the contents of a document or message. Different messages should generate different hash values, but the same message causes the algorithm to generate the same hash value.