This article going to talk about real world query optimization.Most of the times all developers and Database administrators face the long time running query.so this article will help to you optimize the sql query with index.

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

Table columns: 

tblEmail (email varchar(255), IsDeleted int)
tblEmailPromotion (email varchar(255), PromotionID int)

Both tables without index,

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 2:43 (2 minutes 43 seconds) to get the result.

Change the question from "LEFT OUTER JOIN" to "IN" become

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

Now, you’ll see the different, it will takes faster result.

Now, we index table tblEmail column Email (Unique)

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 1:22 (1 minute 22 seconds) to get the result

Now, we run the 2nd query:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It only takes 27 Seconds

To make the 2nd query better:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

This will give 15 seconds.Now you can feel the different way of query optimization and retrive records from database in short time.

Hope the tutorial above help you. If you’re looking for SQL 2012 hosting on Europe server, please visit HostForLIFE.eu. HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.microsoft.com/web/hosting/HostingProvider/Details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

Developer Testing in Visual Studio 2012 has been improved a lot and it is allowing them to concentrate more on the code they are writing in the application. In addition to the testing framework that comes with Visual Studio 2012, you can easily add third-party testing frameworks in the IDE. This post discusses the new unit and integration testing features for developer in Visual Studio 2012.

To open the new Test Explorer window, Select the Test Menu then choose windows and click Test Explorer

The look and fell of Test Explorer in VS 2012 is different in number of ways. You do not have too many different windows to say same information in new IDE, instead you have one widow that focus on unit and integration testing results.

When you select a test case in Test Explorer then you can see the detailed information about the test in below pane. The green bar in test explorer gives you quick view on the out-come.

Integrating Third Party Testing Frameworks

One of the important feature for developers for testing in this release is, it enables you to quickly integrate third-party testing frame-works in VS 2012. So If your team uses N-Unit or XUnit.net or some other testing framework then you can simply install Visual Studio extension.

To open the Extension tools manager, Go to Tools and click Extensions and Updates

You can search the framework that you need and download as shown below

In Visual Studio 2012 Regard less of which Testing Framework you use, it is fast and scales  up to thousands of test cases.

Developer testing is also very easy to use, If you want to Run or Debug the test that is in your editor then you can right-click on the code and select either run tests or debug tests.

You can also Filter the test cases by entering the search term in Test Explorer. You can also group tests with different properties, You can group the tests either by Test Outcome or Duration.

If you often require to run unit and integration tests in your solution then with single click in Visual Studio 2012 you can accomplish with Run test after build feature.

Now your test will run as part of every build you do.

With a single-click you can analyze and understand various code-metrics about your solution.

The Code Metrics Results window will appear as below information like number of line of code , Class Cohesion and Depth of inheritance

As it in previous visual studio versions, you can use Code Analysis Tool to quickly find common coding and design mistakes in your code

Another new feature in Visual Studio 2012 is Code Clone Analysis, using this feature you can scan your entire solution for duplicate code

Code Clone Analysis is smart enough to identify the duplicate code and groups the results with Exact match and medium match.

You can also use code clone feature that matches selected code in editor as shown below

Code Coverage Analysis

Code Coverage Analysis tool helps you to know how well your tests exercise your code? Code Coverage Analysis has significantly improved in Visual Studio 2012. To Analyze the code coverage of all the tests in your solution simply click Run drop down in Test Explorer

You can browse and explore the Code Coverage Results after completing the run

In this article, we make a database in Microsoft Access and that database is used in SQL Server. 

So let's take a look at a practical example of how to import a Microsoft Access database table into SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple steps to do that as described here.

Creating a Empty Database in SQL Server

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Database -> right-click-> select New database

This would open the "New database" window:

Now enter a database name to create a database.

Now click on the OK button to create the database. The new database will be shown in the Object Explorer:

Creating a Table in Access Database

Now open the Microsoft Access database and create a database.  The Access database looks as in the following:

Importing Access database in SQL Server

Now right-click on the Test database and select the Import Data option.

database Name -> Tasks -> Import data

Now click on Import data. The import and export wizard will be opened.

Now select a Data Source. And browse the file from the system.

Now click on the Next Button and enter the computer name or server name and select a database.

Server Name: MCNDESKTOP08\MYMSSQLSERVER

Database name: Test

Now click on the Next Button.

Now click on the Next Button.

Now again click on the Next Button.

Now click on the Finish Button.

And finally click on the Close button.

All of your Access data is nicely imported into a SQL Server 2008 database. Now refresh the test database and expand it to see the imported table. 

In this article you will see how to create a masked password textbox. A masked control is used to save time and reduce the complaints and errors. They enhance the function of the textbox control, which can validate the input. By default the property masked is set to none. A password textbox is used for authorization of an any application. It is very useful to provide security for any application.

1. Open Your VS LightSwitch and Create a New Table

2. Create a Table called Customer

3. Right Click on Screens à Add screen

4. Select New Data Screen

5. Expand Password à select textbox and custom control

6. Go to password properties à Click change

7. Select password control à OK

