KeywordsMultiple Data Source Database Object Recovery Model Code Editor OnLine Analytical Processing
A directory service that supports user and group permissions in Windows networks. It can be used in conjunction with SQL Server–integrated security to control permissions to your SQL Server.
Apply and Rollback Scripts
Apply scripts apply a change of some sort to the database; rollback scripts remove those changes. Normally, for each apply script there will exist a correlating rollback script.
Usually the result of a poorly structured query, involving two or more tables. The tables are not joined correctly, resulting in every possible combination of rows being returned.
A special type of index. Only one clustered index can exist per table. The clustered index dictates how data is sorted on the hard disk.
A collection of rules that define how a SQL Server performs string matching and sorting. A collation can include rules for case sensitivity and accent sensitivity, to name but two. Collations can be applied at server, database, column, and expression levels.
A connection string is used by a computer program, usually written in a .NET language like C#, to connect to SQL Server. It controls the server and database that are connected to, along with the security information needed to connect. Both SQL Server security and integrated security models are supported.
A collection of objects—tables, relationships, stored procedures, functions—that together form a cohesive whole, together with the data held in the tables. The AddressBook database we have been developing in this book is an example of a complete database. A complete database consists of the defined objects and the data they utilize.
A DataBase Administrator. A person who is responsible for keeping an organization’s database servers and databases operational.
Data Definition Language. A term describing SQL statements that are used to define database objects, such as tables and stored procedures. CREATE TABLE is an example of a DDL statement.
A person who creates databases and database objects, which are used as part of a computer system.
Data Manipulation Language. SQL statements that are used to manipulate data, such as INSERT and UPDATE.
Dynamic Management Views (DMVs)
A set of views and functions that are built into SQL Server. These provide you with useful information about your SQL Server, such as which queries are the most expensive to run.
A database object that allows queries to execute faster when certain columns (indexed columns) are included in the query definition. Indexes can greatly speed up querying, especially on tables that contain large numbers of rows.
A help system built into SQL Server Management Studio. It uses predictive lookup to display options as you type, reducing the amount you have to type and making coding easier.
The typical type of index created in SQL Server. Supports faster querying against tables but does not dictate how data is sorted on the disk (a clustered index dictates this). You can create 999 nonclustered indexes against a single table.
A set of rules that help to enforce good database structure.
OnLine Analytical Processing. A database model, where the database is primarily used for querying purposes. Data in an OLAP database hardly changes.
OnLine Transaction Processing. A database model in which the primary purpose of the database is to support data management, including inserts, updates, and deletes. Some querying may take place in the database. OLTP databases are often transformed into OLAP databases to allow more detailed queries to be executed.
A database property that dictates how much information is logged by database operations. There are three recovery models: Simple, which doesn’t log anything; Bulk Logged, which logs normal operations but minimally logs bulk copy operations for speed purposes; and Full, which logs everything. Normally you should use the Full model, and switch to the others temporarily depending upon requirements.
SQL Server Management Studio (SSMS)
A program that allows users to manage most aspects of SQL Server via a graphical interface. SSMS also includes a code editor and can be used as a T-SQL development tool.
SQL Server Analysis Services. A component of SQL Server that provides comprehensive reporting and data mining facilities (the ability to drill down into your data). Together with SSIS and SSRS, this forms the SQL Server Business Intelligence stack of products.
SQL Server Integration Services. A hugely comprehensive import/export tool that allows SQL Server to import and transform data from multiple data sources (such as Oracle and Excel). Data can also be exported from SQL Server to multiple data sources. This is known as an ETL tool (Extract, Transform, Load).
SQL Server Reporting Services. A set of tools that allow you to create and use reports. You can create reports that can be interacted with online, as well as printed reports.
A set of tables provided as part of SQL Server, each of which gives you information about a particular aspect of SQL Server. For example, sys.tables returns information about the tables in a database.
The programming language used to interact with SQL Server.
A list of steps that define a typical way in which a computer system may be used. You may have a use case that states a user should be able to type in a name on a search screen and return a list of matching results, 10 per page.