Performance Tuning on SQL Server

Performance is one of most important factor at the time of the application developement. If your application or website is not responding fast to user queries then it's accepted by the users.

Here are the some tips to make fast retrival of data from sql server.

1) Create Index- Indexes are great way to improve your performance. It uniquely indetifys the each row in table.

2) Avoid Cursor- Cursors are time consuming so avoid cursors as much as you can do more work with queries.

3) Avoid Joins- Joins are also time consuming so avoid it.

4) Create Primary and Foreign keys in tables

5) Be sure your tables must have atleast one column that uniquely indetifys each rows

Continue Reading

SQL Server Index

Some Times we want to retrive data fastly without wasting time to retrive it. SQL Server Indexes can greatly help to make data retrival fast and provide quick access to tables.

Therer are four type of indexes in SQL Server 2000/2005.

1) Unique Key Index

2) Clustered Index

3) Non Clustered Index

4) Compsite Key Index

Unique Key Index:

As the name suggest this index does not allow duplicate values in rows. So it checks whether the data is unique or it Other wise it will give a error.

Clustered Index:

Clustered Index dictates the physical storage order of the data in the table. A table can have only one clustered index.

Non Clustered Index:

A non clustered index is a seperate index structure indepedent of the physical storage order of data. Sql Server 2000 alllowed 249 indexes per table.

Compsite Key Index:

As the name suggest in the composite key index two or more columns of tables to make a single unit. Sql Server 2000 allow 16 indexes per table.

Continue Reading

Creating ASP.NET 2.0 Website with Web 2.0 Standards

Web 2.0 is used mostly today because it provides great compatibility with most of the browsers.
So that a website can reach maximum audience. That's why the web 2.0 used the div tags rather then the other html standards.

An ASP.NET site also can be build by this standards. Stephen Walther has written great article at msdn with all the knowledge required to build ASP.NET Sites with web 2.0. Here is the link of that article.

http://msdn2.microsoft.com/en-us/library/aa479043.aspx

Continue Reading

Directly Binding Dataset with Datagrid View

With windows forms 2.0 you can directly bound the datagrid with dataset.

For example,

your dataset name is 'dt'

First fill your dataset with data.

Then select datagridview and create the columns and in the datamember property of each give the name of column you want to bind.

just write the following code.

For example you datagrid code is dg

dg.DataSource=dt;
dg.Refresh();

That's it you have bound the datagrid with dataset

Continue Reading

Capabilities Microsoft SQL Server,Microsoft ACCESS, MSDE

I have found a very good site with provide the comparison of Microsoft sql server, Microsoft and mdse capabilities on the basis of following.

1. Number of instances per server
2. Number of databases per instance / server
3. Number of objects per database
4. Number of users per database
5. Number of roles per database
6. Overall size of database (excluding logs)
7. Number of columns per table
8. Number of rows per table
9. Number of bytes per row
10. Number of columns per query
11. Number of tables per query Size of procedure / query
12. Number of input params per procedure / query
13. Size of SQL statement / batch Depth of sub query nesting
14. Number of indexes per table
15. Number of columns per index
16. Number of characters per object name
17. Number of concurrent user connections

here is the link for that site:

http://sqlserver2000.databases.aspfaq.com/what-are-the-capacities-of-access-sql-server-and-msde.html

Continue Reading

SQL Server 2005 Reporting Service.

There are lots tool available to develop reports. It has it's own advantage and disadvantage. One of most popular reporting tool is crystal report also has some limitation.

Microsoft has introduced a new way to develop the reports directly from sql server....SQL Reporting Service. It was there in sql server 2000 days but we have to add it as external service. Now with SQL Serve r2005 it is there with the sql server 2005 itself.

It supports all the features such as cross tab,sub reports etc...

Continue Reading

SQL Server 2005 Express Edition User Instances.

Sql Server Express edition is a small version of a sql server 2005 and that provides data storage capability up to 4 gb. It is easily ships with the each application so many users are using this version of sql server instead Microsoft access as a database for windows base application.
One of the problem using sql server instances is whenever you compile it will overwrite the existing database.

For Example, if you perform a insert operations from application and add new row to the database then again after compile the application. It will over right that database with older
database.

I have found a simple solutions to get rid of this problem. In solution browse to the database and select the database (.mdf files) then go to the property window. There is a property called 'Copy to output' select property and select 'Do not copy'.

Now when you compile the application it will not copy your older database to the bin directory.

Continue Reading

Display group header in every page of Crystal Report.NET

We have often used the crystal report as a reporting tool to create various reports.
We some time need our group header to display in each and every page. In crystal
reports right click crystal report-> select group expert and then select the group
you want to display in each page. and goto the options and check the the
checkbox >Repeat group header in each page.

That's it.. Your group header will display on the top of each page.

Continue Reading