8. Click write code->Select CreateNewCustomer_InitializeDataWorkSpace->Write the following code

using System;
using System.Linq;
using System.IO;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Client;
using Microsoft.LightSwitch.Presentation;
using Microsoft.LightSwitch.Presentation.Extensions;
namespace LightSwitchApplication
{
    public partial class CreateNewCustomer
    {
        partial void CreateNewCustomer_InitializeDataWorkspace(List<IDataService> saveChangesTo)
        {
            // Write your code here.
            this.CustomerProperty = new Customer();
             this.FindControl("Password").ControlAvailable += pwdAvailable;
        }
        private void pwdAvailable(object sender, ControlAvailableEventArgs e)
        {
            ((System.Windows.Controls.Control)e.Control).LostFocus += PasswordLostFocus;
        }
        private void PasswordLostFocus(object sender, System.Windows.RoutedEventArgs e)
        {
             this.CustomerProperty.Password = ((System.Windows.Controls.PasswordBox)sender).Password;
        }
        partial void CreateNewCustomer_Saved()
        {
            // Write your code here.
            this.Close(false);
            Application.Current.ShowDefaultScreen(this.CustomerProperty);
        }
    }
}

9. Run Application (Press F5)

One of the biggest benefits of building applications with Visual Studio LightSwitch is that it generates the user interface for you, including labels, data-bound fields, commands, and controls. You can rearrange the positions of controls on the screen, and you can determine a number of properties for setting up the appropriate layout. If you're an experienced developer coming from a different development environment, you might need to set up additional requirements such as displaying text messages, implementing a home screen, or interacting with controls at runtime. This article shows a number of handy tips and tricks that you can use to create even more powerful and interesting screens.

Adding Labels and Descriptive Text

LightSwitch automatically adds label and text box controls to display data-bound information coming from the data source for both single entities and entity collections. This is a tremendous benefit because it saves the developer from having to select and add the appropriate controls. However, in LightSwitch every label or text box control is data-bound. Unlike in other development environments, such as Microsoft Access or Visual Studio 2010, you can't show a text message inside an unbound control.

For instance, you might want to show warning messages, advice, or welcome text in your controls. To accomplish this goal in LightSwitch, you use local properties, which are basically data items associated to the selected screen and representing a piece of information that isn't related to the bound data source. Think of a local property as a variable of a given type.

Let's try an example. Suppose you want to display a warning message inside a screen. Follow these steps:

1. Open the screen designer for the screen and click Add Data Item. This action opens the Add Data Item dialog.

2. Select the Local Property item.

3. Specify the data type in the Type combo box (see Figure below) and a name for the property. For this example, use a String type, enter WarningMessage as the property name, and then click OK. At this point, the new data item appears with the list of other data items and methods.

Make sure that the Is Required check box is deselected when you use local properties to display descriptive messages; otherwise, the validation mechanism will throw an exception. In fact, data validation automatically runs against local properties.

4. Drag the property from the list of data items onto the designer's surface and release it under the Screen Command Bar element. Since the property will only be used to display some text, you can replace the default Text Box with a Label.

5. Go to the Properties window, where you can fine-tune the appearance of your label.

6. Change the value of the Label Position property from Left-aligned to None. This change ensures that the name of the label will not be shown.

7. Change the value of the Font Style property from Normal to Warning. As Figure below shows, you can select from a number of font styles. When you select Warning, your text message will be displayed in red and boldfaced (assuming that you're using the default LightSwitch theme).

So far you've prepared a label to display text, but the dialog offers no way to assign text to the label at design time. The only way to perform this assignment is by writing code, so click the Write Code button in the upper-right corner of the screen designer. You can write the label assignment in a couple of different method hooks:

- Created. Write the assignment inside Created when your text is a message that doesn't take information from the screen's data source. This is exactly our case.

- InitializeDataWorkspace. If you need to display some text that's constructed with information coming from the screen's data source, write the assignment inside InitializeDataWorkspace.

Listing 1 shows how to perform the assignment. (In your code, replace ScreenName with the name of the screen you're creating.)

Listing 1—Assigning the content of an informative label.

Private Sub ScreenName_Created()
    Me.WarningMessage = "Warning: the application is going to handle your information."
End Sub

This is nothing but a variable assignment. If you run the application, you'll see how the text message appears, as represented in Figure below.

In Part I of this Article, we have seen creation of an application with Forms Authentication i.e. when an application starts it should ask the user to enter login credentials.

In this let us discuss about how to create Windows Authentication Application.

Preparing Solution:

We have already created the project FormsAuthenticatioInLS; use that project to continue the creation of Users, Roles and Permission.

Let's confirm the Table has the following columns.

Setting Windows Authentication:

If the Table is perfect then next check with the Project Properties to confirm that you have selected Use Windows Authentication option.

Windows Authentication is recommended if all your users are on a windows domain and you want to trust that whoever is logged into their computer is the same user that is using the application.

It means you don't have to provide an additional login form. This is handy because you never have to store or manage passwords outside of Windows itself which makes it very secure, however this is usually only practical if the application is running in a corporate/domain Intranet environment.

In Project Properties Windows select Access Control and then select the option Use Windows Authentication.

You have two options with Windows Authentication.

- Allow only users specified in the Users screen of your application.
- Allow any authenticated Windows user.

The first option will let you to create users in our application to authenticate.

The User name must be a valid username in the form of an alias (jagan), domainand alias (jagansvr\jagan), alias and domain ([email protected]), orfully qualified domain name and alias (sbalram.corp.jagansvr.com\jagan), and it must be less than 256 characters. If you are using Forms authentication, the username must be unique and must be less than 256 characters.

The second option will allow all users in a Windows domain or authenticated by Windows. Press F5 to see the Application in Action.

As we have selected Windows Authentication our application is logged in automatically as a Windows Authenticated user.

When we create a new database inside the SQL Server, it is typical that SQL Server creates two physical files in the Operating System: one with .MDF Extension, and another with .LDF Extension.
* .MDF is called as Primary Data File.
* .LDF is called as Transactional Log file.
 
Sometimes, it looks impossible to shrink the Truncated Log file. The following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName

GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([DBName_log], 1)
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT
GO

There are two types of Authentications available in LightSwitch applications.

1. Forms Authentication
2. Windows Authentication

In this article, I will discuss the first one, Forms Authentication.

Forms Authentication:

Forms Authentication means that a username/password is prompted for when the application opens and these values are checked against the database.
 
This works nicely for clients running across the Internet that are not on a Windows domain. I'll show you both but first let's choose Forms Authentication.

Setting up LightSwitch Solution:

Create a LightSwitch Desktop Application. And create a table called Person as shown in the following picture.

Design a screen for the created table Person. We will discuss the design in future articles

Select the List and Details Screen from the Screen Template list [No: 1] for getting the Person details as well as the Details of the selected Person.

Give a name for the Screen [No: 2].

Select the Date for the Screen Data ComboBox [No: 3] which is retrieved from the table we have created.

Enabling Authentication:

By default the authentication is not enabled. To enable the authentication just select the project's properties from the Project Menu.
Select Access Control [No: 1] Menu Tab.

From the Access Control Tab, select the Use Forms Authentication option [No: 2].

Yes. We have just done that with enabling the forms authentication. The next step is to create the Permissions for Roles for Users. We will discuss Roles and Users later; first we will see how Permission works.

Adding Permissions:

Permissions are nothing but allowing the user to do something on our LightSwitch application.

In the above picture we have created three permissions Read, Write and Delete. The SecurityAdministration is the default permission provided by the LightSwitch application to create Users and Roles.

We have selected the four permissions to True on Debug mode. So that user can Read, Write and Delete or Add users and roles in Debug mode.
Set the permissions we have created to take effect.

Setting Permissions in Methods:

To add a method to check for the permission, we need to open the table we have created. For this application we have created Person table; just open it.

On the right top corner you can see the Write Code menu. Just click it. You will get a collection of items. From that select Access Control group as shown in this above figure.

Click on People_CanInsert, _CanDelete and _CanRead. These are the default methods provided by LightSwitch.

Now write a line of code as shown in the figure given below.

At the time of creation of Permissions an enumeration of Permissions will be created for us with the Permissions we created in the Access Control Tab in Project Properties.

The first method _CanDelete will check for the currently logged in user whether the user has the permission to do a Delete. If it is true then the application will allow the user to delete.

As like _CanDelete method, the other methods will act.

Now we are ready to Press F5. Just press it.

In Debug mode the LightSwitch application will not show the Login form; it will ask for the user name and password only on Published Application. But permissions that we have selected as granted will havew an affect in Debug mode. Let's see in Action.

As we have enabled all the Permissions the Buttons for Add Edit and Delete are enabled here.

Now deselect the CanDelete permission in Project Properties to check whether it is preventing the user to delete the data.

Here we have deselected the CanDelete Permission. Let's see in Code and Screen.

We can see that the result is false because the Permission CanDelete is deselected.

As the CanDelete Permissions is deselected, the Delete button is disabled in the application.

We have just enabled Permissions in Debug mode but we need to create the user and their roles and permissions for that roles created.

In Part II we will discuss about how to add Users Roles and Permissions for the Roles.

Sometimes our clients complained about this issue when they run a report in the Report Manager:

Your browser does not support scripts or has been configured not to allow scripts.

So, in today post and hope this help for all of you who facing this problem too. What is the solution??

- go to (from its menu) Tools/Internet Options
- go to Security tab
- select Trusted zone from the list of available zones
- click Sites button
- in new window provide the URL of the Reports Manager (e.g. http://MACHINE_NAME/*); if you are running Reports Manager not on the default port, provide the port number as well

Then, it works like a charm. :)

Sometimes you’ll get this error message when queries your database:

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation"

So, how to fix this error?

Just use the following syntax to collate on the fly when joining up tables with different collations. I integrate system so have to do this allot.

select * from [Product] p join [category] c
on 
c.[Name] collate SQL_Latin1_General_CP1_CI_AS
=
p.[Name] collate SQL_Latin1_General_CP1_CI_AS

Hope this help.