Add Column

Adding column
You can add new columns to an existing table. .

ALTER TABLE [dbo].[phone]
ADD inactive_date DATETIME NULL
GO

Alter column
You can add modify an existing column

ALTER TABLE [dbo].[person]
ALTER COLUMN [lastname] VARCHAR(35) NULL
GO

Considerations for altering a column
1. Reducing precision (example, going from CHAR(20) to CHAR(15)) can cause data truncation and should be avoided unless you are absolutely sure their will be no impact to the data.
2. Changing data types should typically be avoided. There are exceptions to this. For example, changing a CHAR(20) to a VARCHAR(20) on columns where the average storage length is 10 can save disk space.

Alter columns - No Can Do
You cannot directly alter a a column that is part of the primary key

MERGE

The MERGE statement allows developers to use one command to perform deterministic inserts, updates, and deletes on a table based on a source table.

When synchronizing some information across two tables, up to three operations have to be performed. First, any new rows need to be inserted into the target table. Then existing rows have to be updated. Finally, old rows no longer in use may need to be deleted. This can lead to a lot of repetitive logic which needs to be maintained, and in turn can lead to subtle bugs.

merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"

Trigger Uses

Triggers are useful in these ways:

Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.


Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.
Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.


Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

Override ToString method

This C# code snippet overrides the ToString method inherited from System.Object in order to display the private variables.

public class MyClass
{
private string customer ="";
private int customerID = 0;

public string Customer
{
get { return customer; }
set { customer = value; }
}

public int CustomerID
{
get { return customerID; }
set { customerID = value; }
}

public override string ToString()
{
return string.Format ("Customer = {0} ID = {1}", Customer, CustomerID);
}

}

Guidelines for Partitioned Tables

You must plan to create the following database objects before partitioning a table or index:

Partition function
Partition scheme

A partition function defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.

A partition scheme maps each partition specified by the partition function to a filegroup.

Planning the Partition Function
There are two factors to consider when planning a partition function: the column whose values determine how a table is partitioned, known as the partitioning column, and the range of values of the partitioning column for each partition. This range of values determines the number of partitions that make up your table. A table can have a maximum of 1,000 partitions.

The choices you have for the partitioning column and the values range are determined primarily by the extent to which your data can be grouped in a logical way, such as by date, and whether this logical grouping is adequate for managing subsets of data.

For example, under the partitioning scenario of the AdventureWorks sample database, the TransactionHistory and TransactionHistoryArchive tables are partitioned on the TransactionDate field. The range of values for each partition is one month. The TransactionHistory table maintains the year's most current transactions, while TransactionHistoryArchive maintains older transactions. By partitioning the tables in this way, a single month's worth of old data can be transferred quickly and efficiently from TransactionHistory to TransactionHistoryArchive on a monthly basis.


For more information check out the below link
http://msdn.microsoft.com/en-us/library/ms180767.aspx

Logon Triggers

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login. For example, in the following code, the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

Check out the below link for more information on this
http://msdn.microsoft.com/en-us/library/bb326598.aspx