SQL Server Performance Forum – Threads Archive

Do I need a Unique Identifier or Identity column?

I have a table called â€˜Sessions#%92 where I store some information about every client that visits my website. In that table I have a column called â€˜Client_cookie#%92 where I want to store an identifier that must be unique for every client (but not for every new row. Since a client can have many sessions), and this column isn#%92t the PK. How can I achieve this? This is the table: -Sessions Table-
Session_id PK (bigint) IDENTITY (1, 1) NOT NULL
User_num (if user logs in) Is the user id in the DB for registered users
Client_cookie (permanent cookie that identifies the machine)
Session_type (Registered or Anonymous) Registered when user logs in during the session, and Anonymous if user doesn#%92 t log in
IP_address
User_Agent
Is_Carwler (â€˜True#%92 or â€˜False#%92)
Browser_name (i.e. IE)
Browser_version (i.e. 6.0)
Javascript_enabled (â€˜True#%92 or â€˜False#%92)
Cookies_enabled (â€˜True#%92 or â€˜False#%92)
UrlReferrer
By the way, which is the difference between the Primary Key and the Identity? Is the same concept? Thank you,
Cesar

PK is the row identifier. It is unique and none of the columns involved can be nullabvle. An identity is just a numeric column that takes the next value from the current seed using the defined step on an insert.
It doesn’t have to be consecutive or unique. To guarantee uniqueness it needs a unique index defined on it. Your User_num I would expect to get from the identifier on the users table after identifying the user that logs in.

Just to add. The PRIMARY KEY is one of the most fundamental concepts of relational databases. As Nigel said, its purpose is to uniquely identify a row in a table. Now, using a numeric column, defining the IDENTITY property on it and making it the PRIMARY KEY is an easy and convenient way to guarantee uniqueness and forget about the rest. You might want to search the Google Group like<a target="_blank" href=http://groups.google.de/group/microsoft.public.sqlserver.programming>http://groups.google.de/group/microsoft.public.sqlserver.programming</a> for postings from Joe Celko and Tony Rogerson on that topic. Sit back, relax, have a cup of coffee and enjoy reading. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Another point. You’re sure, you want session_id to be solely numerical? AFAIK, session_ids, for example, in PHP are alphanumerical in the range [a-Z] and [0-9] Just my $0.02 cents, as always. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Hi Frank, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Now, using a numeric column, defining the IDENTITY property on it and making it the PRIMARY KEY is an easy and convenient way to guarantee uniqueness and forget about the rest.<hr height="1" noshade id="quote"></blockquote id="quote"> I am happy to read it, since this is what I always make <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Another point. You’re sure, you want session_id to be solely numerical? AFAIK, session_ids, for example, in PHP are alphanumerical in the range [a-Z] and [0-9]<hr height="1" noshade id="quote"></blockquote id="quote"> I#%92m sorry, but I don#%92 t understand what you mean. Do you mean something like I can exceed the limit of â€˜bigint#%92 data type..? And, heyy..! What about my main question?: [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote"> I have a table called â€˜Sessions#%92 where I store some information about every client that visits my website. In that table I have a column called â€˜Client_cookie#%92 where I want to store an identifier that must be unique for every client (but not for every new row. Since a client can have many sessions), and this column isn#%92t the PK. How can I achieve this?<hr height="1" noshade id="quote"></blockquote id="quote"> Perhaps making â€˜Client_cookie#%92 uniqueidentifier data type instead of bigint, and getting the identifier for â€˜new clients#%92 when necessary with â€˜newid()#%92 function. Explaining a bit more: Only use newid() function when @known_client received parameter is null. If @known_client received parameter value is not null, then I will store that value in the â€˜Client_cookie#%92 column. The @known_client parameter value is set in the web application, and is the current value of the permanent client cookie called â€œclientâ€, sent by the client to web app. That cookie#%92s value was initially generated in the DB by newid() function, then was sent to web application so that store that value in the cookie, and placed in client#%92s browser to use it in future sessions, and so on.. What do you think? Thank you

Personally I would favor the UNIQUEIDENTIFIER approach, as this gives you a greater range of values and "more" uniqueness. Session ID in PHP look something like sess_4b1e384ad74619bd212e236e52a5a174If, which is probably als much more secure than a just numerical value. In that context I would visit some security sites to see what can be done. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

]]>