Optimal way to remove time from Sql Date Time Column

As there is a separate data type [ date ] in sql server 2008 and on wards but still many  databases are relying on sql server 2000 and sql server 2005 where data type is only datetime and in most of the cases we don’t require time so to remove time part from date developers use different techniques.
Let’s analyze two commonly use techniques to remove time part from date field and know which is better in terms of time.

Select Count(1) From tblNames

508860 records.

For test i have a table of tblNames with almost 508860 dummy records i believe every one should have one dummy table to test each query themselves.

Let’s analyze the two majorly used queries.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select Convert(DateTime, Convert(Varchar, dtCurrentDate, 103), 103)
From tblNames

Set Statistics Time Off;

Firstly Dbcc Freeproccache and Dbcc Dropcleanbuffers is use to clean the cache as this is a very important step before analyzing the result because sql server cache results so if we don’t clean the cache then our result will not be correct.
Convert to datetime is first analyzed as this is a very common technique used but is this the best ???
After running the query 10 times and then taking the average of this query it resulted in average

Cpu time = 531 ms

Now analyze another technique to remove the time part from date time column.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames

Set Statistics Time Off;

This query looks unusual on a first look but let’s understand what it does, in the query initially the number of  days difference is calculated between the minimum sql date and the given date.
Note ( 0 means minimum sql date)

Then the resultant number of days are added by 0 which result in removal of time part from date.
Note ( 0 means number of days int)
To understand this query important this is to understand the functions DateAdd and DateDiff.

DateDiff (Interval, StartDate , EndDate) and it returns Integer.
where as
DateAdd(Interval, Increment Value Integer, GivenDate)
and it returns datetime.

Now back to the execution cpu time of this query. though it looks complex to see this conversion but the resultant time is mighty fast and almost half the previous convert to varchar query.
The average execution time after 5 run is

— SQL Server Execution Time:
— CPU time = 266 ms

So always use
 Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames 
When ever you need to remove time from date time field.

Data Reader vs Data Set

There are some confusing explanations about data reader and data set. Some people think they are just two exactly same ways of reading data from database but that’s not 100% true.

Lets solve those confusions by understanding the basic needs of both the structures.

Data reader will fetch data from database very fast as compared to data set as it uses forward only structure with one row at a time. It release the record as query executes and does not wait for the entire query to run.

The advantage for data reader is that it stores data in network buffer and gives it when ever requested but for that it always require an active database connection.

Data set is a disconnected mode of data retrieval, It uses data adapter to get data from database and data adapter gets complete data at a time. It waits for the query to execute completely mean while it loads all the data in memory and when query is successful gets data and fill it in data set.
The data set stores all the result data at application level in data tables similar to database table.
The advantage is that there is no need for an active connection once data is filled in data set. All the other operations can be done on the data set.
Data set can be serialized so it can be passed to different tiers easily.

Real Time Example

To be very precise and simple
Data reader is an electricity connection to your home when ever you need it you will switch on  and you will start getting electricity but for that you will always require an active electricity connection to your home which will provide you fast access.
On the other hand data set is like a battery which needs to charge once (one time database connection) and then you can use it when ever required even when you don’t have active electricity connection at that time. But carrying battery have a cost and you can not carry battery for every equipment at your home so this is a trade off.

Another One For Interview … This Time Recursion

Recursion has been famous technique of eliminating loop and breaking the execution of code into regular call of methods.
Some times your interview is going good and there comes recursion which just shocks you….
It is simple but it’s simplicity makes you in trouble especially during strict time bound interview.
Basically recursion is a technique of calling a method from it’s own method.
It is similar to divide and break the complex logic into simple executable code.
Key to remember before starting to code any recursion solution is this equation

F(n)=F(x) + – F(n)

.
Where F(n) calls it self multiple times using the old value.

Let say we have to multiply two positive unknown values without using the multiplication sign.
So the function would
F(0) =b+F(1) —– F(1)=b+F(0) …. and so on ….

So lets write the code.

using System;

public class Test
{
public static void Main()
{
int d= multiply (10,2);
Console.WriteLine(d);
}
public static int multiply(int a,int b)
{
if(b==0)
{
return 0;
}
else
{
return a+multiply(a,b-1);
}
}
}

 

Here you can see the function is calling itself and reusing the old returned value until and unless value becomes 0. We can do factorial and division using the same technique with little bit enhancement.

 

1 million prime number generation in less then half of a second …

This is an optimized code of prime number generation.
This question was asked in an entry test of a software house that how can you generate 1 million prime numbers in less then a second.
I wrote the code that generates the prime number and add to a list in 0.39 seconds to be specific 390.8387 milliseconds.

Important key point here to understand is that except 2 no even number is prime so that is why i have started it from 3 and increment 2 so by this why we will not be checking any even number.
public static void prime()
{
List l = new List(1000000);

l.add(1);
l.add(2);
for (uint i = 3; i < 1000000; i += 2)
{
bool isprime = true;
for (uint j = 3; j*j <= i; j += 2)
{
if (i % j == 0)
{
isprime = false;
break;
}
}
if (isprime == true)
{
l.Add(i);
//Console.WriteLine(i);
}
}
}

And to execute code and also calculate the running time you will need the following code.

Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
prime();//Prime Function
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Console.WriteLine("RunTime " + elapsedTime);
Console.WriteLine("RunTime " + stopWatch.Elapsed.TotalMilliseconds);

Interview- Swamping two variables

This is a simple question that how to swap two values let say a and without using third variable. But this question gets confusing when you are asked to swap negative and positive value together.
Let say a=2
b=-3
Now swap a value into b and b value into a.

So the code will be:-

{
int a;
int b;
a=2;
b=-3;
a=b-a;
b=-(a)+b;
a=a+b;
Console.WriteLine(“a=”+a);

Console.WriteLine(“b=”+b);
}

a=-3
b=2

The good part about this code is that you can enter any value in a or b whether negative or positive it will swap it with ease.

Interview Question

Q1). char,varchar,nvarchar difference?

Char means every entry  in a row will take 8000 character space.
whether you store 5 letter string or 1000 letters string.

Varchar means you can specify during the schema design that how much space you want the column should  use for each entry.
e.g if you specify username varchar(10) then for each entry in a row the 10 character space will be use.

nvarchar means you can not only specify the space in the column but also additional asci characters are also included which can be entered in that column.

Q2)What are Triggers?
Trigger are special type of store procedures which can be set with the table so that for each update,insert or delete in the table. A query is run which saves the detail of operation done on table along with the data.
For example if we set update trigger on ITEM table then for each update of ITEM table a trigger will be fired which will save updated item record and time  in another table to maintain log of updates.
Trigger are of 3 type Onupdate, OnInsert, OnDelete.

Q3). Primary Key Vs Unique Key?

Primary key can not have null value unique key can have one null value.
Primary key automatically generates non clustered index where as unique key does not generate index automatically .

 

Interview-Example of Model View Controller In Real World

This is another simple question which was asked to me in an interview.
As all interviewer requires real world example of every concept so the MVC (Model View Controller) application can be seen in a Calculator.

The buttons is the Controller.-> when you press the add button request is send to model.
The back end hidden knowledge is Model-> When you press the add button some business logic runs let say two number are added in the calculator then the answer is send to view.

The Display is View->The View is responsible to display the result.

mvcdiagram