This might be a simple question, but it seems that I've read about exceptions to this... I imagine that most of us don't issue SQL commands or start processes on the server itself, but from a PC (or workstation). So if you are using a PC, how much of the work is done, for example, on the SQL Server as opposed to the PC when SQL processes are running? Is a stored procedure always processed 100% on the server? What about other SQL Server tasks?
T-SQL processes are always run on the server. The client's workload involves displaying the output of that work. Some tasks, such as data import via right click take place on both the client and the server. The processing of the input happens on the client side, with the actual insert activity taking place on the server. The same is true of running SSIS packages from BIDS, which can be heavily client intensive, depending on the tasks within it. Other than that, most everything that take place in SSMS happens on the server with SSMS providing the pretty UI with progress indicators and the like.
All query parsing, compiling and processing is done on the server. The client tool such as SSMS is only responsible to connect to the server, send the query to it and display results in the results window of the client tool after the results have been sent by the server and recieved by the tool over network. In other words, the whole process is akin to so-called pass-through query processing in Access. Suppose I have a text file named C:\\Useless\\Temp\\
Sample.txt. If I connect to my local server and execute select t.BulkColumn from openrowset(bulk 'C:\Useless\Temp\
sample.txt', single_clob) t; then I get the contents of the file displayed in the results window, but if I connect to a different server and execute the query above then I get an error stating that the file could not be found. This of course strongly hints that the query is executed in the server process space. Similarly, if you link Access database to your SQL Server then you can write T-SQL queries against it, but if you use openrowset then you cannot use T-SQL but have to opt for Access flavour of SQL instead. If you need to backup the database then you specify the path to the dump relative to the server. If you write a console app used to connect to the server and execute some procedure, the box on which such app is running does not really have to have anything related to the SQL Server installed, because all that app does is it send the (pass-through) to the server for processing in that server's process space. In short, yes, everything is processed on the server but the results are received, possibly formatted and displayed on the client. Oleg