Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Truncate table Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 9:15 AM
Points: 22, Visits: 33

Clustered indexes are stored within the referenced table and not in memory. Memory is volatile so if the indexes were stored there they would have to rebuilt every time the server was started anew!

Yes I know that I was talking about non clustered indexes and not clustered one!!
Post #1434932
Posted Monday, March 25, 2013 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 2:51 AM
Points: 41, Visits: 227
select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'TableName')

using this query u can check the index that is exist on your table.


Regards,
Arjun
Post #1434938
Posted Monday, March 25, 2013 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
OK, I didn't read your question properly so sorry about that.

That said......

Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions. This information is stored on disk and not in memory so it still makes your question about information stored in memory moot.

Hope that helps!
Post #1434939
Posted Monday, March 25, 2013 9:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
kevaburg (3/25/2013)
Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions.


The view definition is in the system resource database. The data that the view reads is in the system tables of the user database and hence is stored in the specific user database, since views don't store information.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1434967
Posted Monday, March 25, 2013 9:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
GilaMonster (3/25/2013)
kevaburg (3/25/2013)
Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions.


The view definition is in the system resource database. The data that the view reads is in the system tables of the user database and hence is stored in the specific user database, since views don't store information.


Thanks for that Gail......a quick slip of the mind....
Post #1434971
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse