SQL SERVER 2016 INTERVIEW QUESTIONS FOR EXPERIENCED
Q 1 : What are the new features of sql server 2016?
Ans: Here are the Great Features of SQL server 2016
* Query Store
* Live Query Statistics
* Native JSON Support
* Temporal Database support
* Always Encrypted
* Row Level Security
* PolyBase into SQL Server
* Column Store
* BI for Mobile Devices
* Data Stretch to MS Azure
* SSDT (All in One)
For more Click here(detailed)
Q 2 : What is Query Store in Sql Server 2016?
Ans : Click here to see answers
Q 3: Name any three view which are related to Query Store?
Ans : All data that query store stores is available through the following views:
Q 4 : What do you understand by LQS?
Ans : LQS stand for Live Query Statistics, SQL Server 2016 has a new feature, called LQS, that allows you to view what is happening during the query execution. LQS lets you view a list of active queries and associated statistics such as current CPU/memory usage, execution time, query progress, and so on.
Q 5 : SQL Server 2016 coming with XML alternate which is known as?
Ans : JSON
Q 6 : Why JSON is becoming important than XML, even for MS SQL Server?
Q 7 : What do you understand by Temporal data support, a new freature introduce in SQL Server 2016?
Ans : SQL Server 2016 introduces support for temporal tables as a database feature that provides built-in support for provide information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016
Q 8 : What is Row-Level Security, in SQL Server 2016(new feature)?
Ans : Row-Level Security (RLS) enables developers and DBAs to control access to rows in a database table. Using RLS, you can store data for different customers, departments, or tenants in the same table, while restricting access to rows based on a query’s execution context. For example, you could filter rows returned by “SELECT * FROM myTable” according to the identity of the logged-in user or the value of a session-scoped variable like CONTEXT_INFO.
Q 9 : What are the difference between SQL Server 2014 and 2016?
Q 10 : What do you understand by Polybase in SQL Server?
Q 11 : SQL Server 2016 came with new way to drop object if exist, Explain it.(DROP IF EXIST)
Q 12 : What are the benefits/advantages of this release(SQL Server 2016)? Can you summarize?
# Enhanced in-memory performance provides up to 30x faster transactions, more than 100x faster queries than disk-based relational databases and real-time operational analytics.
# New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes.
#Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.
#Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database.
#Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android.
#Simplify management of relational and non-relational data by querying both with T-SQL using PolyBase.
#Faster hybrid backups, high availability and disaster recovery scenarios to back up and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.