SQL SERVER 2000 VS 2005

DIFFERENCES BETWEEN SQL 2000 & SQL 2005:
I am a C# Asp.Net Developer with little knowledge in SQL. I myself had a question what is the difference between SQL server 2000 & SQL 2005. Not only me , more Developer have the same Question . So i analysed surffing in the net mostly from SQLServerCentral an Excellant site for SQL, i have provided a major differnces in tabular form.

First we can discuss the Adminsitrative Differences one by one

The Development differences are more in SQL 2005 which make the major differnces & advantages than SQL Server 2000.

Now we can easily answer the questions regarding the differences in SQL 2000 & SQL 2005

SQL Server Connection Strings - An overview

SQL Server Connection Strings :
Some common and not so common connection strings for the .NET SqlConnection object.
The article includes .NET sample code and some tricks to increase the supportability of your application.

Trusted Authentication:
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;

Trusted authentication uses the security credentials of the current user to make the connection to SQL Server. SQL Server uses Windows (or Active Directory) to validate the current user. ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2.
ServerName can also be expressed as an IP address.
SSPI stands for Security Support Provider Interface (in you were curious).

SQL Server Security Authentication:
Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;
In SQL Server authentication SQL Server stores the username and password.
ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2. ServerName can also be expressed as an IP address.

Setting the Application Name:
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; Application Name=MyAppName;
I often set the Application Name when I construct connections strings. Whatever text you assign to Application Name will appear in a couple of different places:

• It will be displayed in Profiler under the Application Name column.
• It will be shown in the output of sp_who2 in the Program Name column.
• It will be shown in the Activity Monitor in the Application column.

You can get to the Activity Monitor in SQL Server Management Studio by
Management -> Activity Monitor.

• It will appear in the program_name column if you select from master.dbo.sysprocesses (for SQL Server 2000)
• It will appear int he program_name column if you select from sys.dm_exec_sessions (for SQL Server 2005 and later).

Setting the application name makes it very easy to find out what applications are issuing particular SQL statements against my database.
Setting the application name can also lead to an increase in the number of connections to your SQL Server. Each client that uses connection pooling will create one pool inside each application per unique connection string.
If you use multiple application names you have the possibility to increase the number of pools and thus the number of connections to SQL Server.

I've always found it more beneficial to have the application name than to have a few less connections to my database servers.

Using MARS (Multiple Active Result Sets):
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; MultipleActiveResultSets=True;
If you want to use MARS you'll need to enable it in the connection string.

Sample .NET code:
There are two common ways to create a connection string in .NET.
The first is to use an explicit connection string.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=L40; Initial Catalog=master; Integrated Security=SSPI;";

The second is to use the Connection String Builder object in .NET to construct a connection string.
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "L40";
csb.InitialCatalog = "master";
csb.IntegratedSecurity = true;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = csb.ToString();

Ref: http://www.sqlteam.com/article/sql-server-connection-string

C# Generics with examples

Introduction:
Parametric Polymorphism is a well-established programming language feature. Generics offers this feature to C#.
The best way to understand generics is to study some C# code that would benefit from generics. The code stated below is about a simple Stack class with two methods:
Push () and Pop ().
First, without using generics example you can get a clear idea about two issues:
a) Boxing and unboxing overhead and
b) No strong type information at compile type.
After that the same Stack class with the use of generics explains how these two issues are solved.
Example Code
Code without using generics:
public class Stack
{
object[] store;
int size;
public void Push(object x)
{...}
public object Pop()
{...}
}
Boxing and unboxing overhead:
You can push a value of any type onto a stack. To retrieve, the result of the Pop method must be explicitly cast back. For example if an integer passed to the Push method, it is automatically boxed. While retrieving, it must be unboxed with an explicit type cast.

Stack stack = new Stack();
stack.Push(3);
int i = (int)stack.Pop(); //unboxing with explicit int casting

Such boxing and unboxing operations add performance overhead since they involve dynamic memory allocations and run-time type checks.
No strong Type information at Compile Time

Another issue with the Stack class:
It is not possible to enforce the kind of data placed on a stack.
For example, a string can be pushed on a stack and then accidentally cast to the wrong type like integer after it is retrieved:

Stack stack = new Stack();
stack.Push("SomeName");
//pushing the string
int i = (int)stack.Pop();
//run-time exception will be thrown at this point
The above code is technically correct and you will not get any compile time error. The problem does not become visible until the code is executed; at that point an InvalidCastException is thrown.

