Cluster Index On 2 Columns Order
May 29, 2007
I have 2 columns in a table namely ColA and ColB.all DML operations are through views n every view has
Where clause i.e where ColA=€?€? with check option .
All most all my DML queries are using where clause on ColB
Where ColB=€?€?
Now my question is I have a clusted index on both ColA and ColB.in which order I have to create cluster index .
i.e ColA ASC,ColB ASC or ColB ASC,ColA ASC.
Is there any performance gain we can achieve with their order
View 1 Replies
ADVERTISEMENT
Jun 19, 2013
I want to make an index with the following columns, actually together they constitute the PK of the table so the index is created automatically.
The question is witch sort order is the best to have if I want to fetch all rows for one date and one resource? And why?
WHERE Resource = "Car 1" AND Date = "2013-03-03"
Resource, Date, Time
Or
Date, Time, Resource
Below is an example with tree resources, but in reality there can be a lot more and also years of dates.
Car 1, 2013-03-03, 10.00
Car 1, 2013-03-03, 11.00
Car 1, 2013-03-03, 12.00
Car 1, 2013-03-04, 10.00
[Code] ......
View 9 Replies
View Related
May 20, 2008
Is there a dynamic management view or system procedure which I can use to find out what columns are in an index, what columns are as an INCLUDE in the index and whether or not the column(s) are ascending or descending. This is excluding the utilities I already know about below:
sys.indexes
sys.index_columns
sp_helpindex
dm_db_index_physical_stats
dm_db_index_operational_stats
I only ask because it is a pain to look through the sys.indexes and sys.index_columns tables every time I want to know about what columns are in the index created. I also know that scripting the index would give me the information I need but there must be a better way.
Many Thanks
View 8 Replies
View Related
Sep 16, 2015
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
View 4 Replies
View Related
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id      stats_name                                    Â
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id      index_name                                    Â
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
May 20, 2003
Hi all,
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
Deepak
View 2 Replies
View Related
Jul 26, 2015
Wanted to know about difference between cluster and non cluster index with example.
When to use cluster index and non cluster index .
View 3 Replies
View Related
Mar 5, 2015
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
View 1 Replies
View Related
Apr 24, 2007
Hi, all experts here,
Thanks a lot for your kind attention.
As I am creating the non-clustered indexes for the tables, I dont quite understand how dose it really matter to put the columns in the index key columns or put them into the included columns of the index?
I am really confused about that and I am looking forward to hearing from you and thank you very much again for your advices and help.
With best regards,
Yours sincerely,
View 4 Replies
View Related
Mar 30, 2001
I have inherited a db with no clustered indexes, but composite indexes on each table. The DB has about 500 tables(1-5 gig when fully loaded), but on a given session a user might only insert 1 or 2 records in 6-7 of the tables, with about 20-30 current users 8-5PM. THe DB is kinda of a mix between low-end OLTP and DSS. For space and performance I was going to drop the existing indexes and replace them with clustered indexes. Any opinions or gotchas appreciated
View 1 Replies
View Related
Oct 9, 2007
Hi All,
One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables
Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table
As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data
I tried following options with vain
1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also
Pls give me any other solution or suggestion in this regard
with Thanks & Regards
Bhaskara
View 8 Replies
View Related
Jun 2, 2014
We have few tables where we do truncate load or only do insert activities , why do the cluster index get fragmented very often to > 80%?
View 9 Replies
View Related
Sep 24, 2015
I have a table which has cluster index on col1 column. If i insert 10 into my table what would be cluster index key value?Is it going to be 10 as well? How do i get cluster index key value?
View 5 Replies
View Related
Jun 11, 2015
Dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Is it correct way to remove index if dead lock is coming because of index?
View 3 Replies
View Related
Sep 7, 2007
As we all know that cluster index physically sort the table. Now what will happen if I insert a new row in the table which voilating the sort rule of table.
Will it inserte at the end of table or table is reordered as cluster index always keep table in physically sorted . Please cleaar my doubt.
View 3 Replies
View Related
Apr 15, 2003
Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.
View 1 Replies
View Related
Feb 29, 2008
I have a table "Client" that has two columns: "ClientID" and "ProductID". I created on clustered index on ClientID and when I opened the table in the management studio, I saw the table was in the order of ClientID.
Then I added another non-clustered index on ProductID. When I open the table again, it is in the order of ProductID. Shouldn't the table always be in the order of clustered index? Non-clustered index should be a structure outside of the table itself? Did I do anything wrong?
Thanks for any hint.
View 17 Replies
View Related
Jun 9, 2006
Hi,I created a composite index (lastname, firstname). I know the followingqueries will use this index:WHERE lastname = ...WHERE lastname = ... AND firstname = ...Also this won't use the index:WHERE firstname = ...But how about: WHERE firstname = .. AND lastname = ...And why?Thanks a lot,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 13 Replies
View Related
Jul 20, 2005
Hi allI recently noticed when trying to optimise a major query of a chess websiteI am the webmaster of, that adding an order by for "gamenumber" which is aclustered index field as in for example "order by timeleft desc, gamenumberdesc" actually speeded up the queries and reduced sql server 2000 timeouts.I have an ASP error log and I am fairly sure that a dramatic reduction insql server timeouts is simply attributed to adding an extra seeminglyredundant order by field - which is the clustered index. Is this phenomenaat all possible or is it my imagination?!Other special attributes of the query includes the use of "Top" to obtain amaximum specified number of rows. Perhaps it is just the uniquecharacteristics of the query, but I would have thought that the less orderby fields would imply faster performance. Has anyone else noticed that aseemingly redundant order by column on for example the clustered indexcolumn, can actually help speed up queries?!Best wishesTryfon GavrielWebmasterwww.chessworld.net
View 4 Replies
View Related
Sep 20, 2007
Hi,
I want to ask a basic question, that is
IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN????
Somewhere in the MSDN library I read the following line:
"A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)
So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC
or
If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
??????
Thanks..!!!
View 5 Replies
View Related
Aug 22, 2007
We are using partitioned unique indexes on partitioned tables. When the Unique Index is built, should the column the index is partitioned by be the top (leftmost) column in the index? While this violates cardinality, it makes sense (at least to me) that the first thing the query execution would do is figure out which partition(s) contain the result set, then filter from there.
What do you guys think? Is there any documentation on optimizing partitioned indexes?
View 1 Replies
View Related
Mar 26, 2007
Hi
I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):
Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.
Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.
What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?
Regards
SanjaySi
View 1 Replies
View Related
May 29, 2007
Want to check my thinking with you folks...
I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.
Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000
C3 is effectively number of seconds since 01/01/1970.
The usage of the table is typically, insert a row, do something else, then update it.
Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.
My thinking is that this composite index is better ordered C1,C2,C3.
My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.
Welcome your thoughts.
View 3 Replies
View Related
Dec 28, 2005
Hi all,
I'm getting this "invalid descriptor index" exception while trying to fetch a record from the table.
The query is "select * from <tablename> where <columnname> = 'xyz'". The column name is correct and indeed a record with 'xyz' value exists. The record is getting fetched too...! But I'm having this particular error while trying to retrieve a couple of fields with rs.getString().
The order of columns in the table is same as the order in which I'm retrieving them. And I'm not facing any problem retrieving another field which has width of 200 characters.
I'll be very grateful indeed if someone can help me out of this particular problem...
Cheers, mates!
View 1 Replies
View Related
Jun 11, 2007
I upsized an access database with a key / index on ordernumber and linenumber.
However if I open the table in the Management Studio the records aren't ordered this way (same goes for select * from table) I get:
Ordernumber
Linenumber
200724001
37
200724004
3
200724006
33
200724001
3
200724011
19
200724014
5
200724006
37
200724011
19
200724006
28
Same goes for my crystal reports files, since the records aren't ordered by ordernumber / linenumber all my formulas go bezerk..
Am I wrong thinking the records should be ordered according to the prim. index?
Please help because I don't want to have to change all my 40+ reports to include an "ORDER BY"
Best regards,
Mike
View 7 Replies
View Related
Oct 18, 2014
I have the following data:
Name Played Won
Player 1 15 14
Player 2 15 5
Player 3 14 13
Player 4 14 1
I want to be able to order by the number of games won by the highest played. So in the example above, I would get:
Name Played Won
Player 1 15 14
Player 3 14 13
Player 2 15 5
Player 4 14 1
How can I achieve that?
View 2 Replies
View Related
Nov 20, 2000
I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).
Is there a way to insert a new column in a specific place in a table using SQL rather than EM?
View 1 Replies
View Related
Oct 8, 2007
Hi, I am using MS SQL Server 2005 9.00.1399.00. I am trying to make a PivotReport.
Everything is fine, but the columns are are ordered alphabetically, but i want that they are ordered in a specific way.
So i just created a new column ProcId in the Query, but how can i order the columns with the new column but display the other values.
i hope you understand what i mean!?
thanks
Philipp
View 3 Replies
View Related
May 24, 2008
Hi
I am using a OLE_DB component source which connects to sqlserver and runs a query and the output of the query is written to a flat file destination.
Something like this
SELECT col1,
col2,
col3,
col4
FROM my_table
when i check in the mapping i see
col2 ------col2
col4-------col4
col3-------col3
...........
and thats what gets written to the flatfile destination.
i want SSIS to preserve the column order and write it as col1,col2,col3,col4
instead of what is happening now and it gets written as col2,col4,col3....
i am running sql2k5 and SSIS with sp2
is this a bug is there a solution for this or change the order of the columns for the flat file destination
regards
Hrishy
View 3 Replies
View Related
Apr 15, 2015
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
View 1 Replies
View Related
Nov 7, 2003
I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?
Thanks
SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)
View 5 Replies
View Related
Jul 20, 2005
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View 1 Replies
View Related
Mar 10, 2008
I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:
MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB
If run the report in April, I want the columns to be ordered like this:
APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR
So, the columns in the report are always ordered (12 months backward) based on the month it was run.
View 4 Replies
View Related
