index help



  • 1. Is SQL Server Developer Edition 2005 still available
    I have been trying to buy SQL 2005 Developer Edition and have been unable to find where I can buy it. I can only find a place to evaluate Enterprise Edition. Could someone direct me to the URL where it can be purchased? Thanks for your help. Binnie
  • 2. SQL Server 2005 ALTER TABLE/INDEX Trigger
    I have a SQL Server 2005 database that has an audit trigger for tables and triggers are deleted are alter will trigger this event. When I run the SQL Server maintenance plan to rebuild the indexes the job aborts and runs for several minutes and never completes the first index rebuild. I deleted the audit trigger and the maintenance job ran without a problem. Please help me resolve this issue without deleting the audit trigger. Thanks,
  • 3. the alert couldn't send an email
    Hi Guys, I have a question regarding alert in sql server 2005. I created an alert which sends an email when the data file exceed the size. The alert didn't send an email out when the data file exceed the size. In the biginning I got error from sql server agent log file: [264] An attempt was made to send an email when no email session has been established. I restart sql server agent, but I still cannot send email out, but the error in sql server agent log was disappeared. It is weird that I found it was sent by net send in operator history. But I didn't setup any place as by net send, all I set are by e-mail. Why it was said by net send. Could some body give me hint what was problem? Thanks.
  • 4. i3 symantec tools for sql server
    Hi, Does anyone use i3 to monitor the sql server? How is it comparing to other native tools sql profiler etc.? Do DBAs use it a lot? Thanks, sg
  • 5. SQL Server 2005 Unicode
    Is there an option in SQL Server 2005 to set up the databse or convert the database to UNICODE, instead of changing all columns to nvarchar etc. Thanks

Re: index help

Postby Hugo Kornelis » Wed, 24 Sep 2008 00:21:24 GMT

Hi Sarah,

Yes. THis is not only possible, but even an often recommended practice.
Maintaining an index while data is modified is costly. When updating,
inserting, or deleting large amounts of data, it's often faster to
remove the index first and recreate it later.

As a bonus, you'll also avoid fragmentation of the index this way!

Hugo Kornelis, SQL Server MVP
My SQL Server blog:  http://www.**--****.com/ 

Re: index help

Postby c2c » Wed, 24 Sep 2008 00:41:01 GMT

Hi Hugo,

Thanks very much for your fast response.

This is really helpful

Re: index help

Postby Scott » Wed, 24 Sep 2008 23:33:12 GMT

Hi Hugo,

I was wondering if you think dropping and recreating an index in the 
condition where a table contains 150M records with 1M added evenly during 
the day would help or hinder performance ?

... especially if the index is not automatiacally updated during the insert 
(which is what i though you were suggesting above) ?

Inserting 1M records when an index is present will increase the writes 
required i assume. But if SELECT queries are run on this table through the 
day and the index is not updated dynamically/automataiclly during the INSERT 
then surely the SELECT query would miss data as its not in the index ?

Thank you for any input. 

Re: index help

Postby Hugo Kornelis » Thu, 25 Sep 2008 06:03:15 GMT

Hi Scott,

If 1M were added in a single operation, I'd advice you to test it. On
*your* hardware, with *your* tables and data. As that is the only way.

But for 1M insertions over the day, you should just either keep the
index and accept the overhead of index maintenance, or completely remove
it. The cost of rebuilding an index on 150M rows will always exceed the
cost of maintaining an index when inserting just a few hundreds or even
a single row.

The third option is if the index is only required for a single operation
(for instance a report generated in the low-traffic hours) - in that
case, you *might* find that creating an index before executing the query
and dropping it afterwards is better than running the report without
index or keeping the index up to date the entire day. But again, you'd
have to test this very carefully.

No, what I was suggesting (which was based on a scenario of a single
mass insert rather than lots of inserts throughout the day) was to
remove the index and then rebuild it - that is, during the insert there
is no index and a new one is built (on ALL rows - existing and new)


