what are these objects prefixed with ''t''
-
use Northwind
select * from sysindexes where name = 'tCategories'
I am just using Northwind so that everyone can easily see,
but run this query and you will see this in all databases
select *
from sysindexes
where name = 't' + object_name( id )
-
I believe those are entries for tables that contain text or image data.
Greg
Greg
-
Aha! thank you very much Greg!
for the benefit of others:
create table PrefixCreator1(f1 text)
create table PrefixCreator2(f1 image)
create table PrefixCreator3(f1 text, f2 image)
select *
from sysindexes
where name = 't' + object_name( id )
http://msdn2.microsoft.com/en-us/library/aa174534(SQL.80).aspx
-
using a query to highlight which indexes are in need of a quick defrag etc which is based around
DBCC SHOWCONTIG (Categories) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
go
the results include indexname = tCategories, yet if you try
DBCC INDEXDEFRAG (Northwind, Categories, tCategories)
GO
it returns
Server: Msg 2560, Level 16, State 1, Line 1
Parameter 3 is incorrect for this DBCC statement.
Are these t prefixed indexes "real" indexes in the sense they can be defragged/rebuilt etc using dbcc commands ?
many thanks Si
-
The "t" prefixed "indexes" are not "real" indexes... they are merely notations to the server that the table contains image or text datatypes...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Many thanks, Ive not got to work out the best way to exclude them when Im using the following syntax to determine which indexes need defraging
CREATE TABLE #index
(
Table_Name varchar(255),
Table_ID int,
Index_Name varchar(255),
Index_ID int,
Index_Level int,
Page_Count int,
Row_Count int,
Minimum_Record_Size int,
Maximum_Record_Size int,
Average_Record_Size int,
Forwarded_Record_Count int,
Extents int,
Extent_Switches int,
Average_Free_Bytes int,
Average_Page_Density int,
Scan_Density decimal,
Best_Count int,
Actual_Count int,
Logical_Fragmentation decimal,
Extent_Fragmentation decimal
)
-- declare and open a cursor on the list of user table names
DECLARE table_cursor CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME 'dtproperties'
OPEN table_cursor
-- get index information for each table
DECLARE @table_name varchar(128)
FETCH NEXT FROM table_cursor INTO @table_name
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- use the DBCC command to get index fragmentation info
INSERT
INTO #index
EXEC( 'DBCC SHOWCONTIG ( ' + @table_name + ' )
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
-- get the next table
FETCH NEXT FROM table_cursor INTO @table_name
END
-- close and deallocate the cursor
CLOSE table_cursor
DEALLOCATE table_cursor
-- output a result set of each index with its page count and logical
-- fragmentation
SELECT #index.Table_Name,
#index.Index_Name,
#index.Page_Count,
#index.Logical_Fragmentation
FROM #index
--where #index.Logical_Fragmentation >30
ORDER BY #index.Logical_Fragmentation DESC
-- all ok
RETURN( 0 )
GO
-
Oh noo, no, no.... go look at Master.dbo.sp_SpaceUsed and "steal" code from that... do it in a set based manner so that you can create a view like an Information_Schema view.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply