Last month, all the techies in India had a great time at the Tech-Ed 2011 (India). Out of the many sessions being conducted in parallel, one of the T-SQL programming sessions, there was one session from the renowned SQL Server MVP – Jacob Sebastian, which I found to be very interesting and different.

What have I done? T-SQL Worst Practices

There are a lot of reading materials and classes that talk about and discuss T-SQL Best Practices. But there are very few who talk about T-SQL worst practices. That’s because it takes experience and a lot of deep understanding and intimacy with the product in hand to come up with such a list. This session was one of them, and that’s precisely why my it grabbed my attention.

In this session, Jacob demonstrated some of the most common T-SQL worst practices that one can make (besides of course not clapping when one likes a session). All of these are not major issues that would jump right out at you, but are things that appear to be absolutely normal and harmless in the development and QA environments, but turn into performance bottlenecks or may end up returning incorrect results when deployed into production.

Today, I attempt to summarize these worst practices into a blog post. We will look at:

1. Practices that can affect accuracy of data returned/processed (Part 01)
2. Practices that can adversely impact performance (Part 02)
3. Practices that can have unpredictable execution side-effects (Part 03)

While the points covered are exactly the same as covered during the Tech-Ed, I have taken hints from Jacob’s queries and have come up with my own versions using the AdventureWorks2008R2 database supplied as a sample database for SQL 11 (“Denali”) CTP01. In cases where I am directly using Jacob’s scripts, I have mentioned it as such.

Part 01 – Practices affecting accuracy of data returned/processed

Using NOT IN is not really the solution

Manager: I need to find all the Sales staff who do not fall within a given set of territory IDs.

You: That’s easy. I will write up a simple T-SQL query to get you the results in a moment.

USE AdventureWorks2008R2
GO
SELECT * FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)

Manager: Is this correct? It does not appear to be so.

USE AdventureWorks2008R2
GO
--17 records
SELECT COUNT(*) FROM Sales.SalesPerson
--8 records
SELECT COUNT(*) FROM Sales.SalesPerson WHERE TerritoryID IN (1,2,3,4,5)
--6 records, instead of the expected 17-8 = 9 records!
SELECT COUNT(*) FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)

From the above calculations, we see that about records are missing from the results set. These records are such which do not have any Territory ID allocated!

You: What went wrong? The query should have worked….

Here’s what happened…

Root Cause: NULL values are special. In the world of electronics, we are all familiar with NULL as the “tri-state” – ON, OFF and a third-state mysterious state. NULL technically means undefined, and hence cannot take part in comparison operations, by default.

When we used the NOT IN operator, we were asking SQL Server to carry out a comparison which it cannot do for records with NULL values and hence, these records were ignored.

Solution 01: You can modify this behaviour of SQL Server by setting ANSI_NULLS to OFF. However, this is not recommended because:

1. Setting ANSI_NULLS to OFF causes the comparison operators in SQL Server to stop following the ISO standards
2. ANSI_NULLS will always be ON in a future release of SQL Server (refer Books On Line)

Solution 02: Alternatively, you can also use the NOT EXISTS clause to fetch the correct results:

USE AdventureWorks2008R2
GO
-- Solution #01 - Set ANSI_NULLS to OFF. 
/****************************************
This is not a recommended solution
****************************************/
SET ANSI_NULLS OFF
SELECT * FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)
-- Because setting ANSI_NULLS to OFF is not recommended, revert back
SET ANSI_NULLS ON

-- Solution #02 - Using the NOT EXISTS Clause
SELECT * FROM Sales.SalesPerson sp1 WHERE NOT EXISTS (SELECT *
                                                      FROM Sales.SalesPerson sp2
                                                      WHERE sp1.TerritoryID = sp2.TerritoryID AND sp2.TerritoryID 
                                                      IN (1,2,3,4,5))

COUNT(*) and LEFT v/s RIGHT joins

Assume the following scenario wherein we have a list of Sales Personnel in one table and the orders booked by them in another table. The scripts and data is supplied by Jacob, because such data does not exist in the AdventureWorks2008R2 database.

