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

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

Why a Clustered table takes more space than a Heap Table

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

I'm using SQL Server 2005 buld 3080.

I have a table with no clustered index that return the followings results on sp_spaceused:

Rows: 47841318      Reserved :18015392 KB      Data : 7801184 KB      Index : 2489536 KB      Unused :7724672 KB

Tha data takes about 8 gb and there is a space lost of about 7.5 gb on the 17 gb reserved. I understans that because this is a HEAP table it cannot been defrag when running a rebuild index on it. Then I created a clustered index on that table to release unused space. Here is the new result of the sp_spaceused:

Rows: 47841318      Reserved: 15865744 KB      Data : 13140608 KB      Index: 2725000 KB      Unused: 136 KB

Now we can see that the unused space as been released with the defragmentation.

I don't understand why Data space has almost double it's value. The index size has grown up a little bit due to my new clustered index (Probably to store the root levels of the index). I expected that data remain approximatly the same.

If somebody could explain this I would really appreciate to understand.

View Complete Post

More Related Resource Links

space between two table headers


hi, here is the question. i have to display as follows:

Info 1                                               info 2

one                                                  one

two                                                  two

this is in my .ASPX file

here, i have info1 and info2 in <TH> tags. nw how do i get that space between those two.

rite now my out put is:

info1 info2

one                        one

two             &n

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

Table Spool takes up too much processing in execution plan


I have a query that is taking over 2 hours to run. When I look at the execution plan for a major segment of it, 75% of the cost is on three instances of "Table Spool" (25% each). What is "Table Spool" and how do you address it? The execution plan lists this as

 Table Spool
(Eager Spool)

There are actually 2 queries involved. One loops through a series of dates and calls the second one. All joining fields have indexes in their corresponding tables:

Calling Query:

    -- Process all quarters
        declare @lastQtrStart datetime
        declare @currentQtrStart datetime
        declare @currentQtrEnd datetime

        set @currentQtrStart = '1/1/2008'
        set @lastQtrStart = '1/1/2020'

        while @currentQtrStart < @LastQtrStart
                set @currentQtrEnd = dateadd(d , -1 , dateadd(MM , 3 , @currentQtrStart))

Sql Server Management Studio->Table Properties->Storage->Index Space


Hello Forum Members,

 I have a table with 10K rows which does not have any indexes.But when I select the table properties,click on storage I see index space has a value and is not null as i thought. Will sqlserver add deafult index space even if there are no indexes on the table?? Please advise.



SQL 2005 Inserting into Cluster vs. Non Clustered index table


All things being equal, when inserting data into cluster indexed table, assuming I am inserting in the order of clustered index, is there a difference how sql server will fill in the physical pages compared to if the inserted table had only non-clustered index? 

On a different subject, is the physical location of data on a page effects query performance?  If so, how?

Thanks in advance

Single insert/delete takes more than 5 sec on merge replicated table.


Dear experts,

Environment: MSSQL standard edition – 2008 / Windows server 2008 (64 bit)

I have lots of tables with merge publication (filtered) on windows 2008 server and it takes few seconds to insert/ delete single row on that database.


This is what execution plan shows.

Delete single row: 7 sec. It shows 47 queries.

Insert single row (2 columns): 4 sec. 35 queries.


What I can see it MSmerge_past_partition_mappings and MSmerge_current_partition_mappings table take 15%-30% of cost.


Please help to to resole this issue. I can send execution plan if anyone interested. 

Update takes too long - Very Large table update


I have 2 tables Fact_Bill(25 million rows) and Dim_Claim(2 million rows).Relevent columns: Fact_Bill(sk_Bill Int, Claim_Code Varchar,sk_Claim_Key Int) , Dim_Claim(sk_Claim_Key Int, Claim_Code varchar). All the mentioned 5 columns has independent non clustered index. I am doing a Update to Fact table in batches.

sk_Bill in Fact_Bill is an identity column

While (Taking 100,000 records by giving a condition to sk_Bill in fact)

        Update Fact_Bill Set sk_Claim_Key=C.sk_Claim_Key From Fact_Bill Inner Join Dim_Claim C On Fact_Bill.Claim_Code=C.Claim_COde Where Fact_Bill.sk_Bill =@batchRowcount and Fact_Bill.sk_Bill @batchRowCount - 100000.

My problem is the query is running very long , i stopped after 10 hours. Can anybody suggest some tips to increase the performance. Thanks

Bineesh Thomas

Creating a clustered index on a partioned table - need help understanding effect of filegroup placem


We have a large table ~40 Million rows and about 35 columns.  Things are starting to slow down and i am looking at partioning.  The table has some historical data and most of it can be ignored except for the current year, so I wanted to partion the data on the year column.  So I created a filegroup and ndf/ldf files for each year (ie: 2005,2006,2007, etc...)

The primary key is clustered on an identity column in this table.  When I went to recreate the table I got a warning message that the clustered index would be created on the primary file group as I specified, so i decided to do some research and found this article:


This leads me to believe that creating a clustered idex on the primary file group will prevent true partionting.  Is this true?  If so where should I store my clustered index as this seems to defeat the purpose of partioning then.

Also I was reading something that the clustered index should contain the key column that the data is partioned on, so it should be something like (PriKeyCol, theYear) ? as opposed to just the (PriKeyCol)

Thanks for any help,

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

RadioButton Groups, Table Rows And NamingContainers

(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

Building a 3-Tier App with Silverlight 3, .NET RIA Services, and Azure Table Storage

Silverlight 3, .NET RIA Services, and Windows Azure Services Platform makes a 3-tier Cloud application easier to build: Silverlight 3 as presentation tier, .NET RIA services as the business logic and data access tier, and Windows Azure Table as the data storage tier. The sample application in this article demonstrates the architecture with a simple Survey application with all these technologies working together from Windows Azure.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Import XML File to SQL Table

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Adding the table row below a particular row.


What is the best method in jQuery to add an additional row to a table as the row below a particular row?

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