Implementing a default value for a column, if a value is not passed, is a great best practice when developing systems, because all of the logic is inline to the table. As such it is not necessary to write complex logic if a value is not passed and if a NULL value is not inserted.
To find the values it is necessary to query the syscolumns table for the particular column. The syscolumns table resides in the user database and stores one record for each column in a specific table. The syscolumns table also has a column named 'cdefault' that serves as an ID for the column and references syscomments. The syscomments table stores the definition of the default in the 'text' column.
For more information about defaults visit:
Creating and Modifying DEFAULT Definitions
For More Information
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2004