October 29, 2006 at 6:09 pm
Hi,
I have a table called tblABC (see below). After I created it I had a look in sysindexes table and saw that there are 3 records their names are tblABC, PK_tblABC and ttblABC but they all have the same id 965578478. I can understand why the PK_tblABC entry is there but I don’t know about the other 2 (tblABC and ttblABC). Can anyone please give me a quick explanation of what they are?
Thank you,
CREATE TABLE [tblABC] (
[fldID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblABC_fldID] DEFAULT (newid()),
[fldNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDate] [datetime] NULL
CONSTRAINT [PK_tblABC] PRIMARY KEY NONCLUSTERED
(
[fldID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
October 29, 2006 at 10:59 pm
hi
one row in sysindexes is for the table,
second row is for primary key nonclustered index
third row is for the text datatype column (ldNote).
if there is a text column then there is one row in sysindexes for that column. You would have got your answer had u just looked in BOL.
"Keep Trying"
October 30, 2006 at 5:17 am
Warwick,
The ID column is the object_id of the table itself, not the indexes. The INDID column holds the unique value for the index.
Since all your indexes are created on the same table, the ID column will be the same for all 3 rows.
Rows with an INDID of 0 mean the table is a heap.
Rows with an INDID of 1 mean this is a clustered index.
(You will always find an INDID of either 0 or 1 for every table, depending on whether it has a clustered index or not)
Non-clustered indexes will each have an additional row in sysindexes, with possible INDID values of 2 - 254.
INDID values >=255 are for TEXT columns.
Phil
October 30, 2006 at 5:23 am
If you get hold of the sql 2000 resource kit there is an electronic diagram of the system tables you might find interesting, however - word of warning - don't mess with system tables especially if you don't know what they are. Understanding them can be very helpful at times so getting to undertstand them is useful, but bear in mind everthing chnages for 2005!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 30, 2006 at 6:32 am
If I may post some of the same details :
CREATE TABLE [tblABC] (
[fldID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblABC_fldID] DEFAULT (newid()),
[fldNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDate] [datetime] NULL
CONSTRAINT [PK_tblABC] PRIMARY KEY NONCLUSTERED
(
[fldID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Select Object_name (id) as TblName, id, Name as IdxName, Indid AS IdxType from dbo.SysIndexes where id = object_id('tblABC')
/*
tblABC 11628326 tblABC 0 --Heap (no clustered index), 1 is clustered index
tblABC 11628326 PK_tblABC 2 --2 to 254 is a non clustered index (on the PK column in this case)
tblABC 11628326 ttblABC 255 --255 is for every text or image column
*/
GO
DROP TABLE tblABC
GO
October 30, 2006 at 11:22 am
Here's the link to download the system tables map that Colin references if you do not have the CD: http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx
No DBA should be without it !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 30, 2006 at 11:33 am
Thanx for the link... been wanting to send it to a few users but I never had time to search for it!!!
Is it a revised version from the CD version (_rev)?
October 30, 2006 at 11:39 am
They must have revised it in the last month or two is my guess. I still have the original download from the MS web site (it's about 4-5 years old !!!). They have moved the URL for this useful bit of information about 3 times in the last 2 years.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply