sql server data warehouse: We have a table which data is over a million but less 2 billion. We define an
integer field to hold primary keys but we do not like to choose GUID or
IDENTITY.

Anybody knows any other simple ways to generate unique key?

Thanks.

Use a trigger on insert and write your logic there

Yan,
depending on your volumes you may/may not choose to use triggers as
suggested.

The problem with triggers are that they are fired for each insert.
Also, if you unload data, delete rows, and reload data you get
different keys. I have always believed (and in his latest book Ralph
Kimbal also makes this point) that integer keys for dimension tables
are best allocated in the ETL subsystem. There are a bunch of reasons
for this, many of them in Ralphs book.

The most important one, my opinion, is that the ETL sub-system then
controls and maintains the allocation of all keys and the ETL Designer
has full control over all keys.

I would recommend that the keys are allocated inside the ETL subsystem.
I have published C code on my web site for how this is done for type 2
dimensions...but how it is done depends on the ETL tool you choose...

Best Regards

Peter Nolan
www.peternolan.com

Hello Yan,

One idea you may want to try is creating a User Defined Function to
generate the format of you identity column. With a UDF it can be used
as a column default.

I am currently knocking up some sample code.

Also try posting on SQL programming news group

Hope this helps

Myles Matheson
Data warehouse Architect
http://bi-on-sql-server.blogspot.com/

Hello Yan,

I was a bit wrong with the UDFs it is possible but for life I can't
get it tonight. Anyway below is a stored procedure that will do the
same as the Identity function in SQL. You can modify it to return any
range and format you want.
---------------------------------------------------------------------------
-- Identity Parameters Code
-- Version: 1.0
-- Date 1/08/2005
-- By Myles Matheson
-- http://bi-on-sql-server.blogspot.com/
--
-- This is script is without any warranty and like anything else
-- must be tested before use in a production system.

---------------------------------------------------------------------------


-- Create Table
CREATE TABLE [IdentityParameters] (
[TableName] [char] (10) NOT NULL ,
[TableColumnName] [char] (10) NOT NULL ,
[TableColumnLastValue] [char] (10) NOT NULL
) ON [PRIMARY]
GO

-- Insert Sample Values
-- TRUNCATE TABLE IdentityParameters
INSERT INTO [dbo].[IdentityParameters]
([TableName], [TableColumnName], [TableColumnLastValue])
VALUES('Product', 'ProductID', '10001')
GO


CREATE PROCEDURE spIdentityParameters
@TabName CHAR (10), @ColName CHAR (10)

AS
DECLARE
@CurrentID CHAR (10)
, @NextID CHAR (10)
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- NOT sure if this is
correct.
-- Need to maintain lock on IdentityParameters for the duration of the
transaction

-- Get the current id from IdentityParameters
SET @CurrentID = (SELECT TableColumnLastValue FROM IdentityParameters

WHERE @TabName = TableName
AND @ColName = TableColumnName)

-- SET Next ID to be incremented in any format.
-- Note this could be using a Aplha Numeric format.
-- This is just a counter for this example
SET @NextID = CAST (@CurrentID AS INT) + 100


-- Update Identity Parameters with the next id
--Should still be about of the same transaction
UPDATE IdentityParameters
SET TableColumnLastValue = @NextID
WHERE (@TabName = TableName
AND @ColName = TableColumnName)

SELECT @NextID AS ColID-- Return the new ID
END

go

-- Test code to return new ID
EXECUTE dbo.spIdentityParameters 'Product', 'ProductID'

-- Clean up
-- DROP TABLE IdentityParameters
-- DROP PROC spIdentityParameters

----------------------------------------------------------------------------------

Hope this helps

Myles Matheson
Data warehouse Architect
http://bi-on-sql-server.blogspot.com/

Hi All,

I appreciate all of your help.

Peter, we have not used any ETL tools yet. Could you tell me more about how
keys are allocated inside the ETL subsystem?

And Myles, I had the same idea to use store procedure. But it will be the
last choice if I can not find any better and easy ways.

Thanks again.

Yan

Yan,

Have a close look at SQL Server 2005, Integration services. It offers
way more features and functions than DTS.

Check out a look at this web article. See:
http://www.sqlis.com/default.aspx?37

Remember ETL tools can come at considerable cost on top of the DB. Some
tools require there own server to run independently of the DB server.

Peter is right to recommend them if you finding limitations with SQL.

On that what are the problems you are finding with the Identity
function?


Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Why dont you like IDENTITY?

In different ETL it can relay on different things. Oracle Warehouse
Builder uses Sequences.

You can simulate sequence in MSSQL by creating a stored function/proc.
But it will not be so effective.



[quoted text, click to view]