Skip to main content

MS SQL Server Interview Questions

Displaying 1 - 4 of 4

Can a foreign key contain null or duplicate value?

Yes, Foreign key contains null value or duplicate value. A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.

You can have multiple foreign keys on the same table. Unlike primary keys, foreign keys can contain duplicate values. Also, it is OK for them contain NULL values. Indexes aren't automatically created for foreign keys; however, as a DBA, you can define them. A table is allowed to contain more than one foreign key.

What is DENSE_RANK function in MS SQL Server?

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

Syntax of DENSE_RANK Function

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Example of DENSE_RANK Function

Product Table

ProductID  LocationID  Quantity
---------  ----------  --------
1          2           50      
2          3           45      
3          2           45      
4          1           40  

Product List Table

ProductID  LocationID  Quantity
---------  ----------  --------
1          2           50      
2          3           45      
3          2           45      
4          1           60      
5          2           50      
4          2           60 

DENSE_RANK Query

mssql> SELECT pl.ProductID, p.Name, pl.LocationID, pl.Quantity, 
DENSE_RANK() over (PARTITION BY pl.LocationID ORDER BY pl.Quantity DESC) AS RANK 
FROM ProductList as pl INNER JOIN Product p on pl.ProductID = p.ProductID 
WHERE pl.LocationID BETWEEN 1 AND 2 
ORDER BY pl.LocationID;

Output

ProductID  Name      LocationID  Quantity  RANK
---------  --------  ----------  --------  ----
4          Keyboard  1           65        1   
4          Keyboard  2           70        1   
5          Mobile    2           50        2   
1          Laptop    2           50        2   
3          Mouse     2           45        3

If you will use Rank() function instead of DENSE_RANK() function in above query, Rank for Mouse will be 4.

What is PARTITION BY in MS SQL SERVER?

Partition By Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition. We use it inside Over Clause.

Syntax of Over Clause

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

What is the rank function in MS SQL Server?

 Rank Function, Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

Syntax of Rank Function

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )  

Example of Rank Function

Product Table

productID  Name    
---------  --------
1          Laptop  
2          Desktop 
3          Mouse   
4          Keyboard
5          Mobile  

ProductList Table

ProductID  LocationID  Quantity
---------  ----------  --------
1          2           50      
2          3           45      
3          2           45      
4          1           65      
5          2           50      
4          2           70  

Query

mssql> SELECT pl.ProductID, p.Name, pl.LocationID, pl.Quantity,
Rank() over (PARTITION BY pl.LocationID ORDER BY pl.Quantity DESC) AS RANK 
FROM ProductList as pl INNER JOIN Product p on pl.ProductID = p.ProductID 
WHERE pl.LocationID BETWEEN 1 AND 2 
ORDER BY pl.LocationID;

Output

ProductID  Name      LocationID  Quantity  RANK
---------  --------  ----------  --------  ----
4          Keyboard  1           65        1   
4          Keyboard  2           70        1   
5          Mobile    2           50        2   
1          Laptop    2           50        2   
3          Mouse     2           45        4 
Subscribe to MS SQL Server Interview Questions

About

At ProgramsBuzz, you can learn, share and grow with millions of techie around the world from different domain like Data Science, Software Development, QA and Digital Marketing. You can ask doubt and get the answer for your queries from our experts.