The notes taken while reading MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 (Self-Paced Training Kits).

From the book:

“The join relates tables based on a key column, such as primary key or a foreign key.”

JOIN Types

Performance tips from Chapter 14 — Improving SQL Server Performance related to joins:

“Try to avoid nullable foreign key columns to limit the amount of outer joins that might need to be written. Outer joins tend to be more expensive to process than inner joins. ”

“Be sure to create an index on any foreign key. Because foreign keys are used in joins, foreign keys almost always benefit from having an index.”

• INNER JOIN – default join. All matching rows will be returned.

Example:

• FULL OUTER JOIN – this join returns all matching and un-matching rows from both tables.

Example:

• LEFT OUTER JOIN – this join returns all, either matching or un-matching, rows from the left table.

Example:

• RIGHT OUTER JOIN – this join returns all, either matching or un-matching, rows from the right table.

Example:

Examples are based on the sample AdventureWorks DB available here.

More Info

• Join Fundamentals
• Using Inner Joins
• Using Self-Joins
• Using Cross Joins

Related Books

• Ultra-Fast ASP.NET: Build Ultra-Fast and Ultra-Scalable web sites using ASP.NET and SQL Server
• Advanced .NET Debugging
• Debugging Microsoft .NET 2.0 Applications

Special thanks go to my SQL mentor, SQL Customer Advisory Team member Jimmy May (blog|twitter)

The notes taken while reading MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 (Self-Paced Training Kits).

When application access the SQL Server instance make sure those three configurations are in sync:

• Network protocols available for the SQL Server instance.
• Network protocols available for the client, the consuming application.
• Connection string setting for network protocol.

Configure Network Protocol Available for SQL Server Instance

• Start SQL Server Configuration Manager
• Expand SQL Server Network Configuration node on the left

• Available protocols:
  • Shared memory – when application resides on the same machine where SQL Server instance lives – suitable for development.
  • Named pipes – designed for LAN, application access the SQL Server instance over the wire, fastest network protocol [Shared Memory is the fastest in general but not suitable for distributed deployment]
  • TCP/IP – when dealing with slow network or wide area. Seems like it’s a bad design when putting your SQL Server away from the app and letting it access over slow network.
  • VIA – applies to special case of Virtual Interface Adapter hardware – rare.

Configure Network Protocol Available for Client Application

• Consuming application [e.g. ASP.NET] should be configured using SQL Native Client Configuration node:

• Order means which protocol being attempted first. Disable those that do not apply. For example, if the app access the SQL Server instance over the wire, disable Shared Memory protocol to short circuit protocol selection.

Configure Network Protocol Using Connection String

• Use connection string to specify specific protocol following this pattern -  tcp:<serverName>\<instanceName>,protocolNumer
  • Ex. “server=tcp:.\sqlexpress, 1431; …”

Summary

Best case scenario:

• Configure SQL Server’s Network protocol to use only one, Named Pipes, for example, as it appears to be the fastest.
• Configure native client to use the same protocol that’s available for the SQL Server instance. This will reduce the number of attempted protocols, saves time.
• Do not specify any protocols in the connection string to avoid the administration/configuration errors during deployment.

Related Books

• Ultra-Fast ASP.NET: Build Ultra-Fast and Ultra-Scalable web sites using ASP.NET and SQL Server
• Advanced .NET Debugging
• Debugging Microsoft .NET 2.0 Applications

The Problem

Recently hit an issue of inability of debugging in Visual Studio 2010. I was receiving the following error message right after hitting F5:

“Unable to start Debugging.The SilverLight Developer Runtime Not Installed. Please Install a matching version”

 

I have verified the Silverlight version installed by navigating to a Silverlight enabled website (http://www.silverlight.net/) and right clicking on the running Silverlight control:

I have also checked what version Visual Studio was targeting – it offered Silverlight version 3. I thought it’s time to search the web.

The Solution

The solution came from the post Unable to debug silverlight which was pointing to the Silverlight managed debugging package which appeared to be Silverlight_Developer.exe:

After successfully installing it I can flawlessly debug my own code flaws.

Related Books

• Developing More-Secure Microsoft ASP.NET 2.0 Applications (Pro Developer)
• Ultra-Fast ASP.NET: Build Ultra-Fast and Ultra-Scalable web sites using ASP.NET and SQL Server
• Advanced .NET Debugging
• Debugging Microsoft .NET 2.0 Applications