Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

• Identify the relationship hierarchy
• Start deleting the data from the bottom i.e from child tables to the parent tables

Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ? 

Query - To list the jobs and their steps from where SSIS packages are getting executed- SQL 2008

Recently, I was asked to write a SQL query to fetch the information of the enabled jobs and their steps where SSIS package (stored in SQL server) is getting called directly.

As most of us know information related to jobs and other SQL agent related stuff is stored in the MSDB database in SQL server. There are quite a few tables which comes handy while trying to fetch the job details and honestly, I did not know where do I look for the required information. While browsing for the data I got to know about the following tables which provides all the necessary information 

• sysjobsteps - Stores the job step details 
• sysjobs - Stores the information related to jobs

Note - I noticed that when the package (stored in SQL server) is called from the job step, "command" column in the sysjobsteps starts with "/SQL". Following query is based on this observation only and I could be wrong in assuming that hence please feel to correct me in case I'm wrong. Also if there exists an easier way to find the job-package details please let me know..  

SQL Script

Tip - Query to list the accounts running the SQL services

It will be quick this time around ..

Have you guys ever encountered a situation when you badly had to know the account name under which some remote SQL server and SQL server Agent services are running. 

Ahh you did, great.. So what did you do ? went to SQL box and found out it using services.msc or SQL config manager ?  alright Good. 

Let me make situation a bit more challenging ..

What if you can not remote into SQL BOX because of limited access? Now what will you do ?Pretty frustrating isn't it. Just to know the account name for the SQL service you have to raise a ticket with IT and wait for them to respond. 

But what if I tell you, when you can query that server fine why bother jumping onto the server itself? Yes,it can be achieved using simple SQL statement. Isn't that awesome? if you ask me.. YES, it is.

Starting SQL 2008 R2  MICROSOFT has solved this problem for us and presented 

• dm_server_services - The dynamic management view to report status information about the services

Well how about that.. let us see it in action 

MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

Interview Question - What is the difference between Stored Procedures and Functions ?

A very common question, usually asked when the candidate is applying for SQL developers position. 

I have listed few of the differences that I can think of right now. 

Please feel free to let me know 

• In case you know any additional differences
• You think the difference listed below are not correct

Interview Question - How to get the count of rows for each table of the particular database ? How.....

This is simple yet important question which may feature when someone is interviewing for SQL developer position.  

The question goes like this 
"How to get the count of rows for each table of the particular database ? How many ways you can think of to fetch the details ?"

Again second part of the question made it interesting because now interviewer wants to understand your knowledge about different ways of Looping in SQL server ?

One obvious answer for this question would be using CURSOR but I'll leave that to you to write instead I'll try to use in-build looping mechanism that comes handy in this situation.

Interview Question - How to generate values from 1 to 1000 ? without using .....

This question comes across as a straight forward question but can prove lethal to gauge the turnaround time. It also checks the ability of the person to reach the destination when virtually all the doors are closed.

Well the complete question is

" How to generate values from 1 to 1000 without using WHILE loop and Cursor ? and Using single SELECT or block of code"

The question became interesting with the last part because most of us would have thought about using loop/cursor but that is forbidden.. and we also have to use single SELECT or block of code .. 

Okay.. What does that mean ? Is it some kind of a clue to answer this question ? 

MS-CRM - Query to list the TimeZones for the Active Users

In MS-CRM database as we know end user can set the timezone of his choice for himself / herself. And after that all the data that end user sees on CRM UI would be displayed in the timezone that he/she has opted for. 

Now, the question is where does CRM stores the TIMEZONE related information? hence comes the TimeZoneDefinitionBase table to rescue. 

It holds the info such as 

• TimeZoneCode - Unique Integer value representing each timezone
• UserInterfaceName - Displays info in-terms of UTC +/- Hrs
• StatndardName - Gives away the global standard name for the TimeZone that everyone can understand

Interview Question - What will be output for the query ?

Today's question deal with somewhat neglected part of the SQL server. I say Neglected because it is very rarely used.

What will be the output for the following query 

DECLARE @TEST TABLE
(
COL1 SMALLINT,
COL2 SMALLINT
)


INSERT INTO @TEST
SELECT 13,76

SELECT 
COL1 & COL2  AS [&]
,COL1 | COL2 as [|]
,COL1 ^ COL2 AS [^]
,~COL1 AS [~]
,~COL2 AS [~2]
,~ COL1 | COL2 & COL1 ^ COL2 AS Precedence
FROM @TEST

Tip - IN clause other way around

How to do you write a query to fetch the records from a table where particular value is present in 2 different columns or in either of them ? 

Phewww.. pretty simple isn't it ?

Let us give it a shot...

DECLARE @TAB TABLE
(
ID SMALLINT,
NUM SMALLINT
)