/*-----------------------------------------------------------------------------
  Date			: 23 March 2011
  SQL Version           : SQL Server 2005/2008/Denali
  Author		: Jacob Sebastian
  Email			: jacob@beyondrelational.com
  Twitter		: @jacobsebastian
  Blog			: http://beyondrelational.com/blogs/jacob
  Website		: http://beyondrelational.com

  Summary:
  This script is part of the demo presented by Jacob Sebastian during 
  Tech-ED India 2011 on "TSQL Worst Practices".

  Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
-----------------------------------------------------------------------------*/
USE teched 
GO

IF OBJECT_ID('salesrep','U') IS NOT NULL DROP TABLE salesrep
GO

CREATE TABLE salesrep (id int, name varchar(20))
GO

INSERT INTO salesrep(id, name) SELECT 1, 'Jacob'
INSERT INTO salesrep(id, name) SELECT 2, 'Pinal'
INSERT INTO salesrep(id, name) SELECT 3, 'Mike'
INSERT INTO salesrep(id, name) SELECT 4, 'Steve'

IF OBJECT_ID('orders', 'U') IS NOT NULL DROP TABLE orders 
GO

CREATE TABLE orders (salesrep INT, OrderID INT, Amount MONEY)
INSERT INTO orders (salesrep, OrderID, Amount) select 1, 1, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 1, 2, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 3, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 4, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 5, 100

SELECT * FROM salesrep
SELECT * FROM orders 

Manager: I want a list of all Sales Personnel with the number of orders booked by them.

You: Sure. I will have the results ready for your review in 5 minutes

SELECT s.name,
       COUNT(*) AS OrderCount
FROM salesrep s
LEFT JOIN orders o ON o.salesrep = s.id
GROUP BY s.name  

You (thinking): The results don’t look right. What’s going on?

Root Cause: Again, it is the NULL values that bother us. COUNT (*) causes an aggregation of all columns in the result set. Looking at Books On Line (COUNT aggregate function), we can understand that except for COUNT, all aggregate functions ignore NULL values. COUNT is the black sheep in the sense that by default it will count a NULL and all duplicate values as a 1.

Solution(s): There are a minimum of two solutions to this mystery.

1. The first of the solutions is to use the INNER JOIN instead of the LEFT JOIN. However, this might not be what the manager wanted (remember that the manager had asked for a list of all sales personnel with the number of orders booked by them). If we use INNER JOIN, we will only get a list of sales personnel who have booked at least one order in their name
2. The second involves using a column from the right-hand side table of the LEFT JOIN. This would be a correct query

-- Solution 01 - Technically correct, but may not be what the manager wanted
SELECT s.name,
       COUNT(*) AS OrderCount
FROM salesrep s
INNER JOIN orders o ON o.salesrep = s.id
GROUP BY s.name   

-- Solution 02 - Technically correct and what the manager wanted
SELECT s.name,
       COUNT(o.orderid) AS OrderCount
FROM salesrep s
LEFT JOIN orders o ON o.salesrep = s.id
GROUP BY s.name  

Summarizing – Part 01:

Finally, summarizing all the points that have been discussed today (Part 01) and during Tech-Ed 2011

1. Setting ANSI_NULLS to OFF is bad! Any code using this dependency should be revisited
2. Using NOT IN may give you incorrect results. You may use NOT NULL or NOT EXISTS check instead. Be careful about the possibility of NULL values when using NOT IN
3. COUNT(*) with LEFT JOIN may produce incorrect results
4. Use COUNT(column) from RIGHT side table instead of COUNT(*)
5. There are always hidden requirements as in the case of the manager of the COUNT(*) example – pay attention to each and every word!

Thank-you, Jacob, for this really great session!

You can read my memories of Tech-Ed by going through the following series:

Tech Ed 2011 (India) – Day 01 – Connect, Learn, Explore, Evolve!

Tech Ed 2011 (India) – Day 02 – IE 9 – India launch – Fast is now beautiful!

Tech Ed 2011 (India) – Day 03 – For the DBAs – Contained Databases, Extended Events, Virtualization, Wait Stats, Wait Queues & High Availability

Until tomorrow,

Be courteous. Drive responsibly.

Today, we will talk about a slightly different, yet very common subject. We will talk about what are the different ways to measure the number of rows in a table.

I found a couple of posts over the Internet, and I will reference them as and when I demonstrate the associated methods. This post basically is an assimilation of these methods and attempts to perform a comparative analysis to categorize them in terms of:

• Performance (in terms of time)
• Accuracy

The most common methods

The most common of all methods is the one that every person who has even come in contact with Microsoft SQL Server for even 5 minutes knows about. It is the very generic:

SELECT COUNT(*) FROM HumanResources.Employee

Other similar options are:

SELECT COUNT(1) FROM HumanResources.Employee
SELECT COUNT(BusinessEntityID) FROM HumanResources.Employee

Some developers believe that COUNT(1) or using the 1st primary key column is faster for the COUNT() function, however, that is untrue. Here’s the proof:

If you have tables with rows greater than the range of INT data type, you may use COUNT_BIG() instead.

Using Catalog Views

Microsoft SQL Server comes with a few catalog views, which return information that is used by the SQL Server Database Engine. Obviously, this is one of the places where one should go and look for the row count information. A table with a clustered index will have at least one entry for that index in the catalog view – sys.sysindexes. This entry will have the count of the number of rows in the index, which in-turn is equal to the number of rows in the table (for SQL 2008 and up, a clustered index cannot be filtered and hence clustered indexes will always hold the information for the entire table).

SELECT si.rows AS 'RowCount'
FROM sys.sysindexes si
WHERE si.id = OBJECT_ID('HumanResources.Employee') AND si.indid < 2

Comparing against the conventional options, we find that this option is faster!

However, please note that the catalog views may report stale data.

Using DMVs

Mr. Pinal Dave (blog) provides a great method of accurately determining the number of rows in a table with the use of DMVs in his post here.

His query has been modified to only look for the HumanResources.Employee table below:

SELECT sc.name + '.' + ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND 
      pa.index_id IN (1,0) AND
      sc.name = 'HumanResources' AND
      ta.name = 'Employee'
GROUP BY sc.name, ta.name
ORDER BY SUM(pa.rows) DESC

This method falls behind in performance when searching for row counts of individual tables as demonstrated below. However, without the schema name and the table name filters added in the WHERE clause, this is a handy script to get the row counts for all tables in a database. As a matter of fact, I have verified with the help of the SQL Profiler that Microsoft also uses the sys.partitions DMV to fetch the row counts when showing them on the Object Explorer Details window. (If you would like to know more about the Object Explorer Details window, refer my posts – Part I and Part II, which are part of my series on the Underappreciated features of Microsoft SQL Server)

I would recommend this to be in the "tool-kit” of each and every database developer, administrator and operator.

Using Aggregation Functions

The following method, from Madhivanan’s post (blog) (read this post here) is quite interesting:

SELECT SUM(1) AS ‘RowCount’ FROM HumanResources.Employee

Performance-wise, this method is same as the well-known COUNT(*) or COUNT(1), but it’s interesting because it uses an aggregation function to get the row count.

Using System Stored Procedures

This one again comes from Madhivanan’s post, with a small extension from Books-On-Line.

Microsoft SQL Server comes with a big list of system functions (the most common ones being sp_help, sp_executesql and many others which we use on a regular basis). It is no surprise then that we also have a system function to provide information on the number of rows in a table.

Rows in a table occupy space, and hence, it is fit that the system stored procedure responsible to predict the space usage is also responsible to predict the number of rows in a table.

--Default version, may return stale results
EXEC sp_spaceused @objname=N'HumanResources.Employee';

--Force update of usage statistics, will always return current usage
EXEC sp_spaceused @objname=N'HumanResources.Employee', @updateusage=N'TRUE';

Here’s the catch: System stored procedures ALWAYS return an INT. If you have rows that exceed the range of an INT in number, do not use these.

Also, this is slower in performance when compared to the conventional methods mentioned above.

Read more on sp_spaceused on Books-On-Line at: http://msdn.microsoft.com/en-us/library/ms188776.aspx

The next method that also uses a system function is the one below which came from a friend:

SELECT COUNT(@@ROWCOUNT) FROM HumanResources.Employee

This method performs equally well as the conventional ones.

Summary

Summarizing, I would attempt to present a small table that would serve as a ready look-up for each of these methods. I hope you find it useful.

Method	Performance (Time)	Accuracy
Conventional (SELECT COUNT(*) and others)	No	Yes
Catalog Views (sys.sysindexes)	Yes	Approximate
DMVs	Partial*	Yes
Aggregate Functions	No	Yes
System Stored Procedures	No	Approximate

(*Partial = Fares low on performance for individual tables, but scores high for a group of tables).

There are a couple of points which I would like the reader to keep in mind when gathering data for row count analysis:

• System stored procedures will always return an INT value
• Size information is not maintained for non-clustered indexes – please exercise due diligence before using these methods with non-clustered indexes (or tables without clustered indexes, i.e. heaps)
• Row counts may be incorrect for large tables. For their accuracy, always update statistics by using the following before attempting to fetch an accurate row count:

--Update the table usage statistics
DBCC UPDATEUSAGE ('AdventureWorks2008','HumanResources.Employee')
GO
--Per Books On Line:
--Reports and corrects inaccuracies in the sysindexes table, 
--which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

Computing Row Counts for a range of values

When working with a range of values, getting the row counts is a bit difficult and has very limited options. To the best of my knowledge, for users using SQL Server 2005, we can only use the conventional queries:

SELECT COUNT(*) FROM HumanResources.Employee WHERE BirthDate BETWEEN '1950-01-01' and '1960-01-01'

However, for users using SQL Server 2008 and up, the option to create a filtered index (if the range is pre-defined) and then using the catalog view method is always open.

And finally…

Now for the big question – which of the above methods are my favourites? Here’s my choice:

1. Conventional methods if I need the accurate row count for a single table
2. Catalog Views if I only need approximate row counts
3. For fetching row counts of all tables in a database or a schema, I would go in for Pinal’s script

Do you use any other methods? If yes, do let me know – I will publish the alternatives with due credit. Also, if you use any other methods to get the row counts for range of values, please do share your solutions – they will also be published with your name as the contributor.

Until we meet next time,

Be courteous. Drive responsibly.

As you know, I am documenting my experience at the Tech Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

You can find the summary of my experiences on Day 01 and Day 02 at:

Day 01 – http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Day 02 – http://beyondrelational.com/blogs/nakul/archive/2011/03/30/tech-ed-2011-india-day-02-ie-9-india-launch-fast-is-now-beautiful.aspx

Today, as India celebrates it’s win into the cricket World Cup 2011 finals, I will take you through the final day in the wonderful journey of Tech-Ed 2011.

A day for the DBAs

Day 03 was very special – it was a day which had a full track dedicated to the DBA community – guaranteed to attract all DBAs – accidental or not like – bees are to honey.

Hands-on Lab on Contained Databases by Jacob Sebastian

After the keynotes, the day immediately picked up speed as the DBA community got to participate in a hands-on lab on Contained Databases – a long awaited feature, and now part of SQL 11 (Code named “Denali”). The lab was conducted by none other than renowned SQL Server MVP and leader of the Ahmedabad SQL Sever User Group, Jacob Sebastian! His trademark style of explaining difficult concepts by telling a story made this concept a breeze to understand.

I will be taking everyone through the entire hand-on session in a series I am planning for the next week. Stay tuned, and I am sure you will find it useful.

Understanding SQL Server Behavioural Patterns – SQL Server Extended Events by Pinal Dave

Extended Events are not new to SQL Server 11/(“Denali”), but not many know how to efficiently use them. Pinal Dave, the renowned Microsoft Evangelist, demonstrated how to use Extended Events to execute a couple of common database administration tasks. What I liked the most were the demos on:

1. Detect long-running queries
2. Detect page splits

One of the most interesting items of the session was how Pinal demonstrated the concept of what events and extended events are by co-relating them to the human heart and a scenario wherein a hypothetical elephant needs to be taken to the hospital after collapsing during the Tech-Ed. That’s what I call – fun with learning!

You can download the scripts used during this demonstration by visiting Pinal’s blog at: http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/

Data Recovery with Backups by Vinod Kumar

Vinod Kumar was back after Day 01 – this time to show the audience the immense value a good backup strategy can have. Vinod demonstrated how to recover from the following situations from a backup:

1. Recovering/Restoring to a point in time
2. Recovering/Restoring to a named transaction
3. Recovering from a page corruption

Quite frankly, I did not know that one could “mark” a transaction and that this can be used during recovery – just as a point-in-time restore. As a proof of the importance of this feature, Vinod discussed one of the practical implementations that have been done using this feature. In the interest of privacy, the exact nature of the project was not disclosed to the audience.

SQLCAT: SQL Server Consolidation – Using Virtualization & Lessons Learned by Prem Mehra

Prem Mehra is a Program Manager with Microsoft at Redmond and works for the SQL Customer Advisory Team (SQLCAT). This team is comprised of solution experts that regularly engage in the largest, most complex and most unique customer deployment engagements worldwide.

In this session, Prem discussed some of the consolidation projects that the SQLCAT team has done in a virtualized/cloud based environment. The audience had some great things to learn from him, and my biggest take-away was the following:

“If a SQL Server is not performing well, the problem is either in the application or in the underlying hardware platform – in the order mentioned.”

Also, Prem demonstrated the ideal approach in moving towards a cloud based deployment (which is shown here as a picture captured of his slide) and how the upcoming SQL Server 11 (“Denali”) will help in the journey.

