Will Microsoft SQL Server handle an uninstalled calculated column efficiently?

Question

I find it difficult to formulate this question in a way that does not show results for constant, indexed computed columns.

My question is: if I have a table, for example:

CREATE TABLE Customers ( ID int, Name nvarchar(50), Balance money, HasBalance AS CONVERT(bit, CASE WHEN Balance > 0 THEN 1 ELSE 0 END) ) 

Assuming there is an index on the Balance, the SQL query processor would efficiently process the query, for example:

 SELECT ID, Name, Balance FROM Customers WHERE HasBalance = 1 

Will it "embed" the calculated column expression as if I had specified the case directly in the query?

How about if there was a computed column expression in a user function other than schemabound?

EDIT

My example was not great, because, as already noted, the HasBalance column will not have very good data distribution. But ignoring for a moment the efficiency of the index itself, will the request handler mainly process the above request, as shown below, when choosing an index and choosing a execution plan?

 SELECT ID, Name, Balance FROM Customers WHERE Balance > 0 

Answer 1

It depends on the distribution of the data, now you have only 2 possible values ​​1 and 0 .... so if you do not have 99% of the data being one value, your selectivity will be very poor, then it should scan the entire index so that find all positive or negative values

Edit ..... That's what happens, you get a table scan

 CREATE TABLE Customers ( ID int, Name nvarchar(50), Balance money, HasBalance AS CONVERT(bit, CASE WHEN Balance > 0 THEN 1 ELSE 0 END) ) insert Customers values(1,'d',100) insert Customers values(2,'d',-2) insert Customers values(3,'d',-4) insert Customers values(4,'d',3) insert Customers values(5,'d',5) create index ix_test on Customers(Balance) SELECT ID, Name, Balance FROM Customers WHERE HasBalance = 0 set showplan_text on 

| --Table Scan (OBJECT: ([master]. [Dbo]. [Clients]), WHERE: (CONVERT (bits, CASE WHEN [master]. [Dbo]. [Clients]. [Balance]> ($ 0.0000) THEN (1) ELSE (0) END, 0) = [@ 1]))

And take a look at it

 SELECT Balance FROM Customers WHERE HasBalance = 0 

- Scanning indexes (OBJECT: ([master]. [Dbo]. [Clients]. [Ix_test]), WHERE: (CONVERT (bits, CASE WHEN [master]. [Dbo]. [Clients]. [Balance]>> $ 0.0000) THEN (1) ELSE (0) END, 0) = [@ 1]))

 SELECT Balance FROM Customers WHERE Balance > 0 

| - Index search (OBJECT: ([master]. [Dbo]. [Clients]. [Ix_test]), SEEK: ([master]. [Dbo]. [Clients]. [Balance]> CONVERT_IMPLICIT (money, [@ 1 ], 0)) ORDERS FORWARD)