Yeah, this is one of the properties of SQL-Server: it's memory hungry and
has a tendancy to gooble all the memory it can to fill up its numerous
caches and buffers. This is by design in order to achieve the fastest
response time for any request that will follow. Usually not a real problem
because it should render most of it if there is another program that need it
but you can limit the behavior by imposing a limit on the memory used by
SQL-Server (look into its properties) or by setting the AutoClose propertie
of a database to ON (beware that using this last option will have an impact
on the CPU and I/O usages).
The amount of resources needed for creating a resultset and returning it to
the client over the wire in order to fill the recordset on the client side
is usually small in comparaison of what's needed to perform the request
itself on the SQL-Server; so the choice of the type of cursor (location,
amount of cache, use of a implicit temporary table) will have in many cases
little impact on the total memory used by SQL-Server (but can have a great
impact on the performance itself).
