Creating Indexes

Feb 26, 2001

Just a quick question, Is it better to build an Indexindicies after creating a new table or after populating the table with the data. Which is quicker? What about statistics? This table is dropped/and then recreated on a weekly basis. It is a reporting database.

Thanks.

View 1 Replies


ADVERTISEMENT

Creating Indexes

Nov 6, 2000

I ran the index tuning wizard and it suggested 4 new indexes w/ a 20% improvement to be gained..

I saved the index creations to a script file and I'm curious if I can implement them at any time or is it better to do that after most of the users are done w/ the system for the day..

Also, any other performance suggestions to help speed up queries? Thanks in advanced..

View 2 Replies View Related

Creating Primary Key And Indexes

Aug 19, 2002

Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.

I have attempted to create an index by using the following statement:

create nonclustered index SchoolIndx on wrestlerstest (School)

I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the

Thanks again in advance,

Greg Zafros

View 1 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Creating Clustered Indexes

May 6, 2008

Greetings all,

What's best practice for creating clustered indexes?! Should they be added to a table AFTER it has been populated or should the clustered index be created BEFORE?

Thanks for your advice in advance.

View 4 Replies View Related

Doubt In Creating Indexes-Urgent

Jun 4, 2001

Hi,
I have a question do I have to increase the Transaction Log size to create new indexes after dropping existing one on a table which has 18108360 rows. I need to recreate 5 indexes on that table. As this table is corrupt. Let me know. The corruption was on one of the indexes of this table.
Thanks,
Cynthia

View 1 Replies View Related

Creating Indexes On Physically Separate Drives

Apr 25, 2000

I'll be running SQL v7.0 with raid level 0. The DB will have approx. 250 tables and 1200 indexes.
Can you have the indexes located on a physically separate drive with SQL v7.0 and would this be the best practice?

View 1 Replies View Related

Creating Indexes On Tables From The Database Explorer

Jan 16, 2006

I would like to create an index on a table from the Database Explorer.

I am using Microsoft Visual C# 2005 Express Edition.

I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.

Thanks!

 

View 1 Replies View Related

Creating Indexes On Large Table To Increase Performance

Mar 5, 2008

Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:


CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))

CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))



INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC

INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType

FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID

WHERE RecordedCalls.ID <= '9369907'

AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))

AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)

AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')

AND RecordedCalls.ServerID NOT IN('2')

AND RecordedCalls.AgentID NOT IN('1000010000')

AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))

AND RecordedCalls.IsDeleted='false'

GROUP BY RecordedCalls.CallType

SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')

WHEN 'Arabic' THEN #tempLookups.NameA

ELSE #tempLookups.NameE

END AS CallsType FROM

#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code

DROP TABLE #tempLookups

DROP TABLE #tempTable


Thanks all,
Tayseer

Any suggestions how to create efficient indexes??!!

View 2 Replies View Related

Creating Full-text Indexes On A Table In A Publication

Sep 13, 2006

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

View 12 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

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?

View 5 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

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?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

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] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

Creating Trigger On Creating Table

Jan 28, 2008



Hi,

If I want to automatically insert a record which has default value in a table,
how can I create the trigger?

View 5 Replies View Related

Indexes

Jul 31, 2002

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

View 1 Replies View Related

Indexes

Jul 23, 2001

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

View 1 Replies View Related

DB Indexes

Jul 27, 2000

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

View 2 Replies View Related

Indexes

Sep 13, 2000

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

View 1 Replies View Related

Indexes Not Being Used

Nov 20, 2000

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.

View 1 Replies View Related

6.5 Indexes

Jan 10, 2001

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

View 2 Replies View Related

Indexes

Feb 5, 2000

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

View 2 Replies View Related

Indexes

Mar 2, 2001

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

View 1 Replies View Related

Indexes

Mar 5, 2001

How do you find out indexes ( with column names info ) on a table ?

Thanks in advance.
Ivan

View 3 Replies View Related

Indexes!

Jun 20, 2001

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

View 2 Replies View Related

The Indexes

Nov 12, 1998

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.

View 2 Replies View Related

Indexes

Mar 10, 2006

hi.

there 2 different queries

1
[MYSQL].... where Cat=@CatID and Date=@Date[/MYSQL]


2
[MYSQL]... where Cat=@CatID and Date=@Date and Salesman=@SID[/MYSQL]

these queries are often used.
my question is about indexes.

should I use two different indexes?

index 1 : Cat,Date
index 2 : Cat,Date,Salesman

or only index 2 is enough ?

View 8 Replies View Related

SQL Indexes

Jul 11, 2006

I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.

Here is my code:

Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6

I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?

Thanks!

Ronna

View 6 Replies View Related

All Indexes In DB

Nov 15, 2004

Hi all,

I want to know all indexes in database.
What do I do to get them?

Thanks in advanced,
Thi Nguyen

View 4 Replies View Related

Indexes

Feb 6, 2006

We recently updated some of the databases from MS Access 2000 to SQL Server 2000. In Access we had columns which were set to Indexed No Duplicates, these were not Primary key fields. Is this possible to set some indexes to prevent duplicates in SQL Server without creating performance issues?

Miranda

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved