SQL (Structured Query Language)
What is a table?
A table in database is a data object used to
store data. Tables have the following features:
- Data
is stored in a table with a structure of rows and columns.
- Columns
must be pre-defined with names, types and constrains.
- A
table object may have other associated data objects like, constrains,
triggers, indexes, and statistics.
For example, a table called Address may have
columns defined to store different elements of an address like, street number,
city, country, postal code, etc.
What Is a Transact-SQL Statement?
A Transact-SQL statement is a basic execution
unit of the Transact-SQL language. Here are some examples of Transact-SQL
statements:
- "CREATE
TABLE ...;" - A data definition language statement that defines a
table.
- "INSERT
INTO ...;" - A data manipulation language statement that inserts data
into a table.
- "SELECT
* FROM ...;" - A query statement that returns data from a table.
- "DROP
TABLE ...;" - A data definition language statement that deletes a
table.
- "DECLARE
@name data_type;" - A declaration statement that defines a local
variable.
- "SET
@name = expression;" - An assignment statement that assigns a value
to a local variable.
- "BEGIN
TRANSACTION;" - A transaction management statement that marks the
beginning of a transaction.
- "ROLLBACK
TRANSACTION;" - A transaction management statement that rolls back
all changes of a transaction.
How To Start and End Transact-SQL Statements?
There are simple rule about writing Transact-SQL
statements:
- A
Transact-SQL statement should be started with a pre-defined statement
keyword.
- A
Transact-SQL statement should be ended with a new line (/n) or a semicolon
(;).
- A
Transact-SQL statement can be entered in a single line or multiple lines.
- Transact-SQL
statement keywords are case-insensitive.
- Extra
white space characters are ignored within Transact-SQL statements
What Are Exact Numeric Data Types?
Exact numeric data types are used to store
numeric values with exact precisions and scales. SQL Server 2005 supports the
following exact numeric data types:
- BIGINT
- 8-byte integers in the range of -2^63 (-9,223,372,036,854,775,808) to
2^63-1 (9,223,372,036,854,775,807).
- INT
- 4-byte integers in the range of -2^31 (-2,147,483,648) to 2^31-1
(2,147,483,647).
- SMALLINT
- 2-byte integers in the range of -2^15 (-32,768) to 2^15-1 (32,767).
- TINYINT
- 1-byte integers in the range of 0 to 255.
- BIT
- 1-bit integers with 2 values: 0 and 1.
- DECIMAL
- Decimal numbers with a fixed precision p and a fixed scale s defined as
DECIMAL(p,s). DECIMAL values are in the range of -10^38+1 to 10^38-1.
DECIMAL has a synonym of DEC.
- NUMERIC
- Same as DECIMAL.
- MONEY
- Currency values stored in 8 bytes in the range of
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
- SMALLMONEY
- Currency values stored in 4 bytes in the range of - 214,748.3648 to
214,748.3647
What Are Date and Time Data Types?
Date and time data types are used to store an
instances of calendar dates and times. SQL Server 2005 supports the following
date and time data types:
- DATETIME
- Date and time values stored in 8 bytes with 4 bytes for the date and 4
bytes for the time. DATETIME values are in the range of January 1, 1753 to
December 31, 9999 with a precision of 3.33 milliseconds.
- SMALLDATETIME
- Date and time values stored in 4 bytes with 2 bytes for the date and 2
bytes for the time. SMALLDATETIME values are in the range of January 1,
1900 to June 6, 2079 with a precision of 1 minute.
What Are Character String Data Types?
Character string data types are used to store
code page based character strings. SQL Server 2005 supports the following
character string data types:
- CHAR
- Code page based character strings with a fixed length of n bytes defined
as CHAR(n). The maximum length is 8,000 bytes. CHAR has a synonym of
CHARACTER.
- VARCHAR
- Code page based character strings with a variable length of n bytes
defined as VARCHAR(n). The maximum length is 8,000 bytes. VARCHAR has two
synonyms of CHAR VARYING and CHARACTER VARYING. VARCHAR also has special
form as VARCHAR(MAX), which can store up to 2^31-1 bytes.
- TEXT
- Code page based character strings with a variable length up to 2^31-1
(2,147,483,647) bytes. TEXT is equivalent to VARCHAR(MAX).
How To Get a What Are Binary String Data Types?
Binary string data types are used to store
binary strings. SQL Server 2005 supports the following binary string data
types:
- BINARY
- Binary strings with a fixed length of n bytes defined as BINARY(n). The
maximum length is 8,000 bytes.
- VARBINARY
- Binary strings with a variable length of n bytes defined as
VARBINARY(n). The maximum length is 8,000 bytes. VARBINARY has a synonym
of BINARY VARYING. VARBINARY also has special form as VARBINARY(MAX),
which can store up to 2^31-1 bytes.
- IMAGE
- Binary strings with a variable length up to 2^31-1 (2,147,483,647)
bytes.
What Are Unicode Character String Data Types?
Unicode character string data types are used to
store Unicode character strings. SQL Server 2005 supports the following Unicode
character string data types:
- NCHAR
- Unicode character strings with a fixed length of n characters defined as
NCHAR(n). The maximum length is 4,000 characters. NCHAR has two synonyms
of NATIONAL CHAR and NATIONAL CHARACTER.
- VARCHAR
- Unicode character strings with a variable length of n characters defined
as VARCHAR(n). The maximum length is 4,000 characters. NVARCHAR has two
synonyms of NATIONAL CHAR VARYING and NATIONAL CHARACTER VARYING. NVARCHAR
also has special form as NVARCHAR(MAX), which can store up to 2^31-1
bytes.
- NTEXT
- Unicode character strings with a variable length up to 2^31-1
(2,147,483,647) bytes. NTEXT is equivalent to NVARCHAR(MAX). NTEXT has a
synonym of NATIONAL TEXT.
What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length data types.
But they have the following main differences:
- CHAR
stores characters based on the code page with 1 byte per character most of
the time.
- NCHAR
stores characters using Unicode character set with 2 bytes per character
most of the time.
- CHAR(n)
specifies a length of n bytes by default.
- NCHAR(n)
specifies a length of n characters by default.
What Are the Differences between CHAR and
VARCHAR?
CHAR and VARCHAR are both used to store code
page based character strings. But they have the following main differences:
- CHAR(n)
stores character strings with a fixed length, n bytes, storage format.
- VARCHAR(n)
stores character strings with a variable length, less than n bytes,
storage format.
- If
the input string of CHAR(n) has less than n bytes, it will be padded with
space characters to become n bytes.
- If
the input string of VARCHAR(n) has less than n bytes, it will not be
padded with any characters.
- If
the input string of CHAR(n) has more than n bytes, it will be truncated to
n bytes.
- If
the input string of VARCHAR(n) has more than n bytes, it will also be
truncated to n bytes.
What Are the Differences between DECIMAL and
FLOAT?
DECIMAL and FLOAT are both used to store
numerical values. But they have the following main differences:
- DECIMAL(p,s)
stores values with the decimal point fixed at the position of s (scale)
digits from the right. The total number of decimal digits is also fixed as
p (precesion).
- FLOAT(n)
stores values with the decimal point floating based on the value. The
number of bits used to store the mantissa part is fixed as n.
- If
the input value of DECIMAL(p,s) has more digits after the decimal point
than the scale s, the value will be rounded to the scale s.
- If
the input value of FLOAT(n) has more total digits (mantissa) than what n
bits can store, the value will be rounded to fit the storage size.
- If
the input value of DECIMAL(p,s) has more digits before the decimal point
than p-s, SQL Server will give you an over-flow error.
- If
the input value of FLOAT(n) is too big that the exponential part goes over
the positive limit, SQL Server will give you an over-flow error.
- If
the input value of FLOAT(n) is too small that the exponential part goes
over the negative limit, SQL Server will give you an under-flow error.
List of
All Tables with "sys.tables" View?
If you want to see the table you have just
created, you can use the "sys.tables" system view to get a list of
all tables in the current database. The tutorial script gives you a good
example:
SELECT name, type_desc,
create_date FROM sys.tables
GO
name type_desc create_date
tip USER_TABLE 2007-05-19 23:05:43.700
How To Get a List of Columns using the
"sys.columns" View?
If you have an existing table, but you don't
remember what are the columns defined in the table, you can use the
"sys.columns" system view to get a list of all columns of all tables
in the current database.
In order to a list of columns of a single table,
you need to join sys.columns and sys.tables as shown in the tutorial example
below:
SELECT * FROM
sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'tip'
GO
object_id name
column_id user_type_id max_length
2073058421 id
1 56 4
2073058421 subject
2 167 80
2073058421 description
3 167 256
2073058421 create_date
4 61 8
You can see the column names easily from the
sys.columns view. But you can only see the column type IDs. This requires
another join to get the column type names. You may try the "sp_columns"
stored procedure to get a better list of columns shown in the next tutorial.
How To Get a List of Columns using the
"sp_columns" Stored Procedure?
If you have an existing table, but you don't
remember what are the columns defined in the table, you can use the
"sp_columns" stored procedure to get a list of all columns of the
specified table. The following tutorial script shows you a good example:
sp_columns tip
GO
TABLE_OWNER TABLE_NAME
COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar
...
dbo tip description varchar
...
dbo tip create_date datetime
...
The "sp_columns" stored procedure
returns a long list of properties for each column of the specified table. Take
a look at each of them.
How To Get a List of Columns using the
"sp_help" Stored Procedure?
Another way to get a list of columns from a
table is to use the "sp_help" stored procedure. "sp_help"
returns more than just a list of columns. It returns: the table information,
the column information, the identity column, the row GUID column, the primary
key, indexes, and constraints. It you run "sp_help tip" in SQL Server
Management Studio, you will see the result as shown in this picture:
How To Generate CREATE TABLE Script on an
Existing Table?
If you want to know how an existing table was
created, you can use SQL Server Management Studio to automatically generate a
"CREATE TABLE" script The following tutorial shows you how to do
this:
1. Run SQL Server Management Studio and connect
to SQL server.
2. On the Object Explorer window, follow the
object tree: Databases > FyiCenterData > Tables > dbo.tip.
3. Click right mouse button on dbo.tip. The
context menu shows up.
4. Select "Script Table as" >
"CREATE to" > "New Query Editor Window". The following
script will be displayed:
USE [FyiCenterData]
GO
/****** Object: Table [dbo].[tip]
Script Date: 05/19/2007 21:34:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[tip](
[id] [int] NOT NULL,
[subject] [varchar](80)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[description] [varchar](256)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[create_date] [datetime] NULL,
PRIMARY KEY CLUSTERED (
[id] ASC
)WITH (PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
How to create new tables with "SELECT ...
INTO" statements?
Let's say you have a table with many data rows,
now you want to create a backup copy of this table of all rows or a subset of
them, you can use the "SELECT ... INTO" statement. The tutorial
script below gives you a good example:
INSERT INTO tip VALUES
(1, 'Learn SQL',
'Visit dev.fyicenter.com','2006-07-01')
GO
SELECT * INTO tipBackup
FROM tip
GO
(1 rows affected)
SELECT * FROM tipBackup
GO
id subject
description
create_date
1 Learn SQL
Visit dev.fyicenter.com
2006-07-01
sp_columns tipBackup
GO
TABLE_OWNER TABLE_NAME
COLUMN_TABLE TYPE_NAME ...
dbo tipBackup id
int ...
dbo tipBackup subject
varchar ...
dbo tipBackup description
varchar ...
dbo tipBackup create_date
datetime ...
As you can see, the "SELECT ... INTO"
statement created a table called "tipBackup" using the same column
definitions as the "tip" table and copied all data rows into
"tipBackup".
How To Add a New Column to an Existing Table
with "ALTER TABLE ... ADD"?
If you have an existing table with existing data
rows, and want to add a new column to that table, you can use the "ALTER
TABLE ... ADD" statement. The tutorial script below shows you a good
example:
ALTER TABLE tip ADD
author VARCHAR(40)
GO
sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE
TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar
...
dbo tip description varchar
...
dbo tip create_date datetime
...
dbo tip author datetime
...
SELECT * FROM tip
GO
id subject
description
create_date author
1 Learn SQL
Visit dev.fyicenter.com
2006-07-01 NULL
This SQL script added a new column called
"author" to the "tip" table. NULL values were added to this
column on all existing data rows.
How To Delete an Existing Column in a Table with
"ALTER TABLE ... DROP COLUMN"?
If you have an existing column in a table and
you do not need that column any more, you can delete it with "ALTER TABLE
... DROP COLUMN" statement. Here is a tutorial script to delete an
existing column:
ALTER TABLE tip DROP
COLUMN create_date
GO
SELECT * FROM tip
GO
id subject
description author
1 Learn SQL
Visit dev.fyicenter.com NULL
As you can see, the column "create_date"
is gone.
How to rename an existing column with the
"sp_rename" stored procedure?
If you have an existing column in a table and
you want to change the column name, you can use the "sp_rename ...
'COLUMN'" stored procedure. "sp_rename" allows you to change
names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT. The tutorial
example below shows you how to rename a column:
USE master
GO
sp_rename 'tip.subject',
'title', 'COLUMN'
GO
Msg 15248, Level 11,
State 1, Procedure sp_rename, Line 213
Either the parameter
@objname is ambiguous or the claimed
@objtype (COLUMN) is wrong.
USE FyiCenterData
GO
sp_rename 'tip.subject',
'title', 'COLUMN'
GO
Caution: Changing any
part of an object name could break
scripts and stored procedures.
SELECT id, title,
description, author FROM tip
GO
id title
description author
1 Learn SQL
Visit dev.fyicenter.com NULL
You are getting the first error because
'FyiCenterData' is not the current database.
How to rename an existing column with SQL Server
Management Studio?
If you are using SQL Server Management Studio,
you can rename almost any data objects through the Object Explorer window. The
tutorial example below shows you how to rename a column:
1. Run SQL Server Management Studio and connect
to SQL server.
2. On the Object Explorer window, follow the
object tree: Databases > FyiCenterData > Tables > dbo.tip > Columns
> title.
3. Click right mouse button on
"title". The context menu shows up.
4. Select "Rename", type
"subject" over "title", and press Enter key. The column
name will be changed.
How to change the data type of an existing
column with "ALTER TABLE" statements?
Sometimes, you may need to change the data type
of an existing column. For example, you want increase the string length of a
column. You can use the "ALTER TABLE ... ALTER COLUMN" statements in
the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name new_type
Here is a good example of change column data
types:
-- Can not make a string
column shorter
ALTER TABLE tip ALTER
COLUMN subject VARCHAR(10)
GO
Msg 8152, Level 16,
State 14, Line 1
String or binary data
would be truncated.
The statement has been
terminated.
-- Can make a string
column longer
ALTER TABLE tip ALTER
COLUMN subject VARCHAR(100)
GO
Command(s) completed
successfully.
-- Can not change string
to numeric
ALTER TABLE tip ALTER
COLUMN subject NUMBER
GO
Msg 8114, Level 16,
State 5, Line 1
Error converting data
type varchar to numeric.
The statement has been
terminated.
As you can see, the new date type must be
compatible with the old data type in order for the "ALTER TABLE ... ALTER
COLUMN" statement to work.
How to rename an existing table with the
"sp_rename" stored procedure?
If you have an existing table and you want to
change the table name, you can use the "sp_rename ... 'OBJECT'"
stored procedure. "sp_rename" allows you to change names of COLUMN,
DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables). The tutorial
example below shows you how to rename a table:
sp_rename 'tip', 'faq',
'OBJECT'
GO
Caution: Changing any
part of an object name could break
scripts and stored procedures.
SELECT name, type_desc,
create_date FROM sys.tables
GO
name type_desc create_date
faq USER_TABLE 2007-05-19 23:05:43.700
tipBackup
USER_TABLE 2007-05-19 23:25:23.357
You can also rename a table with on the Object
Explorer window of SQL Server Management Studio. See tutorials on rename table
columns.
How To Drop an Existing Table with "DROP
TABLE" Statements?
If you want to delete an existing table and its
data rows, you can use the "DROP TABLE" statement as shown in the
tutorial script below:
SELECT * FROM tipBackup
GO
id subject
description
create_date
1 Learn SQL
Visit dev.fyicenter.com 2006-07-01
DROP TABLE tipBackup
GO
SELECT * FROM tipBackup
GO
Msg 208, Level 16, State
1, Line 1
Invalid object name
'tipBackup'.
Be careful, when you use the "DROP
TABLE" statement. All data rows are gone too.
How To Use Column Default Values in INSERT Statements?
If a column is defined with a default value in a
table, you can use the key word DEFAULT in the INSERT statement to take the
default value for that column. The following tutorial exercise gives a good
example:
INSERT INTO fyi_links
VALUES (102,
'dba.fyicenter.com',
NULL,
0,
DEFAULT)
GO
(1 row(s) affected)
SELECT * FROM fyi_links
GO
id url notes counts
created
101 dev.fyicenter.com NULL
0 2006-04-30
102 dba.fyicenter.com NULL
0 2007-05-19
The default value, getdate(), is used for
"created" column, which gives the current date.
How To Insert Multiple Rows with One INSERT
Statement?
If you want to insert multiple rows with a
single INSERT statement, you can use a subquery instead of the VALUES clause. Rows
returned from the subquery will be inserted the target table. The following
tutorial exercise gives you a good example:
INSERT INTO fyi_links
SELECT id+500, REVERSE(url),
notes, counts, created FROM fyi_links
GO
(3 row(s) affected)
SELECT * FROM fyi_links
GO
id url notes counts
created
101 dev.fyicenter.com NULL
0 2006-04-30
102 dba.fyicenter.com NULL
0 2007-05-19
103 sqa.fyicenter.com NULL
NULL 2007-05-19
601 moc.retneciyf.ved NULL
0 2006-04-30
602 moc.retneciyf.abd NULL
0 2007-05-19
603 moc.retneciyf.aqs NULL
NULL 2007-05-19
As you can see, "INSERT INTO ... SELECT
..." is powerful statement. you can use it build up data in tables
quickly.
How To Update Multiple Rows with One UPDATE
Statement?
If the WHERE clause in an UPDATE statement
matches multiple rows, the SET clause will be applied to all matched rows. This
rule allows you to update values on multiple rows in a single UPDATE statement.
Here is a good example:
SELECT * FROM fyi_links
WHERE id >= 500
GO
id url notes counts
created
601 moc.retneciyf.ved NULL
0 2006-04-30
602 moc.retneciyf.abd NULL
0 2007-05-19
603 moc.retneciyf.aqs NULL
NULL 2007-05-19
SELECT * FROM fyi_links
UPDATE fyi_links SET
counts = 9, notes = 'Wrong'
WHERE id >= 500
(3 row(s) affected)
SELECT * FROM fyi_links
WHERE id >= 500
GO
id url notes
counts created
601 moc.retneciyf.ved Wrong
9 2006-04-30
602 moc.retneciyf.abd Wrong
9 2007-05-19
603 moc.retneciyf.aqs Wrong
9 2007-05-19
The UPDATE statement updated 3 rows with the same
new values.
How to use old values to define new values in
UPDATE statements?
If a row matches the WHERE clause in a UPDATE
statement, existing values in this row can be used in expressions to provide
new values in the SET clause. Existing values are represented by column names
in the expressions. The tutorial exercise below shows you a good example:
SELECT * FROM fyi_links
WHERE id >= 500
GO
id url notes counts
created
601 moc.retneciyf.ved Wrong
9 2006-04-30
602 moc.retneciyf.abd Wrong
9 2007-05-21
603 moc.retneciyf.aqs Wrong
9 2007-05-23
UPDATE fyi_links SET id
= id+200, counts = id*2
WHERE id >= 500
GO
(3 row(s) affected)
SELECT * FROM fyi_links
WHERE id >= 500
GO
id url notes counts
created
801 moc.retneciyf.ved Wrong
1202 2006-04-30
802 moc.retneciyf.abd Wrong
1204 2007-05-19
803 moc.retneciyf.aqs Wrong
1206 2007-05-19
This statement increased values in the id column
by 200. It also updated the counts column with the newly increased id value.
How To Use Values from Other Tables in UPDATE
Statements?
If you want to update values in one table with
values from another table, you can use a subquery as an expression in the SET
clause. The subquery should return only one row for each row in the update
table that matches the WHERE clause. The tutorial exercise below shows you a
good example:
-- Create another table
CREATE TABLE fyi_rates
(id INTEGER,
comment VARCHAR(16))
Go
-- Insert some rows in
the new table
INSERT INTO fyi_rates
VALUES (101, 'The best')
Go
INSERT INTO fyi_rates
VALUES (102, 'Well done')
GO
INSERT INTO fyi_rates
VALUES (103, 'Thumbs up')
Go
-- Update fyi_links with
values from fyi_rates
UPDATE fyi_links SET
notes = (
SELECT comment FROM fyi_rates
WHERE fyi_rates.id = fyi_links.id
)
WHERE id > 0 AND id < 110
GO
(3 row(s) affected)
-- View the updated
values
SELECT * FROM fyi_links
WHERE id > 0 AND id < 110
GO
id url notes counts
created
101 dev.fyicenter.com The best
999 2006-04-30
102 dba.fyicenter.com Well done
0 2007-05-19
103 sqa.fyicenter.com Thumbs up
NULL 2007-05-19
Note that if column names are confusing between
the inner table and the outer table, you need to prefix column names with table
names, like "fyi_rates.id = fyi_links.id".
What Happens If the UPDATE Subquery Returns No
Rows?
If you use a subquery to assign new values in
the SET clause in an UPDATE statement, and the subquery returns no rows for an
outer row, SQL Server will provide a NULL value to the SET clause. The tutorial
exercise below shows you a good example:
-- insert a new row
INSERT INTO fyi_links
(id, url, notes)
VALUES (0, 'www.fyicenter.com', 'Number one')
GO
(1 row(s) affected)
-- view old values
SELECT * FROM fyi_links
WHERE id = 0
GO
id url notes counts
created
0 www.fyicenter.com Number one
NULL 2007-05-23
-- make sure there is no
matching row in fyi_rates
SELECT * FROM fyi_rates
WHERE id = 0
GO
0 rows
-- update a subquery
returning no rows
UPDATE fyi_links SET
notes = (
SELECT comment FROM fyi_rates
WHERE fyi_rates.id = fyi_links.id
)
WHERE id = 0
(1 row(s) affected)
-- view new values
SELECT * FROM fyi_links
WHERE id = 0
GO
id url notes counts
created
0 www.fyicenter.com NULL
NULL 2007-05-23
Column "notes" gets updated with NULL
if there is no return rows in the subquery.
What Happens If the UPDATE Subquery Returns
Multiple Rows?
If a subquery is used in a UPDATE statement, it
must return exactly one row for each row in the update table that matches the
WHERE clause. If it returns multiple rows, SQL Server will give you an error
message. To test this out, you can try the following tutorial exercise:
-- insert two rows to
fyi_rates
INSERT INTO fyi_rates
VALUES (0, 'Number 1')
GO
INSERT INTO fyi_rates
VALUES (0, 'Number 2')
GO
-- make sure there are 2
match rows
SELECT * FROM fyi_rates
WHERE id = 0
GO
id comment
0 Number 1
0 Number 2
-- update with subquery
that returns 2 rows
UPDATE fyi_links SET
notes = (
SELECT comment FROM fyi_rates
WHERE fyi_rates.id = fyi_links.id
)
WHERE id = 0
GO
Msg 512, Level 16, State
1, Line 1
Subquery returned more
than 1 value. This is not permitted
when the subquery follows =, !=, <, <=
, >, >= or when
the subquery is used as an expression.
The statement has been
terminated.
It is clear that we are using subquery as an
expression, and it must return 0 or 1 row. Otherwise, we will get an error.
How To Delete an Existing Row with DELETE
Statements?
If you want to delete an existing row from a
table, you can use the DELETE statement with a WHERE clause to identify that
row. Here is good sample of DELETE statements:
-- insert a row for this
test
INSERT INTO fyi_links
(url, id)
VALUES ('www.myspace.com', 301)
GO
(1 row(s) affected)
-- view the inserted row
SELECT id, url, notes,
counts FROM fyi_links
WHERE id = 301
GO
id url notes counts
301 www.myspace.com NULL
NULL
-- delete one row
DELETE FROM fyi_links
WHERE id = 301
GO
(1 row(s) affected)
-- try to view the
deleted row
SELECT id, url, notes,
counts FROM fyi_links
WHERE id = 301
no rows
How To Delete Multiple Rows with One DELETE
Statement?
You can delete multiple rows from a table in the
same way as deleting a single row, except that the WHERE clause will match
multiple rows. The tutorial exercise below deletes 3 rows from the fyi_links
table:
-- view rows to be
deleted
SELECT id, url, notes,
counts FROM fyi_links
WHERE id > 300
GO
id url notes counts
801 moc.retneciyf.ved Wrong 1202
802 moc.retneciyf.abd Wrong 1204
803 moc.retneciyf.aqs Wrong 1206
-- delete multiple rows
DELETE FROM fyi_links
WHERE id > 300
GO
(3 row(s) affected)
-- try to view the
deleted row
SELECT id, url, notes,
counts FROM fyi_links
WHERE id > 300
GO
no rows
How To Delete All Rows with DELETE Statements?
If you want to delete all rows from a table, you
have two options:
- Use
the DELETE statement with no WHERE clause.
- Use
the TRUNCATE TABLE statement.
Here is an example of deleting all rows with a
DELETE statement:
SELECT COUNT(*) FROM
fyi_links
GO
4
DELETE FROM fyi_links
GO
(4 row(s) affected)
SELECT COUNT(*) FROM
fyi_links
GO
0
Are DATETIME and NUMERIC Values Convertible?
Are datetime and numeric value convertible? The
answer is yes. Here are the main rules on DATATIME and NUMERIC value conversions:
- During
the conversion a DATETIME value will be treated as a NUMERIC value with
the number of days relative the base date, Jan 1, 1900 being the integer
part, and the time of the day being the decimal part.
- DATETIME
values can not be converted to NUMERIC values implicitly using assignment
operations.
- NUMERIC
values can be converted to DATETIME values implicitly using assignment
operations.
- DATETIME
values and DATETIME values can be converted to each other explicitly using
CAST() or CONVERT() functions.
The tutorial exercise below shows you some good
examples:
-- Implicit conversion
NUMERIC to DATETIME
DECLARE @birth_date
DATETIME;
SET @birth_date =
36583.25;
SELECT @birth_date;
GO
2000-02-29 06:00:00.000
-- Explicit conversion
NUMERIC to DATETIME
DECLARE @birth_date
DATETIME;
SET @birth_date =
CONVERT(DATETIME, 36583.75);
SELECT @birth_date;
GO
2000-02-29 18:00:00.000
-- Implicit conversion
DATETIME to NUMERIC
DECLARE @birth_date
DATETIME;
DECLARE @birth_days
NUMERIC(9,2);
SET @birth_date =
'2000-02-29 06:00:00.000';
SET @birth_days =
@birth_date;
GO
Msg 257, Level 16, State
3, Line 4
Implicit conversion from
data type datetime to numeric is
not allowed. Use the CONVERT function to run
this query.
-- Explicit conversion
DATETIME to NUMERIC
DECLARE @birth_date
DATETIME;
DECLARE @birth_days
NUMERIC(9,2);
SET @birth_date =
'2000-02-29 18:00:00.000';
SET @birth_days =
CONVERT(NUMERIC(9,2), @birth_date);
SELECT @birth_days;
GO
36583.75
Stored Procedures
What Are Stored Procedures?
A stored procedure is a collection of
Transact-SQL statements that stored in the SQL Server. A stored procedure can
be executed later with an EXEC statement. SQL Server supports stored procedures
with the following features:
1. Stored procedures can be divided into 3
groups based their accessing scopes:
- Permanent
Procedures - Created for permanent use.
- Global
Temporary Procedures - Created for temporary use within all sessions.
- Local
Temporary Procedures - Created for temporary use within the same session.
2. Stored procedures can take input parameters.
3. Stored procedures can take output parameters.
4. Stored procedures can be mapped to references
to Microsoft .NET Framework common language runtime (CLR) methods.
How To Create a Simple Stored Procedure?
If you want to create a simple stored procedure
with no input and output parameters, you can use the "CREATE
PROCEDURE" command with a statement batch in a simple format as shown in
below:
CREATE PROCEDURE
procedure_name AS
statement_1;
statement_2;
...
statement_n;
GO
The following tutorial exercise shows you how to
create a simple stored procedure:
USE FyiCenterData;
GO
CREATE PROCEDURE Hello AS
SELECT 'Welcome to:';
SELECT '
FYIcenter.com';
GO
Command(s) completed
successfully.
EXEC Hello;
GO
-----------
Welcome to;
(1 row(s) affected)
----------------
FYIcenter.com
(1 row(s) affected)
How To List All Stored Procedures in the Current
Database?
If you want to see a list of stored procedures
in your current database, you can use the system view, sys.procedures as shown
in this tutorial exercise:
USE FyiCenterData;
GO
SELECT * FROM
sys.procedures;
How To Drop an Existing Stored Procedure?
If you have an existing procedure that you don't
want to use it anymore, you should delete it from the SQL Server by using the
"DROP PROCEDURE" statement as shown in the tutorial example below:
USE FyiCenterData;
GO
DROP PROCEDURE datetime;
How To Create a Stored Procedure with a
Statement Block?
If you are creating a stored procedure with
multiple statements, it's better to use "BEGIN ... END" to group all
statements into a single statement block.
The tutorial exercise below shows you some good
examples:
USE FyiCenterData;
GO
CREATE PROCEDURE Show AS
SELECT name, type_desc FROM sys.tables;
SELECT name, type_desc FROM sys.views;
SELECT name, type_desc FROM sys.procedures;
END;
How To End a Stored Procedure Properly?
Where the end of the "CREATE
PROCEDURE" statement structure? The answer is simple, the end of the
statement batch.
Even if you are using a "BEGIN ...
END" statement block, the stored procedure structure is not going to end
at the end of the statement block. It will continue to the end of the statement
batch, usually the GO command. The tutorial exercise gives you a good example:
USE FyiCenterData;
GO
DROP PROCEDURE ShowFaq;
DROP TABLE Faq;
GO
How To Generate CREATE PROCEDURE Script on an
Existing Stored Procedure?
If you want to know how an existing stored
procedure was created, you can use SQL Server Management Studio to
automatically generate a "CREATE PROCEDURE" script The following
tutorial shows you how to do this:
1. Run SQL Server Management Studio and connect
to SQL server.
2. On the Object Explorer window, follow the
object tree: Databases > FyiCenterData > Programmability > Stored
Procedures > dbo.ShowFaq.
3. Click right mouse button on dbo.ShowFaq. The
context menu shows up.
4. Select "Script Stored Procedure as"
> "CREATE to" > "New Query Editor Window". The
following script will be displayed:
USE [FyiCenterData]
GO
/****** Object: StoredProcedure [dbo].[ShowFaq]
Script Date: 05/19/2007 21:31:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE
[dbo].[ShowFaq] AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq
(Question VARCHAR(80));
How To Get the Definition of a Stored Procedure
Back?
If you want get the definition of an existing
stored procedure back from the SQL Server, you can use the system view called
sys.sql_modules, which stores definitions of views and stored procedures.
The sys.sql_modules holds stored procedure
definitions identifiable by the object id of each view. The tutorial exercise
below shows you how to retrieve the definition of stored procedure,
"ShowFaq" by joining sys.sql_modules and sys.procedures:
USE FyiCenterData;
GO
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq
(Question VARCHAR(80));
(1 row(s) affected)
How To Modify an Existing Stored Procedure?
If you find a mistake in an existing stored
procedure previously created, you can drop (delete) it and create it again
correctly. But dropping a stored procedure may affect other database objects
who are depending on this stored procedure.
So the best way to correct a mistake in an
existing stored procedure is to use the "ALTER PROCEDURE" statement
as shown in the following tutorial example:
USE FyiCenterData;
GO
-- Finding a mistake -
the last line is wrong
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq
(Question VARCHAR(80));
(1 row(s) affected)
-- Modifying the stored
procedure
ALTER PROCEDURE ShowFaq AS
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
GO
Command(s) completed
successfully.
-- Confirmation of the
correction
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
How To Create Stored Procedures with Parameters?
Very often, you need to create a stored
procedure with one or more parameters. You only supply values to those
parameters at the time of executing the stored procedure.
Stored procedures with parameters can be created
with the following syntax:
CREATE PROCEDURE
procedure_name
@parameter_1 datatype,
@parameter_2 datatype,
...
@parameter_n datatype
AS
statement_1;
statement_2;
...
statement_n;
GO
How To Provide Values to Stored Procedure
Parameters?
If a stored procedure is created with
parameters, you need pass values to those parameters when calling the stored
procedure with one of two formats listed below:
-- Passing values only
EXEC procedure_name
value_1, value_2, ... value_n;
-- Passing name-value
pairs
EXEC procedure_name
@parameter_1 = value_1,
@parameter_2 = value_2,
...
@parameter_n = value_n;
How To Provide Default Values to Stored
Procedure Parameters?
If you add a parameter when creating a stored
procedure, you can provide a default value so that the execution statement is
not required to pass input value to this parameter.
To provide a default value to a parameter, you
should use this format: "@parameter_name data_type = default_value".
The tutorial exercise below shows you how provide default values to stored
procedure parameters:
USE FyiCenterData;
GO
DROP PROCEDURE
diff_in_days;
GO
CREATE PROCEDURE
diff_in_days
@start_date DATETIME,
@end_date DATETIME = '19-May-2007'
AS BEGIN
PRINT CONVERT(VARCHAR(20),@end_date,107)
+ ' - '
+ CONVERT(VARCHAR(20),@start_date,107)
+ ' = '
+ STR(DATEDIFF(DAY, @start_date,
@end_date));
END;
GO
How To Define Output Parameters in Stored
Procedures?
Sometime a stored procedure not only want to
take input values from the calling statement batch, but it also want to send
output values back to the calling statement batch. This can be done by defining
output parameters in the CREATE PROCEDURE statement.
To define an output parameter, you should use
this format: "@parameter_name data_type OUTPUT", as shown in the
following tutorial exercise:
DROP PROCEDURE
diff_in_days;
GO
-- Defining an output
parameter
CREATE PROCEDURE
diff_in_days
@start_date DATETIME,
@end_date DATETIME = '19-May-2007',
@days VARCHAR(40) OUTPUT
AS BEGIN
SET @days =
CONVERT(VARCHAR(20),@end_date,107)
+ ' - '
+ CONVERT(VARCHAR(20),@start_date,107)
+ ' = '
+ STR(DATEDIFF(DAY, @start_date,
@end_date));
END;
GO
How To Receive Output Values from Stored
Procedures?
If an output parameter is defined in a stored
procedure, the execution statement must provide a variable to receive the
output value in the format: "@variable_name OUTPUT" or
"@parameter_name = @variable_name OUTPUT". The following tutorial
exercise gives you a good example:
-- Using @variable
format
DECLARE @message
VARCHAR(40);
EXECUTE diff_in_days
'01-Jan-2007',
'11-May-2007',
@message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01,
2007 = 130
-- Using @parameter =
@variable format
DECLARE @message
VARCHAR(40);
EXEC diff_in_days
@start_date='01-Jan-2007',
@end_date='11-May-2007',
@days = @message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01, 2007
= 130
-- Mixed formats are not
allowed
DECLARE @message
VARCHAR(40);
EXEC diff_in_days
@start_date='01-Jan-2007',
@end_date='11-May-2007',
@message OUTPUT;
PRINT @message;
GO
Msg 119, Level 15, State
1, Line 2
Must pass parameter number
2 and subsequent parameters
as '@name = value'.
After the form '@name = value' has
been used, all
subsequent parameters must be passed in
the form '@name =
value'.
How To Create a Local Temporary Stored
Procedure?
A local temporary stored procedure is a special
stored procedure that:
- Is
created like a normal (permanent) stored procedure with the name prefixed
with a number sign (#).
- Are
only valid in the same client session where it was created.
- Will
be deleted when creating session is terminated.
This tutorial exercise here creates two stored
procedures, one is permanent and the other is local temporary:
DROP PROCEDURE Hello;
DROP PROCEDURE #Hello;
GO
CREATE PROCEDURE Hello
@url nvarchar(40)
AS
PRINT 'Welcome to ' + REVERSE(@url);
GO
CREATE PROCEDURE #Hello
@url nvarchar(40)
AS
PRINT 'Welcome to ' + @url;
GO
EXECUTE Hello
'fyicenter.com';
GO
Welcome to moc.retneciyf
EXECUTE #Hello
'fyicenter.com';
GO
Welcome to fyicenter.com
Can Another User Execute Your Local Temporary
Stored Procedures?
Can another user execute your local temporary
stored procedures? The answer is no.
TRIGGERS
What Are Triggers?
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.
What Are the Basic Features of a Trigger?
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.
·
How To Create a Simple Table to Test Triggers?
·
If you want to follow other tutorial examples included in this
collection, you need to run this SQL script to create a simple table called
fyi_users:
·
USE
FyiCenterData;
·
GO
·
·
DROP
TABLE fyi_users;
·
GO
·
·
CREATE
TABLE fyi_users (
·
id INTEGER IDENTITY NOT NULL,
·
name VARCHAR(80) NOT NULL,
·
email VARCHAR(80) NULL,
·
password VARCHAR(32) NULL
·
);
·
·
INSERT
INTO fyi_users (name) VALUES ('John King');
·
INSERT
INTO fyi_users (name) VALUES ('Nancy Greenberg');
·
GO
·
How To Create a DML Trigger using CREATE TRIGGER Statements?
·
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 fyi_users table. It does nothing but printing a simple static
message.
·
USE
FyiCenterData;
·
GO
·
·
CREATE
TRIGGER dml_message ON fyi_users
·
AFTER
INSERT, UPDATE, DELETE
·
AS
·
PRINT 'Records are inserted, updated,'
·
+ ' or deleted in fyi_users';
·
GO
·
Command(s)
completed successfully.
·
How To Test a DML 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
FyiCenterData;
·
GO
·
·
INSERT
INTO fyi_users (name) VALUES ('FYI Admin');
·
GO
·
Records
are inserted, updated, or deleted in fyi_users
·
(1
row(s) affected)
·
·
UPDATE
fyi_users SET email='root@fyicenter'
·
WHERE name = 'FYI Admin';
·
GO
·
Records
are inserted, updated, or deleted in fyi_users
·
(1
row(s) affected)
·
·
DELETE
FROM fyi_users WHERE name = 'FYI Admin';
·
GO
·
Records
are inserted, updated, or deleted in fyi_users
·
(1
row(s) affected)
·
How To List All Triggers in the Database with sys.triggers?
·
If you want to list all triggers defined in the current database,
you can use the catalog view, sys.triggers, as shown in the following tutorial
example:
·
USE
FyiCenterData;
·
GO
·
·
CREATE
TRIGGER new_user ON fyi_users
·
AFTER
INSERT
·
AS
·
PRINT 'New users added.';
·
GO
·
·
SELECT
* FROM sys.triggers
·
How To Modify Existing Triggers using "ALTER TRIGGER"?
·
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
FyiCenterData;
·
GO
·
·
ALTER
TRIGGER dml_message ON fyi_users
·
AFTER
INSERT, UPDATE, DELETE
·
AS
·
PRINT 'Time:
'+CONVERT(VARCHAR(12),GETDATE());
·
PRINT 'Records are inserted, updated,'
·
+ ' or deleted in fyi_users';
·
GO
·
·
UPDATE
fyi_users SET email='john@fyicenter' WHERE id = 1;
·
GO
·
Time:
Jul 1 2007
·
Records
are inserted, updated, or deleted in fyi_users
How To Delete Existing Triggers using "DROP
TRIGGER"?
If you don't want to use a trigger any more, you
should delete it from the database by using the "DROP TRIGGER" statement
as shown in tutorial example:
USE FyiCenterData;
GO
DROP TRIGGER new_user;
How To Get the Definition of a Trigger Back?
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 FyiCenterData;
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 fyi_users
AFTER INSERT, UPDATE,
DELETE
AS
PRINT 'Time:
'+CONVERT(VARCHAR(12),GETDATE());
PRINT 'Records are inserted, updated,'
+ ' or deleted in fyi_users';
(1 row(s) affected)
How To Disable Triggers using "DISABLE
TRIGGER"?
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 FyiCenterData
GO
-- disabling a trigger
DISABLE TRIGGER
dml_message ON fyi_users;
GO
INSERT INTO fyi_users
(name) VALUES ('Jack Gate');
GO
(1 row(s) affected)
-- enabling a trigger
ENABLE TRIGGER
dml_message ON fyi_users;
GO
INSERT INTO fyi_users
(name) VALUES ('Roy Bush');
GO
Time: Jul 1 2007
Records are inserted,
updated, or deleted in fyi_users
(1 row(s) affected)
How To Create a Trigger for INSERT Only?
The trigger, dml_message, provided in previous
tutorials was defined to handle all 3 types of DML statements, INSERT, UPDATE,
and DELETE.
If you do not want the trigger to handle all 3
types of DML statements, you can list only 1 or 2 of the statement keywords.
For example, the following SQL script defines a trigger that only handle the
INSERT statement events:
USE FyiCenterData
GO
CREATE TRIGGER new_user
ON fyi_users
AFTER INSERT
AS
PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE())
+ ' New users added.';
GO
INSERT INTO fyi_users
(name) VALUES ('Marc Kumar');
GO
Time: Jul 1 2007
Records are inserted,
updated, or deleted in fyi_users
Time: Jul 1 2007
New users added.
(1 row(s) affected)
UPDATE fyi_users SET
email='marc@fyicenter'
WHERE name = 'Marc Kumar';
GO
Time: Jul 1 2007
Records are inserted,
updated, or deleted in fyi_users
(1 row(s) affected)
Notice that the INSERT statement triggered two
triggers to be executed: dml_message and new_user. But the UPDATE statement
triggered one trigger to be executed: dml_message as expected.
It is also interesting to know that when
multiple triggers are defined to handle the same event, the oldest (defined
first) will be executed first.
How To See the Event List of an Existing Trigger
using sys.trigger_events?
If what are the DML events an existing tigger is
handling, you can use the catalog view, sys.trigger_events. You need to join
sys.trigger_events and sys.triggers to get a better list as shown in this
tutorial example:
USE FyiCenterData
GO
SELECT t.name, e.type,
e.type_desc
FROM sys.trigger_events
AS e, sys.triggers AS t
WHERE e.object_id =
t.object_id
GO
name type type_desc
-------------- ------
---------
dml_message 1
INSERT
dml_message 2
UPDATE
dml_message 3
DELETE
new_user 1
INSERT
(4 row(s) affected)
The list clearly shows that dml_message handles
3 events: INSERT, UPDATE and DELETE.
How To Access the Inserted Record of an Event?
When a DML event occurs, SQL Server will prepare
a temporary table called "INSERTED", which contains the new record of
the affected row, which is:
- A
copy of the inserted row for an INSERT statement.
- A
copy of the updated row for an UPDATE statement.
- Empty
for a DELETE statement.
The tutorial exercise below shows you how to
create a trigger, update_user, to report email changes on table, fyi_users:
USE FyiCenterData;
GO
DISABLE TRIGGER
dml_message ON fyi_users;
GO
CREATE TRIGGER
update_user ON fyi_users
AFTER UPDATE
AS
DECLARE @new VARCHAR(80);
SELECT @new = email FROM INSERTED;
PRINT 'Email changed to '+@new;
GO
UPDATE fyi_users SET
email='john@fyicenter'
WHERE name = 'John King';
GO
Email changed to
john@fyicenter
(1 row(s) affected)
As you can see, the INSERTED table is helpful,
if you want the trigger to perform specific logics on the affected rows.
How To Access the Deleted Record of an Event?
When a DML event occurs, SQL Server will prepare
a temporary table called "DELETED", which contains the old record of
the affected row, which is:
- A
copy of the deleted row for a DELETE statement.
- A
copy of the row to be updated for an UPDATE statement.
- Empty
for an INSERT statement.
The tutorial exercise below shows you how to
improve the trigger, update_user, to report email changes on table, fyi_users,
with both old and new emails:
USE FyiCenterData;
GO
ALTER TRIGGER
update_user ON fyi_users
AFTER UPDATE
AS
DECLARE @new VARCHAR(80);
DECLARE @old VARCHAR(80);
SELECT @new = email FROM INSERTED;
SELECT @old = email FROM DELETED;
PRINT 'Email changed from '+@old+' to '+@new;
GO
UPDATE fyi_users SET
email='king@fyicenter'
WHERE name = 'John King';
GO
Email changed from
john@fyicenter to king@fyicenter
(1 row(s) affected)
How To Improve the Trigger to Handle NULL
Values?
When a NULL value is concatenated with a string,
the result will be a null value. So if you want the trigger to properly report
NULL values, you need to enhance the trigger as shown in the following tutorial
example:
USE FyiCenterData;
GO
-- trigger executed but
printed a NULL
UPDATE fyi_users SET
email=NULL
WHERE name = 'John King';
GO
(1 row(s) affected)
ALTER TRIGGER
update_user ON fyi_users
AFTER UPDATE
AS
DECLARE @new VARCHAR(80);
DECLARE @old VARCHAR(80);
SELECT @new = email FROM INSERTED;
SELECT @old = email FROM DELETED;
SELECT @new = ISNULL(email,'NULL') FROM
INSERTED;
SELECT @old = ISNULL(email,'NULL') FROM
DELETED;
PRINT 'Email changed from '+@old+' to '+@new;
GO
-- trigger is reporting
NULL values now
UPDATE fyi_users SET
email=NULL
WHERE name = 'John King';
GO
Email changed from NULL
to NULL
(1 row(s) affected)
What Happens to a Trigger with Multiple Affected
Rows?
If there is only one row affected by a DML
statement, we know that the DML trigger will be executed once. But how many
times the DML trigger will be executed if the DML statement resulted multiple
affected rows? The answer is still one.
In the case of multiple affected rows, both
INSERTED and DELETED tables will contain multiple rows.
If you want your trigger to report all affected
rows, you need to write a loop
USE FyiCenterData;
GO
select * from fyi_users;
-- reporting the first
affected row only
UPDATE fyi_users SET
email=name;
GO
Email changed from NULL
to John King
(5 row(s) affected)
-- reporting all
affected rows
ALTER TRIGGER
update_user ON fyi_users
AFTER UPDATE
AS
SELECT 'Email changed from '
+ ISNULL(d.email,'NULL')
+ ' to '
+ ISNULL(i.email,'NULL')
FROM INSERTED AS i, DELETED AS d
WHERE i.id = d.id;
GO
UPDATE fyi_users SET
email=REVERSE(name);
GO
------------------------------------------------------
Email changed from Marc
Kumar to ramuK craM
Email changed from Roy
Bush to hsuB yoR
Email changed from Jack
Gate to etaG kcaJ
Email changed from Nancy
Greenberg to grebneerG ycnaN
Email changed from John
King to gniK nhoJ
(5 row(s) affected)
(5 row(s) affected)
How To Override DML Statements with Triggers?
Sometime, you may want to implement some
business logics in a DML trigger to cancel the DML statement. For example, you
may want to check the new email address format provided by the UPDATE
statement. If the email address is invalid, you to cancel the UPDATE statement.
There is no easy way to cancel the DML statement
in a DML trigger. But there is easy way to override the DML statement with an
"INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on
when the defined trigger will be fired:
- AFTER
- Trigger fired after the DML statement executed successfully.
- INSTEAD
OF - Trigger fired instead of the DML statement execution, allowing the
trigger to decide whether or not, and how, to execute the statement.
- FOR
- Same as AFTER.
The tutorial exercise below shows you how define
an "INSTEAD OF" trigger on fyi_users to validate email addresses:
USE FyiCenterData;
GO
CREATE TRIGGER
check_email ON fyi_users
INSTEAD OF UPDATE
AS
DECLARE @count INT;
SELECT @count = COUNT(*) FROM INSERTED
WHERE email NOT LIKE '%_@_%';
IF @count = 0
UPDATE fyi_users SET email=i.email
FROM INSERTED AS i
WHERE fyi_users.id = i.id
ELSE
PRINT 'Invalid email(s) found.';
GO
-- invalid email
UPDATE fyi_users SET
email='john.king'
WHERE name = 'John King';
GO
Invalid email(s) found.
-- valid email
UPDATE fyi_users SET
email='john@fyicenter'
WHERE name = 'John King';
GO
Email changed from gniK
nhoJ to john@fyicenter
-- change all
UPDATE fyi_users SET
email='dba@fyicenter';
GO
Email changed from ramuK
craM to dba@fyicenter
Email changed from hsuB
yoR to dba@fyicenter
Email changed from etaG
kcaJ to dba@fyicenter
Email changed from
grebneerG ycnaN to dba@fyicenter
Email changed from
john@fyicenter to dba@fyicenter
The "invalid email" test shows that
trigger check_email did stoped the UPDATE statement. John King's email did not
get updated.
The "valid email" test shows that
trigger check_email properly updated the email column, if the new email is
valid. The reported message was generated from trigger update_user.
How To Create a DDL Trigger using "CREATE
TRIGGER" Statements?
A DDL trigger is defined to handle a DDL
statement event, like create, alter and drop tables, views, indexes, etc. DDL
triggers can be used to generate warning messages on database object changes.
The format of creating a DDL trigger should be:
CREATE TRIGGER
trigger_name ON DATABASE
AFTER ddl_event_types
AS
statements
GO
-- ddl_event_types are
keywords like:
-- CREATE_TABLE,
ALTER_TABLE, DROP_TABLE, ...
Below is a simple example of creating a DDL
trigger to generate messages on ALTER_TABLE events:
USE FyiCenterData;
GO
CREATE TRIGGER
ddl_message ON DATABASE
AFTER ALTER_TABLE
AS
PRINT 'Someone is changing tables!';
GO
ALTER TABLE fyi_users
ALTER COLUMN id INT NOT NULL;
GO
Someone is changing
tables!
Can You Roll Back the DDL Statement in a
Trigger?
Can you roll back the DDL statement in a
trigger? The answer is yes. Since the DDL statement that fires the trigger and
the statements defined inside the trigger are all executed as a single
statement batch, you can add a ROLLBACK statement in the trigger to rollback
the entire batch.
USE FyiCenterData; GO CREATE TRIGGER drop_rollback ON DATABASE
AFTER DROP_TABLE AS PRINT 'Drop table is not allowed!'; ROLLBACK; GO DROP TABLE
fyi_users; GO Drop table is not allowed! Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
Can You Create a Logon Trigger in SQL Server
2005 Express Edition?
Can you create a logon trigger in SQL Server
2005 Express Edition? The answer is no. LOGON is not a supported event type in
Express Edition. The script below shows you the error message when you try to
create a logon trigger:
CREATE TRIGGER welcome
ON ALL SERVER
AFTER LOGON
AS
PRINT 'Welcome to FyiCenterData database!';
GO
Msg 1084, Level 15,
State 1, Procedure welcome, Line 2
'LOGON' is an invalid event
type.
Or may be there is a configuration setting that
disables the logon event type.
USER
DEFINED FUNCATIONS
What Are User Defined Functions?
A user defined function is a collection of
Transact-SQL statements that stored in the SQL Server. A user defined function
will return data when executed.
A user defined function works in the same way as
a system function. It can be used as a scalar expression or a temporary table
depending on the turning data format. A system function is provided as part of
the SQL Server. But a user defined function must be created and managed by
yourself.
If you are using a group of statements to
calculate the same data repeatedly in different places, you should consider to
create a user defined function for that group of statements.
User defined functions are also called
functions.
What Are the Differences between User Defined
Functions and Stored Procedures?
Differences between user defined functions and
stored procedures are:
- Stored
procedures does not return any data and they can not be used in
expressions.
- User
defined functions does return data and they can be used in expressions.
- Stored
procedures only takes data constants as input parameters.
- User
defined functions can take data constants and expressions as input
parameters.
- Stored
procedures support output parameters.
- User
defined functions do not support output parameters.
- Stored
procedures take a parameter's default value, if it is not specified in the
calling statement.
- User
defined functions take a parameter's default value, if it is specified
with the keyword DEFAULT in the calling statement.
- Stored
procedures are called with no parenthesis to include parameters.
- User
defined functions are called with parenthesis to include parameters. Empty
parenthesis is needed if no parameters.
- Stored
procedures can be created locally and temporarily.
- User
defined functions can not be created locally and temporarily
·
How To Create a Simple User Defined Function?
·
If you want to create a simple user defined function, you can use
the "CREATE FUNCTION" command with a statement block in a simple
format as shown in below:
·
CREATE
FUNCTION function_name()
·
RETURNS data_type
·
AS BEGIN
·
statement_1;
·
statement_2;
·
...
·
statement_n;
·
RETURN expression;
·
END;
·
GO
·
The following tutorial exercise shows you how to create a simple
user defined function:
·
USE
FyiCenterData;
·
GO
·
·
CREATE
FUNCTION Welcome()
·
RETURNS VARCHAR(40)
·
AS BEGIN
·
RETURN 'Welcome to FYIcenter.com';
·
END;
·
GO
·
·
PRINT
dbo.Welcome();
·
GO
·
Welcome
to FYIcenter.com
·
How To Use User Defined Functions in Expressions?
·
An user defined function must return a value, which can be used in
any expression as long as the return value data type matches the expression.
·
To execute a user defined function and use its return value in an
expression, you just need to enter the schema name and the function name as a
value in the expression. The tutorial exercise below shows you how use a user
defined function in an expression:
·
USE
FyiCenterData;
·
GO
·
·
--
Calling a function without schema name
·
PRINT
'Hi there, '+Welcome();
·
GO
·
Msg
195, Level 15, State 10, Line 1
·
'Welcome'
is not a recognized built-in function name.
·
·
--
Calling a function with schema name
·
PRINT
'Hi there, '+dbo.Welcome();
·
GO
·
Hi
there, Welcome to FYIcenter.com
·
How To List All User Defined Functions in the Current Database?
·
If you want to see a list of all user defined functions in your
current database, you can use the system view, sys.objects as shown in this
tutorial exercise:
·
USE
FyiCenterData;
·
GO
·
·
--
Number of Sundays in this year
·
CREATE
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
·
·
·
SELECT
* FROM sys.objects WHERE type_desc LIKE '%FUNCTION';
·
GO
·
name object_id
schema_id type type_desc
·
--------
----------- ---------- ---- -------------------
·
Welcome 2085582468
1 FN SQL_SCALAR_FUNCTION
·
Sundays 2117582582
1 FN SQL_SCALAR_FUNCTION
·
(2
row(s) affected)
·
sys.objects contains all types of objects in the current database.
You need select only the FUNCTION object type.
·
How To Drop an Existing User Defined Function?
·
If you have an existing user defined function that you don't want
to use it anymore, you should delete it from the SQL Server by using the
"DROP FUNCTION" statement as shown in the tutorial example below:
·
USE
FyiCenterData;
·
GO
·
·
DROP
FUNCTION Welcome;
·
GO
·
Command(s)
completed successfully.
·
·
SELECT
* FROM sys.objects WHERE type_desc LIKE '%FUNCTION';
·
GO
·
name object_id
schema_id type type_desc
·
--------
----------- ---------- ---- -------------------
·
Sundays 2117582582
1 FN SQL_SCALAR_FUNCTION
·
(1
row(s) affected)
·
User defined function "Welcome" is no longer in the
database.
·
How To Generate CREATE FUNCTION Script on an Existing Function?
·
If you want to know how an existing user defined function was
created, you can use SQL Server Management Studio to automatically generate a
"CREATE FUNCTION" script The following tutorial shows you how to do
this:
·
1. Run SQL Server Management Studio and connect to SQL server.
·
2. On the Object Explorer window, follow the object tree:
Databases > FyiCenterData > Programmability > Functions >
Scalar-valued Functions > dbo.Sundays.
·
3. Click right mouse button on dbo.Sundays. The context menu shows
up.
·
4. Select "Script Function as" > "CREATE
to" > "New Query Editor Window". The following script will be
displayed:
·
USE
[FyiCenterData]
·
GO
·
/******
Object: UserDefinedFunction
[dbo].[Sundays]
·
Script Date: 05/19/2007 23:24:36 ******/
·
SET
ANSI_NULLS ON
·
GO
·
SET
QUOTED_IDENTIFIER ON
·
GO
·
CREATE
FUNCTION [dbo].[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;
·
How To Get the Definition of a User Defined Function Back?
·
If you want get the definition of an existing user defined
function back from the SQL Server, you can use the system view called
sys.sql_modules, which stores definitions of functions, stored procedures, and
views.
·
The sys.sql_modules holds user defined function definitions
identifiable by the object id of each function. The tutorial exercise below
shows you how to retrieve the definition of stored procedure,
"Sundays" by joining sys.sql_modules and sys.objects:
·
USE
FyiCenterData;
·
GO
·
·
SELECT
m.definition
·
FROM sys.sql_modules m, sys.objects o
·
WHERE m.object_id = o.object_id
·
AND o.name = 'Sundays';
·
GO
·
definition
·
-----------------------------------------------
·
CREATE
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,
·
(1
row(s) affected)
·
How To Modify an Existing User Defined Function?
·
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:
·
USE
FyiCenterData;
·
GO
·
·
--
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.
·
How To Create User Defined Functions with Parameters?
·
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
FyiCenterData;
·
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('dba.FYIcenter.com');
·
GO
·
Hi
there, Welcome to dba.FYIcenter.com
·
How To Provide Values to User Defined Function Parameters?
·
If a user defined function is created with parameters, you need
pass values to those parameters when calling the function with one of two
formats listed below:
·
expression...
function_name(value_1, value_2, ... value_n)...
·
The tutorial exercise below shows you how to pass values to
function parameters:
·
USE
FyiCenterData;
·
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('dba.FYIcenter.com');
·
GO
·
Hi
there, Welcome to dba.FYIcenter.com
·
·
PRINT
'Hi there, '+dbo.Welcome('dev.FYIcenter.com');
·
GO
·
Hi
there, Welcome to dev.FYIcenter.com
·
·
How To Use "IF ... ELSE IF ... ELSE ..." Statement
Structures?
·
"IF ... ELSE IF ... ELSE ..." statement structure is
used to select one of the specified statements to be executed based on pecified
Boolean conditions. Here is the syntax of "IF ... ELSE IF ... ELSE
..." statement structure:
·
IF
condition_1 statement_1;
·
ELSE
IF condition_2 statement_2;
·
...
·
ELSE
IF condition_n statement_n;
·
ELSE
statement_o;
·
--
Executes statement_x is
·
if condition_x results in Boolean TRUE
·
The tutorial exercise below shows you how to use an IF ... ELSE
statement structure to selectively execute one of the CREATE TABLE statements:
·
USE
FyiCenterData
·
GO
·
·
DECLARE
@site_name VARCHAR(40);
·
SET
@site_name = 'SQA';
·
IF
@site_name = 'DBA'
·
CREATE TABLE dba_links (url VARCHAR(256));
·
ELSE
IF @site_name = 'SQA'
·
CREATE TABLE sqa_links (url VARCHAR(256));
·
ELSE
·
PRINT 'Unknown site name: '+@site_name;
·
GO
·
Command(s)
completed successfully.
·
How To Use "BEGIN ... END" Statement
Structures?
"BEGIN ... END" statement structure is
used to group multiple statements into a single statement block, which can be
used in other statement structures as a single statement. For example, a
statement block can be used in an "IF ... ELSE ..." statement
structure as a single statement.
The tutorial exercise below shows you how to use
"BEGIN ... END" statement structures to place multiple statements
into an "IF ... ELSE" statement structure:
DECLARE @site_name
VARCHAR(40);
SET @site_name = 'SQA';
IF @site_name = 'DBA'
BEGIN
PRINT 'Dropping table: dba_links';
DROP TABLE dba_links;
END
ELSE IF @site_name =
'SQA'
BEGIN
PRINT 'Dropping table: sqa_links';
DROP TABLE sqa_links;
END
ELSE
PRINT 'Unknown site name: '+@site_name;
GO
Dropping table:
sqa_links
How To Use WHILE Loops?
WHILE statement structure is used to create a
loop to execute a statement or a statement block repeatedly under a specific
condition. WHILE statement structure has the following syntax formats:
1. Loop with a single
statement
WHILE condition
repeating_statement
2. Loop with a statement
block
WHILE condition BEGINE
statement_1
statement_2
...
statement_n
END
The tutorial exercise below shows you how to use
a WHILE statement structure to execute a statement block repeatedly:
-- Counting number of
days in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date =
'2000-01-01';
SET @count = 0;
WHILE DATEPART(YEAR,
@date) = 2000 BEGIN
SET @count = @count + 1;
SET @date = DATEADD(DAY, 1, @date);
END
SELECT @count;
366
-- 2000 is a leap year!
How To Stop a Loop
Early with BREAK Statements?
If you want to stop a WHILE loop early, you can
use the BREAK statement in the loop statement block.
The tutorial exercise below shows you how to use
a BREAK statement to stop a WHILE loop early:
-- Counting number of
days in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date =
'2000-01-01';
SET @count = 0;
WHILE 1=1 BEGIN
IF DATEPART(YEAR, @date) > 2000 BREAK;
SET @count = @count + 1;
SET @date = DATEADD(DAY, 1, @date);
END
SELECT @count;
366
-- 2000 is a leap year!
How To Skip Remaining Statements in a Loop Block Using CONTINUE Statements?
If you want to skip the remaining statements in
a loop block, you can use the CONTINUE statement.
The tutorial exercise below shows you how to use
a CONTINUE statement to skip the remaining statements and continue the next
iteration:
-- Printing first 7
Sundays in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date =
'1999-12-31';
SET @count = 0;
WHILE DATEPART(YEAR,
@date) < 2001 BEGIN
SET @date = DATEADD(DAY, 1, @date);
IF @count = 7 BREAK;
IF DATENAME(WEEKDAY, @date) <>
'Sunday' CONTINUE;
PRINT CONVERT(VARCHAR(40),@date,107);
SET @count = @count + 1;
END
GO
Jan 02, 2000
Jan 09, 2000
Jan 16, 2000
Jan 23, 2000
Jan 30, 2000
Feb 06, 2000
Feb 13, 2000
What Is Open Database Communication (ODBC)?
ODBC, Open Database Communication, a standard
API (application program interface) developed by Microsoft for Windows
applications to communicate with database management servers.
If you want to access a database server through
an ODBC driver from an application program, you need to meet the following
requirements:
- An
ODBC driver specifically designed for the database server.
- An
ODBC DSN (Data Source Name) - an ODBC configuration representing the ODBC
driver and database server.
- An
ODBC API (Application Programming Interface) for your application program
to interact with database server through the ODBC driver.
For example, if you want to a SQL Server in a
PHP script through an ODBC driver, you need to make sure that:
- An
ODBC driver for MS SQL Server - The ODBC driver is installed as part of
the Windows system.
- An
ODBC DSN - You need to create one yourself using the ODBC configuration
tool.
- An
ODBC API in PHP language - The API is installed as part of the PHP engine.
How To Add a New DSN with the ODBC Driver for
SQL Server?
Assuming that the ODBC driver for SQL Server has
been installed as part of the Windows system, the next step of setting up ODBC
connection to SQL Server is to create a new DSN (Data Source Name) with the
ODBC Data Source Administrator:
- Go
to Control Panel.
- Go
to Administrative Tools.
- Run
Data Sources (ODBC). The ODBC Data Source Administrator window shows up.
- Go
to System DSN tab.
- Click
the Add button.
You should a list of all ODBC drivers installed
on your system. SQL Server ODBC driver should be on the list. If you look at
the picture below, you will see the SQL Server ODBC driver with version
2000.85.1117.00, file SQLSRV32.DLL, date 8/4/2004:
Select "SQL Server" from the ODBC
driver list, and click "Finish" button. The "Create a New Data
Source to SQL Server" wizard window shows up. Continue with the next
tutorial to finish up creating a new DSN.
How To Define the Name and Server for a new DSN?
Continuing from the previous tutorial, on the
first screen of the "Create a New Data Source to SQL Server" wizard,
you should enter 3 fields: Name, Description, and Server as suggested below:
Name: FYI_SQL_SERVER
Description:
FYIcenter.com SQL Server
Server: LOCALHOST
See the picture below:
Note that the name and description can be
decided by yourself. But the server must be the network machine name where the
SQL Server is running. In this example, LOCALHOST is the machine name for your
local system.
Click Next and continue with the next tutorial
to finish up creating a new DSN.
How To Provide Login Information for a New ODBC
DSN?
Continue from the previous tutorial. After
clicking Next on the first screen of the "Create a New Data Source to SQL
Server" wizard, you should see the second screen asking you to select SQL
Server login type, login name and password.
Select the radio button on "With SQL Server
authentication using a login ID and password entered by the user. Also enter in
Login ID field: "sa" and in Password field: "FYIcenter".
See the picture below:
Remeber that "sa" is the system
administrator login name, you probably should use a less privileged login name
here. "FYIcenter" must be the correct password defined in the SQL
Server for "sa" login name.
Click Next and continue with the next tutorial
to finish up creating a new DSN.
Why Are You Getting Errors When Creating a New
ODBC DSN?
Continue from the previous tutorial. After
clicking Next on the SQL login information screen, ODBC manager will try to
connect to the SQL Server with the login information you provided.
After a period of waiting time, you may get
error message box saying that:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL
Server Driver][DBNETLIB]
ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL
Server Driver][DBNETLIB]
SQL Server does not exist or access denied.
See the picture below:
Three possible reasons for the failing:
- Wrong
server name - You provided an incorrect server name.
- SQL
Server not configured to take a network connection - You need to check the
SQL Server configuration.
- Wrong
login name or password - You provided incorrect login name or password.
The first and third reasons are easy to validate
and correct. The second reason requires further investigation. Continue with
the next tutorial to configure your SQL Server to take a network connection.
What Are the Requirements on SQL Server Network
Connections?
By default, SQL Server 2005 Express Edition is
installed only one connection protocol enabled:
- Shared
Memory - SQL Server connection protocol for applications that are running
on the same machine where the SQL Server is running. For example, if you
are running SQLCMD tool on the SQL Server machine, it will use the
"Shared Memory" protocol.
If you want application that are running
remotely to connect and access the SQL Server you need enable the
"TCP/IP" protocol.
For applications that are running on the same
machine as the SQL Server, but they do not support "Shared Memory"
protocol, you also need to enable the "TCP/IP" protocol for those
applications. ODBC Manager is an application that requires the
"TCP/IP" protocol to connect to the SQL Server.
In order for the SQL Server to accept network
connections, you also need to run SQL Server Browser Service on the SQL Server
machine.
In summary, there are two requirements for a SQL
Server to accept network connections:
- Start
SQL Server Browser Service on the SQL Server machine.
- Enable
the TCP/IP protocol support on the SQL Server.
·
How To Start SQL Server Browser Service?
·
SQL Server Browser Service is installed as part of the SQL Server.
But it is turned off by default to reduce the security risk. If you want start
SQL Server Browser Service to allow the SQL Server to accept network
connections, you need to follow the steps below:
·
1. Go to Control Panel > Administrative Tools.
·
2. Double click on "Services". The Services window shows
up.
·
3. Double click on "SQL Server Browser". The properties
dialog box shows up.
·
4. Change the "Startup Type" from Disabled to Automatic.
Then click the Start button.
·
The "SQL Server Browser" service should be running now.
·
How To Enable TCP/IP Protocol on a SQL Server?
·
By default, the TCP/IP protocol is turned off when a SQL Server is
installed to reduce security risk. But if you want applications to connect and
access the SQL Server, you need to enable the TCP/IP protocol on the server by
following this tutorial:
·
1. Go to Start > All Programs > Microsoft SQL Server 2005
> Configuration Tools > SQL Server Configuration Manager.
·
2. Double click on "SQL Server 2005 Network
Configuration" to see the list of SQL Server instances.
·
3. Click on "Protocols for SQLEXPRESS". You will see a
list of protocols.
·
4. Right-mouse click on TCP/IP and select the "Enable"
command. See the picture below:
·
5. Click on "SQL Server 2005 Services". You will see two
services.
·
6. Right-mouse click on "SQL Server (SQLEXPRESS)" and
select restart to finish the TCP/IP protocol setting change.
·
Your SQL Server is ready to accept network connection now.
·
How To Verify the Port Number of the SQL Server?
·
When applications use TCP/IP for network communication, you need
to know the port number where the server is listening for connect request from
the client.
·
If you want to connect to the SQL Server through the TCP/IP, you
must know on which port number the SQL Server is listening for connection
requests. Follow this tutorial to find and verify the port number of your SQL
Server.
·
1. Go to Start > All Programs > Microsoft SQL Server 2005
> Configuration Tools > SQL Server Configuration Manager.
·
2. Double click on "SQL Server 2005 Network
Configuration" to see the list of SQL Server instances.
·
3. Click on "Protocols for SQLEXPRESS". You will see a
list of protocols.
·
4. Right-mouse click on TCP/IP and select the
"Properties" command. The properties dialog box shows up.
·
Click the IP Address tab. The server IP address and port number
used by the SQL Server will be displayed. You should see something like:
·
IP
Address: 127.0.0.1
·
Ports:
1269
·
Note that 1433 may also be used as the default port number by your
SQL Server.
·
How To Configure ODBC DSN with Different Port Numbers?
·
If your SQL Server is not using the default port number, like
1269, you need to set the port number to the correct value during the ODBC DSN
creation process, as show in this tutorial:
·
1. Start ODBC Data Source Administrator and click System DSN tab.
·
2. Click Add button, select SQL Server and click Finish button.
·
3. Enter the following and click Next:
·
Name:
FYI_SQL_SERVER
·
Description:
FYIcenter.com SQL Server
·
Server:
LOCALHOST
·
4. Select the radio button on "With SQL Server authentication
using a login ID and password entered by the user. Also enter in Login ID
field: "sa" and in Password field: "FYIcenter".
·
5. Click "Client Configuration..." button. The
"Edit Network Library Configuration" dialog box shows up.
·
Check and uncheck "Dynamically determine port" and enter
"1269" as the Port Number. See picture bellow:
·
Click OK and continue with the next tutorial to finish up ODBC DSN
creation.
·
How To Configure and Test ODBC DSN Settings?
·
Continue from the previous tutorial. Click Next after you have
finished changing the port number. The ODBC Data Source Administrator will try
to connect to the SQL server through the specified port number and login
information.
·
If the connection is successful, you will see the next screen
asking for selecting default database.
·
1. Check "Change the default database to:" check box,
and select "FyiCenterData" as the default database for this DSN
setting. See picture bellow:
·
2. Click Next to see the last screen of the "Create a New
Data Source to SQL Server" wizard.
·
3. Change nothing on the last screen and click Finish button. The
confirmation screen shows up.
·
4. Click "Test Data Source..." button. You should see
the test result as:
·
Attempting
connection
·
Connection
established
·
Verifying
option settings
·
Disconnecting
from server
·
·
TESTS
COMPLETED SUCCESSFULLY!
·
Your FYI_SQL_SERVER ODBC DSN is ready to use.
How To Connect MS Access to SQL Servers through
ODBC?
Once you got a DSN defined in the ODBC manager
that connects to your SQL Server, you can connect a normal MS Access document
to the Oracle server, and link an Access table to a SQL Server table. The
tutorial below gives you a good example:
- Start
MS Access with a new database file.
- Go
to File menu.
- Select
Get External Data.
- Select
Import.... The Import dialog box shows up.
- Select
Files of type: ODBC Database(). The Select Data Source dialog box shows
up.
- Click
the Machine Data Source tab. You should see the DSN name
"FYI_SQL_SERVER" you defined earlier.
- Select
"FYI_SQL_SERVER".
- Enter
User Name: sa.
- Enter
Password: FYIcenter.
Click the OK button to continue. You should see
a list of tables available for you to import from the SQL Server as shown in
the picture below:
Select the table you are interested in and click
OK. You should the selected table being imported from the SQL Server to MS
Access.
How Can Windows Applications Connect to SQL
Servers via ODBC?
One way of connecting a windows application to a
SQL Server is to use ODBC drivers. The requirements to do this is summarized
here:
- The
SQL Server must have TCP/IP protocol enabled with a specific port number.
- The
SQL Server Browser Service must be running on the server machine.
- A
ODBC DSN must be created to represent the connection to SQL Server on the
specified port number.
- The
local windows application must be able to support ODBC API calls.
The diagram below shows how MS Access can
connect to a SQL Server through the ODBC driver:
DIFFERENCES:
Difference Between Primary Key &
Unique Key
Both
primary key and unique key enforce uniqueness of the column on which they are
defined. But by default primary key creates a clustered index on the
column, where are unique creates a nonclustered index by default. Another
major difference is that, primary key doesn't allow NULLs, but unique
key allows only one NULL.
A join selects
columns from 2 or more tables. A union selects rows.
Combines the results of two or more queries into a single result
set consisting of all the rows belonging to all queries in the union
Two basic rules for combining the result sets of two queries
with UNION are:
The number and the order of the columns must be identical in all
queries.
The data types must be compatible.
What are the differences between Self Join and Inner
Join?
Self Join is used to extract data from a single table and
query should be perform on one table
Inner Join is used to extract data from more than one table and
query should be perform on
more than one table.
You can use a WHERE clause in a query containing a GROUP BY
clause. Rows not meeting the conditions in the WHERE clause are eliminated
before any grouping is done
Both the group by and having clause can be applied only on the
aggregate fields
Specifies a
search condition for a group or an aggregate. HAVING is usually used with the
GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause
The ORDER BY clause sorts
query results by one or more columns up to 8,060 bytes. This will happen by the
time when we retrieve data from database. Clustered indexes physically
sorting data, while inserting/updating the table.
Difference between char
and nvarchar / char and varchar data-type?
char[(n)] - Fixed-length
non-Unicode character data with length of n bytes. n must be a value from 1
through 8,000. Storage size is n bytes. The SQL-92 synonym for char is
character.
nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
varchar[(n)] -
Variable-length non-Unicode character data with length of n bytes. n must be a
value from 1 through 8,000. Storage size is the actual length in bytes of the
data entered, not n bytes. The data entered can be 0
Define Candidate key, Alternate key,
and Composite key?
A candidate key is one that can identify
each row of a table uniquely. Generally a candidate key becomes the primary
key of the table. If the table has more than one candidate key, one of them
will become the primary key, and the rest are called alternate keys. A key
formed by combining at least two or more columns is called composite key.
|
DELETE
|
TRUNCATE |
|
1.
Delete deletes each row and logs them
|
Truncate
Logs only page and extent deallocations in to the log
|
|
2.
Delete Trigger is fired for delete command
|
Delete
trigger is not fired for Truncate Command
|
|
Stored Procedures
|
Functions
|
|
Stored procedure returns always
integer value by default zero
|
Function return type could be scalar
or table or table values
|
|
Stored Procedure is pre compiled
execution plan
|
Function is not a pre compiled
execution plan
|
|
Stored Procedure returns more than
one value at a time
|
Function returns only one value at a
time
|
|
We
cannot call sps in sql statements
|
We can call the functions in sql
statements (select max(sal) from emp).
|
|
Procedures
can take both Input and Out Put Parameters
|
Functions can take only Input
parameters
|
|
Procedures
can return any values.
|
Functions
cannot return values of type text, ntext, image & timestamps
|
|
Stored
Procedures cannot be used as user defined datatypes in create table
|
Functions
can be used as user defined datatypes in create table
Ex:-create
table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function
which returns a salary type, when table is created no storage is allotted for
salary type, and getsal function is also not executed, But when we are
fetching some values from this table, getsal function get’s executed and the
return
Type is returned as the result set. |
|
Stored Procedures
|
Triggers
|
|
We
have to call stored procedure manually.
|
Trigger will get execute automatically
when an UPDATE, INSERT, or DELETE statement is issued against a table or view.
|
|
Stored
Procedure allows parameters
|
Triggers
doesn’t have parameters
|
No comments:
Post a Comment