How to write inner join in update query T-SQL

When we are very new to t-sql, we generally face a problem like how to make a query to update a table by doing inner join. if you don't know this, your queries become very complex. Below is the example how to write that.

Where it can be useful is,
for example, some how db admin created a table called State, where he is using the StateName as the foreign key instead of the StateID to employee table. Now he wants to update the State column in employee table with the StateID from StateName, we need this inner join in update query.

//Syntax:
Update Table1 set columnName = pt.columnName
from Table1 d
inner join Table2 pt on d.columnName = pt.ColumnName

//Example:
Update Employee set State = s.StateID
from Employee e
inner join State s on e.State = s.StateName 

Read More...

How to Create/Drop an index in a table through t-sql

Create an Index in a Table:

Syntax: CREATE INDEX [IndexName]
ON [dbOwner].[TabelName] ([ColumnName])
WITH ( ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY]

Example: CREATE INDEX [UQ_DateCreated]
ON [dbo].[Contact] ([DateCreated])
WITH ( ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY]

Drop an Index in a Table:

Syntax: DROP INDEX [IndexName] on dbOwner.TableName

Example: DROP INDEX [IX_Content] on dbo.Content Read More...

How to add a column and set primary key in a table through t-sql

Add a Column to a Table:

Syntax: ALTER TABLE TableName add columnName DataType NULL/NOT NULL

Example: ALTER TABLE Employee add IsDeleted bit NULL

Add a Primary Key To aTable:

Syntax: ALTER TABLE TableName add PRIMARY KEY(ColumnName)

Example: ALTER TABLE AddressType add PRIMARY KEY(AddressTypeID)

Note: PrimaryKey:Don’t Allow duplicates. Read More...

How to set Foreign key through sql script

Syntax:
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ColumnInFirstTable)
REFERENCES ReferenceTable(ColumnName)

Example:
ALTER TABLE Employee
ADD CONSTRAINT Fk_Employee_AddressType
FOREIGN KEY (AddressTypeID)
REFERENCES AddressType (AddressTypeID)

Note: The column AddressTypeID should be a primary key in both tables. Read More...

Use of SET ANSI_NULLS ON in SQL SERVER stored procedures

As we discussed the use of NOCOUNT in previous post , we also use another statement with all the stored procedures in sql server. That is SET ANSI_NULLS ON/OFF

Which is useful when,
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

Syntax: SET ANSI_NULLS {ON | OFF}
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.

When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with non-null values in the column. Read More...

Need of SET NOCOUNT in SQL SERVER SPROCS

In every stored procedure we have define a common syntax is SET NOCOUNT ON/OFF. But most of us don't have any idea of why are we using it.
It is useful because of knowing the number of rows effected by some t-sql statement.

"Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results."

Syntax is: SET NOCOUNT {ON | OFF}

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. Read More...

Register IIS with ASP.NEt framework

Some times we forget to install IIS before installing ASP.NET frame work. In these cases, we encounter different problems because of not registering IIS with ASP>NET frame work. Please follow below steps to register IIS.

1. Open Command Prompt;
   
2. Move to your (.net) version directory, like C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>
3. type registerIIS exe file with option i and enable it. (For Windows2003 you must give enable option,For Windows2000/XP no need just option I is enough.)
   

Example: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -i –enable

Now just run IISRESET once to set everything correct. Read More...

Special behaviour of Aggregate or Set operation functions(SQL SERVER)

If you are calculating the average of employe salaries from employe table,
The aggregate functions (example AVG()) : Null values are ignored.

Note: 1. Null value is eliminated by an aggregate or other SET operations.
2. For Count,Count_Big(For number of items in a group -- returns type of bigint.), they don’t ignore NULL or duplicates. Remaining aggregate functions ignores NULL values.

Take care about these aggregation while analyzing the values.... Read More...

Sql Server error message "don't allow remote connections"

If your Sqlserver gives error like "don't allow remote connections" etc….
Or error like
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

Main cause for this error is, you are trying to connect to remote sqlserver, but on that server, remote connections option was disabled.

Do the following to solve the problem.
Start -- All Programs -- MicrosoftSqlServer2005 -- Configuration Tools -- SqlServer Surface Area Configuration -- Surface Area Configuration for Services and Connections -- choose your server(MSSQLServer or SQLEXPRESS) -- Database Engine -- Remote Connections -- Using both TCP/IP and namedpipes, click Apply then Ok.

Note: You must restart your server after change this configuration. Go to Administration Tools -- Services -- your server (MSSQLServer or SQLEXPRESS) -- restart/start. Read More...

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.

Go to IIS and check your virtual directory is configured as an application. If it is not converted as an application then create an application. Read More...

Not possible to debug and Integrated Authentication mode not enabled on your server

While Debugging VS2005 if error comes like Not possible to debug and Integrated Authentication mode not enabled on your server then
Go to IIS -- default website -- properties -- Directory Security tab -- Authentication and Access Control -- edit -- Enable Integrated Authentication mode. Read More...

unrecognized configuration section 'connectionStrings'

If you get the error like unrecognized configuration section 'connectionStrings' in ASP.NET application then the reason behind is your application is not running under the ASP>NET version 2.0. It is running under the ASP.NET version 1.1.4322.
To change this, please follow the steps below.

1. Go to IIS and select the folder Website
   
2. Select Default Websites
   
3. Right click on the default web sites
   
4. Select properties and choose Asp.net tab
5. Here select the ASP.NET version to 2.0

Note: To allow ASP.NET version 2.0 you need to do this. In IIS, Web service Extensions allow Asp.net v2.0.50727. Read More...