uniqueness of columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

uniqueness of columns

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 06:05:23

Hey Guys,

I have a table as such. Basically I have constructed this "orderCode" to be an alternative to the orderID column. This is done for security reasons so that users can't see the IDENTITY column and how often it increases. The admin panel is based off the orderID.

So everytime I insert a record I run this code.

The function dbo.fnCreatePassword does not check for duplicates or anything in the tables column. What is the best way to make sure I do not insert any duplicate columns ? This a topic I have always not known enough about.

Any help much appreciated. :)

Thanks again!
Mike123

CREATE PROCEDURE [dbo].[insert_order]
(
@clientID int,
@approvedByAdmin tinyint,
@OrderCode [char](6) OUTPUT,
@OrderID int OUTPUT
)
AS SET NOCOUNT ON

SELECT @OrderCode = dbo.fnCreatePassword(3,0,3,0)

INSERT INTO [dbo].[tblOrders]
(
[clientID]
,[approvedByAdmin]
,[orderDate]
,[orderCode])
VALUES
(
@clientID,
@approvedByAdmin,
getDate(),
@orderCode
)

SELECT @orderID = @@IDENTITY

GO

TABLE STRUCTURE

CREATE TABLE [dbo].[tblOrders](
[orderID] [int] IDENTITY(1,1) NOT NULL,
[clientID] [int] NOT NULL,
[orderCode] [varchar](6) NOT NULL,
[approvedByAdmin] [tinyint] NOT NULL,
[approvedByAdmin_Date] [datetime] NULL,
[orderDate] [datetime] NOT NULL
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 06:45:50

isnt it better to create ordercode as a computed column based on orderid column with some additional logic rather than using function?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 07:01:43

Hey Visakh16,

You may well be correct. I was concerned about reverse engineering the code and finding out the orderID.

Do you have any specific suggestions ? I would love to consider them :)

Thanks again!
Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:24:28

yup. see the below

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 07:50:01

Hey Visakh16,

I have read over this article, from the way I understand it it is still an incrementing value ? The reason I liked my solution is because the values where completely random.

Thanks once again!
mike123

FUNCTION BELOW

CREATE FUNCTION [dbo].[fnCreatePassword]
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
RETURNS VARCHAR(80)
AS
BEGIN
-- Initialize some variables
DECLARE @UpperCase VARCHAR(26),
@LowerCase VARCHAR(26),
@Numbers VARCHAR(10),
@Special VARCHAR(13),
@Temp VARCHAR(8000),
@Password VARCHAR(8000),
@i SMALLINT,
@c VARCHAR(1),
@v TINYINT

-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
@Numbers = '0123456789',
@Special = '!@#$%&*()_+-=',
@Temp = '',
@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20

IF @UpperCaseItems < -20
SET @UpperCaseItems = -20

IF @LowerCaseItems > 20
SET @LowerCaseItems = 20

IF @LowerCaseItems < -20
SET @LowerCaseItems = -20

IF @NumberItems > 20
SET @NumberItems = 20

IF @NumberItems < -20
SET @NumberItems = -20

IF @SpecialItems > 20
SET @SpecialItems = 20

IF @SpecialItems < -20
SET @SpecialItems = -20

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = nwd % LEN(@UpperCase) + 1,
@c = SUBSTRING(@UpperCase, @v, 1),
@UpperCase = CASE
WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = nwd % LEN(@LowerCase) + 1,
@c = SUBSTRING(@LowerCase, @v, 1),
@LowerCase = CASE
WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Number Items
SET @i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = nwd % LEN(@Numbers) + 1,
@c = SUBSTRING(@Numbers, @v, 1),
@Numbers = CASE
WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Special Items
SET @i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = nwd % LEN(@Special) + 1,
@c = SUBSTRING(@Special, @v, 1),
@Special = CASE
WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = nwd % LEN(@Temp) + 1,
@Password = @Password + SUBSTRING(@Temp, @v, 1),
@Temp = STUFF(@Temp, @v, 1, '')
FROM dbo.vwNEWID

RETURN @Password
END

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:57:59

nope you can make it random by putting a logic making sure it generates a random unique number. or go for unique identifier (GUID) type field with default set to NEWID() function

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 08:31:10

Hey visakh16,

Again thanks :) I am looking into it, another requirement that I didn't mention is that I really like the 6 character randomly generated value of 3 numbers and 3 characters. It doesn't have to be exactly 3/3 but I do need a total of 6 characters.

Is there any way to perhaps use this function in the computed column formula ?

Thanks once again! Very much appreciated :)
Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 08:46:08

yup you can just make column definition as
create table...
...,
yourcol as dbo.fnCreatePassword(3,0,3,0),
...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-02 : 08:46:58

I'm confused...what is the relation between OrderID and OrderCode? I know visakh suggested this before but as far as I can see the following complies with your requirements:

SELECT LEFT(NEWID(), 6)

CREATE TABLE (
OrderCode varchar(6) NOT NULL DEFAULT(LEFT(NEWID(), 6))
)

