DAT 335 SQL Server 2000 Tips and Tricks

DAT 335 SQL Server 2000 Tips and Tricks: DBAs and Developers Kimberly L. Tripp Solid Quality Learning – Solid. Quality. Learning. com Email: Kimberly@Solid. Quality. Learning. com SYSolutions, Inc. – SQLSkills. com Email: Kimberly@SQLSkills. com

Introduction Kimberly L. Tripp, SQL Server MVP Principal Mentor, Solid Quality Learning * In-depth, high quality training around the world! www. Solid. Quality. Learning. com Content Manager for www. SQLSkills. com Writer/Editor for TSQL Solutions/SQL Mag www. tsqlsolutions. com and www. sqlmag. com Consultant/Trainer/Speaker Coauthor for MSPress title: SQL Server 2000 High Availability Presenter/Technical Manager for SQL Server 2000 High Availability Overview DVD Very approachable. Please ask me questions!

Overview Saving Production Data Case Sensitive Searching Creating Cool Constraints All About Raiserror Minimizing Transactional Code VLDB Backup Best Practices – File/Filegroup Backup/Restore

Saving Production Data from Production DBAs Data Disaster Issues Schemabinding Create SCHEMABOUND Views

Data Disaster Issues Unexpected UPDATEs/DELETEs Minimize User’s permissions/direct access to base tables Use stored procedures for consistency/integrity Unexpected Table Drop Foreign Keys – protect a table from being dropped ONLY when other tables REFERENCE it… What about a Sales type of table? ? !? Customers Customer. ID @$#!& Sales Customer. ID Sales. Person. ID Product. ID Employees Employee. ID Products Product. ID DROP TABLE Sales

Schemabinding Solid Dependency Chains – Used to more permanently associate objects All Objects on which a schemabound object is bound – must also be bound All Objects must be referenced using two-part naming Must explicitly name columns in object (no *) No objects (or columns in definition of bound object) can be modified or dropped unless the object or schemabinding are removed! Sales Customer. ID Sales. Person. ID Product. ID DROP TABLE Sales Server: Msg 3729, Level 16, State 1, Line 1 Cannot DROP TABLE 'Sales' because it is being referenced by…

Create SCHEMABOUND Views Use WITH SCHEMABINDING in View Definition CREATE VIEW Sales_Prevent. Table. Drop. View WITH SCHEMABINDING AS SELECT Sales. ID FROM dbo. Sales Create Views on All objects you want to protect Review TSQL Tutor Quick Tip, Instant. Doc ID# 22073 for code to automate the creation! www. TSQLSolutions. com or www. SQLMag. com

Case Sensitive Searching Sort Order Concepts Sort Order Options Case Sensitive Searching

Sort Order Options To see the list of collations SELECT * FROM : : fn_helpcollations() To see the server's setting sp_helpsort To see the database's setting sp_helpdb dbname To see the table's setting (for each column) sp_help tname For more information, check out these BOL topics: COLLATE "Specifying Collations"

Sort Order Concepts Windows Install SQL Server Install I n h Database Creation e r Table Creation i t e d Query Usage } Use COLLATE option to change May be changed at any point in chain Different options offer different gains – most in language sorting, some in performance Can assign on the fly for just a single query – or within a view for subsequent use Can index the view for speed Full benefits of Indexed Views in Enterprise Edition only, see the whitepaper (listed in resources) for specific details.

Case Sensitive Searching CREATE VIEW Authors. Last. Name. Case. Sensitive AS SELECT au_lname COLLATE Latin 1_General_CS_AS_KS_WS AS Last. Name FROM authors go -- Select from the View SELECT * FROM Authors. Last. Name. Case. Sensitive WHERE Last. Name= 'ringer' -- 0 Rows SELECT * FROM Authors. Last. Name. Case. Sensitive WHERE Last. Name= 'Ringer' -- 2 Rows!!! Performance will NOT be good on large data sets. Consider creating an index on the Authors. Last. Name. Case. Sensitive View IF BOTH case-sensitive and case-insensitive searching is a common requirement for this table. See the Whitepaper: Improving Performance with Microsoft SQL Server 2000 Indexed Views.

Creating Cool Constraints DEFAULT Constraints CHECK Constraints DO NOT USE WITH NOCHECK

DEFAULT Constraints Need to adhere to CHECK constraints, if one exists Must be compatible with column datatype Can use system or user-defined functions! Inserted. By varchar(60)NOT NULL CONSTRAINT Employee. Inserted. By. Dflt DEFAULT host_name() + '' + suser_sname() + '' + user_name() Road. Runner. KTdomainkimberlykim Road. Runner. KTsadbo

Check Constraints (optimizer) All data will be checked on INSERT/UPDATE Pattern CHECK (Phone LIKE ‘(___) ___-____’) *Range* CHECK (Salary < 750000) The only operators supported for increased performance gains are: BETWEEN, AND, OR, <, <=, >, >=, =. Others are supported for data integrity purposes only. List CHECK (Country Code IN (‘US’, ‘AU’, ‘GB’, ‘FR’)) Added during CREATE TABLE Add later using ALTER TABLE But what about the existing data?

DO NOT USE WITH NOCHECK ALTER TABLE with CHECK – all existing data is checked and verified ALTER TABLE with NOCHECK – speeds up the creation BUT there are no performance gains for later use of the constraint Scenario Charge Table 1. 6 Million Rows Constraint added with NOCHECK (Charge. Amt < 5000) SELECT * FROM Charge WHERE Charge. Amt > 6000 -- yields 9305 I/O Constraint added with CHECK (Charge. Amt < 5000) SELECT * FROM Charge WHERE Charge. Amt > 6000 -- yields 0 I/O

All About RAISERROR Syntax Reviewed Parameterized Messages

RAISERROR Syntax Reviewed RAISERROR(Error, Severity, State [, Variable. List] WITH LOG Error String – can be a parameterized or fixed string of up to 400 characters. When parameterized the variables need to follow using Variable. List Number System messages < 50000 = default number for string messages User defined messages > 50000, added with sp_addmessage Variable. List – Error string must follow a specific format using placeholders for variables. Variables can follow two formats – by position or by number Examples coming up!

RAISERROR Syntax Reviewed RAISERROR(Error, Severity, State [, Variable. List]) WITH LOG Severity 1 -9 Error number in black with error message @@ERROR is NOT set Severity 10 ONLY the error message is returned, no error number is returned @@ERROR is NOT set Severity 11 -14 Error number in red with error message @@ERROR is set Severity 15 Error number in red with error message @@ERROR is set Severity 16 Error number in red with error message @@ERROR is set For more information, see the BOL topic “Error Message Severity Levels” When Logged to the Event Viewer Application Log Informational Warning Error

RAISERROR Syntax Reviewed RAISERROR(Error, Severity, State [, Variable. List] WITH LOG State Usually avoided by using 1 Virtually no use…except one! Using osql. exe and a state of 127 you can control the termination of a complex script before it executes in the wrong database! IF DATABASEPROPERTYEX('DBName', 'COLLATION') IS NULL BEGIN RAISERROR ('Error Message. ', 16, 127) END

Parameterized Messages exec sp_addmessage @msgnum = 900001, @severity = 16, @msgtext = 'Value %s is invalid. Procedure %s expected a value of TRUE or FALSE for parameter %s. The default is %s. ', @lang = 'US_English', @with_log = 'FALSE' go exec sp_addmessage @msgnum = 900001, @severity = 16, @msgtext = 'La valeur %s est inadmissible. Le procédé %s s''est attendu à une valeur de VRAI ou de FAUX pour le paramètre %s. Le défaut est %s. ' -- translation provided by www. babblefish. com , @lang = 'French', @with_log = 'FALSE' go RAISERROR(900001, 16, 1, ‘string’, ‘string’) WITH LOG

Minimize Transactional Code UPDATE Syntax Reviewed Common Transaction Mistakes Using an UPDATE to Minimize Transactional Code Real World Changes for Better Performance

UPDATE Syntax Reviewed DECLARE @id varchar(10) SELECT @id = (SELECT MIN(title_id) FROM dbo. titles WHERE type = 'business') UPDATE titles SET type = 'Min. Test', WHERE @id = title_id --Min may have changed SELECT @id go DECLARE @id varchar(10) UPDATE titles SET type = 'Min. Test', @id = title_id WHERE title_id = (SELECT MIN(title_id) FROM dbo. titles WHERE type = 'business') SELECT @id

Common Transaction Mistakes Worst Scenario (Data Integrity Problems) BEGIN TRAN SELECT data to analyze before update IF data is OK then UPDATE (data may have changed) Reselect data to return to client COMMIT TRAN Better Scenario (No Data Integrity Issues) BEGIN TRAN UPDATE data right away Reselect data and Verify UPDATE Return Data to client COMMIT TRAN

Updates to Minimize Code Best Case Scenario No Data Integrity Issues Best Performance BEGIN TRAN UPDATE data and assign variable for Verify UPDATE Return Data to client COMMIT TRAN Sometimes you can even get rid of the BEGIN TRAN and COMMIT TRAN!

Real World Changes BEGIN TRAN EXEC sp_executesql N'UPDATE dbo. table with (ROWLOCK, UPDLOCK) SET Col 1 = Col 1 + @U 1 WHERE Type = @U 2 SELECT Col 1 FROM dbo. table WHERE Type = @U 3', N'@U 1 int, @U 2 int, @U 3 int', @U 1=1, @U 2=0, @U 3=0 COMMIT TRAN -- Was changed to: EXEC sp_executesql N'UPDATE dbo. table WITH (ROWLOCK, UPDLOCK) SET @Col 1 = Col 1 + @U 1 WHERE Type = @U 2‘ , N'@U 1 int, @U 2 int‘ , @U 1=1, @U 2=0 -- But sp_executesql is not necessary. . . UPDATE dbo. table SET @Col 1 = Col 1 + @U 1 WHERE Type = @U 2

VLDB Backup Best Practices File/Filegroup Backup Strategies Case Study/Self Paced Example Gathering Backup History

File/Filegroup Backup Strategies Require Transaction Log backups for Restore Require FULL and/or Bulk Logged Recovery Model Do NOT Require a FULL Backup to have been performed – ever! Can BACKUP Files individually – not restricted to backup all files in a Filegroup (this was a change from SQL Server 7. 0) Add Improved Restore Capabilities (less time) at the expense of some Administrative Complexity

Demo Case Study 1 time filegroup Primary File 1 File 2 File 3 Log D B C R E A T E 2 F I L E T L O G 3 F I L E G R O U P 4 5 6 7 8 9 10 X 11 F I L E T L O G D I F F F G T L O G Step 1 – Determine how to get the structure of the database created. Tip – need the Primary and all other FULL file/filegroup backups. Backups 7 and 3. Step 2 – Bring the file/filegroups up to the minute as fast as possible. Tip – find the latest differentials. Backup 9. Step 3 – Apply the Transaction Logs that will make this database consistent. Tip – Look for the oldest FULL file/filegroup or differential and then check for the min LSN. Backups – 6? , 8, 10, and 11.

Gathering Backup History From Devices – LOAD HEADERONLY From MSDB, if the information exists SELECT Backup_Start_Date, Must find the Minimum Effective LSN [Name], * Take the oldest of the [Description], last few files or filegroups that have been restored and First_LSN, see what the FIRST_LSN is – Last_LSN, that’s your minimum effective LSN. Backup_Finish_Date, * Next find the FIRST log * -- OR Just * that includes this LSN. FROM msdb. dbo. backupset AS s JOIN msdb. dbo. backupmediafamily AS m ON s. media_set_id = m. media_set_id WHERE database_name = 'Pubs. Test' ORDER BY 1 ASC

Review Saving Production Data Case Sensitive Searching Creating Cool Constraints All About Raiserror Minimizing Transactional Code VLDB Backup Best Practices – File/Filegroup Backup/Restore

Other Sessions… DAT 335 – SQL Server Tips and Tricks for DBAs and Developers Tuesday, 1 July 2003, 15: 15 -16: 30 DBA 324 – Designing for Performance: Structures, Partitioning, Views and Constraints Wednesday, 2 July 2003, 08: 30 -09: 45 DBA 328 – Designing for Performance: Optimization with Indexes Wednesday, 2 July 2003, 16: 45 -18: 00 DBA 322 – Optimizing Stored Procedure Performance in SQL Server 2000 Thursday, 3 July 2003, 08: 30 -09: 45

Articles… Articles in TSQLSolutions at www. tsqlsolutions. com (FREE, just register) All About Raiserror, Instant. Doc ID#22980 Saving Production Data from Production DBAs, Instant. Doc ID#22073 Articles in SQL Server Magazine, Sept 2002: Before Disaster Strikes, Instant. Doc ID#25915 Log Backups Paused for Good Reason, Instant. Doc ID#26032 Restoring After Isolated Disk Failure, Instant. Doc #26067 Filegroup Usage for VLDBs, Instant. Doc ID#26031 Search www. sqlmag. com and www. tsqlsolutions. com for additional articles

Resources… Inside Microsoft SQL Server 2000, Kalen Delaney, MS Press, ISBN: 0735609985 http: //www. insidesqlserver. com/ Whitepaper: Index Tuning Wizard for Microsoft SQL Server 2000, http: //msdn. microsoft. com/library/enus/dnsql 2 k/html/itwforsql. asp? frame=true Whitepaper: Improving Performance with Microsoft SQL Server 2000 Indexed Views, http: //msdn. microsoft. com/library/enus/dnsql 2 k/html/indexedviews 1. asp? frame=true

Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx

Suggested Reading And Resources The tools you need to put technology to work! TITLE Available Microsoft® SQL Server™ 2000 High Availability: 0 -7356 -1920 -4 7/9/03 Microsoft Press books are 20% off at the Tech. Ed Bookstore Also buy any TWO Microsoft Press books and get a FREE T-Shirt

evaluations

Thank You! Kimberly L. Tripp Principal Mentor, Solid Quality Learning Website: www. solidqualitylearning. com Email: Kimberly@Solid. Quality. Learning. com President, SYSolutions, Inc. Website: www. sqlskills. com Email: Kimberly@SQLSkills. com

© 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.