Sql-server – In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to

Some info, if not a definitive answer

It's been blogged recently

• When are Statistics Updated – What triggers Statistics to Update
• Comment from Paul Randal

There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:

One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?

HTH...

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well:

CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO

INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2

Now I can also change the primary key and repeat the process:

ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA  
  PRIMARY KEY (targetID);

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2
1     41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA   3

So I haven't had to change my bad code that uses insert/select without any column lists, as long as the source table doesn't also change and assuming that the only change to the target is the addition of an identity column.

PS here is how you can automate the generation of the IDENTITY columns (assuming you will want <tablename>ID):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS 
(
  SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id] 
    AND (is_identity = 1 OR name = t.name + 'ID')
);

SELECT @sql;
-- EXEC sp_executesql @sql;

(Note that the SELECT output will show you roughly what the command looks like, but due to output limitations in SSMS and depending on how many tables you have, it won't necessarily show you the full command that will get executed when you uncomment the EXEC.)

And the drop / re-create of the primary keys:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT ' 
  + k.name + ' PRIMARY KEY (' + t.name + 'ID);' 
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables

SELECT @sql;
-- EXEC sp_executesql @sql;

And you'll want to do this while the database is in SINGLE_USER mode or while the application(s) are otherwise not able to connect to the database. You'll also want to test all this on a QA or dev system before unleashing any of it on production.

Now, this still isn't exactly best practice - I highly recommend you stop embedding SQL code in your apps, especially SQL code that does insert/select without specifying column lists.