I've been using SQL Server for many years now, I think I first used version 6.5 many years ago and I've used every version since 2000.

• Pass a set of data to stored procedures with table-valued parameters.
• I hardly ever see views used appropriately. Views are SQL Servers implementation of derived relations in Relational Algebra. Views should be used for encapsulating business logic. For example, consider a database that contains order, there should be views that correspond to new orders, in-progress orders and complete orders.
• Indexed views can be used to quickly query summarized data. For example, in a database containing orders you can create an indexed view that stores the total quantity and the quantity filled so far.
• the snapshot isolation level eliminates read locks and generally allows for more concurrency and less deadlocks using Multiversion concurrency control
• The READ_COMMITTED_SNAPSHOT option which enables MVCC as the default isolation level
• the OPTIMIZE FOR query hint is useful for optimizing stored procs that have range conditions (e.g. date range) or LIKE conditions
• Forcing a stored proc to generate a optimial query plan for it queries using the WITH RECOMPILE option is useful when the proc queries ranges or like conditions

SQL Server 2016 is a game-changer in that it causes you to think again about appropriate applications of SQL Server. Why has the game changed?

• Memory-optimized tables with full durability and ACID properties give around 30 times performance improvement compared to traditional tables
• Memory-optimized tables can be used as faster temp tables and table variables
• Native compilation of stored procedures speeds up stored procedure execution
• Automatic population of audit tables using System versioned tables
• SQL Server now has a JSON data type as well as the XML data type
• Automatically updated column store indexes
• Columnstore indexes on memory optimized tables enabling real-time analytics

The following restrictions apply:

• No database SNAPSHOTs are allowed of a database which uses Memory-optimized tables
• Memory-optimized tables are not allowed to contain columns with the RowVersion type
• No MERGE statements in native stored procedures
• No joins in UPDATE or DELETE statements in native stored procedures
• No CASE statements in native stored procedures, although there is a work-around

The MERGE statement has several issues to be wary about:

• When merging from a temp table or table variable make sure that the source has a primary key otherwise concurrent MERGE statements can deadlock due to reading source rows in random order and taking out a page lock. See merge statement deadlocking itself
• Use WITH (HOLDLOCK) hint to avoid race conditions on insert leading to primary key violation errors. See Race condition with merge

• Script to list foreign key constraints without a supporting index