INSERT INTO @TAB
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,3 UNION ALL
SELECT 1,4

With reference to the question raised @ the top we have to write a query to find the records containing 1 in either of the columns from @TAB or featuring in both of them.. 

Interview Question - What is the difference between Table Variable and Temp Table ?

Few days back, one of my colleague asked me about the difference between Table variable and Temp table. Although I did manage to tell him few difference that got him going but that answer did not satisfy me. So to settle the nerve I ended up reading about them and got to know few more differences between table variable and temp table. 

Then it occurred to me that it's good candidate for the blog entry as this question fielded quite often during screening process. 

Below given screenshot shows the difference between the Table variable and Temp table

NULL with NOT IN () caluse

Most of you must be aware of the fact that NULL value causes more problem than anything else database world. Well here is an example which may or already have ruined few minutes @m some point in your career.

NULL means nothing then Why on the earth should we care about it ? well it is one of the most important thing that DB developer should care or you'd be doomed for unexpected results.

Let's see one example of NULL causing few of us headache.. 

Interview Question - What is the difference between TRUNCATE and DELETE ?

I am not sure why this question is fielded in the interviews but over the last couple of years I have seen/heard it enough times to be made as a blog entry..

The question is simple - What is the difference between TRUNCATE and DELETE ? 

Many of you must be aware of the most of the differences that are listed below but it might turn into worth reading if you find something new 

Interview Question - Can we have 2 Identity columns in a single table ? If No then can we simulate such situation ?

I came across this question few days back and ever since wanted to blog about it. Although it was asked to me in one of internal project training session, I though it might be worth asking in an interview process as well.

So the question was - Can we have 2 Identity columns in the same table ?

Let us try ans see if we can..

DECLARE @TEST TABLE
(
ID SMALLINT IDENTITY(1,1),
NEW_ID SMALLINT IDENTITY(1,1)
)

MS-CRM - Query to list the Roles which are in use

I'm going to keep this blog post short and simple.. 

In case you are working on the MS-CRM and came across a situation wherein you want to list the roles that are currently in use in the particular organization then following query would help you out.

Objects used in the query are..

• SystemUserRoles - Holds the association between users of the CRM organization and the roles from it
• RoleBase - Lists the roles across CRM organization
• SystemUserbase - list of users present in the CRM system. Isdisabled column from the table tells us whether user is in enabled or disabled state

Please run this query against your CRM database. I am assuming here that you have READ privileges on the CRM database.

Query to find the object across databases

When a new member joins our team first thing is expected out of him or her is to understand the database structure in order to deal with the issues clearly. It takes time to understand the database structure completely until then they rely on us to answer their quest to locate the object. And most of the times they tend to ask you about the location of the object when you yourself are wandering in the complex code that someone else has written.

This is where a small script to list the objects from the database becomes handy.

Following script has helped me on numerous occasions to locate the objects see if it helps you as well...

Interview Question - How to manually insert a value into Identity column ?

In the last blog entry we saw that how Identity column values cannot be updated. And at the end I had asked you a question what if we still want to do it .. can we ?

The answer is - No we can't. But we can simulate the behavior though. What does that mean? I mean we can do certain operation which would suggest as if we have updated the Identity value..

• Copy the record details for identity value that you want to update ( I am assuming here that you don't have duplicates in the identity column. In case you do then you know the record for which you want to preserve the details. )
• Delete the record for identity value that you want to update
• Insert new record into table with the identity value that you wanted to set for the deleted record

Interview Question - What is Identity column and Can we update it ?

This question was fielded in the recent job interview that my colleague was part of. He is moving on and I am glad that he is .. not that I hate him but his skills are underutilized here...

Back to the question  .. What is the Identity column in SQL server and Can we update the Identity column value ?

We all know what Identity column is ... in case you don't then 

According to Wikipedia - 

In short -  Identity column is an auto increment column. 

Now back to practical part of the question - Can we update the Identity column value ?

MS-CRM - Query to list the Access Rights of the Role's on all the Entities

Recently, one of the Business Analyst associated with the CRM project wanted to know the access rights for all the roles across all the entities using T-SQL? The MS-CRM (4.0) database for which end user intended to fetch this information did not have any organization created on the top of it. Thus end users were not able to know what roles has what permission and on what entity...

I thought it would be good exercise to write a query to fetch this information. So I started writing one and at the end had following query ready with me.. It fetches required information for the roles which are associated with the Active users only. This means the roles which are not in use (or part of Inactive user) would not feature in the output of the query

Database Objects used in the query are listed below

• PrivilegeObjectTypeCodes - Contains the association between PrivilegeId and ObjectTypeCode of the entity
• PrivilegeBase - Contains the information about privileges such as Name, GUID etc..
• RolePrivileges -  Association between Roles and Privileges is defined here 
• RoleBase - Contains the information about Roles such as Name, GUID etc..
• EntityView - Has the information about Entities
• SystemUserRoles -  Association between Users and Roles
• SystemUser - Information about CRM users

MS-CRM - Query to List Roles assigned to Active users

This requirement came when I was working on the MS-CRM migration project and my client wanted to know ROLE's that ACTIVE users are part of....

In MS-CRM following objects hold this information

• SystemUserBase - Contains User related information. It also has a Isdisabled column which gives the information about which one's are active and which one's are not

• RoleBase - List of roles that are defined in the MS-CRM Organization

• SystemUserRole - defines the relationship between user and the role.

Following query will give fetch us the roles and the count of active users belonging to that role.

Issues while Importing data from Excel to SQL using SSIS - 2 - Alphanumeric data in a column

This is something I had faced long long time ago... 

This is what happened,I was given one excel sheet to import into database table. I created one package excel source and OLEDB destination..  Yes, I knowwwwww you geniuses, I could have done that using Import/Export wizard which also creates package. But I wanted to create package by myself … so.. created a package and ran it. Data got imported so without even querying the table I conveyed that data import is done and can be verified… To my surprise, I got an immediate reply with my senior in CC stating imported data is incorrect. In one of the column the text data was replaced by NULL during import … 

Now, in order to test what went wrong during import process I had to run it all over again and geniuses you know what I have package ready with me…   When verified I found out that that particular column had mixed type of values (numeric and character) so ideally excel should have treated this column of a TEXT data type. However, interestingly it did not and it treated it as a numeric column … Question is why did this happen? If we find out how does EXCEL decides on the data type of the column? We will have the solution. 

SSIS - Way to export all the packages from the Integration services

On several occasions, my friends or colleagues have asked me that why there is not a way to extract multiple packages @ same time from integration services. Well seriously, Why ? Microsoft whyyyyyy ?

However, as we all know for every problem there exist a solution (IMHO this is true for at least Microsoft technologies.. if solution does not work the RESTART will :) ) and mind you, that solution would be just around the corner, looking at you.. teasing you...Waiting for you to notice it (Just like girls, when they've got something new )… And when you notice it... all you (in girls case they) remember is the time you wasted to notice it. :)

So how do we export all the SSIS packages from the MSDB (or any other) folder to some location on the hard disk? Well use SSIS package to get this done… I am not kidding you? as Bizzar as it may sound but SSIS has one transformation which can be used to export the multiple packages in one go. .. Let me show you…

Interview Question - What will be the Output for ....

This question was put forward during one of the project interview that my friend had appeared for

The question was related to DATEADD() function

What will be the Output for 

SELECT DAY(0) AS DAY,YEAR(0) AS YEAR,MONTH(0) AS MONTH ? Will it throw an error ? If Yes, then what would be error ? If No, then what would be the output and why ?

Interview Question - Write a Query to list the Prime numbers between 1 to 100

This one is something that was just tossed-up at me by my team lead. Honestly, it took me few seconds to recall what Prime Number is. And when I knew what I need to find, I started thinking about the logic to do it..

Prime Number - A prime number is a whole number greater than 1, whose only two whole-number factors are 1 and itself. In simple words, any Positive Number number which is divisible by Itself or 1. 

I could think of following ways to achieve it

Interview Question - How to delete duplicate rows from the table using T-SQL ?

In my opinion, every SQL developer is asked about the way to remove one of the duplicate records from a table data, at least once in his/her career. And there are multiple way to achieve this but the easiest of lot is using CTE (Common table expression)
Let's create a table and populate it with duplicate data first

DECLARE @TAB TABLE(Id TINYINT,Name CHAR(1)
)
INSERT INTO @TABSELECT 1,'C' UNION ALLSELECT 2,'J' UNION ALLSELECT 3,'D' UNION ALLSELECT 2,'J' UNION ALLSELECT 3,'D' 

Interview Question - Can we have 2 tables with same name in a SQL database ?

This question was put forward in the recent interviews that I was part of. The interviewee had 3+ years of experience in SQL.

Interviewer - What is collation in SQL server ?

Job Aspirant - It defines what all characters are supported  by SQL and in what order they will be sorted. 

Interviewer - Usually, what is the default Collation for SQL server ?

Job Aspirant  - SQL_Latin1_General_CP1_CI_AS

Interviewer - What does CI stands for ?

Job Aspirant - Case Insensitive

Interviewer - Can we have different collation for a SQL server and Database created on it?

Job Aspirant - Yes.

Interviewer - Suppose I have a table X in the database Y then is there any difference between

Select * from x  & Select * from X ?

Job Aspirant  - No, there isn't. Both will point to same table.

Till this point it was all theory so aspirant was up for it and then came the conceptual question which was  ...

Can we have 2 tables with same name in a SQL database ?

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