Hide Notes 

Slide 1: Microsoft® SQL Server Training
Slide 2: What is SQL Server ? • A full blown relational database management system. • Supports it’s own flavour of SQL called Transact SQL. • A RDBMS supported by rich set of management tools. • A largely self managing database. • A RDBMS tighly integrated to Windows.
Slide 3: Comparison Oracle® To SQL Server • Schema design and user access: – – – – Users connect to an instance via a login. A database is connected to via a user. Instead of schemas SQL Server has databases. All database objects in SQL Server come under the ownership of the database owner role (dbo).
Slide 4: Comparing Oracle® To SQL Server • Memory and process architecture – Memory is not broken down into configurable pools as it is in Oracle®. – Internal workings are not as readily available as they are with Oracle® (v$ and fixed views). – On Windows both Oracle® and SQL Server instances run as services.
Slide 5: Comparing Oracle® To SQL Server • Storage – – – – – SQL Server does not use tablespaces instead it uses file groups. The SQL Server equivalent of a temporary tablespace is tempdb. SQL Server has no concept of rollback segments. You do not specify a storage clause when creating objects. All storage in SQL Server is managed using bitmaps, cf locally managed tablespaces and automatic segment manager in Oracle® 9i.
Slide 6: Comparing Oracle® To SQL Server • Redo Logs Vs Transaction Logs – The SQL Server equivalent of redo logs are transaction logs. – Transaction logs are not used in a round robin style like redo logs they grow indefinitely until they are either backed up or truncated. – A transaction log will fill up or grow until it is either backed up or truncated. – Both Oracle® and SQL Server have log shipping capabilities.
Slide 7: Comparing Oracle® To SQL Server • Table and Indexes – SQL Server only supports tables, indexes and clustered indexes. – Oracle® also supports partioned indexes and tables, clusters, hash clusters, bitmapped indexes, bitmap join indexes and reverse key indexes.
Slide 8: Comparing Oracle® To SQL Server • Oracle® SQL Vs Transact SQL – Transact SQL supports the ANSI join syntax which is not available in Oracle® until 9i. – DDL statements can be written straight into Transact SQL stored procedure code. – Both languages support global temporary tables. Continued . . .
Slide 9: Comparing Oracle® To SQL Server • Oracle® SQL Vs Transact SQL – Both languages support dynamic SQL. – Transact SQL has no native file handling capability, instead you have to use bcp via xp_cmdshell. – Transact SQL is case sensitive if a case sensitive collation has been used when SQL Server has been installed.
Slide 10: Comparing Oracle® To SQL Server • Command Line Utilities – Svrmgrl – SQLPLUS – SQLLDR -> isql & DBCC -> isql -> BCP (bi directional)
Slide 11: SQL Server Tool Set • We will focus on each tool in more detail later. • Enterprise Manager: tool for administering SQL Server (cf Oracle® Enterprise Manager). • Query Analyzer: tool for running ad-hoc queries and executing store procedures (cf SQL Work Sheet). • Client Network Utility: for creating aliases with which to access remote instances (cf Net 8 Assistant). • Import and Export Data: data transformation wizard (cf Oracle® Warehouse Builder). • Profiler: tool for tracing current SQL activity (no direct Oracle® comparison).
Slide 12: Enterprise Manager
Slide 13: What Can Be Done Through Enterprise Manager ? • • • • • • • • Database creation and deletion. Database backup and recovery. Modification of instance properties. DTS management. Maintenance plan administration. Replication and security. Launch other apps: Query Analyzer and Profiler. And much more . . .
Slide 14: Getting Started With Enterprise Manager 1. In Enterprise Manager right click on “SQL Server Group” and select “New SQL Server Registration”. Hit ‘Next’ on the “Register SQL Server Wizard”. Select the server you wish to register from the “Available Servers” pane and hit next. 2. 3. Continued . . .
Slide 15: Getting Started With Enterprise Manager 1. Select the authentication mode (usually SQL Server Authentication), hit next. Select an existing group and hit next (new groups can be easily created). Hit finish and then close once the SQL Server instance is successfully registered. 2. 3.
Slide 16: Administering Databases In Enterprise Manager Expand the databases tree and all the databases associated with the instance will appear. Some of these are created when SQL Server is installed.
Slide 17: SQL Server Default Databases • Dropping any one of these will damage SQL Server: – tempdb: Database used for sort operations caused by sorts and joins and order by statements on queries (cf Oracle® temporary tablespace). – master: Database containing system wide information such as error messages , login information, system stored procedures and information regarding connected and linked servers (cf Oracle® system tablespace). – model: Used as a template for the creation of new databases (no direct Oracle® comparison).
Slide 18: SQL Server Default Databases • msdb: is also a system database and supports the SQL Server agent service, including information stored about jobs, alerts, events, replication and backup and restore history. • northwind: Database provided for learning (cf scott/tiger schema in Oracle®).
Slide 19: Databases Components & Attributes • In Enterprise Manager right click on a database and select properties, a dialogue will appear with six tabs. The Collation name is similar to the character set of an Oracle database. We always use Latin1_General_BIN. The Collation needs to be the same as that for the default databases, otherwise problems will be encountered later on. The collation of the default databases is determined when SQL Server is installed. • • • •
Slide 20: Databases Components & Attributes • Each database has to have at least one datafile. • Data files can be deleted and modified from this dialog. • Note the ability to allow data files to grow on demand.
Slide 21: Databases Components & Attributes • Each database has to have at least one transaction log. • Transaction logs can be deleted and modified from this dialog. • Note the ability to allow data files to grow on demand.
Slide 22: Databases Components & Attributes • A filegroup is similar to a tablespace. • Allow objects to be stored in separate files. • We will not go into them in great detail because we don’t use them that much.
Slide 23: Databases Components & Attributes • Restricted access: options are used when repairing objects with torn pages, e.g. DBCC CHECKTABLE. Read only: users cannot modify data. Recovery model & torn page detection covered later. Auto update statistics: rebuilds out of date statistics needs by a query automatically. Auto create statistics: create missing statistics needed by a query automatically. • • • •
Slide 24: Database Recovery Models • Full – Allows all types of backup: full, transaction log, differential, file / file group & point in time recovery. – Is the recommended model for full protection.
Slide 25: Database Recovery Models • Simple – Recommended if you want to recover your database as fast as possible. – Suitable when all you care about is restoring to your last back. – Transaction log backups aren’t available and the transaction log is periodically erased.
Slide 26: Database Recovery Models • Bulk Logged – Information that is normally written to the transaction log by statements such as SELECT INTO, BULK INSERT, CREATE INDEX does not go to the transaction log. – Point in time recovery cannot be used. – A restore can only be performed using the last clean backup.
Slide 27: Torn Pages • What is a page ? – Basic unit of storage which SQL Server uses when reading from and writing to the database (cf Oracle® block). • What is a torn page ? – A page is deemed to be ‘torn’ when it in an inconsistent state. – Usually occurs when the IO operation involving a page being written to disk is prematurely aborted due to a hardware or power failure.
Slide 28: Torn Pages • What can be done in the event of a torn page if the database is suspect ?. – If the database is marked as suspect it will have to be recovered using the the latest decent backup. • What can be done in the event of a torn page if the database can be connected to ? – Put the database in single user mode: ALTER DATABASE database_name SINGLE USER – Repair the table: DBCC CHECKTABLE (‘table_name',REPAIR_REBUILD) – Put the database backinto multi user mode: ALTER DATABASE database_name SET multi_user
Slide 29: Workshop: Creating A Training Database Each student will need to perform this exercise one at a time. 1. 2. 3. 4. Start Enterprise manager and login as sa. Expand the databases tree. Right click on the training database and select Backup Database. Hit the add button and select a file the database is to be backed upto, call the the file <yourname>.bak and then hit OK. Continued . . .
Slide 30: Workshop: Creating A Training Database 1. Hit OK on the main backup directory and the backup will begin. NOTE : each student will have to perform this exercise one at a time. Hit OK on the backup completion dialog. Right click on the Databases folder and select restore database. Change the name of the database to your name and hit Ok. 2. 3. 4.
Slide 31: Query Analyzer
Slide 32: Query Analyzer Overview • Login into Query Analyzer by hitting the new query icon (top left of the screen). • Multiple queries can be present in one or more query windows on the right hand side of the screen. • Different output formats including a queries execution plan can be obtained from the query pull down menu.
Slide 33: Query Analyzer Overview • To run a query against a particular databases: – Select the database from the pulldown menu at the top of the screen. Or . . . – Prefix you SQL statement by: use database_name go • Query analyzer has an object browser, this can be made viewable and hidden from the tools pulldown menu. • Stored procedures can be run as follows: – exec storedprocedure_name go
Slide 34: Workshop: Creating A Login 1. 2. 3. – Start Query Analyzer and connect to asp_awt as sa and sysadmin. Set the current database to auditworks. Create yourself a new login according to the following spec: login name is your first name and first letter of your surname, the username should be the same – username same as your login name – role of db_owner HINT: lookup sp_addlogin using books online. Continued . . .
Slide 35: Workshop: Creating A Login 1. Set the current database to mw and add a user to this database using the previous spec. 2. Set the password for newly created login HINT: lookup sp_password using books online.
Slide 36: Viewing Execution Plans • Select “show execution plan” from the query pull down menu. • Edit and run your query. • The execution plan will appear in the execution plan tab, refer to the example on the right.
Slide 37: Viewing Execution Plans • To drill down and view table access statistics click on a table in the execution plan. • Note the execution plan is accessed from right to left. • Query tuning will be covered in the Transact SQL section later.
Slide 38: Client Network Utility (CNU)
Slide 39: What is the Client Network Utility ? • Allows the creation of network protocols connections to specified servers. • Displays information about the current network libraries installed on the server (cf Oracle protocol adapters). • Displays the DB library version currently installed on the system and sets the defaults for DB library options.
Slide 40: Creating An Alias In The CNU • Invoke the CNU. • Hit the Alias tab. • Click on add. • Enter the name you wish to give the alias in the “Server alias” box. • Hit the ‘TCP/IP’ network library radio button. • Enter the name of the server in the “Server name” text box, this will usually be the same as the server on which the instance resides. • Hit Ok.
Slide 41: Data Transformation Services (DTS)
Slide 42: DTS Overview • Accessible from the SQL Server program group or tools menu in Enterprise manager. • Powerful tool which comes free for transferring data and database objects from one database to another. • Programs can be built using DTS and ran on a scheduled basis, these are called packages. • Tool comes free with SQL Server. • The use of DTS will be illustrated in a simple example. • DTS is an application in it’s own right, whole books and even web sites are dedicated to the topic.
Slide 43: A Working Example Of DTS 1. Start the Import/Export Wizard • Invoke the DTS Import / Export Wizard. • Hit Next
Slide 44: A Working Example Of DTS 2. Specify The Destination For The Data • • Choose the data source for the target. Select name of the SQL Server instance from the server pulldown LOV. Select “Use SQL Server Authentication” radio button. Enter sa and the password for sa in the username and password boxes respectively. Select the database in which the data resides from the Database LOV. Hit Next. • • • •
Slide 45: A Working Example Of DTS 3. Specify The Transfer Mechanism • • Choose the data source for the destination. Select name of the SQL Server instance from the server pulldown LOV. Select “Use SQL Server Authentication” radio button. Enter sa and the password for sa in the username and password boxes respectively. Select the destination database for the data from the Database LOV. Hit Next. • • • •
Slide 46: A Working Example Of DTS 4. Specify The Data/Tables/Views To Be Copied • Tick the tables you wish to copy. • Alternatively you can specify all the tables in the database using select all.
Slide 47: A Working Example Of DTS 5. Specify The Name Of The Destination Tables • The name of the destination tables does not necessarily have to match the name of the source table. • Click the name of the destination and edit the table name as you wish. • Hit Next.
Slide 48: A Working Example Of DTS 6. Specify The Data Transform Schedule • Check the run immediately tick box. • Hit Next.
Slide 49: A Working Example Of DTS 7. Complete The Wizard • Hit Finish to initiate the data transfer. • The transfer will begin . . . • The rest is quite straight forward.
Slide 50: The DTS Package Designer • To invoke this expand the data transformation services folder, right click and select new.
Slide 51: DTS Package Designer • Following data sources and targets are allowed: – FTP – email – Active X script – BCP – DTS package execution – Message queue – Copy SQL Server object task
Slide 52: DTS Package Designer • Following tasks are allowed: – SQL Server – Access – Excel – dBase 5 – HTML – Paradox 5 – Text file – Oracle – Microsoft Data Link
Slide 53: Query Profiler
Slide 54: Query Profiler • Invoked from either the SQL Server program group or the tools menu from Enterprise Manager. • Primary SQL Server performance monitoring and tuning tool. • Statistics collections gathered via Query Profiler are known as traces. • A saved trace can fed into the Index Tuning Wizard.
Slide 55: Gathering A Trace • • Select new -> trace from the file menu. Specify a SQL Server, username and password for connecting to the server. Select a template and template file name. SQLProfilerStandard should suffice most of the time. Hit save to file and choose a file that the trace will be saved in. • •
Slide 56: Gathering A Trace • The trace can be written into a table however, this will cause an overhead in the database the trace is being written to and therefore be intrusive. Specify a stop time, the trace will be gathered until either the stop time is reached or it is stopped manually. Hit run. • •
Slide 57: Gathering A Trace • In the events tab specify the events (statistics) you wish to collect. • The events recommended in the dialog on the right are recommended. • Move onto the data columns tab.
Slide 58: Gathering A Trace • In the data column tab specify which columns you wish to appear in the trace. • Hit filters.
Slide 59: Gathering A Trace • In the filters tab it is recommend that a filter is placed on CPU time, e.g. CPU greater than or equal to 10. • Database name is also a recommended filter. • Hit Run and the trace will begin.
Slide 60: Gathering A Trace • Once running the traces events and can be changed at any time by pausing it (pause video button) and using the properties icon. • The stop video button can be used to stop the trace at any time.
Slide 61: What Can Be Done With The Trace File ? • The trace file can be fed into the index tuning wizard. • In SQL Server 2000 this takes an inordinately long amount of time to run. • Therefore the best bet is to copy the poorly performing SQL into query analyzer and generate an execution plan for it from there.
Slide 62: Transact SQL (T-SQL) • Dual table – There is no dual table in T-SQL – In Oracle® you would: SELECT 1 FROM dual – With T-SQL you would write: SELECT 1
Slide 63: ANSI 92 Inner Join Syntax • In Oracle®: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a, publishers p WHERE a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 64: ANSI 92 Join Syntax Inner Joins • ANSI 92 Syntax: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a INNER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 65: ANSI 92 Outer Join Syntax • Left outer join in Oracle®: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a, publishers p WHERE a.city (+)= p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 66: ANSI 92 Outer Join Syntax • Left outer join in ANSI 92 Syntax SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 67: ANSI 92 Outer Join Syntax • Right outer join in Oracle®: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a, publishers p WHERE a.city =(+) p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 68: ANSI 92 Outer Join Syntax • Right outer join using ANSI 92 syntax: SELECT a.au_fname, a.au_lname, p.pub_name authors a RIGHT OUTER JOIN publishers p a.city = p.city p.pub_name ASC, a.au_lname ASC, a.au_fname ASC FROM ON ORDER BY
Slide 69: Transact SQL ANSI 92 Join Syntax • Join resulting in a cartesian product: – In Oracle® you would write (prior to 9i): SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a, publishers p – With T-SQL you would write (SQL 92 syntax): SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a CROSS JOIN publishers p
Slide 70: ANSI 92 Outer Join Syntax • Full outer join in Oracle®: SELECT FROM WHERE ORDER BY UNION SELECT FROM WHERE ORDER BY a.au_fname, a.au_lname, p.pub_name authors a, publishers p a.city =(+) p.city p.pub_name ASC, a.au_lname ASC, a.au_fname ASC a.au_fname, a.au_lname, p.pub_name authors a, publishers p a.city (+)= p.city p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 71: ANSI 92 Outer Join Syntax • Full outer join using ANSI 92 syntax: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Slide 72: ANSI 92 Outer Join Syntax • Simple example SELECT th.transaction_date, SUM(tl.gross_line_amount) FROM transaction_line tl INNER JOIN transaction_header th ON th.transaction_id = tl.transaction_id INNER JOIN store_audit_status sas ON th.transaction_date = sas.sales_date GROUP BY th.transaction_date
Slide 73: Relational Operators • Oracle® supports: – MINUS – INTERSECT – UNION • SQL Server only supports UNION.
Slide 74: Using Conventional Joins In UPDATE Statements • Update statement using a subquery: – UPDATE titles SET price = price * 2 WHERE pub_id IN (SELECT pub_id FROM publishers WHERE pub_name = 'New Moon Books') Update statement using a conventional join (ANSI 92 syntax): – UPDATE titles SET price = price * 2 FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books' • • Update statement using an in-line view: – UPDATE authors SET state = 'ZZ' FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1 WHERE authors.au_id = t1.au_id
Slide 75: Using Conventional Joins In DELETE Statements • Delete statement using a sub query: – DELETE sales WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'business') Delete statement using a join (ANSI 92 Syntax): – sales FROM sales INNER JOIN titles ON sales.title_id = titles.title_id AND type = 'business' Delete statement using an inline view: – DELETE authors FROM (SELECT TOP 10 * FROM authors) AS t1 WHERE authors.au_id = t1.au_id • •
Slide 76: T-SQL Case Statement • Syntax – Simple CASE function: CASE input_expression     WHEN when_expression THEN result_expression         [ ...n ]     [         ELSE else_result_expression     ] END – Searched CASE function: CASE     WHEN Boolean_expression THEN result_expression         [ ...n ]     [         ELSE else_result_expression     ] END
Slide 77: T-SQL Case Statement • An example – Simple CASE function: SELECT       DISTINCT b.store_no,   CASE    WHEN MAX(a.entry_date_time) < DATEADD(MINUTE, -20, GETDATE()) THEN 'Zero trxns received in last 20 Minutes!'   ELSE CONVERT(VARCHAR(11),a.transaction_date,103)+ ' Normal' END updated FROM   transaction_header a, check_store b WHERE            a.store_no = b.store_no GROUP BY b.store_no,          a.transaction_date ORDER BY updated desc GO
Slide 78: T-SQL & Create Table As Select • In Oracle® – CREATE TABLE foo AS SELECT * FROM bar • Using Transact SQL – SELECT * INTO foo FROM bar
Slide 79: T-SQL Compute Statement • Compute clause goes at the end of the statement • E.g. SELECT store_no, tender_total FROM auditworks..transaction_header COMPUTE SUM(tender_total) • Compute returns two results sets, one result set for each grouping and one result for each computed aggregation. • The next few slides will illustrate this.
Slide 80: Computing An Aggregate Across The Result Set
Slide 81: Computing An Aggregate Across A Grouping
Slide 82: Limiting Result Sets Using TOP & PERCENT • Allows results set to be limited by the top n where n is an integer or the top n percent. • Syntax TOP n [ PERCENT ] • Example: – SELECT TOP 10 PERCENT transaction_date, tender_total FROM transaction_header ORDER BY tender_total DESC
Slide 83: T-SQL Transactional Model • • Any changes made by a SQL statement are automatically committed unless transaction blocks are used. Syntax for start of block BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable     [ WITH MARK [ 'description' ] ] ] Block is terminated with either a rollback or commit. • Example: BEGIN TRAN T1 UPDATE mytab SET col1 = col1 + 1 SELECT * FROM mytab ROLLBACK SELECT * FROM mytab GO
Slide 84: Other Difference Between Oracle® & T-SQL • Concatenation operator in T-SQL is ‘+’. • Use GETDATE instead of sysdate. • Use the CAST function to convert data from one type to another. • Use ‘..’ to specify the database in which an object resides, e.g. SELECT transaction_date FROM auditworks..transaction_header
Slide 85: Transact SQL Batches • What is a batch ? – A set of statements which are executed together. – e.g.: USE pubs SELECT au_id, au_lname FROM authors SELECT pub_id, pub_name FROM publishers INSERT INTO publishers VALUES (‘9998’, ‘SAMS Publishing’, ‘Seattle’, ‘WA’, ‘USA’) GO • If a statement fails SQL server will move onto the next one.
Slide 86: Transact SQL Batches • Some statements cannot be used in batches: – CREATE RULE – CREATE TRIGGER – CREATE PROCEDURE – CREATE DEFAULT – CREATE VIEW • You cannot • #add columns to a table and then use them in the same batch. • SET statements take effect immediately, except QUOTED IDENTIFIERS and ANSI NULLS.
Slide 87: SQL Server IDENTITY • SQL Server has no concept of sequences. • Instead it uses identities. • An identity can be associated with a column of a table such that it acts like a counter column in Access. • An example is provided on the next set of slides.
Slide 88: SQL Server IDENTITY
Slide 89: SQL Server IDENTITY • The last IDENTITY generated by a BCP, SELECT INTO or INSERT can be retrieved by @@IDENTITY. • @@IDENTITY will return NULL if no rows are affected. • If any triggers are fired, @@IDENTITY will return the latest identity value generated by the triggers. • See SCOPE_IDENTITY and IDENT_CURRENT also.
Slide 90: Filtering Out Duplicate Data Using Identities • Simple table with a single INT Column. • Three rows: 1, 2, 2. • Add an IDENTITY column to the table to give each row a unique identifier: ALTER TABLE mytab ADD [serial_no] [numeric](14, 0) IDENTITY (1, 1) NOT NULL
Slide 91: Filtering Out Duplicate Data Using Identities • Delete the duplicate data: DELETE mytab FROM (SELECT col1, MAX(serial_no) max_serial_no FROM mytab GROUP BY col1) AS t1 WHERE mytab.col1 = t1.col1 AND serial_no < t1.max_serial_no • Drop the serial_no column: ALTER TABLE mytab DROP COLUMN serial_no
Slide 92: Transact SQL Hints • There are three types – Tables hints: specify an operation to take place on a table. – Query hints: specify an operation that affects the whole statement. – Join hints (can only be specified when using ANSI 92 syntax). • If a hint is wrong in Oracle® it will be ignored. • If a hint is wrong in SQL Server, the statement will not parse.
Slide 93: Table Hints Syntax [ FROM { < table_source > } [ ,...n ] ] < table_source > ::=     table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]     | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]     | rowset_function [ [ AS ] table_alias ]     | user_defined_function [ [ AS ] table_alias ]     | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]     | < joined_table > < joined_table > ::=     < table_source > < join_type > < table_source > ON < search_condition >     | < table_source > CROSS JOIN < table_source >     | [ ( ] < joined_table > [ ) ] < join_type > ::=     [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]     [ < join_hint > ]     JOIN
Slide 94: Available Table Hints Option INDEX = FASTFIRSTROW HOLDLOCK | SERIALIZABLE | REPEATABLEREAD | READCOMMITTED | READUNCOMMITTED | NOLOCK ROWLOCK | PAGLOCK | TABLOCK | TABLOCKX | NOLOCK READPAST UPDLOCK Description Instructs SQL Server to use the specified indexes for a table. Has the same effect as specifying the FAST 1 query hint. Specifies the isolation level for the table. Default Setting Chosen by SQL Server No such optimization Defaults to transaction isolation level. Specifies locking granularity for a table. Chosen by SQL Server Skips locked rows altogether Takes update locks instead of shared locks. Cannot be used with NOLOCK or XLOCK. Takes an exclusive lock that will be held until the end of the transaction. Cannot be used with NOLOCK or UPDLOCK. Waits for locked rows Take shared locks. XLOCK Chosen by SQL Server
Slide 95: Join Hints Syntax [ FROM { < table_source > } [ ,...n ] ] < table_source > ::=     table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]     | view_name [ [ AS ] table_alias ]     | rowset_function [ [ AS ] table_alias ]     | OPENXML     | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]     | < joined_table > < joined_table > ::=      < table_source > < join_type > < table_source > ON < search_condition >     | < table_source > CROSS JOIN < table_source >     | < joined_table > < join_type > ::=     [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]     [ < join_hint > ]     JOIN
Slide 96: Available Join Hints Option Description Default Setting LOOP | HASH | MERGE | REMOTE Specifies the strategy to use when joining rows from two tables. Chosen by SQL Server.
Slide 97: Query Hints SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] Syntax SELECT statement ::=     < query_expression >     [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }         [ ,...n ]    ]     [ COMPUTE         { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]         [ BY expression [ ,...n ] ]     ]     [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }             [ , XMLDATA ]             [ , ELEMENTS ]             [ , BINARY base64 ]         } ]     [ OPTION ( < query_hint > [ ,...n ]) ]
Slide 98: Available Query Hints Option { HASH | ORDER } GROUP Description Specifies whether hashing or ordering is used to compute GROUP BY and COMPUTE aggregations Specifies the strategy to use for all UNION operations within the query. Optimizes the query for the retrieval of the specified number of rows. Joins the tables in the order they appear in the FROM clause. Create a plan accomodates maximum potential row sizes. Default Setting Chosen by SQL Server { MERGE | HASH | CONCAT } UNION FAST integer Chosen by SQL Server No such optimisation FORCE ORDER ROBUST PLAN Chosen by SQL Server Chosen by SQL Server
Slide 99: Workshop: Removing Duplicate Data From A Table 1. Using the test database you created for yourself earlier create a copy of the transaction_header table, call the table anything you like. HINT: right click on the object in the Query Analyzer object Browser 2. Duplicate the data in your new table by SELECTing everything from the table and reinterting it (INSERT SELECT). 3. Create a temporary table which is a copy of the new table.
Slide 100: Workshop: Removing Duplicate Data From A Table 1. Load the temporary table with the data from the new table (INSERT SELECT). 2. Truncate the table you created in step 1. 3. INSERT the contents of the temporary table into the table you just trucated. HINT: you will need to specify SELECT DISTINCT * to make sure the data is unique.
Slide 101: Workshop: Write A Query Using An Outer Join in ANSI 92 Syntax • The ti_transaction_header_error table in the mw database contains rows for each transaction header row that has been rejected by the standard import. • Write a query which returns the store dates from the transaction header table (in the auditworks database) for which there are no import errors.
Slide 102: Workshop: Write A Query Using An Outer Join in ANSI 92 Syntax HINTS • OUTER JOIN on store_no and transaction_date. • The store_no and transaction_date from ti_transaction_header_error table which have no corresponding entries in the ti_transaction_header_error table will be null.
Slide 103: Workshop: Using Table Level Hints 1. Execute the following query against the auditworks database in Query Analyzer, specify that an execution plan should be produced: SELECT DISTINCT b.store_no, CASE WHEN MAX(a.entry_date_time) < DATEADD(MINUTE, -20, GETDATE()) THEN 'Zero trxns received in last 20 Minutes!' ELSE CONVERT(VARCHAR(11),a.transaction_date,103)+ ' - Normal' END updated FROM auditworks..transaction_header a, auditworks..store_audit_status b WHERE a.store_no = b.store_no GROUP BY b.store_no, a.transaction_date ORDER BY updated desc GO
Slide 104: Workshop: Using Table Level Hints 1. 2. 3. 4. 5. Make a note of the access method for store_audit_status. Incorporate a table level hint (INDEX(0)) to force a full table scan on store_audit_status. Run the query again with show execution plan. Look at the access method for transaction header to verify that it has changed. Although the access path used on store_audit_status has changed, why is this not a FTS ?
Slide 105: Workshop: Query Hints Use a query hint to make SQL Server perform the group by using hashing on the statement below, verify that the hint has taken effect by looking at the statements execution plan. SELECT store_no, SUM(tender_total) FROM auditworks..transaction_header GROUP BY store_no GO
Slide 106: Workshop: Join Hints Modify the query below to use a MERGE join. Use Query Analyzer to verify that the execution plan has changed to reflect this: SELECT ath.store_no, ath.transaction_date, COUNT(ath.transaction_id) FROM auditworks..transaction_header ath INNER JOIN mw..transaction_header mth ON mth.store_no = ath.store_no AND mth.transaction_date = ath.transaction_date GROUP BY ath.store_no, ath.transaction_date ORDER BY ath.transaction_date GO
Slide 107: T-SQL Numeric Data Types • Bigint: Whole number with a range of -9223372036854775808 to 9223372036854775807. • Int: Whole number with a range of -2,147,483,648 to 2,147,483,647. • Smallint:Whole number range of -32,768 to 32,767.
Slide 108: T-SQL Numeric Data Types • Tinyint: Integer data, range 0 to 255. • Bit: Integer which cam be 1 or 0. • Decimal: Fixed precision numeric 10^38 +1 to 10^38 –1. • Numeric: functionally equivalent to decimal.
Slide 109: T-SQL Time & Decimal Data Types • Float: -1.79E + 308 through 1.79E + 308. • Real: Floating precision number data from -3.40E + 38 through 3.40E + 38. • Datetime: Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. • Smalldatetime: Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
Slide 110: T-SQL Time Data Types • Money: Monetary data, -2^63 (-922,337,203,685,477.5808) to 2^63 - 1 (+922,337,203,685,477.5807), accuracy to a tenthousandth of a monetary unit. • Smallmoney: Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Slide 111: T-SQL Character Data Types • char: Fixed-length non-Unicode character data with a maximum length of 8,000 characters. • varchar: Variable-length non-Unicode data with a maximum of 8,000 characters. • text: Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
Slide 112: T-SQL Explicit Cursors • Declare cursor: DECLARE c_get_dups CURSOR FOR SELECT sales_date, store_no, register_no FROM #register_to_verify OPEN c_get_dups • Open cursor:
Slide 113: T-SQL Explicit Cursors • Fetch from the cursor: WHILE 1=1 BEGIN FETCH c_get_dups INTO @sales_date, @store_no, @register_no IF @@fetch_status <> 0 BEGIN CLOSE c_get_dups DEALLOCATE c_get_dups DROP TABLE #register_to_verify BREAK END END
Slide 114: DDL Can Be Incorporated Straight Into Code • An example using a temporary table: ALTER PROC CCHUK_verify_duplicates_$sp AS DECLARE @register_no smallint, @store_no int, @sales_date smalldatetime CREATE TABLE #register_to_verify ( sales_date smalldatetime, store_no int, register_no smallint)
Slide 115: Dynamic Transact SQL • A simple example: Create Procedure GenericTableSelect @TableName VarChar(100) AS Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT * FROM ' SELECT @SQL = @SQL + @TableName Exec ( @SQL) GO
Slide 116: Inline T-SQL Functions: An Example CREATE FUNCTION whichContinent (@Country nvarchar(15)) RETURNS varchar(30) AS BEGIN declare @return varchar(30) select @return = case @Country when 'Argentina' then 'South America' when 'Belgium' then 'Europe' when 'Brazil' then 'South America' when 'Canada' then 'North America' when 'Denmark' then 'Europe' when 'Finland' then 'Europe' when 'France' then 'Europe' else 'Unknown' end return @return end select dbo.whichContinent('Belgium') go
Slide 117: Workshop: Inline Functions You require a function for use in Transact SQL which has similar behaviour to the RPAD function available in Oracle®, this is the specification for the function: • It takes three arguments: – String (String 1) – Integer – String (String 2) The function returns the first the original string with the right hand side of it padded to the length indicated by the integer using the characters in string 2. Example The following example right-pads a name with the letters "ab" until it is 12 characters long: SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL; RPAD example ----------------- MORRISONabab • •
Slide 118: T-SQL System Functions • SQL Server provided a number of built in functions, these are: – – – – – – – – – Configuration functions, e.g. @@DATEFIRST Cursor functions, e.g. @@FETCH_STATUS Date and time functions, e.g. DATEADD Metadata functions, e.g. COL_LENGTH Security functions, e.g. fn_trace_getfilterinfo String functions, e.g. REPLACE System functions, e.g. @@TRANCOUNT System Statistical functions, e.g. @@CPU_BUSY Text and image functions, e.g. PATINDEX • All of these are described in the online documentation. • To run the function that start with ‘@@’ simply SELECT from them.
Slide 119: Useful Extended Stored Procedures • xp_sendmail {[@recipients =] 'recipients [;...n]'}     [,[@message =] 'message']     [,[@query =] 'query']     [,[@attachments =] 'attachments [;...n]']     [,[@copy_recipients =] 'copy_recipients [;...n]'     [,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'     [,[@subject =] 'subject']     [,[@type =] 'type']     [,[@attach_results =] 'attach_value']     [,[@no_output =] 'output_value']     [,[@no_header =] 'header_value']     [,[@width =] width]     [,[@separator =] 'separator']     [,[@echo_error =] 'echo_value']     [,[@set_user =] 'user']     [,[@dbuse =] 'database'] Requires a MAPI compliant client and SQL Mail to be configured on the client PC. Example: exec master..xp_sendmail ‘A.N. Other’, ‘Hello world’ • •
Slide 120: Useful Extended Stored Procedures • xp_cmdshell {'command_string'} [, no_output] • Example: exec master..xp_cmdshell ‘dir *.exe’ • Powerful when combined with MKS Toolkit (Microsoft services for UNIX).
Slide 121: Error Handling In Stored Procedures • Error code of last executed statement is stored in @@error. • T-SQL with SQL Server 2000 has no exception handling ability. • Exceptions are introduced in SQL Server 2003 (Yukon). • Until then GOTOs can be used to jump to the equivalent of an exception handling block.
Slide 122: Clustered Indexes • Is a table in which the data is stored in the order determined by the “cluster key”. • Essentially an index in which whole rows are stored in leaf nodes. • There can only be one clustered index per table. • PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.
Slide 123: When To Use Clustered Indexes • Columns that contain a large number of distinct values. • Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=. • Columns that are accessed sequentially. • Queries that return large result sets. • Columns frequently accessed by queries with join or GROUP BY clauses; which are typically foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because it is already sorted. This improves query performance. • OLTP-type applications requiring fast single row lookups, typically by means of the primary key. Create a clustered index on the primary key.
Slide 124: When Not To Use Clustered Indexes • Columns that undergo frequent changes resulting in entire rows moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile. • Wide keys The key values from the clustered index are used by all nonclustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry.
Slide 125: Bulk Copy – BCP • What is BCP ?. – The SQL Server equivalent of sqlldr • • • • Can load data into tables from flat files. Can unload data from tables into a file. No direct path option unlike sqlldr. Files can be written to and read from in both ASCII and ‘native’ format.
Slide 126: Loading Data Using BCP • This requires a format file to be specified: 6.0 4 1 SQLDATETIME 2 SQLCHAR 3 SQLCHAR 4 SQLCHAR 0 0 0 0 8 255 255 255 "\t" "\t" "\t" "\n" 1 3 2 4 ErrorDate ErrorMsg ErrorCaption ErrorClass • • • • • • • Column one: specifies the order that the columns appear in the file. Column two: the host data type. Column three: prefix length (0 for ASCII & fixed length binary files). Column four: the host file data length. Column five: field terminator. Column six: position of the column in the table the data is to be inserted into. The last column is the table column name.
Slide 127: Unloading Data Using BCP • A format file be used to specify the format for unloaded data. OR • A query can be used, e.g.: bcp "SELECT * FROM pubs..authors" queryout authors.txt -U garth -P pw -c
Slide 128: Administering SQL Server Using DBCC & Supplied Stored Procedures • What is DBCC ? – DataBase Console Command • Used for checking the physical and logical consistency of the database. • Four categories of DBCC command are available: – Maintenance statements, e.g DBCC DBREINDEX – Miscellaneous statements, e.g. DBCC PINTABLE – Status statements, e.g. DBCC INPUTBUFFER – Validation statements, e.g. DBCC CHECKALLOC
Slide 129: Administering SQL Server Using DBCC & Supplied Stored Procedures • DBCC can be used with variables, example: DECLARE @dbcc_stmt sysname SET @dbcc_stmt = 'CHECKDB' DBCC HELP (@dbcc_stmt) GO
Slide 130: Administering SQL Server Using DBCC & Supplied Stored Procedures • Run CHECKDB when the system usage is low. • Be sure that you are not performing other disk I/O operations, such as disk backups. • Place tempdb on a separate disk system or a fast disk subsystem. • Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
Slide 131: Administering SQL Server Using DBCC & Supplied Stored Procedures • Avoid running CPU-intensive queries or batch jobs. • Reduce active transactions while a DBCC command is running. • Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly. • Consider using DBCC CHECKDB with the PHYSICAL_ONLY option to check the physical structure of the page and record headers. This operation performs a quick check if hardware-induced errors are suspect.
Slide 132: Addendum: Exporting Data From A Database To Another Data Source • Navigate down to the “Data Transformation Services” node and hit new. • The package designer will appear.
Slide 133: Addendum: Exporting Data From A Database To Another Data Source • Drag the “Microsoft ODBC Driver for Oracle” connection onto the design sheet. • Enter the server, username and password for the database you wish to connect to.
Slide 134: Addendum: Exporting Data From A Database To Another Data Source • Drag the “Excel” connection onto the design sheet. • Enter the name and path of the spread sheet you wish to create. • Pull the “Transform Data Task” onto the spread and use it to link the Oracle ODBC data source to the spread sheet.
Slide 135: Addendum: Exporting Data From A Database To Another Data Source • Double click on the transform data task. • Hit the SQL Query Radio button and enter the following in the “SQL Query window”: SELECT STORE_NO,SALES_DATE FROM STORE_AUDIT_STATUS WHERE STORE_AUDIT_STATUS=100
Slide 136: Addendum: Exporting Data From A Database To Another Data Source • On the ‘Destination’ tab hit the create table button and the dialog to the left should appear. Change ‘New Table’ to ‘store_dates_lt_verified’. Hit the ‘Transformations’ tab and make sure that the source and destination column mappings are correct. Hit OK and then execute from the package pull down menu to test the package. • • •
Slide 137: Addendum: Isolation Levels • What is an isolation level ? – Level at which a transaction is prepared to accept inconsistent data. • Lower isolation levels increase concurrency at the expense of data correctness. • Higher isolation levels increase data correctness at the expense of concurrency.
Slide 138: Addendum: Isolation Levels • The following levels defined in the ANSI SQL-92 standard are all supported by SQL Server: – Read uncommitted Lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read. – Read committed SQL Server default – Repeatable read – Serializable Highest level, where transactions are completely isolated from one another.
Slide 139: Addendum: Isolation Levels • Isolation level behaviour: Isolation . level Read uncommitted Dirty Read Yes Nonrepeatable read Yes Yes No No Phantom Yes Yes Yes No Read committed No Repeatable read Serializable No No
Slide 140: Addendum: Isolation Levels • What is a dirty read ?: – A transaction that reads a set of rows being inserted or deleted by another transaction. • What is a non repeatable read ? – When a transaction can read data at one point in it’s life and a subsequent read on the ‘same’ data yields different results. • What is a dirty read ? – When a transaction reads data being updates by another transaction.
Slide 141: Useful Resources • www.sqlteam.com • www.dbforums.com • www.sqldts.com