January 18, 2012 at 12:31 pm
hi this is just que i am confused.
how many unique key we can have in a table?
if a data coming from xml format type,hw you can store in a variable?
Thanks
January 18, 2012 at 12:53 pm
shah.simmy (1/18/2012)
hi this is just que i am confused.how many unique key we can have in a table?
Should be able to have 1000. Of course, that's a little on the ridiculous side
if a data coming from xml format type,hw you can store in a variable?
Same way you store any other data in a variable.
Interview questions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 12:54 pm
shah.simmy (1/18/2012)
hi this is just que i am confused.how many unique key we can have in a table?
if a data coming from xml format type,hw you can store in a variable?
Thanks
I don't think there is any limit to the number of unique indexes other than the number of columns. I would ask though why do you need more than 1 column that is unique? The point of a unique index is to allow you to identify any given row. You don't gain anything by being able to do that with more than 1 column. That is unless I am misunderstanding what you are asking. I think you are asking how many unique constraints can you have as opposed to how many columns can make up a composite unique key.
For your second question I don't know what you mean. Are you wanting to store xml data? If so, use the xml datatype. If that is not what you are asking then can you please try explaining it again?
_______________________________________________________________
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/
January 18, 2012 at 1:55 pm
Sean Lange (1/18/2012)
I would ask though why do you need more than 1 column that is unique? The point of a unique index is to allow you to identify any given row. You don't gain anything by being able to do that with more than 1 column.
Remember that the primary key automatically creates a unique index and you may want to create an additional unique index, particularly if you always use an IDENTITY column for your primary key. So, you might want to create a unique index on an ISBN number for a books table in addition to a unique index on the IDENTITY column.
Also a unique index can include multiple columns, so in a table representing a many-to-many relationship, you may want to have a unique index on a single column for use in foreign key constraints and a separate index on the columns containing the foreign keys to the tables being related ensuring that each relationship is unique.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2012 at 2:02 pm
drew.allen (1/18/2012)
Sean Lange (1/18/2012)
I would ask though why do you need more than 1 column that is unique? The point of a unique index is to allow you to identify any given row. You don't gain anything by being able to do that with more than 1 column.Remember that the primary key automatically creates a unique index and you may want to create an additional unique index, particularly if you always use an IDENTITY column for your primary key. So, you might want to create a unique index on an ISBN number for a books table in addition to a unique index on the IDENTITY column.
Also a unique index can include multiple columns, so in a table representing a many-to-many relationship, you may want to have a unique index on a single column for use in foreign key constraints and a separate index on the columns containing the foreign keys to the tables being related ensuring that each relationship is unique.
Drew
True and if I saw unique columns in a situation like that I wouldn't twice about it. When presented with a question of how many can you have my brain goes to bad places. 😛
_______________________________________________________________
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply