Private Constructor

A private constructor is a special instance constructor. It is commonly used in classes that contain static members only. If a class has one or more private constructors and no public constructors, then other classes (except nested classes) are not allowed to create instances of this class. For example:
class NLog
   // Private Constructor:
   private NLog() {}

   public static double e = 2.71828;
The declaration of the empty constructor prevents the automatic generation of a default constructor. Note that if you don’t use an access modifier with the constructor it will still be private by default. However, the private modifier is usually used explicitly to make it clear that the class cannot be instantiated.
Private constructors are useful to prevent creation of a class when there are no instance fields or methods, such as the Math class, or when a method is called to obtain an instance of a class.
The following is an example of a class using a private constructor.
// PrivateCtor1.cs
using System;

public class MyClass
   private MyClass() {}
   public static int counter;
   public static int IncrementCounter()
      return ++counter;

class MainClass
   static void Main()
      // If you uncomment the following statement, it will generate
      // an error because the constructor is inaccessible:
      // MyClass myObject = new MyClass();   // Error
      MyClass.counter = 100;
      Console.WriteLine("New count: {0}", MyClass.counter);
New count: 101
Notice that if you uncomment the following statement from the example, it will generate an error because the constructor is inaccessible due to its protection level:
// MyClass myObject = new MyClass();   // error


Cursors in SQL

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursor process
1. Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
2. Execute the Transact-SQL statement to populate the cursor.
3. Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
4. Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
5. Close the cursor.

DECLARE titles_cursor CURSOR FOR
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   — Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

      PRINT "         <<No Books>>"    

      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title

   CLOSE titles_cursor
   DEALLOCATE titles_cursor

Cursor Types
Static cursors
The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. If rows making up the result set are updated by other users, the new data values are not displayed in the static cursor. The static cursor displays rows deleted from the database after the cursor was opened. No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.Microsoft® SQL Server™ static cursors are always read-only.Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.

Dynamic cursors 
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.

Forward-only cursors
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor, except for the case where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, Microsoft® SQL Server™ 2000 does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors. Transact-SQL cursors support forward-only static, keyset-driven, and dynamic cursors. The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.

Keyset-driven cursors 
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Changes to data values in nonkeyset columns (made by the cursor owner or committed by other users) are visible as the user scrolls through the cursor. Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened. Inserts made through the cursor using an API function such as the ODBC SQLSetPos function are visible at the end of the cursor. @@FETCH_STATUS returns a "row missing" status when an attempt is made to fetch a row deleted after the cursor was opened. An update to a key column operates like a delete of the old key value followed by an insert of the new key value. The new key value is not visible if the update was not made through the cursor; it is visible at the end of the cursor if the update was made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause and the SELECT statement did not contain a JOIN condition in the FROM clause. The new key value is not visible if the insert contained a remote table in the FROM clause. Attempts to retrieve the old key value get the same missing row fetch status as a deleted row.

User Defined Functions in SQL

Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. Microsoft® SQL Server™ 2000 does not limit users to the built-in functions defined as part of the Transact-SQL language, but allows users to create their own user-defined functions.

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (database_name.owner_name.function_name) must be unique.

You must have been granted CREATE FUNCTION permissions to create, alter, or drop user-defined functions. Users other than the owner must be granted appropriate permissions on a function before they can use it in a Transact-SQL statement. To create or alter tables with references to user-defined functions in the CHECK constraint, DEFAULT clause, or computed column definition, you must also have REFERENCES permission on the functions.
Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. In functions, such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled.

SQL Server 2000 supports three types of user-defined functions:

  1. Scalar functions
  2. Inline table-valued functions
  3. Multistatement table-valued functions

A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword default DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.

Scalar functions return a single data value of the type defined in a RETURNS clause. All scalar data types, including bigint and sql_variant, can be used. The timestamp data type, user-defined data type, and nonscalar types, such as table or cursor, are not supported. The body of the function, defined in a BEGIN…END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Table-valued functions return a table. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. For a multistatement table-valued function, the function body, defined in a BEGIN…END block, contains the TRANSACT-SQL statements that build and insert rows into the table that will be returned. For more information about inline table-valued functions, see Inline User-Defined Functions. For more information about table-valued functions, see User-Defined Functions That Return a table Data Type.

The statements in a BEGIN…END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

The types of statements that are valid in a function include:

  1. DECLARE statements can be used to define data variables and cursors that are local to the function.
  2. Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
  3. Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
  4. Control-of-flow statements.
  5. SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
  6. UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
  7. EXECUTE statements calling an extended stored procedure.

The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:

Schema-Bound Functions
CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. An attempt to alter or drop any object referenced by a schema-bound function fails.
These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:
• All views and user-defined functions referenced by the function must be schema-bound.
• All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names.
• You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.
When calling a scalar user-defined function, you must supply at least a two-part name:
SELECT *, MyUser.MyScalarFunction()
FROM MyTable
Table-valued functions can be called by using a one-part name:
FROM MyTableFunction()
However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:
SELECT * FROM ::fn_helpcollations()
A scalar function can be referenced any place an expression of the same data type returned by the function is allowed in a Transact-SQL statement, including computed columns and CHECK constraint definitions. For example, this statement creates a simple function that returns a decimal:
— Input dimensions in centimeters
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) — Cubic Centimeters.
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:
    BrickPartNmbr   int PRIMARY KEY,
    BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
                         BrickLength, BrickWidth)
dbo.CubicVolume is an example of a user-defined function that returns a scalar value. The RETURNS clause defines a scalar data type for the value returned by the function. The BEGIN…END block contains one or more Transact-SQL statements that implement the function. Each RETURN statement in the function must have an argument that returns a data value that has the data type specified in the RETURNS clause, or a data type that can be implicitly converted to the type specified in RETURNS. The value of the RETURN argument is the value returned by the function.

Clustered Indexes vs Nonclustered Indexes

Clustered Indexes
Clustered indexes have one row in sysindexes with indid = 1. The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point the key value in the inserted row fits in the ordering sequence.

Microsoft® SQL Server™ 2000 indexes are organized as B-trees. Each page in an index holds a page header followed by index rows. Each index row contains a key value and a pointer to either a lower-level page or a data row. Each page in an index is called an index node. The top node of the B-tree is called the root node. The bottom layer of nodes in the index are called the leaf nodes. The pages in each level of the index are linked together in a doubly-linked list. In a clustered index, the data pages make up the leaf nodes. Any index levels between the root and the leaves are collectively known as intermediate levels.

For a clustered index, sysindexes.root points to the top of the clustered index. SQL Server navigates down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server navigates through the index to find the starting key value in the range, and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.

Nonclustered Indexes
Nonclustered indexes have the same B-tree structure as clustered indexes, with two significant differences:

  1. The data rows are not sorted and stored in order based on their nonclustered keys.
  2. The leaf layer of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the nonclustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.

Nonclustered indexes can be defined on a table with a clustered index, a heap, or an indexed view. In Microsoft® SQL Server™ 2000, the row locators in nonclustered index rows have two forms:

  1. If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The entire pointer is known as a Row ID.
  2. If the table does have a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 makes duplicate keys unique by adding an internally generated value. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.

Query Analyzer

The basic steps that SQL Server uses to process a single SELECT statement are:

  1. The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. A query tree, sometimes called a sequence tree, is built describing the logical steps needed to transform the source data into the format needed by the result set.
  3. The query optimizer analyzes all the ways the source tables can be accessed and selects the series of steps that returns the results fastest while consuming fewer resources. The query tree is updated to record this exact series of steps, and the final, optimized version of the query tree is called the execution plan.
  4. The relational engine begins executing the execution plan. As steps that need data from the base tables are processed, the relational engine uses OLE DB to request that the storage engine pass up data from the rowsets requested from the relational engine.
  5. The relational engine processes the data returned from the storage engine into the format defined for the result set, and returns the result set to the client.