This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Should we allow NULL or We should apply NOT NULL?
Database Design: Should we allow NULL or We should apply NOT NULL ?
“SELECT *” is good practice or bad practice in database system?
What is Left Anti Semi Join Operator?
The Left Anti Semi Join operator returns the result set for each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. Left Anti Semi Join is a logical operator.
Why you should not use a cursor? What are its alternatives?
Microsoft requests us to avoid using cursors due to Memory and RAM overhead including comparatively more DISK IO. Alternatives: WHILE LOOP / CASE / Temprary Tables / Derived Tables / Sub Queries can be used in place of Cursors.
What are Dynamic Queries in T-SQL?
Dynamic T-SQL Queries are those queries designed on the fly / at run time using Variables or using CTE or other sources. We use SP_EXECUTESQL Stored Procedure or EXECUTE function to execute such queries.
How you can load large data in SQL Server?
IMPORT and EXPORT Wizard, BULK INSERT and BCP are the possible options. We need to set the RECOVERY MODEL to BULKLOGGED before we perform such activities.
What is Mutex error in Triggers?
The only use of “mutex” that I know of is for a mechanism for re-entrant code that can’t be simultaneously executed by multiple processes to force that code to be single threaded. It’s not a typical SQL Server term. If it were, it would apply as much to stored procedures as triggers. Generally, in a well designed database with well written code, this would be handled by using standard data locking and transactions.
What is the use sp_refreshview?
It updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Syntax:
EXECUTE sp_refreshview ‘view_name’;
Can you create a Primary key without clustered index?
Yes, you can create a Primary Key with a Non Clustered Index. But by default creation of PK automatically creates a clustered index upon the table.
What are the virtual tables in Triggers?
Inserted & Deleted are virtual tables in Triggers.
How will you know what indexes a particular table is using?
By checking the graphical Execution Plan.
By using sys.dm_db_index_usage_stats DMV.
How many types of internal joins are there in SQL Server?
- Nested loop join
- Merge join
- Hash join
What are Join Operators?
A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical joins between two sets of data.
The SQL Server Optimizer may choose a different algorithm for different scenarios based on the requested query, available indexes, statistics and number of estimated rows in each data set.
It’s possible to find the operator which was used by looking at the execution plan that SQL Server has prepared for your query.
What are the Nested Loops Joins?
The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. All these variants are considered by the query optimizer.
What are the Merge Loops Joins?
The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.
Because each input is sorted, the Merge Join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, the lower-value row is discarded and another row is obtained from that input. This process repeats until all rows have been processed.
What are the Hash Joins?
Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department. These modifications use only one input for both the build and probe roles.
What is Logical Join and Physical Join?
Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.
Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.
What is an In-Memory Hash Join?
The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table.
This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key’s value is computed, the corresponding hash bucket is scanned, and the matches are produced.
What is a Grace Hash Join?
If the build input does not fit in memory, a hash join proceeds in several steps. This is known as a grace hash join. Each step has a build phase and probe phase. Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files.
Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. The hash join is then applied to each pair of partitioned files.
What is a Recursive Hash Join?
If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.
If the optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. The hash join makes sure that it uses the smaller overflow file as build input. This technique is called role reversal. Role reversal occurs inside the hash join after at least one spill to the disk.
What is COMPUTE BY statement?
COMPUTE BY statement has both detail and summary rows within the SELECT statement. It calculates the summary values for subgroups, or a summary value for the entire result set.
