June 1, 2013 at 6:38 am
Hi Folks,
Please help me on below few queries
My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)
1) now im trying SET IDENTITY_INSERT USERS.ID OFF
But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.
2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.
Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?
June 1, 2013 at 7:06 am
I Also tried
SET IDENTITY_INSERT ON
but the error as above. Please help on both of above.
June 1, 2013 at 7:38 am
Hi,
As per my concern we can not apply identity property on alphanumeric field.
to set the identity property on you have to use like this,
SET IDENTITY_INSERT table_name ON/OFF
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 1, 2013 at 3:46 pm
kiran.vaichalkar (6/1/2013)
Hi Folks,Please help me on below few queries
My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)
1) now im trying SET IDENTITY_INSERT USERS.ID OFF
But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.
2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.
Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?
Kapil answered the question about identity insert.
You can't have an identity that is a varchar. What are you hoping to gain here by using that type of construct? I would run away from that idea screaming. Keep it simple, go with an int (or bigint) identity and be done with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2013 at 6:18 pm
Thanks Folks!!
2nd question is now addressed
an you also let me know what special we need to do to get the below executed correctly
SET IDENTITY_INSERT USERS.ID ON
Where USERS(ID INT PRIMARY KEY, Name nvarchar(20))
I tried but it throws error saying, 'Either the object 'USERS' does not exist or you do not have permissions'
Please Help..
June 2, 2013 at 8:48 am
kiran.vaichalkar (6/1/2013)
Thanks Folks!!2nd question is now addressed
an you also let me know what special we need to do to get the below executed correctly
SET IDENTITY_INSERT USERS.ID ON
Where USERS(ID INT PRIMARY KEY, Name nvarchar(20))
I tried but it throws error saying, 'Either the object 'USERS' does not exist or you do not have permissions'
Please Help..
You don't set identity insert on the column, just the table.
SET IDENTITY_INSERT USERS ON
Then when you are done you need to set it back off.
SET IDENTITY_INSERT USERS OFF
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 2, 2013 at 10:15 am
Now a question for kiran.vaichalkar, what is it you think that IDENTITY_INSERT does?
June 2, 2013 at 3:08 pm
Hi Lynn,
i was mistaken about including the Column name in the syntax for IDENTITY_INSERT.
Well i was looking continuity for one of my table column entries which is the primary key (Column - USERS.ID) was also set to IDENTITY(1,1).
The continuity on USERS.ID was missed due to series of transactions on that table.(INSERT / DELETE / UPDATE)
Now I wanted manually to enter the missing records.
Help me if am getting IDENTITY_INSERT wrong.
June 2, 2013 at 3:14 pm
Why do you need the user ids to be sequential? You will also get gaps if you delete users from the table as well?
June 2, 2013 at 11:53 pm
kiran.vaichalkar (6/1/2013)
Hi Folks,Please help me on below few queries
My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)
1) now im trying SET IDENTITY_INSERT USERS.ID OFF
But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.
2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.
Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?
I wonder if the table exists. The reason I say this, is that the syntax above is incorrect/incomplete. Maybe I'm wrong on this. But will it create the table if the type of the first column is not identified? You have:
ID IDENTITY(1,1) PRIMARY KEY,
I would write it like this:
ID INT IDENTITY(1,1) PRIMARY KEY,
I have never tried to do this without giving the INT type, so I don't really know if SQL Server will assume INT and is forgiving about this. But considering the error message, I am leaning to thinking no.
Dana
June 25, 2013 at 2:26 pm
Hi Folks,
Thanks again to every1 looking into this for me.
I was in need to generate Alphanumeric ID ( An Identity which is based on PRIMARY KEY [assigned to every row for an user information] AND User Status [Suppose Status can have values {A,B,C,D,E}]
So the Alphanumeric ID for First user could be '1A', Similarly second user '2A', Third '3B', Fourth '4A'.. and some nth user has 'nE'.
So now,
I've found the logic to Derive this Alphanumeric ID.
Of course, It cannot be a Primary KEY so my assumption about it was false tough it can be a UNIQUE KEY.
The logic is as below,
UPDATE EMP_DETAILS SET Use_ID = CAST((EMP_STATUS+''+CAST(ID as varchar)) as varchar)
Where
EMP_DETAILS: Table for User information.
ID: Primary Key & EMP_STATUS[As the user chooses from Front end]
But finally, what i got is I have to re-look to the written records and re-process for the USE_ID(the required Alpha numeric ID).
So my question is, Is there any other way like to achieve this, alike the derived columns are(Value get calculated and store automatically)?
June 25, 2013 at 10:42 pm
kiran.vaichalkar (6/25/2013)
Hi Folks,Thanks again to every1 looking into this for me.
I was in need to generate Alphanumeric ID ( An Identity which is based on PRIMARY KEY [assigned to every row for an user information] AND User Status [Suppose Status can have values {A,B,C,D,E}]
So the Alphanumeric ID for First user could be '1A', Similarly second user '2A', Third '3B', Fourth '4A'.. and some nth user has 'nE'.
So now,
I've found the logic to Derive this Alphanumeric ID.
Of course, It cannot be a Primary KEY so my assumption about it was false tough it can be a UNIQUE KEY.
The logic is as below,
UPDATE EMP_DETAILS SET Use_ID = CAST((EMP_STATUS+''+CAST(ID as varchar)) as varchar)
Where
EMP_DETAILS: Table for User information.
ID: Primary Key & EMP_STATUS[As the user chooses from Front end]
But finally, what i got is I have to re-look to the written records and re-process for the USE_ID(the required Alpha numeric ID).
So my question is, Is there any other way like to achieve this, alike the derived columns are(Value get calculated and store automatically)?
So what happens when you get to 26Z? What would 27 be? Perhaps 27AA, 28AB, etc?
I'm trying to be practical here. Really, with enough ingenuity we can do just about anything we want in SQL. I don't question as much as others as to WHY people have unusual requirements. Sometimes we just do because a client or some other power-that-be has made the request and either won't change or it's just not worth the bother.
So in that vein of thought, you CAN do this but not with an identity column which is of course always and only numeric. What you'll probably need to do is create a computed column to hold the alphanumeric value. The function/formula for that computed column will then have to get the identity value and using some algorithm determine the letter combination that would be next in sequence. To get alpha characters from a number you can use the CHAR operator.
For example, you can easily convert the number 67 into its ASCII equivalent,
which in this case is an upper-case 'C', and back again.
SELECT CHAR(67) -- returns 'C'
--this does the opposite
SELECT ASCII('C') -- returns '67'
So with that, you could do your string manipulations. But it will get complicated once you get past that first 'Z'! I've been trying to do something like that for awhile as an experiment and it never seems to work correctly and then the code turns into spaghetti.
Probably the best method would be a table of characters similar to a tally table:
ID INT IDENTITY(1,!)
VAL VARCHAR(5)
The values would be like this or whatever pattern you choose:
1 A
2 B
...
26 Z
27 AA
28 AB
...
53 AZ
54 AAA
55 AAB
...
80 AAAA
81 AAAB
Then you do a JOIN on a tally table with the IDENTITY value to concatenate your artificial key into a single string.
June 26, 2013 at 7:46 am
Steven Willis (6/25/2013)
So in that vein of thought, you CAN do this but not with an identity column which is of course always and only numeric.
Just because you CAN do something does not mean it is a good idea. The whole concept of this is going to be nothing but a complete and total PITA. All kinds of hoops to create the keys, concurrency is a serious concern when you have a table to hold your keys. This thing can and will backfire at some point. It all comes back to what is the gain here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 10:38 am
Sean Lange (6/26/2013)
Steven Willis (6/25/2013)
So in that vein of thought, you CAN do this but not with an identity column which is of course always and only numeric.Just because you CAN do something does not mean it is a good idea. The whole concept of this is going to be nothing but a complete and total PITA. All kinds of hoops to create the keys, concurrency is a serious concern when you have a table to hold your keys. This thing can and will backfire at some point. It all comes back to what is the gain here?
Sean
I agree that using such an artificial "key" would be a horrible idea and didn't mean to imply that such values should be part of a primary key. But I can see where a computed column approach to create something like an ascending aphanumeric code BASED on an identity value might be useful as part of an employee ID, badge number, or whatever.
Not too many months ago I had a requirement to generate unique registration codes to be handed out to users who would use the code to validate their login. This was a "use-once" code so that the user could login once. The site admins wanted the codes to be sequential because they thought it would be easier for them to administer the codes. I pointed out that if the codes were sequential, anyone with a brain could figure out the sequence from the one authorized code they were given and screw things up. Not to mention the aforementioned difficulties of even doing this as this thread demonstrates. So wisely they took my advice to use random codes.
Now I understand that SQL2012 has a sequential alphanumeric function of some sort, though I haven't seen or tried it yet. So I guess as we all eventually upgrade over the next few years the point will be moot.
Steve
June 26, 2013 at 11:44 am
Steven Willis (6/26/2013)
Sean Lange (6/26/2013)
Steven Willis (6/25/2013)
So in that vein of thought, you CAN do this but not with an identity column which is of course always and only numeric.Just because you CAN do something does not mean it is a good idea. The whole concept of this is going to be nothing but a complete and total PITA. All kinds of hoops to create the keys, concurrency is a serious concern when you have a table to hold your keys. This thing can and will backfire at some point. It all comes back to what is the gain here?
Sean
I agree that using such an artificial "key" would be a horrible idea and didn't mean to imply that such values should be part of a primary key. But I can see where a computed column approach to create something like an ascending aphanumeric code BASED on an identity value might be useful as part of an employee ID, badge number, or whatever.
Not too many months ago I had a requirement to generate unique registration codes to be handed out to users who would use the code to validate their login. This was a "use-once" code so that the user could login once. The site admins wanted the codes to be sequential because they thought it would be easier for them to administer the codes. I pointed out that if the codes were sequential, anyone with a brain could figure out the sequence from the one authorized code they were given and screw things up. Not to mention the aforementioned difficulties of even doing this as this thread demonstrates. So wisely they took my advice to use random codes.
Now I understand that SQL2012 has a sequential alphanumeric function of some sort, though I haven't seen or tried it yet. So I guess as we all eventually upgrade over the next few years the point will be moot.
Steve
I didn't mean to imply that you thought it was a good idea. My apologies. I was simply restating what has been said through this thread in a last attempt at convincing the OP to change their mind. I agree that it could be useful as some sort of code. It would be difficult to maintain though.
I am not familiar with an alphanumeric sequence in 2012. It seems like it would be difficult to establish the rules for how it would work.
There is a sequence but it is numeric.
http://msdn.microsoft.com/en-us/library/ff878058.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply