Hi
You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall?
If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option?
Thought I'd make it a poll just for the hell of it.
:)
A few days after droping and recreating noclustered indexes on a large table, scan density of most indexes falls below 20%. Only a few number of rows has deleted. Update operation didn't update these keys and there was a lot of inserts. Could anyone explain why are indexes fragmented so much?
View 2 Replies View RelatedI have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
trying to search a DB for all records within a range.
I have a recordset containing about 10 postcodes and I need to find all the records containing any one of those postcodes
currently I have:
sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & left(rsPostcodeResult, 4) & "%'"
but I am getting 'type mismatch' error.
Thanks for looking
Mark
i have the follow SQL for an update...
UPDATE tblStudyServices
SET tblStudyServices.StudyLT = 1
FROM (SELECT S.PatientID FROM tblStudyServices S
INNER JOIN tblPatient P ON P.PatientID = S.PatientID
INNER JOIN PAS.dbo.[PAS Patient Details] PT ON PT.[CaseNote Number] = P.Casenotenumber
INNER JOIN @filteredAdminKeys [PAD] ON [PAD].[PAS key 1] = PT.[PAS key]
INNER JOIN @filteredConKeys PCE ON PCE.[PAS key 1] = PAD.[PAS key 1] AND PCE.[PAS key 2] = PAD.[PAS key 2]) AS Results
WHERE tblStudyServices.PatientID = Results.PatientID
i would like to extend this so that it would update (set StudyLT=0) the other records that dont meet the criteria
is there a quick way to do it?
Cheers,
Craig
Hello, quick question
Doing a bit of research and need to know whether Stored Procedures can be recursive. I know I could try it out but please anyone is recursion possible with sp?
Thanks
hello!
Is it possible to set nonclustered
index on column with dublicate values?
Thank you,
Alona
Ok, let me try to set the stage.
Between 2m and 5m inserts per day. NO UPDATES.
Table has a 4 part primary key. All BigInt data types. Key value 1 and 2 have a range between 1 and 100. Key values 3 and 4 are auto incrementing values (forign key values) from other tables.
Space is an issue, so we have chosen not to have an additional column for a counter field for the PK. (We would never use the field for querying.)
Users complained of query speeds, so we added a couple non clustered indexs. This brought up the query speeds a lot. But of course it slowed down the input speed a bit. Nothing dramatic, but enough so we could tell.
Now the users was to increase the amount of data by about 5X. Obviosly I'm somewhat concerend, as SQL is already spending a lot of the day pegged.
So, in looking around, since the new indexes seem to be the most help in querying, I'm thinking of dropping the PK back to a nonclustered index, so I can get rid of the over head of restructring the data table on every insert. Then maybe making one of the other indexes the clustered index. (only 2 columns in this index)
Thoughts?
I have 3 table A, B, C
Table A (15 field, 4 fields indexed and Primary Key) – approximate rows: 50.000 – 60.000
Table B (18 field, 6 fields indexed and Primary Key) – approximate rows: 350.000 – 500.000
Table C (16 filed, 9 fields indexed and Primary Key) – approximate rows: 500.000 – 1.000.000
Structure is something like this:
A (master) --> B (detail) ---> C (sub detail)
On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX
Thanks
Sorry for my english
when do you use them?
i only know you use clustered when you have million of records. So once the table has been indexed, query statement is able to retrieve the recordset faster.
what about nonclustered?
Hi guys. I have a table named [Check] and need to create an index for CVNumber field. The table has no primary key for the meantime. I tried this script but error occured.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE NONCLUSTERED INDEX IX_Check_1 ON dbo.[Check]
(
CVNumber
) ON [PRIMARY]
GO
COMMIT
Error message:
Server: Msg 3023, Level 16, State 2, Line 3
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Is it because I used the table name Check which is a reserved word for SQL? But I included [ ].
Please help. Thank you.
Hi everyone,
I have some problems on composite nonclustered indexes. I could not exatly understand their logic.
In my opininon, suppose that we have a table called Order and we create a composite nonclustered index on this table for OrderID column and OrderDate column. So I am using this query;
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate
So in here, I think our first research is based on OrderID and ten after ordering our data pointer according to the OrderID and then our index is converted to an index which is based on OrderDate while performing ordering. So is this correct ??
Would you please explain this ?
Thanks
When a nonunique nonclustered index is built on top of a clusteredindex, is it guaranteed that the bookmark in the nonclustered indexwill be kept in the same order as the clustered index?Here's an example to demonstrate my question:CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3int,col4 int)ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)CREATE INDEX ix_indextest ON indextest (col1,col3)GOINSERT indextest VALUES (1,2,1,1)INSERT indextest VALUES (1,3,2,1)INSERT indextest VALUES (1,4,2,1)INSERT indextest VALUES (2,1,1,1)INSERT indextest VALUES (1,1,1,1)SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1DROP TABLE indextestThe select statement above is covered by the nonclustered index, sothat index is used. However, the nonclustered index is defined only toensure the ordering of col1 and col3 within the index; col1 and col2follow within the index as the bookmark to the clustered index. When Irun this query, my desired result is to have the records appear in theorder supported by the clustered index:1,11,2As it happens, the result I got was indeed in that order, but I don'tknow if it was mere coincidence, or if the bookmark in the nonclusteredindex is maintained in the same order as the clustered index. If Iwant to ensure the above order, is it sufficient to have thenonclustered index defined as above, or do I need to define it as:create index ix_indextest on indextest (col1,col3,col2)just to be sure that the results are returned in ascending order forcol1,col2? If the two-column index is sufficient, is it guaranteed tostill be sufficient in SQL2005 and future versions of SQL Server, or amI better off adding the third column just to be safe?Thank you,--Dennis Culley
View 4 Replies View RelatedI have table which having clustered index based on column (A,B,C,D,E,F).Now my query based on B,D,F. e.g: where b='Test1' and D='test2' and F='test3' Now Execution plan ask to create non clustered index with (B,D,F) column.is it make any sense to create non clustered index where clustered already available.
View 4 Replies View Related
I am attempting to understand the behaviour of a nonclustered index in regards to page splitting.
If I had the following table:-
Col1, Col2, Value
This table has a Clustered index on Col1, Col2 and a NonClustered index on Col2 and include Col1, Values.
If inserts into Col1 were causing page splits in the Clustered index, what are the effects on the NonClustered index?
I am attempting to understand what happens to the NonClustered index in this scenario so that I can make a decision on what fillfactor to use.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
What is the difference please?
View 1 Replies View RelatedHello I want to learn disparity clustered index or nonclustered index and in queries which one run better.
example
select * from orders where orderID=5
to this query clustered or nonclustered
thanks
For a table 'table1' in sqlserver 2000,
table1(col1) is clustred index 'Ix1' and table1(col1,col2,col3) is nonclustered index 'ix2'
is not tabel1(col1) 'ix1' a duplicate/redundant index ?
which index should be retained ?
or should both indexes remain on the OLTP table ?
Thanks a lot in advance.
Sir,
1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?
Please give me some tips & samples
Sundar Raman
Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index.
My Question is does the system creates these indexes or something else does this.
Thanks
Chak
Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list.
any help would be appreciated, thank you
At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
Thanks a lot
I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Has anyone encountered this before? Any ideas?
Appreciate it, K.
Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all.
I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
thanks
when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those.
_WA_Sys_is_platinum_0A9D95DB
_WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active
hind_c_33_15 nonclustered, statistics located on
hind_c_37_1 clustered, hypothetical located
Thanks
Raj
Is there a way T-SQL script can find out all indexes built on a set of tables, drop them and periodically ( quarterly as an example ) re-build them ?
Thanks in advance for help.
Ivan
How do you find out indexes ( with column names info ) on a table ?
Thanks in advance.
Ivan
I am on SQL 6.5.
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
All suggestions will be appreciated.
Thanks,
Eric
We have to interduce a new naming convention for the indexes currently available in the user databases.
We also have to drop all the old indexes available in about 250 tables and recreate them all acording to the naming convention we are coming up with.
Can any body suggest any idea.
I thank you guys in advance for your considaration.