Does anyone know if there is a command in query analizer for sql server 2005 (Express) that changes the collation of all the char (varchar, nvarchar etc) in a table or in a database. And I don't mean to set the collation when creating a database, but to modify a database's collation seting.
thank you
This information might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3zw3.asp
Buck Woody
Hi,
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank ';
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank ';
GO
DROP TABLE t1
DROP TABLE t2
ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.
select datalength(charcol), datalength(varcharcol)
from t1
select datalength(charcol), datalength(varcharcol)
from t2
-nationalityid int autoincrement PK
-nationality varchar(50)
the clustered index is on nationality
however; when i do a select i still get a record set back ordered by
the nationality id...what am I doing wrong?
-Jim"Jim" <jim.ferris@.motorola.com> wrote in message
news:729757f9.0311282036.68e87388@.posting.google.c om...
> I put a clustered index on a table with 2 columns
> -nationalityid int autoincrement PK
> -nationality varchar(50)
>
> the clustered index is on nationality
> however; when i do a select i still get a record set back ordered by
> the nationality id...what am I doing wrong?
> -Jim
When you do a SELECT, you must specify ORDER BY if you want the result set
to be in a certain order. Even with a clustered index on a table, there is
no guarantee that you will get your results in any specific order.
Simon|||Hi ,
The clustered index is usually set on in the primary key field.The
query optimiser selects the records and displays as per the P.K Field
by default ascending.
Since u have CLUSTERED INDEX which is a non P.K field , U hadthe
problem.
Generally , Clstered index is only used for the sake of speed fetching
..
I thing my words clear.
Raghu
India|||"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0311290600.46954ab5@.posting.google.c om...
> Hi ,
>
> The clustered index is usually set on in the primary key field.The
> query optimiser selects the records and displays as per the P.K Field
> by default ascending.
"Maybe".
Without and ORDER BY there is no guarantee what order you will get stuff
back in.
Now, in most cases you may see the behaviour you describe, but I would not
count on it.
> Since u have CLUSTERED INDEX which is a non P.K field , U hadthe
> problem.
>
> Generally , Clstered index is only used for the sake of speed fetching
> .
>
> I thing my words clear.
>
> Raghu
> India
Thanks,
Jim GeissmanOn 5 Aug 2005 15:09:48 -0700, jim_geissman@.countrywide.com wrote:
(snip)
>Are varchar
>columns stored on the page along with the fixed-size columns, or
>are they managed separately with the page containing pointers
>to them?
Hi Jim,
Varchar (and nvarchar, varbinary) columns are stored on the data page.
Text (and ntext, image) columns are stored on seperate pages, with only
a pointer on the data page. Unless the "text in row" option is set for
the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo.
Jim|||(jim_geissman@.countrywide.com) writes:
> One table I manage has a clustered index, and it includes some
> varchar columns. When it is initially created, all the columns
> in the clustered index are populated, and then some of the longer
> varchars are populated through update queries. If the varchar
> columns are stored outside the clustered structure, then it would
> make sense to create the clustered index before populating the
> varchar columns. Otherwise it would make sense to wait, because
> populating the varchars might cause page splits. Are varchar
> columns stored on the page along with the fixed-size columns, or
> are they managed separately with the page containing pointers
> to them?
As Hugo said, the varchar data is stored within the page.
One should be careful with having to large clustered-index keys. In
non-clustered indexes, the value of clustered-index key is used as
row locator. Thus a wide clustered key, also affects the size of
the non-clustered index.
Of course, if you don't have any non-clustered indexes on the table,
this is not much of an issue.
--
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Correct me if I'm mistaken but the values of the columns that make up the
clustered index are only used as the row locator if the index is set to
unique otherwise a uniqueidentifier is used. Either way the values are
still stored in all the nonclustered indexes so wide clustered indexes are
generally unpleasant. Small clustered indexes like a single int can still
make the nonclustered indexes large if when not set to unique.
At least this is always what appears to happen...
Danny
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AA8DD847A5CYazorman@.127.0.0.1...
> (jim_geissman@.countrywide.com) writes:
>> One table I manage has a clustered index, and it includes some
>> varchar columns. When it is initially created, all the columns
>> in the clustered index are populated, and then some of the longer
>> varchars are populated through update queries. If the varchar
>> columns are stored outside the clustered structure, then it would
>> make sense to create the clustered index before populating the
>> varchar columns. Otherwise it would make sense to wait, because
>> populating the varchars might cause page splits. Are varchar
>> columns stored on the page along with the fixed-size columns, or
>> are they managed separately with the page containing pointers
>> to them?
> As Hugo said, the varchar data is stored within the page.
> One should be careful with having to large clustered-index keys. In
> non-clustered indexes, the value of clustered-index key is used as
> row locator. Thus a wide clustered key, also affects the size of
> the non-clustered index.
> Of course, if you don't have any non-clustered indexes on the table,
> this is not much of an issue.
>
> --
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Danny (someone@.nowhere.com) writes:
> Correct me if I'm mistaken but the values of the columns that make up the
> clustered index are only used as the row locator if the index is set to
> unique otherwise a uniqueidentifier is used. Either way the values are
> still stored in all the nonclustered indexes so wide clustered indexes are
> generally unpleasant. Small clustered indexes like a single int can still
> make the nonclustered indexes large if when not set to unique.
> At least this is always what appears to happen...
The clustered index is always used as a row locator.
When the clustered index is not unique, SQL Server adds a 32-bit
"uniquifier". That is, not a 128-bit uniqueidentifier.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
