October 30, 2012 at 6:05 pm
CREATE TABLE [dbo].[table1] (
[Id] [int] NOT NULL IDENTITY,
[xyzName] [nvarchar](max) NOT NULL,
[xyzId] [nvarchar](max) NOT NULL,
[Active] [bit] NOT NULL,
[CreatedBy] [nvarchar](max) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](max),
[ModifiedDate] [datetime],
CONSTRAINT [PK_dbo.table1] PRIMARY KEY ([Id])
)
CREATE UNIQUE INDEX [IX_xyzId] ON [dbo].[table1]([xyzId])
Error:
Msg 1919, Level 16, State 1, Line 13
Column 'IX_xyzId' in table 'dbo.table1' is of a type that is invalid for use as a key column in an index.
October 30, 2012 at 6:19 pm
No.
For starters, you want to go here:
http://msdn.microsoft.com/en-us/library/ms188783(v=sql.105).aspx
In particular:
Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes.
Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.
So, you can include them (except for the old versions), but not use them as a portion of the key, which would be required for unique.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply