-1

I read this post What does ON [PRIMARY] mean?

and I wonder how I can get the text "PRIMARY" or any other file group names in use by using SQL scripts. I also look into the syntax of CREATE table and see SORT_IN_TEMPDB and try searching for it with queries in MS SMS but can't find where it is either.

Black cowgirl
  • 59
  • 3
  • I will be voted down till banned. please help me quick before I go. :( – Black cowgirl Jun 07 '17 at 11:50
  • please clarify your question, it is not clear, what are you looking for, you wanna see word "Primary" in SSMS ? is it your target ? - voted it up for deleting voted down. .. keeping you between us. – ahmed abdelqader Jun 07 '17 at 11:55
  • Thank you. Yes I'd like to know which view is storing the information of my database table that I can use to output the word "Primary" or "Your table is on Primary". – Black cowgirl Jun 07 '17 at 12:06
  • `SORT_IN_TEMPDB` is not a property of an index and does not persist; it must be specified on every `CREATE` or `ALTER` statement and is specific to that particular execution. – Jeroen Mostert Jun 07 '17 at 12:44

1 Answers1

0
  1. select OBJECT_NAME(p.object_id) as obj_name, fg.name as fg 
from sys.partitions p inner join sys.allocation_units au 
        on au.container_id = p.hobt_id 
     inner join sys.filegroups fg 
        on fg.data_space_id = au.data_space_id

  2. SORT_IN_TEMPDB is the CREATE INDEX option: CREATE INDEX (Transact-SQL)

SORT_IN_TEMPDB = { ON | OFF } Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Specifies whether to store temporary sort results in tempdb. The default is OFF. ON The intermediate sort results that are used to build the index are stored in tempdb. This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. However, this increases the amount of disk space that is used during the index build.

sepupic
  • 8,409
  • 1
  • 9
  • 20