Microsoft SQL Server: Difference between revisions

Revision as of 21:52, 18 August 2025

Revision as of 21:53, 18 August 2025

Line 119:

The main mode of retrieving data from a SQL Server database is [[database query|querying]] for it. The query is expressed using a variant of [[SQL]] called [[T-SQL]], a dialect Microsoft SQL Server shares with [[Adaptive Server Enterprise|Sybase SQL Server]] due to its legacy. The query [[declarative programming language|declaratively]] specifies what is to be retrieved. It is processed by the query processor, which figures out the sequence of steps that will be necessary to retrieve the requested data. The sequence of actions necessary to execute a query is called a [[query plan]]. There might be multiple ways to process the same query. For example, for a query that contains a [[join (SQL)|join]] statement and a [[select (SQL)|select]] statement, executing join on both the tables and then executing select on the results would give the same result as selecting from each table and then executing the join, but result in different execution plans. In such case, SQL Server chooses the plan that is expected to yield the results in the shortest possible time. This is called [[query optimization]] and is performed by the query processor itself.<ref name="storageengine" />

SQL Server includes a cost-based query optimizer which tries to optimize on the cost, in terms of the resources, it will take to execute the query. Given a query, then the query optimizer looks at the [[database schema]], the database statistics and the system load at that time. It then decides which sequence to access the tables referred in the query, which sequence to execute the operations and what access method to be used to access the tables. For example, if the table has an associated index, whether the index should be used or not: if the index is on a column which is not unique for most of the columns (low "selectivity"), it might not be worthwhile to use the index to access the data. Finally, it decides whether to execute the query [[concurrent computing|concurrently]] or not. While a concurrent execution is more costly in terms of total processor time, because the execution is actually split to different processors might mean it will execute faster. Once a query plan is generated for a query, it is temporarily cached. For further invocations of the same query, the cached plan is used. Unused plans are discarded after some time.<ref name="storageengine" /><ref name="Single SQL Statement Processing" />

SQL Server also allows [[stored procedure]]s to be defined. Stored procedures are parameterized T-SQL queries, that are stored in the server itself (and not issued by the client application as is the case with general queries). Stored procedures can accept values sent by the client as input parameters, and send back results as output parameters. They can call defined functions, and other stored procedures, including the same stored procedure (up to a set number of times). They can be [[access control|selectively provided access to]]. Unlike other queries, stored procedures have an associated name, which is used at runtime to resolve into the actual queries. Also because the code need not be sent from the client every time (as it can be accessed by name), it reduces network traffic and somewhat improves performance.<ref name="Stored Procedure Basics" /> Execution plans for stored procedures are also cached as necessary.