This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the new development features of SQL Server2016?
- Native JSON Support
- Always Encrypted
- Query Store
- Live Query Statistics
- Row Level Security
- Temporal database support
What does the Query Store do in SQL Server 2016?
- Store the history of query plans in the system.
- Capture the performance of each query plan over time.
- Identify queries that have “gotten slower recently”.
- Allow you to force plans quickly.
What do you know about Live Query Statistics of SQL Server 2016?
It is a new feature of SQL Server2016 that allows you to view what is happening during the query execution.
It lets you view a list of active queries and associated statistics such as current CPU/Memory usage, execution time, query progress, and so on.
What is a good alternative of XML data type in SQL Server 2016?
The newly introduced JSON data type is one of the best option and alternative of XML data type.
Why JSON is becoming more important than XML?
One of the biggest reasons JSON is becoming more important than XML is that XML has to be parsed with an XML parser, while JSON can be parsed by a standard JavaSript function and it is very light-weight.
This makes it easier and faster than working with XML.
What is Row-Level Security in SQL Server 2016?
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.
What are the two types of character data SQL Server supports?
- Regular and Unicode.
- Char and Varchar is Regular character data type.
- NChar and NVarchar is Unicode character data type.
How are literal strings expressed with the Unicode character column?
It must start with N’text’.
What is the difference between Datetime and Timestamp data types?
Datetime is a data type.
Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Timestamp is a synonym of the Rowversion data type.
What is SWITCHOFFSET function?
SWITCHOFFSET function is used to convert the time from one time zone to another time zone, and it will work for date time offset fields.
How to SELECT n Random records from a Table in the database?
1 |
SELECT TOP 3 *FROM tbl_TableName ORDER BY NEWID(); |
Which stored procedure is used to send an email in SQL Server?
sp_send_dbmail: The msdb database stored procedure.
Which stored procedure is used to update a description of the column?
We can update column description using sp_updateextendedproperty.
How we can get the Database name using SQL Command?
1 |
SELECT DB_NAME(); |
I want to concatenate NULL value with string and do not require output as NULL only.
How do you achieve this?
Using,
1 2 3 4 5 6 7 8 9 |
SET CONCAT_NULL_YIELDS_NULL ON / OFF; SET CONCAT_NULL_YIELDS_NULL ON: PRINT 'dbrnd.com' + NULL; Result is NULL. SET CONCAT_NULL_YIELDS_NULL OFF: PRINT 'dbrnd.com' + NULL; Result is dbrnd.com |
How can you insert multiple rows together in a single insert statement?
1 2 3 4 |
INSERT INTO tbl_Tablename (id,name) VALUES (1,'abc'),(2,'xyz'),(3,'pqr'); |
How will you delete all records of table other then recently added 10 records?
1 2 3 4 5 6 |
DELETE FROM table_name WHERE id not in ( SELECT TOP 10 ID FROM table_name ORDER BY date DESC ); |
What is the solution of below error?
Msg 7411, Level 16, State 1, Procedure , Line 3 Server ‘SERVERNAME\MSSQL2012’ is not configured for DATA ACCESS.
Require to enable DATA ACCESS on SQL Server Instance.
1 |
EXEC sp_serveroption 'SERVERNAME\MSSQL2012', 'DATA ACCESS', TRUE |
Can you execute Stored Procedure in View?
Yes: Using OPENQUERY, we can execute stored procedure in view.
What is a @@FETCH_STATUS in T-SQL?
To know the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.