DateTime Manipulation: Date Part or Time Part

There are times when using SQL server 2000 or 2005 , you really want to use either the date-part or the time-part of a datetime value. But, you would still want to use it as a datetime value for comparison. Usually, as a solution, you would try and build a new datetime by converting the current datetime into a string or extracting the day, month, year etc. from the datetime using the respective SQL functions.

The SQL server’s default language settings may sometimes poses problems when building the new datetime using various parts of the datetime.

However, I found the following solution from here. It is a clean solution to convert the datetime to midnight or keep the time-part but change the date-part to 1900-01-01.

Here is the code:
SELECT GETDATE() AS [CURRENT_DATE],
       CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [DATE_PART],
       CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [TIME_PART]

Mumbai Assault Over?

Finally, after 60 hours of horror the assault on Mumbai is finally over. It is said that 3 more terrorists have been killed in the Taj and the NSG is running clear ups in the Taj.

It has also been said that they have a figure for the number of people dead in the Taj and seems to be much higher than expected. This mean the overall damage to human lives after the past 60 hours or so of the horror can be exceptionally high.

It is still not clear who these terrorists are. India has claimed that they have strong evidence about the involvement of Pakistan.

The Pakistan government now as earlier have made a U-turn regarding sending the ISI chief to India. I think the Indian government should what they can without the ISI support. I mean, why do we need the ISI? Aren’t we strong enough to make our own decisions?

I am glad that this horrific terror hours has finally come to an end. The aftermath of the attack is not likely to be that good also as the death toll keeps rising.

Mumbai assault still continues

I am watching NDTV very anxiously as I am typing this post. The firing and explosions continues even after 54 hours of gun battle. I am sure that the NSG commandos are doing their best to put an end to this last hurdle at the Taj Mahal Palace hotel. Even the highly trained troops of the Indian security service are finding it difficult to capture the one or two (according to NDTV) terrorists.

This activity like being put by the media and the security officials shows that the terrorists are highly trained and motivated to do anything to make a mark on India. When is it going to end? Well no one knows anything. Everyone can just wait and watch and the events unfold gradually in the course of the third night of battle.

It is sad that the NSG could not save any of the hostages in Nariman House. I feel sorry for the two year old Israely child who was orphaned on his second birthday. I feel sorry many other who were also not lucky.

So, is it time for the people to finally start thinking rationally about their role as citizens of this wonderful and strong nation? Does the common man really think that Enough is Enough? Will they continue to boast about the spirit of India, spirit of Mumbai and what not?

Currently the city and the nation is in shock, anger and distress not only about the scale of the attack but also about the failure of the government and intelligence to avoid this attack. Cleaning up mess is probably the only thing that the goverment and politicians can do.

I heard on the news that there was some politician who was talking about a bandh in Mumbai on the 1st of December. They don’t learn their lessons after all.

Last battle is still on at the Taj. Hope it will end soon.

India’s 9/11 on 26/11

It has been over 50 hours since the mumbai attacks began to unleash on the Taj Mahal Hotel, Oberoi Trident, Nariman House, CST Train Station and other places. It took all innocent people by surprise. Indians, foreign nationals, everyone. Even more surprising fact is that it came as a surprise to the Indian Government. There is a feeling of relief that this distressing act of terror is about to come to an end. The relief also comes with a feeling of sadness and sorrowfulness to know about the titanic nature of the loss of lives and injuries of the innocent people who had nothing to do with any of this.

I watched the news on Thursday evening on the BBCand ignored it for the evening thinking that it was one of many other attacks on the city of Mumbai in the past. But I really starting getting the grip of the intensity of this particular attack only the next morning. Since then I have tried to get as much information from the media over the internet and television about the latest happenings in the city. This is certainly the 9/11of India. 26/11 attacks is the biggest attack in the history of Mumbai in terms of the impact to the common man.

Once again, the politicians who lead our great “democratic” country have proved their incompetence in their position. It is again proved to me that these politicians are there to make a difference to them and not to the country. I must say I am not much into politics. But this event as it unleashes makes any common man of India make a judgement about the competence of the leaders of the country.

