A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.
A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).
The manual explains it as follows:
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.
The manual explains how indexes are used in general: here.
In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.
Untested, but I believe the MSSQL equivalent is:
CREATE TABLE tmp (
id int NOT NULL PRIMARY KEY IDENTITY,
uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,
name varchar(255) NOT NULL,
tag int NOT NULL DEFAULT 0,
description varchar(255),
);
CREATE INDEX idx_name ON tmp (name);
CREATE INDEX idx_tag ON tmp (tag);
Edit: the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).