Answer to SQL Server Interview Questions & Answers – # 4 ( Part 1 )

Today I will post some of the answers to the questions I posted on yesterday. Also note that answers i am providing are to the point answers, for details please refer online material.

Question –Write a SQL script to implement Fibonacci series in SQL Server up to 200.

Answer – This should not be complex. We can easily achieve this with or without recursion or using any loop. Two sample methods are given below. IF you not aware of Fibonacci series, please visit below URLs-
https://www.mathsisfun.com/numbers/fibonacci-sequence.html
https://www.math.hmc.edu/funfacts/ffiles/10002.4-5.shtml

/****** WITH RECURSION ****/

--

;WITH FibonacciCTE
AS (
 
   SELECT  0 Levels, 0 FibNum, 1 FibNex
   UNION ALL 
   SELECT  a.Levels + 1 Levels, a.FibNex  FibNum, a.FibNum + a.FibNex AS FibNex
   FROM FibonacciCTE a
   WHERE a.FibNex < 200
)
SELECT FibNum FibonacciNumbers
FROM FibonacciCTE f

GO


/****** WITHOUT RECURSION ****/


DECLARE @phi FLOAT
SELECT @phi = (1 + Sqrt(5))/2

;WITH CTE AS
(
	SELECT DISTINCT Number
	FROM master..spt_values
	WHERE number BETWEEN 0 AND 200
)
SELECT
ROUND((POWER(((1 + Sqrt(5))/2), Number) - POWER(-1/((1 + Sqrt(5))/2), Number )) / Sqrt(5) ,1) FibonacciNumbers
FROM CTE
WHERE 
ROUND((POWER(((1 + Sqrt(5))/2), Number) - POWER(-1/((1 + Sqrt(5))/2), Number )) / Sqrt(5) ,1) < 200


--

Question – How to find 2nd highest number from a integer array in C#/C language?( Yes its not related to SQL :))

Answer – One method is sort the array using sort method and get the element present at Array length- 1

Question – What are Spools in Execution Plan? What are the types of Spools in execution plans?

Answer – Please go through the URL below
https://msbiskills.com/2015/07/26/spools-in-execution-plan-are-they-bad/

Question – What is a Partition Column in SQL Server?

Answer – Partition column is a concept used in Table Partitioning. Table partitioning is a way to divide a large table into smaller, more manageable parts without creating separate tables for each part.

Data in a partitioned table is partitioned based on a single column, that column is called partition column, often called the partition key. Note that only one column can be used as the partition column, but it is possible to use a computed column.

Most of the time a date column or a key column is used as the partition column. SQL Server places rows in the correct partition based on the values in the date column. It is important to choose a partition column that is almost always used as a predicate in queries. When the partition column is used as a filter in queries, SQL Server can approach only the relevant partitions and we will get excellent performance.

Question – If the partition column value is NULL, then in which partition rows are inserted or will the insert statement will give error on insertion?

Answer – It will not throw any error. If the partition column value is NULL then rows are placed in the first partition.

Question – What is Range right and range left in SQL Server?

Answer – Partition functions are created as either range left or range right to specify whether the boundary values belong to their left or right partitions:
1. Range left means that the actual boundary value belongs to its left partition, it is the last value in the left partition.
2. Range right means that the actual boundary value belongs to its right partition, it is the first value in the right partition.

--

--Example Partition Function 
CREATE PARTITION FUNCTION Sales (DATE)
AS RANGE RIGHT FOR VALUES ('2011-01-01','2012-01-01','2013-01-01', '2014-01-01', '2015-01-01');

--

Question – What is the Partition Scheme?

Answer – The partition scheme is used to maps the logical partitions to physical filegroups. we can map each partition to its own filegroup or all partitions to one filegroup.

I shall update the remaining answers probably tomorrow. 

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com