July 7, 2009 at 12:15 am
hi
I have a table column with default value newsequentialid() , but I need to get the new GUID generated value first before inserting new records.but calling it from a sql query issues that error:
"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression."
I read that NewID(), and identity() can be called from sql queries but I NewID is slower in performance , and identity misses some GUID feature -as I guess.I also can't use "default" keyword before insertion statement.
so,is there any way to call newSequentialId() directly or indirectly?
thanks all
July 7, 2009 at 1:15 am
Im a bit confused by exactly what you are trying to do.
Please post DDL and a reproduction script.
July 7, 2009 at 2:06 am
CREATE TABLE [dbo].[TMovie](
[Key] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TMovie_Key] DEFAULT (newsequentialid()),
[Name] [varchar](100) NOT NULL,
[Source] [varchar](50) NOT NULL CONSTRAINT [DF_TMovie_Source] DEFAULT (app_name()),
CONSTRAINT [PK_TMovie] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
July 7, 2009 at 2:28 am
I think i see what you are now saying...
Select newSequentialId()
Errors, Correct ? This obviously fails as it has no idea which table you need the sequentialId for.
If you need the value , i think your best bet is to capture it with the OUTPUT Clause as Below
drop table tmovie
go
CREATE TABLE [dbo].[TMovie](
[Key] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TMovie_Key] DEFAULT (newsequentialid()),
[Name] [varchar](100) NOT NULL,
[Source] [varchar](50) NOT NULL CONSTRAINT [DF_TMovie_Source] DEFAULT (app_name()),
CONSTRAINT [PK_TMovie] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] ;
--TEXTIMAGE_ON [PRIMARY]
go
declare @NewVal table
(
[Key] uniqueidentifier not null,
[Name] varchar(100) not null,
[Source] varchar(50) not null
)
insert into TMovie
OUTPUT inserted.[Key],inserted.[Name],inserted.[Source]
into @NewVal
values(default,'King Kong',default)
select * from @NewVal
Hope that helps
July 8, 2009 at 10:33 am
Waleed,
NEWID() and NEWSEQUENTIALID() are both used to generate GUIDs, NEWSEQUENTIALID() was introduced in SQL Server 2005 to generate sequential GUID's. The advantage of using Sequential GUIDs is it performs much better than GUID because there are lesser page splits as compared to GUID's. The only downside is you could potentially guess the next sequential id's and also the fact that you can't use it in SELECT statement. GUID's on the other hand are purely randomly generated sequence but results in frequent page splits thereby degrading the DB performance.
Hope this helps.
Thanks,
Amol
Amol Naik
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply