Tag Info

New answers tagged

0

In a general sense, fragmentation is caused by: Data inserted out of order (the order of the index in question). Using ever-increasing values (e.g. an IDENTITY field) is one way to mitigate this as rows will always be created at the end of the index (assuming an ASC sort order). FILLFACTOR is another means of reducing fragmentation when not using an ...


1

Taken from the MSDN article on FOREIGN KEY Constraints A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the ...


1

To improve performance you may create a copy of data from server A on server C and update it on a schedule or use replication, if possible. Sometimes it works, if you don't need too 'live' data. With OPENQUERY whole dataset is transferred each time the query is executed.


3

You can't create an Indexed view on linked server table. According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx "The view must reference only base tables that are in the same database as the view."


1

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation. As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole ...


13

When a table has a clustered index, the index is the table data (otherwise you have a heap type table). A rebuild of the clustered index (any index in fact, but the space wouldn't be counted as "data" for a non-clustered index) will result in partially used pages being merged into a more full form. As you insert data into an index (clustered or otherwise) ...


5

When you rebuild an index, it literally places all of the data onto new pages. What I suspect happened is that you removed a lot of data prior to the rebuild, e.g. removed a column, updated a variable-width column to have less data, changed a fixed-width column size, or deleted a lot of rows. Either of these operations could leave a lot of empty space on ...


5

The sp_spaceused stored procedure is not examining the total culmulative size of the rows in the database. It is reporting the size of space allocated to hold that data in the cumulative size of the extents allocated for the data. If there is significant freespace available, such as from many deleted rows, then a rebuild of the clustered index would ...



Top 50 recent answers are included