Which Index A Table Is Using In T-sql 2000

Dec 20, 2007

On one of those common interview question lists, there is the question:

How do you know which index a table is using?

The two answers that I've found are:

SELECT * FROM user_constraints
--and
SELECT TableName,IndexName FROM user_constraints

Both of these return the error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'user_constraints'.

Is this because I'm using the free version of 2000(MSDE)?
If so, what is the right answer for this version?

-Thanks

View 2 Replies


ADVERTISEMENT

The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312

Jul 9, 2004

Hi,

I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:

The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.

All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .

Any ideas as to what is causing this error?

View 3 Replies View Related

Advantages Of Using Nonclustered Index After Using Clustered Index On One Table

Jul 3, 2006

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

View 4 Replies View Related

Index Fragmentation On SQL 2000

Nov 8, 2007

I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running???

View 6 Replies View Related

DROP INDEX Syntax For MS SQL 2000

Feb 2, 2007

I am executing

DROP INDEX [IX_Users] ON [Users]

and i get a syntax error
what is the exact syntax to DROP an INDEX for MS SQL 2000

thank you

View 3 Replies View Related

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

How To Create Index In The View On MS SQL 2000?--URGENT!!!

Sep 14, 2001

Hi ALL,

How to create index in the view on MS SQL 2000?

Thank you very much!

View 4 Replies View Related

Determining Uniqueness Of An Index In SQL Server 2000

Jan 23, 2008

Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.

View 5 Replies View Related

How To Create Index On Table Variable (Table Don't Have Primary Key)

Feb 26, 2008



Hi all,


my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...


DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)

I tryed all below senarios...it is giving error...


--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))


View 3 Replies View Related

Will Re-Build Index Update The Stastitics In SQL Server 2000

May 12, 2008



Hi all

I have Re-Builld index Job,it is runnining evry week.now a days my queries are runnign very slow.eben after running re-build indexs.Please guide me how to check where was the problem.

I am suspecting Stastitics how check the stastitics updation.

any help appreciated.



View 1 Replies View Related

Index Seek, Index Scan, Table Scan

Oct 4, 2007



Hey,

what is the difference between Table Scan und Index Scan?

I find no difitions in the internet

Finchen

View 5 Replies View Related

What Is Table Scan, Index Scan And Index Seek??

Sep 21, 2007

Hi,
I want to know wht is a


TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????

View 5 Replies View Related

How Do I Add A Index To A Table

May 21, 2008

How to add a index to a table or to multiple tables but there are no primary keys on them
 
Thanks
Karen

View 3 Replies View Related

When To Index A Table Or Not?

Jan 30, 2005

I have setup a DTS job that performs the following steps once a week.

1. Truncate a User Table called Sales
2. Import 750,000 new sales records from a semi-colon delimited text file.
3. Execute an update query that adds a SalesID field to each record. (this is a concatenation of several columns for each record and may not be unique)

This whole process takes about 2 minutes.

Here is my question: all querys and views against this Sales table use the SalesId field to identify a result set. Therefore my thought is that I need Clustered index on the SalesID field in the sales table.

What is the right way to handle this:

1. Leave the table as is and do not add an index to the SalesID field. (All queries would rely on file scan of the table)
2. Add a permenat Index to the SalesID field. Which will probably cause the truncate and Import to run more slowly.
3. Do option 2 but drop the index before truncating the table and add the Index back to the SalesId field as the last step in the DTS job.

Any idea what would provide the best performance? If I missed any options please let me know, thank you for any help!

View 2 Replies View Related

Index On Table

Dec 12, 2000

When do you index a table and when do you not?.

View 1 Replies View Related

Table Index

Oct 21, 1998

When considering locks caused by a table, would it be better to have more rows in a table
than less? For example, I can design the table to hold 1 million rows that have 300,000 rows that are
updated frequently or I can take out 700,000 rows and place them in another table and have
the 300,000 rows that remain take a severe beating. Would I have less locking problems and
deadlocks if I take out the non-updating rows or would it be more likely to deadlock because
of a higher chance of a lock being held on the same page?

Thanks,
Mike

View 1 Replies View Related

Index On #tmp Table?

Apr 26, 2008

I have a tmp table created as

select row_number() over ( order by duedate) as row ,
duedate as date, ...
into #fronta
from oitb with(nolock)
where ....
order by duedate

The table is filled correctly with about 30k records. Now in next step I want to work with this tmp table I created, but I have problem, when I use query like this

