What is an alias in SQL?
- In SQL Server, aliases can be used to create a temporary name for columns or tables.
- Column aliases are used to make column headings easier to read in your result set.
- Table aliases are used to shorten your SQL for easier reading or when performing joins.
- An alias exists only for the duration of the query.
- The AS keyword is used to create an alias, but it is not required in all SQL Products.
- We frequently get No Column Name as the output column name when performing arithmetic expressions, concatenating columns, or calling system or built-in functions in the select statement. We can use an alias to provide a meaningful name for it.
What is a computed column?
A computed column is basically a virtual column that is not physically stored in the table.
What is a computed column with PERSISTED?
A computed column that is marked as PERSISTED will have its data stored physically.
What are LEAD and LAG functions?
The LAG function gets the information from a past column, while LEAD brings information from an ensuing line. The two functions are fundamentally the same as one another and you can simply supplant one with the other by changing the sort request.
What is the NTILE function?
It divides/distributes an ordered data set (or partition) into a specified number of groups which we call buckets and assigns an appropriate (bucket) number to each row. The bucket number will represent each row to which bucket it belongs.
In other words, it is used to divide rows into equal sets and assign a number to each row.
What is the difference between RANK and DENSE_RANK?
This function is used to assign a rank to the rows based on the column values in the OVER clause. The row with equal values is assigned the same rank with the next rank value skipped.
The DENSE_RANK analytics function is used to assign a rank to each row. The rows with equal values receive the same rank and this rank is assigned in sequential order so that NO rank values are skipped.
What is the difference between RANK and ROW_NUMBER?
This function is used to assign a rank to the rows based on the column values in the OVER clause. The row with equal values is assigned the same rank with the next rank value skipped.
This function represents each row with a unique and sequential value based on the column used in the OVER clause.
What are the FIRST_VALUE and LAST_VALUE functions used for?
The First_Value function in SQL Server returns the first value in an ordered set of values and the Last_Value function returns the last value in an ordered set of values. These two functions are classified as analytical functions since they return the first and last values from a result set’s ordered partition.
What is the difference between CAST and CONVERT functions in SQL Server?
The CAST function converts an expression from one data type to another. This is ANSI-SQL specification.
The CONVERT is SQL implementation-specific function. Though it does the same as the CAST function does and there isn’t any difference in performance however CONVERT function allows a more noteworthy broadness of flexibility when converting among date and time values, fractional numbers, and monetary signifiers.
What is the difference between CONVERT and TRY_CONVERT?
The CONVERT is SQL implementation-specific function that converts an expression from one data type to another. It will cancel the transaction in case of conversion failure.
TRY_CONVERT will return a null value when the conversion fails, instead of canceling the transaction and returning an error.
Big Data and SQL 