Transact SQL :: Any Reason For Difference In Order Of Columns Between Index Of Constraint And Its Statistics Definition?
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,
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).
All,What's the difference between a unique contraint and unique?sementically, if you want a column contain unique values, it is acontraint. And an index is for searching/sort. The questions are:1. Does a unique constraint interally use unique index?2. If Yes to #1, I DO NOT need to create an index for search/sortpurpose, right?3. If Yes to #2, What's better?4. Also for Primary Key column, it is actually a special uniquecontraint. Not need to create index on PK column for searching/sorting,correct?5. Also for FK contraint, no need to create an index forsearching/sorting?ThanksJohn
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.
We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
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
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
It is possible to get the definition of an index in a script? I want the "Create index..." string so I can drop and recreate the index in a single statement. You can do it in the Query Analyzer object browzer with the "Script object..." options.
What are some ways to analyze index coverage and usage? I have a 18 GB database, half is data, other half is indexes and I want to cut down that number as much as I can without affecting performance. Thanks
I have been monitoring some indexes on a table with a lot of inserts, no updates and no deletes. I was wanting to determine when to update the statistics on the index. Does anyone know what would be a good target range for the density when you run the dbcc show_statistics?
I was wondering how often you should reindex. By looking at dbcc showcontig and statistics I see that I am heavily fragmented and scan density is between 10-30% on my important indexes. I'm thinking of scheduling this to be done nightly. nay help is much appreciated.
At managing index SQL Server 6.5, there is distribution button. I have a database on production server, one of the table has 20 indexes. When I press the Distribution button, it reveals that most of the indexes have very poor selection of index, from 30% to 99%. There are 6 of them are very good. Based on this statistics, do you think I should remove these indexes? As the book says, if the statistics is higher than 5%, the optimizer will not use index but do a table scan. Removing those index should not affect the performance, is that right? Your suggestion is very appreciated.
I have a question regarding updating statistics for a primary key.
Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.
Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Is it neccessary to schedule a update statistics on index in sql server 2005 on daily basis Is it neccessary to schedule a rebuild index on index in sql server 2005 on daily basis
Is there any way to determine index usage statistics for a given table? For examle, I have a table, with three indices. I need to know how many times each index was used. Is it possible?
And second part of question: I need to know, which user overloads my base with their giantic queries. Is there any way to determine, how many system resources each of user's sessions uses?
We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ... View definition includes no output columns or no items in the FROM clause. A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select Case_CaseId, Logged, CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting, CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating, CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected, CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved, CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved, CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed from (
SELECT Case_CaseId, MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged, MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting, MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved, MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved, MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed FROM CaseProgress GROUP BY Case_CaseId ) as temp order by Case_CaseId
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as select T1.Matcle as CorpID, T2.Nmpres as Name, T4.DATDEB as LeaveFrom, T4.TEMDEB as PM, T4.DATFIN as LeaveTo, T4.TEMFIN as AM, T10.LIBLON as LeaveType, T8.LIBLON as Location, T12.LIBLON as ParentOrg
from HRINU.zy00 T1, HRINU.zy3y T2, HRINU.zy39 T3, HRINU.zyag T4, HRINU.zy38 T5, HRINU.zy1s T6, HRINU.zd00 T7, HRINU.zd01 T8, HRINU.zd00 T9, HRINU.zd01 T10, HRINU.zd00 T11, HRINU.zd01 T12 where T4.Nudoss = T3.nudoss and T4.Nudoss = T1.Nudoss and T1.Nudoss = T2.nudoss and T3.nudoss = T5.nudoss and T6.nudoss = T1.nudoss AND T7.NUDOSS = T8.NUDOSS AND T9.NUDOSS = T10.NUDOSS AND T11.NUDOSS = T12.NUDOSS AND T3.IDWKLO = T7.CDCODE AND T4.MOTIFA = T9.CDCODE AND T5.IDESTA = T11.CDCODE and T6.stempl = 'A' and t7.cdstco = 'z04' AND T8.CDLANG = 'U' and t9.cdstco = 'DSJ' AND T10.CDLANG= 'U' and t11.cdstco= 'DRE' AND T12.CDLANG= 'U' and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00 and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00 and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables. Also throw some light on how to analyse the execution plan for further enhancements.
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.
************* select orderID, custId, intertcsi from tblorders where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Index : indxInterTCSI orderID custId intertcsi ----------- ----------- --------- 1015245 1011313 2815 2556392 2556392 2815 ....
Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0 *************** Then I use the same select statement to force a table scan:
select orderID, custId, intertcsi from tblorders (index=0) where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Table Scan orderID custId intertcsi ----------- ----------- --------- 60472 61084 2815 102184 102333 2815 ... Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980
When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run. Any idea or comment would be very appreciated.
I need a solution (script) where I can pull all objects(DB's incudes tables, views, synonyms, stored procedures, primary key, foreign key, triggers etc) definitions from the server in a dependency order. I want to generate a script from these objects, where if I run script all objects should be created the way it is..
.All dependencies should be automatically available in script in a dependency order. What is the best way to bring all objects (definition) in form of scripts.
These are the fields am looking - databsename, schemaname, objectname, typeofobject, depenedencyorder (nothing but level)...
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?
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-
Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns?
