103-405: SQL Server may refer to: Microsoft SQL Server , a relational database server from Microsoft Sybase SQL Server , a relational database server developed by Sybase SQL Server Pro , a trade publication and web site owned by Penton Media Topics referred to by the same term [REDACTED] This disambiguation page lists articles associated with
206-409: A .ndf extension, are used to allow the data of a single database to be spread across more than one file, and optionally across more than one file system. Log files are identified with the .ldf extension. Storage space allocated to a database is divided into sequentially numbered pages , each 8 KB in size. A page is the basic unit of I/O for SQL Server operations. A page is marked with
309-405: A .sql file, and are used either for management of databases or to create the database schema during the deployment of a database. SQLCMD was introduced with SQL Server 2005 and has continued through SQL Server versions 2008, 2008 R2, 2012, 2014, 2016 and 2019. Its predecessor for earlier versions was OSQL and ISQL, which were functionally equivalent as it pertains to T-SQL execution, and many of
412-457: A 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. The page type defines the data contained in the page. This data includes: data stored in the database, an index, an allocation map, which holds information about how pages are allocated to tables and indexes; and a change map which holds information about
515-564: A Microsoft-defined format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server, TDS packets can be encased in other physical transport dependent protocols, including TCP/IP , named pipes , and shared memory . Consequently, access to SQL Server
618-475: A bit pattern to each character , digit , or multimedia object. Many standards exist for encoding (e.g. character encodings like ASCII , image encodings like JPEG , and video encodings like MPEG-4 ). By adding bits to each encoded unit, redundancy allows the computer to detect errors in coded data and correct them based on mathematical algorithms. Errors generally occur in low probabilities due to random bit value flipping, or "physical bit fatigue", loss of
721-676: A component named SQL CLR ("Common Language Runtime") via which it integrates with .NET Framework . Unlike most other applications that use .NET Framework, SQL Server itself hosts the .NET Framework runtime , i.e., memory, threading and resource management requirements of .NET Framework are satisfied by SQLOS itself, rather than the underlying Windows operating system. SQLOS provides deadlock detection and resolution services for .NET code as well. With SQL CLR, stored procedures and triggers can be written in any managed .NET language , including C# and VB.NET . Managed code can also be used to define UDT's ( user defined types ), which can persist in
824-497: A computer a brief window of time to move information from primary volatile storage into non-volatile storage before the batteries are exhausted. Some systems, for example EMC Symmetrix , have integrated batteries that maintain volatile storage for several minutes. Utilities such as hdparm and sar can be used to measure IO performance in Linux. Full disk encryption , volume and virtual disk encryption, andor file/folder encryption
927-411: A database can also contain other objects including views , stored procedures , indexes and constraints , along with a transaction log. A SQL Server database can contain a maximum of 2 objects, and can span multiple OS-level files with a maximum file size of 2 bytes (1 exabyte). The data in the database are stored in primary data files with an extension .mdf . Secondary data files, identified with
1030-424: A database) to represent a string of bits by a shorter bit string ("compress") and reconstruct the original string ("decompress") when needed. This utilizes substantially less storage (tens of percent) for many types of data at the cost of more computation (compress and decompress when needed). Analysis of the trade-off between storage cost saving and costs of related computations and possible delays in data availability
1133-702: A drive. When the computer has finished reading the information, the robotic arm will return the medium to its place in the library. Tertiary storage is also known as nearline storage because it is "near to online". The formal distinction between online, nearline, and offline storage is: For example, always-on spinning hard disk drives are online storage, while spinning drives that spin down automatically, such as in massive arrays of idle disks ( MAID ), are nearline storage. Removable media such as tape cartridges that can be automatically loaded, as in tape libraries , are nearline storage, while tape cartridges that must be manually loaded are offline storage. Off-line storage
SECTION 10
#17327808297951236-718: A file in the filesystem. Notification Services was discontinued by Microsoft with the release of SQL Server 2008 in August 2008, and is no longer an officially supported component of the SQL Server database platform. SQL Server Integration Services (SSIS) provides ETL capabilities for SQL Server for data import , data integration and data warehousing needs. Integration Services includes GUI tools to build workflows such as extracting data from various sources, querying data, transforming data—including aggregation, de-duplication, de-/normalization and merging of data—and then exporting
1339-491: A minimum, 2.0 GHz or faster is recommended. The current version is Microsoft SQL Server 2022, released November 16, 2022. The RTM version is 16.0.1000.6. Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are: Tools published by Microsoft include: The protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via
1442-508: A new database, alter any existing database schema by adding or modifying tables and indexes, or analyze performance. It includes the query windows which provide a GUI based interface to write and execute queries. Azure Data Studio is a cross platform query editor available as an optional download. The tool allows users to write queries; export query results; commit SQL scripts to Git repositories and perform basic server diagnostics. Azure Data Studio supports Windows, Mac and Linux systems. It
1545-461: A part of the database engine, provides a reliable messaging and message queuing platform for SQL Server applications. Service broker services consists of the following parts: The message type defines the data format used for the message. This can be an XML object, plain text or binary data, as well as a null message body for notifications. The contract defines which messages are used in an conversation between services and who can put messages in
1648-400: 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
1751-435: A rank of their accurateness is computed. The results are returned to the client via the SQL Server process. SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as
1854-400: A relatively simple processor may keep state between successive computations to build up complex procedural results. Most modern computers are von Neumann machines. A modern digital computer represents data using the binary numeral system . Text, numbers, pictures, audio, and nearly any other form of information can be converted into a string of bits , or binary digits, each of which has
1957-472: A remote SQL server and push the script executions to it, or they can run a R or Python scripts as an external script inside a T-SQL query. The trained machine learning model can be stored inside a database and used for scoring. Used inside an instance, programming environment. For cross-instance applications, Service Broker communicates over TCP/IP and allows the different components to be synchronized, via exchange of messages. The Service Broker, which runs as
2060-464: A single database session. SQL Server Native Client is used under the hood by SQL Server plug-ins for other data access technologies, including ADO or OLE DB . The SQL Server Native Client can also be directly used, bypassing the generic data access layers. On November 28, 2011, a preview release of the SQL Server ODBC driver for Linux was released. Microsoft SQL Server 2005 includes
2163-574: A source to read instructions from, in order to start the computer. Hence, non-volatile primary storage containing a small startup program ( BIOS ) is used to bootstrap the computer, that is, to read a larger program from non-volatile secondary storage to RAM and start to execute it. A non-volatile technology used for this purpose is called ROM, for read-only memory (the terminology may be somewhat confusing as most ROM types are also capable of random access ). Many types of "ROM" are not literally read only , as updates to them are possible; however it
SECTION 20
#17327808297952266-490: A value of 0 or 1. The most common unit of storage is the byte , equal to 8 bits. A piece of information can be handled by any computer or device whose storage space is large enough to accommodate the binary representation of the piece of information , or simply data . For example, the complete works of Shakespeare , about 1250 pages in print, can be stored in about five megabytes (40 million bits) with one byte per character. Data are encoded by assigning
2369-439: Is a GUI tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools that work with objects and features of the server. SQL Server Management Studio replaces Enterprise Manager as the primary management interface for Microsoft SQL Server since SQL Server 2005. A version of SQL Server Management Studio
2472-427: Is a form of volatile memory that also requires the stored information to be periodically reread and rewritten, or refreshed , otherwise it would vanish. Static random-access memory is a form of volatile memory similar to DRAM with the exception that it never needs to be refreshed as long as power is applied; it loses its content when the power supply is lost. An uninterruptible power supply (UPS) can be used to give
2575-425: Is a level below secondary storage. Typically, it involves a robotic mechanism which will mount (insert) and dismount removable mass storage media into a storage device according to the system's demands; such data are often copied to secondary storage before use. It is primarily used for archiving rarely accessed information since it is much slower than secondary storage (e.g. 5–60 seconds vs. 1–10 milliseconds). This
2678-433: Is a mechanism for generating data-driven notifications, which are sent to Notification Services subscribers. A subscriber registers for a specific event or transaction (which is registered on the database server as a trigger); when the event occurs, Notification Services can use one of three methods to send a message to the subscriber informing about the occurrence of the event. These methods include SMTP, SOAP, or by writing to
2781-691: Is a proprietary relational database management system developed by Microsoft . As a database server , it is a software product with the primary function of storing and retrieving data as requested by other software applications —which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users . The history of Microsoft SQL Server begins with
2884-456: Is a report generation environment for data gathered from SQL Server databases. It is administered via a web interface. Reporting services features a web services interface to support the development of custom reporting applications. Reports are created as RDL files. Reports can be designed using recent versions of Microsoft Visual Studio (Visual Studio.NET 2003, 2005, and 2008) with Business Intelligence Development Studio , installed or with
2987-410: Is a technology consisting of computer components and recording media that are used to retain digital data . It is a core function and fundamental component of computers. The central processing unit (CPU) of a computer is what manipulates data by performing computations. In practice, almost all computers use a storage hierarchy , which puts fast but expensive and small storage options close to
3090-526: Is also available for SQL Server Express Edition, for which it is known as SQL Server Management Studio Express (SSMSE). A central feature of SQL Server Management Studio is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server. It can be used to visually observe and analyze query plans and optimize the database performance, among others. SQL Server Management Studio can also be used to create
3193-933: Is available over these protocols. In addition, the SQL Server API is also exposed over web services . Data storage is a database , which is a collection of tables with typed columns. SQL Server supports different data types, including primitive types such as Integer , Float , Decimal , Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of data), Text (for textual data) among others. The rounding of floats to integers uses either Symmetric Arithmetic Rounding or Symmetric Round Down ( fix ) depending on arguments: SELECT Round(2.5, 0) gives 3. Microsoft SQL Server also allows user-defined composite types (UDTs) to be defined and used. It also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). In addition to tables,
SQL Server - Misplaced Pages Continue
3296-539: Is based on the Microsoft Visual Studio development environment but is customized with the SQL Server services-specific extensions and project types, including tools, controls and projects for reports (using Reporting Services), Cubes and data mining structures (using Analysis Services). For SQL Server 2012 and later, this IDE has been renamed SQL Server Data Tools (SSDT). Computer data storage Computer data storage or digital data storage
3399-410: Is computer data storage on a medium or a device that is not under the control of a processing unit . The medium is recorded, usually in a secondary or tertiary storage device, and then physically removed or disconnected. It must be inserted or connected by a human operator before a computer can access it again. Unlike tertiary storage, it cannot be accessed without human interaction. Off-line storage
3502-408: Is done before deciding whether to keep certain data compressed or not. For security reasons , certain types of data (e.g. credit card information) may be kept encrypted in storage to prevent the possibility of unauthorized information reconstruction from chunks of storage snapshots. Generally, the lower a storage is in the hierarchy, the lesser its bandwidth and the greater its access latency
3605-589: Is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksum when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime. SQL Server allows multiple clients to use the same database concurrently. As such, it needs to control concurrent access to shared data, to ensure data integrity—when multiple clients update
3708-609: Is estimable using S.M.A.R.T. diagnostic data that includes the hours of operation and the count of spin-ups, though its reliability is disputed. Flash storage may experience downspiking transfer rates as a result of accumulating errors, which the flash memory controller attempts to correct. The health of optical media can be determined by measuring correctable minor errors , of which high counts signify deteriorating and/or low-quality media. Too many consecutive minor errors can lead to data corruption. Not all vendors and models of optical drives support error scanning. As of 2011 ,
3811-1115: Is from the CPU. This traditional division of storage to primary, secondary, tertiary, and off-line storage is also guided by cost per bit. In contemporary usage, memory is usually fast but temporary semiconductor read-write memory , typically DRAM (dynamic RAM) or other such devices. Storage consists of storage devices and their media not directly accessible by the CPU ( secondary or tertiary storage ), typically hard disk drives , optical disc drives, and other devices slower than RAM but non-volatile (retaining contents when powered down). Historically, memory has, depending on technology, been called central memory , core memory , core storage , drum , main memory , real storage , or internal memory . Meanwhile, slower persistent storage devices have been referred to as secondary storage , external memory , or auxiliary/peripheral storage . Primary storage (also known as main memory , internal memory , or prime memory ), often referred to simply as memory ,
3914-437: 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. SQL Server also allows stored procedures 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
4017-400: Is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains varchar or varbinary data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an allocation unit ) and replaced with a pointer to the data. For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size
4120-591: Is managed by the Buffer Manager . Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation
4223-410: 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 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
SQL Server - Misplaced Pages Continue
4326-416: Is primarily useful for extraordinarily large data stores, accessed without human operators. Typical examples include tape libraries and optical jukeboxes . When a computer needs to read information from the tertiary storage, it will first consult a catalog database to determine which tape or disc contains the information. Next, the computer will instruct a robotic arm to fetch the medium and place it in
4429-713: Is readily available for most storage devices. Hardware memory encryption is available in Intel Architecture, supporting Total Memory Encryption (TME) and page granular memory encryption with multiple keys (MKTME). and in SPARC M7 generation since October 2015. Distinct types of data storage have different points of failure and various methods of predictive failure analysis . Vulnerabilities that can instantly lead to total loss are head crashing on mechanical hard drives and failure of electronic components on flash storage. Impending failure on hard disk drives
4532-491: Is slow and memory must be erased in large portions before it can be re-written. Some embedded systems run programs directly from ROM (or similar), because such programs are rarely changed. Standard computers do not store non-rudimentary programs in ROM, and rather, use large capacities of secondary storage, which is non-volatile as well, and not as costly. Recently, primary storage and secondary storage in some uses refer to what
4635-489: Is the native client side data access library for Microsoft SQL Server, version 2005 onwards. It natively implements support for the SQL Server features including the Tabular Data Stream implementation, support for mirrored SQL Server databases, full support for all data types supported by SQL Server, asynchronous operations, query notifications, encryption support, as well as receiving multiple result sets in
4738-442: Is the only one directly accessible to the CPU. The CPU continuously reads instructions stored there and executes them as required. Any data actively operated on is also stored there in a uniform manner. Historically, early computers used delay lines , Williams tubes , or rotating magnetic drums as primary storage. By 1954, those unreliable methods were mostly replaced by magnetic-core memory . Core memory remained dominant until
4841-405: Is typically automatically fenced out, taken out of use by the device, and replaced with another functioning equivalent group in the device, where the corrected bit values are restored (if possible). The cyclic redundancy check (CRC) method is typically used in communications and storage for error detection . A detected error is then retried. Data compression methods allow in many cases (such as
4944-505: Is typically measured in milliseconds (thousandths of a second), while the access time per byte for primary storage is measured in nanoseconds (billionths of a second). Thus, secondary storage is significantly slower than primary storage. Rotating optical storage devices, such as CD and DVD drives, have even longer access times. Other examples of secondary storage technologies include USB flash drives , floppy disks , magnetic tape , paper tape , punched cards , and RAM disks . Once
5047-496: Is used to transfer information since the detached medium can easily be physically transported. Additionally, it is useful for cases of disaster, where, for example, a fire destroys the original data, a medium in a remote location will be unaffected, enabling disaster recovery . Off-line storage increases general information security since it is physically inaccessible from a computer, and data confidentiality or integrity cannot be affected by computer-based attack techniques. Also, if
5150-405: Is user defined; by default all rows are in a single partition. A table is split into multiple partitions in order to spread a database over a computer cluster . Rows in each partition are stored in either B-tree or heap structure. If the table has an associated, clustered index to allow fast retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing
5253-590: The ADO.NET APIs like any other managed application that accesses SQL Server data. However, doing that creates a new database session, different from the one in which the code is executing. To avoid this, SQL Server provides some enhancements to the ADO.NET provider that allows the connection to be redirected to the same session which already hosts the running code. Such connections are called context connections and are set by setting context connection parameter to true in
SECTION 50
#17327808297955356-600: The XML for Analysis standard as the underlying communication protocol. The cube data can be accessed using MDX and LINQ queries. Data mining specific functionality is exposed via the DMX query language. Analysis Services includes various algorithms— Decision trees , clustering algorithm, Naive Bayes algorithm, time series analysis, sequence clustering algorithm, linear and logistic regression analysis, and neural networks —for use in data mining. SQL Server Reporting Services (SSRS)
5459-476: The disk read/write head on HDDs reaches the proper placement and the data, subsequent data on the track are very fast to access. To reduce the seek time and rotational latency, data are transferred to and from disks in large contiguous blocks. Sequential or block access on disks is orders of magnitude faster than random access, and many sophisticated paradigms have been developed to design efficient algorithms based on sequential and block access. Another way to reduce
5562-690: The 1970s, when advances in integrated circuit technology allowed semiconductor memory to become economically competitive. This led to modern random-access memory (RAM). It is small-sized, light, but quite expensive at the same time. The particular types of RAM used for primary storage are volatile , meaning that they lose the information when not powered. Besides storing opened programs, it serves as disk cache and write buffer to improve both reading and writing performance. Operating systems borrow RAM capacity for caching so long as it's not needed by running software. Spare memory can be utilized as RAM drive for temporary high-speed data storage. As shown in
5665-501: The CPU and slower but less expensive and larger options further away. Generally, the fast technologies are referred to as "memory", while slower persistent technologies are referred to as "storage". Even the first computer designs, Charles Babbage 's Analytical Engine and Percy Ludgate 's Analytical Machine, clearly distinguished between processing and memory (Babbage stored numbers as rotations of gears, while Ludgate stored numbers as displacements of rods in shuttles). This distinction
5768-462: The I/O bottleneck is to use multiple disks in parallel to increase the bandwidth between primary and secondary memory. Secondary storage is often formatted according to a file system format, which provides the abstraction necessary to organize data into files and directories , while also providing metadata describing the owner of a certain file, the access time, the access permissions, and other information. Most computer operating systems use
5871-550: The SQL server instance, allowing people to do machine learning and data analytics without having to send data across the network or be limited by the memory of their own computers. The services come with Microsoft's R and Python distributions that contain commonly used packages for data science, along with some proprietary packages (e.g. revoscalepy , RevoScaleR , microsoftml) that can be used to create machine models at scale. Analysts can either configure their client machine to connect to
5974-443: The changes made to other pages since last backup or logging, or contain large data types such as image or text. While a page is the basic unit of an I/O operation, space is actually managed in terms of an extent which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so
6077-427: The client application. For this it exposes read-only tables from which server statistics can be read. Management functionality is exposed via system-defined stored procedures which can be invoked from T-SQL queries to perform the management operation. It is also possible to create linked Servers using T-SQL. Linked servers allow a single query to process operations performed on multiple servers. SQL Server Native Client
6180-420: 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 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
6283-564: The client every time (as it can be accessed by name), it reduces network traffic and somewhat improves performance. Execution plans for stored procedures are also cached as necessary. T-SQL (Transact-SQL) is Microsoft's proprietary procedural language extension for SQL Server. It provides REPL (Read-Eval-Print-Loop) instructions that extend standard SQL's instruction set for Data Manipulation ( DML ) and Data Definition ( DDL ) instructions, including SQL Server-specific settings, security and database statistics management. It exposes keywords for
SECTION 60
#17327808297956386-510: The command line parameters are identical, although SQLCMD adds extra versatility. Microsoft Visual Studio includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR. It also includes a data designer that can be used to graphically create, view or edit database schemas. Queries can be created either visually or using code. SSMS 2008 onwards, provides intellisense for SQL queries as well. SQL Server Management Studio
6489-524: The concept of virtual memory , allowing the utilization of more primary storage capacity than is physically available in the system. As the primary memory fills up, the system moves the least-used chunks ( pages ) to a swap file or page file on secondary storage, retrieving them later when needed. If a lot of pages are moved to slower secondary storage, the system performance is degraded. The secondary storage, including HDD , ODD and SSD , are usually block-addressable. Tertiary storage or tertiary memory
6592-532: The connection string. SQL Server also provides several other enhancements to the ADO.NET API, including classes to work with tabular data or a single row of data as well as classes to work with internal metadata about the data stored in the database. It also provides access to the XML features in SQL Server, including XQuery support. These enhancements are also available in T-SQL Procedures in consequence of
6695-411: The database, identified by their respective transaction IDs. The main mode of retrieving data from a SQL Server database is querying for it. The query is expressed using a variant of SQL called T-SQL , a dialect Microsoft SQL Server shares with Sybase SQL Server due to its legacy. The query declaratively specifies what is to be retrieved. It is processed by the query processor, which figures out
6798-521: The database. Managed code is compiled to CLI assemblies and after being verified for type safety , registered at the database. After that, they can be invoked like any other procedure. However, only a subset of the Base Class Library is available, when running code under SQL CLR. Most APIs relating to user interface functionality are not available. When writing code for SQL CLR, data stored in SQL Server databases can be accessed using
6901-439: The data—no other user can access the data as long as the lock is held. Shared locks are used when some data is being read—multiple users can read from data locked with a shared lock, but not acquire an exclusive lock. The latter would have to wait for all shared locks to be released. Locks can be applied on different levels of granularity—on entire tables, pages, or even on a per-row basis on tables. For indexes, it can either be on
7004-430: The desired data to primary storage. Secondary storage is non-volatile (retaining data when its power is shut off). Modern computer systems typically have two orders of magnitude more secondary storage than primary storage because secondary storage is less expensive. In modern computers, hard disk drives (HDDs) or solid-state drives (SSDs) are usually used as secondary storage. The access time per byte for HDDs or SSDs
7107-494: The desired location of data. Then it reads or writes the data in the memory cells using the data bus. Additionally, a memory management unit (MMU) is a small device between CPU and RAM recalculating the actual memory address, for example to provide an abstraction of virtual memory or other tasks. As the RAM types used for primary storage are volatile (uninitialized at start up), a computer containing only such storage would not have
7210-400: The diagram, traditionally there are two more sub-layers of the primary storage, besides main large-capacity RAM: Main memory is directly or indirectly connected to the central processing unit via a memory bus . It is actually two buses (not on the diagram): an address bus and a data bus . The CPU firstly sends a number through an address bus, a number called memory address , that indicates
7313-761: The entire index or on index leaves. The level of granularity to be used is defined on a per-database basis by the database administrator. While a fine-grained locking system allows more users to use the table or index simultaneously, it requires more resources, so it does not automatically yield higher performance. SQL Server also includes two more lightweight mutual exclusion solutions—latches and spinlocks—which are less robust than locks but are less resource intensive. SQL Server uses them for DMVs and other resources that are usually not busy. SQL Server also monitors all worker threads that acquire locks to ensure that they do not end up in deadlocks —in case they do, SQL Server takes remedial measures, which in many cases are to kill one of
7416-556: The first Microsoft SQL Server product—SQL Server 1.0, a 16-bit server for the OS/2 operating system in 1989—and extends to the current day. Its name is entirely descriptive, it being server software that responds to queries in the SQL language. As of February 2024 , the following versions are supported by Microsoft: From SQL Server 2016 onward, the product is supported on x64 processors only and must have 1.4 GHz processor as
7519-477: The former using standard MOSFETs and the latter using floating-gate MOSFETs . In modern computers, primary storage almost exclusively consists of dynamic volatile semiconductor random-access memory (RAM), particularly dynamic random-access memory (DRAM). Since the turn of the century, a type of non-volatile floating-gate semiconductor memory known as flash memory has steadily gained share as off-line storage for home computers. Non-volatile semiconductor memory
7622-422: The heap structure has performance advantages over the clustered structure. Both heaps and B-trees can span multiple allocation units. SQL Server buffers pages in RAM to minimize disk I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache
7725-441: The included Report Builder . Once created, RDL files can be rendered in a variety of formats, including Excel, PDF , CSV , XML , BMP , EMF , GIF , JPEG , PNG , and TIFF , and HTML Web Archive. Originally introduced as a post-release add-on for SQL Server 2000, Notification Services was bundled as part of the Microsoft SQL Server platform for the first and only time with SQL Server 2005. SQL Server Notification Services
7828-482: The index. The data is in the leaf node of the leaves, and other nodes storing the index values for the leaf data reachable from the respective nodes. If the index is non-clustered, the rows are not sorted according to the index keys. An indexed view has the same storage structure as an indexed table. A table without a clustered index is stored in an unordered heap structure. However, the table may have non-clustered indices to allow fast retrieval of rows. In some situations
7931-474: The indexer (that creates the full text indexes) and the full text query processor. The indexer scans through text columns in the database. It can also index through binary columns, and use iFilters to extract meaningful text from the binary blob (for example, when a Microsoft Word document is stored as an unstructured binary file in a database). The iFilters are hosted by the Filter Daemon process. Once
8034-514: The indexer in case of updates. When a full text query is received by the SQL Server query processor, it is handed over to the FTS query processor in the Search process. The FTS query processor breaks up the query into the constituent words, filters out the noise words, and uses an inbuilt thesaurus to find out the linguistic variants for each word. The words are then queried against the inverted index and
8137-568: The information stored for archival purposes is rarely accessed, off-line storage is less expensive than tertiary storage. In modern personal computers, most secondary and tertiary storage media are also used for off-line storage. Optical discs and flash memory devices are the most popular, and to a much lesser extent removable hard disk drives; older examples include floppy disks and Zip disks. In enterprise uses, magnetic tape cartridges are predominant; older examples include open-reel magnetic tape and punched cards. Storage technologies at all levels of
8240-515: The introduction of the new XML Datatype (query, value, nodes functions). SQL Server also includes an assortment of add-on services. While these are not essential for the operation of the database system, they provide value added services on top of the core database management system. These services either run as a part of some SQL Server component or out-of-process as Windows Service and presents their own API to control and interact with them. The SQL Server Machine Learning services operates within
8343-401: 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. 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
8446-641: The most commonly used data storage media are semiconductor, magnetic, and optical, while paper still sees some limited usage. Some other fundamental storage technologies, such as all-flash arrays (AFAs) are proposed for development. Semiconductor memory uses semiconductor -based integrated circuit (IC) chips to store information. Data are typically stored in metal–oxide–semiconductor (MOS) memory cells . A semiconductor memory chip may contain millions of memory cells, consisting of tiny MOS field-effect transistors (MOSFETs) and/or MOS capacitors . Both volatile and non-volatile forms of semiconductor memory exist,
8549-493: The network, or database caches on the client side. Replication Services follows a publisher/subscriber model, i.e., the changes are sent out by one database server ("publisher") and are received by others ("subscribers"). SQL Server supports three different types of replication: SQL Server Analysis Services (SSAS) adds OLAP and data mining capabilities for SQL Server databases. The OLAP engine supports MOLAP , ROLAP and HOLAP storage modes for data. Analysis Services supports
8652-430: The network. Also, service broker supports security features like network authentication (using NTLM , Kerberos , or authorization certificates ), integrity checking, and message encryption . SQL Server Replication Services are used by SQL Server to replicate and synchronize database objects, either in entirety or a subset of the objects present, across replication agents, which might be other database servers across
8755-410: The operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself. Client applications that consume data or manage the server will leverage SQL Server functionality by sending T-SQL queries and statements which are then processed by the server and results (or errors) returned to
8858-400: The optimistic concurrency control mechanism, which is similar to the multiversion concurrency control used in other databases. The mechanism allows a new version of a row to be created whenever the row is updated, as opposed to overwriting the row, i.e., a row is additionally identified by the ID of the transaction that created the version of the row. Both the old as well as the new versions of
8961-408: The physical bit in the storage of its ability to maintain a distinguishable value (0 or 1), or due to errors in inter or intra-computer communication. A random bit flip (e.g. due to random radiation ) is typically corrected upon detection. A bit or a group of malfunctioning physical bits (the specific defective bit is not always known; group definition depends on the specific storage device)
9064-403: The queue. The queue acts as storage provider for the messages. They are internally implemented as tables by SQL Server, but do not support insert, update, or delete functionality. The service program receives and processes service broker messages. Usually the service program is implemented as stored procedure or CLR application. Routes are network addresses where the service broker is located on
9167-530: The result. It would have to be reconfigured to change its behavior. This is acceptable for devices such as desk calculators , digital signal processors , and other specialized devices. Von Neumann machines differ in having a memory in which they store their operating instructions and data. Such computers are more versatile in that they do not need to have their hardware reconfigured for each new program, but can simply be reprogrammed with new in-memory instructions; they also tend to be simpler to design, in that
9270-415: The row are stored and maintained, though the old versions are moved out of the database into a system database identified as Tempdb . When a row is in the process of being updated, any other requests are not blocked (unlike locking) but are executed on the older version of the row. If the other request is an update statement, it will result in two different versions of the rows—both of them will be stored by
9373-416: The same data, or clients attempt to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency . When pessimistic concurrency control is being used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive. An exclusive lock grants the user exclusive access to
9476-445: 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 statement and a 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
9579-526: The sequence they are specified in the query but are near each other, they are also considered a match. T-SQL exposes special operators that can be used to access the FTS capabilities. The Full Text Search engine is divided into two processes: the Filter Daemon process ( msftefd.exe ) and the Search process ( msftesql.exe ). These processes interact with the SQL Server. The Search process includes
9682-424: The source string, indicated by a Rank value which can range from 0 to 1000—a higher rank means a more accurate match. It also allows linguistic matching ("inflectional search"), i.e., linguistic variants of a word (such as a verb in a different tense) will also be a match for a given word (but with a lower rank than an exact match). Proximity searches are also supported, i.e., if the words searched for do not occur in
9785-413: The storage hierarchy can be differentiated by evaluating certain core characteristics as well as measuring characteristics specific to a particular implementation. These core characteristics are volatility, mutability, accessibility, and addressability. For any particular implementation of any storage technology, the characteristics worth measuring are capacity and performance. Non-volatile memory retains
9888-422: The stored information even if not constantly supplied with electric power. It is suitable for long-term storage of information. Volatile memory requires constant power to maintain the stored information. The fastest memory technologies are volatile ones, although that is not a universal rule. Since the primary storage is required to be very fast, it predominantly uses volatile memory. Dynamic random-access memory
9991-459: The text is extracted, the Filter Daemon process breaks it up into a sequence of words and hands it over to the indexer. The indexer filters out noise words , i.e., words like A , And , etc., which occur frequently and are not useful for search. With the remaining words, an inverted index is created, associating each word with the columns they were found in. SQL Server itself includes a Gatherer component that monitors changes to tables and invokes
10094-494: The threads entangled in a deadlock and roll back the transaction it started. To implement locking, SQL Server contains the Lock Manager . The Lock Manager maintains an in-memory table that manages the database objects and locks, if any, on them along with other metadata about the lock. Access to any shared object is mediated by the lock manager, which either grants access to the resource or blocks it. SQL Server also provides
10197-510: The title SQL Server . If an internal link led you here, you may wish to change the link to point directly to the intended article. Retrieved from " https://en.wikipedia.org/w/index.php?title=SQL_Server&oldid=1120906861 " Category : Disambiguation pages Hidden categories: Short description is different from Wikidata All article disambiguation pages All disambiguation pages Microsoft SQL Server Microsoft SQL Server (Structured Query Language)
10300-560: The transformed data into destination databases or files. SQL Server Full Text Search service is a specialized indexing and querying service for unstructured text stored in SQL Server databases. The full text search index can be created on any column with character based text data. It allows for words to be searched for in the text columns. While it can be performed with the SQL LIKE operator, using SQL Server Full Text Search service can be more efficient. Full allows for inexact matching of
10403-467: Was extended in the Von Neumann architecture , where the CPU consists of two main parts: The control unit and the arithmetic logic unit (ALU). The former controls the flow of data between the CPU and memory, while the latter performs arithmetic and logical operations on data. Without a significant amount of memory, a computer would merely be able to perform fixed operations and immediately output
10506-441: Was historically called, respectively, secondary storage and tertiary storage . The primary storage, including ROM , EEPROM , NOR flash , and RAM , are usually byte-addressable . Secondary storage (also known as external memory or auxiliary storage ) differs from primary storage in that it is not directly accessible by the CPU. The computer usually uses its input/output channels to access secondary storage and transfer
10609-615: Was released to General Availability in September 2018. Prior to release the preview version of the application was known as SQL Server Operations Studio. Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services , Reporting Services and Integration Services . It
#794205