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