MS SQL Server

Since Unicode character set is different than code page based (non-Unicode) character set, converting Unicode strings to non-Unicode strings may result in wrong characters or missing characters. So you should avoid converting Unicode strings to non-Unicode strings. If you really want to, there are 3 ways to convert a Unicode string to a non-Unicode string:

* Implicit conversion by assignment operations - When a Unicode string is assigned to a variable, a column, or a parameter of a non-Unicode string data type, SQL Server will implicitly convert the Unicode string to a non-Unicode string.

* Explicit conversion using the CAST() function - A Unicode string can be explicitly converted to non-Unicode string using the CAST(Unicode_string AS VARCHAR(size)) function.

* Explicit conversion using the CONVERT() function - A Unicode string can be explicitly converted to non-Unicode string using the CONVERT(VARCHAR(size), Unicdoe_string) function.

Some numeric data type conversion examples are provided in the tutorial exercise below:

-- Implicit conversion by an assignment operation

DECLARE @regcode VARCHAR(40);

SET @regcode = N'Some Unicode characters: '

+ NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794);

SELECT @regcode;

GO

Some Unicode characters: ????

Transact-SQL is not a language designed for manipulating strings, but it does have two simple functions to locate and take substrings: CHARINDEX() and SUBSTRING(). The tutorial exercise below assumes two given strings: 'Pages: 18' and 'Words: 3240'. The objective is to calculate the number of words per page. Read the script below to see how this is done by using CHARINDEX() and SUBSTRING() functions:

DECLARE @sPages VARCHAR(40), @sWords VARCHAR(40);

SET @sPages = 'Pages: 18';

SET @sWords = 'Words: 3240';

SET @sPages = SUBSTRING(@sPages, CHARINDEX(':', @sPages)+1, 20);

SET @sWords = SUBSTRING(@sWords, CHARINDEX(':', @sWords)+1, 20);

PRINT 'Number of words per page: '

+ CONVERT(VARCHAR(20), CONVERT(INT, @sWords)/CONVERT(INT, @sPages));

GO

Number of words per page: 180

If you are a PHP developer, you can get this done in a much quick way.

SQL Server 2005 allows to concatenate two binary strings into a single string with the (+) operator. The following tutorial exercise shows you some binary string concatenation examples:

-- Concatenating two binary string literals

SELECT 0x57656C636F6D6520746F20

+ 0x46594963656E7465722E636F6D;

GO

0x57656C636F6D6520746F2046594963656E7465722E636F6D

-- Watch out: This is not a binary string concatenation

SELECT '0x57656C636F6D6520746F20'

+ '0x46594963656E7465722E636F6D';

GO

0x57656C636F6D6520746F200x46594963656E7465722E636F6D

-- Concatenating two binary strings

SELECT CONVERT(VARBINARY(40),'Welcome to ')

+ CONVERT(VARBINARY(40),'GlobalGuideLine.com');

GO

0x57656C636F6D6520746F2046594963656E7465722E636F6D

-- Binary strings can not be concatenated

with character strings

SELECT 'Welcome to '

+ 0x46594963656E7465722E636F6D;

GO

Msg 402, Level 16, State 1, Line 1

The data types varchar and varbinary are incompatible

in the add operator.

Can binary strings be converted into numeric or float data types? The answer is no. Binary strings can not be converted implicitly or explicitly into NUMERIC, DECIMAL, REAL, or FLOAT data types. The tutorial exercise gives you some examples of errors when converting binary strings to NUMERIC or FLOAT data types:

-- Implicit conversion to NUMERIC

SELECT 0x66 + 0.44;

GO

Msg 8114, Level 16, State 5, Line 1

Error converting data type varbinary to numeric.

-- Explicit conversion to NUMERIC

SELECT CONVERT(NUMERIC(9,2), 0x66) + 0.44;

GO

Msg 8114, Level 16, State 5, Line 1

Error converting data type varbinary to numeric.

-- Implicit conversion to REAL

DECLARE @real REAL;

SET @real = 0x66;

Msg 206, Level 16, State 2, Line 2

Operand type clash: varbinary is incompatible with real

-- Implicit conversion to FLOAT

DECLARE @float FLOAT(53);

SET @float = 0x66;

Msg 206, Level 16, State 2, Line 2

Operand type clash: varbinary is incompatible with float

If you want to break a string into multiple lines, you need to insert new line characters into the string. With some client tools like SQL Server Management Studio, it is not so easy to insert a new line character. One work around is to use the CHAR(int) function to generated new line character and other special characters with their code values:

* CHAR(9) - Generates the tab character.

* CHAR(10) - Generates the line feed (new line) character.

* CHAR(13) - Generates the carriage return character.

The tutorial examples below gives you a good example

PRINT 'Welcome to '+CHAR(10)+'GlobalGuideLine.com';

PRINT CHAR(10);

PRINT 'Current date and time is '

+CONVERT(VARCHAR(20), GETDATE());

GO

Welcome to

GlobalGuideLine.com

Current date and time is May 19 2007 7:30PM

SQL Server 2005 supports 23 character string functions:

* ASCII(char) - Returning the code value of a non-Unicode character.

* CHAR(int) - Returning the non-Unicode character of a code value.

* CHARINDEX(word, string, start_location) - Returning the location of the searched "word" in a string.

* DIFFERENCE(string1, string2) - Returning an integer value that indicates the difference between the SOUNDEX values of two strings.

* LEFT(string, length) - Returning a substring of "length" character from the left hand side.

* LEN(string) - Returning the number of characters in the string.

* LOWER(string) - Returning the same string with all upper case characters converted to lower case.

* LTRIM(string) - Returning the same string with leading spaces removed.

* NCHAR(int) - Returning the Unicode character of a code value.

* PATINDEX(pattern, string) - Returning the location of the "pattern" in a string.

* QUOTENAME(string, quote) - Returning the same string enclosed in "quote".

Can binary strings be used in arithmetical operations? The answer is yes. But there are two simple rules you need to remember:

* If an arithmetical operation has one binary string operand and one integer data type operand, the binary string operand will be converted to a integer data type to match the other operand. The operation will be performed as an integer operation.

* A + operator with two binary strings will be performed as binary string concatenation.

* A -, *, or / operator with two binary strings will be performed as binary string concatenation.

The tutorial exercise below shows you some good examples:

SELECT 0x66 + 44

GO

146

SELECT 0x66 - 44

GO

58

SELECT 0x66 * 44

GO

4488

SELECT 0x66 / 44

GO

2

SELECT 0x66 + 0x44

GO

0x6644

Binary strings and integers are convertible implicitly and explicitly. But there several rules you need to remember:

* Binary strings will be implicitly converted into an integer data type, if it is involved in an arithmetical operation with another integer data type operand.

* Binary strings will be implicitly converted into an integer data type, if it is assigned to a variable, a column or a parameter of an integer data type.

* Binary strings will be explicitly converted into integer data types using CAST() and CONVERT() functions.

* When converting binary strings that have more bytes than the target data type size, bytes on the left hand side will be truncated.

* When converting binary strings that have less bytes than the target data type size, 0x00 will be padded on the left hand side.

Examples showing in the tutorial exercise below will help you remembering those rules.

SELECT 0x66 + 44

GO

146

DECLARE @integer INT;

SET @integer = 0x66;

SELECT @integer + 44

GO

146

SELECT CAST(0x66 AS INT) + 44

GO

146

SELECT CONVERT(INT, 0x66) + 44

GO

146

-- Only last 4 bytes are used for INT conversion

SELECT 0x7700000066 + 44

GO

146

-- 8 bytes will be used for BIGINT conversion

SELECT 0x7700000066 + CONVERT(BIGINT,44)

If you find a mistake in an existing function previously created, you can drop (delete) it and create it again correctly. But dropping a function may affect other database objects who are depending on this function.

So the best way to correct a mistake in an existing function is to use the "ALTER FUNCTION" statement as shown in the following tutorial example:

-- Modifying an existing function

ALTER FUNCTION Sundays()

RETURNS INT

AS BEGIN

DECLARE @date DATETIME;

DECLARE @count INT;

SET @date = '2006-12-31';

SET @count = 0;

WHILE DATEPART(YEAR, @date) < 2008 BEGIN

SET @date = DATEADD(DAY, 1, @date);

IF DATENAME(WEEKDAY, @date) = 'Sunday'

SET @count = @count + 1;

END;

RETURN @count;

END;

GO

Command(s) completed successfully.

Do you know what correction has been made on this function?

Very often, you need to create a function with one or more parameters so that the function can be more generic. You only supply values to those parameters at the time of executing the function.

User defined functions with parameters can be created with the following syntax:

CREATE FUNCTION function_name (

@parameter_1 data_type,

@parameter_2 data_type,

...

@parameter_n data_type

)

RETURNS data_type

AS BEGIN

statement_1;

statement_2;

...

statement_n;

END;

The following tutorial exercise shows you how to create a function with one parameter called @url:

USE GlobalGuideLine;

GO

DROP FUNCTION Welcome;

GO

CREATE FUNCTION Welcome(@url VARCHAR(40))

RETURNS VARCHAR(40)

AS BEGIN

RETURN 'Welcome to '+@url;

END;

GO

PRINT 'Hi there, '+dbo.Welcome('GlobalGuideLine.com');

GO

Hi there, Welcome to GlobalGuideLine.com

If you want get the definition of an existing trigger back from the SQL Server, you can use the catalog view called sys.sql_modules, which stores definitions of views, stored procedures, and triggers.

The sys.sql_modules holds trigger definitions identifiable by the object id of each trigger. The tutorial exercise below shows you how to retrieve the definition of trigger, "dml_message" by joining sys.sql_modules and sys.triggers:

USE GlobalGuideLineDatabase;

GO

SELECT m.definition

FROM sys.sql_modules m, sys.triggers t

WHERE m.object_id = t.object_id

AND t.name = 'dml_message';

GO

definition

-------------------------------------------------

CREATE TRIGGER dml_message ON ggl_users

AFTER INSERT, UPDATE, DELETE

AS

PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE());

PRINT 'Records are inserted, updated,'

+ ' or deleted in ggl_users';

(1 row(s) affected)

If you want to make changes to an existing trigger, you could use the "ALTER TRIGGER" statements to refine the trigger again. The tutorial exercise below shows you how to modify the trigger defined in a previous tutorial:

USE GlobalGuideLineDatabase;

GO

ALTER TRIGGER dml_message ON ggl_users

AFTER INSERT, UPDATE, DELETE

AS

PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE());

PRINT 'Records are inserted, updated,'

+ ' or deleted in ggl_users';

GO

UPDATE ggl_users SET email='john@ggl' WHERE id = 1;

GO

Time: Jul 1 2007

Records are inserted, updated, or deleted in ggl_users

An extra printing statement is added the trigger.

To test a DML trigger defined on a table, you just need to execute several INSERT, UPDATE and DELETE statements on that table as shown in this tutorial example:

USE GlobalGuideLineDatabase;

GO

INSERT INTO ggl_users (name) VALUES ('GGL Admin');

GO

Records are inserted, updated, or deleted in ggl_users

(1 row(s) affected)

UPDATE ggl_users SET email='root@ggl'

WHERE name = 'GGL Admin';

GO

Records are inserted, updated, or deleted in ggl_users

(1 row(s) affected)

DELETE FROM ggl_users WHERE name = 'GGL Admin';

GO

Records are inserted, updated, or deleted in ggl_users

(1 row(s) affected)

The trigger, dml_message, is working as expected.

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. A trigger is really an event handler. SQL Server allows users to create triggers (event handlers) for 3 types of events:

* DML Event - Occurs when a DML (Data Manipulation Language) statement: INSERT, UPDATE or DELETE, is executed.

* DDL Event - Occurs when a DDL (Data Definition Language) statement: CREATE, ALTER, or DROP, is executed.

* Logon Event - Occurs when a user logins to the Server.

There are 3 different types of triggers (event handlers) based on the types of events they are triggered by:

* DML Trigger - Executes in response to a DML event.

* DDL Trigger - Executes in response to a DDL event.

* Logon Trigger - Executes in response to a logon event.

A DML trigger is a trigger declared to handle a DML event, which occurs when an INSERT, UPDATE or DELETE statement is executed. If you want to create a DML trigger, you should use the "CREATE TRIGGER" statement in the following format:

CREATE TRIGGER trigger_name ON table_name

AFTER INSERT, UPDATE, DELETE

AS

statements

GO

The tutorial exercise below shows you a very simple DML trigger defined on the ggl_users table. It does nothing but printing a simple static message.

USE GlobalGuideLineDatabase;

GO

CREATE TRIGGER dml_message ON ggl_users

AFTER INSERT, UPDATE, DELETE

AS

PRINT 'Records are inserted, updated,'

+ ' or deleted in ggl_users';

GO

Command(s) completed successfully.

A simple DML trigger is defined on ggl_users now.

Since a SQL Server trigger is a really an event handler, it has the following basic features similar to event handlers in other programming languages:

* Event Type - It must be declared to handle a specific event, like a DELETE event.

* Object Scope - It must be declared to handle events in a specific database object scope, like a specific table.

* Statement Body - It must have a statement body, a batch of statements to be executed when the specified event occurs in specified database object scope. An event handler (trigger) with an empty statement body is useless.

* Access of Event Attributes - It must have access to some attributes of the event, so it can have different logics for different instances of the event.

For example, you can implement a trigger to send a security alert message to each user whenever his or her password is changed. This trigger should have the following features:

* Event Type - It must be declared to handle the UPDATE event.

* Object Scope - It must be declared to handle the UPDATE event on the user password table only.

* Statement Body - It must have a statement body to determine if the password is really changed or not. If it is changed, send an email to the user's email address.

* Access of Event Attributes - It must have access to some attributes of the event instance, like the old value and the new value of the password, and the user email address.

If want to stop the execution of an existing trigger temporarily, you can use the "DISABLE TRIGGER" statement to disable it. The disabled trigger will be kept in the database.

If you want to resume the execution of a disabled trigger, you can use the "ENABLE TRIGGER" statement to enable it.

The tutorial exercise below shows you how to disable and enable triggers:

USE GlobalGuideLineDatabase

GO

-- disabling a trigger

DISABLE TRIGGER dml_message ON ggl_users;

GO

INSERT INTO ggl_users (name) VALUES ('MHI Gate');

GO

(1 row(s) affected)

-- enabling a trigger

ENABLE TRIGGER dml_message ON ggl_users;

GO

INSERT INTO ggl_users (name) VALUES ('Roy MHI');

GO

Time: Jul 1 2007

Records are inserted, updated, or deleted in ggl_users

(1 row(s) affected)