Just How Does SQL Server Define A Unique Index

Question

Post reply

Just How Does SQL Server Define A Unique Index

Viewing 15 posts - 16 through 30 (of 37 total)

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 1

Hi David

well..what a bummer

i only tried it at the database level, not table level

guess i will change it back as i do not want to worry about objects having sensitivity

Thanks

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi Folks

Can some one tell me the correct syntax for changing the COLLATION to SQL_Latin1_General_Cp1_CS_AS for a column called Name in a table called DTREE

I cannot figure out the mumbo jumbo that MS puts out for with all their brackets and squigly characters for altering a table column. You need a PHD to interpret the whole syntax

ALTER TABLE table_name

ALTER COLUMN column_name

{

type_name[({precision[.scale]})][NULL|NOT NULL]

{DROP DEFAULT

| SET DEFAULT constant_expression

| IDENTITY [ ( seed , increment ) ]

}

| ADD

{ < column_definition > | < table_constraint > } [ ,...n ]

| DROP

{ [ CONSTRAINT ] constraint_name

| COLUMN column }

] }

< column_definition > ::=

{ column_name data_type }

[ [ DEFAULT constant_expression ]

| IDENTITY [ ( seed , increment ) ]

]

[ROWGUIDCOL]

[ < column_constraint > ] [ ...n ] ]

< column_constraint > ::=

[ NULL | NOT NULL ]

[ CONSTRAINT constraint_name ]

{

| { PRIMARY KEY | UNIQUE }

| REFERENCES ref_table [ (ref_column) ]

[ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]

[ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) }

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ (ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]

[ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]

}

Thanks

Jim

WayneS SSC Guru Points: 95409 More actions · Answer 3

JC-3113 (10/14/2010)
Hi Folks
Can some one tell me the correct syntax for changing the COLLATION to SQL_Latin1_General_Cp1_CS_AS for a column called Name in a table called DTREE
Thanks
Jim

ALTER TABLE DTREE ALTER COLUMN Name varchar(100) COLLATE SQL_Latin1_General_Cp1_CS_AS;

You do need to specify the datatype (I assumed varchar(100) here), and to specify NOT NULL if applicable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Thanks WayneS

was out on Friday so i will try it today

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 5

Hi Folks

I changed the Case Sensitivity for the table column

Now I have an issue with what looks like trailing spaces in the name value

So a name value of "P00204" is being treated the same as "P00204 "

when i go to apply the index

1> CREATE UNIQUE INDEX [DTREE_PRIMARY] ON DTREE (PARENTID,OWNERID,NAME);

2> go

Msg 1505, Level 16, State 1, Server NSAB-SS73-SQL-N, Line 1

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DTREE'

and the index name 'DTREE_PRIMARY'. The duplicate key value is (363988, -2000, P00204).

The statement has been terminated.

If i look at the data:

select Parentid, Ownerid,'**' + LEFT (name,12) + '**' as Name from dtree;

Parentid Ownerid Name

----------- ----------- ------------

363988 -2000 **P00204 **

363988 -2000 **P00204**

Why does sql server think these two records are the same when they are not ?

Thanks

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

Hi Folks

if anyone can explain the last question to me, i would appreciate it

Thanks

Jim

Mike John SSCertifiable Points: 7186 More actions · Answer 7

I am not sure of the datatype of the column but would guess it is varchar(n) then this article should explain it..

http://decipherinfosys.wordpress.com/2007/02/15/ansi_padding-and-trailing-spaces-in-sql-server/

In brief with varchar comparisons the ansi rules for comparisons state that the shorter string should be padded with spaces to the same length as the longer, so it is behaving spot on according to the standard.

I have not used Oracle for a fair while but am surprised it is not treating them in he the same way.

Can you confirm the data types you have used in O and SQL Server?

Mike John

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Hi Mike

here are the two column definitions:

Oracle:

NAME NOT NULL VARCHAR2(255 CHAR)

SQL Server

NAME VARCHAR (255) NOT NULL

Oracle treats them as unique as one has a triling space or control character (added by mistake)

whereas SQL server treats them the same

I wil review the article

Thanks

Jim

Mike John SSCertifiable Points: 7186 More actions · Answer 9

I am not sure of the precise rules for Oracles varchar2 - never did really work out why they had a 2 on the end when varchar was adequately defined in ansi already, but I suggest SQL Server is behaving according to ansi standard rules and Oracle is not....

Good luck.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

I cannot figure out the mumbo jumbo that MS puts out for with all their brackets and squigly characters for altering a table column. You need a PHD to interpret the whole syntax

Ha, I sometimes feel like I'm the only stooge when someone posts something like "It's all spelled out here in BOL, which is the authoritative (true), well-written (really?), understandable (sometimes) explanation to all your problems." 🙂

