uniqueidentifier as input

Question

uniqueidentifier as input

sql_2005_fan

SSCommitted

Points: 1871
More actions
April 28, 2011 at 11:49 am

#237233

I have procedure which has uniqueidentifier as one of the input parameters.
CRETAE PROCEDURE testsp
@input1 uniqueidentifier
AS
SET NOCOUNT ON
INSERT INTO testtbl(uniid)
VALUES(@input1)
GO
I should send the uniqueidentifier value for this procedure as input,right?
or will not generate any random value?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

sql_2005_fan (4/28/2011)
I have procedure which has uniqueidentifier as one of the input parameters.
CRETAE PROCEDURE testsp
@input1 uniqueidentifier
AS
SET NOCOUNT ON
INSERT INTO testtbl(uniid)
VALUES(@input1)
GO
I should send the uniqueidentifier value for this procedure as input,right?
or will not generate any random value?
Thanks.

Yes, you should send the unique identifier. However, the table could generate uniqueidentifier values if it has NewID() as the Default value for the column. You should check if you are concerned. You should also make sure there are no triggers generating a value for that column. That is if you're generating new random uniqueidentifiers yourself. If you have valid values that already exist, you should just pass them.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

Alternatively, to reduce index fragmentation you could use NEWSEQUENTIALID()

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

MysteryJimbo (4/28/2011)
Alternatively, to reduce index fragmentation you could use NEWSEQUENTIALID()

If and only if they are generating new uniqueidentifier values for these inserts. If there are existing ids, those should be sent as strings. For example

EXEC testsp 'AC288DAC-0AD7-4D31-A501-DE9CD2BC91F8'

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

Stefan Krzywicki (4/28/2011)
MysteryJimbo (4/28/2011)
Alternatively, to reduce index fragmentation you could use NEWSEQUENTIALID()
If and only if they are generating new uniqueidentifier values for these inserts. If there are existing ids, those should be sent as strings. For example
EXEC testsp 'AC288DAC-0AD7-4D31-A501-DE9CD2BC91F8'

I think you've already covered that in your previous post. I was giving an alternative option to newid() which potentially gives less fragmentation.

From past experience guids are slow to query and consume far too much space.

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 5

@input1 uniqueidentifier

when the input paramter is not set to null, we should definitley send the value for it,right?

@input1 uniqueidentifier=null

then we dont need to send the value to this parameter,right?

Thanks.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 6

MysteryJimbo (4/28/2011)
Stefan Krzywicki (4/28/2011)
MysteryJimbo (4/28/2011)
Alternatively, to reduce index fragmentation you could use NEWSEQUENTIALID()
If and only if they are generating new uniqueidentifier values for these inserts. If there are existing ids, those should be sent as strings. For example
EXEC testsp 'AC288DAC-0AD7-4D31-A501-DE9CD2BC91F8'
I think you've already covered that in your previous post. I was giving an alternative option to newid() which potentially gives less fragmentation.
From past experience guids are slow to query and consume far too much space.

Ah, sure. That is a good point.

I agree. I tend to stay away from guids whenever possible.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 7

sql_2005_fan (4/28/2011)
@input1 uniqueidentifier
when the input paramter is not set to null, we should definitley send the value for it,right?
@input1 uniqueidentifier=null
then we dont need to send the value to this parameter,right?
Thanks.

Right, if the variable has a default value such as NULL, then you don't need to send the value. But if you're the one writing or modifying this, you should make sure that

A: the table allows NULL values in that field

B: that data isn't required for something else even if the column allows NULLs

If it is not set to NULL you have to send the value.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams