What are the differences between a clustered and a non-clustered index?
What are the differences between a clustered and a non-clustered index? ...
clustered-index
Indexing vs. no indexing when inserting records
I have a few questions about whether or not it would be best to not use indexing. BACKGROUND: My records have a timestamp attribute, and the records will be inserted in order of their timestamps (i.e., inserted chronologically). QUESTIONS: If I DON'T use indexing is it typical for the database to insert the records in the order that ...
How important is indexing and clustered indexing to database performance?
There have been several questions recently about database indexing and clustered indexing and it has been kind of new to me until the last couple weeks. I was wondering how important it is and what kind of performance gains can be expected from creating them. Edit: What is usually the best type of fields to look at when putting in a clu...
Smart choice for primary key and clustered index on a table in SQL 2005 to boost performance of selecting single record or multiple records
EDIT: I have added "Slug" column to address performance issues on specific record selection. I have following columns in my table. Id Int - Primary key (identity, clustered by default) Slug varchar(100) ... EntryDate DateTime Most of the time, I'm ordering the select statement by EntryDate like below. Select T.Id, T.Slug, ..., T.Ent...
Is this a bad indexing strategy for a table?
The table in question is part of a database that a vendor's software uses on our network. The table contains metadata about files. The schema of the table is as follows Metadata ResultID (PK, int, not null) MappedFieldname (char(50), not null) Fieldname (PK, char(50), not null) Fieldvalue (text, null) There is a clustered index ...
Improving performance of cluster index GUID primary key
Hi, I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient. ...
Database Indexes
I need to develop a "naive" implementation of database indexes for use in a distributed environment. I know almost nothing about the subject, and I'm a bit pressured by time. I would love to hear some opinions, examples and algorithms on the subject. I'd like to be able to have a mental representation of what I need to implement. EDIT...
SQL Server physical data layout for tables without indexes
I'm curious to see if anyone knows how is the data physically arranged in tables that don't have an index (not even a PK). In this question the OP claims it is taking a long time to drop a clustered PK. I know that the clustered index is the data itself, but what would be the new data arrangement that explains the long processing time? ...
Best way to change clustered index (PK) in SQL 2005
I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] I want to remove this clustered index PK an...
Is this execution plan a motivation for re thinking my primary keys
When I entered my current (employer's) company a new database schema was designed and will be the base of a lot of the future tools that are/will be created. With my limited SQL knowledge I think the table is rather well designed. My only concern is that almost every table has a multy-part primary key. Every table has at least a Customer...
Performance of non-numeric indexes
If I use a nvarchar(n) column as a clustered index on a SQL Server database, am I going to suffer a significant performance hit compared to a numeric (int) index? Also how does the performance of compound indexes compare? ...
SQL Server Performance and clustered index values
I have a table myTable with a unique clustered index myId with fill factor 100% Its an integer, starting at zero (but its not an identity column for the table) I need to add a new type of row to the table. It might be nice if I could distinguish these rows by using negative values of myId. Would having negative values incur extra page s...
Why would you have a clustered composite index when none of the fields are used together?
In a legacy database (SQL Server 2000), we have a clustered index that looks like this: CREATE CLUSTERED INDEX [IX_usr] ON [dbo].[usr] ( [uid] ASC, [ssn] ASC, [lname] ASC ) The thing is, as far as I know none of these fields are used together in a WHERE clause. Nor is there really any reason to use any of them together. ...
Changing newid() to newsequentialid() on an existing table
Hi, At the moment we have a number of tables that are using newid() on the primary key. This is causing large amounts of fragmentation. So I would like to change the column to use newsequentialid() instead. I imagine that the existing data will remain quite fragmented but the new data will be less fragmented. This would imply that I sho...
What type of index in best for DATE type on Oracle?
Hi, Basing on your experience with Oracle, what will be the best type and settings for index that you would set on a column of DATE type? I don't necessarily need to go for partitioned index. It is a logging kind of table. You don't really care about unique id as a primary key (in fact date is close enough to be uniques most of the ...
SQL Server 2005 Clustered Index Query Speed
Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries. While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example If this was the old query: SELECT ... FROM my...
sql server clustered index on a view
hey everyone, Im trying to create a view out of quite a complex select query and it wont let me put a clustered index on it because i have to use subqueries and some aggregate functions. I have to get a clustered index on it otherwise the queries that use the view will take forever. Apparently sql server will only store the result set ...
Clustered composite primary key sort order on the date column
We have a sales table that contains about 88.5m rows of data and has a composite primary key listed below, it also has a few additional columns with informational data: RevenueCentreID int(Asc) DateOfSale smaldatetime(Asc) SaleItemID int(Asc) SaleTypeID, int(Asc) All four the above columns also has its own un-clustered index sorted i...
SQL Server: Clustered index on datetime, ASC or DESC
If I have an SQL Server table with a clustered index on a datetime field, that is set to DateTime.Now (from C#) before inserts, should the index be ascending or descending to avoid reorganization of the table? Thanks. ...
Why is there a scan on my clustered index?
SQL 2000 The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID The SIGN table has a foreing key to the NED table SIGN.SignID to NED.SignID The RowID and SignID are clustered primary keys that are guids (not my choice) The WHERE clause is: FROM [SIGN] A INNER JOIN NED N ON A.SIGNID = N.SIGNID INNER JOIN Wi...