1. What is cross join in SQL server joins?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
2. What is Inner Join in SQL server joins?
A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
3. What is Outer Join in SQL server joins?
A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
☛ Left Outer Join
☛ Right Outer Join
☛ Full Outer Join
4. What is Left Outer Join in SQL server joins?
In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
5. Explain dataWarehousing in SQL server?
☛ Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together.
☛ Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time.
☛ Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
☛ Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
6. Explain Identity in SQL Server?
Identity (or Auto-Number) 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.
7. What is multi-statement table-value user-defined function?
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-update-able view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
8. What is inline table-value user-defined function?
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-update-able view of the underlying tables.
9. What is scalar user-defined function?
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and time stamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
10. Explain user defined functions?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
