ASP.NET, AJAX, PMS, ERP, SQL, Security, IT, ...: 2007-03-18

Wednesday, March 21, 2007

SQL Tips - String User-Defined Functions

Introduction
String UDFs
StrIns
StrDel
StrSeparate
StrCHARINDEX
StrREPLACE
StrREVERSE

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful String User-Defined Functions.

String UDFs

These scalar User-Defined Functions perform an operation on a string input value and return a string or numeric value.

StrIns

Inserts set of characters into another set of characters at a specified starting point.

Syntax

StrIns ( character_expression, start, character_expression )

Arguments

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrIns
 ( @str_1 nvarchar(4000),
   @start int,
   @str_2 nvarchar(4000) )
RETURNS nvarchar(4000)
AS
BEGIN
 RETURN (STUFF (@str_1, @start, 0, @str_2))
END
GO

Examples

This example returns a character string created by inserting the second string starting at position 2 (at b) into the first string.

SELECT dbo.StrIns('abcdef', 2, 'ijklmn')

Here is the result set:

------------
aijklmnbcdef

(1 row(s) affected)

StrDel

Deletes a specified length of characters at a specified starting point.

Syntax

StrDel ( character_expression, start, length )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - an integer value that specifies the location to begin deletion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.

length - an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrDel
 ( @str_1 nvarchar(4000),
   @start int,
   @length int )
RETURNS nvarchar(4000)
AS
BEGIN
 RETURN (STUFF (@str_1 , @start, @length, ''))
END
GO

Examples

This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b).

SELECT dbo.StrDel('abcdef', 2, 3)

Here is the result set:

---
aef

(1 row(s) affected)

StrSeparate

Inserts a specified character into the given string after every n-th character (from the end of the string).

Syntax

StrSeparate ( character_expression, term, number )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

term - a character.

number - an integer.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrSeparate
 ( @str nvarchar(4000),
   @term char(1),
   @number int )
RETURNS nvarchar(4000)
AS
BEGIN
 DECLARE @i int, @j int, @stepcount int
 IF (len(@str) <= @number) RETURN @str  SELECT @str =REVERSE(@str), @i = 1, @j = @number + 1,         @stepcount = len(@str) / @number  WHILE @i <= @stepcount    BEGIN      SET @str = ISNULL(STUFF(@str, @j, 0, @term), @str)      SET @j = @j + @number + 1      SET @i = @i + 1    END  SET @str = REVERSE(@str)  RETURN @str END GO

Examples

This example returns a character string created by inserting the space character after every three characters of the specified string (from the end of the string).

SELECT dbo.StrSeparate('12345678', ' ', 3)

Here is the result set:

----------
12 345 678

(1 row(s) affected)

StrCHARINDEX

Returns the starting position of the n-th entering of the specified expression in a character string.

Syntax

CHARINDEX ( expression1, expression2, start_location, number)

Arguments

expression1 - an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2 - an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location - the character position to start searching for expression1 in expression2. If start_location is a negative number, or is zero, the search starts at the beginning of expression2.

number - an integer.

Return Types

int

The function's text:

CREATE FUNCTION StrCHARINDEX
 ( @expression1 nvarchar(4000),
   @expression2 nvarchar(4000),
   @start_location int = 0,
   @number int )
RETURNS int
AS
BEGIN
DECLARE @i int, @position int
SET @i = 1
WHILE (@i <= @number) AND (CHARINDEX(@expression1, @expression2, @start_location) <> 0)
 BEGIN
   SET @position = CHARINDEX(@expression1, @expression2, @start_location)
   SET @expression2 = STUFF(@expression2,
                            CHARINDEX(@expression1, @expression2, @start_location),
                            len(@expression1),
                            space(len(@expression1)))
   SET @i = @i + 1
 END
RETURN @position
END
GO

Examples

SELECT dbo.StrCHARINDEX('12', '2312451267124', 0, 2)

Here is the result set:

-----------
7

(1 row(s) affected)

StrREPLACE

Replaces all occurrences of the second given string expression in the first string expression with a third expression starting from the start_location position.

Syntax

REPLACE('string_expression1','string_expression2','string_expression3',@start_location)

Arguments

'string_expression1' - the string expression to be searched.

'string_expression2' - the string expression to try to find.

'string_expression3' - the replacement string expression.

start_location - the character position to start replacing.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrREPLACE
 ( @string_expression1 nvarchar(4000),
   @string_expression2 nvarchar(4000),
   @string_expression3 nvarchar(4000),
   @start_location int )
RETURNS nvarchar(4000)
AS
BEGIN
 IF (@start_location <= 0) OR (@start_location > len(@string_expression1))
   RETURN (REPLACE (@string_expression1, @string_expression2, @string_expression3))
 RETURN (STUFF (@string_expression1,
                @start_location,
                len(@string_expression1) - @start_location + 1,
                REPLACE(SUBSTRING (@string_expression1,
                                   @start_location,
                                   len(@string_expression1) - @start_location + 1),
                                   @string_expression2,
                                   @string_expression3)))
END
GO

Examples

SELECT dbo.StrREPLACE('12345678912345', '23', '**', 4)

Here is the result set:

-------------------
1234567891**45

(1 row(s) affected)

StrREVERSE

Returns the reverse of a character expression starting at the specified position.

Syntax

REVERSE ( character_expression, start_location )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start_location - the character position to start reversing.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrREVERSE
 ( @character_expression  nvarchar(4000),
   @start_location int )
RETURNS nvarchar(4000)
AS
BEGIN
IF (@start_location <= 0) OR (@start_location > len(@character_expression))
 RETURN (REVERSE(@character_expression))
RETURN (STUFF (@character_expression,
              @start_location,
              len(@character_expression) - @start_location + 1,
              REVERSE(SUBSTRING (@character_expression,
                                 @start_location,
                                 len(@character_expression) - @start_location + 1))))
END
GO

Examples

SELECT dbo.StrREVERSE('123456789', 3)

Here is the result set:

-------------------
129876543

(1 row(s) affected)

SQL Tips - Mathematical User-Defined Functions

Introduction
Mathematical UDFs
Factorial
PercentFrom
PercentValue
Degree
Allocation
Combination

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Mathematical User-Defined Functions.

Mathematical UDFs

These scalar User-Defined Functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.

Factorial

Returns the factorial value of the given number.

Syntax

Factorial ( number )

Arguments

number - integer value.

Return Types

bigint

The function's text:

CREATE FUNCTION Factorial
 ( @number int )
RETURNS bigint
AS
BEGIN
 DECLARE @i int, @factorial bigint
 IF @number > 20 RETURN 0
 SET @i = 1
 SET @factorial = 1
 WHILE @i <= @number
   BEGIN
     SET @factorial = @factorial * @i
     SET @i = @i + 1
   END
 RETURN @factorial
END
GO

Examples

This example returns the factorial value of the number 5:

SELECT dbo.Factorial(5)

Here is the result set:

--------------------
120

(1 row(s) affected)

PercentFrom

Returns the percent of the expression1 in the expression2.

Syntax

PercentFrom ( expression1, expression2 )

Arguments

expression1 - is an expression of the exact numeric or approximate numeric data type category.
expression2 - is an expression of the exact numeric or approximate numeric data type category.

Return Types

float

The function's text:

CREATE FUNCTION PercentFrom
 ( @expression1 SQL_VARIANT,
   @expression2 SQL_VARIANT )
RETURNS float
AS
BEGIN
 RETURN (CAST(@expression1 AS FLOAT)/CAST(@expression2 AS FLOAT)*100)
END
GO

Examples

This example returns the percent of the 137 in the 273:

SELECT dbo.PercentFrom(137, 273)

Here is the result set:

-------------------------------------
50.183150183150182

(1 row(s) affected)

PercentValue

Returns the percent's value from the given expression for the given percent.

Syntax

PercentValue ( expression, percent )

Arguments

expression - is an expression of the exact numeric or approximate numeric data type category.
percent - integer value.

Return Types

float

The function's text:

CREATE FUNCTION PercentValue
 ( @expression SQL_VARIANT,
   @percent int )
RETURNS float
AS
BEGIN
 RETURN ( CAST(@expression AS FLOAT) / 100 * @percent )
END
GO

Examples

Returns the percent's value from the number 137 for the percent 11:

SELECT dbo.PercentValue (137, 11)

Here is the result set:

--------------------------
15.07

(1 row(s) affected)

Degree

Returns the degree for the given number and degree value.

Syntax

Degree ( number, degree )

Arguments

number - is an expression of the exact numeric or approximate numeric data type category.
degree - integer value.

Return Types

float

The function's text:

CREATE FUNCTION Degree
 ( @number SQL_VARIANT,
   @degree int )
RETURNS float
AS
BEGIN
 DECLARE @i int, @res float
 SET @i = 1
 SET @res = 1
 WHILE @i <= @degree
   BEGIN
     SET @res = CAST(@number AS FLOAT) * @res
     SET @i = @i + 1
   END
 RETURN @res
END
GO

Examples

Returns the degree 4 for the number 3:

SELECT dbo.Degree(3, 4)

Here is the result set:

-------------------------------
81.0

(1 row(s) affected)

Allocation

Returns the allocation from the m by n.

Syntax

Allocation ( m, n )

Arguments

m - integer value.
n - integer value.

Return Types

int

The function's text:

CREATE FUNCTION Allocation
 ( @m int,
   @n int )
RETURNS int
AS
BEGIN
 RETURN (dbo.Factorial(@m)/dbo.Factorial(@m-@n))
END
GO

Examples

Returns the allocation from the 5 by 3:

SELECT dbo.Allocation(5,3)

Here is the result set:

-----------
60

(1 row(s) affected)

Combination

Returns the combination from the m by n.

Syntax

Combination ( m, n )

Arguments

m - integer value.
n - integer value.

Return Types

int

The function's text:

CREATE FUNCTION Combination
 ( @m int,
   @n int )
RETURNS int
AS
BEGIN
 RETURN (dbo.Factorial(@m)/(dbo.Factorial(@m-@n)*dbo.Factorial(@n)))
END
GO

Examples

Returns the combination from the 5 by 3:

SELECT dbo.Combination(5,3)

Here is the result set:

-----------
10

(1 row(s) affected)

SQL Tips - Meta Data User-Defined Functions

Introduction
Meta Data UDFs
COL_LENGTH2
COL_ID
INDEX_ID
INDEX_COL2
ROW_COUNT

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Meta Data User-Defined Functions.

Meta Data UDFs

These scalar User-Defined Functions return information about the database and database objects.

COL_LENGTH2

Returns the defined length (in bytes) of a column for a given table and for a given database.

Syntax

COL_LENGTH2 ( 'database' , 'table' , 'column' )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table for which to determine column length information. table is an expression of type nvarchar.
'column' - the name of the column for which to determine length. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_LENGTH2
 ( @database sysname,
   @table sysname,
   @column sysname )
RETURNS int
AS
BEGIN
 RETURN (COL_LENGTH(@database + '..' + @table, @column))
END
GO

Examples

This example returns the defined length (in bytes) of the au_id column of the authors table in the pubs database:

SELECT dbo.COL_LENGTH2('pubs', 'authors', 'au_id')

Here is the result set:

-----------
11

(1 row(s) affected)

COL_ID

Returns the ID of a database column given the corresponding table name and column name.

Syntax

COL_ID ( 'table' , 'column' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'column' - the name of the column. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_ID
 ( @table sysname,
   @column sysname )
RETURNS int
AS
BEGIN
 DECLARE @col_id int
 SELECT @col_id = colid FROM syscolumns
        WHERE id = OBJECT_ID(@table) AND name = @column
 RETURN @col_id
END
GO

Examples

This example returns the ID of the au_fname column of the authors table in the pubs database:

USE pubs
GO
SELECT dbo.COL_ID('authors', 'au_fname')
GO

Here is the result set:

-----------
3

(1 row(s) affected)

INDEX_ID

Returns the ID of an index given the corresponding table name and index name.

Syntax

INDEX_ID ( 'table' , 'index_name' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'index_name' - the name of the index. index_name is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION INDEX_ID
 ( @table sysname,
   @index_name sysname )
RETURNS int
AS
BEGIN
 DECLARE @indid int
 SELECT @indid = indid FROM sysindexes
        WHERE id = OBJECT_ID(@table) AND name = @index_name
 RETURN @indid
END
GO

Examples

This example returns the ID of the aunmind index of the authors table in the pubs database:

USE pubs
GO
SELECT dbo.INDEX_ID('authors', 'aunmind')
GO

Here is the result set:

------
2

(1 row(s) affected)

INDEX_COL2

Returns the indexed column name for a given table and for a given database.

Syntax

INDEX_COL2 ( 'database' , 'table' , index_id , key_id )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table.
index_id - the ID of the index.
key_id - the ID of the key.

Return Types

nvarchar (256)

The function's text:

CREATE FUNCTION INDEX_COL2
 ( @database sysname,
   @table sysname,
   @index_id int,
   @key_id int )
RETURNS int
AS
BEGIN
 RETURN (INDEX_COL(@database + '..' + @table, @index_id, @key_id))
END
GO

Examples

This example returns the indexed column name of the authors table in the pubs database (for index_id = 2 and key_id = 1):

SELECT dbo.INDEX_COL2('pubs', 'authors', 2, 1)

Here is the result set:

-----------------------
au_lname

(1 row(s) affected)

ROW_COUNT

Returns the total row count for a given table.

Syntax

ROW_COUNT ( 'table' )

Arguments

'table' - the name of the table for which to determine the total row count. table is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION ROW_COUNT
 ( @table sysname )
RETURNS int
AS
BEGIN
 DECLARE @row_count int
 SELECT @row_count = rows FROM sysindexes
   WHERE id = OBJECT_ID(@table) AND indid < 2
 RETURN @row_count
END
GO

Examples

This example returns the total row count of the authors table in the pubs database:

USE pubs
GO
SELECT dbo.ROW_COUNT('authors')
GO

Here is the result set:

-----------
23

(1 row(s) affected)

See this link for more information:
Alternative way to get the table's row count

SQL Tips - Date and Time User-Defined Functions

Introduction
Date and Time UDFs
DatePart
TimePart
GetWorkingDays
AddWorkingDays
FirstMonthDay
LastMonthDay

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Date and Time User-Defined Functions.

Date and Time UDFs

These scalar User-Defined Functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

DatePart

Returns the date part of the datetime value.

Syntax

DatePart ( datepart )

Arguments

datepart - datetime value.

Return Types

varchar

The function's text:

CREATE FUNCTION dbo.DatePart
 ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
 RETURN ( CONVERT(varchar(10),@fDate,101) )
END
GO

Example

This example returns a character string contained the date part of the datetime value:

SELECT dbo.DatePart('11/11/2000 11:15AM')

Here is the result set:

----------
11/11/2000

(1 row(s) affected)

TimePart

Returns the time part of the datetime value.

Syntax

TimePart ( datepart )

Arguments

datepart - datetime value.

Return Types

varchar

The function's text:

CREATE FUNCTION dbo.TimePart
 ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
 RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
END
GO

Example

This example returns a character string contained the time part of the datetime value:

SELECT dbo.TimePart('11/11/2000 11:15AM')

Here is the result set:

----------
11:15AM

(1 row(s) affected)

GetWorkingDays

Returns the number of working days between two dates (not including these dates).

Syntax

GetWorkingDays ( StartDate, EndDate )

Arguments

StartDate - the datetime value (start date).
EndDate - the datetime value (end date).

Return Types

int

The function's text:

CREATE FUNCTION dbo.GetWorkingDays
 ( @StartDate datetime,
   @EndDate datetime )
RETURNS INT
AS
BEGIN
 DECLARE @WorkDays int, @FirstPart int
 DECLARE @FirstNum int, @TotalDays int
 DECLARE @LastNum int, @LastPart int
 IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
   BEGIN
     RETURN ( 0 )
   END
 SELECT
  @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
  @FirstPart = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 6
                WHEN 'Monday' THEN 5
                WHEN 'Tuesday' THEN 4
                WHEN 'Wednesday' THEN 3
                WHEN 'Thursday' THEN 2
                WHEN 'Friday' THEN 1
                WHEN 'Saturday' THEN 0
              END,
  @FirstNum = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 5
                WHEN 'Monday' THEN 4
                WHEN 'Tuesday' THEN 3
                WHEN 'Wednesday' THEN 2
                WHEN 'Thursday' THEN 1
                WHEN 'Friday' THEN 0
                WHEN 'Saturday' THEN 0
              END
 IF (@TotalDays < @FirstPart)
    BEGIN
      SELECT @WorkDays = @TotalDays
    END
 ELSE
    BEGIN
      SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
      SELECT @LastPart = (@TotalDays - @FirstPart) % 7
      SELECT @LastNum = CASE
        WHEN (@LastPart <> 0) THEN @LastPart - 1
        ELSE 0
      END
      SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
    END
 RETURN ( @WorkDays )
END
GO

Example

Returns the number of working days between '11/13/2000' and '12/27/2000':

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

Here is the result set:

-----------
31

(1 row(s) affected)

AddWorkingDays

Works like DATEADD, but adds the working days.

Syntax

AddWorkingDays ( StartDate, WorkDays )

Arguments

StartDate - the datetime value (start date).
WorkDays - the integer value (number of working days).

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.ADDWorkingDays
 ( @StartDate datetime,
   @WorkDays int )
RETURNS datetime
AS
BEGIN
 DECLARE @TotalDays int, @FirstPart int
 DECLARE @EndDate datetime
 DECLARE @LastNum int, @LastPart int

 IF @WorkDays < 0
    BEGIN
      SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 0
                WHEN 'Monday' THEN 1
                WHEN 'Tuesday' THEN 2
                WHEN 'Wednesday' THEN 3
                WHEN 'Thursday' THEN 4
                WHEN 'Friday' THEN 5
                WHEN 'Saturday' THEN 6
              END
      IF ABS(@WorkDays) < @FirstPart
         SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
      ELSE
        BEGIN
          SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5
          SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7
          SELECT @LastNum = CASE
            WHEN (@LastPart <> 0) THEN @LastPart - 1
            ELSE 0
          END
          SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays
          SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
        END
    END

 ELSE

    BEGIN
      SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 6
                WHEN 'Monday' THEN 5
                WHEN 'Tuesday' THEN 4
                WHEN 'Wednesday' THEN 3
                WHEN 'Thursday' THEN 2
                WHEN 'Friday' THEN 1
                WHEN 'Saturday' THEN 0
              END
      IF @WorkDays < @FirstPart
         SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
      ELSE
        BEGIN
          SELECT @TotalDays = (@WorkDays - @FirstPart) / 5
          SELECT @LastPart = (@WorkDays - @FirstPart) % 7
          SELECT @LastNum = CASE
            WHEN (@LastPart <> 0) THEN @LastPart - 1
            ELSE 0
          END
          SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays
          SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
        END
    END

 RETURN ( @EndDate )

END
GO

Example

Adds 9 working days to '11/13/2002' and returns the result date:

SELECT dbo.AddWorkingDays ('11/13/2002', 9)

Here is the result set:

-------------------------------------
2002-11-26 00:00:00.000

(1 row(s) affected)

FirstMonthDay

Returns the first day of the month for the given date.

Syntax

FirstMonthDay ( date )

Arguments

date - datetime value.

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.FirstMonthDay
 ( @Date datetime )
RETURNS datetime
AS
BEGIN
RETURN (CAST(STR(MONTH(@Date)) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime))
END
GO

Example

Returns the first day for the '06/15/99' date:

SELECT dbo.FirstMonthDay('06/15/99')

Here is the result set (from my machine):

-------------------------------------
1999-06-01 00:00:00.000

(1 row(s) affected)

LastMonthDay

Returns the last day of the month for the given date.

Syntax

LastMonthDay ( date )

Arguments

date - datetime value.

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.LastMonthDay
 ( @Date datetime )
RETURNS datetime
AS
BEGIN
RETURN (CASE WHEN MONTH(@Date)= 12
THEN DATEADD(day,-1,CAST('01/01/'+STR(YEAR(@Date)+1) AS DateTime))
ELSE DATEADD(day,-1,CAST(STR(MONTH(@Date)+1)+'/01/'+STR(YEAR(@Date)) AS DateTime))
END)
END
GO

Example

Returns the last day for the '06/15/99' date:

SELECT dbo.LastMonthDay('06/15/99')

Here is the result set (from my machine):

-------------------------------------
1999-06-30 00:00:00.000

(1 row(s) affected)

SQL Tips - Some useful UDF for SQL Server 2000

Introduction
UDF examples
Database creation date
Date the object was created
Get date part of datetime value
Get time part of datetime value
Get the number of working days between two dates

Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables.

There are three types of UDF in SQL Server 2000:

Scalar functions

Inline table-valued functions

Multistatement table-valued functions

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.

Inline table-valued functions return the result set of a single SELECT statement.

Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

UDF examples

Here you can find some useful user-defined functions.

Database creation date

This UDF will return the creation date for a given database (you should specify database name as parameter for this UDF):

CREATE FUNCTION dbo.DBCreationDate
 ( @dbname sysname )
RETURNS datetime
AS
BEGIN
 DECLARE @crdate datetime
 SELECT @crdate = crdate FROM master.dbo.sysdatabases
   WHERE name = @dbname
 RETURN ( @crdate )
END
GO

This is the example for use:

SELECT dbo.DBCreationDate('pubs')

Date the object was created

This UDF will return the creation date for a given object in the current database:

CREATE FUNCTION dbo.ObjCreationDate
 ( @objname sysname)
RETURNS datetime
AS
BEGIN
 DECLARE @crdate datetime
 SELECT @crdate = crdate FROM sysobjects WHERE name = @objname
 RETURN ( @crdate )
END
GO

This is the example for use:

SELECT dbo.ObjCreationDate('authors')

Get date part of datetime value

This UDF will return the date part of datetime value:

CREATE FUNCTION dbo.DatePart
 ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
 RETURN ( CONVERT(varchar(10),@fDate,101) )
END
GO

This is the example for use:

SELECT dbo.DatePart('11/11/2000 11:15AM')

Get time part of datetime value

This UDF will return the time part of datetime value:

CREATE FUNCTION dbo.TimePart
 ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
 RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
END
GO

This is the example for use:

SELECT dbo.TimePart('11/11/2000 11:15AM')

Get the number of working days between two dates

This UDF will return the number of working days between two dates (not including these dates):

CREATE FUNCTION dbo.GetWorkingDays
 ( @StartDate datetime,
   @EndDate datetime )
RETURNS INT
AS
BEGIN
 DECLARE @WorkDays int, @FirstPart int
 DECLARE @FirstNum int, @TotalDays int
 DECLARE @LastNum int, @LastPart int
 IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
   BEGIN
     RETURN ( 0 )
   END
 SELECT
  @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
  @FirstPart = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 6
                WHEN 'Monday' THEN 5
                WHEN 'Tuesday' THEN 4
                WHEN 'Wednesday' THEN 3
                WHEN 'Thursday' THEN 2
                WHEN 'Friday' THEN 1
                WHEN 'Saturday' THEN 0
              END,
  @FirstNum = CASE DATENAME(weekday, @StartDate)
                WHEN 'Sunday' THEN 5
                WHEN 'Monday' THEN 4
                WHEN 'Tuesday' THEN 3
                WHEN 'Wednesday' THEN 2
                WHEN 'Thursday' THEN 1
                WHEN 'Friday' THEN 0
                WHEN 'Saturday' THEN 0
              END
 IF (@TotalDays < @FirstPart)
    BEGIN
      SELECT @WorkDays = @TotalDays
    END
 ELSE
    BEGIN 
      SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
      SELECT @LastPart = (@TotalDays - @FirstPart) % 7
      SELECT @LastNum = CASE
        WHEN (@LastPart <> 0) THEN @LastPart - 1
        ELSE 0
      END
      SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
    END
 RETURN ( @WorkDays )
END
GO

This is the example for use:

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

SQL Tips - Optimizing SQL Server Performance by using File and Filegroups

General concepts

There are no devices or segments in SQL Server 7.0 and SQL Server 2000, as there were in SQL Server 6.5. Now databases reside on operating-system files.
There are three types of such files:

primary

secondary

log

Each database consists of at least two files: one is a primary data file (by default, with the .mdf extension) the other is log file (by default, with the .ldf extension). There are also optional secondary data files (by default, with the .ndf extension).

A database can have only one primary data file, zero or more secondary data files, and one or more log files. Each database file can be used by only one database. So there is no such situation (as in SQL Server 6.5 was), when you can store both databases and their logs on the same device (on the same file with the .dat extension).

The data files (.mdf and .ndf) are combined into filegroups. A filegroup is just a collection of one or more database files. Each database file can be a member of only one filegroup. Log files, on the other hand, are not members of filegroups, but are managed separately.

There are three types of filegroups:

primary

user-defined

default

Each database has only one primary filegroup, only one default filegroup, and zero or more user-defined filegroups. If you don't specify user-defined filegroups, your database will contain only a primary filegroup, which will be also the default filegroup. The primary filegroup contains the primary data file with all system objects in it (system tables, system stored procedures, extended stored procedures and so on). You cannot remove system objects from the primary filegroup, but you can create user objects in the user-defined filegroups for allocation, performance, and administration purposes.

To create user-defined filegroup, you should use CREATE DATABASE or ALTER DATABASE statement with the FILEGROUP keyword. The default filegroup is the filegroup where all the new user objects will be created. You can change the default filegroup by using ALTER DATABASE statement with the DEFAULT keyword.

SQL Server database files can be configured to grow and shrink automatically to reduce the need for active database management and eliminate many of the problems that can occur when logs or databases run out of space. The Autogrow feature is set on by default for all versions of SQL Server 7.0 and 2000, but the Autoshrink feature is set on by default only for the Desktop Edition.

When you create a database, you must set an initial size for both the data and log files. If you want to set database files to grow automatically, you should also specify the Autogrow increment in megabytes, kilobytes, gigabytes, terabytes, or percent, the default is MB. You can also specify a maximum file size to prevent disk drives from running out of space.

Optimization tips

To optimize the performance of your SQL Server, consider the following:

Set a reasonable size of your database.
First of all, before database creation, you should estimate how big your database will be.
To estimate the reasonable database size, you should estimate the size of each table individually, and then add the values obtained. See this link for more information:
Estimating the Size of a Table

Set a reasonable size for the transaction log.
The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The smaller the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimated database size is over 500Mb, the 50Mb may be enough for the size of the transaction log.

Leave the Autogrow feature on for the data files and for the log files.
Leave this feature to let SQL Server to automatically increase allocated resources when necessary without DBA intervention. The Autogrow feature is necessary when there is no DBA in your firm or your DBA doesn't have a lot of experience.

Set a reasonable size of the autogrow increment.
Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable size for the Autogrow increment to avoid automatically growing too often. Try to set the initial size of the database, and the size of the Autogrow increment, so that automatic growth will occur once per week or less.

Don't set the autoshrink feature.
Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on.

Set the maximum size of the data and log files.
Specify the maximum size to which the files can grow to prevent disk drives from running out of space.

Create a user-defined filegroup and make it the default filegroup.
It's a good decision in most cases to store and manage system and user objects separately from one another, so the user objects will not compete with system objects for space in the primary filegroup. Usually, a user- defined filegroup is not created for small databases, for example, if the database is less than 100Mb.

Create a user-defined filegroup and create some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
LOAD TABLE and DUMP TABLE are no longer supported in SQL Server 7.0 (and higher), but you can place a table in its own filegroup and can backup and restore only this table. So you can group user objects with similar maintenance requirements into the same filegroup.

If you have several physical disk arrays, try to create as many files as there are physical disk arrays so that you have one file per disk array.
This will improve performance, because when a table is accessed sequentially, a separate thread is created for each file on each disk array in order to read the table's data in parallel.

Don't create many data and log files on the same physical disk array.
Leaving the autogrow feature on for the data and for the log files can cause fragmentation of those files if there are many files on the same physical disk array. In most cases, it's enough to have 1-2 database files on the same physical disk.

For heavily accessed tables, place these tables in one filegroup and place the table's indexes in a different filegroup on different physical disk arrays.
This will improve performance, because separate threads will be created to access the tables and indexes.

For heavily accessed tables with text/image columns, place this table in one filegroup and place text/image columns in a different filegroup on different physical disks.
You can use CREATE TABLE statement with TEXTIMAGE_ON keyword to place text/image columns in a different filegroup. See the SQL Server BOL for details.

Place the log files on other physical disk arrays than those with the data files.
Because logging is more write-intensive, it's important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance.

If one of your join queries is used much more often than others, place the tables used in this query in different filegroups on different physical disk arrays.

If you have read-only tables, place these tables in different filegroups on different physical disk arrays and use the ALTER DATABASE statement to make just this filegroup READ ONLY.
This not only increases read performance, it prevents any data changes and allows you to control permissions to this data.

Use the Windows NT Performance Monitor to determine the appropriate number for the data and log files on your server by checking the Disk Queue Length counter.
Consider reducing the number of files and filegroups you have for your databases if the Disk Queue length on your server averages above 3, and continue monitoring once you have made your changes to ensure that your disk I/O is optimum over the long term.

Literature

1. SQL Server Books Online.

2. Microsoft SQL Server 7.0 Performance Tuning Guide

3. Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips

SQL Tips - Index Optimization Tips

General concepts

In this article, I want to show how you can improve the speed of your queries by choosing the proper indexes, what kinds of indexes MS SQL supports and what is the advantage and disadvantage of using indexes in particular situation.

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

When you create clustered or nonclustered index you can set the fill factor option to specify how full SQL Server should make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each page of the index. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.

The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created.

A covering index is an index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.

When you perform many data modification operations (INSERT, UPDATE or DELETE statements), then the table fragmentation occurs. If you want to determine the level of fragmentation, you can run the DBCC SHOWCONTIG statement.

Optimization tips

Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.

Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.

Try to create indexes on columns that have integer values rather than character values.

If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.

If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.

Create surrogate integer primary key (identity for example) if your table will not have many insert operations.

Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.

If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.

You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

You can use the sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Literature

1. SQL Server Books Online.

2. Microsoft SQL Server 7.0 Performance Tuning Guide

3. SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0

SQL Tips - View Transaction Log

It's not necessary to view the SQL Server transaction log, but if you are a DBA, it may be useful to resolve some problems. It may be useful for understanding the internal SQL Server architecture too.

SQL Server 6.5

Every database contains the syslogs system table in SQL Server 6.5. This table contains the transaction log, and is used by SQL Server for recovery and roll forward. You cannot modify syslogs system table manually.

This is from SQL Server Books Online (the syslogs table's columns):

Column  Datatype        Description
              
xactid  binary(6)       Transaction ID
op      tinyint         Update-operation number

So, to view the transaction log you can use the following select statement:

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

There are some op values:

op = 0  - BEGIN TRANSACTION
op = 4  - Insert Row
op = 5  - Delete Row
op = 9  - Modify Row
op = 30 - END TRANSACTION

SQL Server 7.0/2000

There is no syslogs system table in SQL Server 7.0/2000. The database log is now an operating system file. So, this SQL statement:

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

will return error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogs'.

You can use the following undocumented command in this case:

DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

by default type = 0

To view the transaction log for the master database, you can use the following command:

DBCC log (master)

SQL Tips - Copy text or image into or out of SQL Server

In this article, I want to show, how you can copy a single text or image value into or out of SQL Server by using the textcopy.exe utility. You can find this utility in the directory containing the standard SQL Server EXE files (C:\Mssql\Binn for SQL Server 6.5, by default and C:\Mssql7\Binn for SQL Server 7.0, by default).

Textcopy.exe utility is not described in SQL Server Books Online, but you can get its description by typing textcopy /? from the command prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The value
is a specified text or image 'column' of a single row (specified by the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
 [/D [database]] [/T table] [/C column] [/W"where clause"]
 [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

 /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not
                    specified, the local SQL Server is used.
  /U login           The login to connect with. If 'login' is not specified,
                    a trusted connection will be used.
 /P password        The password for 'login'. If 'password' is not
                    specified, a NULL password will be used.
 /D database        The database that contains the table with the text or
                    image data. If 'database' is not specified, the default
                    database of 'login' is used.
 /T table           The table that contains the text or image value.
 /C column          The text or image column of 'table'.
 /W "where clause"  A complete where clause (including the WHERE keyword)
                    that specifies a single row of 'table'.
 /F file            The file name.
 /I                 Copy text or image value into SQL Server from 'file'.
 /O                 Copy text or image value out of SQL Server into 'file'.
 /K chunksize       Size of the data transfer buffer in bytes. Minimum
                    value is 1024 bytes, default value is 4096 bytes.
 /Z                 Display debug information while running.
 /?                 Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of textcopy.exe utility:

CREATE PROCEDURE sp_textcopy (
 @srvname     varchar (30),
 @login       varchar (30),
 @password    varchar (30),
 @dbname      varchar (30),
 @tbname      varchar (30),
 @colname     varchar (30),
 @filename    varchar (30),
 @whereclause varchar (40),
 @direction   char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
        'textcopy /S ' + @srvname +
        ' /U ' + @login +
        ' /P ' + @password +
        ' /D ' + @dbname +
        ' /T ' + @tbname +
        ' /C ' + @colname +
        ' /W "' + @whereclause +
        '" /F ' + @filename +
        ' /' + @direction
EXEC master..xp_cmdshell @exec_str

This is the example to copy image into SQL Server database pubs, table pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @srvname = 'ServerName',
           @login = 'Login',
           @password = 'Password',
           @dbname = 'pubs',
           @tbname = 'pub_info',
           @colname = 'logo',
           @filename = 'c:\picture.bmp',
           @whereclause = " WHERE pub_id='0736' ",
           @direction = 'I'

By the way, you should insert something into text/image column before copy text/image into it. I mean, this value should not be null. You should write (for example):

INSERT INTO [dbo].[images] VALUES ('1', 0x0, null)

instead of:

INSERT INTO [dbo].[images] VALUES ('1', null, null)

Otherwise, you will get the following error message:

ERROR: Text or image pointer and timestamp retrieval failed.

SQL Tips - Alternative way to get the table's row count

To get the total row count in a table, we usually use the following select statement:

SELECT count(*) FROM table_name

This query performs full table scan to get the row count. You can check it by setting SET SHOWPLAN ON for SQL Server 6.5 or SET SHOWPLAN_TEXT ON for SQL Server 7.0/2000. So, if the table is very big, it can take a lot of time. In this example, the tbTest table will be created and 10000 rows will be inserted into this table:

CREATE TABLE tbTest (
id int identity primary key,
Name char(10)
)
GO
DECLARE @i int
SELECT @i = 1
WHILE @i <= 10000  BEGIN    INSERT INTO tbTest VALUES (LTRIM(str(@i)))    SELECT @i = @i + 1  END GO

There is another way to determine the total row count in a table. You can use the sysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <>

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.

This is the example:

SET STATISTICS IO ON
GO
SELECT count(*) FROM tbTest
GO
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid <>

This is the result:

 -----------
10000

(1 row(s) affected)

Table 'tbTest'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.
rows     
-----------
10000

(1 row(s) affected)

Table 'sysindexes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

So, you can improve the speed of the first query in several times. This works for SQL Server 6.5 and SQL Server 7.0/2000 as well.

SQL Tips - OLAP - Analysis Services optimization tips

*****

You can use the Usage-Based Optimization Wizard to optimize partition performance based on the history of queries previously sent to the cube.
To run the Usage-Based Optimization Wizard run Analysis Manager, right-click the appropriate cube and choose 'Usage-Based Optimization', then complete the steps in the wizard.

*****

Try to separate the OLTP server with the OLAP server if these servers very hard used by many users.
In this case, placing the OLAP server on its own dedicated server can boost the overall performance of the queries.
Note. When you use HOLAP or ROLAP cubes, placing cubes on a dedicated server can decrease performance if the data warehouse and Analysis Services will be connected via slow link.

*****

Try to use the MOLAP or HOLAP cubes instead of the ROLAP cubes, if you have enough amount of disk space.
The MOLAP and HOLAP cubes provide batter performance in comparison with ROLAP cubes, but can use more disk space to store the cube's data.

*****

Set the 'read only' data warehouse database option to true.
This can reduce the time needed to process the cube and increase the speed of queries against the data.

*****

If the data warehouse database is a read only database, you can create as many indexes as needed to for your Analysis Services queries.
If all of your Analysis Services queries are covered by the indexes, only indexes will be used to get the queries data, and the overall performance of these queries will be greatly maximized.

*****

Create all indexes in the data warehouse database with a 'fill factor' option of 100.
Using a 'fill factor' option of 100 ensures that the index pages will be as full as possible. This can increase the speed of queries and reduce the amount of space used to store the index data.

*****

Make a cube process during periods of low users activity.
Because a cube process can take much time, this operation should be scheduled during CPU idle time and slow production periods.

*****

Declare the foreign key relationships between the fact table and the dimension tables and create the indexes for every foreign key in the dimension tables.
This can greatly maximize the overall performance of the queries against the indexed data.

*****

Increase the level of aggregation for your cubes to boost the performance of the Analysis Services queries.
The larger the level of cube's aggregation will be, the faster the queries will be executed, but the more amount of disk space will be used and the more time it will take to process the cube. So, you should make some monitoring to get the best value for the level of aggregation for your cubes.

*****

Consider placing cubes on their own server to distribute the load, if these cubes are large and very busy.
Placing each cube on its own dedicated server can distribute the workload among these servers and boost the overall performance.

*****

If your OLAP server does not have CPU bottleneck, try to increase the "Maximum number of threads" Analysis Services option.
By default, the "Maximum number of threads" value is equal to two times the number of CPUs in the server computer. If you decide to increase this value, run System Monitor to check that there is no CPU bottleneck. To increase the "Maximum number of threads" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Environment' tab.

*****

If you have a dedicated server for Analysis Services, increase the "Minimum allocated memory" Analysis Services option.
By default, the "Minimum allocated memory" value is equal to one-half of the server computer's memory. Because allocation memory takes some time, if you will increase this value, you can avoid frequently automatic memory allocation. To increase the "Minimum allocated memory" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Environment' tab.

*****

If the OLAP server has a lot of RAM, increase the "Read-ahead buffer size" Analysis Services option.
This option indicates the maximum amount of data placed into memory during each read of the database. The larger this value will be, the less disk read operation will be required to read the cube's data. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the "Read-ahead buffer size" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Processing' tab.

*****

If the OLAP server has a lot of RAM, increase the "Process buffer size" Analysis Services option.
This option indicates how much data is processed in memory before an I/O is performed. The larger this value will be, the fewer the I/O operations will be required. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the "Process buffer size" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Processing' tab.

*****

If you install Analysis Services for SQL Server 2000 Enterprise Edition, you can create multiple partitions in a cube.
Using multiple partitions allows the source data and aggregate data of a cube to be distributed among multiple server computers. This can boost performance, because the workload will be distributed across multiple I/O devices.

SQL Tips - SQL Server 6.5 Optimization Tips

*****

Set a reasonable size of your database.
Because SQL Server 6.5 database cannot automatically grow, you should estimate how big the database will be. To estimate the reasonable database size, you should previous estimate the size of each table individually, and then add the values obtained.

*****

Set a reasonable size for the transaction log.
Because SQL Server 6.5 transaction log cannot automatically grow, you should estimate how big the transaction log will be. The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The less the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimation database size is over 500Mb, the 50Mb can be enough for the size of the transaction log.

*****

You can place a database device on a raw partition.
It can increase the speed of your queries and modify operations on 20 percents and more.

*****

Move tempdb database on its own database device to simplify administering and monitoring.
This is the description:
1. Create new device with appropriate size (tempdb_dev for example).
2. Uncheck "Default device" option for the master database device (this option is enable for the master database device by default).
3. Set "Default device" option for the tempdb_dev device.
4. From the Enterprise Manager (or sp_configure) set tempdb to be in RAM (set value to 1).
5. Stop and restart MSSQLServer service.
6. From the Enterprise Manager (or sp_configure) set tempdb to not be in RAM (set value to 0).
7. Stop and restart MSSQLServer service.

*****

You can put the tempdb database to be in RAM.
If your queries contain subqueries, or GROUP BY, or ORDER BY clause, you can increase their performance by placing the tempdb database into RAM.

*****

Create separate device for the transaction log.
In SQL Server 6.5, any database (except the master database) can span multiple devices. If you want to ensure recoverability and reduce contention, you should place the transaction log on a separate device.

*****

For heavily accessed table with text/image columns place this table on a one device and place text/image columns on a different device on separate physical disks.
It can be useful to simplify administering and monitoring.

*****

Place a table on one physical device and its nonclustered indexes on a different physical device.
This will improve performance, because separate threads will be created to access the tables and indexes.

*****

Split a large, heavily-used table across database devices on two separate disk controllers.
This will improve performance, because when a table is accessed sequentially, a separate thread is created for each physical device on each disk array in order to read the table's data in parallel.

*****

If one of the join queries is used most often, place the tables used in this query on different devices on separate physical disks.

*****

Create separate physical device and place some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
You can group user objects with similar maintenance requirements into the same physical device. It can be useful to simplify administering and monitoring.

*****

If you have several physical disk arrays, try to create as many physical devices as there are physical disk arrays so that you have one physical device per disk array.
This will improve performance, because when a table is accessed sequentially, a separate thread is created for each physical device on each disk array in order to read the table's data in parallel.

*****

You can backup and restore a single table.
It can be used to improve the speed of backup process and decrease the backup size. In SQL Server 7.0 and higher, the LOAD TABLE statement is no longer supported.

*****

If you create default constraint for some fields, you should define these fields as NOT NULL.
It can be used to store storage space.
See this article for more details:
Optimization tips for MS SQL 6.5: storage Nullable fields

*****

Place all nullable fields to the end of the fields list (after fields with fixed length), so than more often this field will contain NULL value, the closer to the end of the record it should be placed.
It can be used to store storage space.
See this article for more details:
Optimization tips for MS SQL 6.5: storage Nullable fields

*****

If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then you can improve performance by writing two queries and combine their result sets by using the UNION ALL statement.
If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then MS SQL 6.5 can use worktable with dynamic index on searchable field instead simple index search. So, if the table is very big, it can take a lot of time. You can increase the speed of this query by divide it into to select statement and union this statements with UNION ALL operator. For each query the appropriate index will be used, and this way can increase the speed of the new select statement in several times in comparison with the first one.
See this article for more details:
Using UNION ALL statement in SQL Server 6.5.

SQL Tips - SQL Server 2000 Optimization Tips

*****

For the very large databases, use distributed partitioned views to partition tables horizontally across multiple servers.
This is a new SQL Server 2000 feature, which is available when using SQL Server 2000 Enterprise Edition only. Due to distributed partitioned views, SQL Server 2000 now on the first place in the TPC-C tests.

*****

Use indexed views to increase the speed of your queries.
The result set of the indexed view is persist in the database and indexed for fast access. Because indexed views depend on base tables, you should create indexed views with SCHEMABINDING option to prevent the table or column modification that would invalidate the view. Furthermore, using views instead of heavy-duty queries can reduce network traffic and can be used to facilitate permission management.

*****

Consider using the WITH SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
Using this option can reduce the time it takes to create an index, but increases the amount of disk space used to create an index.

*****

You can specify whether the index keys are stored in ascending or descending order.
For example, using the CREATE INDEX statement with the DESC option (descending order) can increase the speed of queries, which return rows in the descending order. By default, the ascending order is used.

*****

Consider creating index on computed columns.
In SQL Server 2000, you can create indexes on computed columns. To create index on computed column, the computed column must be deterministic, precise, and cannot has text, ntext, or image data type.

*****

Consider setting the 'text in row' table option.
The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the 'text in row' table option by using the sp_tableoption stored procedure.

*****

Use table variables instead of temporary tables.
The table variable is a new SQL Server 2000 feature. The table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.

*****

Use cascading referential integrity constraints instead of triggers, whenever possible.
For example, if you need to make cascading deletes or updates, you can specify ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****

If you work with SQL Server 2000 Enterprise Edition use SAN (System Area Network) protocols instead of LAN (Local Area Network) or WAN (Wide Area Network).
SANs are more reliable than LANs or WANs and support high levels of messaging traffic by lowering CPU loads and message latency.

*****

Use user-defined functions to encapsulate code for reuse.
The user-defined functions (UDFs) contain one or more Transact-SQL statements that can be used to encapsulate code for reuse. Using UDFs can reduce network traffic.

*****

Set the 'awe enabled' server configuration option to 1 if you work with SQL Server 2000 Enterprise or Developer edition and have more than 4 gigabytes (GB) of physical memory.
Because SQL Server 2000 can support up to a maximum of 64 gigabytes (GB) of physical memory, you can purchase the appropriate server box and get all advantages of it hardware platform.

*****

Use the DBCC CHECKCONSTRAINTS statement if you need to check the integrity of a specified constraint or all constraints on a specified table.

*****

Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.
DBCC INDEXDEFRAG statement is an online operation. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block running queries or updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC DBREINDEX, whenever possible.

*****

You can use INSTEAD OF triggers to perform enhance integrity checks on the data values.
The INSTEAD OF trigger is a new SQL Server 2000 feature. These triggers can be used to enforce business rules when constraints cannot be used.

Note. Because triggers are more resource expensive, use constrains instead of triggers, whenever possible.