1

Nice to Meet you

Another program is using that SQL to load data, But DB Server more than 1TB.

Last SQL sentence use to " ORDER BY" loading time is too long How To sort in ascending order by timestamp without "ORDER BY" in SQL?

SELECT timestamp
, node
, effect_ion
, effect_value
, priority

FROM   "--(*vendor(PML),product(LogServer) Global Event Log @Global *)--"

WHERE   (timestamp >= DATEADD(DD,-6,GETDATE()))
And
(priority BETWEEN  '253' AND '255')


ORDER BY timestamp DESC
T. Peter
  • 887
  • 4
  • 13
이건우
  • 21
  • 2

2 Answers2

1

You asked:

How To sort in ascending order by timestamp without "ORDER BY" in SQL?

You don't. But, you can try adding the following index, which, if used, might let SQL do the ORDER BY sort much faster:

CREATE INDEX idx ON yourTable (priority, timestamp);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • For which database? In Oracle it will not be used. – astentx Feb 23 '21 at 05:57
  • @astentx The syntax used by the OP is clearly SQL Server. As to whether it would use this index, that would depend on the data and a few other things. Certainly, there are databases out there which could use this index. – Tim Biegeleisen Feb 23 '21 at 05:59
  • 1
    OP is new to indexing and your answer should be more detailed and if possible should have some reference links. – Singh Feb 23 '21 at 06:11
  • @Singh No, doing a full background explanation on how indexing works is too broad, and, if my answer really needed that, then the question should probably be closed as too broad. – Tim Biegeleisen Feb 23 '21 at 06:12
  • Agreed that there is no single holy grail that can cover everything about indexes but still it will be better if you could have a brief explanation and some keywords like INDEX, CLUSTERED and etc and at least a ref link from where the OP can start to explore the indexes. – Singh Feb 23 '21 at 06:18
  • Even I'm fairly new to indexes but as a beginner, I can suggest that these type of things in an answer helps a lot. – Singh Feb 23 '21 at 06:19
-2

CLUSTER INDEXES: A clustered index defines the order in which data is physically stored in a table.

So using this you can define the order by which the data will be stored, based on a particular column.

By default, the PRIMARY KEY creates an INDEX which causes the data to be stored in ASC order of Id (PK) column. But as it turns out this will NOT guarantee any sort order (reference links in comments).

MS DOC for creation

CREATE CLUSTERED INDEX <index_name>
ON <table_name>(<column_name> ASC/DESC)

In order to check the INDEXES for a table you can use

EXEC sp_helpindex table_name

PS: Please read more/discuss with someone in your team before creating INDEXES as it affects the performance of the queries.

Singh
  • 163
  • 1
  • 8
  • 1
    A clustered index will **NOT** guarantee any sort order. –  Feb 23 '21 at 06:30
  • As far as I know (which is not that far) the table can have at max a single clustered index and 'A clustered index defines the order in which data is physically stored in a table.' If ^ the information is incorrect, can you tell the reason why it won't GUARANTEE sorting. – Singh Feb 23 '21 at 06:34
  • 4
    The **only** way to _guarantee_ a specific sort order is to use `ORDER BY`. See [here](https://stackoverflow.com/a/1110365) or [here](https://stackoverflow.com/questions/824855/does-select-always-order-by-primary-key) or [here](https://stackoverflow.com/a/20050403) –  Feb 23 '21 at 07:00
  • 1
    @Singh, here's more info in why [ORDER BY is required](https://www.dbdelta.com/order-by-is-required/). – Dan Guzman Feb 23 '21 at 11:12
  • Answers, like questions, should still be useful and relevant if any links go dead. This is one reason why link-only posts are highly discouraged. In addition, an answer should make an attempt to explain why the content addresses the OP's question. Perhaps focus on your own answer first. – SMor Feb 23 '21 at 13:27