Wont't allow NULL.
Default is NULL; can be changed by
ALTER DATABASE <
ALTER DATABASE <
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)
For autoincremental value.
Can have only one IDENTITY column per table
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
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
Can be used only with UNIQUEIDENTIFIER datatype.
Only 1 per table
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
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