(Source: http://msdn.microsoft.com/en-us/library/ee819082.aspx)

SQL Server Waits and Queues – Your Gateway to Performance Troubleshooting by Pinal Dave

Do you really know how to remove SQL Server bottlenecks when a server seems to be really, really slow? Yeah, of course! Look at the wait statistics and queues – how difficult can it be?

Not much. So, are CXPACKET waits bad? Can we eliminate LOGBUFFER waits? What are Signal waits?

Pinal did it once again – with a simple example of a group of people awaiting a cab and then stopping to withdraw cash from an ATM, Pinal very beautifully explained Signal Waits. He then went on to demonstrate and explain what CXPACKET waits are and how they will always be there, and that they might sometimes be good. Finally, in an power-packed demo, he demonstrated how moving the data file of a database to his portable USB drive can reduce LOGBUFFER waits. (Yes, you read me right – Pinal moved the data file of a database to his portable USB drive)

Here’s where you can read Pinal’s series on Wait stats & queues – http://blog.sqlauthority.com/2011/02/28/sql-server-summary-of-month-wait-type-day-28-of-28/

SQL Server AlwaysOn: The next generation high availability solution by Balmukund Lakhani

High Availability is one of the key requirements of any SQL Server deployments. Who better than the Technical Lead in the SQL Server support team with Microsoft India GTSC to demonstrate how simple it is to setup a cluster, configure a high-availability solution, and demonstrate a failover – all LIVE and within the hour!

Balmukund demonstrated the High Availability “AlwaysON” features which will be made available in SQL Server 11 (code named “Denali”). The solution will definitely make the lives of all DBAs and IT administrators easier because combined with the power of Windows Server 2008 R2, setting up a cluster (or an Availability Group) is a breeze. The cluster can have one automatic fail-over partner, with other manual fail-over partners. The best benefit of this solution is that it is similar to mirroring – but without the drawbacks of mirroring.

Here’s a step-by-step guide to getting you started on HADR: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/12/17/sql-server-denali-alwayson-hadr-step-by-setup-setup-guide.aspx

Closing Keynote – Visit by Anil Kumble!

Finally, the time came to close the Tech-Ed 2011 (India). With a heavy heart, all participants submitted their feedback forms for the last sessions of the day and proceeded towards the keynote hall for the closing keynote.

The final attraction of this year’s Tech-Ed was that we had a very special guest with us. None other than the star cricketer – Anil Kumble! None other than Gaurav Kapoor, who hosted the live sessions of Tech-Ed, introduced Anil Kumble on stage and the entire group erupted in cheers! Hands went-up all the way to catch a snap or two of Anil Kumble in person. So high was the excitement, that nothing could distract the audience. I will let the pictures speak for themselves.

 

A full-house awaiting the start of the closing ceremony.

What’s next?

My next post will be a deep dive into Jacob’s session on the worst T-SQL practices on Tech-Ed Day 01. Next week, we will continue the series on the Underappreciated Features of SQL Server, and also explore the “Contained Database” feature of SQL Server 11 (code named – “ Denali”). Contained Databases is based on the hands-on lab conducted on the same topic by Jacob on Day 03 of Tech-Ed 2011.

Watch out for some exciting new and exciting reading material hot off the presses!

Until we meet next time,

Be courteous. Drive responsibly.

As many of you would know by now, these days I am attempting to summarize my experience at Tech-Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

The summary of my experiences on Day 01 can be found at:http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Today, I will be sharing with you the experience of Day 02.

IE 9 India Launch – Fast is Now Beautiful!

I just can’t believe how lucky I am to be a part of history! Day 02 of the Tech Ed 2011 saw the India launch of the most awaited web-browser – The Internet Explorer 9! Brian Hall, General Manager of Windows Live and Internet Explorer products was at the venue to demonstrate the path towards a more beautiful web!

With full support of HTML 5 and CSS3, this is a completely new browser, that allows us (the end users) to pay more attention to the web-site and the overall browsing experience rather than the browser itself. The IE9 browser can leverage the CPU’s available power for better performance. The browser integrates seamlessly with Windows 7 and is able to make use of features like jumplists to allow for a highly productive web-experience.

Incidentally, the new JavaScript engine in the IE9 is called – “Chakra”! The world’s 2nd largest developer ecosystem (refer Mr. Qi Lu’s comment on the Indian IT industry during the opening keynote on Day 01) does have a deep influence.

Some of the sites where you can see the capabilities of IE9 in full bloom are http://www.snapdeal.com and http://www.flipkart.com. Go check them out!

Download IE9 from http://www.beautyoftheweb.com/

Test your browser against IE9 by using the test tools available at: http://ie.microsoft.com/testdrive/

IE6 countdown…

10 years ago, a browser was born. A browser that changed the browsing experience forever, and was so great that people still use it to this day! It was the Internet Explorer 6. Now, it is time for IE6 to die. Internet technologies have grown to such an extent that all of us need to work together to finally sunset IE6. Visit  http://www.ie6countdown.com/ to do the following:

1. Join the cause – Display the countdown timer on your website for IE6 users
2. Get a list of the reasons of why you absolutely should upgrade to IE9
3. Spread the message – about 15.8K tweets are already out as of writing this post – won’t you do your part?

Windows Azure – a solid cloud platform

After the power-packed launch of IE9, it was time for studying some case-studies on the practical implementations of Windows Azure. Bharat Shyam, General Manager, Windows Azure was at the event to showcase the success stories of Windows Azure. The audience was mesmerized when Bharat shared some of the biggest Azure installations. To name a few, Windows Live, Bing, Exchange Hosted Services, MSN website, Windows Hotmail and a lot of other commonly used sites and services are hosted on Azure.

Breakout Sessions – a mix of Core Dev & BI tracks

Day 02 was a mixed-bag for me. Personal interest in data access technologies and BI technologies made me attend sessions across tracks – some in the Core Dev and others in the BI track.

Core .NET Framework 4.0 enhancements every developer should know

Sanjay Vyas and Raj Chaudhuri, two of the leading independent experts (and MVPs) in the .NET area presented a very concise, but thorough session on the key enhancements of the .NET Framework 4.0.

What’s new in the Base Class Library?

1. Managed Extensibility Framework (MEF) – Declaration & consumption of extensibility points and monitoring of new runtime extensions
2. New Data types of BigInteger/ComplexNumber/Tuple and Sorted Set
3. I/O improvements – Memory mapped files

New Language Features

New in C# 4.0	New in VB.NET 10	New in Both
Named Parameters Optional Parameters Dynamic Scoping	Statement Lambdas Multiline Lambdas Auto-implemented properties Collection initalizer	Generic Variance Extension Property

Data Access Methodologies – When to choose what (ADO.NET, Entity Framework, LINQ, WCF Data Services)

Post lunch, Wriju Ghosh gave a concise pill on Data Access Methodologies explaining when and how to choose between ADO.NET, Entity Framework, LINQ and WCF Data services.

Switching gears with BI

After some core Dev sessions, it was time to join the BI track by attending the following sessions:

1. Building Semantic Models in SQL Server “Denali” – Rushabh B. Mehta (President, PASS and MD, Solid Quality India Pvt. Ltd)
2. SSAS – Designing, Development & Deployment Best Practices – Amit Bansal
3. Internal and working of DAX with Power Pivot – Rushabh B. Mehta

Power Pivot, since it’s release has attracted me for it’s simplicity, and Rushabh’s session was full of pointers to make Power Pivot a very powerful self-service BI tool.

Closing time – Demo Extravaganza!

As on Day 01, the final session of the day was an action-packed show of the sheer power that Microsoft’s technologies possess. From games that one can make in under 3 minutes to calculating the distance between Bengaluru and Hyderabad (venue, Tech-Ed 2009) using SQL Server Spatial databases – this session had it all. The audience never batted an eyelid, and we had a perfect end to another perfect day!

Stay tuned for a detailed account of excitement on the final day of Tech-Ed 2011 (India)!

Until we meet next time,

Be courteous. Drive responsibly.

Last week, from March 23-25, 2011 were amongst the 3 most important and exciting days for anyone remotely connected with Microsoft technologies. They were the days when the Tech-Ed 2011 (India) was hosted by Microsoft at the Lalit Ashoka Hotel in Bengaluru, Karnataka.

Microsoft Tech-Ed 2011

Microsoft Tech-Ed is the premier technical education and networking event for tech professionals interested in learning, connecting and exploring a broad set of current and soon-to-be released Microsoft technologies, tools, platforms and services.This year, Tech-Ed 2011 had been divided into a series of multiple tracks mentioned below:

1. Developers
2. Infrastructure Professionals – Focused on solutions that save time, reduce costs and provide an efficiently managed data center
3. Architects – Focus is on bringing in uniqueness and offering niche topics
4. Management – Focused on innovating and reinventing oneself to win the dreams that have been built over the years
5. Interoperability – The focus here is on how businesses today can seamlessly interoperate and integrate with multiple vendor systems, technologies and applications using highly flexible Microsoft technologies
6. Information workers – These sessions focus on how Microsoft hardware and software can help us lead a more productive and comfortable lifestyle
7. Chalk Talks – conducted for the first time in Tech-Ed, these are “free-style” sessions making them a great place to have a great dialog and Q&A with the who’s who of Microsoft technologies

We, being developers, are ultimately most interested in the Developers track, which was further divided into:

1. The Next Web
2. Client Development
3. Database Development
4. Unleash a more beautiful web
5. Core Dev
6. Cloud Services and the Windows platform
7. Business Intelligence
8. Visual Studio Tools
9. Sharepoint Development

Read more about Tech-Ed 2011 (India) at http://www.microsoft.com/india/teched2011/

Through the next couple of posts, I will be sharing with you my experience on the wonderful time I had at the Tech-Ed event.

Opening Keynotes

Tech-Ed 2011 began by the registration of what I believe to be a crowd of at least 3000 participants. Once the registration was complete, we all started to proceed towards the main keynote area for the keynotes.

On Day 01, we were lucky to have keynotes from esteemed leaders like Mr. Qi Lu, President, Microsoft’s Online Services Division and Mr. Yousef Khalidi a Distinguished Engineer (Windows Azure) at Microsoft.

Opening his keynote session, Mr. Qi Lu appreciated India’s IT workforce as the world’s 2nd largest developer ecosystem. His session was focused on how this developer ecosystem can contribute to the making of a new web and how Microsoft’s online division is working towards this goal by developing new ways to interact and providing intelligence through services.

He introduced the concept of the consumer cloud – one that is an online service which can understand user intent using natural user interfaces like the Microsoft Kinect to enable task completion by local commerce and personal relevance. He demonstrated how Microsoft’s Bing has the vision to usher in a new cloud organizing paradigm by combining the Web of pages and the Web of people for better target-ability, measurability and predictability of the search results.

Mr. Yousef Khalidi focused on how we have grown and come almost a full circle as we moved from centralized, thin clients to distributed computers and now on to large data centers running scaled-out commodity hardware. He explained how Windows Azure is an example of Platform-as-a-Service (PaaS). However, the best take-away from his session was the definition of a “cloud” – A cloud is a style of computing provided as a service over the Internet using dynamically scalable, virtualized hardware and resources.

By the end of the session, I was left with great surprise for Windows Azure after learning that prime-time applications like Windows Gaming, Bing Twitter, Docs.com and Microsoft Hohm use Windows Azure.

Post lunch – Sessions on Database Development

After the lunch at the pool-side (being at a Microsoft event has it’s benefits), it was time for the power-packed break-out sessions.

The first of these sessions was from Vinod Kumar (blog) on the ““Unexplained” of SQL Server Security”.

Vinod demonstrated the secure SQL Server login process using the Microsoft’s Network Monitoring tool (download here). He showed the audience that not for a single handshake is the user password being exchanged over the wire. Later, he took the audience for a deep dive into SQL Server security by demonstrating how permissions can be restricted to a particular group of columns in a table to restrict the user of SELECT * and how one can use Policy Management to implement SQL Server security. He also demonstrated how auditing can be made secure and system provided an ability to detect an attempt to intrude via impersonation by the use of SQL Server certificates.

After Vinod’s session into the internals of Microsoft SQL Server, we had a session on “SQL Server Denali – What is new for Database Developers” by Prachi Bora, a Program Manager at Microsoft India R&D. She covered some of the exciting new features of SQL Server “Denali”, which include:

1. Statistical Semantic Search
2. “Surrounds With” feature
3. Substring matching in Intellisense
4. Conditional Breakpoints

After the tea break…”What did I do?”

After the tea break, we had a wonderful session on the worst T-SQL practices by none other than our very own SQL Server MVP – Mr. Jacob Sebastian!

The hall was packed with more than 200 attendees, and everyone participated whole-heartedly as Jacob ran through some of the most common T-SQL worst practices that he has seen in T-SQL code over the years. Everyone in the hall was, I am sure, recalling the various lines of code that they would need to go back and change as soon as they resumed office on Monday! Jacob highlighted the various performance and computational issues that might arise due to a simple slip of the mind on account of the developer attempting to finish off a task in a hurry. The session has left such an impression that I will, for my record, be writing a detailed post on the various points highlighted by Jacob. Stay tuned!

After Jacob’s session, it was a once-in-a-lifetime opportunity for all present in the hall at the time as Mr. Prem Mehra, a program manager with the SQLCAT (SQL Customer Advisory Team – http://sqlcat.com) at Microsoft in Redmond stepped up on stage. The honourable Mr. Mehra has over 49 years of experience in the IT industry, and it was simply mesmerizing to watch him talk about some of “The Largest, Mission-Critical Deployments of Microsoft SQL Server around the world!” He talked about some SQL Server deployments spanning leading US insurance providers, health-care organizations, financial institutions, banks and betting web-sites.

During the session, someone asked Mr. Mehra a question regarding the comparison of Microsoft SQL Server with the competition in terms of scalability, high availability and reliability. In response, Mr. Mehra simply presented the following slide and left the audience to judge for themselves. For me, it was a proof of something I have always believed – the product we love, Microsoft SQL Server, is simply the best!

Closing time – Demo Extravaganza!

As the evening set in, it was time to end the day on an action-packed, excited note. We gathered back into the main keynote hall, where we had a half hour of the most electrifying demos – all completed in less than 3 minutes each! What a magical atmosphere had been created – almost as if it was Hogwarts and Harry Potter himself was presenting!

Stay tuned for a detailed account of excitement on Tech-Ed 2011 (India) Day 02!

Until we meet next time,

Be courteous. Drive responsibly.