Just a day ago while working with database Jr. Developer asked me question how to add column along with column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.
It is very easy to add column and specify default constraint. I have seen many examples where constraint name is not specified, if constraint name is not specified SQL Server will generate unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here : SQL SERVER Database Coding Standards and Guidelines Complete List Download
ALTER TABLE TestTable ADD NewCol VARCHAR(50) CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL GO
Each organization has its own coding standards and enforcement rules. It is sometime difficult for DBAs to change code following code review, as it may affect many different layers of the application. In large organizations, many stored procedures are written and modified every day. It is smart to keep watch on all stored procedures, at frequent intervals, before code comes to final code review. Pre-code reviewing in this manner will save lots of time. I run a few scripts every day to check the status of the all stored procedures on our development server. Doing so gives me a good indication about which stored procedures are not up to coding standards.
SQL Server offers three recovery models: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable and determines whether and how transaction logs can be backed up.
Select Simple Recovery Model if:
* Your data is not critical.
* Losing all transactions since the last full or differential backup is not an issue.
* Data is derived from other data sources and is easily recreated.
* Data is static and does not change often.
Select Bulk-Logged Recovery Model if:
* Data is critical, but logging large data loads bogs down the system.
* Most bulk operations are done off hours and do not interfere
with normal transaction processing.
* You need to be able to recover to a point in time.
Select Full Recovery Model if:
* Data is critical and no data can be lost.
* You always need the ability to do a point-in-time recovery.
* Bulk-logged activities are intermixed with normal transaction processing.
* You are using replication and need the ability to resynchronize all
databases involved in replication to a specific point in time.
You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set. ALTER DATABASE Pubs SET RECOVERY FULL GO