Random Unique Values In SQL Server 2005

Jan 14, 2008

 

Hi I am using VS 2005 with SQL 2005.

I had a Datagrid and i Need to bind data into it.

I need to show Random Unique ID's based on the Parameter i pass.

I wrote select top 5 * from Employee order by NEWID()

for this i had created a Stored Procedure as

create Proc Demo(@N int)
as begin
select top @N * from Employee order by NEWID()
end

Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......

View 6 Replies


ADVERTISEMENT

Unique Random Row Id

May 4, 2006

I once came across a database in which when ever a row was inserted, a unique random code was also inserted in a column. This code generated was similar to format like {8A5FE5F1-6BDA-476C-A4DB584A7710FBF91121054625}. I was told that SQL2000 has some inbuilt functionality for this.

Any idea on how to do this using SQL2000 or coding.
I feel this is much better than Identity Column as using autonumbers in quesrystring is too much risky as they easily readable.

Please help.
Thanks

View 1 Replies View Related

Create A Unique Random Integer

Oct 22, 2004

Hi there,I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key. I have written the following function, but when i call it as a default value for a field, it does not produce a unique number. CREATE FUNCTION GETNEXTID(@CURDATE DATETIME)RETURNS BIGINTASBEGINRETURN (SELECT CAST(CAST(DATEPART(YY,@CURDATE) AS VARCHAR) +RIGHT('0' + CAST(DATEPART(M,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(D,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(HH,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(SS,@CURDATE) AS VARCHAR),2) +RIGHT('00' + CAST(DATEPART(MS,@CURDATE) AS VARCHAR),3) AS BIGINT))END Can anyone help?

View 2 Replies View Related

How To Random Increment Unique Identity

Aug 28, 2007

Hi, for some reason I want to have a unique ID with a seed and random Identity increment (I want an ascending ID's but without able to know how many objects there are).

any ideas?
thanks in advance

View 12 Replies View Related

Generate Random And Unique Number Within Sql ?

Dec 11, 2007

I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?

If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.

thoughts ?

View 5 Replies View Related

Transact SQL :: Set Unique Random Number

Dec 1, 2015

Lets say we have a table (tblProducts)

ID   Item             RandomNumber
-------------------------------------------
1    JEANS                      1234567
2    SHIRT                    72813550
3    HOOD                             Null
4    TROUSER               72191839
5    BLAZER                              0

I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query

select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts

So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.

View 15 Replies View Related

How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies View Related

SQL Server 2012 :: How Update Works For Unique Values

Nov 25, 2014

Look at the following code,

Create table #test
(
id int primary key,
Name varchar(100)
)
insert into #test values (1,'John')
insert into #test values (2,'Walker')

[Code] ....

-- Query 1 :
update #test set name = 'Joney' where id = 1

-- Query 2 :
set rowcount 1
update #test set name = 'Joney' where id = 1
set rowcount 0

1. #test table have primary key & clustered index.
2. Obviously only one row will be available for an id.
3. In query 1, will the sql server look for matching rows even after it found 1 row?
4. Will query 2 really gains some performance?

View 5 Replies View Related

Unique Constraint Doesn't Allow Multiple Null Values In Server?

Jun 2, 2014

Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?

View 2 Replies View Related

Random Values

Dec 24, 2001

Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')

thanks in advance

View 2 Replies View Related

Random Values In MS SQL,

Jan 1, 2005

Hello guys , I am new here,

Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.

See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,

Also I don't know how can I add that Random thing *See the arrow in the screenshot*

Thanks in advance

View 2 Replies View Related

Random Values

Mar 8, 2005

I have a table with 3 fields SiteID (PK), SiteCode, SiteName.
For each SiteCode I have several rows with different SiteName.
For example: 1 327 a
2 327 b
3 327 c
4 328 aa
5 328 bb......

I need to retrieve each time SiteCode with random SiteName.
For example: 327 b , 327 a , 327 c
328 aa 328 bb 328 aa.

I need it very urgent, so any ideas will be very helpful. Thanks to all,

View 1 Replies View Related

Random Values

Dec 4, 2007

Hello,

i want to anonymous data by multiplicating with a random generated value. After a little bit research i know that is only solvable with a script task, but i dont have seen a working example script. Does anybody knows where i can found a little scriptlet or post something? Thank you!

Jonas

View 5 Replies View Related

Random Connection Drop With ODBC And SQL Server 2005

Oct 29, 2007



We have a legacy C++ application which uses ODBC to connect to SQL server. The application is a server process that opens a connection and caches it. Unfortunately on a connection drop, it does not refresh the connection. The standard installation is a 3 machine web/app/sql setup.

It has been working fine since last 4-5 years unless the SQL Server goes down. In that case, I keep seeing "Function sequence error" and "Communication link failure" errors. On one particular client installation on application server, I started getting this exception while SQL Server was up and running (on a different machine). I would see the connections randomly dropping in the activity monitor after 3-4 hrs of inactivity.

I wrote a .NET test app which uses ODBC drivers for SQL Server and makes a connection and then just waits. Out of 5 connections established by this application I saw one connection being dropped as well. Another test app used ADO.NET and it did not drop any connections. I asked the IT to check the network and they say its fine.

The servers are W2k3 SP2 and database is SQL Server 2005. IsAutoClose on all the databases is false. Nothing goes in the logs even after turning on traces using DBCC.

Since the connection drop was very random, I turned off connection pooling on "ODBC driver for SQL Server" on the application server and now it is working fine.

Can someone explain me what could be the reason? Also, is there a way to upgrade or downgrade ODBC drivers on W2K3 machines?

Thanks,
Pranav

View 8 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

Insert Random Values In A Table

Dec 14, 2007

Hi all!

I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.

what i tried didnt work, it always produced the same value.



Maybe you have an idea

Thankx in advance and greetings from Vienna

Landau

View 2 Replies View Related

Set A Attribute To Random Values In A Given List

Jul 20, 2005

Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing

View 1 Replies View Related

SQL 2012 :: Create Random Alphanumeric Characters For Primary Key Values

Feb 11, 2015

For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.

eg: cmSbXsFE3l8

It can start from 4 digit characters and can grow to 6, 7 as required

The database will involve more than 10000 concurrent users.

I don't want to use guid or auto increment integer or sequence.

View 9 Replies View Related

Need Random/unique &&"ID's&&" For Key Verification

Aug 6, 2007

I need to be able to create completely random and unique keys for a key verification system, which would require a user to enter a pre-defined key in order to activate their account, but I need to be able to create those keys on the fly.

This is going to be a key that will be mailed to them on paper, and unfortunately means it needs to be relatively short in order to prevent too much confusion while they are typing it in.

I like the newID() function in SQL, but the ID that it creates is a bit excessive to say the least for someone to have to type when registerring.

I use C#, so I wouldn't have much of a problem creating a small app to create x number of keys, which will sit in the DB until I need them, but I would rather not have to fill the DB with a million or so ID's which might never be used, and don't want to create too little that I have to track when I might need to add more, in case I start to run low on ID's.

Re-using ID's may be an option, but I would prefer to keep them intact for the life of the accounts.

If there is something that I can do to simulate the newID() function, but generate unique/random ID's which look more like this: A97-2C5-77D than this: A972C577-DFB0-064E-1189-0154C99310DAAC12 I would be very grateful to know about it.

Thanks!

View 1 Replies View Related

SQL Server Express 2005 And Unique ID's

Dec 4, 2006

On SQL Server I can create a table and for the primary key it allows me to select AutomatedInt (or something to that effect) and I don't have to worry about unique id's for my table.However in Microsofts free version, SQL Server Express 2005, the automated int isn't an option.... There is a uniqueidentifier field but it doesn't seem to work like automatedint and it is giving me error messages.Any ideas?

View 2 Replies View Related

Inserting Unique Values

Apr 24, 2001

What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.

Suggestions are much appreciated.

Ivan

View 2 Replies View Related

Unique Values Between Two Tables.

Jul 20, 2005

Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.

View 2 Replies View Related

Unique Values Query

Apr 21, 2006

Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.

Thanks

View 5 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

SQL Server 2005; Unique Constraint, Covering Index Question

Aug 9, 2006

Hi,

When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following...

ALTER TABLE PersonsProjects
WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID)

...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows:

CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY

But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column?

I would rather not have two indexes...

NoDupes: PersonID,ProjectID

New Index: PersonID,ProjectID INCLUDE Status

...so I'm trying to determine what other options that might be available...please advise.

Thanks much.

View 10 Replies View Related

Get Unique Values In A Group Statement

Jul 23, 2006

Hi,
Suppose a table [Inventory]:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:


Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis Perrakis

View 10 Replies View Related

Finding All Unique Combinations Of Values?

Mar 29, 2013

how to find all possible combinations of values, for example:

My table includes:

Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);

I need the output to be like this:

apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry

I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.

View 5 Replies View Related

SQL 2012 :: Generate Unique Values

Apr 23, 2014

I am working on an ASP.net web application which inserts new record into an underlying table.

It is actually a ConfirmationNumber and should be unique. I have used abs(checksum(newid()))

For this purpose. Is there a better way to accomplice this?

View 9 Replies View Related

Return Unique Count (Two Values)

Sep 6, 2013

I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.

ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04

I need two results.

Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.

View 3 Replies View Related

Analysis :: How To Get Unique Values For Each Measure

Sep 2, 2015

I am getting same value all over how to get unique values for each measures.

how to resolve it.

View 5 Replies View Related

Help With A Join To Return Unique Values

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 7 Replies View Related

Finding UNIQUE Values In Only One Column

Jan 28, 2008

I am using Access 2007 and I have 10 columns of data.

I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.

I have used this

SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];

But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.

I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.

I know that I am missing something basic but well, I can't figure it out.

Thanks

Bob

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved