SQL AUTO INCREMENT Field

Auto-increment permits a unique number to be generated when a new record is inserted into a table.

AUTO INCREMENT a Field

Very frequently we would like the value of the primary key field to be created automatically every time a new record is inserted.

We would like to create an auto-increment field in a table.

Syntax for SQL Server

The following SQL statement defines the "EmpID" column to be an auto-increment primary key field in the "Employee" table:

CREATE TABLE Employee

(

EmpID Bigint IDENTITY(1,1) PRIMARY KEY,

LastName Nvarchar (200) NOT NULL,

FirstName Nvarchar (200),

Address Nvarchar (500),

CityCode Nvarchar(3)

)

The SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "EmpID" column should start at value 200 and increment by 1, change it to IDENTITY(200,1).

To insert a new record into the " Employee " table, we will NOT have to specify a value for the "EmpID" column a unique value will be added automatically.

SQL Constraints

SQL Constraints

SQL constraints are used to identify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is terminated by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

 In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value for a column

SPARSE COLUMN-SQL SERVER 2008

SPARSE COLUMN-SQL SERVER 2008

SQL SERVER 2008's new feature SPARSE COLUMN. Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.
• A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that's still a net savings.
• Storing a null in a sparse column takes up no space at all.
• To any external application the column will behave the same
• Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
• You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.
• Change Data Capture and Transactional replication both work, but not the column sets feature.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes. A table operation which involves SPARSE column takes performance hit over regular column.
• If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
• Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse. (Changed17/5/2009 thanks Alex for spotting the typo)
• computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
• You can't apply rules or have default values.
• Sparse columns cannot form part of a clustered index. If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
• Merge replication doesn't work.
• Data compression doesn't work.
• Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.

·          

Syntax to create sparsed columns

CREATE TABLE Sparsed(ID INT IDENTITY(1,1),

FirstCol INT SPARSE,

SecondCol VARCHAR(100) SPARSE,

ThirdCol SmallDateTime SPARSE)

GO

SQLFacts: Injection in SQL SERVER

SQLFacts: Injection in SQL SERVER: SQL injection is a technique where  hacker  can inject SQL commands into an SQL statement, via web page.Injected SQL commands can comprom...

SQLFacts: SQL Server Exception Handling by TRY…CATCH

SQLFacts: SQL Server Exception Handling by TRY…CATCH: SQL Server Exception Handling by TRY…CATCH Like  C#, SQL Server also has an exception model to handle exceptions and errors that occur...