by

SQL SERVER – Convert IN to EXISTS for better performance

Today, I have received to optimize a store procedure where I have made changes and gotten good performance.

I can show simple method to convert IN clause to EXISTS clause. Here is the simple example.

USE AdventureWorks
GO
— use of =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
— use of EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO

It is NOT necessary that every time when IN is replaced by EXISTS it gives better performance. However, in my case listed above it does for sure give better performance.

Click on below image to see the execution plan.

inexists

Share this:
Share
by

SQL Server – How to create, alter, drop, enable, disable the Primary Key Constraint

The Primary Key constraint is used to identify each row uniquely in a database table.

Primary key constraint enforce the following rules.

  1. Primary keys must contain UNIQUE values.
  2. Primary key column cannot contain NULL values.
  3. Most tables should have a primary key, and each table can have only ONE primary key.
  4. Primary key can consist of one or more columns on a table (When multiple columns are used as a primary key, they are called a composite key.)
  5. Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Create primary key:
There are several ways to create a primary key in a database table.
At the time of table creation:

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar](50) NULL
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([ID] ASC)
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL PRIMARY KEY,
[StudentName] [nvarchar] (50) NULL
)

Method 3:
CREATE TABLE [dbo].[Table1](
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NULL,
PRIMARY KEY ( [ID] )
)

Create primary key on multiple columns:
For defining a PRIMARY KEY constraint on multiple columns and columns should not nullable.
use the following SQL syntax:


PolicyBazaar IN CPL - Personal Loan

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL,
PRIMARY KEY CLUSTERED ( [ID], [StudentName] )
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL
CONSTRAINT [PK_Table1] PRIMARY KEY ( [ID], [StudentName] ASC )
)

Create primary key on existing table:
To create a primary key constraint on the ID columns when Table1 table already exists.

use the following SQL syntax:

ALTER TABLE [Table1]
ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (ID);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column (s) must already have been declared to not contain NULL values (when the table was first created).

Drop primary key constraint:
You can clear the primary key constraints from the table.

use the following SQL syntax:

ALTER TABLE [Table1]
DROP PRIMARY KEY ;

Disable Primary key constraint :
You can disable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
DISABLE;


Voonik CPA

Enable Primary key constraint:
You can enable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
REBUILD;


PaisaBazaar IN CPL - Credit Card

Share this:
Share
by

SQL SERVER – Query to Retrieve the Nth highest record from database table

You can replace the “Table” table with your table name and “N” with the level of salary to be determined.

Here is query to find Nth Highest Record from Database Table.

Method 1: Using sub query
SELECT *
FROM Table t1
WHERE (N-1) = (SELECT COUNT(DISTINCT(t2.salary))
FROM Table t2
WHERE t2.salary > t1.salary)

Method 2: Using Top
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Table1
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary

Share this:
Share
by

Asp.net: How to Read Application Settings from the Web.config File at Runtime

This example reads an application setting identified by the key customsetting1 from a Web.config file. The appSettings element is a NameValueCollection collection of strings. Working with collection elements can be slightly more complicated than working with other configuration elements.

To obtain configuration settings for the root-level Web configuration, null is passed to the OpenWebConfiguration method.

To update a configuration setting, use the Save or SaveAs method of the configuration object. For more information, see Using the Configuration Classes. For additional code examples, see the AppSettingsSection class and related classes.

This example uses the non-static method of obtaining configuration data, which allows you to pull configuration data from any application. If you are going to obtain configuration information from the application in which your code resides, use the static method, which processes faster. For more information, see the Working with Local and Remote Configuration Settings section in ASP.NET Configuration API Overview.

Example:

C#:
System.Configuration.Configuration rootWebConfig1 = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(null);
if (rootWebConfig1.AppSettings.Settings.Count > 0)
{
System.Configuration.KeyValueConfigurationElement customSetting = rootWebConfig1.AppSettings.Settings[“customsetting1”];

if (customSetting != null)
Console.WriteLine(“customsetting1 application string = \”{0}\””, customSetting.Value);
else
Console.WriteLine(“No customsetting1 application string”);
}

VB.Net:
Dim rootWebConfig1 As System.Configuration.Configuration
rootWebConfig1 = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(Nothing)
If (rootWebConfig1.AppSettings.Settings.Count > 0) Then
Dim customSetting As System.Configuration.KeyValueConfigurationElement
customSetting = rootWebConfig1.AppSettings.Settings(“customsetting1”)
If Not (customSetting.Value = Nothing) Then
Console.WriteLine(“customsetting1 application string = {0}”, customSetting.Value)
Else
Console.WriteLine(“No customsetting1 application string”)
End If
End If

Robust Programming
Values read from the appSettings element of the Web.config file are always of type String. If the specified key does not exist in the Web.config file, no error occurs. Instead, an empty string is returned.

Security
The configuration file should be protected on the server by using Windows security settings to limit who can read the file. Avoid storing sensitive information such as user credentials in the appSettings element of the Web.config file. Also consider encrypting configuration settings.

Share this:
Share
by

Asp.net Difference between appSettings and Connection Strings in web.config

In previous versions of ASP.NET, connection strings were stored in the appSettings. In ASP.NET 2.0, features, such as Session, Membership, Personalization, and Role Manager, rely on connection strings that are stored in the connectionStrings element. You can also use the connectionStrings element to store connection strings for your own applications.

The difference is that the connectionString section gives you strongly typed access to your connection strings through the ConfigurationManager class. It’s meant for connection strings specifically. The connectionStrings element specifies a collection of database connection strings, as name/value pairs, for ASP.NET applications and features.

A connectionString object is an XML node that has specific attributes to set; and semantically it refers to a database connection string.

Example:
<connectionStrings>
<clear/>
<add name=”LocalSqlServer” connectionString=”Data Source=(local);Initial Catalog=(DBName);Integrated Security=True” providerName=”System.Data.SqlClient” />
</connectionStrings>

You’ll notice it has a few different attributes:

  • name
  • connectionString : This has a specific string inside of it, it needs an Initial Catalog, a security mechanism (in this case Integrated Security
  • providerName

AppSettings is meant to store general settings in web.config is used to store server names, file paths, and other miscellaneous settings needed by an application.. You can use it to store connection strings also, but I recommend not doing that since there is a specific element for it in connectionStrings.

appSettings is just a user-defined Key-value pair that allows you to… well… set application settings.

It can be anything:

Example:
<appSettings>
<add key=”Email” value=”abc@abc.com”/>
<add key=”MasterKey” value=”True”/>
<add key=”GoogleAPI” value=”1234567890-AA”/>
</appSettings>

In many cases, it would just be odd to put the connectionString in a key-value pair like appSettings (semantically and programmatically). As well as it would make it more difficult to encrypt the connectionString when you need to.

Share this:
Share