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 ?