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

Query - to script the steps to move user database files around

Many times we don't pay attention while creating a user database and end up creating database files where they are not supposed to be.. in such scenarios often we find ourselves moving file across drives so as to arrange them to match our settings (or liking's ?) 

To move files of the online user database, you have to perform certain steps in the specific order and it is documented quite nicely in MSDN

This is what we do

• Take DB offline
• Move files
• Modify old file location to the new one
• bring database online

The following script generates the statements for the  steps mentioned above.. Please note that on my server I wanted to move files for the databases with a name starting from test.. you will have to change the WHERE clause so that the scripts are generated for the databases of your choice

Query - to find specific column across tables and across database

Here is the situation my friend came up with -

list all the databases and tables with containing one specific column

he required this info because his project was under migration and they were looking for this kind of information .. The query shared here can be modified to find Most commonly used column name across databases

Query does 2 things

• Loops over databases having test at the start of their name
• Finds out the column across tables using Information_schema.Columns

Query - to find objects containing specific word

Sometime back we did one exercise where our objective was to find out the objects which don't work any more .. 

for example suppose we had created a view v_tab with a simple select statement fetching abc column from XYZ table .. later on say after 3-4 months that abc column got renamed to lmn but the view v_tab referring to table column as abc never got updated so that view now became obsolete .. You see what happened there and mind you this happens quite often when attention is not paid while deploying new changes to the database objects.. 

we used following approach to address this

Query - to peek into Maintenance plan History

Today, it's about maintenance plan and it's history

Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm.. 

Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.

Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it 

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 

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