- Lumbago
http://xkcd.com/327/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 10:25:07

quote:
Originally posted by visakh16

yup you can just make column definition as
create table...
...,
yourcol as dbo.fnCreatePassword(3,0,3,0),
...

but does this guarantee its uniqueness ? What happens if a value is genereated that already exists in the column?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 10:28:01

quote:
Originally posted by Lumbago

I'm confused...what is the relation between OrderID and OrderCode? I know visakh suggested this before but as far as I can see the following complies with your requirements:

SELECT LEFT(NEWID(), 6)

CREATE TABLE (
OrderCode varchar(6) NOT NULL DEFAULT(LEFT(NEWID(), 6))
)

Hey Lumbago,

This looks good too!. But how can I guarantee the results aren't duplicated in the column ?

There is no real correlation between the 2. They are both supposed to be unique values. The admin system uses the orderID to run queries, and for security/privacy reasons regular users use "orderCode".

Thanks again!
Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:02:34

quote:
Originally posted by mike123

quote:
Originally posted by visakh16

yup you can just make column definition as
create table...
...,
yourcol as dbo.fnCreatePassword(3,0,3,0),
...

but does this guarantee its uniqueness ? What happens if a value is genereated that already exists in the column?

nope. for that you need to use newid() to get guid each time

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 11:20:24

Hey Visakh16,

Ok I thought I was set for a minute, but then realized something.

If I TRIM the NEWID() to just the left 6 characters, I am no longer guaranteeing its uniqueness, correct ?

Any ideas on how I could proceed guaranteeing uniqueness ? Sorry for all the questions =
many thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:27:30

yup. once you trim it, there's no guarantee that it will be unique.so you want it to be 6 digit unique string?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 11:49:51

Hey Visakh16,

Yes a left trim of 6 characters was perfect. I want a 6 digit unique with numbers & letters..

Your help is really really apprecated..

thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:05:10

try this

http://www.sqlservercurry.com/2007/12/generate-unique-number-in-sql-server.html

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 12:19:27

Hey Visakh16,

That looks pretty good, I think its my best option right now. Unfortunately I will have to change all the datatypes in a few dozen tables and a few dozen SP's, but it wouldn't be the end of the world.

If I could get a combination of letters and numbers that would really be ideal for a few other reasons tho.

I don't know if this is possible, but maybe the function that creates the Trimmed GUID, could also check the table column to see if it exists ? Just an idea.

Your thoughts ?

Again very much appreciated, we are getting very very close!

Thanks,:D
Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:23:09

yup that might also be good. you can create a unique constraint on column for that

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 12:37:11

Hmm ... Ok if I created a unique constraint and it attempted inserted a duplicate, it would error out correct?

Do you have any suggestions on how to modify this SP ? Sorry for all the questions again.. you have been a massive help!

Thanks again, :D
Mike123

CREATE PROCEDURE [dbo].[insert_order]
(
@clientID int,
@approvedByAdmin tinyint,
@OrderCode [char](6) OUTPUT,
@OrderID int OUTPUT
)
AS SET NOCOUNT ON

SELECT @OrderCode = dbo.fnCreatePassword(3,0,3,0)
--after we populate @orderCode with a value, we could check IF EXISTS (SELECT orderCode FROM tblOrders WHERE orderCode = @orderCode )? not exactly sure how to write this .. any help appreciated! :)

INSERT INTO [dbo].[tblOrders]
(
[clientID]
,[approvedByAdmin]
,[orderDate]
,[orderCode])
VALUES
(
@clientID,
@approvedByAdmin,
getDate(),
@orderCode
)

SELECT @orderID = @@IDENTITY

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:41:48

quote:
Originally posted by mike123

Hmm ... Ok if I created a unique constraint and it attempted inserted a duplicate, it would error out correct?

Do you have any suggestions on how to modify this SP ? Sorry for all the questions again.. you have been a massive help!

Thanks again, :D
Mike123

CREATE PROCEDURE [dbo].[insert_order]
(
@clientID int,
@approvedByAdmin tinyint,
@OrderCode [char](6) OUTPUT,
@OrderID int OUTPUT
)
AS SET NOCOUNT ON

SELECT @OrderCode = dbo.fnCreatePassword(3,0,3,0)
--after we populate @orderCode with a value, we could check IF EXISTS (SELECT orderCode FROM tblOrders WHERE orderCode = @orderCode )? not exactly sure how to write this .. any help appreciated! :)

INSERT INTO [dbo].[tblOrders]
(
[clientID]
,[approvedByAdmin]
,[orderDate]
,[orderCode])
VALUES
(
@clientID,
@approvedByAdmin,
getDate(),
@orderCode
)

SELECT @orderID = @@IDENTITY

GO

yup it will error out.
if you're adding unique constraint, no need of above check to see if it exists

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 12:47:26

ok but if it errors out my transaction is aborted .. I don't really want that to happen =[

i think it would be better to check if it exists in the SP, and if it does already, just re-assign a value until it doesnt exist...

would this be hard to do in the SP ? not sure how to write it =
thanks again!
mike123

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources