ASP.Net Tips

When using ADO.NET to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened.

SQL Server Tips

Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

Load text file into SQL Server

Recently i came accross a situation where i have to load the text file data into SQL Server with simple code.
i had several options to do this but i went to the basics of SQL Server. i.e. There is one feature which was started from 7.0 i.e. BULK INSERT.

look at the sample data
-----------------------
jhonson,glen,glen@ge.com
nicole,kid,kid@nicole.com
chai,nee,nee@usa.com

BULK INSERT tmpStList FROM 'c:\data.txt' WITH (FIELDTERMINATOR = ',')

Thats it. Its so simple to do it

New Version of ODP.NET

Oracle just released its latest version of ODP.NET. This version, for Oracle 11g, provides enhancements and the much anticipated Instant Client and User Defined Types (UTDs). One big issue in the past when you were working with ODP.NET for your ASP.NET applications was that you had to install a tremendously large Oracle client application that took some time. Now with the instant client, you simply XCopy your client to the server you want it at. Very easy. UDTs was also another feature that allows you to pass custom types into the database and step away from the need to use numerous ref cursors.

ODP.NET includes the ASP.NET providers that you might be looking for including:

Membership Provider
Role Provider
Profile Provider
Session State Provider
Site Map Provider
Web Parts Personalization Provider
Web Event Provider
Cache Dependency Provider

You can look to download this version at http://www.oracle.com/technology/tech/windows/odpnet/index.html

Except in SQL Server

This is basically to return all the records from one result set where there is no matching in another table. This looks very similar to “Outer join” but join does horizontally and EXCEPT does vertically.

Let’s look at the example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
EXCEPT
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

With the above query we are fetching only the records which are in Employee but not in Employee_HIST.

Intersection in SQL Server

As you know this is basically to combine multiple result sets into single to fetch the common records in multiple result sets. Inner join finds common rows horizontally, while an INTERSECT finds common rows vertically.

Let’s look at the example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
INTERSECT
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

We are combining the results from Employee and Employee_HIST table. When you use INTERSECT operator it will give you the common records from both the tables meaning the records that are duplicated in both the tables.

Union in SQL Server

As you know this is basically to combine multiple result sets into single with or without duplicate. The union operation is different from a join, In Mathematical terms; a union is addition, whereas a join is multiplication. Instead of extending a row horizontally as a join would, the union combines multiple result sets into a single result set.

Let’s look at the example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

We are combining the results from Employee and Employee_HIST table. When you use UNION operator it will eliminate the duplicate records meaning that if the same record is in both the tables then it will pickup the record from only one table.

Let’s look at another example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION ALL
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

Here also we are combining the results from Employee and Employee_HIST table. When you use UNION ALL operator it will not eliminate the duplicate records meaning if you have the same record in both tables then in the final output you will see both the records.

UNION is always creates the performance issue. So when ever you are using UNION use it very judiciously. If you are not sure of what kind of data you have then you can use UNION. If you know you don’t have any duplicate records for which you want to combine the results then use UNION ALL.

Find out SQL Server Version

There are several ways to find out the SQL Server Version, and this is one of the easiest way.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Cannot drop the table

The DROP TABLE command removes a table from the specified database. The specified table cannot be removed because it does not exist in the system catalog.

There could be several reasons,

Make sure the table name is correct.

Make sure you have sufficient access permission to drop the specified table.

If NOT then above ask your DBA to investigate the problem.

Primary key can't be created

The primary key is used to relate the table to foreign keys in other tables. All columns participating in a primary key must contain defined values other than NULL.

To create a primary key on the selected column, first clear the Nulls from that column.

My New Rank in C-Sharpcorner

In June, I spent most of the time in SQL Server Central community by posting answers because of that i was in TOP 5 in Montly TOP contributors. This was a great experience. Now In July, I spent most of the time in C-Sharpcorner, by posting answers to mainly C#, ASP.Net questions and other .Net Related questions.

As always Hard work pays, I am the MOST VALUABLE CONTRIBUTOR (MVC) in C# Corner. This is one more milestone in my Career.....