Do non-relevant columns affect query time of select statements?

It depends, on the table structure and the available indexes.

• Case A: Common (rowstore) table, no index on (order_value).

  The only possible execution plan is to read the whole table (which is of course much different when it's 2 vs 200 columns, so a few vs a few thousand bytes wide).

• Case B: Common table, there is an index on (order_value) or some other indexes that include that column.

  There is a better plan now, scan the whole index (one of them) - which is of course much more narrow than the whole table, just a few bytes. Which makes irrelevant if the table has 2 or 200 columns. Only the index is scanned.

• Case C: It's a columnstore table.

  As the name implies, the structure of these tables are column-wise oriented, not row-wise. There is no need for any index, the table design itself is suited for reading entire columns.

This really depends on indexes and data types.

Using the Stack Overflow database as an example, this is what the Users table looks like:

It has a PK/CX on the Id column. So it's the entirety of the table data sorted by Id.

With that as the only index, SQL has to read that whole thing (sans the LOB columns) into memory if it's not already there.

DBCC DROPCLEANBUFFERS-- Don't run this anywhere near prod.

SET STATISTICS TIME, IO ON 

SELECT u.Id
INTO  #crap1
FROM dbo.Users AS u

The stats time and io profile looks like this:

Table 'Users'. Scan count 7, logical reads 80846, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2406 ms,  elapsed time = 446 ms.

If I add an additional nonclustered index on just Id

CREATE INDEX ix_whatever ON dbo.Users (Id)

I now have a much smaller index that satisfies my query.

DBCC DROPCLEANBUFFERS-- Don't run this anywhere near prod.

SELECT u.Id
INTO  #crap2
FROM dbo.Users AS u

The profile here:

Table 'Users'. Scan count 7, logical reads 6587, physical reads 0, read-ahead reads 6549, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2344 ms,  elapsed time = 384 ms.

We're able to do far fewer reads and save a little CPU time.

Without more information about your table definition, I can't really try to reproduce what you're trying to measure any better.

But you're saying that unless there is a specific index on that lone column, the other columns/ fields will also be scanned? Is this just a drawback inherent to the design of rowstore tables? Why would irrelevant fields be scanned?

Yes, this is specific to rowstore tables. Data is stored by the row on data pages. Even if other data on the page is irrelevant to your query, that whole row > page > index needs to be read into memory. I wouldn't say that the other columns are "scanned" so much as the pages they exist on are scanned to retrieve the single value on them relevant to the query.

Using the ol' phonebook example: even if you're just reading phone numbers, when you turn the page, you're turning last name, first name, address, etc along with the phone number.