Overblog Suivre ce blog
Editer l'article Administration Créer mon blog
18 avril 2011 1 18 /04 /avril /2011 09:27

Microsoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQLand ANSI SQL  HP 448007-001 battery .


Prior to version 7.0 the code base for MS SQL Server was sold by Sybase SQL Server to Microsoft, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase HP Pavilion DV7 battery .

Microsoft, Sybase and Ashton-Tate originally teamed up to create and market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 3.0 on Unix, VMS, etc HP DV6-1120SA battery .

Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with IBM OS/2version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1  HP DV6-1210SA battery .

Microsoft SQL Server v6.0 was the first version designed for NT, and did not include any direction from Sybase.

About the time Windows NT was released, Sybase and Microsoft parted ways and each pursued their own design and marketing schemes Compaq CQ50 battery .

Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server HP Pavilion DV8 battery .

Until 1994, Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.

Since parting ways, several revisions have been done independently Sony VGP-BPS13 battery .

SQL Server 7.0 was a rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which was the first edition to be launched in a variant for the IA-64 architecture  HP DV9700 battery .

In the ten years since release of Microsoft's previous SQL Server product (SQL Server 2000), advancements have been made in performance, the client IDE tools, and several complementary systems that are packaged with SQL Server 2005 Compaq CQ35-100 battery .

These include: an ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data mining server (Analysis Services), and several messaging technologies, specifically Service Broker and Notification Services   Sony VGP-BPS11 battery .

SQL Server 2005

SQL Server 2005 (codenamed Yukon), released in October 2005, is the successor to SQL Server 2000. It included native support for managingXML data, in addition to relational data  HP Pavilion DV6-1223EO Battery .

For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas  Sony Vaio VGN-CR190E/L Battery ;

XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data  Sony VGN-CR11M Battery .

XML data is queried using XQuery; Common Language Runtime (CLR) integration was a main features with this edition, enabling one to write SQL code as Managed Code by the CLR. SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL Sony VGN-NR11Z Battery .

In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP (protocol)requests  Sony VGN-NR11S Battery .

When the data is accessed over web services, results are returned as XML.

For relational data, T-SQL has been augmented with error handling features (try/catch) and support for recursive queries with CTEs (Common Table Expressions)  Sony VGN-NR110E Battery .

SQL Server 2005 has also been enhanced with new indexing algorithms, syntax and better error recovery systems. Data pages are checksummed for better error resiliency , and optimistic concurrency support has been added for better performance  Sony VGN-NR110E/T Battery .

Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier  Sony VGN-NR110E/S Battery .

SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.

SQL Server 2005 introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes  Sony VGN-NR110E/W Battery .

SQL Server 2005 introduced DMVs (Dynamic Management Views), which are specialized views and functions that return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance  Sony VGN-CR11SR Battery .

SQL Server 2005 introduced Database Mirroring, but it was not fully supported until the first Service Pack release (SP1). In the initial release (RTM) of SQL Server 2005, database mirroring was available, but unsupported  Sony VGN-CR11Z Battery .

In order to implement database mirroring in the RTM version, you had to apply trace flag 1400 at startup. Database mirroring is a high availability option that provides redundancy and failover capabilities at the database level  Sony VGN-CR11S Battery .

Failover can be performed manually or can be configured for automatic failover. Automatic failover requires a witness partner and an operating mode of synchronous (also known as high-safety or full safety)  Sony VGN-CR11M Battery .

SQL Server 2008

The latest version of SQL Server, SQL Server 2008, was released (RTM) on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime  Sony VGN-CR11E Battery .

SQL Server 2008 also includes support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data Sony VGN-CR21E Battery .

In current versions, such multimedia data can be stored as BLOBs(binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them  Sony VGN-CR21S Battery .

According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types  Sony VGN-CR21Z Battery .

Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the newFILESTREAM data type, which can be used to reference any file stored on the file system   Sony VGN-CR21SR Battery .

Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a BLOB  Sony VGN-CR31SR Battery .

Backing up and restoring the database backs up or restores the referenced files as well.[13] SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries  Sony VGN-CR31S Battery .

The Full-Text Search functionality has been integrated with the database engine. According to a Microsoft technical article, this simplifies management and improves performance  Sony VGN-CR31E Battery .

Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane  Sony VGN-CR31Z Battery .

A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges" Sony VGN-CR41Z Battery .

Approximately 70 methods are available to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.[15]

SQL Server includes better compression features, which also helps in improving scalability Sony VGN-CR41S Battery .

It enhanced the indexing algorithms and introduced the notion of filtered indexes. It also includesResource Governor that allows reserving resources for certain users or workflows  Sony VGN-CR41E Battery .

It also includes capabilities for transparent encryption of data (TDE) as well as compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data ModelSony VGN-CR41SR Battery .

SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization, Inc., which was acquired by Microsoft  Sony VGN-CR42ZR Battery .

On the management side, SQL Server 2008 includes the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively  Sony VGN-CR42Z Battery .

The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries against a SQL Server 2008 Database Engine  Sony VGN-CR42S Battery .

SQL Server 2008 also makes the databases available via Windows PowerShellproviders and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell Sony VGN-CR42E Battery .

SQL Server 2008 R2

SQL Server 2008 R2 (formerly codenamed SQL Server "Kilimanjaro") was announced at TechEd 2009, and was released to manufacturing on April 21, 2010. Sony Vaio VGN-CR11S/L Battery

SQL Server 2008 R2 adds certain features to SQL Server 2008 including a master data management system branded as Master Data Services, a central management of master data entities and hierarchies Sony Vaio VGN-CR190E/P Battery .

Also Multi Server Management, a centralized console to manage multiple SQL Server 2008 instances and services including relational databases, Reporting Services, Analysis Services & Integration Services. Sony Vaio VGN-CR190E/R Battery

SQL Server 2008 R2 includes a number of new services,[23] including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint Sony Vaio VGN-CR190E/W Battery ,

a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers Sony Vaio VGN-CR21/B Battery .


Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are Sony Vaio VGN-CR21E/L Battery :

SQL Server Compact Edition (SQL CE)

The compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries Sony Vaio VGN-CR21E/P Battery .

Due to its small size (1 MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations) Sony Vaio VGN-CR21E/W Battery .

It is limited to 4 GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes considerable work that supports ADO.NET Synchronization Services  Sony Vaio VGN-CR21S/L Battery .

SQL Server Developer Edition

SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server Sony Vaio VGN-CR21S/P Battery .

This edition is available to download by students free of charge as a part of Microsoft's DreamSpark program.

SQL Server 2005 Embedded Edition (SSEE)

SQL Server 2005 Embedded Edition is a specially configured named instance of the SQL Server Express database engine which can be accessed only by certain Windows Services Sony Vaio VGN-CR21S/W Battery .

SQL Server Enterprise Edition

SQL Server Enterprise Edition is the full-featured edition of SQL Server, including both the core database engine and add-on services, while including a range of tools for creating and managing a SQL Server cluster Sony Vaio VGN-CR21Z/N Battery .

SQL Server Evaluation Edition

SQL Server Evaluation Edition, also known as the Trial Edition, has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop. Sony Vaio VGN-CR21Z/R Battery

SQL Server Express Edition

SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor Sony Vaio VGN-CR220E/R Battery ,

1 GB memory and 4 GB database files (10 GB database files from SQL Server Express 2008 R2[28]). The entire database is stored in a single .mdf file, and thus making it suitable for XCOPY deployment. It is intended as a replacement for MSDE Sony Vaio VGN-CR23/B Battery .

Two additional editions provide a superset of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic. SQL Server Express with Advanced Services adds full-text search capability and reporting services. Sony Vaio VGN-CR23/P Battery ]

