Sunday, 31 May 2015

SQL Server Important and common interview queries

Question 2: Using CTE, how to write a recursive query which will define the organization hierarchy level ?







Solution - We can achieve the above situation by the following query-

With CTE
As
(
Select E1.EmployeeID,
E1.FirstName, E1.LastName,
 E1.ManagerID,
CAST(''as nvarchar(30)) as [ManagerF],
CAST(''as nvarchar(40)) as [ManagerL],
 1 as EmpLevel from dbo.#MyEmployees E1 where managerId is null

Union ALL

Select E2.EmployeeID,
E2.FirstName, E2.LastName,
 E2.ManagerID,
 EL.FirstName as [ManagerF],
 EL.LastName as [ManagerL],

 EL.EmpLevel + 1 as EmpLevel from dbo.#MyEmployees E2
Inner Join  CTE as EL on EL.EmployeeID=E2.ManagerID
where E2.ManagerID is not null
)

Select
FirstName + ' ' + LastName as [Employee Name]
,ManagerF + ' ' + ManagerL  as [Manager Name]

, EmpLevel  from CTE

SQL Server Important queries

SQL Server Important and common interview queries

Question 1:Write a query which will produce the below output ?











Solution - We can achieve the above situation by the following ways -

Query 1
Select 
  T0.ID 
 ,T0.Num
,(Select SUM(Num) from #Temp T1 where T1.Id<=T0.ID) as Result
From #temp T0
Query 2
Select
T0.ID
,T0.Num
,SUM(T1.Num) as Result
from #Temp T0 
Inner Join #Temp T1 on T1.Id<=T0.Id
Group by T0.ID ,T0.Num

SET ANSI_NULLS ON/OFF Setting in Sql Server

Most often we see in SQL server query analyzer, it is written by experts T-SQL programmer - SET ANSI_NULLS ON/OFF, To better understand this I have followed few articles and providing the below explanation with an example.

This option is useful for NULL values comparison. When we set it to ON it follows the ISO standard and according to it NULL values can be compared only with IS or IS NOT keywords.

Refer the below example for better understanding -

Let's create a table and insert few records with NULL values, then we will apply search on NULL values with different situations.

Create a table
Create table #Temp
(
Id int identity(1,1)
, Name Varchar(100)

)

Insert records with NULL values

Insert into #Temp(Name) Values('John'),('Michel'),(NULL)

Above table will look like -













Set ANSI_NULLS ON
When we SET ANSI_NULLS ON, It follows the ISO standard while searching for NULL values. According to the ISO standard, always use IS or IS NOT key words while searching for the NULL values. If we wan to search NULL values by '=' or '<>' operators it will not give any result in this case.

Refer the below results
SET ANSI_NULLS ON 
SELECT *FROM #TEMP WHERE NAME IS NULL







SET ANSI_NULLS ON 
SELECT *FROM #TEMP WHERE NAME IS NULL












Above results are as expected.

Apply '=' or '<>' operator for searching of null values
SET ANSI_NULLS ON 
SELECT *FROM #TEMP WHERE NAME = NULL

and

SET ANSI_NULLS ON 
SELECT *FROM #TEMP WHERE NAME <> NULL








Both of the queries with '=' and '<>' will give the empty result as above and this is not as per expectation because ISO standard do not allow to use '=' or '<>' operator to apply search on NULL values.

Set ANSI_NULLS OFF
We can use '=' and '<>' operators with search on NULL values if we set ANSI_NULLS to OFF.

Refer the below results

SET ANSI_NULLS OFF 
SELECT *FROM #TEMP WHERE NAME = NULL








SET ANSI_NULLS OFF
SELECT *FROM #TEMP WHERE NAME <> NULL












Now we can see the above results and it is as per expectation.

Important points -

1. Search on NULL value will always work with IS and IS NOT keywords. If in case it is required to apply search on NULL values based on '=' or '<>' operators then Set ANSI_NULLS OFF, It will produce the result as expected.

2. It is followed as good practice to Set ANSI_NULLS ON.

3. Do not use  Set ANSI_NULLS ON inside the stored procedures

4. Indexed view and computed column requires SET ANSI_NULLS ON otherwise UPDATE/INSERT will be failed.

5  5Always use SET ANSI_NULLS ON while creating stored procedures or indexes.
      
     I hope above post will be useful. 

d

I

Wednesday, 20 May 2015

How to remove any extra column from a CSV file using C# .NET ?

Scenario - We need to process in which it is required to upload data from a .csv file on the daily basis using the SSIS package. It was observed from the past few days that the few extra columns are coming to the .csv file with same name which already existing. Because of this situation our flat file data source is throwing an error.

Resolution - In order to remove the above error we need to handle the repeated column from the source input .csv file and to make an automatic process for this task, code into script task has been written and it resolved this problem.

Work around steps

1. Downloading the file and acquiring the HTTP:// location
//Acquire the connection
Object objNative = Dts.Connections["HTTPConnection"].AcquireConnection(null);
//Create an object of httpclientconnection
HttpClientConnection objHttp = new HttpClientConnection(objNative);
//Download the file to the below location
objHttp.DownloadFile("\\xxxxxxxxxxx\\xxxxxxxx\\Sample.csv", true);

2. Use the Below function to validate the .csv check extra columns -
        /// <summary>
        /// Method: To check the extra column named - [Product Type] 
        /// </summary>
        /// <param name="pRenamedFilePath"></param>
        /// <param name="pActualFilePath"></param>
        private void ValidateExtraColumn_ProductType(string pRenamedFilePath, string pActualFilePath)
        {
          
            //String builder for normal rows string handling
            StringBuilder tCSVNormalRows = new StringBuilder();
            int tCountProType = 0;

            using (StreamReader tReader = new StreamReader(pActualFilePath))
            {
                string[] col = tReader.ReadLine().Split(',');


                for (int i = 0; i < col.Length; i++)
                {
                    if (col[i].ToString().Trim().ToUpper().Replace(" ", "") == "PRODUCTTYPE")
                    {
                        tCountProType++;
                    }
                }
                if (tCountProType == 2) //Remove the secord [product type] column
                {


                    //Assign the empty values to the string builder variables           
                    tCSVNormalRows.Append("");
                    tCSVNormalRows.Append("Reference Number,OEM Number,Retail,Cost,Consumer Copy,Product Type,Product Long Description,Manufacturer,MAC,UOM,Dimensions,Sell Weight,Product Category,Stock Status,QOH STL,QOH CAR,QOH DAL,QOH FRN,UPC,Country Of Origin,Sell Qty,Product Name");
                    tCSVNormalRows.AppendLine();
                    //read the whole stream
                    while (!tReader.EndOfStream)
                    {
                        var line = tReader.ReadLine();
                        string[] tstrMain = line.Split(',');

                        //Normal rows: read them into string builder for regenerate the file
                        tCSVNormalRows.Append(line.Replace("," + tstrMain[13].ToString().Trim(), ""));
                        tCSVNormalRows.AppendLine();
                    }

                }

            }

            if (tCountProType == 2) //Remove the secord [product type] column
            {
                //Rename the existing file
                if (File.Exists(pActualFilePath))
                {
                    //Delete the file
                    File.Move(pActualFilePath, pRenamedFilePath);
                }

                //Create the file
                File.Create(pActualFilePath).Dispose();
                //Write all the normal data lines into the above created csv file
                File.WriteAllText(pActualFilePath, tCSVNormalRows.ToString());
            }

        }