2

I have the following table in SQL Server:

 CREATE TABLE [dbo].[tblTempPo](
    [TempPoID] [int] IDENTITY(1,1) NOT NULL,
    [guid]  AS ([dbo].[GetIdentity]()),
    [Qty] [int] NULL,
    [MobileBrandID] [int] NULL,
    [MobileID] [int] NULL
)

I need to insert the current row number to the guid column every time a new row is added. I tried to use the following function but it's not working as expected:

ALTER FUNCTION GetIdentity() 
RETURNS INT AS
BEGIN
   RETURN (SELECT top 1 ROW_NUMBER() OVER(ORDER BY TempPoID asc)FROM tblTempPo)
END
Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
chamara
  • 12,649
  • 32
  • 134
  • 210
  • 8
    The column is called `guid` but it stores an `int` ?!?!? That'll be a maintenance nightmare down the line!! You should always adhere to the **Principle of Least Surprise** - if something is **called** `guid` - everyone would expect it to be of type `Guid`, too ..... – marc_s Jan 25 '12 at 05:54
  • 1
    Also: doing this every time a row is added will be a HUGE performance bottleneck! **WHY** do you need to **store** this?? Why can't you just compute it (in your query) when needed, on the fly, and be up to date all the time?? – marc_s Jan 25 '12 at 05:55
  • If you want to have UniqueId for `Guid` column you can set the default value as `NEWID()`. – Amar Palsapure Jan 25 '12 at 05:59
  • I tend to agree with marc_s, but if you REALLY wanna do this, consider two things: store the row number on an int column, not called "Guid" and NOT being a primary key (as he said); and also consider doing it on a post-adding trigger, so the DB does it for you automagically. – CMPerez Jan 25 '12 at 08:40

3 Answers3

1

Your function GetIdentity() will probably always return 1 but it is not a sure thing because you are using select top 1... without an order by clause.
If you want the highest value returned by row_number() you need to add order by 1 desc which would be the same as doing SELECT count(*) from tblTempPo.

Fixing GetIdentity() like that will not help much in your situation because [guid] AS ([dbo].[GetIdentity]()) will give you a computed column that is evaluated every time you query the table and not when you insert a new row. You will always have the same value for all rows.

You could use a function that takes the TempPoID as a parameter as a computed column.

CREATE FUNCTION GetIdentity(@P int) 
RETURNS INT AS
BEGIN
   RETURN (SELECT rn
           FROM (SELECT TempPoID,
                        ROW_NUMBER() OVER(ORDER BY TempPoID ASC) AS rn
                 FROM tblTempPo) AS T
           WHERE TempPoID = @P)
END

Table definition:

CREATE TABLE [dbo].[tblTempPo](
    [TempPoID] [int] IDENTITY(1,1) NOT NULL primary key,
    [guid] as dbo.GetIdentity(TempPoID),
    [Qty] [int] NULL,
    [MobileBrandID] [int] NULL,
    [MobileID] [int] NULL

I have never used this so I can't tell you if it is a good thing to do or not. It might be devastating for your query performance, I just don't know.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

It's all no good idea, but if you REALLY need it, try trigger:

create table [dbo].[tblTempPo](
[TempPoID] [int] identity(1,1) NOT NULL,
[guid]  int,
[Qty] [int] NULL,
[MobileBrandID] [int] NULL,
[MobileID] [int] NULL
)
go

create trigger [dbo].[tblTempPo_Trig] on [dbo].[tblTempPo] instead of insert as
declare @cnt int
select @cnt = count(*)
from [dbo].[tblTempPo] with(nolock)
insert into [dbo].[tblTempPo]([guid], [Qty], [MobileBrandID], [MobileID])
select @cnt+row_number() over (order by [TempPoID]), [Qty], [MobileBrandID], [MobileID] from inserted
go

insert into [dbo].[tblTempPo]([Qty], [MobileBrandID], [MobileID]) values (0, 0,0), (0, 0,0), (0, 0,0), (0, 0,0)
insert into [dbo].[tblTempPo]([Qty], [MobileBrandID], [MobileID]) values (0, 0,0), (0, 0,0), (0, 0,0), (0, 0,0)

select * from [dbo].[tblTempPo]

go
drop table [dbo].[tblTempPo]
go
dizzy128
  • 289
  • 3
  • 11
0

I agree with comments and you should change the GUID column name. If you really have to store the TempPoID column twice use computed column. Example:

CREATE TABLE #tblTempPo (
    [TempPoID] [int] IDENTITY(1,1) NOT NULL,
    [second_id] AS TempPoID,
    [Qty] [int] NULL,
    [MobileBrandID] [int] NULL,
    [MobileID] [int] NULL
)

INSERT INTO #tblTempPo (Qty, MobileBrandID, MobileID) VALUES 
(10, 10, 15), (20, 23, 45), (55, 23, 12), (10, 1, 1)

SELECT * FROM #tblTempPo

DROP TABLE #tblTempPo

If you need more complicated approach - use trigger.

BartekR
  • 3,827
  • 3
  • 24
  • 33