TIP - To fix orphan uers

Orphan Users

When a database is moved from one server to another "Orphan users" is one of those little things that tend to create problem in smooth transition. 

There could be 2 reasons because of which Orphan users might get created

• Associated Login for the user itself isn't present on the server where database has been moved 
• Mismatch between SIDs of USER of the database and the LOGIN of the server

When the database user looses it's association (SID mismatch) with Login it becomes orphan and this uncoupling leads to LOGIN loosing its privileges on that database.

In this tip we will try to build a script to address second reason mentioned above

sp_change_users_login 'report' - lists the orphan users from the databases

master.sys.server_principals / master.dbo.syslogins - lists the logins present on the server. 

Interview Question - To populate a table without specifying values in the INSERT statement

This one should be quick and most of must be aware of it. 

Following question was asked to one of my friend in an interview for the post of Sr. Software Engineer

How would you populate following table with 5 records without specifying values in INSERT statement ?

The table structure was as follows

IF OBJECT_ID('tempdb..#default') IS NOT NULL
DROP TABLE #default

CREATE TABLE #default
(
id INT IDENTITY(1,1),
software VARCHAR(20) DEFAULT('microsoft'),
osType VARCHAR(10)
)

GO

Interview Question - What will be the output of .... ? LEN() function

This blog is about what happens when we do not pay attention to Microsoft documentation ... 

Recently, I had a simple question fielded to me which made me look STUPID.. Well being stupid is my right as a human being :) but making me look one is a different thing altogether ... I hope you guys are with me on this :) .... Here comes the bazoooooooooka 

What will be the output of following SQL statements ?

• SELECT LEN(' ') as 'single Tab' 
• SELECT LEN('    ') as 'multiple spaces' 
• SELECT LEN('    ') as 'tab followed by 3 spaces' 
• SELECT LEN(' ') as '3 spaces followed by tab' 
• SELECT LEN('   a') as '3 spaces followed by char' 
• SELECT LEN(' ') as 'space tab space' 
• SELECT LEN(' a ') as 'space char space' 

Simple isn't it ....

Well if you guess the output of each of these correct then BRAVO, if not then you'll know how does it feel when simple thing such as this stumps you..

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 ? 

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 ? 

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

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

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 - 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 ?

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 - 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

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.