select * from #fronta where row < 500

When the operator is = or <>, the query is quick, but when I use < or >, the query takes about 10 minutes.

I tried to add to this tmp table index on field named row, but with no succes.

Have anyone idea how to improve the speed?

thanks a lot

View 4 Replies View Related

Index On Table

Nov 8, 2007

Vendor software does not supply primary keys on tables

There is a table

EMPLOYEE
Empl_ID This has create unique, and index selected.

As this is unique is this ok to set create as clustered so i get primary key defined.

How to change automatically on all tables that have this set.

Thanks

View 7 Replies View Related

Table Index

Mar 20, 2008

Hello,

I have a table as so:

ID
Field1
Field2
Field3

My ID field is an identity field (unique). It is the primary key. I also want to add an index/unique key so that a combination of Field1 and Field2 can not be duplicated. How do I do this?

Many thanks in advance!

Mark

View 6 Replies View Related

Maximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000

May 27, 2008











Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000

Number of instances per server
n/a
n/a
16
16

Number of databases per instance / server
n/a
32,767
32,767
32,767

Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647

Number of users per database
n/a
16,379
16,379
16,379

Number of roles per database
n/a
16,367
16,367
16,367

Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB

Number of columns per table
255
1024
1024
1024

Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage

Number of bytes per row





(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB

Number of columns per query
255
4,096
4,096
4,096

Number of tables per query
32
256
256
256

Size of procedure / query
64 KB
250 MB
250 MB
250 MB

Number of input params per procedure / query
199
1,024
2,100
2,100

Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB

Depth of subquery nesting
50
32
32
32

Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)

Number of columns per index
10
16
16
16

Number of characters per object name
64
128
128
128

Number of concurrent user connections
255
32,767
32,767
5

View 1 Replies View Related

SUM Uses Table Scan But Not Index

Jan 16, 2002

Hi I'm issuing a SELECT on a field with the SUM on SQL Server 7. I have an index on the field in the WHERE clause but upon analysis, the Query Optimizer always uses a Full Table Scan. Can anyone explain why and is there a way to use the index.

HEre's the structure:
SELECT SUM(colA)
FROM TABLE tblB
GROUP BY colC

An index exists on column colC.

Thanks

View 1 Replies View Related

Name And Size Of Each Index In A Table

Mar 21, 2001

Hi all,
Is it possible to get the name and size of each index in a table? Please let me know.

Thanks in advance,
Praveena

View 4 Replies View Related

Name And Size Of Each Index In A Table

Mar 21, 2001

Hi all,
Is it possible to get the name and size of each index in a table? Please let me know. In sql 7, we could do this using EM but, in sql 2000, I'm not sure how to do this.

Thanks in advance,
Praveena

View 1 Replies View Related

Index On A Table Variable (SQL 2K)

May 31, 2001

I am creating a table variable (@tblBin) to temporarily store a set of data. Later in my sproc, I am doing a JOIN from @tblBin to a persistent table. In order to improve performance, I was thinking of adding an index to the columns of the @tblBin (indexes already exist on the persistent table). Using standard CREATE INDEX syntax(*), I am getting a compile error. Can this be done?

(*)CREATE NONCLUSTERED INDEX IX_tblBin_shortname ON @tblBin(shortname)

TIA ...

Regards,
~DLDay

View 1 Replies View Related

Clustered Index On A VERY Big Table

Oct 28, 2006

I already posted this over on sqlteam so don't peek there if you haven't seen that post yet. :)

So now to the question:

Anyone care to guess how long it took me to build a clustered index on a table with 900 million rows? This is the largest amount of data in a single table I have had to work with thus far in my career! It's sorta fun to work with such large datasets. :)

Some details:

1. running sql 2005 on a dual proc 32bit server, 8gb ram, hyperthreaded, 3ghz clock. disk is a decent SAN, not sure of the specs though.

2. ddl for table:

CREATE TABLE [dbo].[fld](
[id] [bigint] NOT NULL,
[id2] [tinyint] NOT NULL,
[extid] [bigint] NOT NULL,
[dd] [bit] NOT NULL,
[mp] [tinyint] NOT NULL,
[ss] [tinyint] NOT NULL,
[cc] [datetime] NOT NULL,
[ff] [tinyint] NOT NULL,
[mm] [smallint] NOT NULL,
[ds] [smallint] NOT NULL
)

3. ddl for index (this is the only index on the table):


