Unique Key on Text datatype

Question

Post reply

Unique Key on Text datatype

RaviShankar1234

SSCommitted

Points: 1647
More actions
February 19, 2010 at 3:34 pm

#386353

Hi,
here is my query that I wrote
create table Login
(LoginID int identity(5,5) Not null Primary Key,
Username varchar(15) not null,
Password text Null default 'badgerbadger',
DataAdded datetime Not null default getdate(),
DataUpdated datetime Null default getdate() ,
UNIQUE(Username,password, DataAdded))
I want to add composite unique key over three attributes i.e. username, password, dateadded. But Some reason I dont know this query isnt executing when I run this in SQL server. can any one help me in solving this issue.
I have a question tooo can we add a unique key over Text datatype?:exclamation:
Thanks,
Ravi Shankar

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

No this is not allowed on a Text Data Type.

Try it...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 2

See CREATE INDEX (Transact-SQL) for details. Here is the extract relevant to your current question:

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.

You should probably also try to move away from using the text data type - it is deprecated in favour of the MAX data types. See ntext, text and image (Transact-SQL). Again, an extract from that Books Online entry:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 3

It also occurs to me that you might be using 'text' from some past familiarity with Access. In SQL Server, text is one of the old large-object types, able to store up to 2GB of data. For a column like 'password' you probably need to use varchar(n) where n is the maximum number of characters to be stored. The maximum value of n for ordinary varchar is 8000, but varchar(max) can store up to 2GB of character data. If you work with unicode data, the equivalents are nvarchar(n) and nvarchar(max). See cha and varchar for full details.

If you change the definition of your password column to something like varchar(20), it will happily take part in a unique index. Whether that is a good choice for a unique index is another question...

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I apologize for not taking the time as Paul did to fully explain the implications concerning the use of a Text Field.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 5

Welsh Corgi (2/20/2010)
I apologize for not taking the time as Paul did to fully explain the implications concerning the use of a Text Field.

Nothing to apologize for - what you posted was correct, I just had a little more time to kill than you 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 6

Speaking of killing things... I can't believe that anyone would put a default on a password column unless it's meant to daze and confuse. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

If you define an Index on an int column it tasks 4 bytes of storage.

If you define an Index on a CHAR(30) it would take 30 bytes of Storage.

This has an impact on how many pages SQL Server has to search to return the record.

The choice of the datatype used for an Index impacts on performance.

This is a very simplified explanation, however this subject is explained greater Detail in various Microsoft Publications, Forums , Blogs and Articles as well as the Microsoft Press Book for the SQL Server Implementation & Maintenance MCTS Exam.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 8

Jeff Moden (2/21/2010)
Speaking of killing things... I can't believe that anyone would put a default on a password column unless it's meant to daze and confuse. 😉

The original posted query has many fine features - that is but one 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi