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
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.
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).
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
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.
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?
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.
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
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
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?
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?
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.
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.
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?
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
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
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.
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?
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?
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.
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...
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?
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.
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
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.
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)
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?
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.
