start IDENTITY values at a new seed

This can be useful if you want to create an artificial gap between your current seed and your desired next identity value.

SET IDENTITY_INSERT [myTable] OFF INSERT INTO [myTable] (id,other)     VALUES(@NewSeed,'something') SET IDENTITY_INSERT ON [myTable] ON

In this case, the id column will continue incrementing from the vaue of @NewSeed.

If you want to remove all entries in a table and start over at 1 (or a non-default seed), you can do one of two things:

DROP TABLE [myTable] CREATE TABLE [myTable] (     -- Column definition ) -- or TRUNCATE TABLE [myTable]

Note that TRUNCATE TABLE will not work in particular scenarios, e.g. if another table has a foreign key constraint pointing at the table you're trying to reset (even if there is no data representing that relationship). What you can do instead is:

DELETE [myTable] -- assuming you want to clear the data DBCC CHECKIDENT('myTable', RESEED, 0)

The final parameter is a little awkward. If you want to make sure that the next IDENTITY value that gets generated is 1, you set this value to 0 (this is the typical case). If you want to inject a gap into the IDENTITY sequence, let's say you want the next IDENTITY to be 25000, you would say:

DBCC CHECKIDENT('myTable', RESEED, 24999)

Of course, that all assumes that your increment value is 1. If your increment value is 50, you would use 24950 instead of 24999.

It's not a great idea to reseed an IDENTITY sequence *lower* than any values that currently exist in the table. You might get a little surprise when your counter gets back up that high and hits an existing value...

---

For Access, you can reset the AUTONUMBER column by compacting and repairing the database (see Article #2190). Though with certain version / JET combinations, this can cause the engine to fill gaps when you start inserting again. KB #257408 explains this to some degree, but states that it will only use numbers lower than the max that was ever in the table, if all higher numbers have been deleted (e.g. won't fill gaps). There are several documented cases out on Google where users have observed their gaps being filled in, even when higher numbers still exist in the data. So I wouldn't count on your gaps staying intact.

source: http://sqlserver2000.databases.aspfaq.com/can-i-start-identity-values-at-a-new-seed.html

SQL Server Int Maximum Value

bigInt -9223372036854775808 through 9223372036854775807 (8 bytes)
int -2147483648 through 2147483647 (4 bytes)
smallInt -32768 through 32767 (2 bytes)
tinyInt 0 through 255 (1 byte)

Reset all identities in SQL Server

As using query from http://beyondrelational.com/justlearned/posts/169/get-all-the-tables-and-columns-having-identity-propertly-sql-server.aspx , herewith script to reset all identities for all tables in database.

01.DECLARE @sql varchar(8000)

02.DECLARE @tbl VARCHAR(4000)

03.DECLARE @col VARCHAR(4000)

04.DECLARE c_ident cursor FAST_FORWARD FOR 

05.SELECT table_name,column_name

06.FROM

07.INFORMATION_SCHEMA.COLUMNS

08.WHERE

09.COLUMNPROPERTY -- checking for identity = 1

10.(

11.OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name))

12.,column_name

13.,'isidentity'

14.) = 1

15.OPEN c_ident

16.FETCH NEXT FROM c_ident INTO @tbl,@col

17.WHILE @@FETCH_STATUS = 0

18.BEGIN

19.SET @SQL = '

20.DECLARE @RESEEDIDENT BIGINT

21.SET @RESEEDIDENT  = (select ISNULL(max('+@col+'),0) from '+@tbl+')

22.DBCC CHECKIDENT ( '''+@tbl+''', RESEED, @RESEEDIDENT )

23.'

24.PRINT (@SQL)

25.EXEC(@SQL)

26.FETCH NEXT FROM c_ident INTO @tbl,@col

27.END

28.CLOSE c_ident

29.DEALLOCATE c_ident




source: http://beyondrelational.com/modules/1/justlearned/388/tips/8593/reset-all-identities-in-sql-server.aspx

Webhosting Performance Tunings For IIS7

Source: http://blogs.iis.net/mukhtard/archive/2009/04/21/webhosting-performance-tunings-for-iis7.aspx


In a webhosting environment you have hundreds of web sites on a single server. Each web site may not see a significant traffic but when you combine the traffic to all the sites on the server it is quite significant and so it is necessary to tune the server for a webhosting environment.
Here are a few performance related tunings that you can make to help performance and host more sites on your IIS7 server:
1. Make sure your server is running a 64-bit operating system(OS) thus allowing the OS to address more than 4GB of virtual address space. Run your application pools as SysWow64 aka 32-bit on 64-bit windows OS. The advantage in running as SysWow64 is that when compared to the native 64-bit the worker process is light weight – lower memory foot print - allowing more number of worker processes to run at any given time.
2. Perhaps the most important feature for web hosting environment is the new dynamicIdleThreshold feature in IIS7. In a web hosting environment you can divide all active(running) sites on a web server into two categories: hot and cold. Hot sites are the ones which are frequently visited while the cold ones see very low traffic. When hundreds of worker processes are spawned to serve these sites, available memory on the system starts running thin. At one point the system will run out of memory and the performance of the running active sites will suffer. Furthermore, new requests to new sites will not be honored. The dynamicIdleThreshold feature allows you to work around this problem. This feature keeps track of how much memory is being used on the system, and if it reaches a particular set threshold, it cuts down the idle timeout for the application pools, thus shutting down the worker processes which meet the new lower idle timeout. Let’s see in detail how the feature works:
By default the feature is disabled, so the default value for dynamicIdleThreshold is 0. The value set for this attribute is interpreted as ‘% of physical memory(RAM) committed’. So, what is committed memory, it is the processes virtual memory allocations for which the OS has allocated(or committed) a page in the physical memory and/or in the page file. The dynamicIdleThreshold feature will kick in when the total committed memory reaches 80% of the value that is set for the dynamicIdleThreshold. Let’s take an example to understand this better:
Let’s say we have a machine with 2GB physical memory and we set the dynamicIdleThreshold to 150. So the feature will kick in when the total committed memory reaches 80% of 3GB(150% of 2GB) which is 2.4GB. Note that the limit for the committed memory is greater than your physical memory, this limit is typically the sum of the physical memory(excluding the system memory part) and total paging files size on the machine. The following table lists, by how much the idle timeout will be cut, remember that it is windows process activation service(WAS) which is doing the idle timeout chopping:

dynamicIdleThreshold percentage reached	Action
80%	WAS sets idle-timeout of all worker processes to ½ of original value.
85%	WAS sets idle-timeout of all worker processes to 1/4th of original value.
90%	WAS sets idle-timeout of all worker processes to 1/8th of original value.
95%	WAS sets idle-timeout of all worker processes to 1/16th of original value.
100%	WAS sets idle-timeout of all worker processes to 1/32 of original value.

On the other side should the committed memory usage fall below 75% of the configured dynamicIdleThreshold value WAS will restore the original idle timeout settings.
In our internal testing we have seen that setting the dynamicIdleThreshold to around 130 gives optimum performance in a webhosting environment. Use trial and error to find out what will be a good value for your webhosting environment.
So as you can see from the above description the feature limits the number of worker processes that can be run at any point of time by shutting down the least used processes and thus allowing new requests to be served. Compare this to Windows 2003 where in, once you have run out of memory, new request would see ‘service unavailable’ errors, you have to wait till a worker process times out and shuts down freeing up memory for a new worker processes to be launched. Thus on Windows 2008 if you enable this feature you are pretty much guaranteeing that a new request will be served even if the memory is scarce.
3. The new configuration system in IIS7 now supports thousands of sites and application pools that could be used in the web hosting scenario. IIS7’s configuration system is scalable to thousands of sites. You might want to use the new API’s like Microsoft.Web.Administration namespace, Microsoft.ApplicationHost.WritableAdminManager or the appcmd tool to provision hundreds of sites at a time instead of the older API’s like ADSI or WMI which are quite slow when it comes to provisioning new sites and applications.