SQL Server Fast Track

SQL Server Fast Track is specifically for enterprise-scale data warehousing storage and business intelligence processing, and runs on reference-architecture hardware that is optimized for Fast Track Sony Vaio VGN-CR23/R Battery .

SQL Server Standard Edition

SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster)  HP Pavilion DM3 battery

and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes. HP Pavilion DM4 battery

SQL Server Web Edition

SQL Server Web Edition is a low-TCO option for Web hosting.

SQL Server Workgroup Edition

SQL Server Workgroup Edition includes the core database functionality but does not include the additional services HP Pavilion DV6-1210SA battery

Protocol layer

Protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called Tabular Data Stream(TDS) Dell N3010 battery .

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 Dell Inspiron N4010 battery ,

TDS packets can be encased in other physical transport dependent protocols, including TCP/IP, Named pipes, and Shared memory. Consequently, access to SQL Server is available over these protocols. In addition, the SQL Server API is also exposed over web services. Dell INSPIRON 9100 battery

Data storage

The main unit of data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primary types such as Integer Dell XPS 1340 battery

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 Dell Inspiron 1464 battery .

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) Dell Inspiron 1764 battery .

In addition to tables, 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 231 objects, and can span multiple OS-level files with a maximum file size of 220 TB. Dell Inspiron 1564 battery

The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with a .ndf extension, are used to store optional metadata. Log files are identified with the .ldf extension Dell Inspiron 1720 battery

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 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 Dell Inspiron Mini 12 battery .

Page type defines the data contained in the page - data stored in the database, index, allocation map which holds information about how pages are allocated to tables and indexes, change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text Dell LATITUDE D800 battery .

While 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") Dell Inspiron 6400 AC Adapter .

A row in a database table cannot span more than one page, so 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. SONY VAIO VGN-FWZJ/H Battery

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size 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 cluster  SONY VAIO VGN-FW41M/H Battery .

Rows in each partition are stored in either B-tree or heap structure. If the table has an associated index to allow fast retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing the index  SONY VAIO VGN-FW37J Battery .

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. SONY VAIO VGN-FW90NS Battery

A table without an index is stored in an unordered heap structure. Both heaps and B-trees can span multiple allocation units. SONY VAIO VGN-FW83XS Battery

Buffer management

SQL Server buffers pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache SONY VAIO VGN-FW83JS Battery .

The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache 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 SONY VAIO VGN-FW30B Battery .

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 is done in a background thread so that other operations do not have to wait for the I/O operation to complete. SONY VAIO VGN-AW41MF/H Battery

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. SONY VAIO VGN-AW41MF Battery

Logging and Transaction

SQL Server ensures that any change to the data is ACID-compliant, i.e. it uses transactions to ensure that the database will always revert to a known consistent state on failure. SONY VAIO VGN-AW41JF/H Battery

Each transaction may consist of multiple SQL statements all of which will only make a permanent change to the database if the last statement in the transaction (a COMMIT statement) completes successfully. If the COMMIT successfully completes the transaction is safely on disk  SONY VAIO VGN-FW38M Battery .

SQL Server implements transactions using a write-ahead log.

Any changes made to any page will update the in-memory cache of the page, simultaneously all the operations performed will be written to a log, along with the transaction ID which the operation was a part of SONY VAIO VGN-FW31Z Battery .

Each log entry is identified by an increasing Log Sequence Number (LSN) which is used to ensure that all changes are written to the data files. Also during a log restore it is used to check that no logs are duplicated or skipped  SONY VAIO VGN-FW31M Battery .

SQL Server requires that the log is written onto the disc before the data page is written back. It must also ensure that all operations in a transaction are written to the log before any COMMIT operation is reported as completed  SONY VAIO VGN-FW31B Battery .

At a later point the server will checkpoint the database and ensure that all pages in the data files have the state of their contents synchronised to a point at or after the LSN that the checkpoint started  SONY VAIO VGN-FW31E Battery .

When completed the checkpoint marks that portion of the log file as complete and may free it (see Simple transaction logging vs Full transaction logging). This enables SQL Server to ensure integrity of the data, even if the system fails  SONY VAIO VGN-FW31J Battery.

On failure the database log has to be replayed to ensure the data files are in a consistent state. All pages stored in the roll forward part of the log (not marked as completed) are rewritten to the database, when the end of the log is reached all open transactions are rolled back using the roll back portion of the log file Sony VGP-BPS21 Battery .

The database engine usually checkpoints quite frequently, however in a heavily loaded database this can have a significant performance impact. It is possible to reduce the frequency of checkpoints or disable them completely but the rollforward during a recovery will take much longer Sony VGP-BPS21A Battery

Concurrency and locking

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 the same data, or clients attempt to read data that is in the process of being changed by another client Sony VGP-BPS21/S Battery .

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 Sony VGP-BPS21A/B Battery .

Exclusive lock grants the user exclusive access to 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  SONY VGP-BPS21 Battery .

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 the entire index or on index leaves SONY VGP-BPS21A Battery .

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 turn into higher performing solution  SONY VGP-BPS21B Battery .

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  SONY VGP-BPS21/S Battery .

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 is to kill one of the threads entangled in a deadlock and rollback the transaction it started SONY VGP-BPS21A/B Battery .

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  SONY VGP-BPS13/Q Battery .

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 the optimistic concurrency control mechanism, which is similar to the multiversion concurrency controlused in other databases SONY VGP-BPS13/Q Battery .

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  SONY VGP-BPS13B/Q Battery .

Both the old as well as the new versions of the row are stored and maintained, though the old versions are moved out of the database into a system database identified as Tempdb  SONY VGP-BPS13A/Q Battery .

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 the database, identified by their respective transaction IDs SONY VGP-BPS13A/Q Battery

Data retrieval

The main mode of retrieving data from an SQL Server database is querying for it. The query is expressed using a variant of SQLcalled T-SQL, a dialect Microsoft SQL Server shares with Sybase SQL Server due to its legacy  Toshiba Satellite M300 Battery .

The query 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 Toshiba Satellite 1200 Battery .

There might be multiple ways to process the same query. For example, for a query that contains a joinstatement 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 the join, but result in different execution plans Toshiba Satellite A200 Battery .

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  Sony VGP-BPS21 battery .[26]

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 thedatabase schema, the database statistics and the system load at that time   Sony VGP-BPS14 battery .

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  Sony VGP-BPL8 battery -

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 concurrently or not  Sony VGP-BPS22 battery .

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. Sony VGP-BPS11 battery

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). Sony VGP-BPS9 battery

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 selectively provided access to  Sony VGP-BPS10 battery .

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  Toshiba PA3399U-2BRS battery .

Execution plans for stored procedures are also cached as necessary.


Microsoft SQL Server 2005 includes a component named SQL CLR ("Common Language Runtime") via which it integrates with .NET Framework. Unlike most other applications that use Toshiba Satellite T4900 Battery .

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 Toshiba Satellite L305 Battery .

SQLOS provides deadlock detection and resolution services for .NET code as well. With SQL CLR, stored procedures and triggers can be written in anymanaged .NET language, including C# and VB.NET. Managed code can also be used to define UDT's (user defined types), which can persist in the database SONY VGN -FZ210CE Battery .

Managed code is compiled to .NET 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  Dell Precision M70 Battery .

Most APIs relating to user interfacefunctionality are not available.[36]

When writing code for SQL CLR, data stored in SQL Server databases can be accessed using the ADO.NET APIs like any other managed application that accesses SQL Server data  Acer Aspire One battery 10400mAh .

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  Toshiba Satellite P10 Battery .

Such connections are called context connections and are set by setting context connection parameter to true in the connection string. SQL Server also provides several other enhancements to the ADO.NET API, Dell RM791 battery

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  Dell Inspiron E1505 battery .

These enhancements are also available in T-SQL Procedures in consequence of the introduction of the new XML Datatype (query,value,nodes functions).[37]


SQL Server also includes an assortment of add-on services  Dell Studio 1737 battery .

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. Dell Inspiron 300M Battery

Service Broker

Used inside an instance, it is used to provide an asynchronous programming environment. For cross instance applications, Service Broker communicates The Service Broker Dell Latitude E4200 Battery ,

which runs as a part of the database engine, provides a reliable messaging and message queuing platform for SQL Server applications. over TCP/IP and allows the different components to be synchronized together, via exchange of messages. Dell Latitude E5400 Battery

Replication Services

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 the network, or database caches on the client side  Sony VGN-FW139E/H battery .

Replication 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: Sony VGN-FW11M Battery

Transaction replication

Each transaction made to the publisher database (master database) is synced out to subscribers, who update their databases with the transaction. Transactional replication synchronizes databases in near real time. Sony VGN-FW11S Battery

Merge replication

Changes made at both the publisher and subscriber databases are tracked, and periodically the changes are synchronized bi-directionally between the publisher and the subscribers Dell Inspiron Mini 10 Battery .

If the same data has been modified differently in both the publisher and the subscriber databases, synchronization will result in a conflict which has to be resolved - either manually or by using pre-defined policies. rowguid needs to be configured on a column if merge replication is configured Dell Latitude D620 Battery .

Snapshot replication

Snapshot replication published a copy of the entire database (the then-snapshot of the data) and replicates out to the subscribers. Further changes to the snapshot are not tracked. Dell Latitude D830 Battery

Analysis Services

SQL Server Analysis Services adds OLAP and data mining capabilities for SQL Server databases. The OLAP engine supports MOLAP, ROLAP and HOLAP storage modes for data  Toshiba Satellite M60 battery .

Analysis Services supports the XML for Analysis standard as the underlying communication protocol. The cube data can be accessed using MDX queries.[43] Data mining specific functionality is exposed via the DMXquery language Toshiba Satellite M65 battery .

Analysis Services includes various algorithms - Decision trees, clustering algorithm, Naive Bayes algorithm, time series analysis, sequence clustering algorithm, linear and logisticregression analysis, and neural networks - for use in data mining.[ Dell Latitude D610 Battery

Reporting Services

SQL Server Reporting Services is a report generation environment for data gathered from SQL Server databases. It is administered via a web interface. Reporting services features a web servicesinterface to support the development of custom reporting applications. Reports are created as RDL files   Dell Studio 1457 Battery .

Reports can be designed using recent versions of Microsoft Visual Studio (Visual Studio.NET 2003, 2005, and 2008)[46]with Business Intelligence Development Studio, installed or with the included Report Builder  Dell Studio 1450 Battery .

Once created, RDL files can be rendered in a variety of formats[47] including Excel, PDF, CSV, XML, TIFF (and other image formats),[48] and HTML Web Archive. Dell Inspiron 1320n Battery

Notification Services

Originally introduced as a post-release add-on for SQL Server 2000,[49] Notification Services was bundled as part of the Microsoft SQL Server platform for the first and only time with SQL Server 2005 SONY VGP-BPS13AS Battery

with Sql Server 2005, SQL Server Notification Services 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) SONY VGP-BPS13A/S Battery ;

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 a file in the filesystem SONY VGP-BPS13B/B Battery .

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 SONY VGP-BPS13A/B Battery .

Integration Services

SQL Server Integration Services is used to integrate data from different data sources. It is used for the ETL capabilities for SQL Server for data warehousing needs. SONY VGP-BPS13/S Battery

Integration Services includes GUI tools to build data extraction workflows integration various functionality such as extracting data from various sources, querying data, transforming data including aggregating, duplication and merging data, and then loading the transformed data onto other sources, or sending e-mails detailing the status of the operation as defined by the user SONY VGP-BPS8 Battery .

Full Text Search Service

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  SONY VAIO VGN-FZ38M Battery .

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 Text Search (FTS) allows for inexact matching of the source string, indicated by a Rank value which can range from 0 to 1000 - a higher rank means a more accurate match  SONY VAIO VGN-FZ31Z Battery .

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 the sequence they are specified in the query but are near each other, they are also considered a matchSONY VAIO VGN-FZ31M Battery .

T-SQL exposes special operators that can be used to access the FTS capabilities.[54][55]

The Full Text Search engine is divided into two processes - the Filter Daemon process (msftefd.exe) and the Search process (msftesql.exe)  SONY VAIO VGN-FZ31J Battery .

These processes interact with the SQL Server. The Search process includes the indexer (that creates the full text indexes) and the full text query processor. The indexer scans through text columns in the database  SONY VAIO VGN-FZ31B Battery .

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  SONY VAIO VGN-FZ31E Battery .

Once 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  SONY VAIO VGN-FZ4000 Battery .

With the remaining words, aninverted 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 the indexer in case of updates  SONY VAIO VGN-FZ480E Battery .

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 SONY VAIO VGN-FZ460E Battery .

The words are then queried against the inverted index and a rank of their accurateness is computed. The results are returned to the client via the SQL Server process. SONY VAIO VGN-FZ340E Battery


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  SONY VAIO VGN-FZ220E Battery .

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 a .sql file, and are used either for management of databases or to create the database schema during the deployment of a database SONY VAIO VGN-FZ180E Battery .

SQLCMD was introduced with SQL Server 2005 and this continues with SQL Server 2008. Its predecessor for earlier versions was OSQL and ISQL, which is functionally equivalent as it pertains to TSQL execution, and many of the command line parameters are identical, although SQLCMD adds extra versatility. Sony VAIO VGN-NR11Z/T Battery

Visual Studio

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 designerthat can be used to graphically create, view or edit database schemas  Sony VAIO VGN-NR11M/S Battery .

Queries can be created either visually or using code. SSMS 2008 onwards, provides intellisense for SQL queries as well.

SQL Server Management Studio Sony VAIO VGN-NR11Z/S Battery

SQL Server Management Studio 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  Sony VAIO VGN-NR11S/S Battery .

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 is also available for SQL Server Express Edition, for which it is known as SQL Server Management Studio Express (SSMSE) Sony VAIO VGN-FW21E Battery .

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.[59] It can be used to visually observe and analyze query plans and optimize the database performance, among others SONY VAIO VGN-NR11Z/T Battery

SQL Server Management Studio can also be used to create 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  SONY VAIO VGN-NR11Z/S Battery

Business Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is the IDE fr

om Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services  SONY VAIO VGN-NR11M/S Battery .

It is based on the Microsoft Visual Studio development environment but customizes 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) SONY VAIO VGN-NR11S/S Battery


T-SQL (Transact-SQL) is the primary means of programming and managing SQL Server. It exposes keywords for 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. SONY VAIO VGN-FW21E Battery

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 the client application. SQL Server allows it to be managed using T-SQL  SONY VAIO VGN-FW31J Battery .

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 Server using T-SQL SONY VAIO VGN-FW31J Battery .

Linked server allows operation to multiple server as one query.[62]

SQL Native Client

SQL Native Client is the native client side data access library for Microsoft SQL Server, version 2005 onwards. SONY VAIO VGN-FZ21E Battery

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 SONY VAIO VGN-FZ18 Battery ,

as well as receiving multiple result sets in a single database session. SQL Native Client is used under the hood by SQL Server plug-ins for other data access technologies, including ADO or OLE DB. The SQL Native Client can also be directly used, bypassing the generic data access layers HP Pavilion DV8000 battery

SQL ( /??s kju? ??l/, often /?si?kw?l/),[3] often referred to as Structured Query Language, is a database computer language designed for managing data inrelational database management systems (RDBMS), and originally based upon relational algebraand calculus HP Pavilion DV6000 battery

Its scope includes data insert, query, update and delete, schema creation and modification, and data access control. SQL was one of the first commercial languages for Edgar F. Codd'srelational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". HP Pavilion DV8000 battery

Despite not adhering to the relational model as described by Codd, it became the most widely used database language.[6][7]


SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s  HP Pavilion DV6000 battery .

This version, initially called SEQUEL(Structured EnglishQuery Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. SONY VAIO VGN-FZ Battery

The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.[9]

The first Relational Database Management System (RDBMS) was RDMS, developed at MIT in the early 1970s, soon followed by Ingres, developed in 1974 at U.C. Berkeley HP PAVILION DV8000 Battery .

Ingres implemented a query language known as QUEL, which was later supplanted in the marketplace by SQL.[9]

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd HP PAVILION DV6000 Battery ,

Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers  HP Pavilion dv8000 battery .

Oracle V2beat IBM's August release of the System/38 RDBMS to market by a few weeks.

After testing SQL at customer test sites to determine the usefulness and practicality of the system HP Pavilion dv6000 Battery ,

IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively. Dell Inspiron E1505 battery

Language elements

The SQL language is sub-divided into several language elements, including:

  • Clauses, which are constituent components of statements and queries. (In some cases, these are optional.) Sony VGP-BPL15 battery
  • Expressions, which can produce either scalar values or tables consisting of columns and rows of data.
  • Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) or Boolean(true/false/unknown) truth values and which are used to limit the effects of statements and queries, or to change program flow Sony VGP-BPS13B/B battery .
  • Queries, which retrieve the data based on specific criteria. This is the most important element of SQL.
  • Statements, which may have a persistent effect on schemata and data, or which may control transactions, program flow, connections, sessions, or diagnostics.  Sony VGP-BPL9 battery
    • SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.  Sony VGP-BPS13B/B battery
  • Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.


The most common operation in SQL is the query, which is performed with the declarative SELECT statement  Sony VGP-BPS13A/B battery .

SELECT retrieves data from one or more tables, or expressions. Standard SELECTstatements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some databases. Sony VGP-BPS13/S battery

Queries allow the user to describe desired data, leaving the database management system (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses  Sony VGP-BPS13/B battery .

A query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk ("*") can also be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include Sony VGP-BPS13 battery :

  • The FROM clause which indicates the table(s) from which data is to be retrieved. The FROM clause can include optional JOINsubclauses to specify the rules for joining tables Dell Studio 1555 battery .
  • The WHERE clause includes a comparison predicate, which restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set for which the comparison predicate does not evaluate to True. Dell KM958 battery
  • The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause Dell XPS M1210 Battery .
  • The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in theHAVING clause predicate SONY VAIO VGN-FW33GW Battery.
  • The ORDER BY clause identifies which columns are used to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without an ORDER BYclause, the order of rows returned by an SQL query is undefinedSONY VAIO VGN-FW33G/E1 Battery .

The following is an example of a SELECT query that returns a list of expensive books. The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title SONY VAIO VGN-FW290JTH Battery .

The asterisk (*) in the select list indicates that all columns of the Book table should be included in the result set.

XQuery is a query and functional programming language that is designed to query collections of XML data  SONY VAIO VGN-FW290JTB Battery .

XQuery 1.0 was developed by the XML Query working group of the W3C. The work was closely coordinated with the development of XSLT 2.0 by the XSL Working Group; the two groups shared responsibility for XPath 2.0, which is a subset of XQuery 1.0. XQuery 1.0 became a W3C Recommendation on January 23, 2007 SONY VAIO VGN-FW29/B Battery .

"The mission of the XML Query project is to provide flexible query facilities to extract data from real and virtual documents on the World Wide Web, therefore finally providing the needed interaction between the Web world and the database world SONY VAIO VGN-FW27T/H Battery .

Ultimately, collections of XML files will be accessed like databases".[1]

Database-centric architecture or data-centric architecture has several distinct meanings, generally relating to software architectures in which databases play a crucial role  SONY VAIO VGN-FW31ZJ Battery .

Often this description is meant to contrast the design to an alternative approach. For example, the characterization of an architecture as "database-centric" may mean any combination of the following  SONY VAIO VGN-FW27/W Battery :

  • using a standard, general-purpose relational database management system, as opposed to customized in-memory or file-based data structures and access methods. With the evolution of sophisticated DBMS software , much of which is either free or included with the operating system SONY VAIO VGN-FW27/B Battery ,
  • application developers have become increasingly reliant on standard database tools, especially for the sake of rapid application development.
  • using dynamic, table-driven logic, as opposed to logic embodied in previously compiled programs. The use of table-driven logic, i.e  SONY VAIO VGN-FW26T/B Battery .
  • behavior that is heavily dictated by the contents of a database, allows programs to be simpler and more flexible. This capability is a central feature of dynamic programming languages. See also control tables for tables that are normally coded and embedded within programs as data structures SONY VAIO VGN-FW25T/B Battery
  • (i.e. not compiled statements) but could equally be read in from a flat file, database or even retrieved from a spreadsheet.
  • using stored procedures that run on database servers, as opposed to greater reliance on logic running in middle-tier application servers in a multi-tier architecture  SONY VAIO VGN-FW21Z Battery .
  • The extent to which business logicshould be placed at the back-end versus another tier is a subject of ongoing debate. For example, Toon Koppelaars presents a detailed analysis of alternative Oracle-based architectures that vary in the placement of business logic SONY VAIO VGN-FW21M Battery ,
  • concluding that a database-centric approach has practical advantages from the standpoint of ease of development and maintainability.[1]
  • using a shared database as the basis for communicating between parallel processes in distributed computing applicationsSONY VAIO VGN-FW21L Battery ,
  • as opposed to direct inter-process communication via message passingfunctions and message-oriented middleware. A potential benefit of database-centric architecture in distributed applications is that it simplifies the design by utilizing DBMS-provided transaction processing and indexing to achieve a high degree of reliability SONY VAIO VGN-FW21J Battery ,
  • performance, and capacity. For example, Base One describes a database-centric distributed computing architecture for grid andcluster computing, and explains how this design provides enhanced security, fault-tolerance, and scalability Dell Latitude E6400 ATG Battery

Control tables are tables that control the program flow or play a major part in program control. There are no rigid rules concerning the structure or content of a control table - its only qualifying attribute is its ability to direct program flow in some way through its 'execution' by an associated interpreter Asus M52N Battery .

The design of such tables is sometimes referred to as table driven design[1][2]. In some cases, control tables can be specific implementations of finite state machine-based automata-based programming  SONY VAIO VGN-FW21E Battery .

Control tables often have the equivalent of conditional expressions or function references embedded in them, usually implied by their relative column position in the association list  Ibm THINKPAD X200 Battery .

Control tables reduce the need for programming similar structures or program statements over and over again. The two-dimensional nature of most tables makes them easier to view and update than the one-dimensional nature of program code. In some cases, non-programmers can be assigned to maintain the control tables Ibm THINKPAD X200 Battery .

Typical usage

  • Transformation of input values to:
  • an index ,for later branching or pointer lookup
  • a program name, relative subroutine number, program label or program offset, to alter control flow Ibm THINKPAD X200 Battery
  • Controlling a main loop in event-driven programming using a control variable for state transitions
  • Controlling the program cycle for Online transaction processing applications HP Pavilion DV7 battery

More advanced usage

  • Acting as virtual instructions for a virtual machine processed by an interpreter

similar to bytecode - but usually with operations implied by the table structure itself Dell Latitude E6500 battery

Table structure

The tables can have multiple dimensions, of fixed or variable lengths and are usually portable between computer platforms, requiring only a change to the interpreter, not the algorithm itself - the logic of which is essentially embodied within the table structure and content  Dell Latitude E6500 battery .

The structure of the table may be similar to a multimap associative array, where a data value (or combination of data values) may be mapped to one or more functions to be performed. Dell Latitude E6500 battery

One dimensional tables

In perhaps its simplest implementation, a control table may sometimes be a one-dimensional table for directly translating a raw datavalue to a corresponding subroutine offset, index or pointer using the raw data value either directly as the index to the array, or by performing some basic arithmetic on the data beforehand Dell Latitude E6500 battery .

This can be achieved in constant time (without a linear search or binary searchusing a typical lookup table on an associative array). In most architectures, this can be accomplished in two or three machine instructions - without any comparisons or loops  Dell Latitude E6400 Battery .

The technique is known as a "trivial hash function" or, when used specifically for branch tables, "double dispatch". For this to be feasible, the range of all possible values of the data needs to be small (e.g. an ASCIIor EBCDICcharacter value which have a range of hexadecimal '00' - 'FF'  Dell Latitude E6400 Battery .

If the actual range is guaranteed to be smaller than this, the array can be truncated to less than 256 bytes).

Table to translate raw ASCII values (A,D,M,S) to new subroutine index (1,4,3,2) in constant time using one-dimensional array Dell Latitude E6400 battery

(gaps in the range are shown as '..' for this example, meaning 'all hex values up to next row'. The first two columns are not part of the array)

A two byte raw data value would require a minimum table size of 65,534 bytes - to handle all input possibilities - whilst allowing just 256 different output values Dell Vostro 1310 battery .

However, this direct translation technique provides an extremely fast validation & conversion to a (relative) subroutine pointer if the heuristics, together with sufficient fast access memory, permits its use.In automata-based  Dell Studio 1735 battery

programming and pseudoconversational transaction processing, if the number of distinct program states is small, a "dense sequence" control variable can be used to efficiently dictate the entire flow of the main program loop. Dell Studio 1535 battery

Branch tables

A branch table is a one dimensional 'array' of contiguous machine code branch/jump instructions to effect a multiway branch to a program label when branched into by an immediately preceding, and indexed branch Dell Vostro A860 battery .

It is sometimes generated by an optimizing compiler to execute a switch statement - provided that the input range is small and dense, with few gaps (as created by the previous array example). Dell Vostro A840 battery

Although quite compact - compared to the multiple equivalent If statements - the branch instructions still carry some redundancy, since the branch opcode and condition code mask are repeated alongside the branch offsets Dell Studio XPS 1640 battery .

Control tables containing only the offsets to the program labels can be constructed to overcome this redundancy (at least in assembly languages) and yet requiring only minor execution time overhead compared to a conventional branch table Dell Vostro 1720 battery .

Multi-dimensional tables

More usually, a control table can be thought of as a Truth table or as an executable ("binary") implementation of a printed decision table (or a tree of decision tables, at several levels). They contain (often implied) propositions, together with one or more associated 'actions'  Dell Vostro 1710 battery .

These actions are usually performed by generic or custom-built subroutines that are called by an "interpreter" program. The interpreter in this instance effectively functions as a virtual machine, that 'executes' the control table entries and thus provides a higher level of abstraction than the underlying code of the interpreter Dell Inspiron 1320n battery .

A control table can be constructed along similar lines to a language dependent switch statement but with the added possibility of testing for combinations of input values (using boolean style AND/ORconditions) and potentially calling multiple subroutines(instead of just a single set of values and 'branch to' program labels) Dell Inspiron 1320 battery .

(The switch statement construct in any case may not be available, or has confusingly differing implementations in high level languages (HLL). The control table concept, by comparison, has no intrinsic language dependencies, but might nevertheless be implemented differently according to the available data definition features of the chosen programming language.) Dell Inspiron 1764 battery

Table content

A control table essentially embodies the 'essence' of a conventional program, stripped of its programming language syntax and platform dependent components (e.g. IF/THEN DO.., FOR.., DO WHILE.., SWITCH, GOTO, CALL)  Dell Inspiron 1564 battery

and 'condensed' to its variables (e.g. input1), values (e.g. 'A','S','M' and 'D'), and subroutine identities (e.g. 'Add','subtract,..' or #1, #2,..). The structure of the table itself typically implies the (default) logical operations involved - such as 'testing for equality' Dell Inspiron 1464 battery ,

performing a subroutine and 'next operation' or following the default sequence (rather than these being explicitly stated within program statements - as required in other programming paradigms). Dell Inspiron N7010 battery

A multi-dimensional control table will normally, as a minimum, contain value/action pairs and may additionally contain operators and type information such as, the location, size and format of input or output data, whether data conversion (or other run-timeprocessing nuances) is required before or after processing (if not already implicit in the function itself) Dell Inspiron N5010 battery .

The table may or may not contain indexes or relative or absolute pointers to generic or customized primitivesor subroutines to be executed depending upon other values in the "row".

The table illustrated below applies only to 'input1' since no specific input is specified in the table Dell Inspiron N3610 battery .

(This side-by-side pairing of value and action has similarities to constructs in Event-driven programming, namely 'event-detection' and 'event-handling' but without (necessarily) the asynchronous nature of the event itself) Dell Inspiron N4010 battery

The variety of values that can be encoded within a control table is largely dependent upon the computer language used. Assembly language provides the widest scope for data types including (for the actions), the option of directly executable machine code  Dell Inspiron 9100 battery .

Typically a control table will contain values for each possible matching class of input together with a corresponding pointer to an action subroutine. Some languages claim not to support pointers (directly) but nevertheless can instead support  Dell Studio XPS 1340 battery

an index which can be used to represent a 'relative subroutine number' to perform conditional execution, controlled by the value in the table entry (e.g. for use in an optimized SWITCH statement - designed with zero gaps (i.e. a multiway branch) ) Dell XPS M2010 battery .

Comments positioned above each column (or even embedded textual documentation) can render a decision table 'human readable' even after 'condensing down' (encoding) to its essentials Dell XPS M1730 battery

(and still broadly in-line with the original program specification - especially if a printed decision table, enumerating each unique action, is created before coding begins). The table entries can also optionally contain counters to collect run-time statistics for 'in-flight' or later optimization Dell Inspiron Mini 12 battery

Table location

Control tables can reside in static storage, on auxiliary storage, such as a flat file or on a database or may alternatively be partially or entirely built dynamically at program initialization time from parameters (which themselves may reside in a table) Hp 2230 Battery .

For optimum efficiency, the table should be memory resident when the interpreter begins to use it.

The interpreter and subroutines

The interpreter can be written in any suitable programming language including a high level language Hp cq20 Battery .

A suitably designed genericinterpreter, together with a well chosen set of generic subroutines (able to process the most commonly occurring primitives) , would require additional conventional coding only for new custom subroutines (in addition to specifying the control table itself) Hp Pavilion cq72 Battery .

The interpreter, optionally, may only apply to some well-defined sections of a complete application program (such as the main control loop) and not other, 'less conditional', sections (such as program initialization, termination and so on) Hp Pavilion cq42 Battery .

The interpreter does not need to be unduly complex, or produced by a programmer with the advanced knowledge of a compiler writer, and can be written just as any other application program - except that it is usually designed with efficiency in mind. Its primary function is to "execute" the table entries as a set of "instructions" Hp Pavilion cq62 Battery .

There need be no requirement for parsing of control table entries and these should therefore be designed, as far as possible, to be 'execution ready', requiring only the "plugging in" of variables from the appropriate columns to the already compiled generic code of the interpreter  Hp Pavilion dm4 Battery .

The program instructions are, in theory, infinitely extensible and constitute (possibly arbitrary) values within the table that are meaningful only to the interpreter. The control flow of the interpreter is normally by sequential processing of each table row but may be modified by specific actions in the table entries Hp Pavilion dm3 Battery .

These arbitrary values can thus be designed with efficiency in mind - by selecting values that can be used as direct indexes to data or function pointers. For particular platforms/language, they can be specifically designed to minimize instruction path lengthsusing branch table values or even, in some cases such as in JIT compilers, consist of directly executable machine code "snippets" (or pointers to them) Samsung P467 battery .

The subroutines may be coded either in the same language as the interpreter itself or any other supported program language (provided that suitable inter-language 'Call' linkage mechanisms exist). Samsung R522 battery

The choice of language for the interpreter and/or subroutines will usually depend upon how portable it needs to be across various platforms. There may be several versions of the interpreter to enhance theportability of a control table  Apple A1281 battery.

A subordinate control table pointer may optionally substitute for a subroutine pointer in the 'action' column(s) if the interpreter supports this construct, representing a conditional 'drop' to a lower logical level, mimicking a conventional structured program structure  Apple A1280 battery .

Performance considerations

At first sight, the use of control tables would appear to add quite a lot to a program's overhead, requiring, as it does, an interpreter process before the 'native' programming language statements are executed  Ibm ThinkPad X200T Battery .

This however is not always the case. By separating (or 'encapsulating') the executable coding from the logic, as expressed in the table, it can be more readily targeted to perform its function most efficiently. This may be experienced most obviously in a spreadsheet application HP Pavilion DV6 Battery

- where the underlying spreadsheet software transparently converts complex logical 'formulae' in the most efficient manner it is able, in order to display its results.

The examples below have been chosen partly to illustrate potential performance gains that may not only compensate significantly for the additional tier of abstraction HP Pavilion DV6-1000ET Battery ,

but also improve upon - what otherwise might have been - less efficient, less maintainable and lengthier code. Although the examples given are for a 'low level' assembly language and for the 'C' language, it can be seen, in both cases HP Pavilion DV6-1001TX Battery ,

that very few lines of code are required to implement the control table approach and yet can achieve very significant constant time performance improvements, reduce repetitive source coding and aid clarity, as compared with verbose conventional program language constructs. See also the quotationsby Donald Knuth, concerning tables and the efficiency of multiway branching in this article HP Pavilion DV6-1001XX Battery .

Examples of control tables

The following examples are arbitrary (and based upon just a single input for simplicity), however the intention is merely to demonstrate how control flow can be effected via the use of tables instead of regular program statements HP Pavilion DV6-1002TX Battery .

It should be clear that this technique can easily be extended to deal with multiple inputs, either by increasing the number of columns or utilizing multiple table entries (with optional and/or operator). Similarly, by using (hierarchical) 'linked' control tables, structured programming can be accomplished (optionally using indentation to help highlight subordinate control tables) HP Pavilion DV6-1003TX Battery .

"CT1" is an example of a control table that is a simple lookup table. The first column represents the input value to be tested (by an implied 'IF input1 = x') and, if TRUE, the corresponding 2nd column (the 'action') contains a subroutine address to perform by a call(or jump to - similar to a SWITCH statement) HP Pavilion DV6-1004TX Battery .

It is, in effect, a multiway branch with return (a form of "dynamic dispatch"). The last entry is the default case where no match is found.

For programming languages that support pointers within data structures alongside other data values  HP Pavilion DV6-1005EA Battery ,

the above table (CT1) can be used to direct control flow to an appropriate subroutines according to matching value from the table (without a column to indicate otherwise, equality is assumed in this simple case) HP Pavilion DV6-1005EZ Battery .

Assembly language example for IBM/360 (maximum 16Mb address range) or Z/Architecture

No attempt is made to optimize the lookup in coding for this first example ,and it uses instead a simple linear search technique - purely to illustrate the concept and demonstrate fewer source lines  HP Pavilion DV6-1005TX Battery .

To handle all 256 different input values, approximately 265 lines of source code would be required (mainly single line table entries) whereas multiple 'compare and branch' would have normally required around 512 source lines (the size of the binary is also approximately halved HP Pavilion DV6-1006TX Battery ,

each table entry requiring only 4 bytes instead of approximately 8 bytes for a series of 'compare immediate'/branch instructions (For larger input variables, the saving is even greater)  HP Pavilion DV6-1007TX Battery .

improving the performance of the interpreter in above example

To make a selection in the example above, the average instruction path length (excluding the subroutine code) is '4n/2 +3' , but can easily be reduced HP Pavilion DV6-1008TX Battery ,

where n = 1 to 64, to a constant time with a path length of '5' with zero comparisons, if a 256 byte translate table is first utilized to create a direct index to CT1 from the raw EBCDIC data HP Pavilion DV6-1009EL Battery .

Where n = 6, this would then be equivalent to just 3 sequential compare & branch instructions. However, where n<=64, on average it would need approximately 13 times less instructions than using multiple compares HP Pavilion DV6-1009TX Battery .

Where n=1 to 256, on average it would use approximately 42 times less instructions - since, in this case, one additional instruction would be required (to multiply the index by 4). HP Pavilion DV6-1010EA Battery

Improved interpreter (up to 26 times le ss executed instructions than the above example on average, where n= 1 to 64 and up to 13 times less than would be needed using multiple comparisons). HP Pavilion DV6-1010ED Battery

To handle 64 different input values, approximately 85 lines of source code (or less) are required (mainly single line table entries) whereas multiple 'compare and branch' would require around 128 lines (the size of the binary is also almost halved - despite the additional 256 byte table required to extract the 2nd index) HP Pavilion DV6-1010ET Battery .

The next example below illustrates how a similar effect can be achieved in languages that do not support pointer definitions in data structures but do support indexed branching to a subroutine - contained within a (0-based) array of subroutine pointers. HP Pavilion DV6-1010TX Battery

The table (CT2) is used to extract the index (from 2nd column) to the pointer array (CT2P). If pointer arrays are not supported, a SWITCH statement or equivalent can be used to alter the control flow to one of a sequence of program labels HP Pavilion DV6-1011TX Battery

(e.g.: case0,case1,case2,case3,case4) which then either process the input directly, or else perform a call (with return) to the appropriate subroutine (default,Add,Subtract,Multiply or Divide,..) to deal with it. HP Pavilion DV6-1012TX Battery

As in above examples, it is possible to very efficiently translate the potential ASCII input values (A,S,M,D or unknown) into a pointer array index without actually using a table lookup, but is shown here as a table for consistency with the first example. HP Pavilion DV6-1013EA Battery

Multi-dimensional control tables can be constructed (i.e. customized) that can be 'more complex' than the above examples that might test for multiple conditions on multiple inputs or perform more than one 'action', based on some matching criteria. An 'action' can include a pointer to another subordinate control table HP Pavilion DV6-1013TX Battery .

The simple example below has had an implicit 'OR' condition incorporated as an extra column (to handle lower case input, however in this instance, this could equally have been handled simply by having an extra entry for each of the lower case characters specifying the same subroutine identifier as the upper case characters) HP Pavilion DV6-1016EZ Battery .

An extra column to count the actual run-time events for each input as they occur is also included.

The control table entries are then much more similar to conditional statements in procedural languages but, crucially,  HP Pavilion DV6-1018EL Battery

without the actual (language dependent) conditional statements (i.e. instructions) being present (the generic code is physically in the interpreter that processes the table entries, not in the table itself - which simply embodies the program logic via its structure and values) HP Pavilion DV6-1020EC Battery .

In tables such as these, where a series of similar table entries defines the entire logic, a table entry number or pointer may effectively take the place of a program counter in more conventional programs and may be reset in an 'action', also specified in the table entry HP Pavilion DV6-1020ED Battery .

The example below (CT4) shows how extending the earlier table, to include a 'next' entry (and/or including an 'alter flow' (jump[disambiguation needed]) subroutine) can create a loop (This example is actually not the most efficient way to construct such a control table but, by demonstrating a gradual 'evolution' from the first examples above, shows how additional columns can be used to modify behavior HP Pavilion DV6-1018EL Battery .

) The fifth column demonstrates that more than one action can be initiated with a single table entry - in this case an action to be performed after the normal processing of each entry ('-' values mean 'no conditions' or 'no action') HP Pavilion DV6-1020EC Battery .

Structured programming or "Goto-less" code, (incorporating the equivalent of 'DO WHILE' or 'for loop' constructs), can also be accommodated with suitably designed and 'indented' control table structures HP Pavilion DV6-1020ED Battery .

Table-driven rating

In the specialist field of telecommunications rating (concerned with the determining the cost of a particular call), table-driven rating techniques illustrate the use of control tables in applications where the rules may change frequently because of market forces. The tables that determine the charges may be changed at short notice by non-programmers in many casesHP Pavilion DV6-1020EI Battery ]..

If the algorithms are not pre-built into the interpreter (and therefore require additional runtime interpretation of an expression held in the table), it is known as "Rule-based Rating" rather than table-driven rating (and consequently consumes significantly more overhead) HP Pavilion DV6-1020EJ Battery .


A spreadsheet data sheet can be thought of as a two dimensional control table, with the non empty cells representing data to the underlying spreadsheet program (the interpreter)  HP Pavilion DV6-1020EK Battery .

The cells containing formula are usually prefixed with an equals sign and simply designate a special type of data input that dictates the processing of other referenced cells - by altering the control flow within the interpreter HP Pavilion DV6-1020EL Battery .

It is the externalization of formulae from the underlying interpreter that clearly identifies both spreadsheets, and the above cited "rule based rating" example as readily identifiable instances of the use of control tables by non programmers HP Pavilion DV6-1020EQ Battery .

Programming paradigm

If the control tables technique could be said to belong to any particular programming paradigm, the closest analogy might be Automata-based programming or "reflective" (a form of metaprogramming - since the table entries could be said to 'modify' the behaviour of the interpreter) HP Pavilion DV6-1020ET Battery .

The interpreter itself however, and the subroutines, can be programmed using any one of the available paradigms or even a mixture. The table itself can be essentially a collection of "raw data" values that do not even need to be compiled and could be read in from an external source (except in specific, platform dependent, implementations using memory pointers directly for greater efficiency) HP Pavilion DV6-1022EL Battery .

A multi-dimensional control table has some conceptual similarities to bytecode operating on a virtual machine, in that a platform dependent "interpreter" program is usually required to perform the actual execution (that is largely conditionally determined by the tables content) HP Pavilion DV6-1023EF Battery .

There are also some conceptual similarities to the recent Common Intermediate Language (CIL) in the aim of creating a common intermediate 'instruction set' that is independent of platform (but unlike CIL, no pretentions to be used as a common resource for other languages) HP Pavilion DV6-1023EM Battery .

When a multi-dimensional control table is used to determine program flow, the normal "hardware" Program Counter function is effectively simulated with either a pointer to the first (or next) table entry or else an index to it  HP Pavilion DV6-1025EF Battery .

"Fetching" the instruction involves decoding the data in that table entry - without necessarily copying all or some of the data within the entry first. Programming languages that are able to use pointers have the dual advantage that less overhead is involved, both in accessing the contents and also advancing the counter to point to the next table entry after execution  HP Pavilion DV6-1025EI Battery .

Calculating the next 'instruction' address (i.e. table entry) can even be performed as an optional additional action of every individual table entry allowing loops and or jumpinstructions at any stage  HP 6730B battery .

Partager cet article

Repost 0
Published by batterys - dans Laptop Battery
commenter cet article