SSIS INTEGRATION SERVICES

________________________________________
When you first open Business Intelligence Development Studio, the Start Page appears in the center of the Business Intelligence Development Studio user interface. This page displays a list of recently updated projects; help topics, Web sites, technical articles, and other resources; links to product and event information from Microsoft; and by default, a list of articles from the RSS feed of the specified news channel. After you open an object in a project, the designer for working with that object also appears in the center window.
To display a page other than the Start Page at startup, click Options on the Tools menu, expand the Environment node, and in the At Startup list, select the item to display.
To learn more about the Start Page, click within the Start Page and press F1. If the Start Page is closed, click Start Page on the View menu.
Tool Windows in Business Intelligence Development Studio
________________________________________
Business Intelligence Development Studio includes a set of windows for all phases of solution development and project management. For example, Business Intelligence Development Studio includes windows that let you manage multiple projects as a unit and view and modify the properties of objects in projects. These windows are available to all the project types in Business Intelligence Development Studio.
The following diagram shows the windows in Business Intelligence Development Studio in the default configuration.

Business Intelligence Development Studio consists of four main windows:
• Solution Explorer
• Properties Window
• Designer Window
• Toolbox

Other windows included in Business Intelligence Development Studio let you view search results, and get information about error messages and information that are output by the project debuggers or designers. Server Explorer lists database connections; Object Browser displays the symbols available to use in a project; Task List lists user-defined programming tasks; and Error List provides detailed descriptions of errors.
To learn more about these tool windows, click the View menu, select the option for the window you are interested in, and then press F1 from within the window.


Solution Explorer


You can manage all the different projects in a solution from a single window, Solution Explorer. The Solution Explorer view presents the active solution as a logical container for one or more projects, and includes all the items associated with the projects. You can open project items for modification and perform other management tasks directly from this view. Because different kinds of projects store items in different ways, the folder structure in Solution Explorer does not necessarily reflect the actual physical storage of the items listed within the solution.
In Solution Explorer, you can create empty solutions and then add new or existing projects to the solution. If you create a new project without first creating a solution, Business Intelligence Development Studio automatically creates the solution too. When the solution includes projects, the tree view includes nodes for project-specific objects. For example, the Analysis Services project includes a Dimensions node, the Integration Services project includes a Packages node, and the Report Model project includes a Reports node.
To access Solution Explorer, click Solution Explorer on the View menu.


Properties Window


The Properties window lists the properties of an object. You use this window to view and change the properties of objects, such as packages, that are open in editors and designers. You can also use the Properties window to edit and view file, project, and solution properties.
Fields in the Properties window have different types of controls embedded that open when you click them. The type of edit control depends on the particular property. These edit fields include edit boxes, dropdown lists, and links to custom dialog boxes. Properties that are shown as dimmed are read-only.
To access the Properties window, click Properties Window on the View menu.


Toolbox Window


The Toolbox window displays a variety of items for use in business intelligence projects. The tabs and items available in the Toolbox change depending on the designer or editor currently in use.
The Toolbox window always displays the General tab, and may also display tabs such as Control Flow Items, Maintenance Tasks, Data Flow Sources, or Report Items.
Some designers and editors do not use items from the Toolbox. In that case the Toolbox contains only the General tab.
To access the Toolbox, click Toolbox on the View menu.


Designer Window


The Designer window is the tool window in which you create or modify business intelligence objects. The designer provides both a code view and a design view of an object. When you open an object in a project, the object opens within a specialized designer in this window. For example, if you open a data source view in any of the business intelligence projects, the designer window opens using the Data Source View designer.
The Designer window is not available until you add a project to a solution and open an object within that project.
Menus in Business Intelligence Development Studio
________________________________________
The default menus that appear in Business Intelligence Development Studio are identical to those in Visual Studio.
When you first open Business Intelligence Development Studio, before you modify the environment, open a solution, or open any projects, Business Intelligence Development Studio includes the following menus:

• File
• Edit
• View
• Tools
• Window
• Community
• Help

When you open a specific project type in Business Intelligence Development Studio, additional menus are added to the menu bar and new options may appear on the default menus in Business Intelligence Development Studio. Moreover, depending on the object you open in the designer window, the menu bar may change to include additional menus specific to the designer for the object you are working with.


File Menu


The options on the File menu support file management. When you first open Business Intelligence Development Studio, but before you have created a new project or opened an existing project, some options are unavailable. These options become available only when you start to work in the context of a solution, or open a project within a solution.
The File menu includes the Source Control option that lets you integrate source control software with the Business Intelligence Development Studio development environment. For more information about how to use source control with Business Intelligence Development Studio, see Using Source Control Services.


Edit Menu


The options on the Edit menu support editing of text and code in files. This menu provides commands such as undo and redo; find and replace strings and symbols, locate a specified line number in code; enable and manage bookmarks. When you first open Business Intelligence Development Studio, before you have created a new project or opened an existing project, some options are unavailable. Some options become available only when you start to work in the context of a solution, or open a project in a solution.
Depending on the project type, some menu options many not be available. For example, the Undo and Redo options are not supported in Integration Services projects.


View Menu


The options on the View menu help you manage the user interface of Business Intelligence Development Studio. This menu and its submenus provide the options to open the various windows, toolbox, explorers, and browsers. You can also select which toolbars to display.
When you first open Business Intelligence Development Studio, before you have created a new project or opened an existing project, some options are unavailable. These options become available only when you start to work in the context of a solution, or open a project in a solution. For example, the View menu includes options to navigate backward and forward, but appears only when multiple windows are open.


Tools Menu


The options on the Tools menu customize behavior of the development environment. This menu, its submenus, and the dialog boxes it accesses provide options to set the following options:
• Select process and a code type for debugging. Alternatively, select the option to automatically detect the code type.
• Connect to a database. The Database Explorer lists the data connections.
• Add, remove, or import a manager for code snippets in the specified language.
• Choose the items that appear in the Toolbox window.
• If an add-in is installed, select the add-in to include in the environment.
• Work with macros. You can perform tasks such as recording and running macros.
• Select external tools to include in the environment.
• Import and export specified environment settings or reset environment settings to their defaults.
• Choose the toolbars to display in the user interface and arrange the order of commands.
• Set the options that apply to the overall development environment, solutions and projects, source control, debugging, and designers and editors.
For more information about configuring Business Intelligence Development Studio, see Customizing the Environment and Tools.


Window Menu


The options on the Window menu manage the behavior of windows, explorers, and browsers in Business Intelligence Development Studio. For example, you can specify whether windows are floating, dockable, display as tabbed documents, or are hidden.
Depending on which windows are open, the Windows menu may include different options.


Community Menu


The options on the Community menu lets you ask questions of other users and of technical support, send feedback to Microsoft, access communities, connect to the developer center, and search communities.


Help Menu


