DECLARE @CID int
SET @CID =(Select CID from Customers)
SELECT @CID = (Select CID from Customers)
Both work well as far as a single row is being returned. When multiple rows are returned, the SET fails , whereas SELECT is the way to go. If the query returns nothing, SET assigns NULL as the value of the variable, whereas SELECT ignores it. SELECT is also capable of making multiple assignments.
Usually when a single value is to be assigned to a variable, go in for SET. However if you are setting the value using a query, as shown above, then its better to go in for SELECT as the rows returned 'could' be more than one, which can be handled by SELECT but not SET.
Another difference is that SET is ANSI standard, SELECT is not.
As a rule of thumb, I go ahead with SELECT when I have to set multiple variables. Else if I have to only set one variable, I am better off with SET.
Here's a good read about SET VS SELECT performance