The un-inspiring robotic speech by our Prime Minister will not make any difference to the common man. The visits of the politicians to the hospitals and event sites are treated as a hindrance and nuisance.

There has been a serious failure of security. This should have never happened at all. There is no doubt that the Mumbai Police, the Marcos, the NSG has done a heroic job to save the innocent and obliterate the terrorists and they are doing the same as I am typing here. Not to forget the staff and other common people who played a heroic role to save lives. I salute the heroes. But the question again is how did these people sneak into the country through the Navy and coast guard vigil? Why it took those over 8 hours to reach the site. Why did the government take so much time to react to the situation.

I am sure I will have more reaction to this whole episode. But, the one thing that I would like to know is where Mr. Raj Thackeray is? Where are his MNS supporter who were ready to give life and take lives to get non amchisout of the state? Are they all sitting in a bunker and deciding which other citizens of the country that they can target next time? This is a time where I would have expected people like Mr. Raj Thakarey and his so called supporters to have come out in support of the needy. Where are you and your supporters Mr. Raj?

Over 150 people dead and over 320 people injured. This is not acceptable to any Indian. I am sure there is a change coming and hope that people will not take democracy for granted as they have been doing, some selected group going to the extent of giving it a new meaning, Gundaism.

Mumbai and India is in a terrible situation. Hope it all ends soon.

Dwarf Dance anyone?

Yesterday, we had a small get together to celebrate diwali, the festival of lights. There was a small cultural programme. And guess what, I had also taken part. Together with my wife and a mate and his wife, we had put put together a dwarf dance. And I could not resist putting it on YouTube. Here it is. It was a grand success yesterday and hope you like it.

New look blog

I got a little bored this evening and decided to change the theme for this blog. I have tried to have a simple theme this time. I like this theme it is simple and it is flexible width.

By they way a very happy Diwali to all readers.

Me is still there!!!!

It has been a while since I logged into my blog let alone blogging. It has been exactly a year since my last post. I don’t quite know the reason. May be I was bored, may be I did not have anything to blog about, I don’t know. But I am still here and I plan to make some effort to keep the blog alive.

Karaoke evening

Yesterday, we had a karaoke and dance evening. It was organised by the GSB community in the UK. 

I had a go at one of the many bollywood songs. Even Gautham had a go. It was all wonderful. We managed to sing in tune and without missing many beats.

It was an occasion for people from our community to come and meet and have fun.

I have posted some more pictures here.

World cup cricket: India out of the world cup

Good riddance to another rubbish from the Indian cricketers until the next world cup.

India are finally out of the world cup after loosing, again pathetically, against the mighty Lankans. Even though Indian bowlers tried to contain them by getting wickets constantly, the batting order were not that lucky. They were set a target of 255 by the Lankans.

Tendulkar, with all his experience, again out on a duck in possibly his last world world cup. Dhoni, what more can be said about him. He was out of the first ball he faced in the match. It was indeed a great ball from Muralitharan. Yuvraj Singh, run out. Sehwag tried his best, but not enough I reckon. Dravid, the captain, tried to glue himself at the crease, was unlucky. Wickets kept falling at the other end. I reckon he lost his calm at one point and started hitting some good strokes taking him to 60 before he was caught my Muralitharan off Jayasurya. After his dismissal, there was no going back. Our tail-enders cannot handle the Lankans by any means. I must commend Harbhajan for his unbeaten 17. Indians were finally bowled out for 185 and were sent packing back home by the mighty Lankans.

The Indians are now on their way back to India. Dravid, needs to answer many questions back home. His captaincy seems to have gone for a toss. He dosen’t have the wao factor that Ganguly had as a captain. Ganguly may not have scored heavily as a captain, but he had the capacity to turn the game around.

Anyways, good riddence to the rubbish cricket from the Indians, at least for this world cup. Who knows whats more to come. India can just hope a miracle to happen. I saw their faces after they lost the match. A sense of shock. They can now just hope against all odds that Bangladesh will loose to Bermuda.

Partitioned views: CHECK constraints with NOT FOR REPLICATION clause

I am posting this entry to remind myself about what happens when you have the NOT FOR REPLICATION clause with a check constraint on a column in a table.

I was going through how data partitioning can be achieved in SQL server 2000/2005. As mentioned in the one the various articles on the internet, the simple way is to create identical tables with the same primary key columns and have a view on top that includes each of the tables with a UNION clause. However that is not the scope of this post.

The scope of this post is the impact on queries on partitioned views by having a check constraint in the underlying partitioned tables and that the check constraints are created with a NOT FOR REPLICATION clause. I have a standalone database without any replication involved. I would do the following for example:

Create two identical tables:

create table [dbo].[Table_1]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),  
 [AnyOtherColumn] int  NOT NULL ,  
) ON [Primary]
GO
create table [dbo].[Table_2]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),  
 [AnyOtherColumn] int  NOT NULL ,
) ON [Primary]
GO

Note the NOT FOR REPLICATION clause in the check constraints on the TableID column.

Creating a view on top of that

create view TableView
as
 select * from Table_1
 union all
 select * from Table_2
GO

Now use the following query to see the query execution plan:

select * from TableView where TableID = 10

In SQL server 2000 the query execution would look like this:

In SQL server 2005 the execution plan would look like this:

In both cases you would notice that there is an index scan on both the tables that are part of the view. What would happen if there was no primary key? It would have done a table scan on all the tables. In this example there are only two tables. What would be the performance hit if there are many more tables with huge amount of data in it? The last thing you would want is a table scan on all tables to retrieve just one row.

Now drop the tables and the view and create them again, now without the NOT FOR REPLICATION clause in the check constraint.

create table [dbo].[Table_1]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK ([TableID] BETWEEN 1 AND 999),  
 [AnyOtherColumn] int  NOT NULL ,  
) ON [Primary]
GO

create table [dbo].[Table_2]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK ([TableID] BETWEEN 1000 AND 1999),  
 [AnyOtherColumn] int  NOT NULL ,
) ON [Primary]
GO

create view TableView
as
 select * from Table_1
 union all
 select * from Table_2
GO

Now see the execution plan for the same query

select * from TableView where TableID = 10

This is what you see in SQL server 2000:

This is the execution plan on SQL server 2005

The difference this time is that it has done an index seek only on the partition where the data would be present. Since the check constraint on Table_1 allows values for TableID column only in the range of 1 and 999, it picks up data only from that partition table and does not bother looking for it in the other tables in the view.

So, why does the NOT FOR REPLICATION clause on the check constraint reduce the performance of the query so drastically?

Do not use the NOT FOR REPLICATION clause unless you need it.

Now, to add to the fun create the tables again, now with the primary key as clustered and without NOT FOR REPLICATION clause. Run the query execution plan for the same query again.

On SQL server 2000

On SQL server 2005

On both SQL server versions there is a clustered index scan just on the partition that is in the scope of the query.

If any one of you figures out why the NOT FOR REPLICATION on the check constraint makes such a huge query degradation, I would like to know why.

I have opened a thread on the MSDN forum for the same problem. You can find it here.

World cup cricket: Comfortable win for India

India played against Bermuda today. The Indian batting line up, after closing a lost chapter against Bangladesh, scored a record breaking total of 413 for the loss of 5 wickets in 50 overs. Sehwag scored the much awaited century. Ganguly added 89, Yuvraj 83 and Tendulkar, an unbeaten 57. Dhoni added a smashing 29 of 25 balls with Dravid finishing the innings with a six. In all a comfortable and a well earned world cup record total. Even if it is against a weaker and inexperienced Bermuda, it is in the history books.

Next was Bermuda with the bat. India had to contain runs and bowl them out for a very low score in order to have a better chance of getting into the super-eights on run rate. Although Bermuda kept loosing wickets constantly, I still felt that the Indians could not contain the runs. They did bowl well, but it did not impress me for their class. Again, a very mediocre performance with the ball. They could not get Hemp out who stuck impressively on the crease scoring an unbeaten 76 runs. He was the only batsman who scored for Bermuda. Bermuda managed to score 156 and dragged until the 44th over.

