- Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible.
- The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory.
- This dramatically reduces the chances of error or oversight.
- Identity (or AutoNumber) is a column that automatically generates numeric values.
- A start and increment value can be set, but most DBA leave these at 1.
- A GUID column also generates numbers; the value of this cannot be controlled.
- Identity/GUID columns do not need to be indexed.
- SELECT @@IDENTITY – returns the last IDENTITY value produced on a connection
- SELECT IDENT_CURRENT(‘tablename’) – returns the last IDENTITY value produced in a table
- SELECT SCOPE_IDENTITY() – returns the last IDENTITY value produced on a connection
- This join returns rows when there is at least one match in both the tables.
- Select * From Table1 Inner Join Table2 ON table1.ColumnName = Table2.ColumnName
- This join returns all the rows from the left table in conjunction with the matching rows from the right table.
- If there are no columns matching in the right table, it returns NULL values.
- Example – Select * From Table1 LEFT Join Table2 ON table1.ColumnName = Table2.ColumnName
- This join returns all the rows from the right table in conjunction with the matching rows from the left table.
- If there are no columns matching in the left table, it returns NULL values.
- Example – Select * From Table1 RIGHT Join Table2 ON table1.ColumnName = Table2.ColumnName
- This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
- Example – Select * From Table1 FULL Join Table2 ON table1.ColumnName = Table2.ColumnName
It is a specific type of comparator-based join, or the join that uses only equality(only =) comparisons in the join-predicate.
- It produces the Cartesian product of the tables involved in the join.
- A cross join that does not have a WHERE clause
- The size of a result set is the no of rows in the 1st table multiplied by the no of rows in the 2nd table.
- Example: is when company wants to combine each product with a pricing table to analyze each product at each price.
Same table is specified twice with two different aliases in order to match the data within the same table.
- It offers a further specialization of equi-joins.
- The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.
- The resulting joined table contains only one column for each pair of equally-named columns.
- Example – SELECT * FROM Vendor NATURAL JOIN advance
Disclaimer – F5debug Interview Questions & Answers Series:
You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.