Generate sequence number using ROW_NUMBER() without sorting the actual column

As we know about Ranking functions to return a ranking/sequence value for each row in a partition based on a specific/set of column(s). Right ?

Let me define a table and values

If Object_Id('dbo.SequenceData') Is Null

Create Table [dbo].SequenceData
(
Col1 Varchar(5)
)
Go

TRUNCATE TABLE [dbo].SequenceData

Go

Insert [dbo].SequenceData
Select 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go

SELECT * FROM [dbo].SequenceData
Go

let me generate a sequence number for the value

SELECT ROW_NUMBER() OVER(),* FROM [dbo].SequenceData

GO

Msg 4112, Level 15, State 1, Line 31

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

But, It's not working, since we need ORDER BY clause specified as per the syntax. right ?

So. let me include ORDER BY clause and see...

SELECT ROW_NUMBER() OVER(ORDER BY Col1),* FROM [dbo].SequenceData

GO

No..No..No, My actual column "Col1" value should not be Sorted. What should we do ?

Let us try something like this

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),* FROM [dbo].SequenceData

GO

We have specified a virtual column in ORDER BY clause which will return always NULL. So, The actual data will not be sorted.

We get a sequence for the column value without sorting it - "AS IS"

DAC or Normally logged-in ?

How do I know whether I have logged-In as DAC or Normally logged-In ?

SELECT 'You have connected as "DAC"' [Who you are] 
FROM sys.dm_exec_sessions s join sys.endpoints  e
ON (s.endpoint_id = e.endpoint_id) 
WHERE e.name ='Dedicated Admin Connection'
AND s.session_id = @@spid
GO

1. Connect with Instance using SQLCMD by Trusted Connection

2. Paste the above script and execute it

Connect normally (Trusted Connection) using SQLCMD :-

Connect as DAC using SQLCMD :-