The options on the Help menu provide access to How Do I and Help topics. You can locate Help information using the index, table of content, or search features. From this menu, you can also access technical support and look for updates.
Additionally, Business Intelligence Development Studio Helps lets you save index results and maintain a list of favorite topics.
Toolbars in Business Intelligence Development Studio
________________________________________
When you first open Business Intelligence Development Studio the Toolbar includes only the MenuBar toolbar and only a few icons are available on the MenuBar toolbar.
To customize the Toolbar, click Customize on the Tools menu, and then select additional toolbars to display, or change options for the toolbar appearance.
Working with Solutions and Projects
________________________________________
In Business Intelligence Development Studio, a solution is a container that organizes the various projects that you use when you develop end-to-end business solutions. A solution lets you handle multiple projects as one unit and combine one or more related projects that contribute to a business solution. For more information, see Solutions as Containers.
When you create a new solution, Business Intelligence Development Studio adds a Solution folder to Solution Explorer and creates files that have the extensions .sln and .suo.
• The *.sln file contains information about solution configuration and lists the projects in the solution.
• The *.suo file contains information about your preferences for working with the solution.
Projects are stored in solutions. You can create a solution first and then add projects to the solution. If no solution exists, Business Intelligence Development Studio automatically creates one for you when you first create the project. A solution can contain multiple projects of different types. You can also create a blank solution and then add projects later.
Solutions in Business Intelligence Development Studio can include different types of projects. You can add projects of the following types:
• Analysis Services projects, for creating analytic objects. For more information, see SQL Server Analysis Services - Multidimensional Data.
• Integration Services projects, for creating ETL packages. For more information, see SQL Server Integration Services.
• Report Model projects, for creating report models. For more information, see SQL Server Reporting Services.
• Report Server projects, for creating reports. For more information, see SQL Server Reporting Services.
Customizing the Environment, Tools, and Windows
________________________________________
Business Intelligence Development Studio can be easily configured to suit your working style. You can configure the overall development environment and its behavior, and make changes to its tools and windows. When you save a solution, your configurations are saved to a *.suo file in the solution folder.
You can configure the Business Intelligence Development Studio environment with a collection of settings customized for SQL Server business intelligence development by selecting the Business Intelligence Settings collection. Use Import and Export Settings on the Tools menu to reset all your settings based on the Business Intelligence Settings collection, or to import only the categories of Business Intelligence Settings that you select. For more information, see "Working with Settings" in Visual Studio Help.
To configure individual options for the environment and tools, click Options on the Tools menu to open Options dialog box. To learn more about the different options in the dialog box, click a node in the left pane, and then press F1.
You can configure Business Intelligence Development Studio in the following ways:

• Configuring the Environment
• Configuring Projects and Solutions
• Configuring Source Control
• Configuring the Text Editors
• Configuring Designers
• Configuring Database Tools
• Configuring Debugging
• Configuring the HTML Designer
• Configuring Windows Forms Designer
• Configuring Windows


Configuring the Environment


You use the Environment pages in the Options dialog box to configure Business Intelligence Development Studio in the following ways:
• Windows options, such as whether to use a tabbed or multiple-document format, whether to show the status bar, and how many files should be displayed in the recent files lists.
• Security options, such as whether to allow macros to run and which add-ins to load.
• Document options such as whether to show miscellaneous files in Solution Explorer, and whether to automatically detect files that have been changed outside the environment.
• Find and replace options, such as whether to display warnings before replacing, or whether to automatically populate the Find box.
• Fonts and colors options, such as the font, font size, text color, and background color.
• Help options, such as which Help Viewer to use, and the configuration of Dynamic Help.
Additionally, you can specify miscellaneous options such as the location to use when saving files, the language used in the environment for the user interface and for online Help, the keyboard mapping scheme, startup behavior, options for the task list, and settings for the Web browser.


Configuring Projects and Solutions


You use the Projects and Solutions pages in the Options dialog box to specify the following settings:
• The location of projects, user project templates, and user item templates; the properties of solutions, and properties that apply to all project types. For example, you can specify whether to save new projects when you first create them.
• The behavior of Business Intelligence Development Studio when it builds and runs objects, and whether to use a startup project.
Configuring Source Control
You use the Source Control pages on the Options dialog box to configure the integration of source control software and Business Intelligence Development Studio in the following ways:
• Specifying the source control plug-in.
• Configuring the behavior of the plug-in environment.
• Configuring the plug-in.


Configuring the Text Editor


You use the Text Editor pages in the Options dialog box to configure text editor features that apply to all languages, in addition to any features that apply only to specific languages or language versions. For example, you can set options that apply only to the Transact-SQL version used in SQL Server.

Configuring Designers

You use the Business Intelligence Designers pages in the Options dialog box to configure the default settings for the designers used by Analysis Services and Integration Services. On these pages you can specify options such as the colors to use within the data mining viewers, the time-out for Analysis Services queries, and whether to check digital signatures when loading Integration Services packages.


Configuring Database Tools


You use the Database Tools pages in the Options dialog box to configure the behavior of various tools for managing and designing database objects:
• Configuring view and table designers, script editors, and script/query execution.
• Setting properties of data connections.
• Configuring query and view designers.
• Setting table and diagram options.
• Setting column options.


Configuring Debugging


You use the Debugging pages in the Options dialog box to configure debugging in the following ways:
• Specifying general debugging options such as whether to ask before deleting all breakpoints.
• Enabling edit and continue options such as whether to warn about stale code.
• Selecting the types of code to enable for just-in-time debugging.
Configuring the HTML Designer
You use the HTML Designer pages in the Options dialog box to configure HTML pages in the following ways:
• Specifying the view in which to start pages in source or design view and whether to enable Smart tags.
• Specifying positioning and display options of elements.
Configuring Windows Forms Designer
You use the Windows Forms Designer pages in the Options dialog box to configure Windows forms in the following ways:
• Specifying code generation, layout, Smart Tag, and Toolbox behavior.
• Customizing the user interface of data.


Configuring Windows


The Windows menu includes options for configuring the behavior of Business Intelligence Development Studio windows. You can change most windows to float or be dockable, appear as tabbed documents, or hide until you reopen them from the View menu.
From this menu, you can also create new vertical and horizontal tab groups that let you divide the designer window into multiple windows. You can also close all windows, or reset the window layout to defaults.
Using Source Control Services
________________________________________
Like Visual Studio, Business Intelligence Development Studio is integrated with source control software. If source control software is installed on the computer, you can add solutions and projects to source control, and then open the solutions and projects in Business Intelligence Development Studio from the source control application.
You configure integration with source control by using the Options dialog box. To open the Options dialog box, click Options on the Tools menu. The Source Control node in the Options dialog box includes pages for specifying the source control plug-in, configuring the source control environment, and setting plug-in options.
Getting More Information
________________________________________
The Visual Studio documentation provides detailed information about the Microsoft application development environment. Business Intelligence Development Studio uses a subset of the Visual Studio user interface, and the environments in Business Intelligence Development Studio and Visual Studio appear identical. If you install Visual Studio 2008 before you install SQL Server, the environments are one and the same.
The Visual Studio help collection provides documentation for the user interface that Business Intelligence Development Studio uses. To access the Visual Studio help topics that are relevant to the user interface shared with Business Intelligence Development Studio, you must install the MSDN Library that is included with SQL Server or configure the Business Intelligence Development Studio help options to access Help.
To configure Help to use online content
1. On the Start menu, point to Microsoft SQL Server, and then click SQL Server Business Intelligence Development Studio.
2. In Business Intelligence Development Studio, on the Tools menu, click Options.
3. In the Options dialog box, expand Environment, expand Help, and then click Online.
4. On the Online page, select either the Try online first, then local or Try local, and then online option.
When online Help is enabled, you can obtain context-sensitive help from the Visual Studio windows by pressing F1 or clicking Help. To get help for windows and dialog boxes that are available when working within an Analysis Services, Integration Services, or Reporting Services project, or to access SQL Server Books Online, you must install Books Online or MSDN. To benefit from updates to Books Online content, you should install Books Online.
If you included the .NET Framework documentation in the installation of the MSDN Library, the links between topics in the Books Online Help collection work automatically. However, to add the .NET Framework documentation to Books Online when it is viewed from the Start menu or SQL Server Management Studio, you must add the Help collection. For more information about enabling links in other Help collections, see Adding and Removing Help Collections.
You may also want to install the .NET Framework SDK. The .NET Framework SDK is available as a free download at the Microsoft Web site. For more information, see Installing the .NET Framework Documentation.
If you install Business Intelligence Development Studio, SQL Server Books Online, and MSDN Library, two copies of Books Online appear in the table of contents. One of these was installed by MSDN Library, and the other by SQL Server. To avoid seeing multiple versions of Books Online topics in search and index results, you can remove the MSDN Library copy of Books Online or do a custom installation of the MSDN Library and elect not to install Books Online. To remove Books Online from MSDN Library, open Add or Remove Programs in Control Panel, select MSDN Library for Visual Studio 2008 and then click Change. In the MSDN Library wizard, select the Modify option, click next, select the uninstall option for SQL Server and complete the wizard.
Accessibility in Business Intelligence Development Studio
________________________________________
The Business Intelligence Development Studio environment, the business intelligence projects, and the objects that you work with in these projects include accessibility features to help in navigating and viewing the user interface. For example, the user interface in Visual Studio supports shortcut keys for users who cannot use a mouse, text labels that describe functionality otherwise expressed by color coding, to help users who cannot recognize colors, and alternative text for graphical elements, to help users who use a screen reader. For more information, see Accessibility in Business Intelligence Development Studio and Using Keyboard Shortcuts in Business Intelligence Development Studio.
Analysis Services in Business Intelligence Development Studio
________________________________________
Business Intelligence Development Studio includes the Analysis Services project for developing online analytical processing (OLAP) and data mining functionality for business intelligence applications. This project type includes the templates for cubes, dimensions, mining structures, data sources, data source views, and roles, and provides the tools for working with these objects. For more information, see Analysis Services in Business Intelligence Development Studio (Analysis Services - Multidimensional Data).
Integration Services in Business Intelligence Development Studio
________________________________________
Business Intelligence Development Studio includes the Integration Services project for developing ETL solutions. This project type includes the templates for packages, data sources, and data source views, and provides the tools for working with these objects. For more information, see Integration Services in Business Intelligence Development Studio.
Reporting Services in Business Intelligence Development Studio
________________________________________
Business Intelligence Development Studio includes the Report Model and Report projects for developing reporting solutions. The Report Model project type includes the templates for report models, data sources, and data source views, and provides the tools for working with these objects. The Report project includes the templates for working with reports and shared data sources. For more information, see Reporting Services in Business Intelligence Development Studio.

Integration Services in Business Intelligence Development Studio
Business Intelligence Development Studio is the environment that you will use to develop packages for data extraction, transformation, and loading (ETL) in Integration Services. Business Intelligence Development Studio is the Visual Studio 2008 environment with enhancements that are specific to business intelligence solutions. After you create a new Integration Services project by using the New Project dialog box, Business Intelligence Development Studio opens, ready for you to start designing your package in the SSIS Designer. The windows that you see include the familiar Toolbox on the left, the design surface in the middle, and the new Solution Explorer on the right. For more information about the general features of Business Intelligence Development Studio, see Introducing Business Intelligence Development Studio.
Note:
BI Development Studio, the 32-bit development environment for Integration Services packages, is not designed to run on the Itanium 64-bit architecture and is not installed on Itanium servers.
The package development experience for Integration Services packages is much richer in Business Intelligence Development Studio than it was in the single-window designer available in Enterprise Manager that you may have used as a package developer in SQL Server 2000 Data Transformation Services (DTS). The DTS Designer displayed menus, a toolbox of connections and tasks, and a single package design surface. Although the SSIS Designer still has each of these elements, the design surface now includes multiple tabbed windows, and other new features include the Solution Explorer and the Variables and Log Events windows. The new development environment and new features combine to offer Integration Services developers a comprehensive and powerful environment for creating business intelligence solutions.
This topic briefly introduces the Business Intelligence Development Studio features that are unique to Integration Services projects. For more information about the Visual Studio development environment in general, see Introducing Business Intelligence Development Studio. For a walkthrough of how to develop an Integration Services package, see Tutorial: Creating a Simple ETL Package.
Important:
Many new and useful actions are available from the shortcut menus that you see when you right-click objects in the user interface. As you explore Business Intelligence Development Studio and the Integration Services features discussed in this topic, make sure that you right-click various items to learn about these options.
This topic discusses the following features of Business Intelligence Development Studio:

• Solution Explorer
• SSIS Designer
• SSIS Menu
• SSIS Designer Options on the Tools Menu
• Integration Services Project Properties
• Other Integration Services Windows and Features


Solution Explorer

________________________________________
Of the three prominent windows displayed when you open a new Integration Services project, only the Solution Explorer is completely new, and had no equivalent in the DTS Designer. By default, a new Integration Services project contains a single package file, named package.dtsx, but you can also add items of other types, which are grouped in the following Solution Explorer folders.
The shortcut menus available on these folders generally let you add a new item to the folder, whereas the shortcut menus available on individual items in the folder let you open the item, copy, delete, or rename the item, or exclude the item from the project. The View Code shortcut menu option available on most folder items displays the XML definition of the selected item.
• Data Sources folder. A data source represents a connection that can be shared among multiple packages in a project. You can create a connection manager in a package from an existing data source. For more information, see Using Data Sources in Packages.
• Data Source Views folder. A data source view represents a subset of the data in a data source, and can also contain named queries. Data source views can be shared by multiple packages in a project. You can designate tables, views, or named queries from a data source view as the source of data for a data flow source. For more information, see Using Data Source Views in Packages.
• SSIS Packages folder. A package represents an organized collection of connections, control flow elements, data flow elements, and other objects. The package is the unit of work in Integration Services that provides and supports extraction, transformation, and loading (ETL) functionality. A single project can contain multiple packages. The shortcut menu available on the SSIS Packages folder lets you do the following:
o Add a new or existing package to the project.
o Run the SQL Server Import and Export Wizard to jumpstart creation of a new package.
o Run the Package Migration Wizard to migrate existing DTS packages.
o Run the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages.
In the SSIS Packages folder, packages have a shortcut menu. This shortcut menu lets you designate a package as the startup object in the project, run the package, and perform other maintenance tasks.
For an introduction to packages, see Integration Services Packages.
• Miscellaneous folder. If you add files of other types to your Integration Services project, such as documents or images, these files are grouped in the Miscellaneous folder.
By default, when you create a new project, Business Intelligence Development Studio also creates a new solution as a container for the project. You can add more projects to the same solution, such as an Analysis Services project or a Reporting Services project, or a C# or Visual Basic .NET Class Library project in which you can develop custom components to use in your packages. Unlike Analysis Services and Reporting Services, Integration Services provides only a single project type in Business Intelligence Development Studio—the Integration Services project. For more information about Integration Services projects, see Integration Services Projects.


SSIS Designer

