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.
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?
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).
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.
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.
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?
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')
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,
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!
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?
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.
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.
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
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.
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?
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.
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.
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.
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).
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.
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:
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.
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.
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
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.
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.