Stack Overflow
Stack Overflow

Questions tagged [clustered-index]

A clustered index determines the physical order of data in a table.

A clustered index determines the physical order of data in a table. As the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values.

Source: MSDN (Using Clustered Indexes)

508 questions
4
votes
1 answer

view contains a convert that is imprecise or non-deterministic?

My query: IF OBJECT_ID ('vw_F_GWLVL_RAW', 'V') IS NOT NULL DROP VIEW vw_F_GWLVL_RAW ; GO CREATE VIEW vw_F_GWLVL_RAW WITH SCHEMABINDING AS SELECT S.SiteName, CAST(D.[Date] as Date) as [Date], CONVERT(CHAR(5),T.[Time]) as [Time], CAST(F.SampleValue…
user2264507
  • 101
  • 3
  • 9
4
votes
2 answers

does Firebird defrag? If so, like a clustered index?

I've seen a few (literally, only a few) links and nothing in the documentation that talks about clustering with Firebird, that it can be done. Then, I shot for the moon on this question CLUSTER command for Firebird?, but answerer told me that…
user1382306
4
votes
1 answer

Better insert performance on a table only with a clustered index or a table without any index on SqlServer 2008?

Note, I am not talking about a clustered index against a non-cluster index, I mean a clustered index comparing to no index at all for inserting performance. I saw lots of links as below said that, a clustered index has better performance against no…
Q123
  • 51
  • 1
  • 6
4
votes
2 answers

Does SQL Server jump leaves when using a composite clustered index?

Consider the following composite clustered index: CREATE UNIQUE CLUSTERED INDEX ix_mytable ON mytable(a, b) Obviously, a separate index on b will make searching for a particular value of b faster. However, if a separate index on b is not employed,…
richardtallent
  • 34,724
  • 14
  • 83
  • 123
4
votes
1 answer

Clustered and nonclustered index - SQL Server and Oracle?

Overview of clustered and nonclustered index - not DB specific (as I understand): Clustered Index: The physical order of data. As a result, a table can only have one clustered index. Nonclustered Index: One or more nonclustered indexes can be…
user2112730
  • 51
  • 2
  • 4
4
votes
4 answers

How are the table data stored when it has a clustered index

I have found umpteen posts which begin like Quite a lot of time I have come across people saying "Clustered Index Physically sorts the data inside the table based on the Clustered Index Keys". It's not true! Then such posts go on to describe how it…
SexyBeast
  • 7,913
  • 28
  • 108
  • 196
4
votes
2 answers

Non-clustered index and clustered index on the same column

I came across this post in Stackoverflow. The first answer mentions something like A clustered index has all the data for the table while a non clustered index only has the column + the location of the clustered index or the row if it is on a heap…
SexyBeast
  • 7,913
  • 28
  • 108
  • 196
4
votes
2 answers

Can I have a primary key without clustered index ? Also can I have multivalued clustered index?

Folks, I would like to understand the answer for the following questions: Can I have a primary key without clustered index ? ( I am aware that when we create primary key constraint on a column, it by default creates a clustered index. So in that…
Jasmine
  • 5,186
  • 16
  • 62
  • 114
4
votes
1 answer

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…
Chris Klepeis
  • 9,783
  • 16
  • 83
  • 149
3
votes
3 answers

What type of index in best for DATE type on Oracle?

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…
stic
  • 1,008
  • 3
  • 10
  • 23
3
votes
2 answers

Cannot create CLUSTERED INDEX on a View due to LEFT JOIN or subquery

I have created two options for a query used in a view which return the results I require. I need to rewrite either option so that it can be used in an Indexed View. Both fail when creating a unique clustered index on the view. The first one fails…
njb
  • 105
  • 1
  • 1
  • 7
3
votes
1 answer

Define what indexing data structure/algorithm SQL Server uses?

Is there a way to tell SQL Server what data structure/algorithm to use for it's indexing? In other words, can you tell it to use hashes, b-trees or a different base value for a b-tree to make it less memory intensive? My problem, is that I have a…
EhevuTov
  • 20,205
  • 16
  • 66
  • 71
3
votes
2 answers

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…
Mihai Lazar
  • 2,249
  • 2
  • 19
  • 27
3
votes
1 answer

Create nonclustered index with Entity Framework Core 5.0.4 with a GUID primary key

I have this entity class: public class StatusCode { public const String ClassName = nameof(StatusCode); [Key] public Guid UniqueID { get; set; } // Primary Key public Char Level { get; set; } //…
Greg Harris
  • 31
  • 2
3
votes
2 answers

What INCLUDE() function does when creating index in MS SQL Server?

What is the difference between creating an index using INCLUDE function vs not? What would be the difference between the following two indexes? CREATE NONCLUSTERED INDEX SomeName ON SomeTable ( ColumnA ,ColumnB ,ColumnC ,ColumnD …
user12310517
1 2 3
…
33 34