index help

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 
rows). 
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?
thanks!!!

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

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

(most)
balance/budget
code
fk_id
month
year
(least)

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!!

AP

4.full text index help

Hello,

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.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'FulltextTest')

DROP TABLE FulltextTest

GO

CREATE TABLE FulltextTest

( article_id int IDENTITY(100,1)

CONSTRAINT PK_title_id PRIMARY KEY,

article_title nvarchar(200)

)

GO

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

GO

-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'

GO

-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog',

'create'

GO

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

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest',

'create',

'StevenBCatalog',

'PK_title_id'

EXEC sp_fulltext_column 'FulltextTest',

'article_title',

'add'

EXEC sp_fulltext_table 'FulltextTest',

'activate'

GO

-- 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',

'start_full'

WHILE (SELECT fulltextcatalogproperty('StevenBCatalog',

'populatestatus')) <> 0

BEGIN

WAITFOR DELAY '00:00:02'

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

CONTINUE

END

GO

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

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

USE tsNess2
GO

SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
GO

CREATE   VIEW V1
WITH   SCHEMABINDING
AS
SELECT     t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
                      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,
t6.amount_type
GO

but then when I do this:

CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (MemberId)
GO

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.
Thanks,
Trint

6. Index Help

7. Need index help to fight SLOW update

8. Nonclustered index helps ORDER BY?