Varchar(N) for N 1..8000 can be stored 'in-page', that means with the rest of the row. Over that, it has to be stored in a list of LOB pages (each 8k in size), so once you go over 8000 characters there's no limit other than the maxint (int being the data type that stores the length), that's roughly 2 billion, so 2GB.