“Companies don’t hire you, you get hired !!!” –Excellent words by Vinod Kumar M


You can read the full post at blog.sqlauthority.com in the link http://blog.sqlauthority.com/2012/01/01/sql-server-interview-questions-and-answers-perspectives-of-an-author/

“Companies don’t hire you, you get hired !!!”, saying this the author- Vinod Kumar M, Co-author of SQL Server Q & A book is indicating that one can’t rely on luck to get a job, one must be well prepared for it.One more nice quote is- “The most qualified do not always get a job. But that doesn’t mean only the unqualified get one”. It took me more than half-an-hour to read the full post on the above link, with all the links provided there and I feel this is the perfect start to the new year. I just got recharged after reading this post.

There are so many things I learned from the post like –
1. Real story behind “Over-Qualified”
2. Value of smart work
3. Impressing interviewer with right communication
4. Social Media aspect etc.

I believe the areas pointed by the author will really help me if I will need to switch my job later and I will definitely work hard on them.
As a summary I can say that, the post is a must read for both freshers and experienced people.

I am very much thankful to Mr. Vinod Kumar M for such an inspirational post & Mr. Pinal Dave for placing it on his blog. I hope it will help numerous people for their job-interviews.

Advertisements

Where to use NULLIF() in SQL Server ?


Before deciding where to use the function NULLIF() in SQL Server , Let’s first find out what is purpose of NULLIF().
NULLIF() is used to return NULL on a matching expression.
Let’s take an example-
Example:

 
SELECT NULLIF(@VAR,0)

Here if the value of @VAR is 0 then it will return NULL.
Now, Think where can we use this?
For this we will take another example-
Example:

DECLARE @VAR INT
SET @VAR=0
SELECT 100/@VAR

Result:
Divide by zero error encountered.

Some time we may need to show the result as NULL if the variable @VAR has a value of 0.
Now, we can re-write the SELECT statement as –
Example:

SELECT 100/NULLIF(@VAR,0)

Result :
NULL

Which is better – “” or string.Empty ?


Answer is string.Empty. Now you may ask, why?
OK. The answer to your WHY is – string is immutable i.e when we want to change a value, stored in a string variable, it creates a new copy in the memory and then the old memory space hold by that string variable is released by garbage collector.
Above process is followed when we do things like-
string str= “”;
But, in case of string str=string.Empty;
it doesn’t create a new copy.
So, avoid string assignment.

C Sharp: Under-utilized Padleft() method


What I am going to explain is not for a specific situation, we can use it in so many cases. I am talking about PadLeft() method of string class. To explain it, I am just showing you an example, which I found somebody doing while he is supposed to code for three dropdowns like Day,Month & Year. Following is the snippet of his code-

private void bindDay()
    {        
        ArrayList day = new ArrayList();
        for (var i = 1; i <= 31; i++)
        {
            if (i > 0 && i < 10)
            {
                var x = "0" + i;
                day.Add(x);
            }
            else
                day.Add(i);
        }
        ddlDays.DataSource = day;        
        ddlDays.DataBind();
}

Although this block of snippet may need improvements on some other line(s) of code but I am concentrating on the part inside the ‘for loop’. Yes, the if .. else .. block. Here, 5-7 lines of the code is intended to add days of date and with 2-digit format like 01,02,03..etc. I admit that there are so many ways to accomplish it, but I am just talking about the logic. In this code, it has a loop variable which checks the value to find if it is of single digit or two digits. Then if it is of 2-digits then no logic to go before adding it to the dropdownlist and if it is of 1-digit then it is to be padded with a ‘0’..This will work definitely fine for Day dropdown.

Now we can ask what happen when some body wants to form a dropdown with fixed no of 10 digits and a dumb answer will be -“Use 9-10 if..else conditions to do this”.

We can replace the whole if .. else .. by just using a simple method of System.String class – PadLeft(). Now after changing only the if else block with PadLeft(), the code will be like-

private void bindDay()
    {        
        ArrayList day = new ArrayList();
        for (var i = 1; i <= 31; i++)
        {
               day.Add(i.ToString().PadLeft(2,'0'));
        }
        ddlDays.DataSource = day;        
        ddlDays.DataBind();
} 

Here , PadLeft() is taking 2 parameters- width of the resulted string and the character to be padded to the left when string is of length less than 2.
This can also be used to scenarios where you need to generate a code (like Employee Code,Book Code, etc) of a fixed length.

Select contents of a TextBox on focus in ASP.Net (Code behind)


We generally need to select contents of a TextBox while using validation for it. For this, if we are using javascript validation then we may code like-

document.getElementById("<%=TextBox1.ClientID%>").select();

But, we need some other way, when we want this on some other events in code behind. For instance, let consider we have a TextBox in a page, whose purpose is to display the next receipt no. by adding 1 to the previous receipt no.,but it can be changed if user wants to do so and that’s why when cursor comes to this TextBox through any event , contents of this TextBox will be highlighted so that user can have an idea that it can be changed or, need to be changed. To achieve this we will follow below steps.

Step-1: We have to define a javascript function in the .aspx page inside script tag-

function selectText()
{
      document.getElementById("<%=TextBox1.ClientID %>").select();
}

Step-2: On page load event, we will set the attribute of the TextBox to-

TextBox1.Attributes.Add("onfocus","selectText();");

Import data from Excel sheet to SQL Server table


If you have well formatted data in an excel sheet then you definitely can import those data to your SQL Server table. By saying well formatted data, I mean- data should have arranged in columnar structure like a SQL Server table, which has data arranged per columns. Each of the columns is should have data of similar data type. Again, you have to take certain pre-cautions while creating table for it like-
1. While creating a field in the table find out the maximum value or, length of the data present in the corresponding column in the Excel sheet.
2.Trim the blank spaces from both the ends of the data(Although in SQL Server if a field is declared as NVARCHAR,then it automatically truncates the leading blank spaces). For this you can use LTRIM() and RTRIM() functions of SQL Server on those fields, which are supposed to have blank spaces.
3.Define the field to allow NULL, if you are expecting the corresponding column to have NULL values.
4.If you don’t want any blank spaces in your data then you can use REPLACE(columnName,’ ‘,”)

Now once you created the table, then you go for import data and your SQL script will be like-

 
INSERT INTO TableName
SELECT F1,F2,,F3
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;DATABASE=D:\Data.xls', 'Select * from [sheet1$]') 

You can use WHERE, ORDER BY, GROUP BY etc with the above statement as per your requirement.

Backup all databases at a time in SQL Server 2005


This particular script is very useful when you have many databases in your SQL Server 2005 and you need a quick backup of all the databases. Here, the situation may be any thing like you want to format your hard drive due to any problem or you want to move all the databases to another system etc. Just run the following script and all your problem will be solved. One thing to notice here is- you must have a folder or, folder path, in which you want all the backups to be done.

USE master
GO
BEGIN
   DECLARE @DatabaseName NVARCHAR(50)
   DECLARE @BackupPath VARCHAR(400)
   DECLARE @BackUpName VARCHAR(400)
   SET @BackupPath='E:\DB\' ---folder name on your hard drive
   DECLARE cuBackUpAll CURSOR FOR
   SELECT name FROM master.dbo.sysdatabases
   WHERE name NOT IN ('master','model','msdb','tempdb')
   OPEN cuBackUpAll
   FETCH NEXT FROM cuBackUpAll INTO @DatabaseName
   
   WHILE @@FETCH_STATUS=0
   BEGIN
      SET @BackUpName= @BackupPath+@DatabaseName+'.bak'
      BACKUP DATABASE @DatabaseName TO DISK =@BackUpName
      FETCH NEXT FROM cuBackUpAll INTO @DatabaseName
   END
   CLOSE cuBackUpAll
   DEALLOCATE cuBackUpAll
END