________________________________________
SSIS Designer is a graphical tool for creating packages that includes separate tabbed design surfaces for building the control flow, data flow, and event handlers in packages.
• Control Flow tab. On the Control Flow tab, you arrange and configure the tasks, including the Data Flow task, that provide functionality in packages, the containers that provide structure in packages and services to tasks, and the precedence constraints that connect containers and tasks into a control flow. The shortcut menu available on the Control Flow design surface lets you add text annotations, set breakpoints for debugging, and zoom out or zoom in on the layout of the package. The shortcut menu available on individual tasks lets you execute the task by itself, without running the whole package. For more information, see Creating Package Control Flow and Control Flow Tab.
• Data Flow tab. On the Data Flow tab, you combine into a data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components. The shortcut menu available on the Data Flow design surface also lets you add text annotations. The shortcut menu available on the paths that join data flow components lets you configure Data Viewers to watch data as it passes through the data flow. For more information, see Creating Package Data Flow and Data Flow Tab.
• Event Handlers tab. On the Event Handlers tab, you configure workflows to respond to package events. For example, you can create an event handler that sends an e-mail message when a task fails. For more information, see Creating Package Event Handlers and Event Handlers Tab.
• Package Explorer tab. The Package Explorer tab provides a convenient explorer view of the package, with the package as a container at the top of the hierarchy, and underneath it, the connections, executables, event handlers, log providers, precedence constraints, and variables that you have configured in the package. For more information, see Viewing Package Objects and Package Explorer Tab.
• Progress tab. The Progress tab displays information about package execution when you run a package in Business Intelligence Development Studio. For more information, see Progress Tab.
• Connection Managers area. Integration Services uses connection managers to encapsulate connections to a data source. These connection managers are shared within the package by control flow components, data flow components, and log providers, and are displayed in a special area of the designer at the bottom of the Control Flow, Data Flow, and Event Handlers tabs. For more information, see Creating Connection Managers and Connection Managers Area.
The designer also provides access to the dialog boxes, windows, and wizards that you use to add functionality and advanced features to packages and to troubleshoot packages. For more information, see Designing Packages in Business Intelligence Development Studio.


SSIS Menu

________________________________________
When an Integration Services project is active in Business Intelligence Development Studio, an SSIS menu is added to the main menu bar. When the SSIS Designer has the focus, this menu contains the special Integration Services options listed here; when the focus moves to another part of the Business Intelligence Development Studio user interface, the SSIS menu displays only the Work Offline option.
• Logging. Logging lets you keep a record of events raised by a package at run time. During design time, you can view the logged events in the Log Events window. For more information, see Implementing Logging in Packages and Configure SSIS Logs (Providers and Logs Tab).
• Package Configurations. Package configurations let you set properties on package objects during package development, and then update the properties at run time as required. For example, you can update the values of variables or the connection strings of connection managers. For more information, see Package Configurations, Creating Package Configurations, and Package Configurations Organizer.
• Digital Signing. Digital signing lets you use a certificate to guarantee the authenticity of a package. For more information, see Using Digital Signatures with Packages and Digital Signing.
• Variables. Variables let objects in a package communicate with each other, and can be used in expressions and in scripts. Selecting Variables on the SSIS menu displays the Variables window. For more information, see Using Variables in Packages and Variables Window.
• Work Offline. When the data sources for a package are not available, you can avoid error messages about unavailable connections by selecting Work Offline.
• Log Events. Selecting Log Events on the SSIS menu displays the Log Events window. For more information, see Implementing Logging in Packages.
• New Connection. Selecting New Connection on the SSIS menu displays the Add SSIS Connection Manager window. For more information, see Creating Connection Managers.


SSIS Designer Options on the Tools Menu

________________________________________
Under the Business Intelligence Designers node in the Options dialog box available on the Tools menu, you will find three pages of preferences for properties that are unique to the Integration Services designers.
• General page. On the general page, you can specify options for checking or requiring digital signatures and for displaying labels on precedence constraints. For more information, see General Page.
• Control Flow Auto Connect and Data Flow Auto Connect pages. The two Auto Connect pages let you specify the default behavior for connecting objects that you put on the design surfaces. For more information, see Control Flow Auto Connect Page and Data Flow Auto Connect Page.
• Confirm Delete page. On this page, you can specify whether a confirmation message appears when components are deleted.


Integration Services Project Properties

________________________________________
The Project Properties dialog box, which is available from the shortcut menu on the project node in Solution Explorer, contains three pages of default design-time property values for an Integration Services project.
• Build page. On the Build page, you can specify the output path for files generated by the build process.
• Deployment Utility page. On the Deployment Utility page, you can specify whether to create a deployment utility for the package, where to save the deployment utility, and whether to allow updates to configurations during deployment. For more information, see Creating a Deployment Utility.
• Debugging page. On the Debugging page, you can specify several options that apply when you test the package by running it in Business Intelligence Development Studio at design time. For example, you can specify the following options:
o RunInOptimizedMode (default False). Optimize the data flow execution plan. This debugging option temporarily overrides the value of the property of the same name on the Data Flow task.
o InteractiveMode (default True). Allow modal dialog boxes during debugging.
o Run64BitRuntime (default True). Use the 64-bit runtime if it is available. The value of this property is ignored on 32-bit computers. This project property applies only at design time.
o StartAction, StartApplication, and StartObjectID. Specify what to do when debugging starts. By default, StartAction = ExecutePackage, StartApplication is blank, and StartObjectID = .
o CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility.
For more information about debugging, see Debugging Packages.


Other Integration Services Windows and Features

________________________________________
Integration Services objects and tools also appear in the following Business Intelligence Development Studio windows:
• Toolbox. Integration Services populates the Toolbox with a rich variety of ready-to-use control flow tasks and data flow sources, transformations, and destinations. The shortcut menu available on the Toolbox lets you sort the items listed there, add or remove items, or reset the list. You can optionally display the Toolbox by using the predefined Ctrl+Alt+X key combination.
o Control flow items. When you are working on the Control Flow tab or the Event Handlers tab, the Toolbox displays two groups of items: Control Flow Items and Maintenance Plan Tasks.
o Data flow items. When you are working on the Data Flow tab, the Toolbox displays three groups of items: Data Flow Sources, Data Flow Transformations, and Data Flow Destinations.
• Variables window. The Variables window is specific to Integration Services projects. By default, the Variable window appears near the Toolbox, but it may not be visible until the first time that you select Variables on the SSIS menu to display the Variables window. Variables let objects in a package communicate with each other, and can be used in expressions and in scripts. You can optionally display the Variables window by mapping the View.Variables command to a keyboard shortcut of your choice on the Keyboard page of the Options dialog box. For more information, see Using Variables in Packages and Variables Window.
• Log Events window. The Log Events window is specific to Integration Services projects. By default, the Log Events window appears near the Toolbox, but it may not be visible until the first time that you select Log Events on the SSIS menu to display the Log Events window. Logging lets you keep a record of a package's run-time events. You can view the logged events easily at design time in the Log Events window. You can optionally display the Log Events window by mapping the View.LogEvents command to a keyboard shortcut of your choice on the Keyboard page of the Options dialog box. For more information, see Implementing Logging in Packages.

NEW FEATURES IN SQL SERVER 2008

1.Transparent Data Encryption

Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.

2.Extensible Key Management

SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.

3.Auditing

Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"

4.Enhanced Database Mirroring

SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.

5.Automatic Recovery of Data Pages

SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.

6.Log Stream Compression

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.

7.Resource Governor

Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.

8.Predictable Query Performance

Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

9.Data Compression

Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.

10.Hot Add CPU

Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.



11.Policy-Based Management

Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.

12.Streamlined Installation

SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.

13.Performance Data Collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.


14.Language Integrated Query (LINQ)

Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.

15.ADO.NET Data Services

The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.





16.DATE/TIME

SQL Server 2008 introduces new date and time data types:

DATE—A date-only type

TIME—A time-only type

DATETIMEOFFSET—A time-zone-aware datetime type

DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type

The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.

17.HIERARCHY ID

Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.

18.FILESTREAM Data

Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.

19.Integrated Full Text Search

Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.

20.Sparse Columns

NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.

21.Large User-Defined Types

SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.

22.Spatial Data Types

Build spatial capabilities into your applications by using the support for spatial data.

Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.

Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.





23.Backup Compression

Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.

24.Partitioned Table Parallelism

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

25.Star Join Query Optimizations

SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.

26.Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

27.Change Data Capture

With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.

28.MERGE SQL Statement

With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.

29.SQL Server Integration Services (SSIS) Pipeline Improvements

Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.

30.SQL Server Integration Services (SSIS) Persistent Lookups

The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.





31.Analysis Scale and Performance

SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.

32.Block Computations

Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.

33.Writeback

New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.


34.Enterprise Reporting Engine

Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

35.Internet Report Deployment

Customers and suppliers can effortlessly be reached by deploying reports over the Internet.

36.Manage Reporting Infrastructure

Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.

37.Report Builder Enhancements

Easily build ad-hoc and author reports with any structure through Report Designer.

38.Forms Authentication Support

Support for Forms authentication enables users to choose between Windows and Forms authentication.

39.Report Server Application Embedding

Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.

40.Microsoft Office Integration

SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.

41.Predictive Analysis

SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop.

DBCC COMMANDS

DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC FREE... CACHE - Remove items from cache.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE... - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands
DBCC Undocumented commands


1.DBCC CHECKALLOC - Check the consistency of disk space allocation.

