Default Value RAND issue

Question

Default Value RAND issue

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 22, 2016 at 9:33 am

#325869

What is wrong with this Statement?
ALTER TABLE tblCall
ADD CONSTRAINT DF_ReinspectTag
DEFAULT (RAND) FOR ReinspectTag
Msg 128, Level 15, State 1, Line 219
The name "RAND" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

RAND() is a function. You need those parentheses.

Also note that if you create such a column and insert multiple rows in the same statement, they will all have the same "random" value.

Cheers!

MadAdmin SSChampion Points: 11261 More actions · Answer 2

guessing that it should be (rand())

Will check ina bit.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

MadAdmin SSChampion Points: 11261 More actions · Answer 3

Jacob Wilkins (4/22/2016)
RAND() is a function. You need those parentheses.
Also note that if you create such a column and insert multiple rows in the same statement, they will all have the same "random" value.
Cheers!

Bugger.

Beat my by 29 seconds.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

MadAdmin (4/22/2016)
guessing that it should be (rand())
Will check ina bit.

Thanks it worked.

Does this look right?

ALTER TABLE tblCall

ADD CONSTRAINT DF_ReinspectTag

DEFAULT (rand()) FOR ReinspectTag

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 5

multi row inserts will insert the same default value form RAND() for all rows in the insert statement.

is that ok? (oops! tip of the hat to Jacob, who noticed the same logical error first!)

create table #example(exampleid int identity(1,1) not null primary key,

sometext varchar(128),

someRand float default rand() )

insert into #example(sometext)

SELECT top 5 name from sys.tables

select * from #example

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

create table #example(exampleid int identity(1,1) not null primary key,

sometext varchar(128),

someRand int default Abs(Checksum(Newid()) % 90000000 ))

insert into #example(sometext)

SELECT top 5 name from sys.tables

select * from #example

drop table #example

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 7

Welsh Corgi (4/22/2016)
MadAdmin (4/22/2016)
guessing that it should be (rand())
Will check ina bit.
Thanks it worked.
Does this look right?
ALTER TABLE tblCall
ADD CONSTRAINT DF_ReinspectTag
DEFAULT (rand()) FOR ReinspectTag

It works but no... it doesn't look right. Why would you be using a random value for a "ReinspectTag"???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (4/22/2016)
Welsh Corgi (4/22/2016)
MadAdmin (4/22/2016)
guessing that it should be (rand())
Will check ina bit.
Thanks it worked.
Does this look right?
ALTER TABLE tblCall
ADD CONSTRAINT DF_ReinspectTag
DEFAULT (rand()) FOR ReinspectTag
It works but no... it doesn't look right. Why would you be using a random value for a "ReinspectTag"???

And, moreover, a random value between 0 and 1 that won't be unique.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 9

An interesting method I've used before is to use NEWID() in ORDER BY.

In this example I select the 6 winning numbers of the next lotto draw!

WITH MyNumbers (n) AS

(

--Only 49 possible numbers on lotto ticket!

SELECT TOP 49 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)

)

SELECT TOP 6 n FROM MyNumbers ORDER BY NEWID()

This way you won't get repeated values if that is a requirement.

SELECT quote FROM brain WHERE original = 1
0 rows returned