Posted by arlene

User-defined functions (UDFs) rock! UDFs allow us to create compliant units of Transact-SQL that can return either a scalar value or a table.

A scalar value returns a single value (as our UDF did) of the type we specify in the CREATE FUNCTION statement.

A table returns a table of results. It leverages the new data type (which we discuss soon). From this type of function we can return a similar concept to a recordset.

We supply one or more parameters to our UDF. These are of any data type except the new TABLE data type, timestamp, or Cursor. We can use these functions in a SELECT statement, in a WHERE clause, and so forth. They can be used as we would use any of the built-in SQL Server 2000 functions.

Our UDF functions can be bound to a table so that the table structure cannot be changed unless the UDF is dropped. Within the UDF we can also reference other SQL Server 2000 built-in functions. This allows us to get access to the server name, version of SQL Server, and so on. In my opinion this is one of the greatest features of SQL Server 2000.

Coding with Three New Data Types

Three new data types come with SQL Server 2000.

• SQL_Variant—This is a special data type that allows any type of data type to be stored, except SQL_Variant, ntext, text, and timestamp. Because it is not strongly typed, we can have softer data structures that allow many different bits of information to be stored.
• Biglnt—This is an 8-byte integer. This came around for some of the very large
  databases (VLDB), with more than 2 billion rows of data. SQL Server 2000now supports TINYINT, SMALLINT, INT, and BIGINT.
• TABLE—This is perhaps one of the coolest new data types. It allows us to create temporary tables as a data type, but these do not remain in scope like a temporary table, so you do not have to specifically drop them once finished.

These new data types allow greater flexibility in designing and implementing our applications. We can have softer, more fluid structures with the SQL_Variant data type.

Our tables can contain billions of rows with the BIGINT data type, and with the new data type of TABLE we can have temporary tables that are only for the current user rather than being more global and persistent.

Getting More from Query Analyzer

The new Object Browser in Query Analyzer is one of the most noticeable changes in the latest version of SQL Server 2000.

This allows us to navigate our way through the server. We can look at the databases, tables, views, and stored procedures, but it doesn’t stop there; we can also have the object scripted to the window as drop, creates, selects, and so on. For example, we right-click on a table and select Script Object to New Window As, Select. This creates the basic outline of a SELECT statement and from there we can fill in the gaps. This is really programming by example.

The Object Browser is not only for such things as user-defined tables and stored procedures. From within the Object Browser we can select built-in SQL Server 2000 functions. This gives us a list of the parameters that the functions expect, as well as their data types, and it also gives us the return type that the functions will return to us. This will save you time having to refer to Books Online to find the data types that a specific function returns.

The Object Browser also allows us to store templates. Some predefined templates come with the installation of SQL Server 2000, but more than that, we can create our own. This will allow us to create a structured outline that we want all of our developers to follow when they create a stored procedure, view, and so on. We can fully implement standards into our database development.

The Object Search screen (another new feature) enables us to enter search criteria (for example, a table name), and SQL Server 2000 will search through the databases to find a match. This makes finding stored procedures within a database that has hundreds of stored procedures very simple.

The new grid control in Query Analyzer enables multiple results to be returned to the one window. In earlier versions you would have a new result window for each set of results returned. Although this is a little enhancement, it means a lot for ease of use.

The Customize screen in Query Analyzer enables us to create shortcuts to the most commonly used Transact-SQL statements. For example, Alt+Fl will execute the sp_help stored procedure. The Customize screen is under Tools, Customize.

Possibly related posts: (automatically generated)
Mimicking Built-In Functions with User-Defined Functions

• Migration Considerations: Centralized Versus Decentralized Management
• What Is a Domain Tree? What Is a Forest?
• Active Directory Groups
• Scripting Database Objects
• What Role Will Your Server Perform?
• What Role Will Your Server Perform?
• The Active Directory and Dynamic DNS continue...
• Primary, Secondary, and Caching-Only Name Servers
• User Groups in Windows 2000 and Server 2003
• Inter-domain Trust Relationships