Index autoincrement for Microsoft SQL Server 2008 R2
In SQL Server, it's not a separate datatype ("autoincrement") - but you can define an INT
column to be an IDENTITY
.
How are you creating your table - visual designer or T-SQL script??
In T-SQL, you would use:
CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1) ......
and in the visual table designer, you need to check:
It's an option for a column of type INT - you can define the seed (starting value) and the increment - typically both are set to 1.
Reset autoincrement in Microsoft SQL Server 2008 R2
If you use the DBCC CHECKIDENT
command:
DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);
But use with CAUTION! - this will just reset the IDENTITY
to 1 - so your next inserts will get values 1, then 2, and then 3 --> and you'll have a clash with your pre-existing value of 3 here!
IDENTITY
just dishes out numbers in consecutive order - it does NOT in any way make sure there are no conflicts! If you already have values - do not reseed back to a lower value!
Auto-increment primary key in SQL tables
- Presumably you are in the design of the table. If not: right click the table name - "Design".
- Click the required column.
- In "Column properties" (at the bottom), scroll to the "Identity Specification" section, expand it, then toggle "(Is Identity)" to "Yes".
Auto increment primary key in SQL Server Management Studio 2012
Make sure that the Key column's datatype is int
and then setting identity manually, as image shows
Or just run this code
-- ID is the name of the [to be] identity column
ALTER TABLE [yourTable] DROP COLUMN ID
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)
the code will run, if ID
is not the only column in the table
image reference fifo's
Autoincrement uniqueidentifier
Or even better: use the newsequentialid()
as the default for your UNIQUEIDENITIFER column. That'll give you a somewhat sequential series of GUIDs.
CREATE TABLE dbo.YourTable
(SerialID UNIQUEIDENTIFIER
CONSTRAINT DF_SerialID DEFAULT newsequentialid(),
.... (other columns)......
)
Trouble is: newsequentialid is only available as a column default - you cannot call it as a function or anything. But that seems to fit your requirements.
UPDATE: there appears to be an acknowledged bug in SQL Server Management Studio that prevents specifying newsequentialid()
as the default for a column in the interactive table designer.
See: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/cad8a4d7-714f-44a2-adb0-569655ac66e6
Workaround: create your table without specifying any default, and then type in this T-SQL statement in a normal query window and run it:
ALTER TABLE dbo.YourTable
ADD CONSTRAINT DF_SerialID DEFAULT newsequentialid() FOR SerialID
That should do the trick!
BULK INSERT with identity (auto-increment) column
Don't BULK INSERT into your real tables directly.
I would always
- insert into a staging table
dbo.Employee_Staging
(without theIDENTITY
column) from the CSV file - possibly edit / clean up / manipulate your imported data
and then copy the data across to the real table with a T-SQL statement like:
INSERT INTO dbo.Employee(Name, Address)
SELECT Name, Address
FROM dbo.Employee_Staging
For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC
In theory, they will use same plans and run almost same time.
In practice,
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
will more probably use a PRIMARY KEY INDEX
.
Also, this one is more extendable if you will decide to select some else column along with id
.
An actual plan on MAX()
says:
SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN
, while plan for TOP 1
says:
SELECT <- TOP <- CLUSTERED INDEX SCAN
, i. e. aggregate
is omitted.
Aggregate actually won't do anything here, as there is but one row.
P. S. As @Mehrdad Afshari
and @John Sansom
noted, on a non-indexed field MAX
is slightly faster (of course not 20
times as optimizer says):
-- 18,874,368 rows
SET LANGUAGE ENGLISH
SET STATISTICS TIME ON
SET STATISTICS IO ON
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
Changed language setting to us_english.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
MAX
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 20 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 447, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5452 ms, elapsed time = 2766 ms.
TOP 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6813 ms, elapsed time = 3449 ms.
MAX
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 44, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5359 ms, elapsed time = 2714 ms.
TOP 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6766 ms, elapsed time = 3379 ms.
MAX
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5406 ms, elapsed time = 2726 ms.
TOP 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6780 ms, elapsed time = 3415 ms.
MAX
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5392 ms, elapsed time = 2709 ms.
TOP 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6766 ms, elapsed time = 3387 ms.
MAX
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5374 ms, elapsed time = 2708 ms.
TOP 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6797 ms, elapsed time = 3494 ms.
Related Topics
In MySQL: How to Pass a Table Name as Stored Procedure And/Or Function Argument
Rails Scope to Check If Association Does Not Exist
Multiple Tables Need One to Many Relationship
SQL Server Decimal(30,10) Losing Last 2 Decimals
How to Add 10 Seconds in Current_Timestamp SQL ( Oracle )
Subquery in SQL Server Compact Edition
Using 'In' with a Sub-Query in SQL Statements
Merge Identical Databases into One
SQL Server Management Studio - How to Change a Field Type Without Dropping Table
How to Format a Numeric Column as Phone Number in SQL
Trying to Sum Distinct Values SQL
SQL Get "Iso Year" for Iso Week
Rand Not Different for Every Row in T-SQL Update
How to Set the Default Schema of a Database in SQL Server 2005
MySQL - Change Date String to Date Type in Place
Window Functions: Partition by One Column After Order by Another