Author: Saleem Hakani (Microsoft Corporation)

You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time?

There are many things that happen behind the scenes that could contribute to the slowness of index operations.

Some of the reasons include:

1. Load on SQL Server,

2. Out of date statistics,

3. Server configuration setting for degree of parallelism,

4. Amount of available memory

5. Amount of available resources,

6. etc.

SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process or the load. This means it could either grant or limit the number of CPU’s Index operations can use. In many cases this is the best practice and you should not change the default value.

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).