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 (a 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