Hello,
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.
Any ideas/suggestions will be great.
Thanks,
Avinash
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 RelatedWorking on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
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.
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.
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies View RelatedHelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedWhat's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies View Related
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View RelatedHere is the table I created:
create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go
insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
select *
from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))
Thanks for you help!
Regards,
Anand
Hi All,
If I run "SELECT 610/100", query analyser returns 6! (wrong)
If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)
I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?
Is there some setting on my server which says Integer / Integer = Integer ?
Regards
Xo
My question is, i guess, a simple one:
When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?
hi all,
i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.
Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.
I have an sql statmetn that counts all the votes in the table. i need this to calculate the quota. My problem is how to i get the value(i.e the count of the votes) into the area thats colored red below?
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota = (count(Vote) + 1) / ((11) + 1);
Response.Write(quota);
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)
View 6 Replies View RelatedHi all,
I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer
Thanks in advance.
Sanjeev Kumar
I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.
View 7 Replies View RelatedI have some integers I want to format prefixed with zeros, e.g. 1235 would become 001235 and 445 would become 000445,
View 2 Replies View RelatedIn my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
(same question is also posted at sql-server-performance.com forums)
I am trying to select only values within a column are intergers.
My statement looks like this:
(I am using MSSQL querying into a Pervasive DB via a Linked Server)
SELECT Invoice, Invoice_Date, Description, INVOICE_Code_1
FROM API_PENDING__INVOICE
WHERE (API_PENDING__INVOICE.INVOICE_CODE_1) is integer???
Obviously, this is not working...
Can I put a constraint on an integer column that will only allow a certain range of numbers to be entered, or do I have to put that into the application layer only?
I'd like the range to be 0 to 30 as the only allowable values. The only thing that I could think of was to create another table and populate with 0,1,2...,30 and put a foreign key on the new column that wouldn't allow anything not in that list but I was wondering if there was a better way.
philippe writes "Hello ,
i am just starting with sql...
I have a table: Table_user
Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first
I will appreciate some tips...."
Hi there!
I am building an application that counnts clicks on an ad. I use the integer field to count them but someone told me there is a limit number this field can count - is that correct? if so, is there a solution for my problem?
Thanks and happy 2007!
R
for example which function can i use to convert the real number into the corresponding integers?
4.5 ->5
3.4->3
18.9->19
Thans
Trying to write the most effective UDF to convert INT to Datetime.
We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.
ALTER FUNCTION IntegerToDatetime (@int INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @IntegerToDatetime int
DECLARE @time DATETIME
SET @time = '2001-01-01'
SET @IntegerToDatetime =
CASE
WHEN LEN(@int) = 7
THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int)
WHEN LEN(@int) = 6
THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int)
WHEN LEN(@int) = 5
THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int)
WHEN LEN(@int) = 4 THEN cast(@time AS INT)
END
RETURN (@IntegerToDatetime )
END
GO
INPUT
-------------------------------
990831
981019
RESULT
-------------------------------
1900-01-02 00:00:00.000
1900-05-27 00:00:00.000
http://www.sqlserverstudy.com
How do I format an integer. Add commas.1234565 1,234,565TIA
View 3 Replies View RelatedHi
I use this to get all users from active directory.
SELECT *
FROM openquery(
adsi
,'SELECT name, AccountExpires FROM
''LDAP://company/OU=users,dc=company,dc=com''')
WHERE AccountExpires IS NOT NULL AND AccountExpires not in('0','0x7FFFFFFFFFFFFFFF','9223372036854775807','')
AccountExpires returns values like 128514708000000000 (This value represents the number of 100 nanosecond intervals since January 1, 1601 )
How do I convert this value to Datetime?
select getdate() returns a value like 2008-04-18 10:00:00.00 and that's how I'd like my AccountExpires
I am trying to set a decimal value to the pipelinecolumn buffer, but it doesnt get set, and the value is NULL.
Here is the portion of the code of what I am trying to do:
if (columnInfos[x].colName.EndsWith("_CRC"))
{
int a;
a_cmp tst = new a_cmp();
a= tst.a_crc32(inputbufferstream); this function returns a integer value
buffer.SetDecimal(colInfo.bufferColumnIndex, Convert.ToDecimal(a));
}
Please let me know how to set a decimal value in the buffer.
I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.
View 10 Replies View Related