Set a DateTime database field to "Now"

In SQL you need to use GETDATE():

UPDATE table SET date = GETDATE();

There is no NOW() function.

---

To answer your question:

In a large table, since the function is evaluated for each row, you will end up getting different values for the updated field.

So, if your requirement is to set it all to the same date I would do something like this (untested):

DECLARE @currDate DATETIME;
SET @currDate = GETDATE();

UPDATE table SET date = @currDate;

An alternative to GETDATE() is CURRENT_TIMESTAMP. Does the exact same thing.

Use GETDATE()

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

UPDATE table SET date = GETDATE()