book

Beginning Microsoft® SQL Server® 2008 Programming

Name: Beginning Microsoft® SQL Server® 2008 Programming
Author: Robert Vieira
ISBN: 9780470257012

by Robert Vieira

January 2009

Beginner

717 pages

18h 33m

English

Wrox

Read now

Unlock full access

Copyright
About the Author
Credits
Acknowledgments
Introduction
Who This Book Is ForWhat This Book CoversHow This Book Is StructuredWhat You Need to Use This BookConventionsTry It OutHow It WorksSource CodeErratap2p.wrox.comwww.professionalsql.com
1. RDBMS Basics: What Makes Up a SQL Server Database?
1.1. An Overview of Database Objects1.1.1. The Database Object1.1.1.1. The master Database1.1.1.2. The model Database1.1.1.3. The msdb Database1.1.1.4. The tempdb Database1.1.1.5. ReportServer1.1.1.6. ReportServerTempDB1.1.1.7. AdventureWorks20081.1.1.8. AdventureWorksLT20081.1.1.9. AdventureWorksDW20081.1.1.10. The pubs Database1.1.1.11. The Northwind Database1.1.2. The Transaction Log1.1.3. The Most Basic Database Object: Table1.1.3.1. Indexes1.1.3.2. Triggers1.1.3.3. Constraints1.1.4. Filegroups1.1.5. Diagrams1.1.6. Views1.1.7. Stored Procedures1.1.8. User-Defined Functions1.1.9. Users and Roles1.1.10. Rules1.1.11. Defaults1.1.12. User-Defined Data Types1.1.13. Full-Text Catalogs1.2. SQL Server Data Types1.2.1. NULL Data1.3. SQL Server Identifiers for Objects1.3.1. What Gets Named?1.3.2. Rules for Naming1.4. Summary
2. Tools of the Trade
2.1. Books Online2.2. SQL Server Configuration Manager2.2.1. Service Management2.2.2. Network Configuration2.2.3. The Protocols2.2.3.1. Named Pipes2.2.3.2. TCP/IP2.2.3.3. Shared Memory2.2.4. On to the Client2.3. SQL Server Management Studio2.3.1. Getting Started with the Management Studio2.3.1.1. Server Type2.3.1.2. Server Name2.3.1.3. Authentication2.3.1.3.1. Windows Authentication2.3.1.3.2. SQL Server Authentication2.3.2. Query Window2.3.2.1. Getting Started2.3.2.2. Results to Text2.3.2.3. Results to Grid2.3.2.4. Results to File2.3.2.5. sqlcmd Mode2.3.2.6. Show Execution Plan2.3.2.7. The Available Databases Combo Box2.3.2.8. The Object Explorer2.4. SQL Server Integration Services (SSIS)2.5. Bulk Copy Program (bcp)2.6. SQL Server Profiler2.7. sqlcmd2.8. PowerShell2.9. Summary
3. The Foundation Statements of T-SQL
3.1. Getting Started with a Basic SELECT Statement3.1.1. The SELECT Statement and FROM Clause3.1.2. The WHERE Clause3.1.3. ORDER BY3.1.4. Aggregating Data Using the GROUP BY Clause3.1.4.1. Aggregates3.1.4.1.1. AVG3.1.4.1.2. MIN/MAX3.1.4.1.3. COUNT(Expression|*)3.1.5. Placing Conditions on Groups with the HAVING Clause3.1.6. Outputting XML Using the FOR XML Clause3.1.7. Making Use of Hints Using the OPTION Clause3.1.8. The DISTINCT and ALL Predicates3.2. Adding Data with the INSERT Statement3.2.1. Multirow Inserts3.2.2. The INSERT INTO . . . SELECT Statement3.3. Changing What You've Got with the UPDATE Statement3.4. The DELETE Statement3.5. Summary3.6. Exercises
4. JOINs
4.1. JOINs4.2. INNER JOINs4.2.1. How an INNER JOIN Is Like a WHERE Clause4.3. OUTER JOINs4.3.1. The Simple OUTER JOIN4.3.1.1. Finding Orphan or Non-Matching Records4.3.2. Dealing with More Complex OUTER JOINs4.4. Seeing Both Sides with FULL JOINs4.5. CROSS JOINs4.6. Exploring Alternative Syntax for Joins4.6.1. An Alternative INNER JOIN4.6.2. An Alternative OUTER JOIN4.6.3. An Alternative CROSS JOIN4.7. The UNION4.8. Summary4.9. Exercises
5. Creating and Altering Tables
5.1. Object Names in SQL Server5.1.1. Schema Name (aka Ownership)5.1.1.1. A Little More About Schemas5.1.1.2. The Default Schema: dbo5.1.2. The Database Name5.1.3. Naming by Server5.1.4. Reviewing the Defaults5.2. The CREATE Statement5.2.1. CREATE DATABASE5.2.1.1. ON5.2.1.2. NAME5.2.1.3. FILENAME5.2.1.4. SIZE5.2.1.5. MAXSIZE5.2.1.6. FILEGROWTH5.2.1.7. LOG ON5.2.1.8. COLLATE5.2.1.9. FOR ATTACH5.2.1.10. WITH DB CHAINING ON|OFF5.2.1.11. TRUSTWORTHY5.2.1.12. Building a Database5.2.2. CREATE TABLE5.2.2.1. Table and Column Names5.2.2.2. Data Types5.2.2.3. DEFAULT5.2.2.4. IDENTITY5.2.2.5. NOT FOR REPLICATION5.2.2.6. ROWGUIDCOL5.2.2.7. COLLATE5.2.2.8. NULL/NOT NULL5.2.2.9. Column Constraints5.2.2.10. Computed Columns5.2.2.11. Table Constraints5.2.2.12. ON5.2.2.13. TEXTIMAGE_ON5.2.2.14. Creating a Table5.3. The ALTER Statement5.3.1. ALTER DATABASE5.3.1.1. Option and Termination Specs5.3.2. ALTER TABLE5.4. The DROP Statement5.5. Using the GUI Tool5.5.1. Creating a Database Using the Management Studio5.5.2. Backing into the Code: The Basics of Creating Scripts with the Management Studio5.6. Summary5.7. Exercises

6. Constraints
6.1. Types of Constraints6.1.1. Domain Constraints6.1.2. Entity Constraints6.1.3. Referential Integrity Constraints6.2. Constraint Naming6.3. Key Constraints6.3.1. PRIMARY KEY Constraints6.3.1.1. Creating the Primary Key at Table Creation6.3.1.2. Creating a Primary Key on an Existing Table6.3.2. FOREIGN KEY Constraints6.3.2.1. Adding a Foreign Key to an Existing Table6.3.2.2. Making a Table Self-Referencing6.3.2.3. Cascading Actions6.3.2.3.1. Those Other CASCADE Actions ...6.3.2.4. Other Things to Think About with Foreign Keys6.3.2.4.1. What Makes Values in Foreign Keys Required vs. Optional6.3.2.4.2. How Foreign Keys Are Bi-Directional6.3.3. UNIQUE Constraints6.3.3.1. Creating UNIQUE Constraints on Existing Tables6.4. CHECK Constraints6.5. DEFAULT Constraints6.5.1. Defining a DEFAULT Constraint in Your CREATE TABLE Statement6.5.2. Adding a DEFAULT Constraint to an Existing Table6.6. Disabling Constraints6.6.1. Ignoring Bad Data When You Create the Constraint6.6.2. Temporarily Disabling an Existing Constraint6.7. Rules and Defaults — Cousins of Constraints6.7.1. Rules6.7.1.1. Dropping Rules6.7.2. Defaults6.7.2.1. Dropping Defaults6.7.3. Determining Which Tables and Data Types Use a Given Rule or Default6.8. Triggers for Data Integrity6.9. Choosing What to Use6.10. Summary
7. Adding More to Our Queries
7.1. What Is a Subquery?7.1.1. Building a Nested Subquery7.1.1.1. Nested Queries Using Single-Value SELECT Statements7.1.1.2. Nested Queries Using Subqueries That Return Multiple Values7.1.1.3. Using a Nested SELECT to Find Orphaned Records7.2. Correlated Subqueries7.2.1. How Correlated Subqueries Work7.2.2. Correlated Subqueries in the WHERE Clause7.2.2.1. Correlated Subqueries in the SELECT List7.2.3. Dealing with NULL Data — the ISNULL Function7.3. Derived Tables7.4. The EXISTS Operator7.4.1. Using EXISTS in Other Ways7.5. Mixing Data Types: CAST and CONVERT7.6. The MERGE Command7.6.1. A Brief Look at BY TARGET versus BY SOURCE7.6.2. The Output Clause7.7. Performance Considerations7.7.1. JOINs versus Subqueries versus ?7.8. Summary7.9. Exercises
8. Being Normal: Normalization and Other Basic Design Issues
8.1. Tables8.2. Keeping Your Data "Normal"8.2.1. Before the Beginning8.2.2. The First Normal Form8.2.3. The Second Normal Form8.2.4. The Third Normal Form8.2.5. Other Normal Forms8.3. Relationships8.3.1. One-to-One8.3.1.1. Zero or One-to-One8.3.2. One-to-One or Many8.3.2.1. One-to-Zero, One, or Many8.3.3. Many-to-Many8.4. Diagramming8.4.1. Tables8.4.2. Adding and Deleting Tables8.4.2.1. Editing Table Properties and Objects That Belong to the Table8.4.2.2. Properties Window8.4.2.3. Relationships8.4.2.4. Indexes/Keys8.4.2.5. Check Constraints8.4.3. Relationships8.4.3.1. Adding Relationships in the Diagramming Tool8.5. De-Normalization8.6. Beyond Normalization8.6.1. Keep It Simple8.6.2. Choosing Data Types8.6.3. Err on the Side of Storing Things8.7. Drawing Up a Quick Example8.7.1. Creating the Database8.7.2. Adding the Diagram and Our Initial Tables8.7.3. Adding the Relationships8.7.4. Adding Some Constraints8.8. Summary8.9. Exercises
9. SQL Server Storage and Index Structures
9.1. SQL Server Storage9.1.1. The Database9.1.2. The Extent9.1.3. The Page9.1.3.1. Page Splits9.1.4. Rows9.1.5. Sparse Columns9.2. Understanding Indexes9.2.1. B-Trees9.2.1.1. Page Splits — A First Look9.2.2. How Data Is Accessed in SQL Server9.2.2.1. Use of Table Scans9.2.2.2. Use of Indexes9.2.2.3. Index Types and Index Navigation9.2.2.3.1. Clustered Tables9.2.2.3.2. Heaps9.2.2.4. Clustered Indexes9.2.2.4.1. Navigating the Tree9.2.2.5. Non-Clustered Indexes on a Heap9.2.2.6. Non-Clustered Indexes on a Clustered Table9.3. Creating, Altering, and Dropping Indexes9.3.1. The CREATE INDEX Statement9.3.1.1. ASC/DESC9.3.1.2. INCLUDE9.3.1.3. WITH9.3.1.4. PAD_INDEX9.3.1.5. FILLFACTOR9.3.1.6. IGNORE_DUP_KEY9.3.1.7. DROP_EXISTING9.3.1.8. STATISTICS_NORECOMPUTE9.3.1.9. SORT_IN_TEMPDB9.3.1.10. ONLINE9.3.1.11. ALLOW ROW/PAGE LOCKS9.3.1.12. MAXDOP9.3.1.13. ON9.3.2. Creating XML Indexes9.3.2.1. The Primary XML Index9.3.2.2. Secondary XML Indexes9.3.3. Implied Indexes Created with Constraints9.3.4. Creating Indexes on Sparse and Geospatial Columns9.4. Choosing Wisely: Deciding What Index Goes Where and When9.4.1. Selectivity9.4.2. Watching Costs: When Less Is More9.4.3. Choosing That Clustered Index9.4.3.1. The Pros9.4.3.2. The Cons9.4.4. Column Order Matters9.4.5. ALTER INDEX9.4.5.1. Index Name9.4.5.2. Table or View Name9.4.5.3. REBUILD9.4.5.4. DISABLE9.4.5.5. REORGANIZE9.4.6. Dropping Indexes9.4.7. Take a Hint from the Query Plan9.4.8. Use the Database Engine Tuning Advisor9.5. Maintaining Your Indexes9.5.1. Fragmentation9.5.2. Identifying Fragmentation vs. Likelihood of Page Splits9.5.2.1. ALTER INDEX and FILLFACTOR9.6. Summary9.7. Exercises
10. Views
10.1. Simple Views10.1.1. Views as Filters10.2. More Complex Views10.2.1.10.2.1.1. The DATEADD and CAST Functions10.2.2. Using a View to Change Data — Before INSTEAD OF Triggers10.2.2.1. Dealing with Changes in Views with Joined Data10.2.2.2. Required Fields Must Appear in the View or Have Default Value10.2.2.3. Limit What's Inserted into Views — WITH CHECK OPTION10.3. Editing Views with T-SQL10.4. Dropping Views10.5. Creating and Editing Views in the Management Studio10.5.1. Editing Views in the Management Studio10.6. Auditing: Displaying Existing Code10.7. Protecting Code: Encrypting Views10.8. About Schema Binding10.9. Making Your View Look Like a Table with VIEW_METADATA10.10. Indexed (Materialized) Views10.11. Summary10.12. Exercises
11. Writing Scripts and Batches
11.1. Script Basics11.1.1. The USE Statement11.1.2. Declaring Variables11.1.2.1. Setting the Value in Your Variables11.1.2.1.1. Setting Variables Using SET11.1.2.1.2. Setting Variables Using SELECT11.1.2.2. Reviewing System Functions11.1.3. Using @@IDENTITY11.1.4. Using @@ROWCOUNT11.2. Batches11.2.1.11.2.1.1. A Line to Itself11.2.1.2. Each Batch Is Sent to the Server Separately11.2.1.3. GO Is Not a T-SQL Command11.2.2. Errors in Batches11.2.3. When to Use Batches11.2.3.1. Statements That Require Their Own Batch11.2.3.2. Using Batches to Establish Precedence11.3. sqlcmd11.4. Dynamic SQL: Generating Your Code On the Fly with the EXEC Command11.4.1. The Gotchas of EXEC11.4.1.1. The Scope of EXEC11.4.1.1.1. A Small Exception to the Rule11.4.1.2. Security Contexts and EXEC11.4.1.3. Use of Functions in Concatenation and EXEC11.4.1.4. EXEC and UDFs11.5. Control-of-Flow Statements11.5.1. The IF ... ELSE Statement11.5.1.1. The ELSE Clause11.5.1.2. Grouping Code into Blocks11.5.2. The CASE Statement11.5.2.1. A Simple CASE11.5.2.2. A Searched CASE11.5.3. Looping with the WHILE Statement11.5.4. The WAITFOR Statement11.5.4.1. The DELAY Parameter11.5.4.2. The TIME Parameter11.5.5. TRY/CATCH Blocks11.6. Summary11.7. Exercises
12. Stored Procedures
12.1. Creating the Sproc: Basic Syntax12.1.1. An Example of a Basic Sproc12.2. Changing Stored Procedures with ALTER12.3. Dropping Sprocs12.4. Parameterization12.4.1. Declaring Parameters12.4.1.1. Supplying Default Values12.4.1.2. Creating Output Parameters12.5. Confirming Success or Failure with Return Values12.5.1. How to Use RETURN12.6. More On Dealing with Errors12.6.1. The Way We Were ...12.6.1.1. Handling Inline Errors12.6.1.2. Making Use of @@ERROR12.6.1.3. Using @@ERROR in a Sproc12.6.2. Handling Errors Before They Happen12.6.3. Manually Raising Errors12.6.3.1. Message ID/Message String12.6.3.2. Severity12.6.3.3. State12.6.3.4. Error Arguments12.6.3.5. WITH <option>12.6.3.5.1. WITH LOG12.6.3.5.2. WITH SETERROR12.6.3.5.3. WITH NOWAIT12.6.4. Adding Your Own Custom Error Messages12.6.4.1. @lang12.6.4.2. @with_log12.6.4.3. @replace12.6.4.4. Using sp_addmessage12.6.4.5. Removing an Existing Custom Message12.7. What a Sproc Offers12.7.1. Creating Callable Processes12.7.2. Using Sprocs for Security12.7.3. Sprocs and Performance12.7.3.1. When a Good Sproc Goes Bad12.7.3.1.1. Using the WITH RECOMPILE Option12.8. Extended Stored Procedures (XPs)12.9. A Brief Look at Recursion12.10. Debugging12.10.1. Starting the Debugger12.10.2. Parts of the Debugger12.10.2.1. The Locals Window12.10.2.2. The Watch Window12.10.2.3. The Call Stack Window12.10.2.4. The Output Window12.10.2.5. The Command Window12.10.3. Using the Debugger Once It's Started12.11. .NET Assemblies12.12. Summary
13. User-Defined Functions
13.1. What a UDF Is13.2. UDFs Returning a Scalar Value13.3. UDFs That Return a Table13.3.1. Understanding Determinism13.4. Debugging User-Defined Functions13.5. .NET in a Database World13.6. Summary13.7. Exercise
14. Transactions and Locks
14.1. Transactions14.1.1. BEGIN TRAN14.1.2. COMMIT TRAN14.1.3. ROLLBACK TRAN14.1.4. SAVE TRAN14.2. How the SQL Server Log Works14.2.1. Failure and Recovery14.2.1.1. Transaction 114.2.1.2. Transaction 214.2.1.3. Transaction 314.2.1.4. Transaction 414.2.1.5. Transaction 514.2.2. Implicit Transactions14.3. Locks and Concurrency14.3.1. What Problems Can Be Prevented by Locks14.3.1.1. Dirty Reads14.3.1.2. Non-Repeatable Reads14.3.1.3. Phantoms14.3.1.4. Lost Updates14.3.2. Lockable Resources14.3.3. Lock Escalation and Lock Effects on Performance14.3.4. Lock Modes14.3.4.1. Shared Locks14.3.4.2. Exclusive Locks14.3.4.3. Update Locks14.3.4.4. Intent Locks14.3.4.5. Schema Locks14.3.4.6. Bulk Update Locks14.3.5. Lock Compatibility14.3.6. Specifying a Specific Lock Type — Optimizer Hints14.3.6.1. Determining Locks Using the Management Studio14.4. Setting the Isolation Level14.4.1. READ COMMITTED14.4.2. READ UNCOMMITTED14.4.3. REPEATABLE READ14.4.4. SERIALIZABLE14.4.5. SNAPSHOT14.5. Dealing with Deadlocks (aka "a 1205")14.5.1. How SQL Server Figures Out There's a Deadlock14.5.2. How Deadlock Victims Are Chosen14.5.3. Avoiding Deadlocks14.5.3.1. Use Objects in the Same Order14.5.3.2. Keeping Transactions as Short as Possible14.5.3.3. Use the Lowest Transaction Isolation Level Possible14.5.3.4. No Open-Ended Transactions14.6. Summary
15. Triggers
15.1. What Is a Trigger?15.1.1. ON15.1.2. WITH ENCRYPTION15.1.3. The FOR|AFTER vs. the INSTEAD OF Clause15.1.3.1. FOR|AFTER15.1.3.1.1. INSERT Trigger15.1.3.1.2. DELETE Trigger15.1.3.1.3. UPDATE Trigger15.1.4. WITH APPEND15.1.5. NOT FOR REPLICATION15.1.6. AS15.2. Using Triggers for Data Integrity Rules15.2.1. Dealing with Requirements Sourced from Other Tables15.2.2. Using Triggers to Check the Delta of an Update15.2.3. Using Triggers for Custom Error Messages15.3. Other Common Uses for Triggers15.4. Other Trigger Issues15.4.1. Triggers Can Be Nested15.4.2. Triggers Can Be Recursive15.4.3. Triggers Don't Prevent Architectural Changes15.4.4. Triggers Can Be Turned Off Without Being Removed15.4.5. Trigger Firing Order15.4.5.1. Controlling Firing Order for Logic Reasons15.4.5.2. Controlling Firing Order for Performance Reasons15.5. INSTEAD OF Triggers15.6. Performance Considerations15.6.1. Triggers Are Reactive Rather Than Proactive15.6.2. Triggers Don't Have Concurrency Issues with the Process That Fires Them15.6.3. Using IF UPDATE() and COLUMNS_UPDATED15.6.3.1. The UPDATE() Function15.6.3.2. The COLUMNS_UPDATED() Function15.6.4. Keep It Short and Sweet15.6.5. Don't Forget Triggers When Choosing Indexes15.6.6. Try Not to Roll Back Within Triggers15.7. Dropping Triggers15.8. Debugging Triggers15.9. Summary
16. A Brief XML Primer
16.1. XML Basics16.1.1. Parts of an XML Document16.1.1.1. The Document16.1.1.2. Declaration16.1.1.3. Elements16.1.1.4. Nodes16.1.1.4.1. The "Root" Node16.1.1.5. Attributes16.1.1.6. No Defects—Being Well Formed16.1.1.7. An XML Example16.1.1.8. Determining Elements vs. Attributes16.1.2. Namespaces16.1.3. Element Content16.1.4. Being Valid vs. Being Well Formed—Schemas and DTDs16.2. What SQL Server Brings to the Party16.2.1. Defining a Column as Being of XML Type16.2.2. XML Schema Collections16.2.3. Creating, Altering, and Dropping XML Schema Collections16.2.3.1. CREATE XML SCHEMA COLLECTION16.2.3.2. ALTER XML SCHEMA COLLECTION16.2.3.3. DROP XML SCHEMA COLLECTION16.2.4. XML Data Type Methods16.2.4.1. .query (SQL Server's Implementation of XQuery)16.2.4.2. .value16.2.4.3. .modify16.2.4.4. .nodes16.2.4.5. .exist16.2.5. Enforcing Constraints Beyond the Schema Collection16.2.6. Retrieving Relational Data in XML Format16.2.6.1. The FOR XML Clause16.2.7. RAW16.2.8. AUTO16.2.9. EXPLICIT16.2.10. PATH16.2.10.1. PATH 10116.2.10.1.1. Unnamed Columns16.2.10.1.2. Named Columns16.2.11. OPENXML16.3. A Brief Word on XSLT16.4. Summary
17. Reporting for Duty, Sir! A Look At Reporting Services
17.1. Reporting Services 10117.2. Building Simple Report Models17.2.1. Data Source Views17.2.1.1. Manipulating Relationships Between Objects In Data Source Views17.2.2. Building the Data Model17.2.2.1. Deploying Our Model17.2.3. Report Creation17.2.3.1. A Few Last Words on Report Models17.3. Report Server Projects17.3.1. Deploying the Report17.4. Summary
18. Getting Integrated with Integration Services
18.1. Understanding the Problem18.2. Using the Import/Export Wizard to Generate Basic Packages18.3. Examining Package Basics18.3.1. Connection Managers18.3.2. The Package Editor Pane18.3.2.1. Control Flow18.3.2.2. Data Flow18.3.2.3. Event Handlers18.3.2.4. Package Explorer18.4. Executing Packages18.4.1. Executing a Package Inside the Development Studio18.4.2. Using the Execute Package Utility18.4.2.1. General18.4.2.2. Configurations18.4.2.3. Command Files18.4.2.4. Connection Managers18.4.2.5. Execution Options18.4.2.6. Reporting18.4.2.7. Logging18.4.2.8. Set Values18.4.2.9. Verification18.4.2.10. Command Line18.4.2.11. Executing the Package18.4.3. Executing Using the SQL Server Agent18.4.4. Executing a Package from Within a Program18.5. A Final Word on Packages18.6. Summary
19. Playing Administrator
19.1. Scheduling Jobs19.1.1. Creating an Operator19.1.1.1. Creating an Operator Using Management Studio19.1.2. Creating Jobs and Tasks19.1.2.1. Creating Jobs and Tasks Using Management Studio19.2. Backup and Recovery19.2.1. Creating a Backup: a.k.a. "A Dump"19.2.1.1. Backup Type19.2.1.2. Backup Set19.2.1.3. Destination19.2.1.4. Options19.2.1.5. Schedule19.2.2. Recovery Models19.2.3. Recovery19.2.3.1. Restoring to a Different Location19.2.3.2. Recovery Status19.3. Index Maintenance19.3.1. ALTER INDEX19.3.1.1. Index Name19.3.1.2. Table or View Name19.3.1.3. REBUILD19.3.1.4. DISABLE19.3.1.5. REORGANIZE19.3.1.6. Archiving Data19.4. Policy Based Management19.5. Summary19.6. Exercises
A. System Functions
A.1. Legacy System Functions (a.k.a. Global Variables)A.1.1. @@CONNECTIONSA.1.2. @@CPU_BUSYA.1.3. @@IDLEA.1.4. @@IO_BUSYA.1.5. @@PACK_RECEIVED and @@PACK_SENTA.1.6. @@PACKET_ERRORSA.1.7. @@TIMETICKSA.1.8. @@TOTAL_ERRORSA.1.9. @@TOTAL_READ and @@TOTAL_WRITEA.1.10. @@TRANCOUNTA.2. Aggregate FunctionsA.2.1. AVGA.2.2. CHECKSUMA.2.3. CHECKSUM_AGGA.2.4. COUNTA.2.5. COUNT_BIGA.2.6. GROUPINGA.2.7. MAXA.2.8. MINA.2.9. STDEVA.2.10. STDEVPA.2.11. SUMA.2.12. VARA.2.13. VARPA.3. Configuration FunctionsA.3.1. @@DATEFIRSTA.3.2. @@DBTSA.3.3. @@LANGID and @@LANGUAGEA.3.4. @@LOCK_TIMEOUTA.3.5. @@MAX_CONNECTIONSA.3.6. @@MAX_PRECISIONA.3.7. @@NESTLEVELA.3.8. @@OPTIONSA.3.9. @@REMSERVERA.3.10. @@SERVERNAMEA.3.11. @@SERVICENAMEA.3.12. @@SPIDA.3.13. @@TEXTSIZEA.3.14. @@VERSIONA.4. Cryptographic FunctionsA.4.1. AsymKey_IDA.4.2. Cert_IDA.4.3. CertPropertyA.4.4. DecryptByAsmKeyA.4.5. DecryptByCertA.4.6. DecryptByKeyA.4.7. DecryptByPassPhraseA.4.8. EncryptByAsmKeyA.4.9. EncryptByCertA.4.10. EncryptByKeyA.4.11. EncryptByPassPhraseA.4.12. Key_GUIDA.4.13. Key_IDA.4.14. SignByAsymKeyA.4.15. SignByCertA.4.16. VerifySignedByAsymKeyA.4.17. VerifySignedByCertA.5. Cursor FunctionsA.5.1. @@CURSOR_ROWSA.5.2. @@FETCH_STATUSA.5.3. CURSOR_STATUSA.6. Date and Time FunctionsA.6.1. CURRENT_TIMESTAMPA.6.2. DATEADDA.6.3. DATEDIFFA.6.4. DATENAMEA.6.5. DATEPARTA.6.6. DAYA.6.7. GETDATEA.6.8. GETUTCDATEA.6.9. ISDATEA.6.10. MONTHA.6.11. SYSDATETIMEA.6.12. SYSDATETIMEOFFSETA.6.13. SYSUTCDATETIMEA.6.14. SWITCHOFFSETA.6.15. TODATETIMEOFFSETA.6.16. YEARA.7. Hierarchy FunctionsA.7.1. GetAncestorA.7.2. GetDescendantA.7.3. GetLevelA.7.4. GetRootA.7.5. IsDescendantOfA.7.6. ParseA.7.7. GetReparentedValueA.7.8. ToStringA.8. Mathematical FunctionsA.8.1. ABSA.8.2. ACOSA.8.3. ASINA.8.4. ATANA.8.5. ATN2A.8.6. CEILINGA.8.7. COSA.8.8. COTA.8.9. DEGREESA.8.10. EXPA.8.11. FLOORA.8.12. LOGA.8.13. LOG10A.8.14. PIA.8.15. POWERA.8.16. RADIANSA.8.17. RANDA.8.18. ROUNDA.8.19. SIGNA.8.20. SINA.8.21. SQRTA.8.22. SQUAREA.8.23. TANA.9. Basic Metadata FunctionsA.9.1. COL_LENGTHA.9.2. COL_NAMEA.9.3. COLUMNPROPERTYA.9.4. DATABASEPROPERTYA.9.5. DATABASEPROPERTYEXA.9.6. DB_IDA.9.7. DB_NAMEA.9.8. FILE_IDA.9.9. FILE_NAMEA.9.10. FILEGROUP_IDA.9.11. FILEGROUP_NAMEA.9.12. FILEGROUPPROPERTYA.9.13. FILEPROPERTYA.9.14. FULLTEXTCATALOGPROPERTYA.9.15. FULLTEXTSERVICEPROPERTYA.9.16. INDEX_COLA.9.17. INDEXKEY_PROPERTYA.9.18. INDEXPROPERTYA.9.19. OBJECT_IDA.9.20. OBJECT_NAMEA.9.21. OBJECTPROPERTYA.9.22. OBJECTPROPERTYEXA.9.23. @@PROCIDA.9.24. SCHEMA_IDA.9.25. SCHEMA_NAMEA.9.26. SQL_VARIANT_PROPERTYA.9.27. TYPEPROPERTYA.10. Rowset FunctionsA.10.1. CHANGETABLEA.10.2. CONTAINSTABLEA.10.3. FREETEXTTABLEA.10.4. OPENDATASOURCEA.10.5. OPENQUERYA.10.6. OPENROWSETA.10.7. OPENXMLA.11. Security FunctionsA.11.1. HAS_DBACCESSA.11.2. IS_MEMBERA.11.3. IS_SRVROLEMEMBERA.11.4. SUSER_IDA.11.5. SUSER_NAMEA.11.6. SUSER_SIDA.11.7. SUSER_SNAMEA.11.8. USERA.11.9. USER_IDA.11.10. USER_NAMEA.12. String FunctionsA.12.1. ASCIIA.12.2. CHARA.12.3. CHARINDEXA.12.4. DIFFERENCEA.12.5. LEFTA.12.6. LENA.12.7. LOWERA.12.8. LTRIMA.12.9. NCHARA.12.10. PATINDEXA.12.11. QUOTENAMEA.12.12. REPLACEA.12.13. REPLICATEA.12.14. REVERSEA.12.15. RIGHTA.12.16. RTRIMA.12.17. SOUNDEXA.12.18. SPACEA.12.19. STRA.12.20. STUFFA.12.21. SUBSTRINGA.12.22. UNICODEA.12.23. UPPERA.13. System FunctionsA.13.1. APP_NAMEA.13.2. CASEA.13.2.1. Simple CASE function:A.13.2.2. Searched CASE function:A.13.3. CAST and CONVERTA.13.3.1. Using CAST:A.13.3.2. Using CONVERT:A.13.4. COALESCEA.13.5. COLLATIONPROPERTYA.13.6. CURRENT_USERA.13.7. DATALENGTHA.13.8. @@ERRORA.13.9. FORMATMESSAGEA.13.10. GETANSINULLA.13.11. HOST_IDA.13.12. HOST_NAMEA.13.13. IDENT_CURRENTA.13.14. IDENT_INCRA.13.15. IDENT_SEEDA.13.16. @@IDENTITYA.13.17. IDENTITYA.13.18. ISNULLA.13.19. ISNUMERICA.13.20. NEWIDA.13.21. NULLIFA.13.22. PARSENAMEA.13.23. PERMISSIONSA.13.24. @@ROWCOUNTA.13.25. ROWCOUNT_BIGA.13.26. SCOPE_IDENTITYA.13.27. SERVERPROPERTYA.13.28. SESSION_USERA.13.29. SESSIONPROPERTYA.13.30. STATS_DATEA.13.31. SYSTEM_USERA.13.32. USER_NAMEA.14. Text and Image FunctionsA.14.1. TEXTPTRA.14.2. TEXTVALID
B. Very Simple Connectivity Examples
B.1. Some General ConceptsB.2. Connectivity ExamplesB.2.1. Connecting in C#B.2.1.1. Returning a Data SetB.2.1.2. Executing Commands with No Data SetB.2.2. Connecting in VB.NETB.2.2.1. Returning a Data SetB.2.2.2. Executing Commands with No Data SetB.3. A Brief Word on LINQ

Content preview from Beginning Microsoft® SQL Server® 2008 Programming

Chapter 13. User-Defined Functions

Well, here we are already at one of my favorite topics. Long after their introduction, user-defined functions—or UDFs—remain one of the more underutilized and misunderstood objects in SQL Server. In short, these were awesome when Microsoft first introduced them in SQL Server 2000, and the addition of .NET functionality back in SQL Server 2005 just added all that much more to them. One of the best things about UDFs from your point of view is, provided you've done the book in order, you already know most of what you need to write them. They are actually very, very similar to stored procedures—they just have certain behaviors and capabilities about them that set them apart and make them the answer in many situations.

In this chapter, we're not only going to introduce what UDFs are, but we're also going to take a look at the different types of UDFs, how they vary from stored procedures (often called sprocs), and, of course, what kinds of situations we might want to use them in. Finally, we'll take a quick look at how you can use .NET to expand on their power.

What a UDF Is

A user-defined function is, much like a sproc, an ordered set of T-SQL statements that are pre-optimized and compiled and can be called to work as a single unit. The primary difference between them is how results are returned. Because of things that need to happen in order to support these different kinds of returned values, UDFs have a few more limitations to them than sprocs do.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470257012Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Beginning Microsoft® SQL Server® 2008 Programming

by Robert Vieira

Chapter 13. User-Defined Functions

What a UDF Is

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.