DateTime2 in SQL Server 2008

Hi Friends,

SO, now In SQL Server 2008, we have Date, DateTime, DateTime2, Time, DateTimeOffset and smalldatetime. I want to talk about DateTime2 here. This new datatype has been primarily introduced to store larger range, larger fractional precision whihc is again user controllable.

So the date range is:

0001-01-01 through 9999-12-31
January 1,1 AD through December 31, 9999 AD

and the time range is:

00:00:00 through 23:59:59.9999999

orginally, datetime would have the following ranges...

January 1, 1753, through December 31, 9999
00:00:00 through 23:59:59.997

so does DateTime2 go in to nano second precision ???

Thanks
Amit

Database Mirroring in SQL Server 2005

Hi Friends,

I am suddenly getting a number of emails on Database Mirroring in SQL server. I had written this small article on http://aspalliance.com/1388

For a detailed whitepaper, please visit: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Thanks
Amit

Heroes Community Launch - Delhi NCR

Hi,

Hope you are doing good. eDominer Systems & Peopleware India are organizing the Microsoft Heroes Community Launch for Delhi NCR on 30th March at Lok Kala Manch auditorium on Lodhi Road, New Delhi.

We have eminent speakers in the form of Abhishek Kant, Bijoy Singhal & Jadeja from Microsoft India & myself from Peopleware India.

We will run power packed technical sessions on Windows Server 2008, Visual Studio 2008 & SQL Server 2008 and going to make it one of the best community launches ever for the next generation of products from Microsoft.

I write to you to cordially invite you to the event. Please forward this email to your colleagues and friends as well. The venue can accommodate only 200 heroes. The event is absolutely free and there are loads of goodies and technical resources that are going to be distributed to the attendees by us. So, the tech people should register fast as there is little time left.

The following URL has all the details and the registration link:-

http://www.edominer.com/CL/Emailer_Main_1.htm

I would also like to see you becoming a member of www.PoweredBySQLServer.com if you are not a member already.

And do register my blog at http://ABWrites.blogspot.com for latest SQL Server & Technology learning and blogging.

Thank you for your time.

Best Regards
Amit Bansal
MVP-SQL Server
eDominer Systems P Ltd | Peopleware India
www.edominer.com | www.peoplewareindia.com | www.amitbansal.net | http://ABWrites.blogspot.com

Community session for Delhi IT community: Resource Governor in SQL Server 2008

Hi All,

Yet another community session. But not on SQL Server 2005. This time it was
SQL Server 2008. Modest turn up. But I still enjoyed interacting with all
everyone turned up. It was indeed a closed group meeting. Veer, who is
another SQL MVP was also available and participated with me fully. It was
great. Some pics at www.peoplewareindia.com/community.htm

Cheers.
Amit
www.amitbansal.net

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.20.2/1270 - Release Date: 10/02/2008
12:21 PM

Microsoft proposes to buy Yahoo for USD 44.6 Billion

Microsoft proposes to buy Yahoo for USD 44.6 Billion in cash and stock...
approximately.

http://www.microsoft.com/presspass/press/2008/feb08/02-01CorpNewsPR.mspx

MCT Summit 08, Singapore:Experience and pictures

Pics at www.peoplewareindia.com/MCTSummit08.htm

Hi Friends,

Hope you all are doing well. The MCT Summit concluded in Singapore on 25th
Jan 2008 and I wanted to share with you my experience of the same (will keep
it short and precise :) ).

First, I would like to share with you an achievement of mine. I feel proud
to announce that I have become a member of the Microsoft Certified Trainer
Advisory Council. There are 7 council members representing different parts
of the world. I, now being one of them will represent the Asia Pacific
region. Bill Chapman, the World wide MCT program manager, announced this
during his keynote speech at the opening ceremony of the summit. It is truly
an honor to be at such a coveted position and I hope I live up to the
expectations of MCTs in the Asia Pacific region. Yes, I was indeed surprised
when Bill announced this. I thank Microsoft for the pleasant surprise and
that they feel I can handle this honorary & privileged position. You can see
the picture here:
http://www.peoplewareindia.com/images/MCTSUmmit/IMG_0283.JPG

Now, on to the summit...

After TechEd 2007, this was another summit where I met around 100 Microsoft
Certified Trainers from 15 countries including India, Singapore, Malaysia,
Philippines, Thailand, Indonesia, Vietnam, Korea, China, Taiwan, Hong Kong,
Japan, United States, Russia & Egypt.

WOW !!! :)

Yes, there were MCTs participating from 15 countries and it was an honour to
be a part of them. I conducted 3 sessions on SQL Server 2008. And what an
exuberating experience it was to address an excited crowd of MCTs. It was a
reminder that I am part of an elite community. It was invigorating overall
and my sessions were liked and appreciated by everyone. (Link to the
pictures is mentioned below)

Along with me were other much experienced speakers like Gordon from
Australia, Edwin from Singapore, Kristie from Canada, Scott from US and of
course, myself from India :)

Microsoft staff members included Ken Rosen, Bill Chapman, Sukhdev Singh,
Foong Chee Ngiam, Yugesh Jerath, Gina Haines & Lisa. (The names are
important, not the order :) and I hope I have not missed anyone)

The sessions started on 23rd morning with an opening speech from Sukhdev
(APAC Director) followed by refreshing sessions from Ken, Bill and Lisa on
certifications, benefits and opportunities for MCTs. MCTs showed great
interest in many upcoming business opportunities. Gordon kicked off
technical sessions with his presentation on Win2K8 in the evening followed
by Gina and Kristie speaking on Microsoft Dynamics.

The second day continued with Gordon doing more sessions on the upcoming
Win2K server. I followed Gordon with my sessions on SQL Server 2008. I did 2
of them including Advance Administration on SQL Server 2008. Gordon ended
the day with his session.

The third day saw Gordon, Edwin and me speaking on our area of expertise. We
did give some wonderful tips on presentation skills and consulting
opportunities as well.

I forgot to mention that the first day had group photography followed by
welcome reception.

And here are all the pics at www.peoplewareindia.com/MCTSummit08.htm

So friends, this year will see me participating in MVP Summit in Redmond,
TechEd in Orlando and SQL Pass conference in Denver, US.

I love sharing my experiences and adventures with you. Thank you for
reading. Keep in touch.

With warm regards
Amit Bansal
MVP-SQL Server
www.amitbansal.net
http://ABwrites.blogspot.com
www.eDominer.com
www.peoplewareindia.com

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.15/1248 - Release Date: 28/01/2008
9:32 PM

DMV query to get top ten queries that took the most CPU time

Hi,

One of the participants at the MCT summit asked me "how to find out the
queries that are taking most CPU time?"

Here is the answer.

SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost,
plan_handle, execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;

We can have many variations of the query :) {DMVs are hidden gems :)}

Amit

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.9/1238 - Release Date: 22/01/2008
8:12 PM

Whats new in SQL Server 2008 - Grouping Sets

Hi All,

Grouping sets is another cool T-SQL enhancement. A GROUP BY clause that uses
GROUPING SETS can generate a result set equivalent to that generated by a
UNION ALL of multiple simple GROUP BY clauses. GROUPING SETS can generate a
result equivalent to that generated by a simple GROUP BY, ROLLUP or CUBE
operation. Different combinations of GROUPING SETS, ROLLUP, or CUBE can
generate equivalent result sets.

In other words, GROUPING SETS will allow you to get the results in a single
query that you could previously only get by executing multiple queries,
because the GROUP BY clauses are invalid in combination

A simple example--

USE AdventureWorksDW
GO

-- Before

SELECT DueDateKey
,NULL
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactResellerSales
GROUP BY DueDateKey
UNION ALL
SELECT NULL
,EmployeeKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactResellerSales
GROUP BY EmployeeKey

-- Now

SELECT DueDateKey
,EmployeeKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactResellerSales
GROUP BY GROUPING SETS ((DueDateKey), (EmployeeKey))

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.7/1234 - Release Date: 20/01/2008
2:15 PM