Database management system oveview - Xuanhien's Weblog



95250-5987100TR??NG ??I H?C C?NG NGHI?P TP. H? CH? MINH--------------Ths. T? Th? Xu?n Hi?nB?I GI?NG L? THUY?TDATABASE MANAGGEMENT SYSTEM(H? Qu?n Tr? C? S? D? Li?u)Trình ??: ??I H?CNgành: K? THU?T PH?N M?MM?n: H? QU?N TR? C? S? D? LI?UTh?i l??ng: 30 ti?tTP. H? CH? MINH - 2016L?U H?NH N?I B?M?C L?C TOC \o "1-3" \h \z \u Chapter 1.Database management system oveview PAGEREF _Toc458087383 \h 71.1.Data vs. Information PAGEREF _Toc458087384 \h 71.1.1.Data PAGEREF _Toc458087385 \h 71.1.rmation PAGEREF _Toc458087386 \h 71.2.What is a Database? PAGEREF _Toc458087387 \h 71.3.Database management system PAGEREF _Toc458087388 \h 71.4.Characteristics of the Database PAGEREF _Toc458087389 \h 71.5.Characteristics of the Database management system PAGEREF _Toc458087390 \h 71.6.DBMS-Architecture PAGEREF _Toc458087391 \h 81.7.Services of DBMS PAGEREF _Toc458087392 \h 91.8.Types of database management systems PAGEREF _Toc458087393 \h 101.9.SQL Server 2008 overview PAGEREF _Toc458087394 \h 101.9.1.SQL Server Components and Features PAGEREF _Toc458087395 \h 111.9.2.SQL Server 2008 R2 Editions. PAGEREF _Toc458087396 \h 13Chapter 2.Creating and Managing Databases PAGEREF _Toc458087397 \h 142.1.Database in SQL Server PAGEREF _Toc458087398 \h 142.2.Data Storage in SQL Server PAGEREF _Toc458087399 \h 142.3.Database Files PAGEREF _Toc458087400 \h 142.3.1.Primary Files PAGEREF _Toc458087401 \h 142.3.2.Secondary Files PAGEREF _Toc458087402 \h 142.3.3.Using Filegroups PAGEREF _Toc458087403 \h 152.3.4.Using Partitions PAGEREF _Toc458087404 \h 152.3.5.Transaction Log Files PAGEREF _Toc458087405 \h 152.4.Creating Databases PAGEREF _Toc458087406 \h 152.4.1.Using SSMS to Create a Database PAGEREF _Toc458087407 \h 152.4.2.Using T-SQL to Create Databases PAGEREF _Toc458087408 \h 162.5.Setting database options PAGEREF _Toc458087409 \h 172.5.1.The Database Options PAGEREF _Toc458087410 \h 172.6.Managing database PAGEREF _Toc458087411 \h 182.6.1.Expanding Databases PAGEREF _Toc458087412 \h 182.6.2.Moving Databases PAGEREF _Toc458087413 \h 182.6.3.Restoring a Database Backup to a New Location PAGEREF _Toc458087414 \h 182.6.4.Detaching and Attaching Databases PAGEREF _Toc458087415 \h 192.7.Creating and managing tables PAGEREF _Toc458087416 \h 202.7.1.Create table PAGEREF _Toc458087417 \h 202.7.2.System data types PAGEREF _Toc458087418 \h 212.7.3.To convert one data type to another PAGEREF _Toc458087419 \h 222.7.4.User-Defined Data Types PAGEREF _Toc458087420 \h 222.8.Implementing data integrting PAGEREF _Toc458087421 \h 242.8.1.Types of data integrity PAGEREF _Toc458087422 \h 242.8.2.Enforcing data integrating PAGEREF _Toc458087423 \h 252.8.3.Using constraints PAGEREF _Toc458087424 \h 252.8.4.Rules PAGEREF _Toc458087425 \h 302.8.5.Defaults PAGEREF _Toc458087426 \h 302.9.Index PAGEREF _Toc458087427 \h 312.9.1.Types of indexes PAGEREF _Toc458087428 \h 312.9.2.Creating Indexes PAGEREF _Toc458087429 \h 332.9.3.Managing Indexes PAGEREF _Toc458087430 \h 352.9.4.Dropping indexes PAGEREF _Toc458087431 \h 36Chapter 3.Retrieving and manipulating data PAGEREF _Toc458087432 \h 373.1.Data retrieval PAGEREF _Toc458087433 \h 373.1.1.General SELECT statement PAGEREF _Toc458087434 \h 373.1.2.MERGE statement PAGEREF _Toc458087435 \h 393.1.3.Aggregating data PAGEREF _Toc458087436 \h 403.1.4.Joins PAGEREF _Toc458087437 \h 423.1.5.Subqueries PAGEREF _Toc458087438 \h 453.2.Modifying Data PAGEREF _Toc458087439 \h 503.2.1.Inserting Data PAGEREF _Toc458087440 \h 503.2.2.Deleting Data PAGEREF _Toc458087441 \h 523.2.3.Updating Data PAGEREF _Toc458087442 \h 523.3.View PAGEREF _Toc458087443 \h 543.3.1.Definition of Views PAGEREF _Toc458087444 \h 543.3.2.Using Views PAGEREF _Toc458087445 \h 543.3.3.Creating Views Using T-SQL PAGEREF _Toc458087446 \h 543.3.4.Creating Views Using the View Designer PAGEREF _Toc458087447 \h 563.3.5.Managing views PAGEREF _Toc458087448 \h 56Chapter 4.T-SQL Programming PAGEREF _Toc458087449 \h 584.1.Variables PAGEREF _Toc458087450 \h 584.1.1.Declaring and Initializing a Variable PAGEREF _Toc458087451 \h 584.1.2.Using Variables in WHERE and HAVING Clauses PAGEREF _Toc458087452 \h 594.2.Control structures PAGEREF _Toc458087453 \h 604.2.1.The IF… ELSE Construct PAGEREF _Toc458087454 \h 604.2.2.WHILE PAGEREF _Toc458087455 \h 634.2.3.CASE Statement PAGEREF _Toc458087456 \h 644.3.Store Procedure PAGEREF _Toc458087457 \h 654.3.1.Advantages of Stored Procedures PAGEREF _Toc458087458 \h 654.3.2.NOCOUNT ON/NOCOUNT OFF PAGEREF _Toc458087459 \h 654.3.3.Creating Stored Procedures PAGEREF _Toc458087460 \h 664.4.User-Defined Function PAGEREF _Toc458087461 \h 684.4.1.Types of User-Defined Functions PAGEREF _Toc458087462 \h 684.4.2.Modify User-defined Functions PAGEREF _Toc458087463 \h 724.4.3.Delete User-defined Functions PAGEREF _Toc458087464 \h 744.5.Cursor PAGEREF _Toc458087465 \h 744.5.1.DECLARE CURSOR PAGEREF _Toc458087466 \h 754.5.2.OPEN CURSOR PAGEREF _Toc458087467 \h 754.5.3.FETCH (Transact-SQL) PAGEREF _Toc458087468 \h 754.5.4.CLOSE CURSOR PAGEREF _Toc458087469 \h 754.5.5.DEALLOCATE PAGEREF _Toc458087470 \h 754.6.Trigger PAGEREF _Toc458087471 \h 774.6.1.INSERTED and DELETED: PAGEREF _Toc458087472 \h 784.6.2.Types of DML Triggers PAGEREF _Toc458087473 \h 80Chapter 5.Database Backup and Restore PAGEREF _Toc458087474 \h 815.1.Why you should backup? PAGEREF _Toc458087475 \h 815.2.Types of backups PAGEREF _Toc458087476 \h 815.2.1.Full database backups PAGEREF _Toc458087477 \h 815.2.2.Differential Database Backups PAGEREF _Toc458087478 \h 835.2.3.Transaction Log Backups PAGEREF _Toc458087479 \h 845.3.Restoring Databases and Transaction Logs PAGEREF _Toc458087480 \h 855.3.1.Restore a full database backup PAGEREF _Toc458087481 \h 865.3.2.Restore a Differential Database Backup PAGEREF _Toc458087482 \h 875.3.3.Restore a Transaction Log Backup PAGEREF _Toc458087483 \h 88Chapter 6.Database Security PAGEREF _Toc458087484 \h 906.1.Introduction to Database Security PAGEREF _Toc458087485 \h 906.2.Security Mechanisms PAGEREF _Toc458087486 \h 916.2.1.Logins PAGEREF _Toc458087487 \h 916.2.2.Users PAGEREF _Toc458087488 \h 916.2.3.Roles PAGEREF _Toc458087489 \h 916.2.4.Groups PAGEREF _Toc458087490 \h 916.3.Security Levels PAGEREF _Toc458087491 \h 926.3.1.Authentication Methods PAGEREF _Toc458087492 \h 926.3.2.Windows Authentication Mode PAGEREF _Toc458087493 \h 926.3.3.Mixed Authentication Mode PAGEREF _Toc458087494 \h 936.3.4.Setting the Authentication Mode PAGEREF _Toc458087495 \h 936.4.Managing Principals PAGEREF _Toc458087496 \h 936.4.1.Managing SQL Server Logins PAGEREF _Toc458087497 \h 946.4.2.Managing SQL Server Users PAGEREF _Toc458087498 \h 956.4.3.Managing Database Roles PAGEREF _Toc458087499 \h 966.5.Managing Securables PAGEREF _Toc458087500 \h 996.6.Managing Permissions PAGEREF _Toc458087501 \h 996.6.1.GRANT PAGEREF _Toc458087502 \h 1006.6.2.REVOKE PAGEREF _Toc458087503 \h 1006.6.3.DENY PAGEREF _Toc458087504 \h 101Chapter 7.Transaction Management and the Transaction Log PAGEREF _Toc458087505 \h 1027.1.Defining Transactions PAGEREF _Toc458087506 \h 1027.2.Managing Transactions PAGEREF _Toc458087507 \h 1027.2.1.Autocommit Transactions PAGEREF _Toc458087508 \h 1037.2.2.Explicit Transactions PAGEREF _Toc458087509 \h 1037.2.3.Implicit Transactions PAGEREF _Toc458087510 \h 1077.3.Locking PAGEREF _Toc458087511 \h 1087.3.1.What is Locks PAGEREF _Toc458087512 \h 1087.3.2.Types of locks PAGEREF _Toc458087513 \h 1087.3.3.Lock Compatibility PAGEREF _Toc458087514 \h 1097.3.4.Transaction Isolation Levels in SQL Server PAGEREF _Toc458087515 \h 1097.4.The Lock Manager PAGEREF _Toc458087516 \h 1117.4.1.Monitoring Lock Activity in SQL Server PAGEREF _Toc458087517 \h 1117.5.SQL Server Lock Types PAGEREF _Toc458087518 \h 1147.6.SQL Server Lock Granularity PAGEREF _Toc458087519 \h 1157.7.Using Application Locks PAGEREF _Toc458087520 \h 1187.8.Index Locking PAGEREF _Toc458087521 \h 1197.9.Locking Contention and Deadlocks PAGEREF _Toc458087522 \h 120database management system oveview Data vs. InformationDataData is raw, unorganized facts that need to be processed. Data can be something simple and seemingly random and useless until it is rmationWhen data is processed, organized, structured or presented in a given context so as to make it useful, it is called information.What is a Database?A database a set of data that has a regular structure and that is organized in such a way that a computer can easily find the desired information.Database management systemA?database?management?system?(DBMS) is a collection of programs?that enables you to?store, modify, and extract information from a?database. There are many different types of database management systems, ranging from small?systems?that?run?on personal computers?to huge systems that run on?mainframes. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.Characteristics of the Database Self-describing nature of a database system.Insulation between programs and data, and data abstraction.Support of multiple views of the data.Sharing of data and multiuser transaction processing.Characteristics of the Database management systemData Independence: Application programs should not be exposed to details of data representation and storage. The DBMS provides an abstract view of the data that hides such details. Efficient Data Access: A DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiently. This feature is especially impOl'tant if the data is stored on external storage devices.Data Integrity and Security: If data is always accessed through the DBMS, the DBMS can enforce integrity constraints. For example, before inserting salary information for an employee, the DBMS can check that the department budget is not exceeded.Data Administration: When several users share the data, experienced professionals who understand the nature of the data being managed, and how different groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for fine tuning the storage of the data to make retrieval efficient.Concurrent Access and Crash Recovery: A DBMS schedules concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures.DBMS-ArchitectureA 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.36855409652000Database (Data) Tier: the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.Application (Middle) Tier: reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database.User (Presentation) Tier?? End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.Services of DBMSA DBMS performs several important functions that guarantee integrity and consistency of data in the databaseData Storage Management:?It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the?operating system?to access the physical storage.Data Manipulation Management:?A DBMS furnishes users with the ability to retrieve, update and delete existing data in the database.Data Definition Services:?The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mappings in source form.Data Dictionary/System Catalog Management:?The DBMS provides a data dictionary or system catalog function in which descriptions of data items are stored and which is accessible to users.Database Communication Interfaces:?The end-user's requests for database access are transmitted to DBMS in the form of communication messages.Authorization?/?Security Management: The DBMS protects the database against unauthorized access, either international or accidental. It furnishes mechanism to ensure that only authorized users an access the database.Backup and Recovery Management:?The DBMS provides mechanisms for backing up data periodically and recovering from different types of failures. This prevents the loss of data,Concurrency Control Service:?Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.Transaction Management:?A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.Database Access and Application Programming Interfaces:?All DBMS provide interface to enable applications to use DBMS services. They provide data access via Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data Definition Language (DDL) and (b) a Data Manipulation Language (DML).Types of database management systems There are four structural types of database management systems:Hierarchical databases: In a hierarchical database, records contain information about there groups of parent/child relationships, In this structure Data follows a series of records, these record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of work databases: A network databases are mainly used on a large digital computers. It more connections can be made between different types of data, Network databases are similar to hierarchical databases by also having a hierarchical structure. A network database looks more like a interconnected network of records.Relational databases: A relational database (RDB) is a collective set of multiple data sets organized by tables, records and columns. RDBs establish a well-defined relationship between database tablesObject-oriented databases: The object-oriented database derivation is the integrity of object-oriented programming language systems and consistent systemsSQL Server 2008 overviewSQL Server 2008 is an database management system (DBMS) that is capable of running anything from a personal database only a few megabytes in size on a handheld Windows Mobile device up to a multiserver database system managing terabytes of information.SQL Server Components and FeaturesThe SQL Server Database EngineThe Database Engine is the core application service in the SQL Server package for storing, processing, and securing data with SQL Server 2008 that can be used to store and process data in a relational format. The following are the main responsibilities of the Database Engine:Provide reliable storage for dataProvide a means to rapidly retrieve this data Provide consistent access to the data Control access to the data through security Enforce data integrity rules to ensure that the data is reliable and consistent.SQL Server 2008 Administration and Management ToolsSQL Server Management Studio - SSMS is the central console from which most database management tasks can be coordinated. SSMS provides a single interface from which all servers in a company can be managed. Following are some of the tasks you can perform with SSMS:Completely manage many servers in a convenient interface. Set server options and configuration values, such as the amount of memory and number of processors to use, default language, and default location of the data and log files. Manage logins, database users, and database roles. Create, edit, and schedule automated jobs through the SQL Server Agent. Back up and restore databases and define maintenance plans.Create new databases.Browse table contents. Create and manage database objects, such as tables, indexes, and stored proceduresGenerate DDL scripts for databases and database objects. Configure and manage replication. Create, edit, execute, and debug Transact-SQL (T-SQL) scripts.Define, implement, manage, and invoke SQL Server Policies SQL Server Configuration Manager is a tool provided with SQL Server 2008 for managing the services associated with SQL Server and for configuring the network protocols used by SQL Server. Primarily, SQL Server Configuration Manager is used to start, pause, resume, and stop SQL Server services and to view or change service propertiesSQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs.The SQL Server Profiler is a GUI interface to the SQL Trace feature of SQL Server that captures the queries and results flowing to and from the database engine.ReplicationReplication is a server-based tool that you can use to synchronize data between two or more databases. SQL Server supports three kinds of replication: Snapshot replication: With snapshot replication, the server takes a picture, or snapshot, of the data in a table at a single point in timeTransactional replication: Initially set up with a snapshot, the server maintains downstream replication targets by reading the transaction log at the source and applying each change at the targets. For every insert, update, and delete operation, the server sends a copy of the operation to every downstream database.Merge replication: With snapshot and transactional replication, a single source of data exists from which all the replication targets are replenished. In some situations, it might be necessary or desirable to allow the replication targets to accept changes to the replicated tables and merge these changes together at some later date.SQL Server Integration Services (SSIS)SSIS is a platform for building high-performance data integration solutions and workflow solutions. You can build extract, transform, and load (ETL) packages to update data warehouses, interact with external processes, clean and mine data, process analytical objects, and perform administrative tasks.SQL Server Analysis Services (SSAS) SSAS provides online analytical processing (OLAP) and data mining functionality for business intelligence (BI) solutions. SSAS provides a rich set of data mining algorithms to enable business users to mine data, looking for specific patterns and trends.SQL Server Reporting Services (SSRS) SQL Server Reporting Services is a server-based reporting platform that delivers enterprise, web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.SQL Server 2008 R2 Editions.SQL Server 2008 Standard EditionThis is the version intended for the masses to medium-sized systems who don’t require the performance, scalability, and availability provided by Enterprise Edition.SQL Server 2008 Enterprise EditionThe Enterprise Edition of SQL Server 2008 is the most comprehensive and complete edition available. It provides the most scalability and availability of all editions and is intended for systems that require high performance and availability, such as large-volume websites, data warehouses, and high-throughput online transaction processing (OLTP) systems.Other SQL Server 2008 EditionsThe Standard and Enterprise Editions of SQL Server 2008 are intended for server-based deployment of applications. In addition, the following editions are available for other specialized uses: Workgroup Edition Developer Edition Web Edition Express EditionCompact EditionCreating and Managing Databases Database in SQL Server A databases is a collection of tables and related objects that help protect and organize data. It must exist before you can create all database objects, including tables, indexes, and stored procedures. Have two types of databaseSystem databases: the databases that are installed by default when SQL Server is installed, the system databases include master, model, msdb, tempdb, and resource.The master database contains an entry for every user database created and contains server wide information critical to the operation of SQL Server.The model database is basically a template database for any newly created databasesUser databases: The databases that users createData Storage in SQL ServerA database is a storage structure for database objects. It is made up of at least two files. Data file: stores the database objects, such as tables and indexes.Log file: records changes to the data. A data file or log file can belong to only one database.Database FilesPrimary FilesThe primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.Secondary FilesSecondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended file name extension for secondary data files is .ndf.Using FilegroupsEvery database has a primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other filegroups. User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.When creating filegroups, you should keep in mind the following restrictions: You can’t move a data file to another filegroup after it has been added to the database.Filegroups apply only to data files and not to log filesA data file can be part of only one filegroup and cannot be spread across multiple filegroups.You can have a maximum of 32,767 filegroups for each database.Using PartitionsPartitioning in SQL Server 2008 allows for a single table or index to be aligned to more than one filegroup. The ability to spread a table or an index across multiple filegroups is particularly useful for large tables. You can partition a table across multiple filegroups and have data files live on separate disk drives to improve performanceTransaction Log FilesThe transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.Creating DatabasesDatabase creation is a relatively straightforward operation that you can perform by using T-SQL statements or SSMS. Because the data and log files are created at the time the database is created, the time it takes for the database to be created depends on the size and number of files you specify when you create the database.Using SSMS to Create a DatabaseThe Object Explorer in SSMS makes creating a database simple. Right-click the Databases node select New Database. The New Database dialog appears. The General page is selected by default. It allows you to select the essential information needed to create a database, including the database name, database owner, and location of the database files.Using T-SQL to Create DatabasesThe T-SQL command to do this is CREATE DATABASE714375354330CREATE DATABASE [mydb] ON PRIMARY (NAME = N’mydb’, FILENAME = N’C:\mssql2008\data\mydb.mdf’ , SIZE = 2048KB, FILEGROWTH = 1024KB ) LOG ON (NAME = N’mydb_log’, FILENAME = N’C:\mssql2008\log\mydb_log.ldf’, SIZE = 1024KB, FILEGROWTH = 10%) 0CREATE DATABASE [mydb] ON PRIMARY (NAME = N’mydb’, FILENAME = N’C:\mssql2008\data\mydb.mdf’ , SIZE = 2048KB, FILEGROWTH = 1024KB ) LOG ON (NAME = N’mydb_log’, FILENAME = N’C:\mssql2008\log\mydb_log.ldf’, SIZE = 1024KB, FILEGROWTH = 10%) Examplesetting database optionsThe Database OptionsUse SSMS to Set Database OptionsOn Options page of the Database Properties dialogRight-click a database in the SSMS Object Explorer and select PropertiesUsing T-SQL to Set Database OptionsUse the ALTER DATABASE command to set options Example: The following command sets AUTO_UPDATE STATISTICS to OFF in the AdventureWorks2008 databaseALTER DATABASE [AdventureWorks2008] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAITYou can also change some of the options by using the system stored procedure sp_dboptionEXEC sp_dboption ‘AdventureWorks2008’, ‘single user’, ‘TRUE’managing database Expanding DatabasesUse SSMSRight-click the database in the Object Explorer and select Properties.When the Database Properties dialog appears, select the Files page to list all the files associated with the database. The Initial Size (MB) column displays the current disk allocation for each file. Enter the new size directly into the column or use the up arrow to increase the size.Use T-SQLUse ALTER DATABASE command Example: ALTER DATABASE [AdventureWorks2008]MODIFY FILE (NAME = N’AdventureWorks2008_Data’, SIZE = 200MB)Moving DatabasesSometimes you need to move a database or database file. There are several ways to accomplish this task:Make a database backup and then restore it to a new location. Alter the database, specifying a new location for the database file. Detach the database and then reattach the database, specifying an alternate locationRestoring a Database Backup to a New LocationUsing ALTER DATABASE commandThe ALTER DATABASE option for moving user database files was added in SQL Server 2005. This option involves the following steps:Take the database offline. Manually move the file(s) to the new location. Run the ALTER DATABASE command to set the FILENAME property to the new file location.Detaching and Attaching DatabasesA convenient way to move or copy database files is to detach and attach databases.Detaching database files removes the database from an instance of SQL Server but leaves the database files intact. After the database is detached, the files associated with the database (that is, .mdf, .ndf, and .ldf files) can be copied or moved to an alternate location. You can then reattach the relocated files by using the CREATE DATABASE command with the FOR ATTACH option.Use SMSS:In Object Explorer, you simply right-click the database’s node and select the Attach option. The Attach Databases dialog box appears, allowing you to specify the database file(s) you want to attach. You need to click the Add button to be able to select a database file for restoration. When you select the main .mdf file associated with the database, the associated file information for the other related database files is populated as well.Use T-SQLDetach the databaseEXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks2008’, @keepfulltextindexfile=N’false’ GO Attach the database CREATE DATABASE [AdventureWorks2008] ON (FILENAME =‘d: \AdventureWorks2008_Data.mdf’), (FILENAME = ‘d:\ AdventureWorks2008_log.LDF’) FOR ATTACHCreating and managing tablesCreate tableUsing Object Explorer to Create TablesIn Object Explorer, right-clicking this Tables node, select New TableDefine a column name and select the data type from the Data Type drop-downUsing T-SQL to Create Tables104775345440CREATE TABLE [database_name . [schema_name ] . | schema_name.] table_name ({<column_definition> | <computed_column_definition>} [<table_constraint>] [, ...n])[ON {partition_scheme_name ( partition_column_name ) | filegroup | “default” } ] [{TEXTIMAGE_ON {filegroup | “default” } ] [ ; ]0CREATE TABLE [database_name . [schema_name ] . | schema_name.] table_name ({<column_definition> | <computed_column_definition>} [<table_constraint>] [, ...n])[ON {partition_scheme_name ( partition_column_name ) | filegroup | “default” } ] [{TEXTIMAGE_ON {filegroup | “default” } ] [ ; ]Basic T-SQL CREATE TABLE SyntaxExampleCREATE TABLE Printer (PrinterID int NOT NULL, DepartmentID smallint NOT NULL, PrinterName varchar(50) NOT NULL,Manufacturer varchar(50) NOT NULL)Defining columnsA table is defined as a collection of columns. Each column represents an attribute of the database table and has characteristics that define its scope and the type of data it can contain.Data typesSystem data typesData TypeRange/DescriptionStoragebigint –263 (–9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807)8 bytesbinary (n) Binary data with a length of n bytes The number of bytes defined by n, up to 8,000 bit An integer data type that can take a value of 1, 0, or NULL1 byte for every eight columns that are defined as bits on the tablchar Up to 8,000characters 1 byte per characterdate0001-01-01 through 9999-12-313 bytesdatetime through8 bytes; accurate to December 31, 9999January 1, 1753, 3.33 millisecondsdecimalBased on the precision–1038+1 to 1038-1float–1.79E + 38 to –2.23E – 38, 0 and 2.23E – 38 to 1.79E + 384 or 8 bytes, depending on the allocation mantissaint–231 (–2,147,483,648) to 231-1 (2,147,483,647)4bytesncharUp to 4,000 Unicode charactersTwo times the number of characters enterednumeric (p,s) 1038-1Based on the precision–1038+1 throughnvarchar(n)Up to 4,000 Unicode characters Two times the number of characters enterednvarchar(max)Unicode characters up to the maximum storage capacity ofTwo times the number plus 2 bytes, up to 230-1Real–1.18E – 38, 0 and 1.18E – 38 to 3.40E + 384bytesSmalldatetimeJanuary 1, 1900, through June 6, 2079 4 bytes; accurate to 1 minutesmallint to 215-1 (32,767)–215 (–32,768)2 bytesvarchar (n)Up to 8,000 characters1 byte per charactervarchar (max)Non-Unicode characters up to the maximum storage capacity1 byte per character; maximum 231-1 bytesTo convert one data type to anotherUse function CAST(expr AS data_type)expr is the expression to convert, data_type is the target data type. data_type is one of the data typesExample:SELECT price AS "price(DECIMAL)", CAST(price AS INTEGER) AS "price(INTEGER)", '<' || CAST(price AS CHAR(8)) || '>' AS "price(CHAR(8))"User-Defined Data TypesUser-defined data types allow you to create custom data types that are based on the existing system data types. You can create user-defined data types by using T-SQL in a couple of different waysUsing the sp_addtype system stored procedureUsing the new CREATE TYPE command:Example: CREATE TYPE [dbo].[ShortDescription] FROM [varchar](20) NOT NULLAfter a user-defined data type is created, you can use it in the definition of tablesExample: CREATE TABLE [dbo].CodeTable(TableId int identity, TableDesc ShortDescription)Column propertiesName and data type are the most basic properties of a column, but many other properties can be defined for a columnThe NULL and NOT NULL KeywordsWhen you are defining tables, it is always good idea to explicitly state whether a column should or should not contain nulls. You do this by specifying the NULL or NOT NULL keywords after the column data typeIdentity Columns A property commonly specified when creating tables is IDENTITY. This property automatically generates a unique sequential value when it is assigned to a column. It can be assigned only to columns that are of the following types:DecimalIntNumericSmallintBigintTinyintModifying tableUsing T-SQL to Modify TablesThe following types of changes by using the ALTER TABLE statementChange a column property, such as a data type or NULL property. Add new columns or drop existing columns. Add or drop constraints. Enable or disable CHECK and FOREIGN KEY constraints. .Enable or disable triggers.Reassign partitions. Alter an index associated with a constraint.ExampleChanging a Column PropertyAlter table titles Alter column notes varchar (400) nullAdding and Dropping ColumnsALTER TABLE titlesAdd ISBN int nullImplementing data integrtingTypes of data integrityIntegrity is enforced depends on the type of integrity, include domain, entity, and referential integrity.Domain IntegrityDomain integrity controls the validation of values for a column, SQL Server provides several mechanisms to enforce domain integrity: Control the type of data stored in a column by assigning a data type to the column.Use CHECK constraints and rules to control the format of the data. Control the range of values stored in a column by using FOREIGN KEY constraints, CHECK constraints, Default definitions, Null ability, and rules.Entity IntegrityEntity integrity requires that all rows in a table be unique. Enforce entity integrity in SQL Server by using PRIMARY KEY constraints, UNIQUE constraints, and IDENTITY properties.Referential IntegrityReferential integrity preserves the defined relationships between tables. Can define such a relationship in SQL Server by relating foreign key columns on one table to the primary key or unique key of another table. When it is defined, referential integrity ensures that values inserted in the foreign key columns have corresponding values in the primary table. It also controls changes to the primary key table and ensures that related foreign key rows are not left orphaned.Enforcing data integratingYou can enforce data integrity by using declarative or procedural methods.Implementing Declarative Data IntegrityDeclarative integrity is enforced within the database, using constraints, rules, and defaults. This is the preferred method of enforcing integrity because it has low overhead and requires little or no custom programming. It can be centrally managed in the database, and it provides a consistent approach for ensuring the integrity of data.Implementing Procedural Data IntegrityProcedural integrity can be implemented with stored procedures, triggers, and application code. It requires custom programming that defines and enforces the integrity of the data. The biggest benefits of implementing procedural data integrity are flexibility and control. You can implement the custom code in many different ways to enforce the integrity of your data.Using constraintsConstraints—including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT—are the primary method used to enforce data integrity.The PRIMARY KEY ConstraintThe PRIMARY KEY constraint is one of the key methods for ensuring entity integrity. When this constraint is defined on a table, it ensures that every row can be uniquely identified with the primary key value(s). The primary key can have one or more columns as part of its definition.There can be only one primary key defined for each table. When a primary key is defined on a table, a unique index is automatically created as well. This index contains all the columns in the primary key and ensures that the rows in this index are unique.ExampleALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED (BusinessEntityID ASC)The UNIQUE ConstraintThe UNIQUE constraint is functionally similar to PRIMARY KEY. It also uses a unique index to enforce uniqueness, but unlike PRIMARY KEY, it allows nulls in the columns that participate in the UNIQUE constraint.Example:USE AdventureWorks2012; GO CREATE TABLE Production.TransactionHistoryArchive4 ( TransactionID int NOT NULL, CONSTRAINT AK_TransactionID UNIQUE(TransactionID) ); GO The FOREIGN KEY Referential Integrity ConstraintFOREIGN KEY constraints are the declarative means for enforcing referential integrity.The columns in the referencing table can be referred to as foreign key columns. The table with the primary key or unique index can be referred to as the primary table.Cascading Referential Integrity: This type of integrity allows for updates and deletions on the primary table to be cascaded to the referencing foreign key tables. Can specify the same cascading actions for updates and deletions:NO ACTION: the default, causes deletions and updates to the primary table to fail if the rows are referenced by a foreign keyCASCADE: This option causes updates and deletions to cascade to any foreign key records that refer to the affected rows in the primary table. If CASCADE is used with the ON DELETE clause, any records in the foreign key table that refer to the deleted rows in the primary table are also deleted. If CASCADE is used with the ON UPDATE clause, any updates to the primary table records are also made in the related rows of the foreign key table.SET NULL: It is similar to the CASCADE option except that the affected rows in the foreign key table are set to NULL when deletions or updates are performed on the related primary table. SET DEFAULT: similar to the CASCADE option except that the affected rows in the foreign key table are set to the default values defined on the columns when deletions or updates are performed on the related primary table. To use this option, each column in the foreign key must have a default definition assigned to it, or it must be defined as nullable.ExampleALTER TABLE [Person].[BusinessEntityAddress]ADD CONSTRAINT [FK_BSEntityAddress_AddressType_AddressTypeID] FOREIGN KEY ([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID]) ON DELETE CASCADEThe CHECK ConstraintUse the CHECK constraint to enforce domain integrity and to provide a means for restricting the values that can be entered in a column.CHECK constraints are good for ensuring the format of data inserted in a column and for defining a list of acceptable values.Example:00ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_Gender] CHECK ((upper ([Gender]) =’F’ OR upper ([Gender]) =’M’))ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_Gender] CHECK ((upper ([Gender]) =’F’ OR upper ([Gender]) =’M’))Creating ConstraintsUsing T-SQL to Create ConstraintsCreating Constraints by Using a CREATE TABLE Statement103505349885CREATE TABLE [Sales].[Customer2]([CustomerID] [int] IDENTITY(1,1) NOT NULL, [AccountNumber] AS (isnull(‘AW’+[dbo].[ufnLeadingZeros]([CustomerID]),’’)), [CustomerType] [nchar](1) NOT NULL CONSTRAINT CK_Customer_CustomerType2 CHECK ((upper([CustomerType])=’I’ OR upper([CustomerType])=’S’)), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Customer_ModifiedDate2] DEFAULT (getdate()), CONSTRAINT [PK_Customer_CustomerID2] PRIMARY KEY CLUSTERED ([CustomerID] ASC),CONSTRAINT FK_Customer_SalesTerritory_TerritoryID2 FOREIGN KEY ([TerritoryID]) REFERENCES [Sales].[SalesTerritory] ([TerritoryID]) )0CREATE TABLE [Sales].[Customer2]([CustomerID] [int] IDENTITY(1,1) NOT NULL, [AccountNumber] AS (isnull(‘AW’+[dbo].[ufnLeadingZeros]([CustomerID]),’’)), [CustomerType] [nchar](1) NOT NULL CONSTRAINT CK_Customer_CustomerType2 CHECK ((upper([CustomerType])=’I’ OR upper([CustomerType])=’S’)), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Customer_ModifiedDate2] DEFAULT (getdate()), CONSTRAINT [PK_Customer_CustomerID2] PRIMARY KEY CLUSTERED ([CustomerID] ASC),CONSTRAINT FK_Customer_SalesTerritory_TerritoryID2 FOREIGN KEY ([TerritoryID]) REFERENCES [Sales].[SalesTerritory] ([TerritoryID]) )Example: Using SSMS to Create ConstraintsExample: To create a new check constraintIn?Object Explorer, expand the table to which you want to add a check constraint, right-click?Constraints?and click?New Constraint.In the?Check Constraints?dialog box, click in the?Expression?field and then click the ellipses?(…).7067551086210SellEndDate >= SellStartDate OR SellEndDate IS NULLSellEndDate >= SellStartDate OR SellEndDate IS NULLIn the?Check Constraint Expression?dialog box, type the SQL expressions for the check constraint. For example, to limit the entries in the SellEndDate?column of the?Product?table to a value that is either greater than or equal to the date in the?SellStartDate?column or is a NULL value, type:Managing ConstraintsGathering Constraint Information: The sp_help and sp_helpconstraint system stored procedures are another good source of information about constraints.Example: exec sp_helpconstraint [Sales.Customer]Dropping ConstraintsCan drop constraints by using the visual tools or by using T-SQLUse SSMS: Right-click a constraint in the Object Explorer and select the Delete option to drop that constraint.T-SQL statementsALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [CK_Employee_Gender]You should use caution when dropping constraints because some constraints affect other tableDisabling ConstraintsCan disable CHECK and FOREIGN KEY constraints by using the NOCHECK clause. This capability allows you to stop the constraints from being checked without removing the constraints from your database. Example: The ALTER TABLE command to disable the FK_Customer_SalesTerritory_TerritoryID foreign key constraint on the Customer table:ALTER TABLE Sales.Customer NOCHECK CONSTRAINT FK_Customer_SalesTerritory_TerritoryIDWhen the constraint is disabled, it no longer performs validation.RulesYou can use rules as another method to enforce domain integrity. Rules are similar to CHECK constraints but have some limitations. The biggest advantage when using a rule is that one rule can be bound to multiple columns or user-defined data types292735469385CREATE RULE [ schema_name . ] rule_name AS condition_expression [ ; ]CREATE RULE [ schema_name . ] rule_name AS condition_expression [ ; ]The syntax for creating a rule:Condition_expression can include any statement that can be placed in a WHERE clauseExample: the creation of a rule that could be used to enforce the format of data inserted in phone number columnsCREATE RULE phone_rule AS @phone LIKE ‘([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]’DefaultsA default provides a value for a column when a value is not supplied. Defaults can be anything that evaluates to a constant, such as a constant, built-in function, or mathematical expression. Defaults are of two types: declarative and bound. The two types are functionally the same; the difference is in how they are implementedDeclarative DefaultsA declarative default is a constraint defined as part of the table definition. Using declarative defaults is the preferred method for assigning default values to columnsCan use the CREATE TABLE or ALTER TABLE statement to create a default and assign it to a column.Example:ALTER TABLE Sales.Customer ADD CONSTRAINT DF_Customer_CustomerType DEFAULT ‘I’ FOR CustomerTypeBound DefaultsBound defaults are similar to rules in that you first create a bound default and then bind it to a column or set of columns, use the CREATE DEFAULT command to establish a default that can be bound to a column at a later time. The CREATE DEFAULT syntax is as follows:00CREATE DEFAULT [ schema_name . ] default_name AS constant_expression [ ; ]CREATE DEFAULT [ schema_name . ] default_name AS constant_expression [ ; ]Constant_expression can include any constant, built-in function, or mathematical expressionWhen a Default Is AppliedDefaults are applied only when no value is specified for a column during an insertion. They can also be applied during insertions and updates when the DEFAULT keyword is used.Example: CREATE TABLE test_default (id int IDENTITY NOT NULL,tmstmp timestamp NOT NULL, password char(13) NOT NULL DEFAULT ‘defaultpw’, Shortdesc VARCHAR(50) NULL)indexAn index on a table or view allows you to efficiently find the information you are looking for in a database.Also new to SQL Server 2008 are filtered indexes. Filtered indexes utilize a WHERE clause that filters or limits the number of rows included in the index. The smaller filtered index allows queries that are run against rows in the index to run faster.Types of indexesSQL Server has two main types of indexes: clustered and nonclustered. They both help the query engine get at data faster, but they have different effects on the storage of the underlying dataClustered Indexes Clustered indexes sort and store the data rows for a table, based on the columns defined in the index.222885020170200A clustered index is analogous to a filing cabinet where each drawer contains a set of file folders stored in alphabetical order, and each file folder stores the files in alphabetical order.A clustered index is structured as a balanced tree (B-tree). The top, or root, node is a single page where searches via the clustered index are started. The bottom level of the index is the leaf nodes. With a clustered index, the leaf nodes of the index are also the data pages of the table. Any levels of the index between the root and leaf nodes are referred to as intermediate nodes.Nonclustered IndexesA nonclustered index is a separate index structure, independent of the physical sort order of the data rows in the table. You are therefore not restricted to creating only 1 nonclustered index per table; in fact, in SQL Server 2008 you can create up to 999 nonclustered indexes per table. To find the pages on which a specific subject is discussed, you look up the subject in the index and then go to the pages referenced in the index.A nonclustered index is also structured as a B-treeThe main difference between a nonclustered index and clustered index:The leaf row of a nonclustered index is independent of the data rows in the table. The leaf level of a nonclustered index contains a row for every data row in the table, along with a pointer to locate the data rowThis pointer is either the clustered index key for the data row, if the table has a clustered index on it, or the data page ID and row ID of the data row if the table is stored as a heap structure.Creating IndexesCreating Indexes with T-SQLCREATE INDEX SyntaxArguments for CREATE INDEXArgument ExplanationUNIQUEIndicates that no two rows in the index can have the same index key values. Inserts into a table with a UNIQUE index will fail if a row with the same value already exists in the table.CLUSTERED | NON-CLUSTEREDDefines the index as clustered or nonclustered. NON-CLUSTERED is the default. Only one clustered index is allowed per tableindex_nameSpecifies the name of the index to be createdObjectSpecifies the name of the table or view to be indexed.column_nameSpecifies the column or columns that are to be indexed.ASC | DESCSpecifies the sort direction for the particular index column. ASC creates an ascending sort order and is the default. The DESC option causes the index to be created in descending order.INCLUDE (column [ ,... n ] )Allows a column to be added to the leaf level of an index without being part of the index key. This is a new argument.WHERE <filter_predicate>This argument, new to SQL Server 2008, is used to create a filtered index. The filter_predicate contains a WHERE clause that limits the number of rows in the table that are included in the index.relational_index_optionSpecifies the index option to use when creating the index.Example:CREATE NONCLUSTERED INDEX [NC_Person_LastName] ON [Person].[Person] ( [LastName] ASC ).Creating Indexes with SSMSExpand the database tree in the Object Explorer and navigating to the Indexes node of the table that you want to add the index to. Then you right-click the Indexes node and select New IndexManaging IndexesManaging Indexes with T-SQLUse ALTER INDEX statementExample ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REBUILDManaging Indexes with SSMS3459192461597Several tools are available in SSMS for managing indexes. You can use tools such as the Database Engine Tuning Advisor, database diagrams, and the Table Designer to view indexes and make modifications.Right-clicking an index in the Object Explorer. Many of these options are geared toward index management, including the options Rebuild, Reorganize, and Disable.Dropping indexesCan drop indexes by using T-SQL or tools in the SSMSExample:DROP INDEX [IX_WorkOrder_ScrapReasonID] ON [Production].[WorkOrder]retrieving and manipulating datadata retrievalData Manipulation Language (DML) is a vocabulary used to retrieve and work with data in SQL Server.General SELECT statementUsing SELECT to retrieve rows and columnsExample: Uses AdventureWorks SELECT FirstName, LastName, StartDate AS FirstDay FROM DimEmployee ORDER BY LastName; Using SELECT with column headings and calculationsExample: Uses AdventureWorks SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay FROM DimEmployee ORDER BY LastName;Using DISTINCT with SELECTExample: Uses AdventureWorks SELECT DISTINCT Title FROM DimEmployee ORDER BY Title; Using GROUP BY with multiple groupsExample: Uses AdventureWorks SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey, PromotionKey ORDER BY OrderDateKey; Using GROUP BY and WHEREExample: Uses AdventureWorks SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales WHERE OrderDateKey > '20020801' GROUP BY OrderDateKey ORDER BY OrderDateKey; Using the HAVING clauseExample: Uses AdventureWorks SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey HAVING OrderDateKey > 20010000 ORDER BY OrderDateKey; MERGE statementWith the MERGE statement, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table, all in just a single statement, minimizing the number of times that rows in the source and target tables need to be processed.The MERGE statement can also be used for performing conditional inserts or updates of rows in a target table from a source tableThe basic syntax of the MERGE statement is as followsExampleIn this example I will take a Products table as target table and UpdatedProducts as a source table containing updated list of products. I will then use the MERGE SQL command to synchronize the target table with the source table.First Let's create a target table and a source table and populate some data to these tables94890527--Create a target tableCREATE TABLE Products(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into target tableINSERT INTO Products VALUES(1, 'Tea', 10.00),(2, 'Coffee', 20.00),(3, 'Muffin', 30.00),(4, 'Biscuit', 40.00)GO--Create source tableCREATE TABLE UpdatedProducts(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into source tableINSERT INTO UpdatedProducts VALUES(1, 'Tea', 10.00),(2, 'Coffee', 25.00),(3, 'Muffin', 35.00),(5, 'Pizza', 60.00)GOSELECT * FROM ProductsSELECT * FROM UpdatedProductsGO0--Create a target tableCREATE TABLE Products(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into target tableINSERT INTO Products VALUES(1, 'Tea', 10.00),(2, 'Coffee', 20.00),(3, 'Muffin', 30.00),(4, 'Biscuit', 40.00)GO--Create source tableCREATE TABLE UpdatedProducts(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into source tableINSERT INTO UpdatedProducts VALUES(1, 'Tea', 10.00),(2, 'Coffee', 25.00),(3, 'Muffin', 35.00),(5, 'Pizza', 60.00)GOSELECT * FROM ProductsSELECT * FROM UpdatedProductsGOAggregating data26475901300360A simple aggregate will return a single value for the entire table. Although a small number of requirement can be satisfied by simple aggregating all rows within a table, many aggregation questions need to roll up data into analysis buckets, such as sales by month or defects by product line. The group by clause allows you to define the columns that are used to compute aggregate values.The most common aggregate function: SUM() and AVG() work with only numeric data types. MIN() and MAX() work with character, numeric, and datetime data types. COUNT(expr) and COUNT(*) work with all data types.All aggregate functions except COUNT(*) ignore nulls.COUNT(expr) and COUNT(*) never return null but return either a positive integer or zero. The other aggregate functions return null if the set contains no rows or contains rows with only nulls.An aggregate expression can’t appear in a WHERE clauseFinding a Minimum with MIN ()Use the aggregate function MIN() to find the minimum of a set of valuesMIN() works with character, numeric, and datetime data types. With character data columns, MIN() finds the value that is lowest in the sort sequence.Example: SELECT MIN(price) AS "Min price" FROM titles;Finding a Maximum with MAX() Use the aggregate function MAX() to find the maximum of a set of values.MAX() works with character, numeric, and datetime data types.With character data columns, MAX() finds the value that is highest in the sort sequence; DISTINCT isn’t meaningful with MAX();Example:SELECT MIN(price) AS "Min price", MAX(price) AS "Max price", MAX(price) - MIN(price) AS "Range" FROM titles;Calculating a Sum with SUM () Use the aggregate function SUM() to find the sum (total) of a set of valuesSUM() works with only numeric data types.Example: SELECT SUM(sales) AS "Total sales (2000 books)" FROM titles WHERE pubdate BETWEEN DATE '2000-01-01' AND DATE '2000-12-31';Calculating an Average with AVG () Use the aggregate function AVG() to find the average, or arithmetic mean, of a set of values. The arithmetic mean is the sum of a set of quantities divided by the number of quantities in the set.AVG() works with only numeric data typesThe average of no rows is null—not zero, as you might expect.Example:SELECT AVG(sales) AS "AVG(sales)", SUM(sales) AS "SUM(sales)" FROM titles WHERE type = 'business'; -----SELECT title_id, sales FROM titles WHERE sales > (SELECT AVG(sales) FROM titles) ORDER BY sales DESC;Counting Rows with COUNT ()Use the aggregate function COUNT() to count the number of rows in a set of values. COUNT() has two forms: COUNT(expr) returns the number of rows in which expr is not null. COUNT(*) returns the count of all rows in a set, including nulls and duplicates.COUNT(expr) and COUNT(*) work with all data types and never return null.COUNT(*) - COUNT(expr) returns the number of nulls, ((COUNT(*) - COUNT(expr))*100)/COUNT(*) returns the percentage of nulls.Joins A join is a table operation that uses related columns to combine rows from two input tables into one result table.Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.Joins can be categorized as:Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. 1035050484577USE AdventureWorks2008R2;GOSELECT e.BusinessEntityIDFROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.BusinessEntityID = s.BusinessEntityIDUSE AdventureWorks2008R2;GOSELECT e.BusinessEntityIDFROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.BusinessEntityID = s.BusinessEntityIDFor example, here is an inner join retrieving the employees who are also sales personsOuter joins. Outer joins can be a left, a right, or full outer join.Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:LEFT JOIN or LEFT OUTER JOINThe result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.1121410253365USE AdventureWorks2008R2;GOSELECT p.Name, pr.ProductReviewIDFROM Production.Product pLEFT OUTER JOIN Production.ProductReview prON p.ProductID = pr.ProductID0USE AdventureWorks2008R2;GOSELECT p.Name, pr.ProductReviewIDFROM Production.Product pLEFT OUTER JOIN Production.ProductReview prON p.ProductID = pr.ProductIDExample: RIGHT JOIN or RIGHT OUTER JOINA right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.1034870289057USE AdventureWorks2008R2;GOSELECT st.Name AS Territory, sp.BusinessEntityIDFROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson spON st.TerritoryID = sp.TerritoryID ;USE AdventureWorks2008R2;GOSELECT st.Name AS Territory, sp.BusinessEntityIDFROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson spON st.TerritoryID = sp.TerritoryID ;Example:An outer join can be further restricted by using a predicate. 940159531076USE AdventureWorks2008R2;GOSELECT st.Name AS Territory, sp.BusinessEntityIDFROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson spON st.TerritoryID = sp.TerritoryID WHERE st.SalesYTD < $2000000;USE AdventureWorks2008R2;GOSELECT st.Name AS Territory, sp.BusinessEntityIDFROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson spON st.TerritoryID = sp.TerritoryID WHERE st.SalesYTD < $2000000;Example: contains the same right outer join, but includes only sales territories with sales less than $2,000,000:FULL JOIN or FULL OUTER JOINA full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.638175408305USE AdventureWorks2008R2;GO-- The OUTER keyword following the FULL keyword is optional.SELECT p.Name, sod.SalesOrderIDFROM Production.Product pFULL OUTER JOIN Sales.SalesOrderDetail sodON p.ProductID = sod.ProductIDWHERE p.ProductID IS NULLOR sod.ProductID IS NULLORDER BY p.Name ;0USE AdventureWorks2008R2;GO-- The OUTER keyword following the FULL keyword is optional.SELECT p.Name, sod.SalesOrderIDFROM Production.Product pFULL OUTER JOIN Sales.SalesOrderDetail sodON p.ProductID = sod.ProductIDWHERE p.ProductID IS NULLOR sod.ProductID IS NULLORDER BY p.Name ;ExampleCross joinsCross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.1043677394923USE AdventureWorks2008R2;GOSELECT p.BusinessEntityID, t.Name AS TerritoryFROM Sales.SalesPerson pCROSS JOIN Sales.SalesTerritory tORDER BY p.BusinessEntityID;USE AdventureWorks2008R2;GOSELECT p.BusinessEntityID, t.Name AS TerritoryFROM Sales.SalesPerson pCROSS JOIN Sales.SalesTerritory tORDER BY p.BusinessEntityID;Example:Self-Join A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name.793115224155USE AdventureWorks2008R2;GOSELECT DISTINCT pv1.ProductID, pv1.VendorIDFROM Purchasing.ProductVendor pv1????INNER JOIN Purchasing.ProductVendor pv2????ON pv1.ProductID = pv2.ProductID????????AND pv1.VendorID <> pv2.VendorIDORDER BY pv1.ProductID0USE AdventureWorks2008R2;GOSELECT DISTINCT pv1.ProductID, pv1.VendorIDFROM Purchasing.ProductVendor pv1????INNER JOIN Purchasing.ProductVendor pv2????ON pv1.ProductID = pv2.ProductID????????AND pv1.VendorID <> pv2.VendorIDORDER BY pv1.ProductIDExample: 793630296221SELECT st.Name AS TerritoryName, sp.BusinessEntityID, ????sp.SalesQuota, sp.SalesYTDFROM Sales.SalesPerson AS sp????JOIN Sales.SalesTerritory AS st????????ON sp.TerritoryID = st.TerritoryIDORDER BY st.Name, sp.BusinessEntityIDSELECT st.Name AS TerritoryName, sp.BusinessEntityID, ????sp.SalesQuota, sp.SalesYTDFROM Sales.SalesPerson AS sp????JOIN Sales.SalesTerritory AS st????????ON sp.TerritoryID = st.TerritoryIDORDER BY st.Name, sp.BusinessEntityIDExample: produce a list of all the territories and the sales people working in them.Subqueries A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. 138023325755USE AdventureWorks2008R2;GOSELECT Ord.SalesOrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPriceFROM AdventureWorks2008R2.Sales.SalesOrderHeader AS OrdUSE AdventureWorks2008R2;GOSELECT Ord.SalesOrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPriceFROM AdventureWorks2008R2.Sales.SalesOrderHeader AS OrdExample:Subquery TypesSubqueries can be specified in many places:Subqueries with aliasesMany statements in which the subquery and the outer query refer to the same table can be stated as self-joins (joining a table to itself).888162486314USE AdventureWorks2008R2;GOSELECT StateProvinceID, AddressIDFROM Person.AddressWHERE AddressID IN (SELECT AddressID FROM Person.Address WHERE StateProvinceID = 39)0USE AdventureWorks2008R2;GOSELECT StateProvinceID, AddressIDFROM Person.AddressWHERE AddressID IN (SELECT AddressID FROM Person.Address WHERE StateProvinceID = 39)Example: ?find addresses of employees from a particular stateSubqueries with IN or NOT IN. The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. After the subquery returns results, the outer query makes use of them991295585062USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ProductSubcategoryID IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Wheels');0USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ProductSubcategoryID IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Wheels');Example: finds the names of all the wheel products that Adventure Works Cycles makes?Example: finds the names of the products that are not finished bicycles715992516USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ProductSubcategoryID NOT IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Mountain Bikes' OR Name = 'Road Bikes' OR Name = 'Touring Bikes')0USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ProductSubcategoryID NOT IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Mountain Bikes' OR Name = 'Road Bikes' OR Name = 'Touring Bikes')Subqueries In UPDATE, DELETE, and INSERT statements. Subqueries can be nested in the UPDATE, DELETE, INSERT and SELECT data manipulation (DML) statementsExample: doubles the value in the?ListPrice?column in the?Production.Product?tableright324485USE AdventureWorks2008R2;GO UPDATE Production.ProductSET ListPrice = ListPrice * 2WHERE ProductID IN (SELECT ProductID FROM Purchasing.ProductVendor WHERE BusinessEntityID = 1540);GO0USE AdventureWorks2008R2;GO UPDATE Production.ProductSET ListPrice = ListPrice * 2WHERE ProductID IN (SELECT ProductID FROM Purchasing.ProductVendor WHERE BusinessEntityID = 1540);GOright2072640USE AdventureWorks2008R2;GO UPDATE Production.ProductSET ListPrice = ListPrice * 2FROM Production.Product AS pINNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;GO0USE AdventureWorks2008R2;GO UPDATE Production.ProductSET ListPrice = ListPrice * 2FROM Production.Product AS pINNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;GOHere is an equivalent UPDATE statement using a join:Subqueries with comparison operators.Subqueries can be introduced with one of the comparison operators (=, < >, >, > =, <, ! >, ! <, or < =).A subquery introduced with an unmodified comparison operator (a comparison operator not followed by ANY or ALL) must return a single value rather than a list of values, like subqueries introduced with IN. If such a subquery returns more than one value, SQL Server?displays an error message.For example, if you assume each sales person only covers one sales territory, and you want to find the customers located in the territory covered by Linda Mitchell, you can write a statement with a subquery introduced with the simple = comparison operator.629285-4445USE AdventureWorks2008R2;GOSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID = (SELECT TerritoryID FROM Sales.SalesPerson WHERE BusinessEntityID = 276)0USE AdventureWorks2008R2;GOSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID = (SELECT TerritoryID FROM Sales.SalesPerson WHERE BusinessEntityID = 276)Subqueries with ANY, SOME, or parison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an ISO standard equivalent for ANY.Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be restated with EXISTS.687705753745USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ListPrice >= ANY (SELECT MAX (ListPrice) FROM Production.Product GROUP BY ProductSubcategoryID);0USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE ListPrice >= ANY (SELECT MAX (ListPrice) FROM Production.Product GROUP BY ProductSubcategoryID);Example: finds the products whose list prices are greater than or equal to the maximum list price of any product subcategory.The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <>ALL means the same as NOT IN.Example: finds customers located in a territory not covered by any sales persons7327900Use AdventureWorks2008R2;GOSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID <> ANY(SELECT TerritoryID FROM Sales.SalesPerson) ;00Use AdventureWorks2008R2;GOSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID <> ANY(SELECT TerritoryID FROM Sales.SalesPerson) ;Subqueries with EXISTS or NOT EXISTS. When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.1725283277543WHERE [NOT] EXISTS (subquery)WHERE [NOT] EXISTS (subquery)A subquery introduced with?EXISTS?has the following syntax491490326390USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')0USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')Example: find the names of all products that are in the?Wheels?subcategoryThe keyword EXISTS is not preceded by a column name, constant, or other expression.The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.Example: ?find the names of products that are not in the wheels subcategory:4485620USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE NOT EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')0USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE NOT EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')Subqueries in place of an expression.In Transact-SQL, a subquery can be substituted anywhere an expression can be used in SELECT, UPDATE, INSERT, and DELETE statements, except in an ORDER BY list.Example:00USE AdventureWorks2008R2;GOSELECT Name, ListPrice, (SELECT AVG(ListPrice) FROM Production.Product) AS Average, ListPrice - (SELECT AVG(ListPrice) FROM Production.Product) AS DifferenceFROM Production.ProductWHERE ProductSubcategoryID = 1;USE AdventureWorks2008R2;GOSELECT Name, ListPrice, (SELECT AVG(ListPrice) FROM Production.Product) AS Average, ListPrice - (SELECT AVG(ListPrice) FROM Production.Product) AS DifferenceFROM Production.ProductWHERE ProductSubcategoryID = 1;Modifying Data I’ll discuss aspects of inserting, deleting, updating, and merging data, as well as the OUTPUT clause for data modification statements.Inserting Data T-SQL provides several statements for inserting data into tables: INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, and BULK INSERTThe INSERT VALUES StatementThe INSERT VALUES statement to insert rows into a table based on specified values.Example: INSERT INTO dbo.Orders (orderid, orderdate, empid, custid) VALUES(10001, '20090212', 3, 'A');Enhanced VALUES Clause SQL Server 2008 and SQL Server 2012 support an enhanced VALUES clause that allows you to specify multiple rows separated by commas. Example: INSERT INTO dbo.Orders (orderid, orderdate, empid, custid) VALUES (10003, '20090213', 4, 'B'), (10004, '20090214', 1, 'A'),(10005, '20090213', 1, 'C'),(10006, '20090215', 3, 'C');SELECT INTO The SELECT INTO statement is a nonstandard T-SQL statement that creates a target table and populates it with the result set of a query. By “nonstandard,” I mean that it is not part of the ISO and ANSI SQL standardscenter309245SELECT <Field_list> INTO <target_table_name> FROM <Source_table_name>SELECT <Field_list> INTO <target_table_name> FROM <Source_table_name>Syntax: 647700780415SELECT country, region, cityINTO dbo.LocationsFROM Sales.Customers0SELECT country, region, cityINTO dbo.LocationsFROM Sales.CustomersExample:BULK INSERT The BULK INSERT command can be used within any T-SQL script or stored procedure to import data into SQL Server. The parameters of the command specify the table receiving the data, the location of the source ?le, and the options.Example: bulk inserts the contents of the file c:\temp\orders.txt into the table dbo.Orders-43132324BULK INSERT dbo.Orders FROM 'c: \temp\orders.txt' WITH (DATAFILETYPE='char',FIELDTERMINATOR=',',ROWTERMINATOR= '\n');BULK INSERT dbo.Orders FROM 'c: \temp\orders.txt' WITH (DATAFILETYPE='char',FIELDTERMINATOR=',',ROWTERMINATOR= '\n');Deleting Data T-SQL provides two statements for deleting rows from a table—DELETE and TRUNCATEThe DELETE StatementThe DELETE statement is a standard statement used to delete data from a table based on a predicate. The standard statement has only two clausesThe FROM clause, in which you specify the target table name.The WHERE clause, in which you specify a predicate. Only the subset of rows for which the predicate evaluates to TRUE will be deleted.Example: delete all orders that were placed prior to 2007 from the dbo.Orders table,DELETE FROM dbo.Orders WHERE orderdate < '2007/01/01';The TRUNCATE StatementThe TRUNCATE statement deletes all rows from a table. Unlike the DELETE statement, TRUNCATE has no filter.Example: TRUNCATE TABLE dbo.T1;The advantage that TRUNCATE has over DELETE is that the former is minimally loggedIf you use the DELETE statement, the operation can take minutes or even hours.TRUNCATE and DELETE also have a functional difference when the table has an identity column. TRUNCATE resets the identity value back to the original seed, but DELETE doesn’t.Updating Data T-SQL supports a standard UPDATE statement that allows you to update rows in a table. T-SQL also supports nonstandard uses of the UPDATE statement with joins and with variablesThe UPDATE statement The UPDATE statement is a standard statement that allows you to update a subset of rows in a table. To identify the subset of rows that are the target of the update, you specify a predicate in a WHERE clause. You specify the assignment of values or expressions to columns in a SET clause, separated by commas.939800378663UPDATE dbo.OrderDetails SET discount = discount + 0.05 WHERE productid = 51;0UPDATE dbo.OrderDetails SET discount = discount + 0.05 WHERE productid = 51;Example: Increases the discount of all order details for product 51 by 5 percent.UPDATE Using Joins T-SQL supports a nonstandard syntax for modifying data based on a join, the nonstandard syntax has advantages over the supported standard syntax using subqueries.767547414655UPDATE Sales.Orders SET shipcountry = (SELECT C.country FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid),shipregion = (SELECT C.region FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid), shipcity = (SELECT C.city FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid) WHERE custid IN (SELECT custid FROM Sales.Customers WHERE country = N'USA');UPDATE Sales.Orders SET shipcountry = (SELECT C.country FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid),shipregion = (SELECT C.region FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid), shipcity = (SELECT C.city FROM Sales.Customers AS C WHERE C.custid = Sales.Orders.custid) WHERE custid IN (SELECT custid FROM Sales.Customers WHERE country = N'USA');Example: ViewDefinition of ViewsViews are a logical way of viewing data in the underlying physical tables. They are tied to a SELECT statement that retrieves data from one or more tables or views in the same database or a different database.Using Views Views are useful in many scenarios. Some of the most common scenarios include the following:Simplifying data manipulation. Focusing on specific data. Abstracting data. Controlling access to dataCreating Views Using T-SQL0344170CREATE VIEW [ schema_name . ] view_name [column [ ,...n ] ) ] [WITH <view_attribute> [,...n ] ] AS select_statement [;][WITH CHECK OPTION]00CREATE VIEW [ schema_name . ] view_name [column [ ,...n ] ) ] [WITH <view_attribute> [,...n ] ] AS select_statement [;][WITH CHECK OPTION] Syntax:<view_attribute>::={[ENCRYPTION][SCHEMABINDING][ VIEW_METADATA]}Example:CREATE VIEW Sales.vw_OrderSummary ASSELECT datepart(yy, orderdate) as ‘OrderYear’, datepart(mm, orderdate) as ‘OrderMonth’, sum(TotalDue) as ‘OrderTotal’ FROM Sales.SalesOrderHeader GROUP BY datepart(yy, orderdate), datepart(mm, orderdate)Views can also be created with the following special view attributes: ENCRYPTION, SCHEMABINDING, and VIEW_METADATA.ENCRYPTION The ENCRYPTION attribute causes the view definition to be stored as encrypted text in sys.syscomments. This feature is also available for stored procedures and other database code that you may want to protect.724619270978CREATE VIEW Sales.vw_OrderSummary WITH ENCRYPTION AS SELECT datepart(yy, orderdate) as ‘OrderYear’, datepart(mm, orderdate) as ‘OrderMonth’, sum(TotalDue) as ‘OrderTotal’ FROM Sales.SalesOrderHeader GROUP BY datepart(yy, orderdate), datepart(mm, orderdate)CREATE VIEW Sales.vw_OrderSummary WITH ENCRYPTION AS SELECT datepart(yy, orderdate) as ‘OrderYear’, datepart(mm, orderdate) as ‘OrderMonth’, sum(TotalDue) as ‘OrderTotal’ FROM Sales.SalesOrderHeader GROUP BY datepart(yy, orderdate), datepart(mm, orderdate)Example: SCHEMABINDING The SCHEMABINDING attribute binds a view to the schema of the underlying table(s) referenced in the view’s SELECT statement. This binding action prevents any changes to the underlying tables that would affect the view definition.WITH CHECK OPTION WITH CHECK OPTION forces all data modifications made through a view to adhere to the conditions in the view. 456997319632CREATE VIEW HumanResources.vw_MaleEmployees AS SELECT LoginID, Gender FROM HumanResources.Employee WHERE Gender = 'M' WITH CHECK OPTION 0CREATE VIEW HumanResources.vw_MaleEmployees AS SELECT LoginID, Gender FROM HumanResources.Employee WHERE Gender = 'M' WITH CHECK OPTION Example:Creating Views Using the View DesignerIn the Object Explorer and select Design, right-click the Views node in the Object Explorer and select New View. An empty View Designer is displayed. Managing viewsAltering Views with T-SQL The ALTER VIEW statement utilizes the same set of options and parameters as the CREATE VIEW statementDropping Views with T-SQLYou can drop views from a database by using the DROP VIEW statement. The syntax for DROP VIEW follows:39633579842DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]0DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]You can drop more than one view by using one DROP VIEW statement and listing all the targeted views, separated by commas.T-SQL Programming?Even though the primary purpose of T-SQL is to retrieve and manipulate data, like other programming languages it also contains logic elements. Most of the time you will write T-SQL statements that retrieve or update data, but you can also set up loops and write code with conditional flow. Often database administrators write scripts in T-SQL to perform maintenance tasks that require more than just retrieving or updating data.VariablesVariables hold temporary values used to help you in designing programming logic. A variable is used to hold the results of a calculation, or the results of a string concatenation, or to control the number of times a loop executes …Declaring and Initializing a Variablecenter573405DECLARE @variableName <type>[(size)] = <value1> SET @variableName = <value2>DECLARE @variableName <type>[(size)] = <value1> SET @variableName = <value2>To use a variable, you must first declare it. To assign a value to a variable after declare it, can use the SETstatement or the SELECT statement.Example:USE AdventureWorks2012; GO --1 DECLARE @myNumber INT = 10; PRINT 'The value of @myNumber'; PRINT @myNumber; SET @myNumber = 20; PRINT 'The value of @myNumber'; PRINT @myNumber; GO --2 DECLARE @myString VARCHAR(100), @myBit BIT; SELECT @myString = 'Hello, World', @myBit = 1; PRINT 'The value of @myString'; PRINT @myString; PRINT 'The value of @myBit'; PRINT @myBit; GO --3 DECLARE @myUnicodeString NVARCHAR(100); SET @myUnicodeString = N'This is a Unicode String'; PRINT 'The value of @myUnicodeString'; PRINT @myUnicodeString; GO --4 DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50); SELECT @FirstName = FirstName, @LastName = LastName FROM Person.Person WHERE BusinessEntityID = 1; PRINT 'The value of @FirstName'; PRINT @FirstName; PRINT 'The value of @LastName'; PRINT @LastName; GOUsing Variables in WHERE and HAVING ClausesYou will often not know ahead of time what values will be needed, so it makes sense to use variables.Example:USE AdventureWorks2012; GO --1 DECLARE @ID INT; SET @ID = 1; SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityID = @ID; GO --2 DECLARE @FirstName NVARCHAR(50); SET @FirstName = N'Ke%'; SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE FirstName LIKE @FirstName ORDER BY BusinessEntityID; GOControl structures The IF… ELSE ConstructUsing IF: 13525508255IF <condition> <statement> IF <condition> BEGIN <statement1> [<statement2>] END0IF <condition> <statement> IF <condition> BEGIN <statement1> [<statement2>] ENDExample:USE AdventureWorks2012; --1 DECLARE @Count INT; SELECT @Count = COUNT(*) FROM Sales.Customer; IF @Count > 500 BEGIN PRINT 'The customer count is over 500.'; END; --2 DECLARE @Name VARCHAR(50); SELECT @Name = FirstName + ' ' + LastName FROM Person.Person WHERE BusinessEntityID = 1; --2.1 IF CHARINDEX('Ken',@Name) > 0 BEGIN PRINT 'The name for BusinessEntityID = 1 contains "Ken"'; END;Using ELSE45322410740IF <condition> <statement1> ELSE <statement2> 00IF <condition> <statement1> ELSE <statement2> Example:--1 DECLARE @Count INT; SELECT @Count = COUNT(*) FROM Sales.Customer; IF @Count < 500 PRINT 'The customer count is less than 500.'; ELSE PRINT 'The customer count is 500 or more.'; 524510346710IF <condition> BEGIN <statement1> [<statement2>] END ELSE BEGIN <statement1> [<statement2>] END00IF <condition> BEGIN <statement1> [<statement2>] END ELSE BEGIN <statement1> [<statement2>] ENDGO Example:USE AdventureWorks2012; --2 DECLARE @Name NVARCHAR(101); SELECT @Name = FirstName + ' ' + LastName FROM Person.Person WHERE BusinessEntityID = 1; --2.1 IF CHARINDEX('Ken', @Name) > 0 BEGIN PRINT 'The name for BusinessEntityID = 1 contains "Ken"'; END; ELSE BEGIN PRINT 'The name for BusinessEntityID = 1 does not contain "Ken"'; PRINT 'The name is ' + @Name; END; --2.2 IF CHARINDEX('Kathi', @Name) > 0 BEGIN PRINT 'The name for BusinessEntityID = 1 contains "Kathi"'; END; ELSE BEGIN PRINT 'The name for BusinessEntityID = 1 does not contain "Kathi"'; PRINT 'The name is ' + @Name; END;IF EXISTScenter554603IF [NOT] EXISTS (SELECT * FROM <TABLE1> [WHERE <condition>]) BEGIN <statement1> [<statement2>] ENDIF [NOT] EXISTS (SELECT * FROM <TABLE1> [WHERE <condition>]) BEGIN <statement1> [<statement2>] ENDCan use IF EXISTS to check for the results of a SELECT statement before executing the statements within the IF block.Example:USE AdventureWorks2012; --1 IF EXISTS (SELECT * FROM Person.Person WHERE BusinessEntityID = 1) BEGIN PRINT 'There is a row with BusinessEntityID = 1'; END ELSE BEGIN PRINT 'There is not a row with BusEntityID = 1'; END; --2 IF NOT EXISTS (SELECT * FROM Person.Person WHERE FirstName = 'Kathi') BEGIN PRINT 'There is not a person with the first name "Kathi".'; END;WHILEUse the WHILE construct to set up loops, or code that executes a number of times. The WHILE loop requires a condition (an expression that evaluates to true or false) to determine when the looping should stop. If you don’t specify a condition, the loop will run until you stop it or some error condition causes it to stop.948774269875WHILE <condition> BEGIN<statement1>[<statement2>]END0WHILE <condition> BEGIN<statement1>[<statement2>]ENDSyntax:Example:DECLARE @Count INT = 1;WHILE @Count < 5 BEGINPRINT @Count;SET @Count += 1;END;Nesting WHILE LoopsJust as you can nest IF blocks, you can create WHILE loops within WHILE loops.106053351267DECLARE @OuterCount INT = 1;DECLARE @InnerCount INT;WHILE @OuterCount < 10 BEGINPRINT 'Outer Loop';SET @InnerCount = 1;WHILE @InnerCount < 5 BEGINPRINT ' Inner Loop';SET @InnerCount += 1;END;SET @OuterCount += 1;END;0DECLARE @OuterCount INT = 1;DECLARE @InnerCount INT;WHILE @OuterCount < 10 BEGINPRINT 'Outer Loop';SET @InnerCount = 1;WHILE @InnerCount < 5 BEGINPRINT ' Inner Loop';SET @InnerCount += 1;END;SET @OuterCount += 1;END;Example: Using CONTINUEThe CONTINUE command causes the loop to continue at the top. In other words, the code following the CONTINUE statement doesn’t execute.457200337448DECLARE @Count INT = 1;WHILE @Count < 10 BEGINPRINT @Count;SET @Count += 1;IF @Count = 3BEGINPRINT 'CONTINUE';CONTINUE;END;PRINT 'Bottom of loop';END;0DECLARE @Count INT = 1;WHILE @Count < 10 BEGINPRINT @Count;SET @Count += 1;IF @Count = 3BEGINPRINT 'CONTINUE';CONTINUE;END;PRINT 'Bottom of loop';END;Example: 621102281688CASE expression WHEN value_1 THEN result_1 WHEN value_2 THEN result_2 ... WHEN value_n THEN result_nELSE resultENDCASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE resultEND0CASE expression WHEN value_1 THEN result_1 WHEN value_2 THEN result_2 ... WHEN value_n THEN result_nELSE resultENDCASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE resultENDCASE StatementSyntax:8255312420USE AdventureWorks2012; GO SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber; GO 0USE AdventureWorks2012; GO SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber; GO Example:-78105310515USE AdventureWorks2012; GO UPDATE HumanResources.Employee SET VacationHours = (CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) 00USE AdventureWorks2012; GO UPDATE HumanResources.Employee SET VacationHours = (CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) Example:Store Procedure Advantages of Stored ProceduresUsing stored procedures provides many advantages over executing large and complexSQL batches from client applications.Modular programmingRestricted, function-based access to tablesReduced network trafficFaster executionEnforced consistencyReduced operator and programmer errorsAutomating complex or sensitive transactionsNOCOUNT ON/NOCOUNT OFFIn advanced Stored Procedures and Script, performance is very important. Using SET NOCOUNT ON and SET NOCOUNT OFF makes the Stored Procedure run faster. SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.Creating Stored ProceduresUse Transact-SQL to create Stored procedure129396458170CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number ] [{@parameter [type_schema_name.] data_type} [VARYING] [= default] [OUT | OUTPUT | [READONLY] ] [,...n ] [WITH <procedure_option> [,...n ] ] [FOR REPLICATION] AS {[BEGIN] sql_statement [;] [...n] [END] } [;] <procedure_option>::= [ENCRYPTION] [RECOMPILE] [EXECUTE AS Clause] CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number ] [{@parameter [type_schema_name.] data_type} [VARYING] [= default] [OUT | OUTPUT | [READONLY] ] [,...n ] [WITH <procedure_option> [,...n ] ] [FOR REPLICATION] AS {[BEGIN] sql_statement [;] [...n] [END] } [;] <procedure_option>::= [ENCRYPTION] [RECOMPILE] [EXECUTE AS Clause] Syntax: Example: --1CREATE PROCEDURE HumanResources.uspGetAllEmployees AS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment; GO--2CREATE PROCEDURE dbo.uspMultipleResults AS SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person; SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer; GO--3CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName; GO --4CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName; GO Stored Procedure with Output ParametersA SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.Example: --1CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END This stored procedure returns a single OUT parameter (managerID), which is an integer, based on the specified IN parameter (employeeID), which is also an integer. The value that is returned in the OUT parameter is the ManagerID based on the EmployeeID that is contained in the HumanResources.Employee table.--2USE AdventureWorks2008R2;GOCREATE PROCEDURE Sales.uspGetEmployeeSalesYTD@SalesPerson nvarchar(50),@SalesYTD money OUTPUTAS SET NOCOUNT ON; SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson;RETURNExecute a Stored ProcedureSyntax:00[{EXEC | EXECUTE}] procedure_name [{value | @variable [OUT | OUTPUT] } ] [,...n ] } [;] [{EXEC | EXECUTE}] procedure_name [{value | @variable [OUT | OUTPUT] } ] [,...n ] } [;] Example:EXECUTE HumanResources.uspGetAllEmployees; Execute a stored procedure output parameterDeclare the variable to receive the output value of the procedure.DECLARE @SalesYTDBySalesPerson money;Execute the procedure specifying a last name for the input parameter and saving the output value in the variable @SalesYTDBySalesPersonEXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;Display the value returned by the procedure.PRINT 'Year-to-date sales for this employee is ' + convert(varchar(10),@SalesYTDBySalesPerson);User-Defined Function The benefits of using user-defined functions in SQL Server are:They allow modular programming.They allow faster execution.They can reduce network traffic.Types of User-Defined FunctionsSQL Server supports three types of user-defined functions:Scalar functionsInline table-valued functionsMultistatement table-valued functionsScalar FunctionsA scalar function is like the standard built-in functions provided with SQL Server. Itreturns a single scalar value that can be used anywhere a constant expression can be used in a query.A scalar function typically takes one or more arguments and returns a value of a specified data type. Every T-SQL function must return a result using the RETURN statement. The value to be returned can be contained in a local variable defined within the function, or the value can be computed in the RETURN statement8255334645CREATE FUNCTION [schema_name.] function_name ([{@parameter_name [AS][ type_schema_name. ] parameter_data_type [= default] [READONLY]} [,...n] ] ) RETURNS return_data_type [WITH <function_option> [,...n]] [AS] BEGIN function_body RETURN scalar_expression END [ ; ] 0CREATE FUNCTION [schema_name.] function_name ([{@parameter_name [AS][ type_schema_name. ] parameter_data_type [= default] [READONLY]} [,...n] ] ) RETURNS return_data_type [WITH <function_option> [,...n]] [AS] BEGIN function_body RETURN scalar_expression END [ ; ] Syntax: Example: ?The function takes one input value, aProductID, and returns a single data value, the aggregated quantity of the specified product in inventory. CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) RETURNS int AS -- Returns the stock level for the product. BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END; The following example uses the?ufnGetInventoryStock?function to return the current inventory quantity for products that have a?ProductModelID between 75 and 80.SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply FROM Production.Product WHERE ProductModelID BETWEEN 75 and 80; Inline table-valued functionAn inline table-valued function specifies only the TABLE keyword in the RETURNS clause,without table definition information.Syntax: 2587956827CREATE FUNCTION [schema_name.]function_name([{@parameter_name [AS] [schema_name.] scalar_datatype[= default]}[,...n]])RETURNS TABLE [WITH{[ENCRYPTION] [,SCHEMABINDING] [,RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] [,EXECUTE_AS_Clause]} ][AS]RETURN [(]select-stmt [ )]00CREATE FUNCTION [schema_name.]function_name([{@parameter_name [AS] [schema_name.] scalar_datatype[= default]}[,...n]])RETURNS TABLE [WITH{[ENCRYPTION] [,SCHEMABINDING] [,RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] [,EXECUTE_AS_Clause]} ][AS]RETURN [(]select-stmt [ )]Example: USE AdventureWorks2008R2;GOCREATE FUNCTION Sales.ufn_CustomerNamesInRegion (@Region nvarchar(50) )RETURNS tableASRETURN ( SELECT DISTINCT s.Name AS Store, a.City FROM Sales.Store AS s INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE sp.Name = @Region );GOExample of calling the function for a specific regionSELECT *FROM Sales.ufn_CustomerNamesInRegion(N'Washington')ORDER BY City;Inline user-defined functions follow these rules:The RETURNS clause contains only the keyword?table. There is no?function_body?delimited by BEGIN and END.The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function.The table-valued function accepts only constants or?@local_variable?argumentsMultistatement table-valued functionsA Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql?statement. Within the create function command you must define the table structure that is being returned.25878277711CREATE FUNCTION [schema_name. ] function_name([{@parameter_name [AS] [schema_name.] scalar_datatype [=default]}[,... n ]])RETURNS @table_variable TABLE({column_definition|table_constraint}[,... n])[WITH{[ENCRYPTION] [,SCHEMABINDING] [,RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] [, EXECUTE_AS_Clause ]} ][AS ]BEGINSQL_StatementsRETURNEND00CREATE FUNCTION [schema_name. ] function_name([{@parameter_name [AS] [schema_name.] scalar_datatype [=default]}[,... n ]])RETURNS @table_variable TABLE({column_definition|table_constraint}[,... n])[WITH{[ENCRYPTION] [,SCHEMABINDING] [,RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] [, EXECUTE_AS_Clause ]} ][AS ]BEGINSQL_StatementsRETURNENDSyntax:Example: --1CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)RETURNS @CustomerOrder TABLE(SaleOrderID INT NOT NULL,CustomerID INT NOT NULL,OrderDate DATETIME NOT NULL,OrderQty INT NOT NULL)ASBEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURNEND--2create?function dbo.f_LotsOfPeople(@lastNameA?as?nvarchar(50), @lastNameB?as?nvarchar(50))returns @ManyPeople?table(PersonID?int, FullName nvarchar(101), PhoneNumber nvarchar(25))asbegin??insert?@ManyPeople (PersonID, FullName, PhoneNumber)??select?ContactID, FirstName +?‘ ‘?+ LastName, Phone??from?Person.Contact??where?LastName?like?(@lastNameA +?‘%’);???insert?@ManyPeople (PersonID, FullName, PhoneNumber)??select?ContactID, FirstName +?‘ ‘?+ LastName, Phone??from?Person.Contact??where?LastName?like?(@lastNameB +?‘%’);??returnendExecute the functionselect?*?from?dbo.f_LotsOfPeople(‘Abe’,?‘Zie’) Modify User-defined FunctionsLimitations and RestrictionsALTER FUNCTION cannot be used to perform any of the following actions:Change a scalar-valued function to a table-valued function, or vice versa.Change an inline function to a multistatement function, or vice versa.Change a Transact-SQL function to a CLR function, or vice-versa.Using SQL Server Management StudioTo modify a user-defined functionClick on the plus sign next to the database that contains the function you wish to modify. Click on the plus sign next to the?Programmability?folder.Click the plus sign next to the folder that contains the function you wish to modify:Table-valued FunctionScalar-valued FunctionAggregate FunctionRight-click the function you want to modify and select?Modify.In the Query Window, make the necessary changes to the ALTER FUNCTION statement.On the?File?menu, click?Savefunction_name.Using T-SQLScalar Functions ALTER FUNCTION [schema_name.] function_name ([{@parameter_name [AS][type_schema_name.] parameter_data_type [= default]}[,...n]]) RETURNS return_data_type[WITH<function_option>[,...n ]] [AS] BEGIN function_body RETURN scalar_expression END [;] Inline Table-valued Functions ALTER FUNCTION [schema_name.] function_name ([{@parameter_name [AS] [type_schema_name.] parameter_data_type[ = default ] }[,...n ]] ) RETURNS TABLE [WITH <function_option> [,...n ] ] [AS] RETURN [( ] select_stmt [ )] [;] Multistatement Table-valued Functions ALTER FUNCTION [schema_name.] function_name ([{ @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] }[ ,...n ]]) RETURNS @return_variable TABLE <table_type_definition> [WITH <function_option> [,...n ] ] [AS] BEGIN function_body RETURN END [;] Delete User-defined Functions0589915DROP FUNCTION [IF EXISTS] { [ schema_name. ] function_name } [ ,...n ] [;]00DROP FUNCTION [IF EXISTS] { [ schema_name. ] function_name } [ ,...n ] [;]To execute DROP FUNCTION, at a minimum, a user must have ALTER permission on the schema to which the function belongs, or CONTROL permission on the function.Cursor In advances scripts, CURSORs may be very useful. A CURSOR works like an advanced WHILE loop which we use to iterate through the records in one or more tables. CURSORS are used mainly in stored procedures, triggers, and SQL scripts.Creating and using a CURSOR includes these steps: Declare SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Associate a SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only. Use the OPEN statement to execute the SELECT statement and populate the cursor. Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other SQL statements can then reference those variables to access the fetched data values. When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor nameDECLARE CURSOR-635230505DECLARE cursor_name CURSOR [LOCAL | GLOBAL ] [FORWARD_ONLY |SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [ ,...n ] ] ] [;] 0DECLARE cursor_name CURSOR [LOCAL | GLOBAL ] [FORWARD_ONLY |SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [ ,...n ] ] ] [;] Syntax: 01925955OPEN {{[GLOBAL] cursor_name} | cursor_variable_name} 0OPEN {{[GLOBAL] cursor_name} | cursor_variable_name} OPEN CURSOR0325755FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM ] {{[GLOBAL] cursor_name} | @cursor_variable_name} [INTO @variable_name [,...n ] ] 0FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM ] {{[GLOBAL] cursor_name} | @cursor_variable_name} [INTO @variable_name [,...n ] ] FETCH (Transact-SQL)-635386080CLOSE {{[GLOBAL] cursor_name} | cursor_variable_name} 0CLOSE {{[GLOBAL] cursor_name} | cursor_variable_name} CLOSE CURSOR-635346710DEALLOCATE {{[GLOBAL]cursor_name}| @cursor_variable_name} 0DEALLOCATE {{[GLOBAL]cursor_name}| @cursor_variable_name} DEALLOCATEExample: use the CUSTOMER tableWe will create a CURSOR that iterate through all the records in the CUSTOMER table and check if the Phone number consists of 8 digits, if not the script will replace the invalid Phone number with the text “Phone number is not valid”DECLARE @CustomerId int, @phone varchar(50) DECLARE db_cursor CURSOR FOR SELECT CustomerId from CUSTOMER OPEN db_cursor FETCH NEXT FROM db_cursor INTO @CustomerId WHILE @@FETCH_STATUS = 0 BEGIN select @phone=Phone from CUSTOMER where CustomerId=@CustomerId if LEN(@phone) < 8update CUSTOMER set Phone='Phone number is not valid' where CustomerId=@CustomerId FETCH NEXT FROM db_cursor INTO @CustomerId END CLOSE db_cursor DEALLOCATE db_cursorThe CUSTOMER table becomesExample: The following script shows how cursors persist until the last name or until the variable referencing them has been deallocated.USE AdventureWorks2012; GO -- Create and open a global named cursor that -- is visible outside the batch. DECLARE abc CURSOR GLOBAL SCROLL FOR SELECT * FROM Sales.SalesPerson; OPEN abc; GO -- Reference the named cursor with a cursor variable. DECLARE @MyCrsrRef1 CURSOR; SET @MyCrsrRef1 = abc; -- Now deallocate the cursor reference. DEALLOCATE @MyCrsrRef1; -- Cursor abc still exists. FETCH NEXT FROM abc; GO -- Reference the named cursor again. DECLARE @MyCrsrRef2 CURSOR; SET @MyCrsrRef2 = abc; -- Now deallocate cursor name abc. DEALLOCATE abc; -- Cursor still exists, referenced by @MyCrsrRef2. FETCH NEXT FROM @MyCrsrRef2; -- Cursor finally is deallocated when last referencing -- variable goes out of scope at the end of the batch. GO -- Create an unnamed cursor. DECLARE @MyCursor CURSOR; SET @MyCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Sales.SalesTerritory; -- The following statement deallocates the cursor -- because no other variables reference it. DEALLOCATE @MyCursor; GO TriggerA database trigger is code that is automatically executed in response to certain events on a particular table in a database. Triggers are powerful objects for maintaining database integrity and consistency. They are able to evaluate data before it has been committed to the database. During this evaluation period, these triggers can perform a myriad of actions, including the following:Compare before and after versions of data.Roll back invalid modifications.Read from other tables, including those in other databases.Modify other tables, including those in other databases.Execute local and remote stored procedures.0334645CREATE TRIGGER [schema_name.]trigger_name ON {table | view} [WITH <dml_trigger_option> [,...n ] ] {FOR | AFTER | INSTEAD OF} { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [WITH APPEND] [NOT FOR REPLICATION] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > } <dml_trigger_option>::= [ENCRYPTION] [EXECUTE AS Clause] <method_specifier> ::=assembly_name.class_name.method_name 0CREATE TRIGGER [schema_name.]trigger_name ON {table | view} [WITH <dml_trigger_option> [,...n ] ] {FOR | AFTER | INSTEAD OF} { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [WITH APPEND] [NOT FOR REPLICATION] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > } <dml_trigger_option>::= [ENCRYPTION] [EXECUTE AS Clause] <method_specifier> ::=assembly_name.class_name.method_name Syntax for creating a TriggerINSERTED and DELETED: Inside triggers we can use two special tables: the DELETED table and the INSERTED tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications. You cannot modify the data in these tables.The DELETED table stores copies of the affected rows during DELETE and UPDATE statements.During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the DELETED table. The INSERTED table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the INSERTED table and the trigger table. The rows in the INSERTED table are copies of the new rows in the trigger table.Example: use the CUSTOMER tableWe will create a TRIGGER that will check if the Phone number is valid when we insert or update data in the CUSTOMER table. The validation check will be very simple, we will check if the Phone number is less than 8 digits (which is normal length in Norway). If the Phone number is less than 8 digits, the following message “Phone Number is not valid” be written in place of the wrong number in the Phone column.CREATE TRIGGER CheckPhoneNumber ON CUSTOMERFOR UPDATE, INSERT AS DECLARE @CustomerId int, @Phone varchar(50), @Message varchar(50) set nocount on select @CustomerId = CustomerId from INSERTED select @Phone = Phone from INSERTEDset @Message = 'Phone Number ' + @Phone + ' is not valid' if len(@Phone) < 8 --Check if Phone Number have less than 8 digits update CUSTOMER set Phone = @Message where CustomerId = @CustomerId set nocount off GOTest the TRIGGER with the following INSERT INTO statementINSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName, AreaCode, Address, Phone) VALUES ('1003', 'Obama', 'Barak', 51, 'Nevada', '4444')We try to update the Phone number to a valid number:Update CUSTOMER set Phone = '44444444' where CustomerNumber = '1003'Types of DML TriggersAfter Trigger (using FOR/AFTER CLAUSE)This trigger fires after SQL Server completes the execution of the action successfully that fired it.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.Instead of Trigger (using INSTEAD OF CLAUSE)This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.Database Backup and Restore Why you should backup?Data loss?can happen in many ways, the most common causes are physical failure of your PC, accidental error, theft or disasters like fire, flood and dropped coffee mugs! It is also common for data to be saved to just one place, like ‘My Documents’ on your PC’s hard drive. Which means that if this data were to be accidentally changed or deleted it would take considerable time and?expense?to restore it.? types of backups SQL Server offers several different types of backups you can use to restore a database to a former state. Each of these backups uses a file or set of files to capture the database state. The files are found outside the SQL Server database and can be stored on media such as tape or hard disk.These backup types are available with SQL Server 2008:Full database backupsDifferential database backupsPartial backupsDifferential partial backupsFile and filegroup backupsCopy-only backupsTransaction log backupsFull database backupsA full database backup is an all-inclusive backup that captures an entire database in one operation. This full backup can be used to restore a database to the state it was in whenthe database backup completed.Using SQL Server Management StudioIn Object Explorer, Expand?Databases, and either select a user database or expand?System Databases?and select a system database.Right-click the database, point to?Tasks, and then click?Back Up. The?Back Up Database?dialog box appears.In the?Database?drop-down list, verify the database nameThe?Recovery model?text box is for reference only. You can perform a database backup for any recovery modelIn the?Backup type?drop-down list, select?Full.For?Backup component, select the?Database?radio button.In the?Destination?section, use the?Back up to?drop-down list to select the backup destination. Click?Add?to add additional backup objects and/or destinations. and the?Select Backup Destination?dialog box will open.Enter the?file name, click OKUsing Transact-SQL1247913378045BACKUP DATABASE?databaseTO?backup_device?[?,...n?][ WITH?with_options?[?,...o?] ] ;0BACKUP DATABASE?databaseTO?backup_device?[?,...n?][ WITH?with_options?[?,...o?] ] ;The basic Transact-SQL syntax for a full database backup is:Example:USE AdventureWorks2012; GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z: \SQLServerBackups\AdventureWorks2012.Bak' WITH FORMAT, MEDIANAME = 'Z_SQLServerBackups', NAME = 'Full Backup of AdventureWorks2012'; GO Differential Database BackupsA differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the?base?of the differential.?Benefits: Creating a differential backups can be very fast compared to creating a full backup.?Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the databaseUnder the full recovery model, using differential backups can reduce the number of log backups that you have to restore.Create a differential database backupExpand?Databases, and depending on the database, either select a user database or expandSystem Databases?and select a system database.Right-click the database, point to?Tasks, and then click?Back Up. The?Back Up Database?dialog box appears.In the?Database?list box, verify the database name. You can optionally select a different database from the list.You can perform a differential backup for any recovery model (full, bulk-logged, or simple).In the?Backup type?list box, select?Differential.619760-121920Using Transact-SQL63500332105BACKUP DATABASE?database_name?TO <backup_device> WITH DIFFERENTIAL0BACKUP DATABASE?database_name?TO <backup_device> WITH DIFFERENTIALSyntax:Example: -- Create a full database backup first. BACKUP DATABASE MyAdvWorks TO MyAdvWorks_1 WITH INIT; GO -- Time elapses. -- Create a differential database backup, appending the backup -- to the backup device containing the full database backup. BACKUP DATABASE MyAdvWorks TO MyAdvWorks_1 WITH DIFFERENTIAL; GO Transaction Log BackupsTransaction log backups capture records written to the transaction log file(s) defined for a database.The BACKUP DATABASE and BACKUP LOG permissions needed are granted by default to members of the?sysadmin?fixed server role, and the db_owner and db_backupoperator fixed database roles.Back up a transaction log using SSMSIn Object Explorer, Expand?Databases, and, depending on the databaseRight-click the database, point to?Tasks, and then click?Back Up. The?Back Up Database?dialog box appears.In the?Database?list box, verify the database name. You can optionally select a different database from the list.Verify that the recovery model is either?FULL?or?BULK_LOGGED.In the?Backup type?list box, select?Transaction Log.0326169BACKUP LOG {database_name | @database_name_var }TO < backup_device > [,...n ][[MIRROR TO < backup_device > [,...n ] ] [ ... next-mirror ]][WITH...[[,] NO_TRUNCATE][[,] {NORECOVERY | STANDBY = undo_file_name } ]BACKUP LOG {database_name | @database_name_var }TO < backup_device > [,...n ][[MIRROR TO < backup_device > [,...n ] ] [ ... next-mirror ]][WITH...[[,] NO_TRUNCATE][[,] {NORECOVERY | STANDBY = undo_file_name } ]Back up a transaction log using T-SQLNO_TRUNCATE: This option prevents the truncation of the transaction log after a backup occurs.NORECOVERY | STANDBY= undo_file_name: The NORECOVERY option causes the tail of the log to be backed up and leaves the database in a RESTORING state, which allows additional transaction logs to be appliedExample:BACKUP LOG AdventureWorks2012 TO MyAdvWorks_FullRM_log1; GO Restoring Databases and Transaction LogsA database restore allows a database or part of a database to be recovered to a state that it was in previously. This state includes the physical structure of the database, configuration options, and data contained in the database.Each database has a recovery model that determines how transactions will be written tothe transaction log. The recovery model you choose has a direct impact on your ability to recover from a media failure. These following three recovery models are available with SQL Server 2008:Full recoveryBulk-loggedSimpleRestore a full database backupThe system administrator restoring the full database backup must be the only person currently using the database to be restored.Using T-SQLExecute the RESTORE DATABASE statement to restore the full database backup, specifying: The name of the database to restore. The backup device from where the full database backup is restored. The NORECOVERY clause if you have a transaction log or differential database backup to apply after restoring the full database backup.Example:RESTORE DATABASE AdventureWorks2008R2 FROM AdventureWorks2008R2_1 WITH NORECOVERYGORESTORE LOG AdventureWorks2008R2 FROM AdventureWorks2008R2_log WITH FILE = 1, WITH NORECOVERYGORESTORE DATABASE AdventureWorks2008R2 WITH RECOVERYGOUsing SSMSExpand Databases. Depending on the database.Right-click the database, point to Tasks, point to Restore, and then click Database, which opens the Restore Database dialog box. On the General page, use the Source section to specify the source and location of the backup sets to restore.Backup media: Select the medium for the restore operation: File, Tape, or Backup Device. The Tape option appears only if a tape drive is mounted on the computer, The Backup Device option appears, only if at least one backup device existsIn the Destination section, the Database box is automatically populated with the name of the database to be restored. Restore a Differential Database BackupUsing SQL Server Management Studio Expand Databases. Depending on the database, select a user Right-click the database, point to Tasks, point to Restore, and then click Database.On the General page, use the Source section to specify the source and location of the backup sets to restore. Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history. In the Destination section, the Database box is automatically populated with the name of the database to be restored. In the Backup sets to restore grid, select the backups through the differential backup that you wish to restore. Using Transact-SQL Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the full database backup that comes before the differential database backup. Specifying:The name of the database to which the differential database backup is applied.The backup device where the differential database backup is restored from.The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored. Otherwise, specify the RECOVERY clause.With the full or bulk-logged recovery model, restoring a differential database backup restores the database to the point at which the differential database backup was completed.Example:-- Assume the database is lost, and restore full database, -- specifying the original full database backup and NORECOVERY, -- which allows subsequent restore operations to proceed. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY; GO -- Now restore the differential database backup, the second backup on -- the MyAdvWorks_1 backup device. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH FILE = 2, RECOVERY; GO Restore a Transaction Log Backup?Using T-SQLThe RESTORE LOG command allows you to restore a transaction log backup.? The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark.When restoring a transaction log you will need exclusive access to the database, which means no other user connections can be using the database.? If the database is in a restoring state this is not an issue, because no one can be using the database.Restore a transaction log backupTo restore a transaction log backup the database need to be in a restoring state.? This means that you would have to restore a full backup and possibly a differential backup as well. 8743950RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY; GO 0RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY; GO Restore multiple transaction log files (NORECOVERY) The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off. As was mentioned above the database needs to be in a restoring state, so this would have already been done for at least one backup file that was restored. Example:RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERYGORESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'GOUsing SSMSIn Object Explorer Expand?Databases, and, depending on the database, either select a user database or expandSystem Databases?and select a system database.Right-click the database, point to?Tasks, point to?Restore, and then click?Transaction Log, which opens the?Restore Transaction Log?dialog box.On the?General?page, in the?Database?list box, select the name of a database. Only databases in the restoring state are listed.To specify the source and location of the backup sets to restore. Database SecurityIntroduction to Database SecuritySecurity is an exercise in creating enough barriers to the system such that the effort involved to attack a system exceeds the benefit derived from the data. In order to defeat a variety of attacks systems, rely on a “defense in depth” approach that places several barriers in the way of an attacker.Three main objectives Secrecy: Information should not be disclosed to unauthorized users.Integrity: Only authorized users should be allowed to modify data.Availability: Authorized users should not be denied accessSQL Server 2008 Security ComponentsPrincipals are the entities that request security to SQL Server resources. They include Windows users, SQL Server users, and database users.Securable are the SQL Server resources to which permissions can be granted.Permissions link principals with securable.Principals Permissions SecurableWindows:GRANT/REVOKE/DENY Server ScopeGroups CREATE LoginDomain Login ALTER EndpointLocal Login DROP DatabaseSQL Server: CONTROL Database ScopeSQL Login CONNECT UserServer Role SELECT RoleDatabase: EXECUTE Application roleUser UPDATE AssemblyDatabase Role DELETE Message TypeApplication Role INSERT RouteREFERENCES ServiceRECEIVE Remote ServiceBindingVIEW DEFINITION Fulltext CatalogTAKE OWNERSHIP CertificateCONTROL Asymmetric KeyVIEW CHANGE TRACKING Symmetric KeyContractSchemaSchema ScopeTableViewFunctionProcedureQueueTypeSynonymAggregateXML SchemaCollectionSecurity MechanismsLoginsLogin identifiers (Ids) are associated with users when they connect to Microsoft? SQL ServerControl access to the SQL Server system UsersA user identifier (ID) identifies a user within a database All permissions and ownership of objects in the database are controlled by the user account RolesAllow you to collect users into a single unit against which you can apply permissions 2095500341630Permissions are applied automatically when the users become members of the role GroupsSQL Server security can be managed with Windows NT/2000 GroupsRight-click on server Select PropertiesIn Select a page ch?n Security Click OK to save your changesSecurity LevelsA user passes through 2 stages of security in SQL ServerAuthentication – validates that a user can connect to a SQL Server instance (Login)Authorization – permissions validation; controls the activities the user is allowed to perform in the SQL Server database (User)Authentication MethodsThe authentication process performs the validation needed to allow a user or client machine to connect to SQL Server. This connection can be granted via a Windows login or SQL Server login.Authentication ModesIntegrated security modeOnly accept logins using Windows NT/ Windows credentialsImplements network-wide single sign-onMixed security modeAllows integrated loginsAllows SQL server based securityMore difficult to secureWindows Authentication ModeWindows Authentication mode validates the account name and password, using information stored in the Windows operating system. A Windows account or group must be established first, and then security can be established for that account in SQL Server. This mode has the advantage of providing a single login account and the capability to leverage domain security features, such as password length and expiration, account locking, encryption, and auditing. Mixed Authentication ModeMixed authentication allows for both Windows authentication and SQL Server authentication. SQL Server authentication is based on a login that is created in SQL Server and lives in SQL Server only. No Windows account is involved with SQL Server authentication. The account and password are established and maintained in SQL Server. SQL Server logins can be created with stronger password enforcement that help better protect the login.Setting the Authentication ModeYou can select the authentication mode when you install SQL Server, and you can change it after the installation. To change the authentication mode after installation, you right click the server node in the Object Explorer and choose the Properties option.Managing PrincipalsPrincipals are the entities that request security to SQL Server resources. The principal is used to describe individuals, groups, and processes that will interact with the SQL Server.SQL Server supports several different types of principals defined at three different levels: The Windows level.The SQL Server level.The database level.Managing SQL Server LoginsSQL Server offers two kinds of logins for authentication:Windows logins: us used by user or group accounts stored in the local Security Accounts Manager (SAM)SQL logins: are used to an individual or entity that does not have a Windows account.Creating Logins in Management StudioFrom Object Explorer, expand your server.Expand the Security folder.Right-click Logins and select ‘‘New Login.’’In the Login–New dialog box, either type the Login name, or click the Search button to browse for a Windows account.If create a SQL Login, then select the ‘‘SQL Server authentication’’ radio button.Creating Logins Using T-SQL683813274210CREATE LOGIN [name] {WITH <options> | FROM <source>}CREATE LOGIN [name] {WITH <options> | FROM <source>}SyntaxOption: PASSWORD= ‘password’HASHED: When a password is created for a SQL login, the password is stored in the database using a one-way hashing algorithmDEFAULT_DATABASE= databaseExample: CREATE LOGIN Bill WITH PASSWORD=‘P@ssw0rd’ HASHEDCREATE LOGIN [AughtEight\Bob] from WindowsAlter LoginsALTER LOGIN name {<status> | WITH <options>}Remove loginsDROP LOGIN nameHelp proceduresSp_helplogins [ 'login' ] sp_helprole [ 'role' ]sp_helprolemember [ [ @rolename = ] 'role' ]Sp_helpsrvrolemember [ 'role' ] sp_helpdbfixedrole ['role' ] Sp_helpuser [ 'security_account' ] Sp_helprolemember [ 'role' ] Sp_helpntgroup [ 'name' ] Managing SQL Server UsersUsers are granted access to database objects, either directly or through membership in one or more database roles. Users are also associated with ownership of objects such as tables, views, and stored procedures.Create a New User and Default SchemaIn Object Explorer, expand Databases.Expand database name.Expand Security.Right-click Users and select ‘‘New User.’’Type username in the User Name box.Type Loginname in the ‘‘Login name’’ box, or select her login using the ‘‘...’’ button.Select schema in the ‘‘Default schema’’ box.651593400050Click OK.Create a New User by using T-SQLUsers based on logins in masterCREATE USER user_name [{FOR|FROM} LOGIN login_name] [WITH DEFAULT_SCHEMA = schema_name] Users that authenticate at the database CREATE USER {windows_principal[WITH<options_list>[,...]]|username WITH PASSWORD ='password'[,<options_list>[,...]}Assigning Login Acounts as Database UsersThrough Stored procedureSp_grantdbaccess 'login', 'name_in_db' Sp_revokedbaccess 'name' Managing Database RolesSQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. There are two types: 46990006159500Fixed server roles Fixed database roles have a fixed set of permissions assigned to themSQL Server defines eight server-level roles that are available to simplify management for SQL logins.Create Server Roles using SSMSRight -Click on Roles and choose New Database RoleThrough Stored proceduresp_addrole 'role' , 'owner' ] sp_addsrvrolemember [ @loginame=]‘login’ , [ @rolename=]‘role’Sp_droprole ‘role' sp_addrolemember 'role' , 'security_account' sp_droprolemember 'role' , 'security_account' Example --1 --The following statements must be run by an administrator to add--a login and database user with no explicit permissions grantedCREATE LOGIN [TestUser] WITH PASSWORD=N’pw’,DEFAULT_DATABASE=[master],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFUSE [AdventureWorks2008]GOCREATE USER [TestUser] FOR LOGIN [TestUser]GO--2--the following statement fails when executed by the TestUser which has no explicit permissions defined in the AdventureWorks2008 databaseselect top 5 * from person.personUPDATE person.person SET suffix = ‘Jr.’WHERE FirstName = ‘Ken’--3 --The following statement is run by an administrator to:--1)add a new TestDbRole with permission to UPDATE--2)grant UPDATE permission on the Person.person table--3)add the TestUser to the TestDbRole database roleUSE [AdventureWorks2008]GOCREATE ROLE [TestDbRole] AUTHORIZATION [dbo]GRANT UPDATE ON [Person].[Person] TO [TestDbRole]GRANT SELECT ON [Person].[Person] TO [TestDbRole]EXEC sp_addrolemember N’TestDbRole’, N’TestUser’--3--the following statements now succeed when executed--by the TestUser because the role that it was added to has SELECT and UPDATE permission on that tableselect top 5 * from person.personUPDATE person.person SET suffix = ‘Jr.’WHERE ContactID = 1--4the following select fails because ‘testdbrole’ does not permit SELECT on any table but person.personselect * from person.ContactType--5The following statement is run by an administrator to add the TestDbRole database role to the db_datareader fixed-database roleEXEC sp_addrolemember N’db_datareader’, N’TestDbRole’GO--6Finally, the testuser can update the Person.person table and select from any other table in the databaseselect * from person.ContactTypeManaging SecurablesSecurables are the entities in SQL Server on which permissions can be granted. In other words, principals (for example, users or logins) obtain permission to securables.SQL Server 2008 SecurablesServer DatabaseSchemaLogins User TableEndpoints Role ViewDatabases Application role FunctionAssembly ProcedureMessage Type QueueRoute TypeService SynonymRemote Service Binding AggregateFulltext Catalog XML Schema CollectionCertificateAsymmetric KeySymmetric KeyContractSchemaManaging PermissionsPermissions are the security mechanisms that tie principals (for example, logins) to securables (for example, tables). Permissions controls the activities a user is allowed to perform in a databaseLogins are mapped to a user accountA separate user account is required in each databaseUser accounts are mapped to database roles, which have their own set of permissionsPermissions are applied to the objects (tables, views, stored procs, etc.) in the databaseWith SQL Server 2008, permissions can be applied at a granular level that provides a great deal of flexibility and control.SQL Server’s 3 permission verbs:Grant gives a rightDeny explicitly denies a rightRevoke takes away an existing grant or denyGRANTAllows you to grant the right to perform an action or interact with an object in a specific way.Syntax: GRANT privilege_name?ON object_name?TO {user_name |PUBLIC |role_name}[WITH GRANT OPTION];?privilege_name: the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.object_name:?the name of an database object like TABLE, VIEW, ...user_name: the name of the user to whom an access right is being granted.PUBLIC:?is used to grant access rights to all users.ROLES:?are a set of privileges grouped together.WITH GRANT OPTION: allows a user to grant access rights to other users.Example:?GRANT SELECT ON employee TO user1;REVOKE?The revoke command removes user access rights or privileges to the database objectsSyntax:3257550REVOKE privilege_name?ON object_name?FROM {user_name |PUBLIC |role_name}?0REVOKE privilege_name?ON object_name?FROM {user_name |PUBLIC |role_name}?Example:Revoke a SELECT privilege on employee table from user1REVOKE SELECT ON employee FROM user1Revoke a CREATE TABLE privilege from testing ROLEREVOKE CREATE TABLE FROM testingDENYThis command prevent the user from performing the action.453225347980DENY <permissions>[ON <object>]TO <user/role>DENY <permissions>[ON <object>]TO <user/role>Syntax:Example:Use pubsDENY select, insert, update ON titles TO faculty--Grant permissions to create a table to a user named ChrisGRANT CREATE TABLE TO Chris--Grant ALL permissions on a stored procedure to a database role named TestDBRoleGRANT ALL ON dbo.uspGetBillOfMaterials TO TestDBRole--DENY UPDATE permission on the Customer table to user named LauraDENY UPDATE ON OBJECT::sales.customer TO Laura--REVOKE UPDATE permissions on the Customer table to user named Laura.REVOKE UPDATE ON OBJECT::sales.customer TO LauraTransaction Management and the Transaction LogDefining TransactionsA transaction is one or more SQL statements that must be completed as a whole or, in other words, as a single logical unit of work. Transactions provide a way of collecting and associating multiple actions into a single all-or-nothing multiple-operation action. All operations within the transaction must be fully completed or not performed at all.Transaction ensuring the highest level of integrity and consistency within a database.If a transaction is successful, all of the data modifications made during the transaction are committed If a transaction encounters errors and must be rolled back, then all of the data modifications are erased.A transaction has four special characteristics, known as the ACID properties:Atomicity: Associated modifications are an all-or-nothing proposition; either all are done or none are done.Consistency: After a transaction finishes, all data is in the state it should be in, all internal structures are correct, and everything accurately reflects the transaction that has occurred.Isolation: One transaction cannot interfere with the processes of another transaction.Durability: After the transaction has finished, all changes made are permanent.Managing TransactionsCan carry out transaction processing with Microsoft SQL Server in three ways:AutoCommit: Every T-SQL statement is its own transaction and automatically commits when it finishes. This is the default mode in which SQL Server operates.Explicit: This approach provides programmatic control of the transaction, using the BEGIN TRAN and COMMIT/ROLLBACK TRAN/WORK commands.Implicit: In this mode, when you issue certain SQL commands, SQL Server automatically starts a transaction. You must finish the transaction by explicitly issuing the COMMIT/ROLLBACK TRAN/WORK commands.AutoCommit TransactionsAutoCommit is the default transaction mode for SQL Server. Each individual T-SQLcommand automatically commits or rolls back its work at the end of its execution. EachSQL statement is considered to be its own transaction, with begin and end control pointsimplied.Example: UPDATE accountSET balance = balance + 1000WHERE account_no = “123456789”If an error is present in the execution of the statement, the action is undone (that is,rolled back); if no errors occur, the action is completed, and the changes are saved.Explicit TransactionsThe Transaction defined explicitly. The beginning of a transaction explicitly with a BEGIN TRAN (or BEGIN TRANSACTION) statement, the end of a transaction explicitly with a COMMIT TRAN statement if you want to confirm it and with a ROLLBACK TRAN (or ROLLBACK TRANSACTION) statement if you do not want to confirm it (that is, if you want to undo its changes).294005368935BEGIN TRAN[SACTION] [<transaction_name>]< T-SQL statements>< @@TRANCOUNT = 1>[SAVE TRANSACTION <savepoint name>]…<T-SQL statements> IF <some error condition>ROLLBACK TRANSACTION [<transaction_name> | <savepoint name>]ELSE COMMIT TRANSACTION-- @@TRANCOUNT = 00BEGIN TRAN[SACTION] [<transaction_name>]< T-SQL statements>< @@TRANCOUNT = 1>[SAVE TRANSACTION <savepoint name>]…<T-SQL statements> IF <some error condition>ROLLBACK TRANSACTION [<transaction_name> | <savepoint name>]ELSE COMMIT TRANSACTION-- @@TRANCOUNT = 0Syntax: BEGIN TRAN: user is unable or do not want to commit the transaction, this is the point to which all database activity will be rolled MIT TRAN: COMMIT TRAN[SACTION] [<transaction name>|<@transaction variable>]The committing of a transaction is the end of a completed transaction. At the point that user issue the COMMIT TRAN, the transaction is considered durable.The effect of the transaction is now permanent and will last even if you have a system failure.ROLLBACK TRAN: to the beginning of the transaction, and erase all data modifications made from the start of the transaction. It also frees resources held by the transaction.ROLLBACK TRAN[SACTION] [<transaction name>|<save point name>| <@transaction variable>|<@savepoint variable>]206734517221SAVE TRAN[SACTION] [<save point name>| <@savepoint variable>]SAVE TRAN[SACTION] [<save point name>| <@savepoint variable>]SAVE TRAN: The savepoint is a location to which a transaction can return if part of the transaction is conditionally canceled.Example: select * from [Order Details] where ProductID in(3,7)Begin Tranuse NorthwindUpdate Productsset UnitsInStock =UnitsInStock+20where ProductName like 'A%'Update [Order Details]set Discount =Discount+0.25 where ProductID in (3,7)SAVE TRAN tran1Using TRY…CACTH or IF with TRANSACTION BEGIN TRY BEGIN TRAN -- Code for your transaction COMMIT TRAN END TRY BEGIN CATCH -- output an error message ROLLBACK TRAN END CATCHExample: Begin trybegin tranUpdate products set UnitsInstock =100 where ProductID update [order details]set quantity =Quantity +100where ProductID in(3,7)Commit tranend trybegin catchrollback tranraiserror ('Transaction Error',16,1)returnend catchNote: To enable implicit transactions for a connection in SQL Server 2008, you need to enable the IMPLICIT_TRANSACTIONS session setting using the following command:SET IMPLICIT_TRANSACTIONS ONAfter this option is turned on, transactions are implicitly started, if they are not already in progress, whenever any of the following commands are executed:ALTER TABLEINSERTCREATEOPENDELETEREVOKEDROPSELECTFETCHTRUNCATE TABLEGRANTUPDATETransaction States and Additional OperationsActive: the transaction stays in this state while it is executingPartially committed: after the final statement has been executed.Failed: after the discovery that normal execution can no longer proceed.Aborted: after the transaction has been rolled back and the database restored to its state prior to the start of the transaction. Two options after it has been aborted:Restart the transaction – only if no internal logical errorKill the transactionCommitted: after successful completion.Read and Write: Specify read or write operations on the database items that are executed as part of a transactionread_item(X): Reads a database item named X into a program variable also named X.Find the address of the disk block that contains item XCopy that disk block into a buffer in the main memoryCopy item X from the buffer to the program variable namedwrite_item(X): Writes the value of program variable X into the database item named X.Find the address of the disk block that contains item XCopy that disk block into a buffer in the main memoryCopy item X from the program variable named X into its current location in the buffer store the updated block in the buffer back to disk Implementation of Atomicity and DurabilitySchedulesSchedules: sequences that indicate the chronological order in which instructions of concurrent transactions are executed.A schedule for a set of transactions must consist of all instructions of those transactionsMust preserve the order in which the instructions appear in each individual transaction.SerializabilityBasic Assumption: Each transaction preserves database consistency.Thus serial execution of a set of transactions preserves database consistency.A schedule is serializable if it is equivalent to a serial schedule. Different forms of schedule equivalence give rise to the notions of:Conflict serializabilityView serializabilityInstructions li and lj of transactions Ti and Tj respectively, Conflict if and only if there exists some item Q accessed by both li and lj, and at least one of these instructions wrote Q.Ii = read(Q), Ij = read(Q) (li and lj don’t conflict)Ii = read(Q), lj = write(Q) (li and lj conflict)Ii = write(Q), lj = read(Q) (li and lj conflict)Ii = write(Q), lj = write(Q) (li and lj conflict)A conflict between li and lj forces a (logical) temporal order between them. If li and lj are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule.If a schedule S can be transformed into a schedule S? by a series of swaps of non-conflicting instructions, we say that S and S? are conflict equivalent.Implicit TransactionsAutoCommit transactions and explicit user-defined transactions, which are the default transaction mode in SQL Server 2008. The ANSI-92 SQL standard states that any data retrieval or modification statement issued should implicitly, begin a multistatement transaction that remains in effect until an explicit ROLLBACK orCOMMIT statement is issued.1351722350520SET IMPLICIT_TRANSACTIONS ONSET IMPLICIT_TRANSACTIONS ONTo enable the IMPLICIT_TRANSACTIONS session, use command:After this option is turned on, transactions are implicitly started, if they are not already in progress, whenever any of the following commands are executed:ALTER TABLEINSERTCREATEOPENDELETEREVOKEDROPSELECTFETCHTRUNCATE TABLEGRANTUPDATELockingWhat is LocksLocks are acquired automatically as needed to support a transaction based on “isolation levels”COMMIT and ROLLBACK statements release all locksThe main role of locking is to provide the isolation that transactions need. Isolation ensures that individual transactions don’t interfere with one another, that a given transaction does not read or modify the data being modified by another transaction. In addition, the isolation that locking provides helps ensure consistency within transactions. Without locking, consistent transaction processing is impossible. Transactions are logical units of work that rely on a constant state of data, almost a “snapshot in time” of what they are modifying, to guarantee their successful completion. Types of locksBasic locksShared (LS)Exclusive (LX)Special situation locksIntentUpdateSchemaBulk updateLock CompatibilityLocks may or may not be compatible with other LocksLSLXLStruefalseLXfalsefalseExamplesShared locks are compatible with all locks except exclusiveExclusive locks are not compatible with any other locksUpdate locks are compatible only with shared locksTransaction Isolation Levels in SQL ServerIsolation levels determine the extent to which data being accessed or modified in one transaction is protected from changes to the data by other transactions. In theory, each transaction should be fully isolated from other transactions. However, in practice, for practical and performance reasons, this might not always be the case. In a concurrent environment in the absence of locking and isolation, the following four scenarios can happen:Lost update: no isolation is provided to a transaction from other transactions. Multiple transactions can read the same copy of data and modify it. The last transaction to modify the data set prevails, and the changes by all other transactions are lost.Dirty reads: one transaction can read data that is being modified by other transactions. The data read by the first transaction is inconsistent because the other transaction might choose to roll back the changes.Nonrepeatable reads: a transaction reads the data twice, but before the second read occurs, another transaction modifies the data; therefore, the values read by the first read are different from those of the second read. Phantom reads: similar to nonrepeatable reads. However, instead of the actual rows that were read changing before the transaction is complete, additional rows are added to the table, resulting in a different set of rows being read the second time. ANSI SQL-92 defines four standards for transaction isolation:Read uncommittedThis is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.Read CommittedThis is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying.Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.Repeatable ReadThis is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. Any transactions that try to modify these records are forced to wait for the read transaction to complete.SerializableThis isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finishedSnapshotThis provides the same guarantees as serializable. Snapshot transactions do not block other transactions from writing data.Once snapshot isolation is enabled, updated row versions for each transaction are maintained in?tempdb.Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions.When all transactions that started before the changes are complete the previous row version is removed from tempdb.This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.Locking timeout Limits time waiting for a locked resource SET LOCK_TIMEOUTThe Lock ManagerThe Lock Manager is responsible for deciding the appropriate lock type (for example, shared, exclusive, update) and the appropriate granularity of locks (for example, row, page, table), according to the type of operation being performed and the amount of data being affected, manages compatibility between lock types attempting to access the same resources, resolves deadlocks, and escalates locks to a higher level, if necessary. The Lock Manager manages locks for both shared data and internal system resources. For shared data, the Lock Manager manages row locks, page locks, and table locks on tables, as well as data pages, text pages, and leaf-level index pages. Locks are used to ensure the logical consistency of data, whereas latches are used to ensure the physical consistency of the data and data structures.Monitoring Lock Activity in SQL ServerTo monitor the performance of a system, you need to keep track of locking activity in SQL Server. The following are the most commonly used methods to do so:Querying the sys.dm_tran_locks dynamic management view directlyViewing locking activity with SQL Server ProfilerMonitoring locks with Performance MonitorQuerying the sys.dm_tran_locks ViewThe sys.dm_tran_locks dynamic management view returns information about all the locks currently granted or waiting to be granted in SQL Server. This view provides no historical information; rather, the data in this view corresponds to live Lock Manager information. This data can change at any time for subsequent queries of the view as locks are acquired and released.The information returned by the view can be divided into two main groups: resource information and lock request information. The resource information describes the resource on which the lock request is being made, and the request information provides details on the lock request itself. Viewing Locking Activity with SQL Server ProfilerAnother tool to help you monitor locking activity in SQL Server 2008 is SQL Server Profiler. SQL Server Profiler provides a number of lock events that you can capture in a trace. The trace information can be viewed in real-time or saved to a file or database table for further analysis at a later date. Saving the information to a table allows you to run different reports on the information to help in the analysis.SQL Profiler provides the following lock events that can be captured in a trace:Lock:Acquired—Indicates when a lock on a resource, such as a data page or row, has been acquired.Lock:Cancel—Indicates when the acquisition of a lock on a resource has been canceled (for example, as the result of a deadlock).Lock:Deadlock—Indicates when two or more concurrent processes have deadlocked with each other.Lock:Deadlock Chain—Provides the information for each of the events leading up to a deadlock.. Lock:Escalation—Indicates when a lower-level lock has been converted to a higher-level lock (for example, when page-level locks are escalated to table-level locks).Lock:Released—Indicates that a process has released a previously acquired lock on a resource.Lock:Timeout—Indicates that a lock request that is waiting on a resource has timed out due to another transaction holding a blocking lock.Lock:Timeout (timeout >0)—Is similar to Lock:Timeout but does not include any events where the lock timeout is 0 seconds.Deadlock Graph—Generates an XML description of a deadlock.SQL Server Profiler also provides a number of data values to display for the events being monitored. You might find the following data columns useful when monitoring locking activity:spid—The process ID of the process that generated the event.EventClass—The type of event being captured.Mode—For lock monitoring, the type of lock involved in the captured event.ObjectID—The ID of the object involved in the locking event—that is, the object that the lock is associated with.DatabaseID—The ID of the database involved in the locking eventTextData—The query that generated the lock event.LoginName—The login name associated with the process.ApplicationName—The name of the application generating the lock event.Monitoring Locks with Performance MonitorThe counters that belong to the SQLServer:Locks object are as follows:Average Wait Time—This counter represents the average wait time (in milliseconds) for each lock request. A high value is an indication of locking contention that could be affecting performance of concurrent processes.Lock Requests/sec—This counter represents the total number of new locks and lock conversion requests made per second. A high value for this counter is not necessarily a cause for alarm; it might simply indicate a system with a high number of concurrent users.Lock Timeouts (timeout > 0)/sec—This counter is similar to the LockTimeouts/sec counter but does not include NOWAIT lock requests that time out immediately.Lock Timeouts/sec—This counter represents the total number of lock timeouts per second that occur for lock requests on a resource that cannot be granted before the lock timeout interval is exceeded. Lock Wait Time—This counter represents the cumulative wait time for each lock request. It is given in milliseconds. A high value here indicates that you might have long-running or inefficient transactions that are causing blocking and locking contention.Lock Waits/sec—This counter represents the total number of lock requests generated per second for which a process had to wait before a lock request on a resource was granted. A high value might indicate inefficient or long-running transactions or a poor database design that is causing a large number of transactions to block one another.Number of Deadlocks/sec—This number represents the total number of lock requests per second that resulted in deadlocks.SQL Server Lock TypesLocking is handled automatically in SQL Server. The Lock Manager chooses the type of lock, based on the type of transaction (such as SELECT, INSERT, UPDATE, or DELETE). LockManager uses the following types of locks:Shared locksUpdate locksExclusive locksIntent locksSchema locksBulk update locksShared locks: A shared lock is, by definition, not exclusive. Theoretically, an unlimited number of shared locks can be held on a resource at any given time. In addition, shared locks are unique in that, by default, a process locks a resource only for the duration of the read on the resource (row, page, or table).Update locks: Update locks are used to lock rows or pages that a user process intends to modify. When a transaction tries to update a row, it must first read the row to ensure that it is modifying the appropriate record. If the transaction were to put a shared lock on the resource initially, it would eventually need to get an exclusive lock on the resource to modify the record and prevent any other transaction from modifying the same record. The problem is that this could lead to deadlocks in an environment in which multiple transactions are trying to modify data on the same resource at the same time.Exclusive locks: An exclusive lock on a resource makes sure no other transaction can interfere with the data locked by the transaction that is holding the exclusive lock. SQL Server releases the exclusive lock at the end of the transactionIntent locks: Intent locks do not really constitute a locking mode; rather, they act as a mechanism to indicate at a higher level of granularity the types of locks held at a lower level. The types of intent locks mirror the lock types previously discussed: shared intent locks, exclusive intent locks, and update intent locks.Schema locks: schema locks provide isolation for the schema of database objects, such as tables, views, and indexes within a transaction.The Lock Manager uses two types of schema locks:Schema stability locks—When a transaction is referencing either an index or a data page, SQL Server places a schema stability lock on the object. This ensures that no other process can modify the schema of an object—such as dropping an index or dropping or altering a stored procedure or table—while other processes are still referencing the object.Schema modification locks—When a process needs to modify the structure of an object (for example, alter the table, recompile a stored procedure), the Lock Manager places a schema modification lock on the object. For the duration of this lock, no other transaction can reference the object until the changes are complete and committed.Bulk update locks: A bulk update lock is a special type of lock used only when bulk copying data into a table using the bcp utility or the BULK INSERT command. This special lock is used for these operations only when either the TABLOCK hint is specified to bcp or the BULK INSERT command or when the table lock on bulk load table option has been set for the table. Bulk update locks allow multiple bulk copy processes to bulk copy data into the same table in parallel, while preventing other processes that are not bulk copying data from accessing the table. If there are any indexes on the table, or any other processes already holding locks on the table, a bulk update lock cannot be granted.SQL Server Lock GranularityLock granularity is essentially the amount of data locked as part of a query or update to provide complete isolation and serialization for the transaction. The Lock Manager needs to balance the concurrent access to resources versus the overhead of maintaining a large For example, the smaller the lock size, the greater the number of concurrent users who can access the same table at the same time but the greater the overhead in maintaining those locks. The greater the lock size, the less overhead required to manage the locks, but concurrency is also less.SQL Server provides the following locking levels:DATABASE—Whenever a SQL Server process is using a database other than master, the Lock Manager grants a database lock to the process. These are always shared locks, and they are used to keep track of when a database is in use to prevent another process from dropping the database, setting the database offline, or restoring the database. Note that because master and tempdb cannot be dropped or set offline, database locks are not required on those databases.FILE—A file lock is a lock acquired on a database file.EXTENT—Extent locks are used for locking extents, usually only during space allocation and deallocation. An extent consists of eight contiguous data or index pages. Extent locks can be shared extent or exclusive extent locks.ALLOCATION_UNIT—This type of lock is acquired on a database allocation unit.TABLE—With this type of lock, the entire table, inclusive of data and indexes, is locked. Examples of when table-level locks may be acquired include selecting all rows from a large table at the serializable level and performing unqualified updates or deletes on a table.Heap or B-Tree (HOBT)—This type of lock is acquired on a heap of data pages or on the B-Tree structure of an index.PAGE—With a page lock, the entire page, consisting of 8KB of data or index information, is locked. Page-level locks might be acquired when all rows on a page need to be read or when page-level maintenance needs to be performed, such as updating page pointers after a page split.Row ID (RID)—With an RID lock, a single row within a page is locked. RID locks are acquired whenever efficient and possible to do so in an effort to provide maximum concurrent access to the resource.KEY—SQL Server uses two types of key locks. The one that is used depends on the locking isolation level of the current session. For transactions that run in Read Committed or Repeatable Read isolation modes, SQL Server locks the actual index keys associated with the rows being accessed. (If a clustered index is on the table, the data rows are the leaf level of the index. You see key locks instead of row locks on those rows.) When in Serializable Read isolation mode, SQL Server prevents phantom rows by locking a range of key values so that no new rows can be inserted into the range. These are referred to as key-range locks. Key-range locks associated with a particular key value lock that key and the previous one in the index to indicate that all values between them are locked. Key-range locks are covered in more detail in the next section.METADATA—This type of lock is acquired on system catalog information . APPLICATION—An application lock allows users to essentially define their own locks by specifying a name for the resource, a lock mode, an owner, and a timeout interval. Using application locks is discussed later in this chapter, in the section “Using Application Locks.”Serialization and Key-Range LockingSQL Server provides serialization (Isolation Level 3) through the SET TRANSACTION ISOLATION SERIALIZABLE command. One of the isolations provided by this isolation level is the prevention against phantom reads. Preventing phantom reads means that the recordset that a query obtains within a transaction must return the same result set when it is run multiple times within the same transaction.Key-Range Locking for a Range SearchIn a scenario that involves key-range locking for a range search, SQL Server places locks on the index pages for the range of data covered in the WHERE clause of the query. (For a clustered index, the rows would be the actual data rows in the table.) Because the range is locked, no other transaction can insert new rows that fall within the range. In Figure 37.7, for example, transaction A has issued the following SELECT statement:Key-Range Locking When Searching Nonexistent RowsIn a scenario that involves key-range locking when searching nonexistent rows, if a transaction is trying to delete or retrieve a row that does not exist in the database, it still should not find any rows at a later stage in the same transaction with the same query. For example, in Figure 37.8, Transaction A is trying to fetch a nonexistent row with the key value 7200 using the following query:Using Application LocksThe SQL Server Lock Manager knows nothing about the object or the structure of the object it is locking. The Lock Manager simply checks whether two processes are trying to obtain incompatible locks on the same resource. If so, blocking occurs. SQL Server allows you to extend the resources that can be locked beyond the ones automaticallyprovided. You can define your own custom locking resources and let the Lock Manager control the access to those resources as it would for any resource in a database. This essentially allows you to choose to lock anything you want. These user-defined lock resources are called application locks. To define an application lock, you use thesp_getapplock stored procedure and specify a name for the resource you are locking, a mode, an optional lock owner, and an optional lock timeout interval.The syntax forsp_getapplock is as follows:sp_getapplock [ @Resource = ] ‘resource_name’,[ @LockMode = ] ‘lock_mode’[ , [ @LockOwner = ] { ’transaction’ | ‘session’ } ][ , [ @LockTimeout = ] ‘value’ ][ , [ @DbPrincipal = ] ‘database_principal’ ]Locks can also be explicitly released at any time, with thesp_releaseapplock stored procedure. The syntax for sp_releaseapplock is as follows:sp_releaseapplock [ @Resource = ] ‘resource_name’[ , [ @LockOwner = ] { ‘transaction’ | ‘session’ }][ , [ @DbPrincipal = ] ‘database_principal’ ]Procedure sp_releaseapplock to release the application lock called ’QueueLock’ to let another session acquire the application lock:exec sp_getapplock ‘QueueLock’, ‘Exclusive’, ‘session’exec get_next_item_from_queueexec sp_releaseapplock ‘QueueLock’, ‘session’Viewing Application Locks Using sys.dm_tran_locksselect str(request_session_id, 4,0) as spid,convert (varchar(12), db_name(resource_database_id)) As db_name,case when resource_database_id = db_id() and resource_type = ‘OBJECT’then convert(char(6), object_name(resource_Associated_Entity_id))else convert(char(6), resource_Associated_Entity_id)end as object,convert(varchar(12), resource_type) as resource_type,convert(varchar(4), request_mode) as mode,convert(varchar(24), resource_description) as resource_desc,convert(varchar(6), request_status) as statusfrom sys.dm_tran_locksorder by request_session_id, 3 descgoIndex LockingThere is the opportunity for greater locking contention in index pages than in data pages. Contention at the root page of the index is the highest because the root is the starting point for all searches via the index. Contention usually decreases as you move down the various levels of the B-tree, but it is still higher than contention at the data page level due to the typically greater number of index rows per index page than data rows per data page.If locking contention in the index becomes an issue, you can use ALTER INDEX to manage the locking behavior at the index level. The syntax of this command is as follows:ALTER INDEX { index_name | ALL } ON object{ ALLOW_ROW_LOCKS = { ON | OFF }| ALLOW_PAGE_LOCKS = { ON | OFF }Lock CompatibilityIf a process has already locked a resource, the granting of lock requests by other transactions on the same resource is governed by the lock compatibility matrix within SQL Server. Table 37.3 shows the lock compatibility matrix for the locks most commonly acquired by the SQL Server Lock Manager, indicating which lock types are compatible and which lock types are incompatible when requested on the same resource.Locking Contention and DeadlocksIn the grand scheme of things, the most likely culprits of SQL Server application performance problems are typically poorly written queries, poor database and index design, and locking contention. Whereas the first two problems result in poor application performance, regardless of the number of users on the system, locking contention becomes more of a performance problem as the number of users increases. It is further compounded by increasingly complex or long-running transactions.Locking contention occurs when a transaction requests a lock type on a resource that is incompatible with an existing lock type on the resource. By default, the process waits indefinitely for the lock resource to become available. Locking contention is noticed in the client application through the apparent lack of response from SQL Server.Figure 37.9 demonstrates an example of locking contention. Process 1 has initiated a transaction and acquired an exclusive lock on page 1:325. Before Process 1 can acquire the lock that it needs on page 1:341 to complete its transaction, Process 2 acquires an exclusive lock on page 1:341. Until Process 2 commits or rolls back its transaction and releases the lock on Page 1:341, the lock continues to be held. Because this is not a deadlock scenario (which is covered in the “Deadlocks” section, later in this chapter), by default, SQL Server takes no action. Process 1 simply waits indefinitely.Identifying Locking ContentionWhen a client application appears to freeze after submitting a query, this is often due to locking contention. To identify locking contention between processes, you can use the SSMS Activity Monitor, as discussed earlier in this chapter, in the “Monitoring Lock Activity in SQL Server” section; use the sp_who2 stored procedure; or query the sys.dm_tran_locks system catalog view. Figure 37.10 shows an example of a blocking lock as viewed in the SSMS Activity Monitor.Setting the Lock Timeout IntervalIf you do not want a process to wait indefinitely for a lock to become available, SQL Server allows you to set a lock timeout interval by using the SET LOCK_TIMEOUT command. You specify the timeout interval in milliseconds. For example, if you want your processes to wait only 5 seconds (that is, 5,000 milliseconds) for a lock to become available, you execute the following command in the session:SET LOCK_TIMEOUT 5000If your process requests a lock resource that cannot be granted within 5 seconds, the statement is aborted, and you get the following error message:Server: Msg 1222, Level 16, State 52, Line 1Lock request time out period exceeded.To examine the current LOCK_TIMEOUT setting, you can query the system function@@lock_timeout:select @@lock_timeoutgo-----------5000Minimizing Locking ContentionSome general guidelines to follow to minimize locking contention include the following:Keep transactions as short and concise as possible. The shorter the period of time locks are held, the less chance for lock contention. Keep commands that are not essential to the unit of work being managed by the transaction (for example, assignment selects, retrieval of updated or inserted rows) outside the transaction.Keep statements that comprise a transaction in a single batch to eliminate unnecessary delays caused by network input/output (I/O) between the initial BEGIN TRAN statement and the subsequent COMMIT TRAN commands.Consider coding transactions entirely within stored procedures. Stored procedures typically run faster than commands executed from a batch. In addition, because they are server resident, stored procedures reduce the amount of network I/O that occurs during execution of the transaction, resulting in faster completion of the mit updates in cursors frequently and as soon as possible. Cursor processing is much slower than set-oriented processing and causes locks to be held longer.DeadlocksA deadlock occurs when two processes are each waiting for a locked resource that the other process currently holds. Neither process can move forward until it receives the requested lock on the resource, and neither process can release the lock it is currently holding until it can receive the requested lock. Essentially, neither process can move forward until the other one completes, and neither one can complete until it can move forward.Two primary types of deadlocks can occur in SQL Server:283845014033500Cycle deadlocks: A cycle deadlock occurs when two processes acquire locks on different resources, and then each needs to acquire a lock on the resource that the other process has. Figure 37.11 demonstrates an example of a cycle deadlock.Conversion deadlocks: A conversion deadlock occurs when two or more processes each hold a shared lock on the same resource within a transaction and each wants to promote the shared lock to an exclusive 2449002276lock, but neither can do so until the other releases the shared lock. An example of a conversion deadlock is shown in Figure 37.12.Tài li?u tham kh?o [1] S. Alapati, Expert Oracle Database 11g, Administration, Apress, 2009.[2] M. B. R. Diana Lorentz, SQL Language Reference 11g Release 2, Oracle, 2014.[3] J. J. M. Arena, Oracle and PL/SQL Recipes: A Problem-Solution Approach, Apress, 2010.-Tài li?u tham kh?o:[1] S. K. Gupta, Oracle Advanced PL/SQL Developer Professional,, Packt Publishing, 2012. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download