New Features in SQL Server 2016

Always Encrypted

Always Encrypted feature is designed to protect the data. With this feature enabled data is always be encrypted within SQL Server. Access to encrypted data is only available to the applications calling SQL Server.  This enables client application owners to control who gets access to see their applications confidential data.  It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. Encryption and decryption of data happens transparently inside the application. This means the data stored in SQL Server will be encrypted which can secure it from DBA and administrators but that also has considerations for ad-hoc queries, reporting and exporting the data.

PolyBase

PolyBase allows you to query distributed data sets. With this feature you will be able to use T-SQL statements to query Hadoop or SQL Azure blob storage. By using PolyBase you can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage.  This allows you to get data from Hadoop without knowing the internals of Hadoop.  Additionally you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data. As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data.

JSON Support

JSON stands for Java Script Object Notation. JSON (JavaScript Object Notation) is a standardized data exchange format that is currently not supported natively by SQL Server. With JSON Support now you can interchange JSON data between applications and the SQL Server database engine. By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format. Additionally, with JSON support you can take relational data, and turn it into JSON formatted data.  Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server. Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server

Multiple TempDB Database Files

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine.  In the past, up through SQL Server 2014, you always had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server.  Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.

Query Store

One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.

Row Level Security

A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID. I've seen some customized implementations of RLS at clients in the past, and they weren't pretty. It is hard to execute at scale. The implementation of RLS in SQL 2016 still has it limits (updates and inserts are not covered), but it is good start on a much-needed feature.

Temporal Table

A temporal table is table that holds old versions of rows within a base table.  By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.  The temporal table is physically a different table then the base table, but is linked to the base table.  If you’ve been building or plan to build your own method to managing row versioning then you might want to check out the new temporal tables support in SQL server 2016 before you go forth and build your own row versioning solution.

Matrix Report in SSRS

In this tutorial, we are demonstrating step by step example of creating a simple Matrix Report in SSRS.

Need of Matrix Report?
Matrix Reports are required where you have to display some aggregated data and at least one of grouping column need to displayed as column in the report. It is something similar to Pivot we use in normal T-SQL where we convert rows to columns using Pivot.

Now let's have a walk-through of a Matrix Report

Example:
We will create a matrix report where sum of sales will be displayed Year and Product wise in a matrix form and Year will be displayed as columns (similar to pivot in SQL)

We would be using Shared Data source we created in previous article: Shared Data Source

Step 1: Create a Shared Data Source
We would be using this shared data source to fetch the data from the database for the report.

Step 2: Right Click Reports>Add New Report, Report Wizard will appear to configure the report. Click Next.

Step 3: Configure Data Source. Select the Shared Data Source we created in Step 1 and Click Next

Step 4: Design Query either thru Query Builder or write your own query and Click Next.

select  P.ProductNumber, P.Name as ProductName
        , YEAR(SOH.OrderDate) as Year, SOD.LineTotal as SaleAmount
from    Sales.SalesOrderDetail SOD
inner join Sales.SalesOrderHeader SOH on SOH.SalesOrderID = SOD.SalesOrderID
inner join Production.Product P on P.ProductID = SOD.ProductID

 

Step 5: Select Matrix as Report Type and click Next.

Step 6: All available field will be displayed in Available fields Section. Configure the fields like below by clicking on Columns, Rows & Details buttons respectively.

• Year Field moved to Columns as it is to be displayed as Column 
• ProductNumber & ProductName Fields moved to Rows as they are to be displayed as Rows 
• SalesAmount to Details as it is to be aggregated based on Year and Product. 

Once Configured Click Next

Step 7: Here, you can select Table Style. Default Style is Slate. Click Next

Step 8: At this stage, we are done with configuration of reports. Change the Report Name to YearProductWiseSale and Click Finish.

Step 9: Click Finish will create a report. You can configure design part like color, height, width etc..manually

We are done with creating a Simple Matrix Report.  Let's run the report and see the output.

To run the report, set this report as StartItem in Project Properties.

Right Click Project>Properties. Select the report we just created as StartItem.

Now let's run the project and see the output of the report

Query Parameter in SSRS

Query Parameters are used to filter the result set return from the database similar to where condition applied in a T-SQL statement. It limits the number of rows return back from the database to the report, thus improving the performance.

Let's create a report to see how Query Parameters work.

For this, we will  apply Query Parameter on a Tabular Report we created in an earlier article.

Step 1: Create a simple Tabular Report
First Create a simple Tabular Report using above link. Once report is created we would apply Query Parameters on the report

Step 2: On Report Data Tab, Click Parameters>Add Parameter

Configure Report Parameter Properties as below and click OK

Step 3: This will add GroupName Parameter under Parameters like below.

Step 4: Now let's apply the Parameter on DataSet to filter the result-set. In Report Data Tab right click DataSet1 under Datasets and then click Dataset Properties

Step 5: In Query Tab under Dataset Properties, change the Query to

select  * from HumanResources.Department where GroupName = @GroupName

Step 6: Select Parameters Tab, you will find Parameter Name @GroupName that we added in the query in Where clause in previous Step. Select Parameter Value as @GroupName from the list (created in step 3) and click OK

Step 7: At this step we are done with adding Query Parameter to the Report. Now let's build the Solution and run the report. The Parameter Group Name that we created started appearing in the report to filter the result set. Enter Manufacturing in the text-box and click View Report

Look at the output, the report is filtered with Group Name as Manufacturing

Creating Tabular Report in SSRS

In this tutorial, we will create a Simple Tabular Report in SSRS.

We would be using Shared Data source we created in previous article: Shared Data Source

Step 1: Create a Shared Data Source
We would be using this shared data source to fetch the data from the database for the report.

Step 2: Right Click Reports and then Add New Report, Report Wizard will appear to configure the report. Click Next

Step 3: Configure Data Source. Select the Shared Data Source we created in Step 1 and Click Next

Step 4: Design Query either thru Query Builder or write your own query and Click Next.

select  * from HumanResources.Department

Step 5: Select Tabular as Report Type and click Next.

Step 6: You can configure to display the data based on grouping but as of now we are working on a simple tabular report, so just click Finish.

Step 7: Here, you can select Table Style. Default Style is Slate. Click Next

Step 8: At this stage, we are done with configuration of reports. Click Finish.

Step 9: Click Finish will create a report. You can also configure color, height, width etc..manually

We are done with creating a Simple Tabular Report.

To run the report, set this report as StartItem in Project Properties.

Right Click Project>Properties. Select the Report we just created as StartItem.

Now let's run the project and see the output of the report

Create Shared Datasource SSRS

In this post, we will see how to create a Shared Data Source in SSRS.

Data Source contain the information necessary to retrieve the data from the relational database to render the reports. If a data source is not shared, it is stored inside a report and can not be used by other reports.

In general, we need to have a data source that can be used in all the reports in the same project. Such data sources are called as shared data source.

Now let's walk through how to create a shared data source

Prerequisites

• SQL Server 2012 with Reporting Services
• AdventureWorks Database

NOTE: We would be using AdventureWorks Sample Database which is shipped with SQL Server 2012 Enterprise Edition. If you have Express Edition you have to download it.  Download here.

Step 1:  Open SQL Server Data Tools and create a new Project  Business Intelligence > Report Server Project and name it as SSRS-Tutorials

Step 2: Right Click Shared Data Source, then Add New Data Source. A new pop pup window will appear to configure Data Source Properties. Change the name of Data Source to AdventureWorks

Step 3: Click on Edit, a new pop up window will appear to configure Connection Properties like Server Name, Authentication and Database

Step 4: Click OK twice to close the popup windows and Shared Data Source AdventureWorks is created.

AdventureWorks Database for SQL Server 2012

AdventureWorks Sample Database for SQL Server 2012 can be downloaded from CodePlex site using below link.

Download AdventureWorks2012 Data File (Case Sensitive)

The data file name is AdventureWorks2012_CS_Data.mdf as the collation of database is case sensitive.

you can download case insensitive version from the below link:

Download AdventureWorks2012 Data File (Case Insensitive)

Once the data file is downloaded, run below script to attach the downloaded AdventureWorks Data File to SQL Server 2012

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\SampleDatabases\AdventureWorks2012_CS_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

In the script change, you need to change your path in FILENAME where you have placed the AdventureWorks Data File.

SQL Server 2016 CTP 3.0 download

Microsoft has recently announced the Community Technology Preview 3.0 (CTP 3.0) update of SQL Server 2016.

Using Microsoft Live Account, you can login and download the CTP 3.0 Evaluation version using any of the below links. If you don't have Live account, you can register one.

• Download the CTP 3 Evaluation version
• Download the CTP 3 Evaluation version

Alternatively, you can download directly using below link

Direct download link:  SQLServer2016CTP3.0-x64-ENU.iso

Using DVD burning software, burn this ISO file on to DVD. Then, run setup.exe file to start the installation.

Alternatively, you can install software that can mount your ISO file. Once mounted, a new drive will start appearing in "My Computer" (similar to when you plugin Pen Drive)

Note : You must have administrative rights on the computer to install SQL Server 2016 CTP 3.0.

Key Notes :

• The release is not supported by Microsoft Customer Services and Support (CSS).
• The release is available for testing purposes only and should not be installed and used in production environments.
• Side-by-Side installation with down-level production SQL Server instances as well as in-place upgrades of down-level production SQL Server instances, is supported for SQL Server 2008 and higher.

SQL Server Interview Questions (Part-4)

What is a transaction?
A transaction is a logical unit of work in which, all the steps must be performed or none.
Read more in detail...

What are ACID properties
ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

Difference Between Implict Transaction And Explict Transaction
Implicit Transaction is auto commit and there is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction, Commit Transaction and Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.

What is the use of @@TRANCOUNT in SQL Server?
@@TRANCOUNT returns the number of active transactions for the current connection.

What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

What is NOLOCK?
NOLOCK is used to query data which is affected by the transaction  but yet committed. Querying uncommited data using NOLOCK is called "dirty read"

How to Get nth Record in a Table?
Refer this post : find nth highest salary

How to generate Random number in SQL Server?
Refer this post : generate random number for each row in SQL

How to delete duplicate record in SQL?
Refer this post : delete duplicate records in SQL

How do we find the last date of current month?
SQL Server 2012 introduced function EOMONTH to find the last date of the month
In prior versions, we have to write our own logical script
Refer this post : Last day of Month

What is computed column in SQL Server?
Computed Column is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators. Computed columns can be used in select list, where clause, order by clause.
Read more in detail with example...

What is the use of @@ROWCOUNT?
@@ROWCOUNT returns the number of row(s) affected as a result of DML statement like Insert/Update/Delete.

<< Prev

SQL Server Interview Questions (Part-3)

What is user-defined function
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

What is the use of COALESCE in SQL Server?
COALESCE accepts "n" no of parameters and returns first not null values from the parameters passed to the function.
Read more in detail with example... 

What is Row_Number()?
ROW_NUMBER() is used to generate a unique row number in the result set.
Read more in detail with example...

What is Apply Operator?
The Apply operator acts like a Join without the ON clause. The Apply function in a query allows you to join a table to a table-valued function in such a way that function is invoked for each row returned from the table
Read more in detail with example...

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters.

Syntax

STUFF (string_expression, start, length, replacement_characters)

• string_expression is the string that will have characters substituted
• start is the starting position
• length is the number of characters in the string that are substituted
• replacement_characters are the new characters interjected into the string.
• REPLACE function is used to replace existing characters of all occurrences. 

Syntax 

REPLACE (string_expression, search_string, replacement_string)

Every incidence of search_string found in the string_expression will be replaced with replacement_string.

What is a Stored Procedure?
Stored Procedure in nothing but a set of T-SQL statements combined to perform several set of statements as a single task.

What are the advantages of using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
1024

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.
From within Query Analyzer is an option called "Show Execution Plan"
(located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Read more in detail with example..

How to pass data table to stored procedure as parameter in SQL Server 2005 (Write stored procedure) ?
Refer this post : Passing Data Table to Stored Procedure

How to achieve paging from SQL side.
Refer this post : Custom Paging

<< Prev Next >>

SQL Server Interview Questions (Part-2)

What is the difference between a Local and a Global temporary table?
Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is #temp table and @table variable in SQL Server?
Both temp table and table variable are used to store data temporarily for a particular session.
Both have some similarities and differences.
Read More in detail...

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
It is called Heap. A heap is a table that does not have any index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is difference between Delete and Truncate commands?

Delete is a DML Command	Truncate is a DDL Command
"Where" clause can be used to remove partial rows	Can't use "Where" clause
Slow as maintain transaction logs	Faster as transnational log is not maintained
Rollback is possible from log files	Rollback is not possible from log files.
Delete does not reset identity of the table.	Truncate resets the identity of the table
Delete activates Trigger because the operations are logged	Does not activate trigger because operations are not logged.

What is Trigger?
Triggers are special types of stored procedures which are automatically executes when an event occur in database.
Read more in Detail...

How many types of triggers are there?
There are four types of triggers.

1. Insert
2. Delete
3. Update
4. Instead of

Read more in Detail...

What are Magic Tables?
Whenever DML operations insert/update/delete are performed "INSERTED" and "DELETED" are created automatically. These tables are called Magic Tables.
Read more in detail...

<< Prev Next >>

SQL Server Interview Questions (Part-1)

Which TCP/IP port does SQL Server run on?
SQL Server by default runs on port 1433. However it can be changed from the Network Utility TCP/IP properties.

What are the authentication modes available in SQL Server?

• Windows mode 
• Mixed Mode - SQL and Windows. 

When UPDATE_STATISTICS command is used?
The indexes need to be updated when a large no. of DML operations like insertion, deletion and modifications are done.UPDATE_STATISTICS command performs this job.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.
Read more in detail...

Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

Difference between char and varchar?
Char takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n" where as Varchar takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data.
Read more in detail...

What is the use of Set NOCOUNT ON?
By Default, When a command is executed it returns the number of affected record as result of the command. UsingSET NOCOUNT ON commands will no longer returns the number of affected records in the current scope.

What is an index?
An index is used to speed up the performance of queries. They are created for faster retrieval of data.

What are the difference between clustered and a non-clustered index?
A Clustered index is an index that sorts the data physically on the basis of columns used in clustered index and therefore only one clustered index is possible. The leaf nodes of a clustered index contain the data pages.
A Non Clustered index is an index in which the logical order of the index does not match the physical stored order of the rows. The leaf node of a non clustered index does not consist of the data pages, instead, the leaf nodes contain index rows.

What are Constraint?
SQL Server users constraints restricts only valid data to be inserted into column of a table.
There are following types of constraints.
Primary Key, Foreign Key, Unique, Default, Check, Not Null.

What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

What is FOREIGN KEY?
A FOREIGN KEY constraint allows to create relationship between tables. It ensures that data in FOREIGN KEY column should exists in the referring PRIMARY KEY column thus mainlining the referential integrity

What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the data in a set of columns, so no duplicate values are entered.

What is CHECK Constraint?
A CHECK constraint is used to restrict the set of values that can be entered in a column. The check constraints are used to enforce domain integrity.

What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity.

WHAT is the difference between a Primary Key and a Unique Key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where as unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULL, but unique key allows one NULL.

Next >>

Temp Table vs Table Variable in SQL

Both temp table and table variable are used to store data temporarily for a particular scope. You can perform all DML operations on both like you perform in a normal table. In terms of querying data they are similar to normal tables except the scope which is limited to a particular session.

Name of temp table starts with "#" and name of table variable starts with "@"

Syntax for Temp Table

CREATE TABLE #temp (id int, name VARCHAR(100))

Syntax for Table Variable

DECLARE @tab TABLE (id int, name VARCHAR(100))

Both have some similarities and differences.

Similarities

• Both are instantiated in TempDB (System Database)
• Clustered index can be created on both Temp Table and Table Variable
• Perform any DML operations like Insert/Update/Delete.

Differences

• Transaction logs are logged for Temp tables but not for Table Variable
• Tables variables can not have non-clustered index
• Indexes can be explicitly created on Temp tables but not on table variable i.e index can be created while creating table only
• Scope of table variable is limited to the Object only in which it is declared while scope of temp variable is the session in which it is created.
• Temp table can be created using "SELECT INTO" but not Table variables.
• Table variables can be used as parameter.
• Default collation for table variable is collation of the current database where as temp tables takes the default collation of tempdb.

Cross Apply vs Outer Apply in SQL Server

Cross Apply and Outer Apply comes under Apply Operator which was introduced in SQL Server 2005

Apply function allows to join a table to a table-valued function in such a way that function is invoked for each row returned from the table which you can't do with Join and is the main difference between Join and Apply

• The Cross Apply only returns rows from the left side table if the table-valued-function returns rows.
• The Outer Apply clause returns all the rows of the left side table regardless of whether table-valued-function returns any row or not. If no row is returned by table-valued-function, the columns that the table-valued-function returns are null.

Let's take an example to understand in more detail.

In the below example we have used split function (Table Valued Function) in which we would pass comma separated string and it would return a table

First understand and created split function before running the below snippet using the link : split function in SQL Server

declare @tab table(Category varchar(20), item varchar(max))
insert into @tab
select 'Vegetables', 'Carrot,Tomato' union all
select 'Fruits', 'Apple,Banana,Grape' union all
select 'Beverages', null

select t.Category, s.val as item
from   @tab t
cross apply dbo.split(item, ',') s

select t.Category, s.val as item
from   @tab t
outer apply dbo.split(item, ',') s

OUTPUT

Look at the output.

• First output with Cross Apply: No row with Category "Beverages" in the output
• Second output with Outer apply: Getting a row with Category "Beverages" in the output

Reason for difference:

• Function is returning null value for Category "Beverages" because there is null value in item column for "Beverages"