SQL Server Performance Forum – Threads Archive

UniqueIdentifier, Identity column, Collation…

using SQL Server Express 2005 I think the datatype uniqueidentifier would be useful, because it provides function like newid(). Figure that its usage is like sequence in postgresql and oracle. But I don’t understand why i cannot set a uniqueidentifier column as the identity column!! And I want to use UTF-8 for encoding of db for multi-language supports. I found the collation setting in db property, and i see a whole lot collection of language encoding, but no unicode!! Is there a sequence-like datatype, that can be generated uniquely by sql server and can set as the identity-column?? I’m using MS SQL Server Management Studio Express to get familiar with its sql syntax, is that a good approach?

Identity can be added to an INT or BIGINT column only. The uniqueidentifier data type is alphanumeric – as in: it contains a mix of characters and numbers, plus a series of hyphens. The CHAR, VARCHAR and TEXT data types have Unicode equivalents NCHAR, NVARCHAR and NTEXT. [edit:]Hm – not sure SQL 2005 still has TEXT and NTEXT – probably VARCHAR(MAX) and NVARCHAR(MAX).

thanks Adriaan. What about the collation and its main usage!? It seems really confusing when I can use ndatatype for unicode but with all these collations that I can select!!

Me too, I always wonder why one would need collations when the data is unicode. Then again, collations are critical when matching data in JOINs etc. – just think of case sensitive collations …

cheers mate, I just hope it won’t become some kind of limitation/bottleneck when I really have to work with multi-language… … was reading this book :"collate has to do with the issue of sort order, case sensitivity, and sensitivity to accents."

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Identity can be added to an INT or BIGINT column only. The uniqueidentifier data type is alphanumeric – as in: it contains a mix of characters and numbers, plus a series of hyphens. The CHAR, VARCHAR and TEXT data types have Unicode equivalents NCHAR, NVARCHAR and NTEXT. [edit:]Hm – not sure SQL 2005 still has TEXT and NTEXT – probably VARCHAR(MAX) and NVARCHAR(MAX). <hr height="1" noshade id="quote"></blockquote id="quote"> Just for the records… You can create the IDENTITY property on all numeric columns with scale 0. That means you can also have a DECIMAL(38,0) IDENTITY column. Should take some time until you’re about to run out of range. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – 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>

Uh Frank, "Should take some time until you’re about to run out of range.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]" – you know you shouldn’t tease me with my lack of knowledge of mathematical basics … I had to look this one up – DECIMAL(38,0) can hold a higher maximum value than both INT and BIGINT? Is it only for backward compatibility that INT is limited?

Sorry Adriaan, it wasn’t my intention to tease anyone here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] When you roughly transform the numbers in Excel you’ll get this result <pre id="code"> 99.999.999.999.999.900.000.000.000.000.000.000.000,00 9.223.372.036.854.770.000,00 </pre id="code"> The first is DECIMAL(38,0), the second is BIGINT. You see, DECIMAL is vastly larger than BIGINT. – 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>

Another missing item in BOL! Under Using Integer Data, I find this: "The integer data types are the only ones that can be used with the IDENTITY property". The same page refers only to the tinyint, smallint, int, integer and bigint data types – suggesting that you cannot add identity to a decimal(38,0) column, which is incorrect (just tested it, works fine).

fun… Int – 4 bytes Bigint – 8 bytes Decimal or Numeric – varies ((-10^3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />-1 to (10^3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />-1) Works as long as the identity column is numeric-datatype. *** anything like "sysdate" that i can use as default value for date column??

The storage requirements for DECIMAL can be found in BOL

quote:
Precision Storage bytes
1 – 9 5
10-19 9
20-28 13
29-38 17

—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> *** anything like "sysdate" that i can use as default value for date column?? <hr height="1" noshade id="quote"></blockquote id="quote"> Check out GETDATE() or the ANSI equivalent CURRENT_TIMESTAMP. Btw, it’s better to start to new thread when you have some other question. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

oh, i thought it may be just a small silly question!! will do next time…

]]>