An SQL statement that has been executed but whose result set has not yet been canceled or fully processed. When using default result sets, Microsoft® SQL Server™ supports only one active statement at a time on a connection. ODBC and OLE DB-based applications support multiple active statements on a SQL Server connection when using application programming interface (API) server cursors.
A custom extension, written in any language that supports the Component Object Model (COM), usually Microsoft Visual Basic®, which interacts with the OLAP Manager and provides specific functionality. Add-ins are registered with the online analytical processing (OLAP) Add-In Manager. They are called by the OLAP Add-In Manager in response to user actions in the user interface.
The OpenRowset function in the FROM clause of a query, which allows all connection information for an external server and data source to be issued every time the data must be accessed. The OpenRowset function provides the properties and parameters necessary to access specific data.
See Microsoft ActiveX® Data Objects (Multidimensional) (ADO MD).
Functions that calculate summary values, such as averages and sums, from the values in a particular column and return a single value for each set of rows to which the function applies. The aggregate functions are: AVG, COUNT, COUNT(*), MAX, MIN, SUM, STDEV, STDEVP, VAR, and VARP.
A query that summarizes information from multiple rows by including an aggregate function such as SUM or AVG. Aggregate queries can also display subtotal information by creating groups of rows that have data in common.
A table or structure containing precalculated data for a cube. Aggregations support rapid and efficient querying of a multidimensional database. See also precalculate.
A string that is combined with a system-defined ID to create a unique name for a partition’s aggregation table. A default string is generated based on the name of the partition and the name of its parent cube, but a user-defined string of up to 21 characters can be specified to replace the automatically generated string.
A user-defined response to a SQL Server event. Alerts can either execute a defined task or send an e-mail and/or pager message to a specified operator.
An alternative name for a table or column in expressions that is often used to shorten the name for subsequent reference in code, prevent possible ambiguous references, or provide a more descriptive name in query output. When referring to a database username shared by several login IDs, aliases have been replaced by roles.
The optional highest level of a dimension, named “(All)” by default. The All level contains a single member that is the summary of all members of the immediately subordinate level.
An organization of American industry and business groups that develops trade and communication standards for the United States. Through membership in International Organization for Standardization (ISO) and International Electrotechnical Commission (IEC), ANSI coordinates American standards with corresponding international standards. ANSI published the ANSI SQL-92 standard in conjunction with the ISO/IEC SQL-92 standard.
A member in a superior level in a dimension hierarchy that is related through lineage to the current member within the dimension hierarchy. For example, in a Time dimension containing the levels Quarter, Month, Day, Qtr1 is an ancestor of January 1. See also child, descendant, parent, sibling.
A pull subscription that allows a server known to the Publisher only for the duration of the connection to receive a subscription to a publication. Anonymous subscriptions require less overhead than standard pull subscriptions because information about them is not stored at the Publisher or Distributor.
See American National Standards Institute.
An operating system option, AutoANSItoOEM controls the default conversion behavior when you connect to a server. If ON (default) conversion occurs in these cases:
The DB-Library Automatic ANSI to OEM option converts characters from OEM to ANSI when communicating with SQL Server, and from ANSI to OEM when communicating from SQL Server to the client. You can set Automatic ANSI to OEM by using the SQL Server Client Network Utility.
See application programming interface.
A server cursor built to support the cursor functions of an application programming interface (API), such as ODBC, OLE DB, ADO, and DB-Library. An application does not usually request a server cursor directly; it calls the cursor functions of the API. The SQL Server interface for that API implements a server cursor if that is the best way to support the requested cursor functionality.
A Windows NT file that records events. It can be viewed only by using Windows NT Event Viewer. When SQL Server is configured to use the Windows NT application log, each SQL Server session writes new events to that log. (Unlike the SQL Server error log, a new application log is not created each time you start SQL Server.)
A set of routines available in an application, such as DB-Library, for use by software programmers when designing an application interface.
A SQL Server role created to support the security needs of an application. Activated by a password.
A switch supported by a function that allows you to specify a particular behavior. Sometimes called an option or parameter.
The basic unit of replication. An article contains data originating from a table or stored procedure marked for replication. One or more articles are contained within a publication.
Identifies the user and verifies the permission to connect with SQL Server.
The operation that verifies the permissions and access rights granted to a user.
Recovery that occurs every time SQL Server is restarted. Automatic recovery protects your database if there is a system failure. In each database, the automatic recovery mechanism checks the transaction log. If the log has committed transactions that have not been written to the database, it performs those transactions again. This action is known as rolling forward.
Synchronization accomplished automatically by SQL Server when a server initially subscribes to a publication. A snapshot of the table data and schema are written to files for transfer to the Subscriber. The table schema and data are transferred by the Distribution Agent. No operator intervention is required.
A set of tuples. Each tuple is a vector of members. A set of axes defines the coordinates of a multidimensional dataset. For more information about axes, see your OLE DB documentation. See also tuple, slice.
A term applied to the database server level where processing, data storage, and data retrieval occur.
A database, transaction log, file, or filegroup in a database. A backup is made to tape, named pipe, or hard disk. Backups are made using either SQL Server Enterprise Manager or the BACKUP statement.
A tape, disk file, or named pipe used in a backup or restore operation.
In a Windows NT domain, a backup domain controller (BDC) is a server that receives a copy of the domain’s security database from the primary domain controller (PDC) and shares the user login authentication load.
A file that stores a full or partial database, transaction log, or file and/or filegroup backup.
The disk, tape, or named pipe used to store the backup set.
The output of a single backup operation.
Any system-supplied data type, for example, char, varchar, binary, and varbinary, from which user-defined data types are made.
See underlying object.
A table from which a view is derived. Also called an underlying table. A view can have one or more base tables or base views.
A set of SQL statements submitted together and executed as a group. A script is often a series of batches submitted one after the other. A batch, as a whole, is compiled only one time and is terminated by an end-of-batch signal (such as the GO command in SQL Server utilities).
Files that store table data during synchronization. The .sch and .bcp files are a synchronization set that represents a snapshot in time of an article.
A command prompt utility that copies SQL Server data to or from an operating system file in a user-specified format.
A data type storing hexadecimal numbers. The binary data type can contain 0 bytes, but when specified, n must be a value from 1 through 8000. Storage size is n regardless of the actual length of the entry.
In SQL application programming interfaces (APIs), associating a result set column with a program variable so that data is moved automatically into or out of a program variable when a row is fetched or updated. In Transact-SQL, associating rules or defaults with table columns by using sp_bindrule or sp_bindefault.
A data type that holds a value of either 1 or 0. Integer values other than 1 or 0 are accepted, but interpreted as 1. The storage size is 1 byte. Multiple bit data types in a table can be collected into bytes. Use bit for true/false or yes/no data.
A type of data column containing binary data such as graphics, sound, or compiled code. This is a general term for text or image data type.
A series of statements enclosed by BEGIN and END. You can nest BEGIN...END blocks within other BEGIN...END blocks.
An expression that returns a true or false value. For example, comparing the value of 1 to a value of 5 returns a false value (1=5).
A function that lets you scan database rows and update their values one row at a time. Several browse mode functions return information that an application can use to examine the structure of a complicated ad hoc query.
A group of functions provided by SQL Server and grouped as follows:
An organizational standard operating procedure that requires certain policies be followed to ensure a business is run correctly. Business rules ensure that the database maintains its accuracy with business policies.
A buffer used to hold data during input/output (I/O) transfers between disk and random access memory (RAM).
Pages that are held in cache. One page is 8K of data.
A member of a dimension whose value is calculated at run time using an expression. Calculated member values may be derived from other members’ values. A calculated member is any member that is not an input member. For example, a calculated member Profit can be determined by subtracting the value of the member Costs from the value of the member Sales. See also Calculated Member Builder, input member.
A dialog box in the OLAP Manager used to create calculated members. You can pick parent members and members from a list. In addition, you can construct calculated value expressions using the cube data and analytical functions provided. See also calculated member.
The interface supported by ODBC for use by an application.
A unique identifier for a row within a database table. A candidate, or surrogate, key can be made up of one or more columns. By definition, every table must have at least one candidate key, in which case it becomes the primary key for a table automatically. However, it is possible for a table to have more than one candidate key, in which case one of them must be designated as the primary key. Any candidate key that is not the primary key is called the alternate key.
The process of recording and storing information during the monitoring process.
All the possible combinations of the rows from each of the tables involved in a join operation. The number of rows in a Cartesian product of two tables, for example, is equal to the number of rows in the first table multiplied by the number of rows in the second table.
A delete that deletes all related database rows or columns.
An update that updates all related database rows or columns.
In a relational database, the addressable attribute of a row and column. In a cube, the set of properties, including a value, specified by the intersection when one member is selected from each dimension. See also coordinate.
A text file that contains SQL statements for all changes made to a database, in the order in which they were made, during an editing session. Each change script is saved in a separate text file with an .sql extension. Change scripts can be applied back to the database later, using a tool such as isql.
Data stored in a bulk copy data file using text characters.
A character set determines the types of characters that SQL Server recognizes in the char, varchar, and text data types. A character set is a set of 256 letters, digits, and symbols specific to a country or language. The printable characters of the first 128 values are the same for all character sets. The last 128 characters, sometimes referred to as extended characters, are unique to each character set. A character set is related to, but separate from, Unicode characters.
A character data type that holds a maximum of 8,000 characters. Storage size is n regardless of the actual length of the entry.
Data values that are acceptable in a column. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column. When a table is dropped, CHECK constraints are also dropped.
The point at which all changed data pages are written to disk.
A member in the next lower level in a hierarchy that is directly related to the current member. For example, in a Time dimension containing the levels Quarter, Month, and Day, January is a child of Qtr1. See also ancestor, descendant, parent, sibling.
See call-level interface.
A front-end application that uses the services provided by a server. The computer that hosts the application is referred to as the client computer. SQL Server client software enables computers to connect to a computer running SQL Server over a network.
An application that retrieves data from an OLAP server and performs local analysis and presentation of data from relational or multidimensional databases. Client applications connect to the OLAP server through the PivotTable® Service component. See also PivotTable Service.
A cursor implemented on the client. The entire result set is first transferred to the client, and the client application programming interface (API) software implements the cursor functionality from this cached result set. Client cursors typically do not support all types of cursors, only static and forward-only cursors.
A system of computing in which two or more computers share processing across a network. The server computer manages a shared resource, such as a database, and responds to requests from clients for use of this resource. The client computer interacts with a user and makes requests for use of a shared resource. Client/server computing separates the functions of an application into two parts: a front end component and a back end component. The client presents and manipulates data on the workstation; the server stores, retrieves, and protects data.
An index in which the logical or indexed order of the key values is the same as the physical stored order of the corresponding rows that exist in a table.
See character set.
In an SQL database table, the area, sometimes called a field, in each row that stores the data about an attribute of the object modeled by the table (for example, the ContactName column in the Customers table of the Northwind database). Individual columns are characterized by their maximum length and the type of data that can be placed in them. A column contains an individual data item within a row.
A restriction used to enforce data integrity on a column. SQL Server provides these types of constraints: CHECK, DEFAULT, FOREIGN KEY REFERENCE, PRIMARY KEY, and UNIQUE.
A component object model (COM) compound file consisting of a root storage object containing at least one stream object representing its native data, along with one or more storage objects corresponding to its linked and embedded objects. The root storage object maps to a file name in whatever file system it happens to reside.
To save a change to a database, cube, or dimension. An SQL COMMIT statement guarantees that all or none of the transaction’s modifications are made a permanent part of the database. A COMMIT statement also frees resources, such as locks, used by the transaction. See also roll back.
A key created to make explicit a logical relationship between two tables in a database. See also primary key and foreign key.
A relationship between more than two entities, subsets, dependencies, or relations.
The programming model upon which several SQL Server and database application programming interfaces (APIs) such as SQL-DMO, OLE DB, and ADO are based.
An index that uses more than one column in a table to index data.
A key composed of two or more columns. A drawback of composite keys is that they require more complex joins when two or more tables are joined.
Combining two or more character strings or expressions into a single character string or expression, or combining two or more binary strings or expressions into a single binary string or expression.
A process that allows multiple users to access and change shared data at the same time. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.
Controls concurrent access. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.
When more than one user accesses and updates shared data at the same time.
A successful login to a computer running SQL Server.
The ability of different classes of computers to communicate with one another.
Any constant or literal string, built-in function, or mathematical expression. The value cannot include the names of any columns or other database objects.
A property that can be placed on a column or set of columns in a table. SQL Server provides these constraints: CHECK, DEFAULT, FOREIGN KEY, REFERENCE, PRIMARY KEY, and UNIQUE.
The media inserted when the initial medium becomes full, allowing continuation of the backup operation.
See master database.
A report whose summary values are controlled by user-defined groupings or breaks.
Transact-SQL keywords that control the flow of execution of SQL statements, statement blocks, and stored procedures.
Protocols that control the access that Subscribers have to a publication by marking the publication as either unrestricted or restricted.
An element (member or tuple) of an axis. The intersection of a set of coordinates determines a cell. See also cell.
A repeating subquery. Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery, the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, one time for each row that is selected by the outer query.
A SQL Server statistic that reports the time, in milliseconds, the central processing unit (CPU) spent on SQL Server work.
An option that adds object-creation statements to a script.
A subset of data, usually constructed from a data warehouse, organized and summarized into a multidimensional structure defined by a set of dimensions and measures. A cube’s data is stored in one or more partitions.
A tool in the OLAP Manager that you can use to create new cubes or edit existing ones.
See local cube.
A database object used by applications to manipulate data by rows instead of by sets. Using cursors, multiple operations can be performed row by row against a result set with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the database(s). For example, a cursor can be generated to include a list of all user-defined table names within a database. After the cursor has been opened, movement (fetching) through the result set can include multiple operations against each table by passing each table name as a variable. Cursors are powerful when combined with stored procedures and the EXECUTE statement (to build strings dynamically). Cursors are a powerful component of the SQL Server application programming interfaces (APIs).
A part of the ODBC and DB-Library application programming interfaces (APIs) that implements client cursors. A cursor library is not commonly used in current systems; server cursors are used instead.
The coded representation of information for use in a computer. Data has attributes, such as type and length.
See page.
The subset of SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements.
The process of setting up databases and creating database objects, such as tables, indexes, constraints, defaults, rules, procedures, triggers, and views.
The subset of SQL statements used for modeling the structure (rather than the contents) of a database or cube. The DDL gives you the ability to create, modify, and remove databases and database objects.
An SQL-specific query that contains Data Definition Language (DDL) statements. These are statements that allow you to create or alter objects (such as tables, indexes, views, and so on) in the database.
System tables containing descriptions of database objects and how they are structured.
See system tables.
The exponential growth in size of a multidimensional structure, such as a cube, due to the storage of precalculated data.
A file that contains data such as tables, rows, and stored procedures. Databases can span multiple data files. See also log file.
Accuracy and reliability of data. Data integrity is important in both single-user and multiuser environments. In multiuser environments, where data is shared, both the potential for and the cost of data corruption is high. In large scale relational database management system (RDBMS) environments, data integrity is a primary concern.
A mechanism of recording information to determine the source of any piece of data, and the transformations applied to that data using Data Transformation Services (DTS). Data lineage can be tracked at the package and row levels of a table and provides a complete audit trail for information stored in a data warehouse.
The subset of SQL statements used to retrieve and manipulate data.
A subset of the contents of a data warehouse, stored within its database. A data mart tends to contain data focused at the department level, or on a specific business area. It is frequently implemented to manage volume and scope of data. See also data warehouse.
The process of extracting data from operational systems to a data warehouse with minimal effect on the source systems, and the transformation of the source data into a format consistent with the design and requirements of the data warehouse. See also data transformation, data warehouse.
Adding, deleting, or changing information in a database by using the INSERT, DELETE, and UPDATE Transact-SQL statements.
An OLE DB service provider that provides the infrastructure to import, export, and transform data between heterogeneous data stores using Data Transformation Services (DTS).
The process of making data consistent either manually, or automatically using programs. For example, a database with inconsistent data might contain customer addresses that have the State column set to “WA” for one customer, but “Washington” for another. Data scrubbing is performed prior to or during the transfer of data to a data warehouse. See also data transformation.
The ability to share individual pieces of data transparently from a database across different applications.
The source of data for an object such as a cube or dimension. Also, the specification of the information necessary to access source data. Sometimes refers to a DataSource object. See also data source name.
The name assigned to an ODBC data source. Applications can use data source names (DSNs) to request a connection to a system ODBC data source, which specifies the computer name and (optionally) the database to which the DSN maps. A DSN can also refer to an OLE DB connection.
The process of copying data to or from a computer running SQL Server.
A set of operations applied to source data before it can be stored in the destination using Data Transformation Services (DTS). For example, DTS allows calculating new values from one or more source columns, or breaking a single column into multiple values to be stored in separate destination columns. Data transformation is performed during the process of copying data into a data warehouse.
A SQL Server component used to import, export, and transform data from different data sources.
An attribute that specifies what type of information can be stored in a column or variable. System-supplied data types are provided by SQL Server; user-defined data types can also be created. See also base data type.
Functions that transform expressions from one data type into another.
A database specifically structured for query and analysis. A data warehouse typically contains data representing the business history of an organization.
A collection of information, tables, and other objects organized and presented to serve a specific purpose, such as facilitate searching, sorting, and recombining data. Databases are stored in files.
The system tables of a database. See also system catalog.
A statement used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. Database consistency checker (DBCC) ensures the physical and logical consistency of a database, but is not corrective.
A graphical representation of any portion of a database schema. A schema is a description of a database to the database management system (DBMS), generated using the Data Definition Language (DDL) provided by the DBMS. A database diagram can be either a whole or a partial picture of the structure of a database; it includes objects for tables, the columns they contain, and the relationship between them.
A file in which databases are stored. One database can be stored in several files.
The language used for accessing, querying, updating, and managing data in relational database systems. SQL is a widely used database language. With SQL, you can retrieve data from a database, create databases and database objects, add data, modify existing data, and perform other complex functions. Many of these capabilities are implemented by using one of three types of SQL statements: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). The Microsoft SQL Server implementation of SQL is called Transact-SQL.
A repository for the collection of computerized data files that enables users to perform a variety of operations on those files, including retrieving, appending, editing, updating, and generating reports.
A name that must correspond to the rules for identifiers and can have up to 30 characters (for example, sales or payroll98).
One of the components of a database: a table, index, trigger, view, key, constraint, default, rule, user-defined data type, or stored procedure.
A user who creates a database object (table, index, view, trigger, or stored procedure).
A member of the database administrator role of a database. There is only one database owner. The owner has full permissions in that database and determines the access and capabilities provided to other users.
See query.
A collection of statements used to create database objects. Transact-SQL scripts are saved as files, usually ending with .sql.
See database consistency checker.
In general, a collection of related information made up of separate elements that can be treated as a unit. In OLE DB for OLAP, the set of multidimensional data that is the result of executing an multidimensional expression (MDX) statement. For more information about datasets, see your OLE DB documentation.
Functions used to display information about dates and times. They manipulate datetime and smalldatetime values, including arithmetic.
A SQL Server system data type. A datetime data type is stored in 8 bytes of two 4-byte integers: 4 bytes for the number of days before or after the base date of January 1, 1900, and 4 bytes for the number of milliseconds after midnight.
A series of high-level language (including C) libraries that provide the application programming interface (API) for the client in a client/server system. DB-Library sends requests from a client to a server. DB-Library allows the developer to incorporate Transact-SQL statements into an application to retrieve and update data in a SQL Server database.
See database consistency checker.
A character set that uses one or two bytes to represent a character, allowing more than 256 characters to be represented. Double Byte Character Set (DBCS) character sets are typically used in environments that use ideographic writing systems, such as Japanese, Korean, and Chinese.
See database management system.
See Data Control Language (DCL).
A situation when two users, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each user waits for the other to release the lock. SQL Server detects deadlocks and terminates one user’s process.
Database applications optimized for performance in data queries that do not change data. Decision support typically requires read-only access to data.
The Microsoft SQL Server OLAP Services server object model. Decision Support Objects (DSO) are used to create applications that define and manage cubes and other objects. DSOs can also be used to extend the functionality of the OLAP Manager or to automate the ongoing maintenance of your system.
The SQL Server built-in capacity that checks the data integrity of a specific related table.
A value inserted into a column automatically if the user does not enter one. In a relational database management system, every data element (a particular column in a particular row) must contain a value, even if that value is NULL. Because some columns do not accept null values, another value must be entered, either by the user or by SQL Server. Also, the behavior exhibited by a statement or component unless overridden by the user.
The database the user is connected to immediately after logging in to SQL Server.
The language (for example, French, German, or English) used to communicate with the server. After the default language is set, the user is logged in automatically using that language.
The default mode SQL Server uses to return a result set back to a client. Rows are sent to the client in the order they are placed in the result set, and the application must process the rows in this order. After executing an SQL statement on a connection, the application cannot do anything on the connection other than retrieve the rows in the result set until all the rows have been retrieved. The only other action that an application can perform before the end of the result set is to cancel the remainder of the result set. This is the fastest method to get rows from SQL Server to the client.
A query that removes rows from one or more tables.
The character used for separating elements in a list.
To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. Denormalization can improve efficiency and performance by reducing complexity in a data warehouse schema. See also star schema.
The relative percentage of a multidimensional structure’s cells that contain data. OLAP Services stores only cells that contain data. A dense cube requires more storage than a sparse cube of identical structure design. See also data explosion, sparsity.
Removes a permission from a user account and prevents the account from gaining permission through membership in groups or roles within the permission.
The views and procedures that depend on the specified table or view.
A member in a dimension hierarchy that is related to a member of a higher level within the same dimension. For example, in a Time dimension containing the levels Year, Quarter, Month, and Day, January is a descendant of 1997. See also ancestor, child, parent, sibling.
See Subscriber.
The subscribing table created as a replica of a published table. A destination table in a subscription database is synchronized with and contains data derived from the published table in a publication database.
See file.
A database backup that records only those changes made to the database since the last full database backup. A differential backup is smaller, and is faster to restore than a full backup and has minimal effect on performance.
A structural attribute of a cube, which is an organized hierarchy of categories (levels) that describe data in the fact table. These categories typically describe a similar set of members upon which the user wants to base an analysis. For example, a geography dimension might include levels for Country, Region, State or Province, and City. See also level, measure.
A tool in the OLAP Manager that you can use to create, examine, and edit a dimension and its levels. It offers two views: Schema, which examines and edits the dimension table structure, and Browse, which checks dimension data.
One of the hierarchies of a dimension. See also hierarchy.
A table in a data warehouse whose entries describe data in a fact table. Dimension tables present business entities. A database object stored in a data warehouse containing information used to reference the data stored in a fact table.
The default mode in which SQL Server statistics are gathered separately from the SQL Server Statistics display. Data is available immediately to SQL Server Performance Monitor; however, the statistics displayed are one period behind the statistics retrieved.
Cached pages that have been modified since the last checkpoint.
Reads that contain uncommitted data. For example, transaction1 changes a row. Transaction2 reads the changed row before transaction1 commits the change. If transaction1 rolls back the change, transaction2 reads a row that is considered to have never existed.
The process that protects against media failure by maintaining a fully redundant copy of a partition on another disk. It is recommended that you use a redundant array of independent disks (RAID) for disk mirroring.
To move transactions or snapshots of data from the Publisher to Subscribers, where they are applied to the destination tables in the subscription databases.
A database implemented on a network in which the component partitions are distributed over various nodes of the network. Depending on the specific update and retrieval traffic, distributing the database can enhance overall performance significantly.
SQL Distributed Management Objects (SQL-DMO) are 32-bit Component Object Model (COM) objects for the Microsoft Windows 95/98 and Microsoft Windows NT operating systems. SQL-DMO objects are OLE Automation compatible. The SQL-DMO object model includes objects, properties, methods and collections used to write programs to administer multiple SQL Servers distributed across a network. SQL-DMO programs can range from simple Visual Basic scripts to complex Visual C++ applications.
Data processing in which some or all of the processing, storage, and control functions, in addition to input/output functions, are situated in different places and connected by transmission facilities. The transparent access of both applications and data by programs and users is an important goal of distributed processing systems.
A single query that accesses data from heterogeneous data sources.
The replication component that moves the transactions and snapshot jobs held in distribution database tables to Subscribers.
A store-and-forward database that holds all transactions waiting to be distributed to Subscribers. The distribution database receives transactions sent to it from the Publisher by the Log Reader Agent and holds them until the Distribution Agent moves them to the Subscribers.
The server containing the distribution database. The Distributor receives all changes to published data, stores the changes in its distribution database, and transmits them to Subscribers. The Distributor may or may not be the same computer as the Publisher. See also local Distributor, remote Distributor.
See dynamic link library.
See data language.
See Distributed Management Objects.
In Windows NT security, a collection of computers grouped for viewing and administrative purposes that share a common security database.
Integrity that enforces valid entries for a given column. Domain integrity is enforced by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through REFERENCE and CHECK constraints, and rules).
See declarative referential integrity.
A technique for navigating through levels of data ranging from the most summarized (up) to the most detailed (down). For example, to view the details of sales data by year, a user can drill down to display sales data by quarter, and further to display data by month.
See Data Transformation Services.
See backup.
See backup file.
A cursor that can reflect data modifications made to the underlying data while the cursor is open. Updates, deletes, and inserts made by users are reflected in the dynamic cursor.
An executable routine containing a specific set of functions stored in a .dll file and loaded on demand when needed by the program that calls it.
The process used by SQL Server to determine the most cost-effective locks to use at any one time.
The process that detects and/or attempts to correct software failure or loss of data integrity within an relational database management system (RDBMS).
In Embedded SQL for C, an SQL statement built and executed at run time.
The location where a row and a column meet in a table. Element is synonymous with field.
The process of allowing full-text querying to occur on the current database. Execute sp_fulltext_database with action set to enable.
A trigger created with an optional encryption parameter that encrypts the definition text and cannot be decrypted. Encryption makes the information indecipherable to protect it from unauthorized viewing or use.
A method for keeping sensitive information confidential by changing data into an unreadable form.
Defines a row as a unique entity for a particular table and ensures that the column cannot contain duplicate values. It usually enforces the primary key of a table (through indexes, UNIQUE constraints, or PRIMARY KEY constraints).
A join in which the values in the columns being joined are compared for equality, and all columns are included in the results.
The SQL Server error log records information from SQL Server. You can view the error log by using SQL Server Enterprise Manager or any text editor. Each time SQL Server is started, it retains the last logs and creates a new log. You can specify the number of logs to retain.
Provides information about the context of an error. Valid error state numbers are from 1 through 127. An error state number identifies the source of the error (if the error can be issued from more than one source).
A character used to indicate that another character in an expression is meant literally and not as an operator.
A file that contains both SQL Server error messages and messages for all activities on the computer.
A lock that prevents any other transaction from acquiring a lock on a resource until the original lock on the resource is released at the end of the transaction. An exclusive lock is always applied during an update operation (INSERT, UPDATE, or DELETE).
A group of SQL statements enclosed in the transaction delimiters BEGIN TRANSACTION and COMMIT TRANSACTION, and optionally one of the following statements:
See bcp files.
A column name, function, variable, subquery, or any combination of column names, constants, and functions connected by an operator(s) in a subquery.
A SQL Server-provided procedure that dynamically loads and executes a function within a dynamic-link library (DLL) in a manner similar to a stored procedure. Actions outside of SQL Server can be triggered and external information returned to SQL Server. Return status codes and output parameters (identical to their counterparts in regular stored procedures) are also supported.
The space allocated upon creation of a SQL Server object, such as a table or index. In SQL Server, an extent is eight contiguous pages.
A row in a fact table in a data warehouse. A fact contains one or more numeric values that measure a data event such as a sales transaction.
A central table in a data warehouse that contains numerical measures and keys relating facts to a dimension table. Fact tables contain data that describes a specific event within a business, such as a bank transaction or product sale. See also data warehouse, dimension table, star join, star schema, and snowflake schema.
A method for managing disk storage. A file allocation table (FAT) file system is used by an operating system to keep track of the status of various segments of disk space used for file storage. See Windows NT File System.
An error message with a severity level of 19 or higher. Contact your primary support provider when these errors occur.
Standards that apply to computer systems purchased by the American government. Each Federal Information Processing Standard (FIPS) standard is defined by the National Institute of Standards and Technology (NIST). The current standard for SQL products is FIPS 127-2, which is based on the ANSI SQL-92 standard. ANSI SQL-92 is aligned with ISO/IEC SQL-92.
An operation that retrieves a row or block of rows from a cursor. Forward-only cursors support a FETCH NEXT statement only. Scrollable cursors support FETCH NEXT as well as FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH RELATIVE(n), and FETCH ABSOLUTE(n). FETCH RELATIVE(n) fetches the row n rows from the current position in the cursor. FETCH ABSOLUTE(n) fetches the nth row in the cursor. Transact-SQL batches, stored procedures, and triggers use the FETCH statement to fetch from Transact-SQL cursors. Applications use application programming interface (API) functions, such as the ODBC SQLFetch and SQLFetchScroll functions.
A single item of information contained within a row. A field is more commonly called a column in an SQL database.
The maximum number of characters needed to represent data in a bulk copy character format data file.
One or many characters marking the end of a field or row, separating one field or row in the data file from the next.
A file in which a database is stored. One database can be stored in several files. SQL Server uses three types of files: data files (which store data), log files (which store transaction logs), and backup files (which store backups of a database).
Describes how data is stored in a bulk copy data file.
The portion of an operating system that translates file-operations requests from an application into low-level, sector-oriented tasks that can be understood by the drivers that control the disk drives.
SQL Server is usually installed on disk drives formatted for the Windows NT file system (NTFS) or file allocation table (FAT) file systems. It can be installed on a compressed NTFS volume, but at a performance cost.
A named collection of one or more files that forms a single unit of allocation and administration.
An option used when creating an index to reserve free space on each page of the index. FILLFACTOR accommodates future expansion of table data and reduces the potential for page splits. FILLFACTOR is a value from 1 through 100 that specifies the percentage of the index page to be left empty.
A set of criteria applied to records to show a subset of the records or to sort the records.
To designate selected rows or columns of a table for replication as an article. See also horizontal filtering, vertical filtering, and partitioning.
See Federal Information Processing Standard.
Obsolete term for default result sets. See default result set.
Predefined roles defined at the database level existing in each database.
Predefined roles defined at the server level existing outside individual databases.
See foreign key.
A data type that holds positive or negative floating-point numbers. float, double precision, and float(n) are SQL Server float data types.
The column or combination of columns whose values match the primary key (PK) or unique key in the same or another table. A foreign key (FK) does not have to be unique. A foreign key is often in a many-to-one relationship with a primary key. Foreign key values should be copies of the primary key values; no value in the foreign key except NULL should ever exist unless the same value exists in the primary key. A foreign key may be NULL; if any part of a composite foreign key is NULL, the entire foreign key must be NULL.
A cursor that cannot be scrolled; rows can be read only in sequence from the first row to the last row.
A server running SQL Server that receives designated events.
Occurs when data modifications are made. You can reduce fragmentation and improve read-ahead performance by dropping and re-creating a clustered index.
Software used to access a database or capture input data.
A type of outer join in which all rows in all joined tables are included, whether they are matched or not.
Stores a database’s full-text index.
The portion of a full-text catalog that stores all of the full-text words and their locations for a given table.
The SQL Server component that performs the full-text querying.
As a SELECT statement, a query that searches for words, phrases, or multiple forms of a word or phrase in the character-based columns (of char, varchar, text, ntext, nchar, or nvarchar data types). The SELECT statement returns those rows meeting the search criteria.
A set of instructions that operates as a single logical unit, can be called by name, accepts input parameters, and returns information. In programming languages such as C, a function is a named subroutine of a program that encapsulates some logic. The function can be called by name, using parameters to pass data in to the function and retrieve data produced by the function. In Transact-SQL, a function is a unit of syntax consisting of a keyword and, usually, a set of parameters. There are several categories of Transact-SQL functions: string, math, system, niladic, text and image, date, aggregate, and conversion functions.
A network software product that allows computers or networks running dissimilar protocols to communicate, providing transparent access to a variety of foreign database management systems (DBMS). A gateway moves specific database connectivity and conversion processing from individual client computers to a single server computer. Communication is enabled by translating up one protocol stack and down the other. Gateways usually operate at the session layer.
A network server on which a gateway application resides.
A Windows NT group containing user accounts from the Windows NT Server domain in which it is created. Global groups cannot contain other groups or users from other domains, and cannot be created on a computer running Windows NT Workstation.
In SQL Server 7.0, a variable that can be referenced by multiple Data Transformation Services (DTS) tasks. In earlier versions of SQL Server, the term referred to the Transact-SQL system functions whose names start with two at signs (@@).
Applies a permission to a user account, which allows the account to perform an activity or work with data.
The degree of specificity of information contained in a data element. A fact table that has fine granularity contains many discrete facts, such as individual sales transactions. A table that has coarse granularity stores facts that are summaries of individual elements, such as sales totals per day.
An option of SQL Server Query Analyzer and SQL Server Enterprise Manager that shows the execution plan for a query. See also Showplan.
Administrative unit within Windows NT that contains Windows NT users or other groups.
Special user account in each database for logins without a database user account. Guests can be removed from a database.
Data that comes from a mixture of sources including one or more SQL Server databases.
An arrangement of members of a dimension into levels based on parent-child relationships, such as Year, Quarter, Month, Day or Country, Region, State or Province, City. Members in a hierarchy are arranged from more general to more specific.
A storage mode that uses a combination of multidimensional data structures and relational database tables to store multidimensional data. OLAP Services stores aggregations for a hybrid OLAP (HOLAP) partition in a multidimensional structure and facts in a relational database. See also MOLAP, ROLAP.
Data that comes from one or more SQL Server databases.
To create an article that replicates only selected rows from the base table. Subscribers receive only the subset of horizontally filtered data. You can use horizontal filtering to partition your base table horizontally. See also vertical filtering, horizontal partitioning.
To segment a single table into multiple tables based on selected rows. Each of the multiple tables has the same columns but fewer rows. See also vertical partitioning, horizontal filtering.
See HOLAP.
The name of a database object. An identifier can be from 1 through 128 characters. The first character must be a letter, underscore (_), at sign (@), or number sign (#). An identifier beginning with # denotes a temporary table. An identifier beginning with @ denotes a variable. Embedded spaces are not allowed.
A column in a table that uses the identity property for a system-generated, monotonically increasing number.
A property that enables columns to contain system-generated values that uniquely identify each row within a table. When inserting values into a table that has an identity column, SQL Server generates the next identifier automatically based on the last used identity value (incremented by adding rows) and the increment value specified during column creation.
The time, in milliseconds, that SQL Server has been idle.
See International Electrotechnical Commission.
A SQL Server system data type of variable length that can hold from 0 through 2,147,483,647 bytes of binary data. The image data type cannot be used for variables. Conversions and calculations of hexadecimal numbers stored as binary can be unreliable.
A replication model that guarantees all copies are identical to the original. It is implemented using Microsoft Distributed Transaction Coordinator (MS DTC), and it requires a high speed, well-connected local area network (LAN). It reduces database availability and is less scalable in its implementation than latent consistency.
A level of transaction consistency in which all participating sites are guaranteed to have the same data values at the same time, and the data is in a state that could have been achieved if all the work had been done at one site. See also latent transactional consistency, no guaranteed consistency.
A transaction in which each single SQL statement is considered an atomic unit.
Permission to perform an activity specific to a role. Implied permissions cannot be granted, revoked, or denied.
The set of operations that either adds new members to an existing cube or dimension, or adds new data to a partition. One of three processing options for a cube or partition. One of two processing options for a dimension. See also process, refresh data.
In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes provide quick access to data and can enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes.
An execution strategy that consists of looking up rows of a single table using several indices, followed by producing the result (by combining the partial results). Usually corresponds to an OR in the WHERE <search_conditions>. For example, WHERE R.a = 6 OR R.b = 7 with indices on columns R.a and R.b.
A database page containing index filters.
The first medium in each media family.
The process that ensures publication and destination tables contain the same schema and data before a Subscriber receives replicated transactions from a Publisher. This process is performed by the Snapshot Agent and Distribution Agent. See also synchronization.
A join in which records from two tables are combined and added to a query’s results only if the values of the joined fields meet certain specified criteria.
A member whose value is loaded directly from the data warehouse instead of being calculated from other data. See also calculated member.
The set of data provided to a multidimensional expression (MDX) value expression upon which the expression operates. For more information about set value expressions, see your OLE DB documentation.
Any table, view, or schema diagram used as an information source for a query.
A cursor that does not reflect data modification made to the underlying data by other users while the cursor is open. Insensitive cursors are typically used in Transact-SQL batches, stored procedures, and triggers using the INSENSITIVE keyword on the DECLARE CURSOR statement.
A query that copies specific columns and rows from one table to another or to the same table.
A SQL Server system data type that holds whole numbers from 2,147,483,647 through -2,147,483,648, inclusive. You cannot enter –2,147,483,648 in an integer column, but you can enter –2,147,483,647 – 1. You can store this number, or it can be the result of a calculation. Storage size is 4 bytes.
See Windows NT Authentication.
See rule.
See constraint.
An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on a more specific resource. An intent lock prevents another transaction from acquiring an exclusive lock on the resource containing that page or row.
An interactive command prompt utility provided with SQL Server that allows users to execute Transact-SQL statements or batches from a server or workstation and view the results returned.
One of two international standards bodies responsible for developing international data communications standards. The International Electrotechnical Commission (IEC) works closely with the International Organization for Standardization (ISO) to define standards of computing. They jointly published the ISO/IEC SQL-92 standard for SQL.
One of two international standards bodies responsible for developing international data communications standards. International Organization for Standardization (ISO) works closely with the International Electrotechnical Commission (IEC) to define standards of computing. They jointly published the ISO/IEC SQL-92 standard for SQL.
A publication setting that enables replication to Internet Subscribers.
A system by which threads and processes can transfer data and messages among themselves. Interprocess communication (IPC) is used to offer and receive services from other programs.
The time, in milliseconds, that SQL Server spent performing input and output operations.
See interprocess communication.
See International Organization for Standardization.
An option that allows you to customize locking for an entire SQL Server session. When you set the isolation level, you specify the default locking behavior for all SELECT statements in your SQL Server session.
An implementation of an administrative action that contains one or more steps. Replaces the SQL Server 6.5 term, task.
As a verb, to combine the contents of two or more tables and produce a result set that incorporates rows and columns from each table. Tables are typically joined using data that they have in common. As a noun, the process or result of joining tables, as in the term “inner join” to indicate a particular method of joining tables.
A comparison clause that specifies how tables are related by their join fields. The most common join condition is equivalence (an equijoin) in which the values of the join fields must be the same.
A comparison operator in a join condition that determines how the two sides of the condition are evaluated and which records are returned.
A table that has associations with two other tables and is used indirectly as an association between those two tables. Also called a linking table.
The essential core component of the server that handles several functions, such as task scheduling, disk caching, locking, and executing compiled queries.
A column or group of columns that uniquely identifies a row (PRIMARY KEY), defines the relationship between two tables (FOREIGN KEY), or is used to build an index.
A column whose contents uniquely identify every row in a table.
A lock used to lock ranges between records in a table to prevent phantom insertions or deletions into a set of records. Ensures serializable transactions.
A cursor that shows the effects of updates made to its member rows by other users while the cursor is open, but does not show the effects of inserts or deletes.
A reserved word in SQL Server that performs a specific function, such as to define, manipulate, and access database objects.
The amount of time that elapses between when a change is completed on the Publisher and when it appears in the destination database on the Subscriber.
A replication model that allows a time lag between the moment original data is altered and the replicated copies are updated. An advantage of latent consistency is that it supports local area networks (LANs), wide area networks (WANs), fast and slow communication links, and intermittently connected databases. SQL Server replication is based on a latent consistency model. See also immediate consistency.
A level of transaction consistency in which all participating sites are guaranteed to have the same data values at the same time, and the data is in a state that could have been achieved if all the work had been done at one site. See also immediate transactional consistency, no guaranteed consistency.
The bottom level of a clustered or nonclustered index. In a clustered index, the leaf level contains the actual data pages of the table. In a nonclustered index, the leaf level either points to data pages or points to the clustered index (if one exists), rather than containing the data itself.
A type of outer join in which all rows from the first-named table (the left table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.
An element of a dimension hierarchy. Levels describe the dimension order from the highest (most summarized) level to the lowest (most detailed) level of data. For example, possible levels for a Geography dimension are: Country, Region, State or Province, City. See also dimension, hierarchy.
See dimension hierarchy. See also hierarchy.
In OLAP Services, a folder that contains shared objects such as shared dimensions that can be used by multiple objects within a database.
See procedure cache.
An abstraction of an OLE DB data source that looks like another server to the local SQL Server. A linked server has an associated OLE DB provider that manages the data source.
A table that has associations with two other tables and is used indirectly as an association between those two tables. Also called junction table.
A request for an exclusive lock that is repeatedly denied because a series of overlapping shared locks keep interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. See also deadlock.
A cube created and stored with the extension .cub on a local computer using PivotTable Service. See also PivotTable Service.
A server configured as a Publisher that also acts as its own Distributor. In this configuration, the publication and distribution databases reside on the same computer. See also remote Distributor.
A Windows NT group containing user accounts and global groups from the domain group it is created in, and any trusted domain. Local groups cannot contain other local groups.
The identification (ID) a user must use to log in to a local server. A login ID can have up to 128 characters. The characters can be alphanumeric; however, the first character must be a letter (for example, CHRIS or TELLER8).
The server to which the user is logged. If remote servers are set up for the local server, users can access remote servers from their local server.
A user-defined variable that has an assigned value. A local variable is defined with a DECLARE statement, assigned an initial value with a SELECT or SET statement, and used within the statement batch or procedure in which it was declared.
The set of information that corresponds to a specific language and country. A locale indicates specific settings such as decimal separators, date and time formats, and character-sorting order.
A restriction on access to a resource in a multiuser environment. SQL Server locks users out of a specific record, field, or file automatically to maintain security or prevent concurrent data manipulation problems.
The process of converting many fine-grain locks into a fewer coarse-grain locks, reducing system overhead.
A file or set of files containing a record of a database’s transactions.
The transactional replication component that moves transactions marked for replication from the transaction log on the Publisher to the distribution database.
A name used by SQL Server to identify a file. A logical name for a file must correspond to the rules for identifiers and can have as many as 30 characters (for example, ACCOUNTING or LIBRARY).
The operators AND, OR, and NOT. Used to connect search conditions in WHERE clauses.
Establishes a connection to SQL Server.
The identification (ID) a user needs to log in to SQL Server. A login ID can have up to 128 characters and must be unique for that server. The characters can be alphanumeric; however, the first character must be a letter, the number sign (#), or underscore (_). With Windows NT Authentication, you do not need to maintain a separate login ID for SQL Server; you can use your Windows NT account.
A security mode that determines the manner in which a SQL Server validates a login request. There are two types of login security: Windows NT Authentication and Mixed Mode.
An update in which two transactions read and update the same data item.
A relationship between two tables in which rows in each table have multiple matching rows in the related table. Many-to-many relationships are maintained by using a third table called a junction table.
An e-mail application programming interface (API).
The database that controls user databases and the operation of SQL Server as a whole. It is installed automatically with SQL Server and keeps track of user accounts, remote user accounts, and remote servers that this server can interact with. It also tracks ongoing processes, configurable environment variables, system error messages, databases on SQL Server, storage space allocated to each database, tapes and disks available on the system, and active locks.
See Publisher.
The file installed with earlier versions of SQL Server used to store the master, model, and tempdb system databases and transaction logs and the pubs sample database and transaction log.
See Distributor.
See multidimensional expressions.
A quantitative, numerical column in a fact table. Measures typically represent the values that are analyzed. See also dimension.
The descriptive text describing the media set.
All media in a set written by a single device. For example, an initial medium and all continuation media, if any.
Information about the backup media.
The descriptive name for the entire backup media set.
The password for the entire media set. SQL Server does not support media passwords.
All media involved in a backup operation.
An item in a dimension representing one or more occurrences of data. A member can be either unique or nonunique. For example, 1997 and 1998 represent unique members in the year level of a time dimension, whereas January represents nonunique members in the month level because there can be more than one January in the time dimension if it contains data for more than one year.
The property that specifies the identifiers for dimension members. The MemberKeyColumn specifies a column in a table or an expression that, when evaluated, results in a set of member identifiers. For example, a MonthNumber column in a time dimension table would contain numbers from 1 through 12, corresponding to the months of the year. See also MemberNameColumn, member variable.
The property that associates names with identifiers for dimension members specified by the MemberKeyColumn property. For example, a MonthName column in a time dimension table would contain the names Jan, Feb, Mar, and so on, to correspond to the numbers from 1 through 12 in the MonthNumber column in the same table. These names are returned to the client when queries are evaluated and can be used to make the presented data more readable. See also MemberKeyColumn, member variable.
Information about the members of a dimension level in addition to that contained in the dimension. For example, the color of a product or the telephone number of a sales representative. For more information about member properties, see your OLE DB documentation.
The value used internally by OLAP Services to identify a dimension member. MemberKeyColumn specifies the member variables for a dimension. For example, a number from 1 through 12 could be the member variable that corresponds to a month of the year. See also MemberKeyColumn, MemberNameColumn.
A type of column containing long strings of text (typically more than 255 characters). This is the Access equivalent of a SQL Server text datatype.
The operation that combines two partitions into a single partition.
In merge replication, the component that applies initial snapshot jobs held in publication database tables to Subscribers. It also merges incremental data changes that have occurred since the initial snapshot was created.
A type of replication that allows sites to make autonomous changes to replicated data, and at a later time, merge changes made at all sites. Merge replication does not guarantee transactional consistency. See also snapshot replication, transactional replication.
A number that uniquely identifies an error message.
Information about the properties of data, such as the type of data in a column (numeric, text, and so on) or the length of a column. Information about the structure of data. Information that specifies the design of objects such as cubes or dimensions.
A function that performs an action by using a component object model (COM) object, as in SQL-DMO, OLE DB, and ADO.
An easy-to-use, application programming interface (API) wrapping OLE DB for use in languages, such as Visual Basic, Visual Basic for Applications, Active Server Pages, and Microsoft Internet Explorer Visual Basic Scripting.
A high-level, language-independent set of object-based data access interfaces optimized for multidimensional data applications. Visual Basic and other automation languages use ActiveX Data Objects (Multidimensional) (ADO MD) as the data access interface to multidimensional data storage. ADO MD is a part of ADO 2.0 and later.
Microsoft Management Console (MMC) is an extensible, common console framework for management applications. OLAP Services uses MMC to host its user interface, the OLAP Manager.
An extensible object modeling system that allows full programmatic access to Automation objects. Microsoft Repository includes a relational database component that can be used to store Data Transformation Services (DTS) packages.
A name for a mirrored file. It must correspond to the rules for identifiers and can have up to 30 characters.
Continuous duplication of the information on one SQL Server file to another. Mirroring can provide continuous recovery in the event of media failure.
Combines Windows NT Authentication and SQL Server Authentication. Allows users to connect to SQL Server, through either a Windows NT user account or a SQL Server login.
See Microsoft Management Console.
A storage mode that uses a proprietary multidimensional structure to store a partition’s facts and aggregations. A partition’s data is completely contained within the multidimensional structure. See also HOLAP, ROLAP.
A database installed with SQL Server that provides the template for new user databases. Each time a database is created, SQL Server makes a copy of model and then extends it to the size requested. A new database cannot be smaller than model. The model database contains the system tables required for each user database. You can modify model to add objects that you want in all newly created databases.
An arithmetic operator that provides the integer remainder after a division involving two integers.
A SQL Server system data type that stores monetary values from +922,337,203,685,477.5807 through –922,337,203,685,477.5808 with accuracy to a ten-thousandth of a monetary unit. The storage size is 8 bytes.
A syntax used for querying multidimensional data. For more information about multidimensional expressions (MDX), see your OLE DB documentation.
See MOLAP.
A database paradigm that treats data not as relational tables and columns, but as information cubes that contain dimension and summary data in cells, each addressed by a set of coordinates that specify a position in the structure’s dimensions. For example, the cell at coordinates {SALES, 1997, WASHINGTON, SOFTWARE} would contain the summary of software sales in Washington in 1997. See also cube.
An application that creates multiple threads within a single process to service multiple user requests at the same time.
The ability of a computer to support many users operating at the same time, while providing the computer system’s full range of capabilities to each user.
An interprocess communication (IPC) mechanism that SQL Server and Open Data Services use to provide communication between clients and servers. Named pipes permit access to shared network resources.
Data stored in a bulk copy data file using SQL Server native data types.
A fixed-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters.
A SELECT statement that contains one or more subqueries.
A library of functions for managing network connections and routing. Each Net-Library allows SQL Server to use a particular network protocol.
An expansion card or other physical device used to connect a computer to a local area network (LAN); also known as NIC (Network Interface Card).
SQL Server built-in functions that do not retrieve information from SQL Server. These functions are most often used to provide a default data value if one is not supplied during the inserting of data.
A level of transaction consistency in which all participating sites can have the same data values, but not necessarily the same data values that could have been achieved if all the work had been done at one site. The act of replicating the data creates the possibility that variations in data values result at one or more sites. See also latent transactional consistency, immediate transactional consistency.
A replication option used when a server subscribes to a publication. This option allows changes to replicated data to be distributed immediately to Subscribers, without delay for synchronization. An initial snapshot is not performed by SQL Server; it is the responsibility of the user setting up replication to ensure that the table schema and data are identical for the published article and the destination table. See also automatic synchronization.
Words that do not participate in a full-text query search. For example, a, and, the, and so on.
An index in which the logical order of the index does not match the physical, stored order of the rows on disk.
When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency.
Commonly accepted relational theory is governed by normalization rules that identify certain attributes that must be present (or absent) in a well-designed database.
A variable-length data type that can hold a maximum of 2³º -1 (1,073,741,823) characters or 2³¹ -1 bytes which is 2,147,483,647. ntext columns store a 16-byte pointer in the data row, and the data is stored separately.
An entry that has no explicitly assigned value. NULL is not equivalent to zero or blank. A value of NULL is not considered to be greater than, less than, or equivalent to any other value, including another value of NULL.
The capability that determines whether a column can allow null values for the data in that column.
A variable-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters.
One of the components of a database: a table, index, trigger, view, key, constraint, default, rule, user-defined data type, or stored procedure. Also called a database object.
The views and procedures that depend on a table or view, and the tables or views that are dependent on a view or procedure.
An application programming interface (API) for sharing objects among applications.
The security account with special permissions for an object, usually the creator of the object. Also called the database object owner.
Based on a table or view; controls the ability to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table or view.
See Open Database Connectivity.
A dynamic-link library (DLL) that an ODBC-enabled application, such as Microsoft Excel, can use to access an ODBC data source. Each ODBC driver is specific to a database management system (DBMS) (SQL Server, Access, and so on.)
See Open Data Services.
A set of C functions that makes an application a server. ODS Library calls respond to requests from a client in a client/server network. Also manages the communication and data between the client and the server. ODS Library follows the tabular data stream (TDS) protocol.
A text file used to store Open Data Services (ODS) error messages. The default log file for ODS is Srv.log.
See online analytical processing.
See client application.
A Microsoft Management Console (MMC) snap-in that provides a user interface for managing the OLAP server and for designing and creating multidimensional databases, cubes, and dimensions. See also Microsoft Management Console, snap-in.
The server component of OLAP Services that is specifically designed to create and maintain multidimensional data structures and provide multidimensional data in response to client queries. See also PivotTable Service.
See Object Linking and Embedding.
A programming environment (for example, Visual Basic) that can drive Automation objects.
A Component Object Model (COM) object that provides Automation-compatible interfaces.
An OLE custom component that provides programmable Automation objects.
A COM-based application programming interface (API) for accessing data. OLE DB supports accessing any format data storage (databases, spreadsheets, text files, and so on) for which an OLE DB provider is available.
The application software that calls and uses the OLE DB application programming interface (API).
A section of OLE DB 2.0 and later that addresses multidimensional structures and OLAP. See also OLE DB.
A software component that exposes an OLE DB interface. Each OLE DB provider is specific to a particular storage mechanism (for example SQL Server databases, Access databases, or Excel spreadsheets.)
See online transaction processing.
A relationship between two tables in which a single row in the first table can be related to one or more rows in the second table, but a row in the second table can be related only to one row in the first table. A typical one-to-many relationship is between the publishers table and the titles table in the pubs sample database, in which each publisher can be related to several titles, but each title can be related to only one publisher.
A relationship between two tables in which a single row in the first table can be related only to one row in the second table, and a row in the second table can be related only to one row in the first table. This type of relationship is unusual.
A technology that uses multidimensional structures to provide rapid access to data for analysis. The source data for OLAP is commonly stored in data warehouses in a relational database. See also HOLAP, MOLAP, ROLAP.
See transaction log.
A database management system representing the state of a particular business function at a specific point in time. An online transaction processing (OLTP) database is typically characterized by having large numbers of concurrent users actively adding and modifying data.
An application programming interface (API) for the server portion of a client/server system that makes data sources or data services appear to a client as a SQL Server. Open Data Services (ODS) provides a network interface that handles network protocol processes and a set of server routines that provides the application programming interface.
A database-material application programming interface (API) aligned with the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standards for a database Call Level Interface (CLI). ODBC supports access to any database for which an ODBC driver is available.
A symbol used to perform mathematical computations and/or comparisons between columns or variables.
See query optimizer.
A set of members returned in some specific order. The ORDER function in a multidimensional expression (MDX) query returns an ordered set. For more information about the ORDER function, see your OLE DB documentation.
A join that includes all rows from the joined tables regardless of whether there is a matching row between the joined tables.
A Data Transformation Services (DTS) object that defines one or more tasks to be executed in a coordinated sequence to import, export, or transform data.
The number of network errors that SQL Server detects while reading and writing packets of data over the network.
The number of input packets that SQL Server has read.
The number of output packets that SQL Server has written.
In a virtual storage system, a fixed-length block of contiguous virtual addresses copied as a unit from memory to disk and back during paging operations. SQL Server allocates database space in pages. In SQL Server, a page is 8K in size.
The process of moving half the rows in a full index page to a new page to make room for a new index entry.
A placeholder in a query or stored procedure that can be filled in when the query or stored procedure is executed. Parameters allow you to use the same query or stored procedure many times, each time with different values. Parameters can be used for any literal value, and in some databases, for column references as well.
A member in the next higher level in a hierarchy that is directly related to the current member. The parent value is usually a consolidation of the values of all of its children. For example, in a Time dimension containing the levels Quarter, Month, and Day, Qtr1 is the parent of January. See also ancestor, child, descendant, sibling.
One of the storage containers for data and aggregations of a cube. Every cube contains one or more partitions. For a cube with multiple partitions, each partition can be stored separately in a different physical location. Each partition can be based on a different data source. Partitions are not visible to users; the cube appears to be a single object.
To divide a table into logical subsets based on characteristics of the data. Partitioning is used to improve application performance or reduce the potential for conflicts in multisite update replication. See also horizontal partitioning, vertical partitioning, filtering.
A query passed uninterpreted to an external server for evaluation. The result set returned by a pass-through query can be used in the FROM clause of a query like an ordinary base table.
A SELECT statement that is passed directly to the source database without modification or delay. In PivotTable Service, the PASSTHROUGH option is part of the INSERT INTO statement. See also PivotTable Service.
A Windows NT component that provides status information about system performance.
Authorization to enforce database security. SQL Server permissions specify the Transact-SQL statements, views, and stored procedures each user is authorized to use. The ability to assign permissions is determined by each user’s status. There are two types of permissions: object permissions and statement permissions.
Controls the activities the user is allowed to perform in the SQL Server database.
Permanent, or persistent, storage of objects and data structures that involves converting complex data structures into a format suitable for file storage.
Phantom behavior occurs when a transaction attempts to select a row that does not exist and a second transaction inserts the row before the first transaction finishes. If the row is inserted, the row appears as a phantom to the first transaction, inconsistently appearing and disappearing.
The path where a file or mirrored file is located. The default is the path of the Master.dat file followed by the first eight characters of the file’s logical name. For example, if Accounting is the logical name, and the Master.dat file is located in Sql\Data, the default physical name is Sql\Data\Accounti.dat. For a mirrored file, the default is the path of the Master.mir file followed by the first eight characters of the mirror file’s logical name. For example, if Maccount is the name of the mirrored file, and the Master.mir file is located in Sql\Data, the default physical name is Sql\Data\Maccount.mir.
Reads and writes of the data performed by the database page.
To rotate rows to columns, and columns to rows, in a crosstabular data browser. Also refers to choosing dimensions from the set of available dimensions in a multidimensional data structure for display in the rows and columns of a crosstabular structure.
An in-process desktop OLAP server that communicates with the OLAP server and provides interfaces for use by client applications accessing OLAP data on the server. PivotTable Service is an OLE DB for OLAP provider. It provides online and offline data analysis functionality.
The option that sets how often the state of the service (SQL Server or SQL Server Agent) is checked.
See process.
The current location of processing in a cursor. For example, after an application fetches the first 10 rows from a cursor, it is positioned on the tenth row of the cursor. Database application programming interfaces (APIs) also have functions, such as the ODBC SQLSetPos function, that allow an application to move directly to a specific position in a cursor without performing a fetch.
An update, insert, or delete performed on a row at the current position of the cursor. The actual change is made in the rows of the base tables used to build the current row in the cursor. Transact-SQL batches, stored procedures, and triggers use the WHERE CURRENT OF clause to perform positioned updates. Applications use application programming interface (API) functions, such as the ODBC SQLSetPos function, to perform positioned updates.
To compute combinations of data while a cube is being processed. Data is precalculated in anticipation of ad hoc queries to minimize computation and disk access time when a query is submitted. For example, total quantity sold for a year can be precalculated from individual sales transactions during cube processing. See also aggregation.
The maximum total number of decimal digits that can be stored, both to the left and right of the decimal point.
The number of prefix characters preceding each noncharacter field in a bulk copy native format data file indicating the length of the field.
The number of prefix characters preceding each noncharacter field in a bcp native format data file.
Full-text query searching for those columns where the specified character-based text, word, or phrase, is the prefix. When using a phrase, each word within the phrase is considered to be a prefix. For example, a prefix search specifying the phrase “sport fish*” matches “sport fishing”, “sportsman fishing supplies”, and so on.
A dimension table in a snowflake schema in a data warehouse that is directly related to the fact table. Additional tables that complete the dimension definition are joined to the primary dimension table instead of to the fact table. See also snowflake schema, dimension table.
A server in a Windows NT domain that maintains the domain’s security database and authenticates user login passwords. It also provides a copy of the domain’s security database to backup domain controllers (BDCs), which share the user login authentication load.
The column or combination of columns that uniquely identifies one row from any other row in a table. A primary key (PK) must be nonnull and must have a unique index. A primary key is commonly used for joins with foreign keys (matching nonprimary keys) in other tables.
A structure passed to Open Data Services event handlers that contains information to make and use a connection to a remote database management system.
A dimension created for and used by a specific cube. Unlike shared dimensions, private dimensions are available only to the cube in which they are created. See also shared dimension.
A collection of stored Transact-SQL statements that can be called from one or more locations in program code.
A temporary storage location for the current, executing version of a specific stored procedure.
In a cube, the series of operations that rebuilds the cube’s structure, loads data into a multidimensional structure, calculates summaries, and saves the precalculated aggregations. As a verb, to populate a cube with data and aggregations. One of three processing options for a cube. In a dimension, the operation that loads data from a dimension table in a data warehouse into the levels defined for a dimension and rebuilds the structure of the dimension. One of two processing options for a dimension. See also incremental update, refresh data.
Collects events in a specific event category and sends the data to a SQL Server Profiler queue.
The process of extracting data from fewer than all available columns in a table or set of tables.
A set of rules or standards designed to enable computers to connect with one another and exchange information.
An OLE DB provider. An in-process dynamic link library (DLL) that provides access to a database.
Full-text query searching for those columns where the specified words are close to one another.
A group of articles available for replication as a unit. A publication can contain one or more published tables or stored procedure articles from one user database. Each user database can have one or more publications.
A list of logins that have access to a publication. The default Publication Access List on a server controls access to all publications on that server not having a custom Publication Access List.
A database source of replicated data. A database containing tables for replication.
To make data available for replication.
A server that makes data available for replication. A Publisher maintains publication databases and sends copies of all changes of the published data to the Distributor.
A sample database provided with SQL Server.
A type of subscription in which the initiation of data movement is made at the Subscriber. The Subscriber maintains a subscription by requesting, or pulling, data changes from a Publisher. The Distribution Agent is maintained at the Subscriber, thereby reducing the amount of overhead at the Distributor. See also push subscription.
A subscription in which the initiation of data movement is made at the Publisher. The Publisher maintains a subscription by sending, or pushing, the appropriate data changes to one or more Subscribers. The Distribution Agent is maintained at the Distributor. See also pull subscription.
A specific request for data retrieval, modification, or deletion.
The SQL Server component responsible for generating the optimum execution plan for a query.
A SQL Server Profiler queue provides a temporary holding place for server events to be captured.
A query that specifies a range of values as part of the search criteria, such as all rows from 10 through 100.
A value indicating the degree of matching (0 is a very low degree of matching and 1,000 is the highest degree of matching) of each value that is determined to match a full-text query.
See relational database management system.
A publication that cannot be updated or changed by the Subscriber.
See read-only replica.
A SQL Server system data type that has 7-digit precision. The approximate range of values is from 3.4E - 38 through 3.4E + 38. Storage size is 4 bytes.
A group of related fields (columns) of information treated as a unit. A record is more commonly called a row in an SQL database.
The ADO object used to contain a result set. It also exhibits cursor behavior depending on the recordset properties set by an application. ADO recordsets are mapped to OLE DB rowsets.
The interval that determines checkpoint frequency by specifying the amount of time it should take the system to recover.
See backup file.
An integrity mechanism ensuring vital data in a database, such as the unique identifier for a given piece of data, remains accurate and usable as the database changes. Referential integrity involves managing corresponding data values between tables when the foreign key of a table contains the same values as the primary key of another table.
A relationship from a column or combination of columns in a table to other columns in that same table. A reflexive relationship is used to compare rows within the same table. In queries, this is called a self-join.
The series of operations that clears data from a cube, loads the cube with new data from the data warehouse, and calculates aggregations. Refresh data is used when a cube’s underlying data in the data warehouse changes but the cube’s structure and aggregation definitions remain the same. One of three processing options for a cube. See also process, incremental update.
registry A database repository that contains information about a computer’s configuration. It is organized hierarchically and is comprised of subtrees and their keys, hives, and value entries.
A method of organizing data into two-dimensional tables made up of rows and columns. The model is based on the mathematical theory of relations, a part of set theory.
A collection of information organized in tables, each table models a class of objects of interest to the organization (for example, Customers, Parts, Suppliers). Each column in a table models an attribute of the object modeled by the table (for example, LastName, Price, Color). Each row in a table represents one entity in the class of objects modeled by the table (for example, the customer names John Smith or the part number 1346). Queries can use data from one table to find related data in other tables.
A system that organizes data into related rows and columns. SQL Server is a relational database management system (RDBMS).
See ROLAP.
A link between tables that references the primary key in one table to a foreign key in another table. The relationship line is represented in a database diagram by a solid line if referential integrity between the tables is enforced, or a dashed line if referential integrity is not enforced for INSERT and UPDATE transactions. The endpoints of a relationship line show a primary key symbol to denote a primary key to foreign key relationship or an infinity symbol to denote the foreign key side of a one-to-many relationship.
Data stored on a computer other than the computer running SQL Server and accessed by either establishing a linked server or using the ad hoc connector name.
A server configured as a Distributor, but on a separate computer from the Publisher. In this configuration, the publication and distribution databases reside on separate computers. Compare to local Distributor.
The login identification (login ID) assigned to a user for accessing remote procedures on a remote server. This login ID can be the same as the user’s local login ID. A remote login ID can have up to 128 characters. The characters can be alphanumeric, however, the first character must be a letter (for example, CHRIS or TELLER8).
The invocation of a stored procedure on a remote server from a procedure on a server.
A SQL server on the network that can be accessed through a user’s local server. SQL Server Setup can install, upgrade, or configure remote servers.
A collection of SQL statements and optional control-of-flow statements stored under a name on a remote server. Remote stored procedures can be called by clients or SQL Server.
An Open Data Services event that occurs when a client or a server calls a remote stored procedure.
A table external to the local SQL Server data source.
A copy of objects in a publication received when a server subscribes to the publication.
Duplication of table schema and data or stored procedure definitions and calls from a source database to a destination database, usually on separate servers.
A graphical tool in SQL Server Enterprise Manager used to simplify replication monitoring and troubleshooting.
A software component that produces formatted output from a database.
The storage container for the metadata managed by OLAP Services. Metadata is stored in tables in a relational database and is used to define the parameters and properties of OLAP server objects. See also metadata, Microsoft Repository.
In replication, a security status. A publication marked Restricted cannot be subscribed to by any registered Subscriber. See also unrestricted publication.
The set of rows returned from a SELECT statement. The format of the rows in the result set is defined by the column-list of the SELECT statement.
A message sent to a client with srv_senddone indicating that one set of results has been sent to the client.
The period of time a transaction is maintained in the distribution database.
Output parameters returned by an ODS Library function to the client.
A bookmark that can be consumed from a rowset for a given table and used on a different rowset of the same table to position on a corresponding row.
Removes a previously granted or denied permission from a user account in the current database. The user account may or may not have the permission through membership in groups or roles.
A type of outer join in which all rows in the second-named table (the right table, the one that appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included.
A storage mode that uses tables in a relational database to store multidimensional structures. See also HOLAP, MOLAP.
Administrative unit within SQL Server that contains SQL Server logins, Windows NT logins, groups, or other roles. See also group.
The ability to remove partially completed transactions after a database or other system failure. See also commit.
The ability to recover from disasters, such as media failure, by reading the transaction log and reapplying all readable and complete transactions.
See pivot.
A data structure that is a collection of elements (columns), each with its own name and type. A row can be accessed as a collective unit of elements, or the elements can be accessed individually. A row is equivalent to a record. See also column.
A function (SUM, AVG, MAX, MIN, or COUNT) used on a group or aggregate of data.
A function, which generates summary values that appear as additional rows in the query results (unlike aggregate function results that appear as new columns). It allows you to see detail and summary rows in one set of results. Row aggregate functions (SUM, AVG, MIN, MAX, and COUNT) are used in a SELECT statement with the COMPUTE clause.
A lock on a single row in a table.
The OLE DB object used to contain a result set. It also exhibits cursor behavior depending on the rowset properties set by an application.
A database object bound to a column or user-defined data type that specifies what data can be entered in that column. Every time a user enters or modifies a value (with an INSERT or UPDATE statement), SQL Server checks it against the most recent rule bound to the specified column, for example, for limit checking or list checking. Data entered before the creation and binding of a rule is not checked.
Artificially generated data presented instead of actual data when a cube is queried before it has been processed. Sample data enables you to view the effects of structure changes while modifying a cube.
A marker that the user includes in a user-defined transaction. When transactions are rolled back, they are rolled back only to the savepoint.
A function applied to all the rows in a table (producing a single value per function). An aggregate function in the select list with no GROUP BY clause applies to the whole table and is an example of a scalar.
An automatic backup accomplished by SQL Server Agent when defined and scheduled as a job.
A description of a database generated by the data definition language (DDL) of the database management system (DBMS). In OLAP Services, a schema is a description of multidimensional objects such as cubes, dimensions, and so forth.
See table schema script.
A collection of Transact-SQL statements used to perform an operation. Transact-SQL scripts are stored as files, usually with the .sql extension.
The ability to move around a cursor in directions other than forward-only. Users can move up and down the cursor.
In a WHERE or HAVING clause, conditions to be met for the specified action to occur on the specified data.
A unique value that identifies a user who is logged on to the security system. Security IDs (SIDs) can identify either one user or a group of users.
An entity (for example, a user, group, or computer) assigned an ID for security purposes.
The Transact-SQL statement used to request a selection, projection, join, query, and so on, from a SQL Server database.
The information (columns, expressions, and so on) to return from the specified tables in a query.
A query that returns rows into a result set from one or more tables. A Select query can contain specifications for those columns to return, the rows to select, the order to put the rows in, and how to group (summarize) information.
An extraction of data from a subset of all rows of a table or set of tables.
A join that compares rows within the same table. In database diagrams, a self-join is called a reflexive relationship.
A cursor that can reflect data modifications made to underlying data by other users while the cursor is open. Updates, deletes, and inserts made by other users are reflected in the sensitive cursor. Sensitive cursors are typically used in Transact-SQL batches, stored procedures, and triggers by omitting the INSENSITIVE keyword on the DECLARE CURSOR statement.
See identity column.
A file whose records are arranged in the order they are placed in the file.
A transaction isolation level. Ensures that a database changes from one predictable state to another. If multiple concurrent transactions can be executed serially, and the results are the same, the transactions are considered serializable.
A computer on a local area network (LAN) that controls access to resources, such as files, printers, and communication devices. See also OLAP server.
A cursor implemented on the server. The cursor itself is built at the server, and only the rows fetched by an application are sent to the client.
The name a client uses to identify a server running SQL Server. The server names on a client are managed by using the Client Network Utility. Also the name used by one SQL Server when making a remote stored procedure call to another SQL Server.
The polling interval, used to set how often the state of the service is checked.
A process that performs a specific system function and often provides an application programming interface (API) for other processes to call. It runs independently on a computer running Windows NT, unlike a program that requires a logged-on user to start or stop the program.
A function that generates a single value from a group of values, often used with Group By and Having clauses. Aggregate functions include Avg, Count, Max, Min, and Sum. Also known as an aggregate.
A text file, using the Windows .ini file format, that stores configuration information allowing SQL Server to be installed without a user having to be present to respond to prompts from the Setup program.
The severity level of an error. Valid levels are from 1 through 25. Only the system administrator can add a message with a severity level from 19 through 25.
A dimension created within a database that can be used by any cube in the database. See also private dimension.
A lock created by nonupdate (read) operations. Other users can read the data concurrently, but no transaction can acquire an exclusive lock on the data until all the shared locks have been released.
A report showing the execution plan for an SQL statement. SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL produce textual showplan output. SQL Server Query Analyzer and SQL Server Enterprise Manager can display showplan information as a graphical tree.
A member in a dimension hierarchy that is a child of the same parent as a specified member. For example, in a Time dimension with Year and Month levels, the members January 1997 and February 1997 are siblings. See also ancestor, child, descendant, parent.
A startup mode that restricts connections. Only a single user can connect, and the CHECKPOINT mechanism (which guarantees that completed transactions are regularly written from the disk cache to the database device) is not started.
A subset of the data in a cube, specified by limiting one or more dimensions by members of the dimension. For example, facts for a particular year constitute a slice of multiyear data.
A SQL Server system data type that holds dates and times of day less precisely than datetime. Storage size is 4 bytes, consisting of one small integer for the number of days after January 1, 1900, and one small integer for the number of minutes past midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
A SQL Server system data type that holds whole numbers from 32,767 through -32,768, inclusive. Storage size is 2 bytes.
A SQL Server system data type that stores monetary values from +214,748.3647 through -214,748.3648 with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes. When smallmoney values are displayed, they are rounded up two places.
A program that runs within Microsoft Management Console (MMC) and provides specific added functionality. The OLAP Manager is a snap-in. See also OLAP Manager, Microsoft Management Console.
The replication component that prepares snapshot files of published tables and stored procedures, stores the files on the Distributor, and records information about synchronization status in the distribution database.
See static cursor.
A type of replication that takes a snapshot of current data in a publication at a Publisher and replaces the entire replica at a Subscriber on a periodic basis, in contrast to publishing changes when they occur. See also transactional replication, merge replication.
An extension of a star schema such that one or more dimensions are defined by multiple tables. In a snowflake schema, only primary dimension tables are joined to the fact table. Additional dimension tables are joined to primary dimension tables. See also star schema.
A set of rules that determines how SQL Server compares, collates, and presents character data in response to database queries.
See publication database.
The relative percentage of a multidimensional structure’s cells that do not contain data. OLAP Services stores only cells that contain data. A sparse cube requires less storage than a dense cube of identical structure design. See also data explosion, density.
See structured query language.
The latest version of the standard for SQL, published in 1992. The international standard is ISO/IEC 9075:1992 Database Language SQL. The American National Standards Institute (ANSI) also published a corresponding standard (Data Language SQL X3.135-1192), so SQL-92 is sometimes referred to as ANSI SQL in the United States.
SQL Distributed Management Framework. An integrated framework of objects, services, and components used to manage SQL Server. SQL-DMF lessens the need for user-attended maintenance tasks, such as database backup and alert notification, by providing services that interact directly with SQL Server. At its most basic level, SQL-DMF provides direct access to the SQL Server engine and services from the command line through Transact-SQL. The second tier of the framework is a set of distributed management objects (DMOs) that provides an object interface to the SQL Server engine and services. The top level of the framework is a graphical administration tool, SQL Server Enterprise Manager, which provides an easy way to manage a multiserver environment. The framework also provides services for replication, scheduling, and alerting.
A component of SQL Server that includes extended stored procedures and allows SQL Server to send and receive mail messages through the built-in Windows NT mail application programming interface (MAPI). A mail message can consist of short text strings, the output from a query, or an attached file.
SQL Server Agent is used to create and manage local or multiserver jobs, alerts, and operators. Job schedules are defined in the Job Properties dialog box. SQL Server Agent communicates with SQL Server to execute the job according to the job’s schedule.
Allows users to connect to SQL Server using SQL Server logins. SQL Server performs the authentication.
The SQL Server online documentation set. SQL Server Books Online is an installation option presented by the Setup program. If you select this option, files are copied to your hard disk, and the SQL Server Books Online icon is added to the Microsoft SQL Server 7.0 program group.
A SQL Server utility provided with SQL Server for Windows NT version 4.2x that sets the default Net-Library and sets up server connection information on clients. The utility is also used to display the current DB-Library version number. This functionality is also provided in SQL Server Enterprise Manager.
A graphical application that allows for easy, enterprise-wide configuration and management of SQL Server and SQL Server objects. You can also use SQL Server Enterprise Manager to manage logins, permissions, and users; create scripts; manage devices and databases; back up databases and transaction logs; and manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types.
SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on Windows NT-based computers, and as part of the client software on Windows NT-based and Microsoft Windows 95/98-based computers. Because SQL Server Enterprise Manager is a 32-bit application, it cannot be installed on computers running 16-bit operating systems.
See forwarding server.
An account stored in SQL Server that allows users to connect to SQL Server.
Integration of Windows NT Performance Monitor with SQL Server, providing up-to-the-minute activity and performance statistics.
A SQL Server tool that captures a continuous record of server activity in real-time. SQL Server Profiler can monitor many different server events and event categories, filter these events with user-specified criteria, and output a trace to the screen, a file, or another SQL Server.
A SQL Server utility that allows you to enter Transact-SQL statements and stored procedures in a graphical user interface. SQL Server Query Analyzer also provides capability for graphically analyzing queries.
A named set of security accounts. A SQL Server role can contain Windows NT users, Windows NT groups, SQL Server users, or other SQL Server roles from the same database.
A SQL Server utility that provides a graphical way to start, pause, and stop the MSDTC, MSSQLServer, and SQLServerAgent services. SQL Server is integrated with the service control management of Windows NT, so you can start, pause, and stop SQL Server, MS DTC, and SQL Server Agent from the Services application in Control Panel or from the Server Manager application.
Security account that maps to a SQL Server login and controls the permissions on activities performed in a database.
An SQL or Transact-SQL statement, such as SELECT or DELETE, that performs some action on data.
The Open Data Services data structure that contains global information about the server application.
See SQL Server Authentication.
A join between a fact table (typically a large fact table) and at least two dimension tables. The fact table is joined with each dimension table on a dimension key. SQL Server considers special index manipulation strategies on these queries to minimize access to the fact table.
An example of a schema participating in a star join query could be a sales table, the fact table (with millions of rows), a product table, with the description of several hundred products, and a store table with several dozen store names. In this example, the product and store tables are dimension tables). A query for selecting sales data for a small set of stores and a subset of products restricted by attributes not present in the sales database is an ideal candidate for the star query optimization.
A relational database structure in which data is maintained in a single fact table at the center of the schema with additional dimension data stored in dimension tables. Each dimension table is directly related to the fact table by a key column. See also snowflake schema.
Controls the execution of Transact-SQL statements that create database objects or perform certain administrative tasks. Can be granted, revoked, or denied.
A cursor that shows the result set exactly as it was at the time the cursor was opened. Static cursors do not reflect updates, deletes, or inserts made to underlying data while the cursor is open. Sometimes called snapshot cursors.
In Embedded SQL for C, an SQL statement that is built at the time the application is compiled. It is created as a stored procedure when the application is compiled and the stored procedure is executed when the application is run.
The interval, in minutes and seconds, for refreshing SQL server statistics information. The default is 30 seconds.
A 4-byte integer that indicates the status of a result set returned to the client. The status code is sent to the client by using srv_senddone.
A Data Transformation Services (DTS) object that coordinates the flow of control and execution of tasks in a DTS package. A task that does not have an associated step object is never executed. See also data transformation and task object.
A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. Stored procedures are available for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures.
Functions that perform operations on binary data, character strings, or expressions. Built-in string functions return values commonly needed for operations on character data.
A database query and programming language originally developed by IBM for mainframe computers. It is widely used for accessing data, querying, updating, and managing relational database systems. There is now an ANSI-standard SQL definition for all computer systems.
See COM-structured storage file.
A SELECT statement nested inside another SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.
To agree to receive a publication. A destination database on a Subscriber subscribes to replicated data from a publication database on a Publisher.
A server that receives copies of published data.
The database that receives tables and data replicated from a publication database.
A unique identifier for a row within a database table. A surrogate, or candidate, key can be made up of one or more columns. By definition, every table must have at least one surrogate key (in which case it becomes the primary key for a table automatically). However, it is possible for a table to have more than one surrogate key (in which case one of them must be designated as the primary key). Any surrogate key that is not the primary key is called the alternate key.
The process of maintaining the same schema and data in a publication at a Publisher and in the replica of a publication at a Subscriber. See also initial snapshot.
See synchronization.
A replication feature that allows a Subscriber to modify replicated data and send it to the Publisher by using two-phase commit. Synchronous transactions can be performed using either transactional replication or snapshot replication.
The person responsible for the overall administration of a SQL Server. The sa login is the only login authorized to perform all functions in SQL Server. Certain critical administrative functions can be performed only by the sa login. Members of the sysadmin fixed server role operate outside the protection system (SQL Server does not check permission for these members). The members are also treated as the owner of whatever database they are using.
Collection of system tables found only in the master database.
Four databases are provided on a newly installed SQL Server installation:
In addition, you can also install the sample database, pubs, which is provided as a learning tool and is the basis for most of the examples in the SQL Server documentation. If pubs was not installed with SQL Server, you can install it using the Instpubs.sql script.
Functions that return specific information from the SQL Server installation. System functions allow access to database or server information from within an expression, such as a WHERE clause or the SELECT statement.
A SQL Server-supplied, precompiled collection of Transact-SQL statements. System stored procedures are provided as shortcuts for retrieving information from system tables or mechanisms for accomplishing database administration and other tasks that involve updating system tables. The names of all system stored procedures begin with sp_. System stored procedures are located in the master database and are owned by the system administrator, but many of them can be run from any database. If a system stored procedure is executed in a database other than master, it operates on the system tables in the database from which it is executed. You can write stored procedures (called user-defined stored procedures), which can be executed from any database.
System tables store SQL Server configuration information and definitions of all the objects, users, and permissions in SQL Server databases. Server-level configuration information is stored in system tables found only in the master database. Every database contains system tables defining the users, objects, and permissions contained by the database.
The master database and its system tables are created during SQL Server Setup. System tables in a user database are created automatically when the database is created.
SQL Server contains system stored procedures to report and manage the information in system tables. Users should use these system stored procedures rather than accessing the system tables directly. Users should not update directly any system table.
An object in a database that stores data as a collection of rows and columns.
See table schema script.
A file containing a snapshot of the data of a published table used during synchronization as the source of data inserted into the destination table. The file name extension of a data snapshot is .bcp. The file is stored in the working folder of the distribution database, a subfolder in \Mssql7\Repldata by default. See also table schema script.
Constraints that allow various forms of data integrity to be defined on one column (column-level constraint) or several columns (table-level constraints) when the table is defined or altered. Constraints support domain integrity, entity integrity, and referential integrity, as well as user-defined integrity.
A lock on a table including all data and indexes.
SQL Server starts at the beginning of the table and reads every row in the table to find the rows that meet the search criteria of the query.
A script containing the schema of a published table used during synchronization to create the destination table. The file name extension of a schema script is .sch. The file is stored in the working folder of the distribution database, a subfolder in \Mssql7\Repldata by default. See also table data file.
See filegroup.
The SQL Server internal client/server data transfer protocol. Tabular data stream (TDS) allows client and server products to communicate regardless of operating-system platform, server release, or network transport.
A backup operation to any tape device supported by Windows NT. If you are creating a tape backup file, you must first install the tape device by using Windows NT. The tape device must be physically attached to the SQL Server you are backing up.
See job.
A Data Transformation Services (DTS) object that defines pieces of work to be performed as part of the data transformation process. For example, a task can execute an SQL statement or move and transform heterogeneous data from an OLE DB source to an OLE DB destination using the DTS Data Pump. See also data transformation and step object.
See tabular data stream.
The database that provides a storage area for temporary tables, temporary stored procedures, and other temporary working storage needs. No special permissions are required to use tempdb (that is, to create temporary tables or to execute commands that may require storage space in the tempdb database). All temporary tables are stored in tempdb, no matter what database the user who creates them is using.
A procedure placed in the temporary database, tempdb, and erased at the end of the session. A temporary stored procedure is created by prefacing the procedure name (in the CREATE statement) with a number sign, for example:
CREATE PROCEDURE #author_sel AS
SELECT *
FROM authors
The first 13 characters of a temporary stored procedure name (excluding the number sign) must be unique in tempdb. Because all temporary objects belong to the tempdb database, you can create a temporary stored procedure with the same name as a procedure already in another database.
A table placed in the temporary database, tempdb, and erased at the end of the session.
A temporary table is created by prefacing the table name (in the CREATE statement) with a number sign, for example:
CREATE TABLE #authors (au_id Exchar (11))
The first 13 characters of a temporary table name (excluding the number sign) must be unique in tempdb. Because all temporary objects belong to the tempdb database, you can create a temporary table with the same name as a table already in another database.
A SQL Server system data type specifying variable-length columns that can hold 2,147,483,647 characters. The text data type cannot be used for variables or parameters in stored procedures.
A join based on a comparison of scalar values (=, > , >= , < , <= , < >, !<, !>).
A mechanism that allows one or more paths of execution through the same instance of an application. Each device requires one thread and each remote site requires two threads. SQL Server uses the native thread services of Windows NT. There are separate threads for each network, a separate thread for database checkpoints, and a pool of threads for all users.
A dimension that breaks time down into levels such as Year, Quarter, Month, and Day. In OLAP Services, a special type of dimension created from a date/time column.
A SQL Server system data type that is a monotomically increasing counter whose values are always unique within a database. A timestamp is the date and time the data was last modified.
A SQL Server system data type that holds whole numbers from 0 through 255, inclusive. Storage size is 1 byte.
A SQL Server application with a graphical user interface used to perform common tasks.
The number of errors that SQL Server detected while reading and writing.
The number of disk reads made by SQL Server.
The number of disk writes made by SQL Server.
A file used by SQL Server Profiler to record monitored events.
The standard language for communicating between applications and SQL Server. The Transact-SQL language is an enhancement to Structured Query Language (SQL), the ANSI-standard relational database language. It provides a comprehensive language for defining tables; inserting, updating, or deleting information stored in tables; and for controlling access to data in those tables. Extensions such as stored procedures make Transact-SQL a full programming language.
A server cursor defined by using the Transact-SQL DECLARE CURSOR syntax. Transact-SQL cursors are intended for use in Transact-SQL batches, stored procedures, and triggers.
A group of database operations combined into a logical unit of work that is either wholly committed or rolled back. A transaction is atomic, consistent, isolated, and durable.
A database file in which all changes to the database are recorded. It is used by SQL Server during automatic recovery.
A processing method in which transactions are executed immediately after they are received by the system.
Rollback of a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction.
A type of replication that marks selected transactions in the Publisher’s database transaction log for replication and then distributes them asynchronously to Subscribers as incremental changes, while maintaining transactional consistency. See also merge replication, snapshot replication.
See data transformation.
A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.
Authenticated connections between clients and servers. These are referred to as trusted connections. Windows NT Authentication requires network protocols that support trusted connections.
An ordered collection of members from different dimensions. For example, (Boston, [1995]) is a tuple formed by members of two dimensions: Geography and Time. A single member is a degenerated case of a tuple and can be used as an expression without the parentheses. See also axis. For more information about tuples, see your OLE DB documentation.
A process that ensures transactions that apply to more than one server are completed on all servers or on none.
See universal naming convention.
An object (a table or another view) from which a view is derived. A view can have one or more underlying objects.
A table from which a view is derived. A view can have one or more underlying tables or underlying views. Also called base table.
A link between tables that references the primary key in one table to a foreign key in another table, and which does not check the referential integrity during INSERT and UPDATE transactions. An unenforced relationship is represented in a database diagram by a dashed line.
Unicode defines a set of letters, numbers, and symbols that SQL Server recognizes in the nchar, nvarchar, and ntext data types. It is related to but separate from character sets. Unicode has more than 65,000 possible values compared to a character set’s 256, and takes twice as much space to store. Unicode includes characters for most languages.
This acts as a sort order for Unicode data. It is a set of rules that determines how SQL Server compares, collates, and presents Unicode data in response to database queries.
Data stored in a bulk copy data file using Unicode characters.
A query that combines two tables by performing the equivalent of appending one table onto the other.
Constraints that enforce entity integrity on a nonprimary key. UNIQUE constraints ensure that no duplicate values are entered and that an index is created to enhance performance.
An index in which no two rows are permitted to have the same index value, thus prohibiting duplicate index or key values. The system checks for duplicate key values when the index is created and checks each time data is added with an INSERT or UPDATE statement.
A data type containing a unique identification number stored as a 16-byte binary string used for storing a globally unique identifier (GUID).
A naming convention that consists of the following format:
\\servername\sharename\path\file_name
In replication, a security status. A publication marked Unrestricted (the default) can be subscribed to by any registered Subscriber. See also restricted publication.
An addition, deletion, or change to data.
update lock A lock placed on resources (such as row, page, table) that can be updated. Updated locks are used to prevent a common form of deadlock that occurs when multiple sessions are locking resources and are potentially updating them later.
A query that changes the values in columns of one or more rows in a table.
Any replication technology that allows you to update replicated data. See also merge replication, synchronous transaction.
A process that recalculates information about the distribution of key values in specified indexes. These statistics are used by the query optimizer to determine the most efficient way to execute a query.
Controls permissions for activities performed in a database.
A database created by a user. Each user database is created with a subset of system tables from the master database. The master database and its system tables are created when SQL Server is installed. The system tables in a user database are created automatically when a database is created.
A data type, based on a SQL Server data type, created by the user for custom data storage. Rules and defaults can be bound to user-defined data types (but not to system data types). See also base data type.
A server process created internally by Open Data Services and not as the result of a client action. The srv_define_event function creates a user-defined event.
A name known to a database and assigned to a login ID for the purpose of allowing a user to access that database. The abilities a user has within a database depend on the permissions granted to the username, and the permissions granted to any groups to which the username is a member. A username can have up to 128 characters and must be unique within the database. The characters can be alphanumeric, but the first character must be a letter or the symbols # or _ (for example, CHRIS or USER8).
A SQL Server application run from a command prompt to perform common tasks.
A multidimensional expression (MDX) that returns a value. Value expressions can operate on sets, tuples, members, levels, numbers, or strings. For example, set value expressions operate on member, tuple, and set elements to yield other sets. For more information about MDX, see your OLE DB documentation.
A SQL Server system data type that holds up to 8,000 bytes of variable-length binary data.
A SQL Server system data type that holds any combination of up to 8,000 letters, symbols, and numbers.
Defined entities that are assigned values. A local variable is defined with a DECLARE@localvariable statement and assigned an initial value within the statement batch where it is declared with either a SELECT or SET@localvariable statement. Global variables are predefined and maintained by the system.
Functions applied to all rows that have the same value in a specified column or expression by using the GROUP BY clause and, optionally, the HAVING clause (producing a value for each group per function).
To create an article that replicates only selected columns from the base table. Subscribers receive only the subset of vertically filtered data. The primary key column(s) in a table cannot be filtered out of an article in a transactional publication. You can use vertical filtering to partition your base table vertically. See also horizontal filtering, vertical partitioning.
To segment a single table into multiple tables based on selected columns. Each of the multiple tables has the same number of rows but fewer columns. See also horizontal partitioning, vertical filtering.
An alternate way of looking at data from one or more tables in the database. A view is a virtual table, usually created as a subset of columns from one or more tables.
A logical cube composed of dimensions and measures of one or more physical cubes, virtual cubes are similar to views in a relational database. Virtual cubes combine data from the underlying physical cubes and require no additional data storage.
A logical dimension based on the properties of members of a physical dimension. Members of a virtual dimension are derived from the values of one of the properties of a member of the physical dimension. For example, a virtual dimension Color could be derived from a product dimension containing member properties Color, Size, and Style; it could contain members Blue, Red, and Green, which are values for the property Color. See also dimension, member, member property.
Characters, including underscore (_), percent (%), and brackets ([ ]), used with the LIKE keyword for pattern matching.
The all-encompassing application architecture used by Microsoft Corporation.
Allows users to connect to SQL Server through a Windows NT user account.
A Windows NT application that allows you to view events, filter certain events, and to retain event logs.
An advanced file system designed for use specifically within the Windows NT operating system. It supports file system recovery, extremely large storage media, long file names, and various features for the Portable Operating System Interface for Unix (POSIX) subsystem. It also supports object-oriented applications by treating all files as objects with user-defined and system-defined attributes.
A Windows NT utility that provides a way for system administrators to monitor the performance of SQL Server. SQL Server statistics include lock performance, current size of transaction logs, user connections, and server performance. You can even set alerts to initiate a specified action when a specified threshold is reached.
Security account that maps to a Windows NT login and controls permissions on activities performed in a database.
A series of pages, displayed in a secondary window, that automate tasks. A wizard is generally used to help you perform complex or infrequent tasks.
Process of determining other forms of the word(s) specified. The Microsoft Search Service currently implements inflectional word generation. For example, if the word swim is specified, SQL Server also searches for swim, swam, and swimming.
A transaction logging method in which the log is always written prior to the data.
The facility that enables users to apply changes to data in a cube. User-inititated changes to cube data are logged to a separate partition table associated with the cube and applied automatically as cube data is viewed. To the user it appears as if the data in the cube has changed.