[sql-server-2008] What does "select 1 from" do?

I read some articles but really didn't understand what does select 1 from do? Someone says "you should use select 1 instead of select *". Here is an example table:

cust_id     cust_name       cust_address

1000000001  Village Toys    Mapl
1000000002  Kids Place      South
1000000003  Fun4All         Sunny
1000000004  Fun4All         Riverside
1000000005  The Toy Store   53rd

What will the result be when I write select 1 from customer_table what does this statement do?

The construction is usually used in "existence" checks

if exists(select 1 from customer_table where customer = 'xxx')

or

if exists(select * from customer_table where customer = 'xxx')

Both constructions are equivalent. In the past people said the select * was better because the query governor would then use the best indexed column. This has been proven not true.

It does what you ask, SELECT 1 FROM table will SELECT (return) a 1 for every row in that table, if there were 3 rows in the table you would get

1
1
1

Take a look at Count(*) vs Count(1) which may be the issue you were described.

The statement SELECT 1 FROM SomeTable just returns a column containing the value 1 for each row in your table. If you add another column in, e.g. SELECT 1, cust_name FROM SomeTable then it makes it a little clearer:

            cust_name
----------- ---------------
1           Village Toys
1           Kids Place
1           Fun4All
1           Fun4All
1           The Toy Store

SELECT COUNT(*) in EXISTS/NOT EXISTS

EXISTS(SELECT CCOUNT(*) FROM TABLE_NAME WHERE CONDITIONS) - the EXISTS condition will always return true irrespective of CONDITIONS are met or not.

NOT EXISTS(SELECT CCOUNT(*) FROM TABLE_NAME WHERE CONDITIONS) - the NOT EXISTS condition will always return false irrespective of CONDITIONS are met or not.

SELECT COUNT 1 in EXISTS/NOT EXISTS

EXISTS(SELECT CCOUNT 1 FROM TABLE_NAME WHERE CONDITIONS) - the EXISTS condition will return true if CONDITIONS are met. Else false.

NOT EXISTS(SELECT CCOUNT 1 FROM TABLE_NAME WHERE CONDITIONS) - the NOT EXISTS condition will return false if CONDITIONS are met. Else true.