Code with generics
In C# with generics, you declare class Stack {...}, where T is the type parameter.
Within class Stack you can use T as if it were a type. You can create a Stack as Integer by declaring
Stack or Stack as Customer object by declaring
Stack.
Simply your type arguments get substituted for the type parameter. All of the Ts become ints or Customers, you don't have to downcast, and there is strong type checking everywhere.
public class Stack OpenTag(T)CloseTag
{
// items are of type T, which is kown when you create the object
T[] items;
int count;
public void Push(T item) {...}
//type of method pop will be decided when you creat the object
public T Pop() {...}
}
In the following example, int is given as the type argument for T:

Stack OpenTag Int CloseTag stack = new Stack OpenTag Int CloseTag ();
stack.Push(3);
int i = stack.Pop();

The Stack type is called a constructed type. In the Stack type, every occurrence of T is replaced with the type argument int. The Push and Pop methods of a Stack operate on int values, making it a compile-time error to push values of other types onto the stack, and eliminating the need to explicitly cast values back to their original type when they are retrieved.
You can use parameterization not only for classes but also for interfaces, structs, methods and delegates.
//For Interfaces interface IComparable OpenTag T CloseTag
//for structs struct HashBucket OpenTag T CloseTag
//for methods static void Reverse OpenTag T CloseTag (T[] arr)
//for delegates delegate void Action OpenTag T CloseTag (T arg)

Inside the CLR
When you compile StackOpenTag T CloseTag , or any other generic type, it compiles down to IL and metadata just like any normal type. The IL and metadata contains additional information that knows there's a type parameter. This means you have the type information at compile time.
Implementation of parametric polymorphism can be done in two ways
1. Code Specialization: Specializing the code for each instantiation
2. Code sharing: Generating common code for all instantiations.

The C# implementation of generics uses both code specialization and code sharing as explained below.
At runtime, when your application makes its first reference to StackOpenTag T CloseTag , the system looks to see if anyone already asked for Stack . If not, it feeds into the JIT the IL and metadata for Stack and the type argument int.

The .NET Common Language Runtime creates a specialized copy of the native code for each generic type instantiation with a value type, but shares a single copy of the native code for all reference types (since, at the native code level, references are just pointers with the same representation).

In other words, for instantiations those are value types: such as Stack OpenTag int CloseTag , Stack OpenTag Long CloseTag , Stack OpenTag Double CloseTag , Stack OpenTag Float CloseTag, CLR creates a unique copy of the executable native code.

So StackOpenTag int CloseTag gets its own code. Stack OpenTag Long CloseTag gets its own code. Stack OpenTag Float CloseTag gets its own code. Stack OpenTag int CloseTag uses 32 bits and Stack OpenTag Long CloseTag uses 64 bits. While reference types, Stack OpenTag Dog CloseTag is different from Stack OpenTag Cat CloseTag , but they actually share all the same method code and both are 32-bit pointers.

This code sharing avoids code bloat and gives better performance.
To support generics, Microsoft did some changes to CLR, metadata, type-loader,language compilers, IL instructions and so on for the next release of Visual Studio.NET(code named Whidbey).

What you can get with Generics
Generics can make the C# code more efficient, type-safe and maintainable.
Efficiency: Following points states that how performance is boosted.
Instantiations of parameterized classes are loaded dynamically and the code for their methods is generated on demand [Just in Time].
Where ever possible, compiled code and data representations are shared between different instantiations.
Due to type specialization, the implementation never needs to box values of primitive types.

Safety: Strong type checking at compile time, hence more bugs caught at compile time itself.
Maintainability: Maintainability is achieved with fewer explicit conversions between data types and code with generics improves clarity and expressively.

Conclusion
Generics gives better performance, type safety and clarity to the C# programs. Generics will increase program reliability by adding strong type checking. Learning how to use generics is straightforward, hopefully this article has inspired you to look deeper into how you can use them.

How to write property for CheckBox control in Asp.Net 2.0

Say chActive is the checkBox ,

private string Active
{
get
{
return chkActive.Checked ? "Y" : "N";
}

set
{
chkActive.Checked = (string.Compare(value, "Y", true) == 0 ? true : false);
}
}
Thus a check box property has been written

SQL server Count Aggregate

Count Aggregate:

Just declare a table, say

DECLARE @t table

(

id int identity(1,1),

value varchar(50)

)

Insert some values,

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values('1')

INSERT INTO @t values('2')

INSERT INTO @t values('3')

What is the output?

SELECT count(id), count(value) FROM @t

It's 6,3 actually because, The Count aggregate does not include NULL values, so the count will be 3 rows for the 2nd column