.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Computed Columns - Index and Performance

Posted By: Amit Mehra     Posted Date: September 03, 2010    Points: 2   Category :Sql Server
This article summarized all the articles related to computed columns.

In this article today, we will see how we can get better performance using computed column. Here are few steps which we are going to follow. First, we will create regular table and populate with some data. Once data is populated we will try to query the data

View Complete Post

More Related Resource Links

Computed Columns in Table Variables or Table Types

I was just looking for confirmation that computed columns can or cannot be used in a @Table Variable or User-Defined Table Type. I'm trying this: declare @testtable as table(  [testcol] as case when [testcol] = '' then 'a' else 'b' end,  [testcol2] varchar(100) ); insert into @testtable select '','someval' insert into @testtable select 'c','someval2' select * from @testtable It all parses fine, but during the first insert an error is returned Msg 402, Level 16, State 1, Line 2 The data types void type and varchar are incompatible in the equal to operator. Thank you, Paul

How to create Index on Inline View columns



i faced one problem when worked with Inline views.

For some reasons, i used Inline views in our project. Is there any possiblity to create Index on InlineViews. I will expain more clearly using my code.

select (Row_Number() over (order by Net)), * from (select ID,Fname,LName,Sal, Sal + 1000 as Net from EMP) cc where Net between 10000 and 20000

Here, ID,Fname,Lname,sal are the table columns and Net is the newly cretaed column in Inline view. I know how to create Index on table columns but i am getting struck how to create Index on Inline View columns means here i need to create index on Net column.

Thanks in Advance.....

Compare to Index with Included Columns, what's the advantage of composite index


I found lots of blog talk about the advantage of [include] index, for example, to avoid the Size Limits. But what's the advantage of composite index?

I created two tables with the same structure and data. table(a,b,c), and then created two indexes for each table index C_index on table1 (b,c)  and index I_index on table2 (b) include (C). But I got the same execution plan all the time even if the indexes are different. For example:

select B, C from table

select c from table where b =**

select b from table where c=**

select a from table where b=** and c=**

select a from table where c=** and b=**

... ...

i konw that the key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level. But I cannot find an example to show the disadvantage compare to composite index. I must be missing something, but what's that?




improve performance of gird on page index change


i am using data gridview asp.net  control and i bind it dynamically, gird displayed 80,000 thousands records so i am using paging techniques,on each page gird display 10 record so when i am change page each time gird should filled 80.000 thousands records so how could i bind gird from pageIndex,pageSize rather than bind it whole records

my code is below

BAL,DAL techniques so Method for data gird binding is following

private void FillGrdTendersDeptWise()
        tender objTdr = new tender();
            DataTable dt = new DataTable();
            dt = objTdr.GetAllTenderDeptWise();

            grdTender.DataSource = dt;

        catch { }

page index change

SQL Server 2008 - why is performance WORSE with xml index?

It's crazy, but query performance is about 50% WORSE after I add a primary XML index to my xml field. 

Here's what I'm doing. I have a table containing an XML field ActivityStepLog (contains LogData, XML, UNTYPED)

I generate sample data to insert into this table by running the following
INSERT INTO dbo.ActivityStepLog (
 LogGUID = newid()
 ,LogContextID = newid()
 ,LogTypeID = 2
 ,LogSourceName = 'test test test'
 ,LogContent = (SELECT top 1 * FROM ##SampleData SampleData1 where DecisionLogID = SampleData.DecisionLogID FOR XML AUTO, ELEMENTS, ROOT('BusinessRule') )
 ,LogDate = current_timestamp
 ,CreateDate = current_timestamp
 ,CreatedBy = 'test create by'
from ##SampleData SampleData
SampleData has 100,000 rows, I run it in a loop 5 times so end up with 500,000 rows. The LogContent field has data such as the following:

Very important SQL performance queries - index building


Hello i need several queries.

1- auto index suggestion query. dont tell me sql server profiler since it always fails because my sql server processes about 10000 query per second maybe more

2- performance degreading index finder query

3- duplicate index finder query

4- fragmantation rebuild query


thank you

Browser based Pokemon MMORPG Pokemon Craft Developer Used asp.net 4.0 routing at it's Pokedex

Xml Index performance



I'm trying to understand why and how XQuery queries can be optimized.
I've found a case that I find quite strange:

I've got property,value,path and primary index on my xml column, and also a schema so that the integer values are typed

This query is slow'ish and needs a nested join.
(the [1] on the property path seems to cause this)



from mytable
where (Data

Index on persisted computed column not functioning as expected


In the below example, I'd expect both queries to be seeks against their appropriate index, however the engine doesn't seem to be able to handle ISNULL appropriately.  I've changed my computed columns to be non nullable by actually specifying not null instead of using isnull.  Any feedback on this strange behavior would be appreciated, as I have always thought ISNULL was the appropriate way to make a computed column non-nullable until I discovered not null actually functions, but is not exposed through design view mode.


 title VARCHAR(250) NOT NULL,
 isActive BIT NOT NULL,

ASP.NET Best Practices for High Performance Applications

This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

High-Performance .NET Application Development & Architecture

It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

How to improve the performance of ASP.NET MVC web applications

It is an in depth analysis of a Digg-like site and how it went from serving (on a test machine) 6 req/sec to 390 req/sec.

The biggest gain, 74 req/sec to 390 req/sec happened when he introduced data caching and another 25 req/sec where gained when he introduced the compilation of LINQ queries.

High Performance ASP.NET - Speeding Up ASP.NET Pages

have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

Improving Application Performance in .Net

Application performance has always been a concern for Web Application developers. This article contains guidelines and tips for maximizing application performance in ASP.NET.

Tips to Improve Your ASP.NET Web site performance

ASP.NET has been developed with lots of features which makes developer life easy. Start from ViewState, PostBack, Server Controls , User Controls and etc. All of them can make your web development experience fun and easy, but improper use of them can cause your site performance slowdown. Most of the controls generate overhead to the client and server. Therefore in this article, I I will list out about things you can do and don't to improve your ASP.NET website performance.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend