Now why would it be a bad idea to reset an Identity column to a lower number? To say change it
from 5,000 to 1? Because if your table is not empty, you might create a duplicate. If you have a
unique index, you may recieve an error when inserting. And reusing numbers can cause confusion for
Identity columns are typically used as unique references. Order Numbers, Customer Number,
Receipt Number, Invoice Number, Patient Number, etc. Things where you don't want to confuse two
different entities. Used as surrogate keys with referential integrity they are quite handy.
Reusing a number means they no longer represent unique things.
But if you're using a unique index, your safe right? Not entirely. Say you only have 100 rows
in your table, identities 5,000 to 5,100. For some reason they start at 5,000, and you decide to
reset to 1 without truncating the table, because it looks nice. What happens after 4,999 inserts?
Identity is set to 5,000. But you already have an 5,000 record. So you next insert will fail. As
will all other insert attempts until IDENTITY reaches a number that is not already in use. If
those were sales that you missed out on, that could be a serious loss.
What about in your development environment? I still think you should not reset them there
either. Can all your applications handle high numbers? If you've declared the identity as an INT,
but have used it as a smallint somewhere else, you might never realise until you hit a number
smallint doesn't handle. What about testing procedures? Are they expecting certain low values to
always exist? What about reporting? Can that handle numbers being reused, or does it change
There is one instance where I DO support resetting identity, but only using the DBCC CHECKIDENT ('mytablename') method: after making inserts with
IDENTITY_INSERT ON. In this case you ARE very likely to create duplicate numbers / insert errors.
Immediately after making inserts with SET IDENTITY_INSERT ON, you should reset the identity. Don't
specify a new value and the DBCC CHECKIDENT command will reset it to the maximum current value of
the identity column. This way the next insert will be a unique number.
But please, don't reset IDENTITY to a number lower than it's current value. You really don't
gain anything by having identity columns that start at 1. It doesn't use any less resources to
start at 1 than it does to use 1,000,000.
Microsoft has quietly kicked out the final version of SQL Server 2005 and Visual Studio 2005 to MSDN subscribers ahead of the Launch Tour 2005. The .Net 2.0 Framework has also been released, which you will need to install/use either of these great things.
If you've been keeping track of SQL Server 2005's development, you'd know that the last three years have seen a lot of new features added. Service Broker, XQuery, Partitioning, Mirroring, Snapshots, .Net Integration, and new Management tools. Even DTS got a make over so severe it also got a new name: SQL Server Integration Services (SSIS).
If you haven't had the chance to cut your teeth on SQL Server 2005, I'd really recommend installing the RTM. Now that its stable you can install it side-by-side with your existing SQL Server 2000 instances (or by itself in a shiny new server). The new management interface (SQL Server Management Studio) might take a bit of getting used to but it can be used with 2005 or 2000 installations.
Installing the SQL Server 2005 BOL help requires the .Net 2.0 Beta Framework, which you might not wish to install yet. In my case, I have a virtual machine with a full 2005 install, but on my physical machine, I often need to refer to 2005 BOL. If you used to refering to the MSDN website for SQL Server 2000's Books Online (BOL) for help, you'll have probably noticed that the BOL for SQL Server 2005 isn't available there yet.
The reason is that Microsoft are planning to launch a new MSDN website along with Visual Studio 2005 (around the same time SQL Server 2005 is launched). In the meantime the new MSDN website, dubbed MSDN2, is available online in its beta form. It's a bit buggy at times, but is supposed to provide better access than the current MSDN when it is launced.
SQL Server 2005's BOL is available on the new site, along with Visual Studio 2005 help. Take a look and remember to give feedback on any bugs you find so they can be fixed in time.
The September CTP will be the last CTP/Beta before Microsoft releases the RTM/Gold version in November. Its available in the same five flavours as before: Enterprise, Standard, Workgroup, Express, and Developer.
Microsoft have said that much touted Database Mirroring feature, an alternative to Clustering with Log Shipping which works with physically separated hardware, will slip from the initial release. It's expected to ship in the first half of 2006, which can be thought of as our first Service Pack for SQL Server 2005.
We're also expecting around that time a cut-down version of SQL Server 2005 Managment Studio to replace the existing Express management tools. This should make looking after Express installs much easier, and more inline with the other versions of SQL Server.
The September CTP itself should be a seemless upgrade (without uninstall) from the June CTP. June CTP upgrade from April's seemed to go well for me, so I'm hoping the same can be said for what will undoubtably be a more polished product. If you are planning to uninstall your previous ctp/beta installation, there are some things you should consider first.
If you've scripted out a stored procdure, you'll have noticed the SET ANSI_NULLS ON or SET QUOTED_IDENTIFIER ON options are also scripted. If your a verteran SQL coder, no doubt you set these before you create any stored procedures. But once a stored procedure is created, how do you determine what options were set?
Well in SQL Server 2005 you will need to use the sys.sql_modules catalogue view. the sys.sql_modules view will show you the options set for definitions of:
Functions (scalar / table)
The code below shows how to view the options of your objects.
SELECT syso.name, type, sysm.*
FROM sys.sysobjects AS syso
INNER JOIN sys.sql_modules AS sysm
ON syso.id = sysm.object_id
Extended Properties in SQL Server give you the ablility to attach a comment or description to an object in the database. When you use Management Studio to add a Description to a table or column, it is stored as an Extended Properties with the name of MS_Description, and the object it relates to.
You can use the fn_listextendedproperty() table function to return the extended properties for the database, or a specific table or column. But what if you want to return all comments for all column and tables in the database? Well you'll need to use the system tables directly to make that happen.
To select all the extended properties for all the columns and tables in the database, use this command:
SELECT OBJECT_NAME(EXP.major_id) AS TableName,
C.name AS ColumnName,
EXP.name AS PropertyName,
EXP.value AS PropertyValue
FROM sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON C.object_id = EXP.major_id
AND C.column_id = EXP.minor_id
WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'
This is a really neat addition to SQL Server 2005. Database Snapshots are essentially a read-only, static view of an existing database, without duplicating all the data on disk.
I use this feature a lot when I upgrade database schemas, or make major changes to data, and need to be able to compare the changes afterwards. If you had a 3rd party tool (or an untrusted developer) that upgrades an existing database, you could use this to see what changes occur.
Now database snapshots are only available in the Enterprise and Developer editions of SQL Server 2005, and is really usefull in both these setups. Developers benefit from being able to compare database changes to a prior state. In Enterprise production, you can use the snapshot for reporting purposes or with Analysis Services. The read-only snapshot will not suffer from locking issues that you might experience with active OLTP databases.
Creating a snapshot of an existing database is really simple.
CREATE DATABASE database_snapshot_name
NAME = logical_file_name,
FILENAME = 'os_file_name'
AS SNAPSHOT OF source_database_name
So why the need for a filename if we're creating a read-only view of the source database? We'll before a data-page is modified in the source database, the original page is copied to the snapshot database. This "copy-on-write operation" is what ensures the data in the snapshot database remains constant.
SQL Server Database Snapshots use the NTFS file system "sparce file" feature to create a file on disk that appears the same size as the original database, but doesn't actually consume disk space until a data-page is modified in the source. You can check this by viewing the snapshot physical file properties in windows explorer (the Size on Disk represents how much disk space is really being consumed). Or you can use the T-SQL fn_virtualfilestats function. Should every page in your source database be modified, your snapshot file will at most consume the size of the original source database at the time of the snapshot creation.
Now if you really wanted to, you can even restore your source database from the snapshot. While you couldn't use snapshots in place of a proper backup stratergy: if you are testing a change script over and over, it might be quicker to restore from the snapshot itself. In that case a simple
RESTORE DATABASE source_database_name
FROM DATABASE_SNAPSHOT = database_snapshot_name
will bring your source database back to its previous state.