How to insert records on SQL Server identity column?

Scenario:

Insert records in SQL Server table with pre-defined primary key on an identity column. For instance, the the data ContactId to insert is from 11-50. How to insert the ContactId as my identity column value is 150 now? The assumption here is existing ContactId  in the range could be deleted for the new INSERT.

 Concepts:

Identity column on a table increment the value automatically for each record INSERT into the table. For example, the table "Contact" has an identity column defined on ContactID column. The column is defined to increase the max identity value by 1. This is done by specifying the increment value to 1.

The seed value lets you specify the identity column starting value. For instance, I would like to start all data from ContactId = 100.

The T-SQL table create statement looks like below:

Solution:

Turn the IDENTITY_INSERT ON to tell the server that you want to insert specific ID value. Perform the record insert. Enable the identity columns auto value by setting the IDENTITY_INSERT OFF.

Syntax:
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Example:

Further references:

MSDN Table Identity Column
MSDN IDENTITY_INSERT

SQL Server Table Fragmentation

How do you find out the % fragmentation of your table? 

SQL Server stores your table data rows on data page that is normally 8Kb/Page. These 8k pages form a 64Kb container call Extent.

use dbcc showcontig ('YOUR_TABLE_NAME').


If it's fragmented, then run DBCC REINDEX. DBCC REINDEX reorganises your database table and index like defraging your hard drive.


This table describes the information in the result set. Microsoft owns the following tables. More info here.

Statistic	Description
Pages Scanned	Number of pages in the table or index.
Extents Scanned	Number of extents in the table or index.
Extent Switches	Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent	Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]	Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation	Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation	Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page	Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full)	Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

SQL2008 Intellisense Quick Fix

I opened a query window under SQL2008 Management studio. Typing "Select * from " , presses ctrl+space bar to bring up the table intellisense. It's not displaying the table in the list (although I have ran the use command on a database).

What have I done?

1. use AdventureWorks; // this connect my query window the database I wish to use.
   
2. Type select * from + [Ctrl+ space bar]. My table that resides in the AdventureWorks db doesn't exist in the drop-down list (as shown below, the list doesn't show tblApplication).

What are the work-arounds?

1. Use full schema in select definition:
Type select * from AdventureWorks.dbo.tblApplication

2.Work-around I found by accident.
Leave the use command on the first line. Type select * from" and ctrl+space. It shows tblApplication in the intellisense result.