CODEWITHSUNDEEP
sql-serverdatabasestored-procedures
Annabele
Annabele

Reputation: 13

Trying to understand stored procedure behavior

I'm tired of searching for this, but I couldn't find anything.

I have three databases in SQL Server and although all stored procedures are in the Main database, they work with tables from the other databases.

My question is: if you have the query 

select name 
from SecondDatabase.dbo.SomeTable 
where id = 56

and this query is stored in the main database, will it run in the main database and go all the way to the second database and returns the data, or will it run in the second database and you have the select result directly?

(hope you understand my question)

Upvotes: 1

Views: 119

Answers (3)

Alexander Volok
Alexander Volok

Reputation: 5940

use FirstDatabase
select name 
from SecondDatabase.dbo.SomeTable 
where id = 56

Question:

will it run in the main database and go all the way to the second database and returns the data, or will it run in the second database and you have the select result directly?

Your first assumption is correct:

This query will run in a first database, it will use context and all settings (ANSI, query optimizer and statistic related) of the first database but will get data from a table of the second database.

Just an example from a life: if database have to stay in an old compatibility mode, but new T-SQL features need occasionally to be used, query can switch context to tempdb (which normally set to the latest compatibility level) and run queries referencing data from any other database where access is granted. Usage of those new features will not raise exception

Upvotes: 0

EzLo
EzLo

Reputation: 14189

I think you are misunderstanding the difference between a Database and an Instance.

An instance is the software running the SQL service. Each instance can have multiple databases. For example, there is a master database and a tempdb database for each instance of SQL Server, these are system databases. You can create any number of user databases. All these databases will be handled by the same SQL Server instance (on the same machine).

A particular client session is connected first to an instance and then to a particular database, thats why you include which database you will connect to by default on connection strings (or by login). When you write select name from SecondDatabase.dbo.SomeTable, you are telling the SQL service to retrieve data from the SecondDatabase, even if your session is linked to any other database. The engine will then use your login credential to match a user of the other database (since users go by database and logins by instance) to validate if it has enough privileges to query that table, before searching for the data.

A complete different story would be trying to access data from another instance (machine), in which you will need a linked server, a openrowset or such.

Upvotes: 1

Igor
Igor

Reputation: 62213

The (now edited) query above will always execute on SecondDatabase.dbo.SomeTable even if the active database context was another database and even if the active user had a different default schema. This is because the object SomeTable is qualified with the schema and the schema owner.

Test to illustrate that the following still returns the executed results (assuming the objects exist and the active user context has access to them)

USE [OtherDatabaseSchema]
GO

SELECT TOP 10 *
FROM [SecondDatabase].[dbo].[SomeTable]

Upvotes: 0

Related Questions