Here's a link to an explanation of the symbols MS uses in BOL for optional parameters, required arguments, etc.:

http://msdn.microsoft.com/en-US/library/ms177563%28v=SQL.90%29.aspx.

It is especially difficult to follow when the command you're looking up is something like ALTER TABLE, which has so many possible uses that there will, necessarily, be many diverse syntaxes. In such cases, I usually find it's easier to Google "ALTER TABLE t-sql change collation". One of the first results in this case was a very easy-to-follow article from Pinal Dave at http://blog.sqlauthority.com/2008/12/20/sql-server-change-collation-of-database-column-t-sql-script/.

So use Google, but if you need a definitive answer, rely on BOL.

I often skip down to the examples at the bottom of BOL, which I find apply to my situation (or are very close) most of the time and are easier to follow.

I would concur with others' posts here about case-sensitivity: all-or-nothing is a lot cleaner for everyone involved and less likely to cause problems. If you want case-sensitivity in your database, make the entire database case-sensitive. Otherwise, every time a new table/SP/UDF is created, you'll have to ask yourself: is this one case-sensitive? Should it be?

Good luck!

Rich

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

Hi Rich

thanks for the info

this all started because i could not index a column because sql server told me the columns were the same, when they are not because of a triling space or control character

i guess i just do not understand why sql server thinks that a column with the same name, one with no trailing space or control character and one with nothnig at the end thinks that ther are the same when trying to put an index on them both. To me they are not the same, so i do not get it

Thanks

Jim

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

i guess i just do not understand why sql server thinks that a column with the same name, one with no trailing space or control character and one with nothnig at the end thinks that ther are the same when trying to put an index on them both. To me they are not the same, so i do not get it

Well, I'm no expert, but I think Mike John's post above and the link he provided answer the issue in spades: SQL Server follows the ANSI SQL standard for comparing strings, by padding the shorter string with spaces to make the two strings the same length and then comparing.

You may think this is wrong, but at least Microsoft follows the ANSI standard (and, it would seem, Oracle does not). N.B.: I'm not saying one product is better, just that one is standards-compliant here.

Rich

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 13

You are correct

In Oracle (from a web site)

..Thus the difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.

The problem I have is..is that SQL Server is handling the index wrong when it comes to creation

the data in the database is as i think it should be

one record with the name with a space at the end and one record with name with no space at the end

i see these two record values as being different and they are if you grab the data as the space is in the value of the name

BUT when it comes time to add the indexes, all of a sudeen SQL SERVER says "No" they are the same and I cannot add an index

That is what I do not get, but then i don't get alot about SQL Server right now 🙂

Thanks

Jim

Rich Mechaber SSChampion Points: 11130 More actions · Answer 14

Hey Jim, maybe this will help (maybe not....:-D ):

CREATE TABLE TrailingSpace

(id INT IDENTITY (1,1),

myText VARCHAR(6)

);

GO

INSERT INTO TrailingSpace (myText) VALUES ('abc');--no space

INSERT INTO TrailingSpace (myText) VALUES ('abc ');--1 space

INSERT INTO TrailingSpace (myText) VALUES ('abc ');--2 spaces

INSERT INTO TrailingSpace (myText) VALUES ('abc ');--3 spaces

GO

--Returns 4 rows

SELECT *

FROM TrailingSpace;

--Returns 1 row

SELECT DISTINCT myText

FROM TrailingSpace;

DROP TABLE TrailingSpace;

So, yes, those values are different to you and me, but ANSI standard says they are the same for the purposes of comparison to each other. If they're the same, then they aren't UNIQUE and you cannot build a UNIQUE index on that column with that data. SQL server is handling the index creation correctly, b/c you are trying to build a UNIQUE index on a column with non-unique values in it.

Does that make sense?

Rich

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 15

Hi Rich

thanks for taking the time to expand on this

i understand the way it is being handled, but it is not logical to my way of thinking

sql server is actually stripping out characters to make the name the same when in my way of thinking it should not remove any characters. it is changing my data for indexing purposes or when it is selected distinctly

See, to me,these values are distinct in the database

To SQl Server they are not and that is what bothers me

SELECT id, '***' + mytext + '***' "myText"

FROM TrailingSpace;

go

id myText

----------- ------------

1 ***abc***

2 ***abc ***

3 ***abc ***

4 ***abc ***

Also, when you do a LEN on the table, it says they all are 3 characters long, when in actuality, they are not

SELECT id, len ( myText) "myText"

FROM TrailingSpace;

go

id myText

----------- -----------

1 3

2 3

3 3

4 3

I guess this is going to be a major issue in trying to index any data that has this problem that i import

I would rather it strip the trailing spaces and control characters out when it is imported

Thanks Again

Jim