CREATE CLUSTERED INDEX [CIfld]
ON [dbo].[fld]
(
extid asc
)WITH (FILLFACTOR=100, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)


4. extid column was not sorted to begin with. ordering was completely random.

Note that I have changed the column names, etc, to protect the innocent. I can't go into details about what it's for or I'd be violating NDA type stuff.

View 5 Replies View Related

Read Table By Index

Dec 7, 2014

I need to read a db Table but sorted by index - is there a generic "select * from tableA sorted by index" where it just uses whatever index it finds (main index) or do i have to name the index?

View 2 Replies View Related

Index A View Or A Table

Apr 8, 2004

I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

Thanks

View 14 Replies View Related

Scripting An Index To A Table

Apr 19, 2004

What is the correct syntax to add an index to a table?

Example: tableA with fieldB- create an index on fieldB

View 4 Replies View Related

Creating Index On Table...

Jan 22, 2008

Hi
i am new to using this sql server 2000....this is a very simple question to all u guys.....i am just in a learning stage...so any help from u guys is really appreciable....

i need to create a table customers with the following columns...
identity column to self-populate as the primary key,
joindate, leavedate, custcode, empID.

This is the one i tried:
create table customers (id int primary key identity (1,1) not null,
joindate smalldatetime null,
leavedate smalldatetime null,
custcode varchar (10) not null,
empid int not null
)
is tht code correct only???
and i also want the below one :
Create indexes on the leavedate, custcode and empid columns.

how to create these indexes???
and wht happens when i create them(like is thr any advantage of creating indexes???)

thanks......

View 2 Replies View Related

Can You Add An Index To A Table Variable?

Jul 20, 2005

Hi,I've got 2 table variables inside of an SQL 2000 function:@tmpBigList(BItemID, BRank)@tmpSmallList (ItemID, Rank)The following UPDATE statement can run for a long time if @TmpTable1has 500 rows and @TmpTable2 has 35,000 rows.UDPATE @tmpBigListSET BRank = t.RankFROM @tmpBigListJOIN @tmpSmallList t on t.ItemID = BItemIDLooking at the Query Plan, you see that the INNER JOIN Of @tmpBigListto @TmpSmallList results in 500 * 35,000 = 17,500,000 rows beingreturned from @TmpSmallList. That takes a long time.An index would help, but it appears that you can't add an index to atable variable.Changing to a temp table does not work since it's in a function.Thanks,Joe Landes

View 1 Replies View Related

Table,index Partitioning

Apr 30, 2007

We had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.


Each table has a column USER and we are queering data based on login user .this is the foreign key of USER table. Each view contains user column in where clause .So for every query we are searching all records .instead of that is there any way to get data with out searching all records.

I heard about table Partitioning, index Partitioning, view Partitioning. Are they helpful to boost my query performance?

And also let me know is there any good way of designing apart from above options

View 3 Replies View Related

Inserting In A Table With Index...?

Aug 30, 2006

In the table that i will insert rows, there are columns:

Col1 Col2 Col3 Col4 Col5

an there's an index for (Col2,Col3,Col4)

So,

Col1 Col2 Col3 Col4 Col5

---------- ------------------- ------------- ---------- -----------------

0 30082006 'TERM1' 1 7

1 30082006 'TERM1' 1 7



this is not allowed because of the index for (Col2,Col3,Col4).Col4 must be different from 1.

I can see only one way to insert into this table..



SELECT @max_col4= max(Col4) FROM TABLEXXX WHERE Col2=30082006 AND Col3='TERM1'

INSERT INTO TABLEA(Col2,Col3,Col4,Col5) VALUES (30082006,'TERM1',(@max_col4+1),7)



this is right with only one client....but if there are more than one,possibly two clients can try to insert to the table.When one of them gets the max_Col4,lets say it got 89.At the same time,another client started the process and it got 89,too..but after the first client inserted the row, then max_Col4 will be 89.However the second client will still try to insert with Col4 as 89......namely, it will boom...

There must be another method to achieve that job..but what is it...???

I wish I could be able to explain my problem....





Thanks in advance..

View 1 Replies View Related

Table/Index Onitoring

Aug 15, 2006

Is
there a way to track tables/indexes/stored procedures that are being
used? I know that the Profiler can do this but I am looking for a way
to query a system table to get this information. Oracle has a way to
turn on/off monitorint for tables and indexes so I was wondering if
this info is avaialble and if so if something needs to be done to
activate the collection of the info.

Thanks

View 8 Replies View Related







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