1.What do you understand by Adaptive query processing launched in SQL Server?
Answer: SQL Server and Azure SQL Database introduce a new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions.
2.Name all three Adaptive query processing features?
Answer. In SQL Server and Azure SQL Database there are Three adaptive query processing features by which you can improve your query performance:
- Batch mode memory grant feedback.
- Batch mode adaptive join.
- Interleaved execution.
3.Write a T-SQL statement to enable adaptive query processing?
Answer: You can make workloads automatically adaptive query processing by enabling compatibility level 140 for the database. You can set this using Transact-SQL. For example:
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
4.Name the new string function which is very useful to generate a CSV file from a table?
Answer: CONCAT_WS is a new function launched in SQL Server its takes a variable number of arguments and concatenates them into a single string using the first argument as the separator. It requires a separator and a minimum of two arguments. It is very helpful in generating comma or pipe separated CSV file content.
5. How you will find the LEN in a case?
Answer: We can use following tick Select LEN(‘A value ‘ + ‘x’) – 1
6.What is the use of the new TRIM function?
Answer: It Removes the space character char(32) or other specified characters from the start or end of a string.
7.Is SQL Server 2021 support Python?
Answer: Yes
8. What is log shipping?
Answer: Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping, the transactional log file from one server is automatically updated into the backup database on the other server.
9.What is NOT NULL Constraint?
Answer: A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
10.What are the types of database recovery models?
Answer: There are 3 types of database recovery models available
- Full
- Simple
- Bulk Logged
11. Define Synonym?
Answer: Synonym is an alternative method to creating a view that includes the entire table or view from another user to create a synonym. A synonym is a name assigned to a table or view that may thereafter be used to refer to it.
12.What is an active database?
Answer: Active database is a database that includes active rules, mostly in the form of ECA rules(Event Condition rules). Active database systems enhance traditional database functionality with powerful rule processing capabilities, providing a uniform and efficient mechanism for database system applications
13.What is a linked server?
Answer: A linked server enables you to work with other SQL Servers as well as databases other than SQL Server databases, right from within Management Studio.
14. What is the difference between a DDL trigger and a DML trigger?
Answer: A DDL trigger executes in response to a change to the structure of a database (for example, CREATE, ALTER, DROP). A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).
15. What database does SQL Server use for temporary tables?
Answer: TempDB
16.What are the types of transaction levels in SQL SERVER?
Answer: There are four transaction levels in SQL SERVER.
- Read committed
- Read uncommitted
- Repeatable read
- Serializable
17.What is SQL Profiler?
Answer: SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later.
18.What are Data files?
Answer: This is the physical storage for all of the data on disk. Pages are read into the buffer cache when users request data for viewing or modification. After data has been modified in memory (the buffer cache), it is written back to the data file during the checkpoint process.
19.What are the different locks in SQL Server?
Answer: There are six types of locks
- Intent (I)
- Shared (S)
- Update (U)
- Exclusive (X)
- Schema (Sch)
- Bulk Update (BU)
20.Difference between Len() and DataLength()?
Answer: DATALENGTH()- returns the length of the string in bytes, including trailing spaces. LEN()- returns the length in characters, excluding trailing spaces.
For example:
SELECT LEN(‘string’), LEN(‘string ‘), DATALENGTH(‘string’), DATALENGTH(‘string ‘), LEN(N’string’), LEN(String ‘), DATALENGTH(N’string’), DATALENGTH(N’string ‘) Will return 6, 6, 6, 9, 6, 6, 12, 18
