1

Topic: SORT_IN_TEMPDB In meta data

Good afternoon...
Created an index with SORT_IN_TEMPDB = ON
I come into properties and I see False
I generate a script through CREATE AS... I see OFF
Through  I look as the server gets meta data and I do not see anywhere something similar on sort in tempdb
Whether it was possible to learn from meta data with what value of this parameter the index is created? And generally why at creation ON I see False.....

2

Re: SORT_IN_TEMPDB In meta data

X-Cite wrote:

Good afternoon...
Created an index with SORT_IN_TEMPDB = ON
I come into properties and I see False
I generate a script through CREATE AS... I see OFF
Through  I look as the server gets meta data and I do not see anywhere something similar on sort in tempdb
Whether it was possible to learn from meta data with what value of this parameter the index is created? And generally why at creation ON I see False.....

Create not cluster, then recreate cluster without instructions and after that check not cluster?
"The SORT_IN_TEMPDB option affects only the current statement. No metadata records that the index was or was not sorted in tempdb. For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the Database Engine does not use the option when it re-creates the nonclustered index ."
Or right after creations not cluster check?

3

Re: SORT_IN_TEMPDB In meta data

X-Cite;
How I understand - the version of the server 2014 RTM?

4

Re: SORT_IN_TEMPDB In meta data

X-Cite wrote:

I Generate a script through CREATE AS... I see OFF

Well generally it not a bug, is a feature. An option "sort_in_tempdb"  only at the moment of index creation. At re-creation it is necessary to put again hands.

5

Re: SORT_IN_TEMPDB In meta data

Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64)
Or right after creations not cluster check?
At once created and at once I look.
Salt in that I write a script on transfer COLLATE from one to another in all tables.
For this business I write a script which deletes dependent indexes from string fields and recovers reversely with the same in parameters.
From your comment No metadata records that the index was or was not sorted in tempdb as I understood to recover this parameter it is impossible.

6

Re: SORT_IN_TEMPDB In meta data

X-Cite;
about 13 Years ago considered

7

Re: SORT_IN_TEMPDB In meta data

All drove...
Then in what sense of it? It also misleads me...
If there is a parameter in properties it is logical that it is in meta data...
Same concerns both ONLINE and MAXDOP, what for they in properties carried out it...

8

Re: SORT_IN_TEMPDB In meta data

X-Cite wrote:

Then in what sense of it?

It is studio (SSMS). Klietsky  on operation with bases and . Its sense not in was a logical software, and in working as a hammer. Took, without reflecting, and drive in nails.
These properties - a heavy heritage of the Soviet mode of OOP,  in process of creation SSMS and other tools. Well is, and another practically and is not present.