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

Top 5 Contributors of the Month
Imran Ghani
Sohaib Khalid
Post New Web Links

big sort when create an index

Posted By:      Posted Date: November 20, 2010    Points: 0   Category :Sql Server

I have a table of size 70G and the server has 128G memory. I will create a clustered index on the table, so a big sort will happen, if the memory allocated to the sort is not enough to do the sort in memory, and the sql server will spill the data to disk. The question is, is sql server so smart that it can allocate sufficient memory for the sort and don't cause the write to disk? If not, is there any hint or parameter to tell sql server to allocate 90G sort memory?


View Complete Post

More Related Resource Links

how to create index for a column


Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.


What is the meaning of Ascending\descending sort order of a column within an index

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

cannot create an index on a view

Hi All, Can I create an index on the following view? Create view myview with schemabidning as select id, date, value from tablea a right join (select id,min (date) as mindate, value from tablea ) b on a.id = b.id and a.date = b.mindate GO CREATE UNIQUE CLUSTERED INDEX IDX_1 ON MYVIEW(ID)     Right now i am getting an error.   Cannot create index on view "myview" because it references derived table "B" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view. shamen

Unable to create index for view

Hi, I'm including the script for my ProductCategories table but I think it might be very beneficial for you to understand what I'm doing first: As the name implies, the ProducttCategories table is where I store all product categories for an ecommerce site. Furthermore, the CategoryHierarchy column uses the HierarchyID datatype in SQL Server 2008 so that I can have a hierarchy and make suggestions to users who are looking for a product in a category along with all its child categories. Also, in order to make the site as user friendly as possible, I've implemented friendly URLs. Therefore, I need to do a reverse look up when someone requests: Electronics-DigitalCameras-DigitalSLRs to figure out the CategoryID (int) for the Digital SLR's category. I wanted to create an index in the database to make sure these look ups would have pretty good performance and that's where I got stuck. First, I got a message that told me that I couldn't create an index because my view was not bound to schema. And when I tried to do that, I got another error saying "Cannot schema bind view 'dbo.vwProductCategoriesAndPathsOrdered2' because name 'ProductCategories' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself." The view simply creates a category path that looks like this: Electronics > Digital Cameras > Digital SLRs I'd

How to create nonclustered index which uses function on column?

Hi, Is there any way I can perform following. The ID column is of varchar type.   CREATE NONCLUSTERED INDEX idxID ON Table_1(CAST(ID AS BIGINT) );   Regards, Jigs

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.....

create an index on just the date part of a datetime field


Good Morning

is it possible to create an index on a datetime field that just looks at the date part,

as I am constantly querying the table, using a where clause like this


or is there an more efficient method of achieving the same result?







Create Facebook sort of thing in ASP.NET


Actually what i wanted to create is user can upload Videos , Images and Pdf . Just like in Facebook . I want these videos/Images and pdf to saved in server machine . Are thier any tools in ASP.NET to provide me this ? Thanks 

create index on view


HI Guys

Can any one please tell me how to create Index on this view

Create VIEW [dbo].[Vw_Fact_GLTransactions]








Need to know about index how to create and how it usefull



I have two tables order and order details in which i have some 20000 orderNo in order table and some 80000 data in order detail table.. so i need to set index.. can any body let me know how index work and when to create index and why it usefull

create index in subscribe table

Can I create a new index in the subscribe table? Thanks.

Sort Array along with its Corresponding index



  I have a one-d Array named zz containing {5,3,8,1,9,6}. Current order of indices of the elements is : 0,1,2,3,4,5. 

Now I want to sort the array z in ascending order in such a way that its corresponding indices also sort according to its respective elements.

Ex: Elts in Ascending order : 1,3,5,6,8,9

and Respective indices will be : 3,1,0,5,2,4.

Another Ex: What "Additions" should i make to the following code, if the Array Element repeats ??  Epected Oreder of Indices : 1,0,4,3,2

int[] A = new int[6] { 3, 2, 8, 5, 3 };  //{ 5, 3, 8, 1, 9, 6 };
            int[] B = new int[6];
            int[] index = new int[6];
            int flag = 0;
            Console.WriteLine("Array A:");
            for (int i = 0; i < A.Length; i++)

Howto create an index on queue_messages table


SQL 2005 St. Edition.

I have an application that use service-broker. I noticed that there is a queue_messages_xxxx table that needs an index (I found it using Performance Dashboard report).

How can I add this index ? As far as I know it is not possible to manipulate internal objects, is it ? However, how can I speed up application better indexing that internal object ?


Create index in create table syntax



What is the syntax for create table with some index?

(Create table ...

   INDEX X () ... what should I write here?)

Also, where is a good document for all sql-server syntax.

on http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx I didn't find any. clue for the question above.

Thanks :)

How to create 3 tier application using LINQ

As you know that in 3 tier architecture there are three layers

User interface layer. (Is our Form in Windows application and .aspx page in Web application)
Data Access layer. (Which provides interface between Business logic layer and Database)
Business Logic layer.(Which stores your application logic)

Create ToolTip for GridView Header

The following allows you to add a tooltip to the header links of your GridView, a feature that is lacking.

Create And Run Excel Macro At Runtime in C#

You've been tasked with writing a Windows Form application using the .NET Framework that interacts with Microsoft Excel. If you are new to working with Excel in .NET and are having trouble learning how to format certain cells or cell ranges, then this little tip will come in handy. Rather than scour the internet hoping to find the correct set of constants or method syntax for formatting your spreadsheet, use the built in tools in Excel to write the familiar VBA code for you. Then, you can paste the VBA code generated by Excel into the sample C# code below and run it dynamically at runtime.
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