Odds are that at least some of your tables
contain columns whose values are defaulted. Common defaults include
GetDate(), which returns the current system date and time, and
User_Name(), which returns the login of the current user.
You can default money columns to zero. This is
controversial because, on one hand, doing so simplifies various
calculations since you don’t need to worry about NULL values; on
the other hand, some database designers despise this kind of thing
and insist that NULL and zero are distinctly different values.
Suppose you create a table with columns that
all have default values. For example:
CREATE TABLE [dbo].[TestDefaults2] (
[PK] [int] IDENTITY (1, 1) NOT NULL
[DateEntered] [datetime] NOT NULL,
[SomeText] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EnteredBy] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[TestDefaults2] ADD
DEFAULT (getdate()) FOR
DEFAULT (‘This is some text’) FOR
DEFAULT (user_name()) FOR
) ON [PRIMARY]
Paste this code into Query Analyzer and run it
against a test database. Then, run Enterprise Manager, open your
test database, expand the Tables node, and open the table. (If you
prefer, you can use Access. Create an ADP file that talks to the
test database and then open the table.) When you try to insert a
row, you’ll see that you can’t. It seems that SQL Server won’t let
you insert a row into a table whose every column has a default.
However, that’s not the case. You can’t insert a row interactively,
but you can insert a row using the following special syntax:
INSERT TableName DEFAULT VALUES
There are several reasons why you may want to
insert rows into a completely defaulted table. For example, this
might be an audit-trail table with extra columns in which the
update, insert, or delete occurs; in which case, this code might be
a trigger rather than a stored procedure.
Add columns to suit your requirements, and then
default them or alternatively write a stored procedure to add the
undefaulted values using parameters.
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!