The tag has no usage guidance.

learn more… | top users | synonyms

3
votes
1answer
179 views

How to lower HEAP Fragmentation in SQL Server?

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a ALTER TABLE dbo.myTable REBUILD Funny enough, afterwards i had 20% fragmentation. There was no write ...
6
votes
3answers
466 views

Would a table benefit if it was a heap

I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always ...
0
votes
0answers
16 views

Postgres number of heap fetches greater than total rows

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
3
votes
1answer
70 views

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

I have a procedure that I use to find out how big is a table. select * from sys.objects where name = 'WMS_SLALoad' -- to get the object_id of my table -- in this case it is 2041058307 SELECT o....
15
votes
4answers
1k views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
1
vote
0answers
70 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
4
votes
1answer
1k views

Query shows SELECT 0

I'm looking through the queries being executed by a third party application. The application was flagged as being slow by some of our end-users. It seems to INSERT records into a HEAP, then DELETE ...
4
votes
1answer
453 views

Table Data Space Used Confusion

I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (...
1
vote
1answer
1k views

Moving a heap between filegroups

I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in ...
18
votes
3answers
4k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
3
votes
1answer
728 views

How do I automate converting heaps into clustered indexes?

I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index. What is the best automated method for converting these heaps? Since I'...
4
votes
2answers
773 views

Heap size too large for data present

A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over ...
6
votes
1answer
1k views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
4
votes
1answer
133 views

SQL Server - is it ok to store link table as a heap?

I'm trying to understand if it's a good practice to have non-clustered PK on link table: CREATE TABLE table1_table2(table1_id INT NOT NULL, table2_id INT NOT NULL, CONSTRAINT PK_T1_T2 PRIMARY KEY ...
12
votes
3answers
549 views

Are heaps considered an index structure or are they strictly a table structure without index?

Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505 Heaps: Are they considered an index structure or are they strictly a table structure without index?