Syntax
DBCC CHECKALLOC [ ( 'database' | database_id | 0
[ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
[WITH
{ [ ALL_ERRORMSGS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
}
]
]

Key:
REPAIR_FAST | REPAIR_REBUILD | NOINDEX - deprecated options
REPAIR_ALLOW_DATA_LOSS - Use only as a last resort
- back up the database before you run this option.To find the repair level to use run DBCC CHECKDB without a repair option. The best and easiest way to repair errors is to restore from a backup.

Examples-- Check disc space allocation for the current database.DBCC CHECKALLOC;GO
"Millions of individuals making their own decisions in the market-place will always allocate resources better than any centralized government planning process" - Ronald W. Reagan

Related

DBCC CHECKDB - Check allocation, and integrity of all objects.

Equivalent Oracle commands:

ALTER TABLE MODIFY CONSTRAINT VALIDATE
DBMS_REPAIR


2. DBCC CHECKCATALOG - Check for catalog consistency

Syntax
DBCC CHECKCATALOG
[('database_name' | database_id | 0 )]
[WITH NO_INFOMSGS]

Key:
WITH NO_INFOMSGS - Suppresses all informational messages.
"The quality of a department is inversely proportional to the number of courses it lists in its catalogue" - Hildebrand's Law

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.


3. DBCC CHECKCONSTRAINTS - Check the integrity of table constraints.
Syntax
DBCC CHECKCONSTRAINTS
[('table' | table_id | 'constraint' | constraint_id) ]
[WITH
[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
[ , ] [ NO_INFOMSGS ]
]
Key:
ALL_CONSTRAINTS - Check disabled constraints in addition to enabled constraints
has no effect when a constraint name is specified.
ALL_ERRORMSGS - Return all rows that violate constraints in the table being checked.
default = first 200 rows.
"The practice of both mathematics and art requires a blend of discipline and vision--a delicate balancing of constraint and freedom" - Ivars Peterson

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.

Equivalent Oracle commands:

ALTER TABLE MODIFY CONSTRAINT VALIDATE


4. DBCC CHECKDB - Check the allocation, and integrity of all objects in a database.

Syntax
DBCC CHECKDB [( 'database' | database_id | 0 [ , NOINDEX {REPAIR_ALLOW_DATA_LOSS |
REPAIR_FAST |
REPAIR_REBUILD} )] [WITH { [ALL_ERRORMSGS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [, ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] } ]
Key:
NOINDEX - Skip intensive checks of nonclustered indexes for user tables
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
this limits the checks that are performed.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure
DATA_PURITY - Check the database for column values that are not valid or out-of-range.
Example

-- Check the current database.DBCC CHECKDB;GO


5. DBCC CHECKFILEGROUP - Check the allocation and structural integrity of all tables and indexed views in a filegroup.
Syntax
DBCC CHECKFILEGROUP
[( 'filegroup' | filegroup_id | 0
[, NOINDEX ]
)]
[WITH
[ {ALL_ERRORMSGS | NO_INFOMSGS } ]
[, TABLOCK ] [ , ESTIMATEONLY ] [ , PHYSICAL_ONLY ]
]
Key:
filegroup_name - The name of the filegroup to be checked.
default (or if 0 is specified) = the primary filegroup.
NOINDEX - Skip intensive checks of nonclustered indexes.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limit checking to the integrity of the physical structure of the page,
record headers and the physical structure of B-trees.Examples

-- Check the primary filegroup in 'MyDatabase'
USE MyDatabase;GODBCC CHECKFILEGROUP;GO
"One of the most important ways to manifest integrity is to be loyal to those who are not present. In doing so, we build the trust of those who
are present" - Stephen Covey

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.


6. DBCC CHECKIDENT - Check and/or reseed the current identity value for a table.

Syntax
DBCC CHECKIDENT ( 'table' [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ] ) [WITH NO_INFOMSGS]

Key:
NORESEED - The current identity value should not be changed.
RESEED - Change the identity value.
new_reseed_value - The new seed value to be used for the identity column.
WITH NO_INFOMSGS - Suppresses all information messages.Example

-- Reset the current identity value
USE MyDatabase;GODBCC CHECKIDENT ('MySchema.MyTable', RESEED, 5000);GO
I finally got it all together and now I forgot where I put it!

Related commands

DBCC CHECKCONSTRAINTS - Check integrity of table constraints.

Equivalent Oracle command:

SELECT Sequence_for_my_Table.currval into CurrIdentity from dual;




7. DBCC CHECKTABLE - Check the integrity of a table or indexed view.
Syntax
DBCC CHECKTABLE ('table' | 'view' [ , { NOINDEX | index_id } |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [WITH { ALL_ERRORMSGS ] [ , EXTENDED_LOGICAL_CHECKS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] } ]
Key:
NOINDEX - Skip intensive checks of nonclustered indexes.
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
REPAIR_FAST - deprecated option
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure
EXTENDED_LOGICAL_CHECKS - If the compatibility level is 100 (SQL Server 2008) or higher,
perform logical consistency checks on indexed views, XML indexes,
and spatial indexes.
DATA_PURITY - Check the table for column values that are not valid or out-of-range.Use the DATA_PURITY option for databases upgraded from versions of SQL Server earlier than 2005.

To repair errors restore from a backup, use the REPAIR options only as a last resort.

Examples

-- Check the integrity of MyTable in 'MyDatabase'
USE MyDatabase;GODBCC CHECKTABLE ('MySchema.MyTable')GO"We're going to rebuild and rebuild stronger" - Mayor Rudolph Giuliani

Related

DBCC CHECKCONSTRAINTS - Check integrity of table constraints.



8. DBCC CLEANTABLE
Reclaim space from dropped variable-length columns in a table or indexed view.

Syntax
DBCC CLEANTABLE
( { 'database' | database_id | 0 }
, { 'table' | table_id | 'view' | view_id }
[ , batch_size]
) [WITH NO_INFOMSGS]

Key:
batch_size - The no. of rows to process per transaction.
default (or if 0 is specified) = whole table
NO_INFOMSGS - Suppress all information messages.
If 0 is specified, the current database will be used.

Example

DBCC CLEANTABLE ('MyDatabase','MySchema.MyTable', 0)WITH NO_INFOMSGS;GO"It's hard to be funny when you have to be clean" - Mae West.

Equivalent Oracle command:

ALTER TABLESPACE COALESCE



9. DBCC dllname

Unload a DLL from memory.

Syntax
DBCC dllname (FREE) [ WITH NO_INFOMSGS ]


Key:
dllname - Name of the DLL to release from memory.
WITH NO_INFOMSGS - Suppress all information messages.

When an extended stored procedure is executed, the DLL will remain loaded until the server is shut down (or DBCC dllname is used to unload it)

Example

DBCC xp_my_stored_proc (FREE)"...there is nothing that so much contributes to a survival of the trials and sufferings of the day as a sense of humor. It is like the buffers in
the solid train, like the air cushion of a modern field gun. It saves the jolt; it takes up the recoil" - William Howard Taft

Equivalent Oracle command:

Set (for all packages) with Server Parameters..
SHARED_POOL_SIZE / LARGE_POOL_SIZE / JAVA_MAX_SESSIONSPACE_SIZE



10. DBCC DROPCLEANBUFFERS
Remove all clean buffers from the buffer pool.

Syntax
DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS]


Key:
WITH NO_INFOMSGS - Suppress all information messages.

This command is useful to test queries with a cold buffer cache without shutting down and restarting the server.

To produce a 'cold' buffer cache with all dirty pages written to disk, first use CHECKPOINT.

# Why can't I ease your doubtful mind, and melt your cold, cold heart # - Hank Williams

Related commands:

CHECKPOINT
DBCC FREE... CACHE

Equivalent Oracle command:

ALTER SYSTEM FLUSH SHARED POOL



11. DBCC FREE... CACHE
DBCC FREEPROCCACHE - Remove all elements from the procedure cache.
DBCC FREESESSIONCACHE - Flush the distributed query connection cache.
DBCC FREESYSTEMCACHE - Release all unused cache entries from all caches.

Syntax
DBCC FREEPROCCACHE [WITH NO_INFOMSGS]

DBCC FREESESSIONCACHE [WITH NO_INFOMSGS]

DBCC FREESYSTEMCACHE
('ALL')
[WITH [MARK_IN_USE_FOR_REMOVAL] , [NO_INFOMSGS] ]

Key:
NO_INFOMSGS - Suppress all information messages
ALL - All supported caches
MARK_IN_USE_FOR_REMOVAL - Free up currently used entries asap (asynchronous)
MARK_IN_USE_FOR_REMOVAL will not prevent new entries being created in the cache.
Requires ALTER SERVER STATE permission on the server.

Examples

USE MyDatabase;GODBCC FREESESSIONCACHE WITH NO_INFOMSGS;GO

DBCC FREEPROCCACHE;"It's hard to be funny when you have to be clean" - Mae West.

Related commands:

DBCC DROPCLEANBUFFERS

Equivalent Oracle command:

ALTER SYSTEM FLUSH SHARED POOL


12. DBCC HELP
Help and syntax information for DBCC commands.

Syntax
DBCC HELP ('dbcc_command') [WITH NO_INFOMSGS ]

DBCC HELP (@dbcc_command_var) [WITH NO_INFOMSGS ]

DBCC HELP ('?') [WITH NO_INFOMSGS ]

Key:
WITH NO_INFOMSGS - Suppress all information messages (severity levels 0-10)
dbcc_command - The DBCC 'xyz' commandExamples

DBCC HELP ('?')GO

DBCC HELP ('USEROPTIONS')
GO

DECLARE @help_var sysname
SET @help_var = 'CHECKALLOC'
DBCC HELP (@help_var)
GO"It is our special duty, that if anyone needs our help, we should give him such help to the utmost of our power" - Cicero

Equivalent Oracle command:

HELP topic - In SQL*Plus



13. DBCC INPUTBUFFER / OUTPUTBUFFER

Display the last statement sent from a client to a database instance.

Syntax
DBCC INPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

DBCC OUTPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

Key:
session_id - The session ID
request_id - A specific request (batch) within the session.
NO_INFOMSGS - Suppress all information messages (severity 0-10)To find the request_ids for a given session id (@@spid = current session):

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Example

-- Display session # 52DBCC INPUTBUFFER (52);
DBCC OUTPUTBUFFER (52); "A lawyer who represents himself has a fool for a client." - proverb

Equivalent Oracle command:

Select from v$SESSION


14. DBCC OPENTRAN
Display information about the oldest active transaction and the oldest replicated transactions.

Syntax
DBCC OPENTRAN [( [ 'database' | database_id | 0 ] ) ] [WITH TABLERESULTS] [, [NO_INFOMSGS] ] ]

Key:
TABLERESULTS - Output in a tabular format that can be loaded into a table.
NO_INFOMSGS - Suppress all information messages (severity 0-10)Example

BEGIN TRAN...Insert/Update/DeleteGODBCC OPENTRAN;ROLLBACK TRAN;“If love does not know how to give and take without restrictions, it is not love, but a transaction that never fails to lay stress on a plus and a minus” - Emma Goldman

Equivalent Oracle command:

Select from V$TRANSACTION


15. DBCC INPUTBUFFER / OUTPUTBUFFER

Display the last statement sent from a client to a database instance.

Syntax
DBCC INPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

DBCC OUTPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

Key:
session_id - The session ID
request_id - A specific request (batch) within the session.
NO_INFOMSGS - Suppress all information messages (severity 0-10)To find the request_ids for a given session id (@@spid = current session):

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Example

-- Display session # 52DBCC INPUTBUFFER (52);
DBCC OUTPUTBUFFER (52); "A lawyer who represents himself has a fool for a client." - proverb

Equivalent Oracle command:

Select from v$SESSION



16. DBCC PROCCACHE
Display information about the procedure cache (in table format)

Syntax
DBCC PROCCACHE [WITH NO_INFOMSGS]

Key:
NO_INFOMSGS - Suppress all information messages (severity 0-10)All output figures are Totals:

proc cache size
Entries in the procedure cache.

proc cache used
Entries that are currently being used.

num proc buffs
Pages used by all entries in the procedure cache.

num proc buffs used
Pages used by all entries that are currently being used.

proc cache active / num proc buffs active
For backward compatibility only.

“Misrepresentation and deception are standard operating procedure for this administration, which - to an extent never before seen in U.S. history - systematically and brazenly distorts the facts.” - Paul Krugman (NY Times)

Equivalent Oracle command:

Select from V$SYSSTAT



17. DBCC SHOW_STATISTICS

Display the current distribution statistics for the specified target on the specified table.

Syntax
DBCC SHOW_STATISTICS ('table' | 'view' , target)
[WITH [NO_INFOMSGS] option [, option...] ]

Options:
STAT_HEADER
DENSITY_VECTOR
HISTOGRAM

Key:
table / view - The table or indexed view for which to display stats
target - The column, index or statistics for which to display stats.
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Specifying any of the 3 options will return only those statistics.

Example

DBCC SHOW_STATISTICS ('MySchema.MyTable', MyIndex);GO"A lawyer who represents himself has a fool for a client." - proverb

Related commands:

UPDATE STATISTICS
sp_createstats
sp_updatestats

Equivalent Oracle commands:

INDEX_STATS
DBA_PART_COL_STATISTICS
DBA_TAB_COL_STATISTICS



18. DBCC SHRINKDATABASE
Shrink the size of the database data and log files.

Syntax
DBCC SHRINKDATABASE ('database' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE ('database_id' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE (0 option [,option]) [WITH NO_INFOMSGS]

Options:
target_percent
NOTRUNCATE
TRUNCATEONLY

Key:
0 - Shrink the current database

target_percent - Percentage of free space to remain in the database file

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Only one of the truncate options can be specified - they do not apply to log files.

Example

DBCC SHRINKDATABASE (MyDatabase);“Men shrink less from offending one who inspires love than one who inspires fear” - Niccolo Machiavelli

Related commands:

ALTER DATABASE
DBCC SHRINKFILE
FILE_ID
sys.database_files

Equivalent Oracle command:

ALTER DATABASE... DATAFILE...


19. DBCC SHRINKFILE
Shrink the size of the current database data / log file or empty a file by moving the data.

Syntax
DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS]

DBCC SHRINKFILE ( file , target_size
[, {NOTRUNCATE | TRUNCATEONLY }] ) [WITH NO_INFOMSGS ]

Key:
file - Logical file name or file_id

EMPTYFILE - Migrate data to other files in the same filegroup.
The file can be removed with ALTER DATABASE.

target_size - The size for the file in megabytes.
default = that specified when the file was created, or
the last size used with ALTER DATABASE.(int)

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
You can shrink a transaction log file while the system is in use (DML commands are also being executed), however this will only affect the inactive portion of the transaction log file.
Discover the file_ID for each file with the SQL: SELECT file_id, name FROM sys.database_files;
After using TRUNCATE_ONLY you must perform a full backup

Examples

Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);Shrink a Log file to 8 GiB (8192 MiB):

USE MyDatabase;
GO
DBCC SHRINKFILE(MyDatabase_Log, 8192)BACKUP LOG MyDatabase WITH TRUNCATE_ONLYDBCC SHRINKFILE(MyDatabase_Log, 8192)
Afterwords, perform a full backup of the database.
To make the file as small as possible you can specify 1 for 1 Mb, or just leave out the target_size completely, be aware that doing this will slow down the system a little as the system will just have to grow the log file again as soon as another transaction is started.

In SQL Server 2008 the procedure is slightly different, the database must first be set to Simple recovery mode, then shrink the file, then restore FULL recovery mode:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(MyDatabase_log)
go
EXEC sp_helpdb MyDatabase
go
ALTER DATABASE MyDatabase SET RECOVERY FULL
go “Men shrink less from offending one who inspires love than one who inspires fear” - Niccolo Machiavelli

Related:

Why you want to be restrictive with shrink of database files - karaszi.com
ALTER DATABASE MyDatabase SET RECOVERY FULL;
DBCC SHRINKDATABASE
FILE_ID
sys.database_files
Equivalent Oracle command: ALTER DATABASE Datafile '/oradata/ss64.dbf' resize 64M;



20. DBCC SQLPERF
Display transaction-log space statistics. Reset wait and latch statistics.

Syntax
DBCC SQLPERF ( Option ) [WITH NO_INFOMSGS ]

Options:
LOGSPACE 'sys.dm_os_latch_stats' , CLEAR 'sys.dm_os_wait_stats' , CLEAR

Key:
LOGSPACE - Monitor log space, indicates when to back up or truncate the tx log.
dm_os_latch_stats - Reset the latch statistics.
dm_os_wait_stats - Reset the wait statistics.
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Example

DBCC SQLPERF(LOGSPACE)GO“If you happen to be one of the fretful minority... never force an idea; you'll abort it if you do. Be patient and you'll give birth to it when the time is ripe. Learn to wait” - Robert A. Heinlein

Related commands:

sp_spaceused
sys.dm_os_latch_stats
sys.dm_os_wait_stats

Equivalent Oracle commands:

Oracle deals with transaction logs in a *totally* different way
See V$LOG and V$LOGFILE


21. DBCC TRACEON - Enable trace flags.
DBCC TRACEOFF - Disable trace flags.
DBCC TRACESTATUS - Display the status of trace flags.

Syntax
DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACESTATUS ( [ [trace# [,...n ] ] [,] [-1] ] ) [WITH NO_INFOMSGS]

Key:

trace# - Number of the trace flag(s)
-1 - Display the status of trace flags that are enabled globally.
NO_INFOMSGS - Suppress all information messages.
By default all trace flags that are enabled for the session are displayed.

Examples

-- Turn flag on
DBCC TRACEON (3205)GO
-- Turn flag on globally
DBCC TRACEON (2528, -1)GO
-- Turn flag off
DBCC TRACEOFF (3205);GO
-- Show flag status
DBCC TRACESTATUS (2528, 3205)GO“Every branch of human knowledge, if traced up to its source and final principles vanishes into a mystery” - Arthur Machen

Related commands:

Trace Flags

Equivalent Oracle command:

Server Parameters Configuration settings



22. DBCC UPDATEUSAGE
Report and correct page and row count inaccuracies in catalog views, use this after upgrading a database to SQL Server 2005.

Syntax
DBCC UPDATEUSAGE ( database
[, {table | view} [,{index} ] ] )
[WITH [ NO_INFOMSGS ] [ , ] [COUNT_ROWS ] ]

Key:
database - 'database_name' or database_id or 0 (current db)
NO_INFOMSGS - Suppress all information messages.
COUNT_ROWS - Update the row count column.
The table/view and indexes may be specified by 'name' (in single quotes) or ID.

If 0 is specified, the current database will be used.

Example

DBCC UPDATEUSAGE ('MyDatabase','MySchema.MyTable');GO“No matter how cynical you get, it is impossible to keep up” - Lily Tomlin

Related commands:

sp_spaceused
sys.sysindexes
UPDATE STATISTICS

Equivalent Oracle command:

DBA_TABLES - Rows in table
DBA_TAB_COL_STATISTICS - Other column stats



23. DBCC USEROPTIONS
Return the SET options currently active (set for the current connection.)

Syntax
DBCC USEROPTIONS [WITH NO_INFOMSGS]

Options:

NO_INFOMSGS - Suppress all information messages.
Example

DBCC USEROPTIONS“Eventually, all things merge into one, and a river runs through it. The river was cut by the world's great flood and runs over rocks from the basement of time” - Norman Maclean, A River Runs Through It

Related commands:

SET
SET TRANSACTION ISOLATION LEVEL

Equivalent Oracle command:

DBA_TABLES - Rows in table
DBA_TAB_COL_STATISTICS - Other column stats



24. DBCC - Deprecated commands
DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC dbreindex - - use ALTER INDEX instead

DBCC DBREPAIR - - use DROP DATABASE instead

DBCC INDEXDEFRAG

DBCC PINTABLE

DBCC SHOWCONTIG

DBCC UNPINTABLE



25. DBCC - Undocumented commands
These commands may affect system performance and/or force table-level locks.
There is no guarantee these commands will remain available in any future release of SQL server.

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ] [, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches ('database_name'|database_id, 'table_name'|table_id, 'index_name'|index_id)

DBCC collectstats (on | off)

DBCC config

DBCC cursorstats ([spid [,'clear']])

DBCC dbinfo [('dbname')]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair ('dbname', DROPDB [, NOINIT])

DBCC dbtable [({'dbname' | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]

DBCC detachdb [( 'dbname' )]

DBCC dropextendedproc (function_name)

DBCC dropuserobject ('object_name')

DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} | {'CLEAR', exception_number})

DBCC errorlog

DBCC extentinfo [({'database_name'| dbid | 0} [,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])]

DBCC fileheader [( {'dbname' | dbid} [, fileid])

DBCC fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'} , filenum, pagenum [, objectid, indid])

DBCC flush ('data' | 'log', dbid)

DBCC flushprocindb (database)

DBCC freeze_io (db)

DBCC getvalue (name)

DBCC icecapquery ('dbname', stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)])
Use 'dbcc icecapquery (printlist)' to see list of SP's to profile.
Use 'dbcc icecapquery (icecapall)' to profile all SP's.

DBCC incrementinstance (objectname, countername, instancename, value)

DBCC ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { 'dbname' | dbid | 0 | -1 } , { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, 'owners'] [, 'stackdumps'])

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] | [{'STALLREPORTTHESHOLD', stallthreshold}])

DBCC lockobjectschema ('object_name')

DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y'] | ['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs', {'lop'|op}...]|['output',x,['filename','x']]...]]])

DBCC loginfo [({'database_name' | dbid})]

DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents ('sink' [, 'filter-expression'])

DBCC newalloc - please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2} , targetfile, targetpg, order={1|0})

DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid} [, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, 'dbname' }, fileid, pageid , formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) - Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC sqlmgrstats

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {'query' | 'release'}[,('\\.\tape')]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])

DBCC thaw_io (db)

DBCC upgradedb (db)

DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC wakeup (spid)

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)



26. DBCC PAGE: Use this command to look at contents of a data page stored in SQL Server.

Example:

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

where:

Dbid or dbname: Enter either the dbid or the name of the database in question.

Pagenum: Enter the page number of the SQL Server page that is to be examined.

Print option: (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.

Cache: (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.

Logical: (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.



27. DBCC SHOWCONTIG: Used to show how fragmented data and indexes are in a specified table. If data pages storing data or index information becomes fragmented, it takes more disk I/O to find and move the data to the SQL Server cache buffer, hurting performance. This command tells you how fragmented these data pages are. If you find that fragmentation is a problem, you can reindex the tables to eliminate the fragmentation. Note: this fragmentation is fragmentation of data pages within the SQL Server MDB file, not of the physical file itself.

Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script so you don't have to manually look up the table name ID number and the index ID number.

Example:

DBCC SHOWCONTIG (Table_id, IndexID)

Or:

--Script to identify table fragmentation

--Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table

--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database.

you can search this link also ss64.com/sql/

LOCKS IN SQL SERVER 2005

Lock modes
All examples are run under the default READ COMMITED isolation level. Taken locks differ between isolation levels, however these examples are just to demonstrate the lock mode with an example. Here's a little explanation of the three columns from sys.dm_tran_locks used in the examples:

resource_type This tells us what resource in the database the locks are being taken on. It can be one of these values: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
request_mode This tells us the mode of our lock.
resource_description This shows a brief description of the resource. Usually holds the id of the page, object, file, row, etc. It isn't populated for every type of lock

The filter on resource_type <> 'DATABASE' just means that we don't want to see general shared locks taken on databases. These are always present. All shown outputs are from the sys.dm_tran_locks dynamic management view. In some examples it is truncated to display only locks relevant for the example. For full output you can run these yourself.

Shared locks (S)

Shared locks are held on data being read under the pessimistic concurrency model. While a shared lock is being held other transactions can read but can't modify locked data. After the locked data has been read the shared lock is released, unless the transaction is being run with the locking hint (READCOMMITTED, READCOMMITTEDLOCK) or under the isolation level equal or more restrictive than Repeatable Read. In the example you can't see the shared locks because they're taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks.

BEGIN TRAN

USE AdventureWorks

SELECT * FROM Person.Address WITH (HOLDLOCK)
WHERE AddressId = 2

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Update locks (U)

Update locks are a mix of shared and exclusive locks. When a DML statement is executed SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can't modify the underlying data. It has to be converted to an exclusive lock before the modification takes place. You can also force an update lock with the UPDLOCK hint:

BEGIN TRAN

USE AdventureWorks

SELECT * FROM Person.Address WITH (UPDLOCK)
WHERE AddressId < 2

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Exclusive locks (X)

Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level. Because DML statements first need to read the data they want to modify you'll always find Exclusive locks accompanied by shared locks on that same data.

BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressId = 5

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Intent locks (I)

Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. Thus the name. Their purpose is to assure proper data modification by preventing other transactions to acquire a lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on the page or the row level an intent lock is set on the table. This prevents other transactions from putting exclusive locks on the table that would try to cancel the row/page lock. In the example we can see the intent exclusive locks being placed on the page and the table where the key is to protect the data from being locked by other transactions.

BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Schema locks (Sch)

There are two types of schema locks:

Schema stability lock (Sch-S): Used while generating execution plans. These locks don't block access to the object data.
Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed.
In the example we can see the Sch-S and Sch-M locks being taken on the system tables and the TestTable plus a lot of other locks on the system tables.

BEGIN TRAN

USE AdventureWorks

CREATE TABLE TestTable (TestColumn INT)

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Bulk Update locks (BU)

Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import. This allows for multiple fast concurrent inserts by disallowing data reading to other transactions.

Conversion locks

Conversion locks are locks resulting from converting one type of lock to another. There are 3 types of conversion locks:

Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock

Shared with Intent Update (SIU). A transaction that holds a Shared lock also has some pages/rows locked with an Update lock.

Update with Intent Exclusive (UIX). A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock.
In the example you can see the UIX conversion lock being taken on the page:

BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Key - Range locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction. In the example we can see that there are two types of key-range locks taken:

RangeX-X - exclusive lock on the interval between the keys and exclusive lock on the last key in the range
RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressLine1 LIKE '987 %'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

Lock Granularity

Lock granularity consists of TABLE, PAGE and ROW locks. If you have a clustered index on the table then instead of a ROW lock you have a KEY lock. Locking on the lower level increases concurrency, but if a lot of locks are taken consumes more memory and vice versa for the higher levels. So granularity simply means the level at which the SQL Server locks data. Also note that the more restricted isolation level we choose, the higher the locking level to keep data in correct state. You can override the locking level by using ROWLOCK, PAGLOCK or TABLOCK hints but the use of these hints is discouraged since SQL Server know what are the appropriate locks to take for each scenario. If you must use them you should be aware of the concurrency and data consistency issues you might cause.