The Indians did manage a comfortable victory but I feel they could have bowled Bermuda out at a much lower total. India stands second on run rate in its group with three teams (Sri Lanka, Inda and Bangladesh) tied at 2 points each.

World cup cricket: Another best of the worst from Indians

Today’s world cup battle against the Bangladeshis turned out to be one of the best of the worst for Indians. They have had a humiliating defeat against the so called “relatively inexperienced” Bangladesh team. I must say, I am not a hardcore cricket enthusiast, but I try and keep myself updated with the latest. I happened to see the match today. I started watching when Indians were playing at 90 for the loss of 4 wickets and Saurav Ganguly and Yuvraj Singh were at the crease. Looking at the Indian scorecard, they seemed to be on the loosing front right from the beginning. Sehwag, Tendulakar, Dravid, all back in the pavilion.

Although, Ganguly and Yuvraj, seemed to stabilize the situation for India at one point of time, all that was washed away when Yuvraj got out at 47, followed by Ganguly, Dhoni, Harbhajan and Agarkar in a span of just 2 runs. Ultimately, the Indians were bowled out at a total of 191 runs, thanks to some good and some lucky strokes by Zahir Khan and Munaf Patel at the end with both adding 15 runs each.

Then came the so called “relatively inexperienced” Bangladeshis with the bats. They, had nothing much to do I reckon, except for playing calmly and not loosing early wickets. The Indian bowling attack was not that great. Very mediocre for their experience in the game. Even, Bhajji didn’t seem do any magic with the “doosra“ nor did Tendulkar, compared to the spinning attack by the Bangladeshis. This allowed the Bangladeshis to reach their target calmly with the loss of 5 wickets in the 49th over.

In all the Bangladeshis had a historic win against the Indians today. What happened to the Indians in the field today? They seemed to be showing no contention even whilst fielding. The Indians have been totally outplayed by the Bangladeshis in all respect in the field today.

India still has hopes of making up to this loss.

But the first major upset in this tournment is that Pakistan is out of contention for the world cup 2007. They lost against Ireland today. It was another major upset, worse than India’s loss against Bangladesh. The Pakistanis were surprisingly bowled out for just 132 on board. Ireland, being supposedly made up of amateur cricketers, crawled to victory to reach a revised target of 128 in 47 overs. This match ended with a six by Trent Johnston of Azhar Mahmood. Again, a historic win for the Irish in world cup cricket. They are probably through to the Super-Eights, while the Pakistanis are on their way home.

Sadly, there won’t be the India -Pakistan clash in this world cup. Hope India fairs well. Come on India!!!!!!

Cumulative hotfix for SQL Server 2005 SP2 is now available

Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available.

 For more information about the bug fix list, follow the link.

Not long since SP2 was released.

Nostalgic memories of MIT Manipal

I just happened to think about the back old days when I was in college in MIT Manipal. I happen to find a few videos on YouTube of the college where I used to study back in those days. I saw the videos and realized so many things have changed in and around the campus in these years. The main block, and the other block which we used to call the new block (its not new anymore), the library block, the workshop blocks where we used to learn carpentry and metal fitting in our first year of university and last but not the least the off college hang outs, all these have changed considerably. All this creates and feeling of nostalgia within me. This also sometimes makes me feel sad about the fact that I did not have easy access to Internet technology then, to put up my memories on the Internet forever. Actually, Internet and having a computer at home were a very big and an immensly expensive affair in those times which actually are not long gone.

I had to put some of these up here so that I can get back to them when I need to.

This is another good video about life in Manipal:

It is a real challenge to keep in touch with all my mates from college. All have spread far across the globe. Even in this Internet age, I find it hard to keep in touch. But, I do try hard to keep in touch with as many people as I possibly can.

I don’t know how many of you are in touch will your mates from college?

Microsoft releases SQL Server 2005 Service Pack 2

It is here and ready for download. Microsoft has officially released SQL Server™ 2005 Service Pack 2. It can be downloaded from here.

Books Online available here. 

Cheers