Sql Server memory management is a very broad topic, but there are some things mentioned here that should be addressed.
I'm going to assume you are looking at Task Manager to get what you are referring to as the Page File Usage. This is NOT the size of your physical page file or the amount of physical page file space used - this is what is referred to as the commit charge, which is basically the potential amount of total physical page file space that would be required to hold all VAS (VAS and physical memory are not the same thing). If you want to see the actual page file usage, view the PagingFile:%Usage perfmon counter - it will almost certainly be a very small percentage. The total size of your commit charge (i.e. what Task Manager will display as PF Usage) is basically the sum of all possible VAS (i.e. all page file space + total physical memory on the system).
Sql Server does NOT override the memory management facilities of the OS - it uses the same windows-provided memory APIs as any user-mode application (primarily the VirtualAlloc family and the AWE family, which still is used on 64bit systems), . Sql Server does indeed have a subsystem dedicated to managing it's own memory utilization, however this subsystem does nothing special that isn't available to any user-mode application in terms of requesting, receiving, committing, etc. memory from the underlying OS.
Like others have mentioned, Sql Server is definitely designed to use as much memory as possible based on system/user-defined constraints. However, it will grow into this memory footprint based on the utilization of the system (i.e. it will reserve a relatively small amount of memory at startup and continue requesting/reserving from the OS as it needs). It is also designed to (and fairly good at) respond to OS memory pressure as necessary, and to also keep it's reserved memory footprint until/unless the OS "requests" it back (for a more complete understanding of this, see this blog and this blog and this blog).
If you want to force Sql Server to release/shrink it's current memory footprint, you can simply tell it to do so (assuming you are running Sql 2005 and above) by lowering the value specified in the "max server memory" system configuration and running a reconfigure. There are certainly limitations to this (i.e. this configuration only limits the buffer pool, however this is by far the largest memory consumer). This is NOT a connection-level setting as alluded to elsewhere, it is a system-wide setting (There are memory-related connection-level settings, however nothing that would have anything to do with this question). If you are running Sql 2000, you'll need to make the adjustment and then recycle the Sql Service - NOTE that on Sql 2000 memory management was much, much different in almost every way (and would likely require a separate question/discussion).
I would strongly encourage you to read through a couple of blogs to get an understanding for basic windows and sql server memory management, shouldn't take too long and is well worth the knowledge when trying to understand what is going on in situations like this.
