Sunday, August 4, 2013

COLUMN PROPERTIES

COLUMN PROPERTIES

Column Property
Description
NOT NULL
Wont't allow NULL.
Default is NULL; can be changed by
 
ALTER DATABASE <
> SET ANSI_NULL_DEFAULT OFF -- Default NOT NULL
ALTER DATABASE <
> SET ANSI_NULL_DEFAULT ON -- Default NULL

SET ANSI_NULLS ON
 -- can not be compared with ColName = NULL (default)
SET ANSI_NULLS OFF -- can be compared with ColName = NULL (Avaible in that session only)
IDENTITY
For autoincremental value.
Can have only one IDENTITY column per table
SET IDENTITY_INSERT 
ON -- To insert desired value
SET IDENTITY_INSERT 
OFF -- will take value automatically

DBCC CHECKIDENT (table_Name, RESEED, value) -- to set the next desired valu
NOT FOR REPLICATION
Can be applied on IDENTITY Column.
By specifiying this, SQL Server won't reseed the value when replication happens
SPARSE
applicable on nullable columns
Generally NULL occupies some space. When we specifiy SPARSE, null record won't occupy any space; however non-nullable records will occupy 4 bytes in addition to actual storage.
Can be used only when we have high amount of nullable records.
Cannot be applied on IDENTITY, ROWGUID, GEOMETRY, GEOGRAPHY, HIERARCHYID, TIMESTAMP, IMAGE, TEXT, NTEXT, VARBINARY(MAX) WITH FILESTREAM, Computed column, userdefined data type
ROWGUIDCOL
Can be used only with UNIQUEIDENTIFIER datatype.
Only 1 per table
FILESTREAM
Can be applied on VARBINARY(MAX) column.
For that table should have ROWGUID column
Stores the file in Filesystem and maintains the reference in table. This is the best approach to store the Files rather than using Windows File System or complete database approach















COMPUTED COLUMN
CREATE TABLE Comp1 (int, b int, c as (a+b) persisted)
If persisted specified, then SQL server stores the actual result of the calculation and will be updated automatically whenever changes happen in the columns being used in calculaton.
If persisted not specified, SQL server stores only calculation and while execution, it will calculate the values