Super SQL Server Systems: Turbocharge Database Performance with C++ External Procedures

Description

This unique book focuses on hypercharging SQL Server database performance with external procedures.

SQL Server runs databases for financial, bank, biotech, Ecommerce, data warehousing, scientific applications, etc. which often require huge data processing with complex formulas or algorithms.

Most of this work is done with TSQL stored procedures and .NET procedures (in SQL Server 2005), however neither one of them can achieve the performance and flexibility of external procedures which are natively compiled code. External procedures are analogous to stored procedures but with the power of C++ for CPU intensive algorithms, hardware/low level access, use of external libraries, etc.

Database administrators/developers can create more efficient code and avoid expensive, slow and unreliable third party tools. There is also the extra bonus of having the source compiled, to protect the intellectual property.

This book is intended for C++ programmers who want to learn about database programming and use their C++ skills seamlessly, and database administrators and/or programmers who need more performance, functionality and versatility than they can achieve with TSQL, .NET procedures or ADO.

 

Table of Contents

1. Introduction to Extended Stored Procedures
What is an XP - "extended stored procedure"?
Why use XP's?
How to create XP's?
How to use XP's?
How to call XP's?
About the compilers/languages used to create XP’s
Can VB create XP's
SQL Server 2005 and extended stored procedures
ODS API
How to install the ODS code samples, libraries and headers
Registering an XP from Enterprise Manager
Using Query Analyzer
Execution of Extended Stored Procedures
XP Samples
Parameter handling
Data Types

2. Misc compilers and languages
Microsoft Visual C++ - WIN32 DLL xp_hellovcdll
Microsoft Visual C++ - WIN32 MFC DLL xp_hellomfc
CodeWarrior xp_hellocw
Borland C++ xp_hellobcc
Dev-C++ xp_hellodevc
MINGW xp_hellomw
Intel C++ Compiler
Digital Mars
Notes about compilation
Microsoft Platform SDK
Debugging an XP
Execution errors

3. A C++ Class Framework for XP's (XP++)
Exporting the function
Support for __GetXpVersion
Implement encapsulation by using C++ classes
Automatically link to the correct ODS import library
Encapsulate verification and setup of parameters.
Provide automatic conversion of parameter data types
Encapsulate the SDK calls provided by ODS
Reduce the number of parameters required by ODS
Track Memory Leaks
Using the framework
Other Examples
Summary

4. Network Messaging (XP_NETSEND)
Implementation
XP_NET_ADDNAME
XP_NET_DELNAME
XP_NET_SEND
Testing the Code
Applications of XP_NETSEND
Summary

5. SMTP Mailing (XP_SMTPSENDMAIL)
Choice of Mail Library
Using CDO for Windows 2000
Implementation Details
Code Highlights
Applications of XP_SMTPSENDMAIL
Summary

6. NT Event Log (XP_NTLOG)
Implementation
XP_NTLOG_INSTALL
XP_NTLOG_UNINSTALL
XP_NTLOG_ERROR, XP_NTLOG_WARNING & XP_NTLOG_INFORMATIONAL
Testing the Code
Applications of XP_NTLOG
Summary

7. Disk Logging (XP_DISKLOG)
Implementation
Testing the Code
Summary

8. Audio Notification (XP_NOTIFY)
Initial Implementation
XP_SIMPLE_MESSAGEBEEP, XP_ICONASTERISK_MESSAGEBEEP, XP_ICONEXCLAMATION_MESSAGEBEEP, XP_ICONHAND_MESSAGEBEEP, XP_ICONQUESTION_MESSAGEBEEP and XP_OK_MESSAGEBEEP
XP_BEEP
XP_PLAYSOUNDFILE and XP_STOPSOUND
XP_PLAYSOUNDALIAS
XP_PLAYSOUNDMEMORY
Testing the Code
Applications of XP_NOTIFY
Summary

9. Encoding HTML special characters (XP_HTMLENCODE)
Implementation
Testing the Code
Applications of XP_HTMLENCODE
XP_HTMLENCODE2
Summary

10. Cryptography (XP_CRYPTOAPI)
Hashing
Symmetric Encryption / Decryption
Hash Message Authentication Code
Asymmetric Encryption / Decryption
Message Signing / Verification
Public Key Management
XP_CRYPTOAPI Usage
Applications of XP_CRYPTOAPI
Summary

11. Random Data (XP_RAND)
Implementation
Testing the Code
Applications of XP_RAND
Summary

12. INI File Manipulation (XP_INI)
Implementation
XP_INI_WRITESTRING
XP_INI_WRITEINTEGER
XP_INI_READSTRING
XP_INI_READINTEGER
XP_INI_GETSECTIONS
XP_INI_READSECTION
XP_INI_DELETESECTION
XP_INI_DELETEKEY
XP_INI_GETKEYS
XP_INI_WRITE
XP_INI_READ
Testing the Code
Summary

13. Retrieving free disk space (XP_DISKSPACE)
Implementation
Testing the Code
Summary

14. Retrieving CPU usage (XP_CPUUSAGE)
Implementation
Testing the Code
Applications of XP_CPUUSAGE
Summary

15. Raw sockets sample (XP_RAWIP)
Implementation
Testing the Code
Summary

16. Geographical Information Systems (XP_GIS)
Introduction
The Shape of the Earth
Datums
Coordinate Systems and UTM
Implementation
XP_GIS_ELLIPSOIDINFO
XP_GIS_DATUMINFO
XP_GIS_DATUMTRANSFORM
XP_GIS_LL2UTM
XP_GIS_UTM2LL
XP_GIS_DISTANCE_BETWEEN
Testing the Code
Summary

17. Regular Expressions (XP_REGEXP)
Implementation
Testing the Code
Applications for XP_REGEXP
Summary

18. A Multiple Use XP (XP_SERVERREACHABLE)
Initial Creation
Adding COM / Win32 DLL Support
Adding XP Support
Testing the Code (XP Support)
Testing the Code (Win32 DLL Support)
Testing the Code (COM Support)
Summary

19. Computational Astronomy (XP_ASTRO)
Implementation
XP_ASTRO_DATE2JULIAN & XP_ASTROJULIAN2DATE
XP_ASTRO_DELTAT
XP_ASTRO_SPRING_EQUINOX, XP_ASTRO_SUMMER_SOLSTICE, XP_ASTRO_AUTUMN_EQUINOX & XP_ASTRO_WINTER_SOLSTICE
XP_ASTRO_MOONPHASEK & XP_ASTRO_MOONPHASE
Testing the Code
Summary

20. IP Configuration (XP_IPConfig)
IPConfig
IPConfig Syntax
Implementation
IP Helper
Testing the Code
Summary

21. Beyond C/C++ (XP_RUNSCRIPT)
Why the Need?
sp_OACreate, sp_OAMethod etc
The Solution
The Object Model
Initial Creation
The XPServer Object
The XPParameters Object
The XPParameter Objects
Adding XP and Active Scripting Support
Extending to support Visual Basic etc
Testing the Code
Summary

22. Protecting Intellectual Property with XP's
Prior Art
The valuable disclaimer
Technical protections
Stored procedures can be decrypted
Market for 3rd party components
Enforcing licenses with XP's
XP's as black boxes

23. Managed Code in SQL Server 2005
.NET vs TSQL
.NET vs XP’s
The right choice
Hello World in VB.NET
Visual Studio 2005
Stored Procedures with .NET code
Compiling from the Command Prompt
Express Manager
Levenshtein distance in .NET
Comparing Rijndael encryption
Comparing several MD5 implementations

24. Security in SQL Server 2000
Port security
SQL Injection
Cross-site scripting (XSS)
Buffer overflows
Snort
Security tools

25. From TSQL to XP's
TEA encryption with TSQL
TEA encryption with XP_CRYPTOAPI

26. SQL Server database tutorial
SQL Server default databases
Data types
Data types for code and data structure use
Data types for code use
Data types for data structure use
Data types for blobs
Tables
Data Integrity
Referential Integrity
Constraints
Rules, Defaults, and User-Defined Data Types
Views
Joins
Temporary tables
Subqueries
Derived table
Cursors
Indexes
SQL Server Collation
Triggers
Referential Integrity with triggers

27. SQL Server stored procedure tutorial
Samples of SP’s
Parameter handling
SP with optional Input parameters
Mean, Median and Modal with SP’s
SP error management
Rowset returning SP's
Start up SP's
Recursive SP's
Crosstab queries
Dynamic SQL

28. SQL Server User-defined functions (UDF's)
Types of functions
Scalar UDF's
Inline Table-valued UDF's
Multi-statement Table-valued UDF's
Recursive UDF’s
Jobs
Alerts

29. System XP's
Documented system XP's
Undocumented system XP's
Interesting undocumented system XP's
File related XP's
Registry related XP's
One-way encryption
SQL Mail Procedures

30. The Authors
Joseph Gama
Patrick Joseph (PJ) Naughter