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
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
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
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
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
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
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
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
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
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
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
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
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
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
Dec 12, 2000
When do you index a table and when do you not?.
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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