Interview Question - How to INSERT data into two tables with one INSERT statement

Well the answer is simple use OUTPUT clause with your INSERT statement and you should be able to achieve it.

Given below is the example 


-- First Table in which we will INSERT data

DECLARE @TAB TABLE
(
NUM INT IDENTITY(50,1),
ID INT

)

-- Second table to hold the indentity value from the first table
-- This table will be populated using OUTPUT clause


DECLARE @TAB1 TABLE
(
ID1 INT

)

Interview Question - Write a query to produce required output

Recently, a friend of mine faced an interesting question in an interview which from the look of it looked worth sharing

Firstly, interviewer asked him about his comfort level in SQL scripting to which he replied as he is Alright but there is a lot of scope for an improvement. Then interviewer presented him with the following table 

and asked him to write a query to produce the following output within 3 minutes

Interview Question - What is the difference between LEN() and DATALENGTH() function ?

This question was faced by friend in a project allocation interview.

From the look of it both the function appears to be same but they are not. One gives the total number  (count) of characters in the string while second gives the number of bytes occupied by the characters in the string. 

As a SQL developer while dealing with string, quite often we end up using LEN() function. But DATALENGTH() isn't used that often.

The LEN() function is a String function while DATLENGTH() comes under data type function.

Interview Question - How to combine result and display where the data is coming from

This one is the interview question faced by my friend during project allocation interview. 

We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.

 


The query needs to be written to produce the output given below

MS-CRM - Query to list the roles that user belongs to in a business unit

One day my client asked me to give him the list of CRM users and their roles as defined in the CRM organization. He also laid down couple of conditions 

• If a user belongs to more than one role then roles must be displayed as a comma separated string in a single row.
• We must know whether that user is in disabled state or not
• What is the business unit of the User

Luckily for me all this information was store in 3 objetcs (Table / view)

• SystemUser
• SystemUserRole
• Role

So given below is what I came up with

Interview Question - How to separate positive and negative numbers from column

This one is the interview question asked to one of my friend for the post of t-SQL developer. 

We have a table with ID column containing following values in it 

And the Output should be 

How would you go about it and write the query for it.

MS-CRM - Query to list the relationship between the Entities

In the recent past, I have been associated with multiple data migration projects. All of them had one thing common, the destination for all those projects was MS-CRM database. 

There were occasions when it was required to know the parent/child entity relationship before SSIS package for the migration could be designed. For this information, I used to bug CRM team of respective project every now and then, thanks to my exceptional remembering/recalling power and unfamiliarity with CRM Tool . :) 

CRM Team had enough of me one day and told me "So what you don't know CRM tool, entity relationship information can be extracted from CRM database as well." A
And it triggered me to write this script and believe me CRM Team was more happy than I was when I was done with it..

Issues while Importing data from Excel to SQL using SSIS - 1 - Uniqueidentifier column

There are multiple issues which one can run into while importing data from Excel to SQL server. Although SSIS has pave quite a easy way to accomplish this task but failing to paying attention to details can run us into multiple issues.

In this part we will be discuss the issue related to column from excel with Uniqueidentifier data in it. 

I have an excel file (GUIDShow) with following column structure and data in GUIDDesti sheet

Excel Data

ID - Int
UniqueId - String

Way to replace individual characters from the string

In one of the project that I am associated with, we had a requirement to replace each occurrence of the characters from the input string by the characters of users choice.

For example -

Suppose we have a string - "Chetan Deshpande" 
and we have been asked to

• Replace D  by C
• Replace E  by O
• Replace S  by U
• Replace H  by N

Split comma seperated string values

Few days back I was told to write a stored procedure which would accept comma separated string as an input. The requirement was such that the values from this comma separated string had to be compared/combined with table later. 

The first thing I did was, to search for a function over internet, which would segregate the comma separated string into values. That search showed me many links which had a function written to address this task. Few of them are listed at the bottom of this article.

I used the third one (from the list) and completed the procedure.

Then few days later when I had some spare time, I thought of writing my own little function to split the strings. And ended up writing the one that is given below..

It might not be the ideal solution when it comes to performance but I just had to write one :)

Interview Question - Find distinct values without using distinct

I have appeared for an interview @ several companies and have observed that few questions pops up every now and then. These questions aren't tricky but still have an ability to throw otherwise brilliant T-SQL scripters of the hook. Thus it is better to prepare for them in advance.

One such question is How can you find the distinct values from a column of a table, (big pause ....wait for it ..) without using DISTINCT ? 

Now, why would you want to do that when Microsoft has been generous and has given us DISTINCT clause to use in such situation. Well the answer is because you want that job so either answer it or ....

Given below are the ways that are @ top of my head right now

SQL script to find if date is a part of Leap Year or Not - 2

In my previous blog entry on the same topic, I have mentioned the logic and function used to find the Leap year. 

In this article with little bit tweak in the logic we are trying to find the same information. 

I haven't included this script as a part of function. This is standalone script which would return 1 or 0 depending on whether the evaluated date is from a leap year or not.

The logic used this time around is given below

SQL script to find if date is a part of Leap Year or Not - 1

There are times when we wish to check the date that we are dealing with belongs to the leap year or not. 
I came across such a situation and ended up designing a function for it. 
This function takes the date as an input and returns BIT value depending on whether year of the 
date passed qualifies as a leap year of not.

The logic that I used here is given below

• Fetch the year of the date passed.
• Using it form the first and last day of year
• Calculate the difference between FirstDayOfYear and LastDayOfYear

SQL script to list columns with NULL values for all the records

Recently, my client asked me to give him the list of columns which have NULL value for all the records of that Table. This has to be done on the CRM database.

There might be better ways to fetch this information but this is what I did

• Find out total number of rows present in concerned table
• List the columns without NOT NULL constraint
• Loop over this column list using cursor by substituting it in Count(column name) in a dynamic query

SQL script to deal with Day Light Savings in SQL server

We have a situation wherein the data from one particular table needs to be displayed in different time-zones. We store the history of one particular SSRS report in this table. This report provides the UI to refresh the data of few tables. It executes one particular procedure in the back-end to do so. The same procedure is scheduled to run on weekly basis using SQL agent as well.
To log last refresh time and the place (agent or report) from where it is done, we created a table in which an entry is made as soon as the procedure is called from either (SQL agent or SSRS report)

Displaying data in different timezone is easy until day light savings rule comes into picture. In order to overcome this I wrote a SQL script so that particular date gets converted to expected timezone.

In the below given script I have tried to display the date in following timezones

• Melbourne
• EST
• PST
• CST
• UTC

SQL script to find the latest file in the folder

With the below given script one would be able to find out the latest modified file of specific type (extension)
in a directory

To achieve this, XP_CMDSHELL is used to list the files from the directory. Then extension of the file is provided
in a select statement to retrieve the file which has latest modified timestamp on it