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 BIGINT
AS
BEGIN
RETURN (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
Edit: There seems to be some interaction between GROUP BY/NEWID() that can cause problems if these functions are used directly in an aggregate query. Please read the posts on this thread about this, and use caution.
This script creates three functions, F_RANDOM_INTEGER, F_RANDOM_SAMPLE, and F_RANDOM_DATETIME. The last parameter of each function must be function NEWID() to generate the random number.
Theses functions are designed for applications where it is necessary to generate random integers, random datetimes, or take random samples from sets of data. Typical applications would be software testing, inventory control, auditing, and product quality testing.
Function F_RANDOM_INTEGER returns a random integer in the range of the input parameters so that the return value is >= @START_INT and <= @END_INT. It is valid for any range of two integer values.
Function F_RANDOM_SAMPLE returns a 1 or a 0 to determine if a sample should be selected, based on the input sample rate. Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. A sample rate of 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%.
F_RANDOM_DATETIME returns a random datetime value >= @START_TIME and < @END_TIME. It is valid for any datetime range. Input parameters default, if null, to @START_TIME = '19000101' and @END_TIME = '19000102’. The datetime is random to the level of clock ticks (1/300 of as second). Note that the latest time is not included in the range of datatime values that can be returned. This is to allow selection of times within adjacent time periods, without having to specify times to the level of milliseconds. This means a range of 1990-12-01 01:00:00.000 through 1990-12-01 02:00:00.000 will never return a value of 1990-12-01 02:00:00.000.
The NEWID() function is the basis of the random numbers. These functions should not to be considered random for purposes of data encryption or other high security applications. However, they should be adequate for business applications of the types mentioned above. I conducted extensive testing with the functions where I generated millions of results, analyzed the results various ways to look for non-random patterns, and I saw no evidence of non-random results.
The script also includes a demo of each function, and sample output from the demos is also included.
The demo script uses the number table function, F_TABLE_NUMBER_RANGE, available on this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
if objectproperty(object_id('dbo.F_RANDOM_INTEGER'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_INTEGER end go create function dbo.F_RANDOM_INTEGER ( @START_INTint, @END_INTint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_INTEGER
This function returns a random integer value >= @START_INT and <= @END_INT.
Valid for any integer range.
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random integer between -100, 200000 select [Random Integer] = [dbo].[F_RANDOM_INTEGER](-100, 200000,newid()) */ begin
-- Set default values for input dates if they are null if @START_INT is null begin set @START_INT = 0 end if @END_INT is null begin set @END_INT =1000000 end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_INT > @END_INT select @sn = @END_INT, @en = @START_INT else select @sn = @START_INT, @en = @END_INT
-- Return start int if start int = end int if @sn = @en return @sn
-- Get modulus select @mod = @en-@sn+1
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random integer select @rand_result = @sn+(@rand_bigint%@mod)
return @rand_result
end go grant execute on dbo.F_RANDOM_INTEGER to public go
if objectproperty(object_id('dbo.F_RANDOM_SAMPLE'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_SAMPLE end go create function dbo.F_RANDOM_SAMPLE ( @SAMPLE_RATEint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_SAMPLE
This function returns a 1 or a 0 to determine if a sample should be selected, based on the sample rate. It is designed to select random samples at a specific rate.
Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%
Requires newid() to be input parameter @NEWID to generate the random number.
-- Select sample 200,000 times in 1,000,000 samples (20%) select [Random Sample] = [dbo].[F_RANDOM_SAMPLE](200000,newid()) */ begin
declare @rand_bigint bigint
-- Get random bigint from @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Select sample if the modulus of @rand_bigint is less than the sample rate return case when @rand_bigint%1000000 < @SAMPLE_RATE then 1 else 0 end
end go grant execute on dbo.F_RANDOM_SAMPLE to public go
if objectproperty(object_id('dbo.F_RANDOM_DATETIME'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_DATETIME end go create function dbo.F_RANDOM_DATETIME ( @START_TIMEdatetime, @END_TIMEdatetime, @NEWIDuniqueidentifier ) returns datetime as /* Function: F_RANDOM_DATETIME
This function returns a random datetime value >= @START_TIME and < @END_TIME.
Valid for any datetime range.
Input parameters default, if null, to: @START_TIME'19000101' @END_TIME'19000102'
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random time between 08:30 and 12:00 select [Random Time] = [dbo].[F_RANDOM_DATETIME]('08:30:00.000','12:00:00.000',newid()) */ begin
declare @st datetime declare @et datetime
declare @hours int declare @ms int declare @ticks bigint declare @rand_ticks bigint declare @rand_bigint bigint
declare @remaining_ticks int declare @return_hours int declare @return_ms int
-- Set default values for input dates if they are null if @START_TIME is null begin set @START_TIME = '19000101' end if @END_TIME is null begin set @END_TIME = '19000102' end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_TIME > @END_TIME select @st = @END_TIME, @et = @START_TIME else select @st = @START_TIME, @et = @END_TIME
-- Return start time if start time = end time if @st = @et return @st
-- Get hours boundary difference. -- Subtract 1 from diff, before dividing by 2 and multiplying by 2 -- so the milliseconds remaining is always positive and -- hours is always >= zero. set @hours = ((datediff(hh,@st,@et)-1)/2)*2
-- Get remainder milliseconds set @ms = datediff(ms,0,@et-dateadd(hh,@hours,@st))
-- Convert remainder milliseconds to -- SQL Server 'clock ticks' of 1/300 of a second set @ticks = ((@ms/10)*3) + ((@ms%10)/3)
-- Add hours * tick per hour (3600*300) to give total -- ticks between @START_TIME and @END_TIME set @ticks = @ticks + (@hours * 0000001080000 )
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random number of ticks select @rand_ticks = @rand_bigint%@ticks
-- Get hours component of random ticks select @return_hours = @rand_ticks/1080000
-- Get left over ticks after removing hours. select @remaining_ticks = @rand_ticks%1080000
--Convert remaining clock ticks back to milliseconds select @return_ms = ((@remaining_ticks/3)*10) + floor(((@remaining_ticks%3)*3.5))
-- Return the random time between the start and end time return dateadd(ms,@return_ms,dateadd(hh,@return_hours,@st))
end go grant execute on dbo.F_RANDOM_DATETIME to public go
insert into @t select -- Get integert in range of 1 to 10,000,000 [Random Integer] = [dbo].[F_RANDOM_INTEGER](1,10000000,newid() ) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Right Int] = [Random Integer]%10, [Count] = count(*) from @t a group by [Random Integer]%10 order by 1,2
select [Million Range] = [Random Integer]/1000000, [Count] = count(*) from @t a group by [Random Integer]/1000000 order by 1,2
insert into @t select -- Sample rate = 6.3775% [Sample Taken] = [dbo].[F_RANDOM_SAMPLE](63775,newid()) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Sample Taken], [Result Count] = count(*) from @t a group by [Sample Taken] order by 1,2
go
print '-----------------------------------------------------------------' print ' Demo F_RANDOM_DATETIME function' print '-----------------------------------------------------------------' print '' select Random_Datetime = convert(varchar(23),[dbo].[F_RANDOM_DATETIME]( a.ST, a.ET,newid() ) ,121) , [Start] = convert(varchar(23),a.ST ,121) , [End] = convert(varchar(23),a.ET ,121) , a.Comment from ( select ST = getdate(), ET = getdate()+2 , Comment = 'Now thru 2 days from now' union all select '20060101', '20060102' , 'One day diff' union all select '20030101', '20030101' ,'Both times same' union all select '20030101', '20030108' ,'One week diff' union all select '20021228', '20030104' ,'One week diff' union all select '20010701', '20010713' ,'12 day diff' union all select '20010701', '20010714' ,'13 day diff' union all select '20010630', '20010713' ,'13 day diff' union all select '19901201 01:00:00.000', '19901201 02:00:00.000' ,'1 hour diff' union all select '19901201 01:00:33.003', '19901201 02:00:33.003' ,'1 hour diff' union all select '19901201 01:00:00.000', '19901201 01:30:00.000' ,'30 min diff' union all select '19901201 01:00:33.447', '19901201 01:30:33.447' ,'30 min diff' union all select '19901201 01:00:00.000', '19901201 01:05:00.000' ,'5 min diff' union all select '19901201 01:00:29.123', '19901201 01:05:29.123' ,'5 min diff' union all select '19901201 01:00:00.000', '19901201 01:01:00.000' ,'1 min diff' union all select '19901201 01:00:00.000', '19901201 01:00:01.000' ,'1 sec diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.100' ,'100 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.050' ,'50 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.023' ,'23 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.020' ,'20 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.013' ,'13 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.010' ,'10 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.007' ,'7 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.003' ,'3 ms diff' union all select '20030101', '20030201' ,'One month diff 31 days' union all select '20030101', '20040101' ,'One year diff' union all select '20050101', '20070101' ,'Two year diff' union all select '20060101', '20060301' ,'2 month diff' union all select null, '20060101' ,'Start time null' union all select '20060102', null ,'End time null' union all select null, null ,'Both null' union all select '17530101', '99991231 23:59:59.997' ,'Max datetime diff' union all select '99991231 23:59:59.997','17530101' ,'Max datetime diff reversed' ) a
Demo Results:
----------------------------------------------------------------- Demo F_RANDOM_INTEGER function -----------------------------------------------------------------
----------------------------------------------------------------- Demo F_RANDOM_SAMPLE function -----------------------------------------------------------------
(100000 row(s) affected)
Sample Taken Result Count ------------ ------------ 0 93669 1 6331
(2 row(s) affected)
----------------------------------------------------------------- Demo F_RANDOM_DATETIME function -----------------------------------------------------------------
Random_Datetime Start End Comment ----------------------- ----------------------- ----------------------- -------------------------- 2006-07-24 08:21:22.593 2006-07-23 17:54:47.283 2006-07-25 17:54:47.283 Now thru 2 days from now 2006-01-01 06:44:36.897 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 One day diff 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 Both times same 2003-01-05 01:57:02.183 2003-01-01 00:00:00.000 2003-01-08 00:00:00.000 One week diff 2003-01-01 20:02:05.550 2002-12-28 00:00:00.000 2003-01-04 00:00:00.000 One week diff 2001-07-02 11:35:11.147 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 12 day diff 2001-07-02 16:39:57.433 2001-07-01 00:00:00.000 2001-07-14 00:00:00.000 13 day diff 2001-07-06 12:33:53.087 2001-06-30 00:00:00.000 2001-07-13 00:00:00.000 13 day diff 1990-12-01 01:15:42.530 1990-12-01 01:00:00.000 1990-12-01 02:00:00.000 1 hour diff 1990-12-01 01:02:21.647 1990-12-01 01:00:33.003 1990-12-01 02:00:33.003 1 hour diff 1990-12-01 01:21:06.267 1990-12-01 01:00:00.000 1990-12-01 01:30:00.000 30 min diff 1990-12-01 01:26:17.983 1990-12-01 01:00:33.447 1990-12-01 01:30:33.447 30 min diff 1990-12-01 01:00:56.327 1990-12-01 01:00:00.000 1990-12-01 01:05:00.000 5 min diff 1990-12-01 01:03:20.423 1990-12-01 01:00:29.123 1990-12-01 01:05:29.123 5 min diff 1990-12-01 01:00:21.617 1990-12-01 01:00:00.000 1990-12-01 01:01:00.000 1 min diff 1990-12-01 01:00:00.443 1990-12-01 01:00:00.000 1990-12-01 01:00:01.000 1 sec diff 1990-12-01 01:00:00.050 1990-12-01 01:00:00.000 1990-12-01 01:00:00.100 100 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.050 50 ms diff 1990-12-01 01:00:00.010 1990-12-01 01:00:00.000 1990-12-01 01:00:00.023 23 ms diff 1990-12-01 01:00:00.017 1990-12-01 01:00:00.000 1990-12-01 01:00:00.020 20 ms diff 1990-12-01 01:00:00.007 1990-12-01 01:00:00.000 1990-12-01 01:00:00.013 13 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.010 10 ms diff 1990-12-01 01:00:00.003 1990-12-01 01:00:00.000 1990-12-01 01:00:00.007 7 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.003 3 ms diff 2003-01-14 09:00:09.520 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 One month diff 31 days 2003-08-27 11:47:04.100 2003-01-01 00:00:00.000 2004-01-01 00:00:00.000 One year diff 2006-11-23 03:57:21.737 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000 Two year diff 2006-01-12 08:50:40.717 2006-01-01 00:00:00.000 2006-03-01 00:00:00.000 2 month diff 1933-11-15 13:39:10.050 NULL 2006-01-01 00:00:00.000 Start time null 1997-05-28 06:42:32.407 2006-01-02 00:00:00.000 NULL End time null 1900-01-01 01:50:42.743 NULL NULL Both null 2758-10-18 13:50:47.987 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 Max datetime diff 8426-03-24 13:51:08.407 9999-12-31 23:59:59.997 1753-01-01 00:00:00.000 Max datetime diff reversed
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.
I wanted to hear from you - if you've used any unique integer generation technique in the context of a disconnected smart client/local data store and have been successful with it.
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.
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.
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 beginselect 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 .......
use AFMIF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name='uye_idler') drop table uye_idlerCREATE TABLE uye_idler ( uye_no int NOT NULL, ) GO DECLARE @uye_numarası int Set @uye_numarası = 1 WHILE @uye_numarası < 15001 BEGIN INSERT INTO uye_idler VALUES (@uye_numarası) Set @uye_numarası = @uye_numarası + 1 EndHi , I am able to create 15000 records by the codes above .By the same way I want to create random texts as ASD ,WER,SAD,DFG etc. How can I do this ? Thanks ...
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.
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
I use ODBC driver to perform SQLServer commands from C/C++ application.
An "INSERT INTO <table> (<column>) VALUES (NULL)" command has a random behavior in a SQL2000 Server running on WindowsXP.
The <column> in this command has 2 definitions about the NULL value :
- the NULL is accepted in the table definition, with <column> ut_oui_non NULL".
- the NULL is rejected in the type definition, with EXEC sp_addtype ut_oui_non, 'char(1)', 'NOT NULL' and a rule to check values with '0' or '1'
1/ The column definition in any explorer show the NULL from table deffinition
2/ The "INSERT INTO" is completed in SQL Query tool, used on Windows2000 and WindowsXP computers, connected to the same SQL2000 server.
3/ The "INSERT INTO" is completed in the application, running on Windows2000 with an ODBC driver to the same SQL2000 server.
4/ The "INSERT INTO" is rejected in the application, running on WindowsXP witjh an ODBC driver to the same SQL2000 server. The error 513 means that INSERT VALUES conflicts with previous rule. So only the type definition seems to be used.
But :
5/ This is a random error, and some INSERT with the same values in this column are completed.
6/ This random error seems to be discarded by using the "Use NULLs, paddings and warnings ANSI" checkbox in the ODBC driver user source configuration.
This checkbox is only use for enforcing the ANSI syntax in SQL commands, and has no known effect on type checking.
Do you know about any conflict of column NULL value between a type definition and a table definition ?
I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
Hi all,I might be getting this all wrong but bear with me. I need to create some kind of Unique field in my DB that is nonsequential. This is because I need it to be difficult to guess ids if you have an example in front of you.I have looked at 8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN) ) The "T" serves to split the numbers for when I am converting back. So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) ) ------------------------------------------------------------------------------------------ 3166 00031663 C5ET7BAF 3167 00031673 C5FT7BB9 3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?) What do people think? Thanks,Pete
I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field) that allows nulls. The table contains multiple rows with the value of null for field theology. The documentation says one can create a unique constraint on a field with all unique value except for null. Here is the error message:
'testtable1' table - Unable to create index 'IX_testtable1'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found. Most significant primary key is ''. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
Any ideas? I am creating a unique constraint and not a unique index. Is there some other database option to set to allow this?
I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs
Hello. Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have: CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
why it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
Introduction This MS SQL Store Procedure solves a problem which is not common except when you have a table in a database which each row needs to be uniquely identified and their are more rows in the table than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to 8.6904152163272468261061026005374e+50 unique rows at best. If you used a "Big Int" you would be limited to -2^63 ( -9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the identity field.) or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for this invention is due to the need to track every email that an application sends for Sarbanes/Oxley Requirements. With this technique, every email sent out will be uniquely identified for a very very very long time.
The purpose of this is to show how to set up an identity column with a larger range than a big int or unique id. Try transaction logs where you need to track every change, or determining click paths through a website, etc.
The point is, that this method pretty much does what the title says, "Create unlimited Unique ID's". What table you apply this too and for what reason is up the the programmer.
Background This design uses basic counting methods and handles the limitations of MS SQL TSQL. First, you can use a varchar(4000) as the unique id column but the issue with this is that as of MSSQL 2000, the largest indexable field is 900 character. So if you need to be able to quickly search the table by key, or clustered keys, you need to limit your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a temporary table, select the subset into it and search that.
Using the code First, copy and paste all the TSQL into a Query Window and compile it in the database you wish to use it in.
[Code] /********************************************************************************** Program: ALTER Unlimited Unique ID's (Auto Increment) Programmer: Vince Gee Date: 9/28/2005 Parameters: @TABLE_NAME - The name of the table to establish the auto incrementing field in @COLUMN_NAME - The column name in the table to establish the auto incrementing field in @DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with the same name. Theory: A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description: The purpose of the sql procedure is to automate the creation of auto updating identities on a sql table without the trouble of writing the trigger each time.
So what does this do? Well for example lets say we have the following table which you will have many many many rows in.
myKey is the unique identifier for each row. We can set it's size really to anything, This proc will look for the column specified and determine it's size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated together exceed the storage compacity w/ the trigger code of 4000 characters. If this is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc: Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey' or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output: When data is inserted into the table, the auto incrementing field will look like 0000000001 0000000002 0000000003 0000000004 0000000005 0000000006 0000000007 0000000008 0000000009 000000000A 000000000B 000000000C 000000000D 000000000E 000000000F 000000000G 000000000H 000000000I 000000000J 000000000K 000000000L with how many 0's set up etc. It goes 0-9, then A-Z
--If the trigger if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1) begin IF @DROP_EXISTING_TRIGGER = 0 BEGIN -- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' RETURN END ELSE BEGIN --CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT. set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME --EXECUTE THE SQL EXEC SP_EXECUTESQL @EXECSQLSTRING END end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS create table #temp ( TABLE_QUALIFIER varchar(255), TABLE_OWNER varchar(255), TABLE_NAME varchar(255), COLUMN_NAME varchar(255), DATA_TYPE int, [TYPE_NAME] varchar(255), [PRECISION] int, LENGTH int, SCALE int, RADIX int, NULLABLE int, REMARKS varchar(255), COLUMN_DEF varchar(255), SQL_DATA_TYPE int, SQL_DATETIME_SUB varchar(255), CHAR_OCTET_LENGTH int, ORDINAL_POSITION int, IS_NULLABLE varchar(255), SS_DATA_TYPE int ) --POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE insert into #temp exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING --FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD. SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP SET @COUNTER = 1 SET @COLUMN_NAMES = '' WHILE @COUNTER <= @MAXORDINAL BEGIN select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER if (@tCOLUMN_NAME <> @COLUMN_NAME) begin SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ',' end else begin select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER end SET @COUNTER = @COUNTER +1 END --CLEAN UP drop table #temp
IF @KEYLENGTH > 900 Begin PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' END SET @EXECSQLSTRING = ' CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
END' if len(@EXECSQLSTRING) <4000 begin EXEC SP_EXECUTESQL @EXECSQLSTRING end else begin print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.' end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on. Column Name - Name of the column to use as the key. Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int set @t = 0
while @t <= 40000 begin insert into countertest select '','s','s','s' set @t = @t + 1 end GO
Once this completes, you can inspect the unique id by selecting it from the table SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?
I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.
Please help, else I have to always do a duplication check before inserting new records.
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example: Row 1 - Column 1 = "cool" Row 1 - Column 3 = 91 Row 3 - Column 1 = "cool" Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