No. If the index is removed (or disabled, an alternate option as of SQL
Server 2005 which basically has the same effect), it can't be used to
find rows fast so the queries might be slower (lots slower in some
cases). But the results will always be correct.

Hugo Kornelis, SQL Server MVP
My SQL Server blog:  http://www.**--****.com/ 

Re: index help

Postby Scott » Thu, 25 Sep 2008 18:56:51 GMT

undertood. thank you for the post. 

Similar Threads:

1.2005 ft index help

I have numerous vldb's that my mgmt want to have full text indexed. Usually 
there are betwen two ad four columns per table that would need the FTI put on 
them. FTI on sql2000 failed miserably as it was slow and my tables were 
probably too large (smallest is 10 million rows and largest is 800 million 
I would like to know if 2005 FTI, since its been incorporated into the sql 
engine, has had its upper limits increased in regards to the size of db can 
be indexed. 
also, can you replicate a FTI to another server along with its table?

2.Would descending index help our View's performance?

3.need index help

i have 2 tables one with header information and one with data.

the header is like
id	int identity primary key clustered not null
dt_stamp	datetime defatult getdate() not null
desc	varchar(20) null

the data is like
fk_id	int references header (id) not null
code 	char(10) references another table on its primary key not null
year	int not null
month	int not null
balance	decimal(15,2) not null default(0)
budget decimal(15,2) not null default(0)

each time an insert is made, one record gets put in the header and
about 10,000 get put in the data. this will happen about 5 times per

i wonder about indexing.  with 1,000,000 (just a random number)
records in the data, that means there are 100 records in the header. 
balance and budget will be very varied, month will only be from 0 to
13.  year will be year, 2000, 2001, 2002, etc.  code can be any of
about 1000 types, fk_id will not be many, under a few thousand (260
per year over 5 years). so in unique speaking from most to least order
will be


what is the best way to index?  do i cover all of them?  when SQL
selecting, user will always do fk_id = some number, never will they
get more fk_id at a time.

do i cluster index, noncluster index, both, neither?  help please!!


4.full text index help


I have a database that uses full text index; however, it is not working.

I looked into Books Online for "full-text indexes, adminstering".
and copy and ran the following:

-- Create and populate a table.


WHERE TABLE_NAME = 'FulltextTest')

DROP TABLE FulltextTest



( article_id int IDENTITY(100,1)


article_title nvarchar(200)



INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always
enjoyed ice skating.')

INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male
figure skater')

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice:
Skating Reaches Tops in Public Opinion Poll')

INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan
skated on the ice!! Skating fans cheer!')

INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the
best in Steven. Buchanan exults in first victory...')


-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'


-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog',



-- Register the new table and column within it for full-text querying,

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest',




EXEC sp_fulltext_column 'FulltextTest',



EXEC sp_fulltext_table 'FulltextTest',



-- Start full population of the full-text catalog. Note that it is

-- asynchronous, so delay must be built in if populating a

-- large index.

EXEC sp_fulltext_catalog 'StevenBCatalog',


WHILE (SELECT fulltextcatalogproperty('StevenBCatalog',

'populatestatus')) <> 0


WAITFOR DELAY '00:00:02'

-- Check every 2 seconds to see if full-text index population is complete.




-- Execute a full-text query against the new table.

SELECT article_title

FROM FulltextTest

WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

The result should be 4 records...

I get 0 records.

How do you setup fulltext...I have never done this before

Is there a way to check if the database is setup correctly to use fulltext?

Thanks again.

5.Create Index....Help

I create my view like this (for now is exactly what I need):

USE tsNess2


SELECT     t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
                      SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
                      SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
                      SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
                      SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
                      SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
                      SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
                      SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
                      SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM         dbo.tblTravelDetail t1 INNER JOIN
                      dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
                      dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
                      dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
                      dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
                      dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE     (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,

but then when I do this:


I get "Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression)."

And cannot create my index.
Any help is appreciated.

6. Index Help

7. Need index help to fight SLOW update

8. Nonclustered index helps ORDER BY?



Who is online

Users browsing this forum: No registered users and 68 guest