Collation in SQL Server

Hi friends,
What is Collation in SQL Server and how does it affect Search and Sort in
database?"Prabhat" <not_a_eMail@.hotmail.com> wrote in message
news:e49pDEv4HHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi friends,
> What is Collation in SQL Server and how does it affect Search and Sort in
> database?
>
http://msdn2.microsoft.com/en-us/library/ms143726.aspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sqlsql

Collapsing groups while toggling intteractive sort

Hi all
i have a table with grouping by the first column.
the details section is hidden and can be expanded by the group box (with +).
i have interactive sort based on the second column.
While toggling the interactive sort button the expanded field being colappsed.
Is there any option for keeping the expanded rows in their state?

THank You

No, currently toggling interactive sort would reset the show/hide state. Changing this behavior is on our wish list for a future release.

Code Page Headaches

Hi

I am looking at upgrading some SQL 6.5 servers to SQL 2000. The 6.5 servers run a mixture of code page 850 & ISO 8890 and sort orders of 42 & 52.

We want to ensure all the 6.5 databases are migrated to 2000 BUT want to future proof the SQL 2000 databases by supporting unicode characters, as the SQL 2000 databases will be avialable world wide to all languages. That said, we want to ensure the existing table structures are preserved and run at 6.5 compatibility level. I am a little concerned as I am unsure if unicode support will alter table structures and require a re-write of the database schema or table structures.

Can someone help by giving me a straight forward explanation of the implications of my plans please?

Many thanks,I would just one thing at a time.

Do the upgrade to 2000 and make sure that is ok...

when your happy it is then make a copy of the db(s) and change the char and varchar types to be nchar and nvarchar respectively.
Test and when your happy take the live server offline and backup the server (optionally restore it to another machine or have tested that your backups work) and then do the upgrade.

One thing at a time (and slowly) or your asking for trouble.

good luck

Marc|||Unicode versions of chartacter fields are nchar and nvarchar which probably won't be available in 6.5 comapatability mode.
Using these fields will mean a change to the database structure.
If you want the database to be specific to a language then it's not too much trouble but if you want it multi-language you will have to include language versions of all the presentation data on separate tables.
To give true international support you will also have to consider the code pages for the sort order - i.e. you will need the alphabetical order for the language selected which will mean either coercing the columns in the query or having a database installed with the correct collation.

Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a se
t
of data, expecially if the result set requires a sort by on those columns. M
y
company is running education services. Let's say our enrollment table
contains the fields year, session (term), course, student_id (fk to student
table), and marks.
We do a lot of queries based on year, session, and course. I'm planning to
put year, session, and course on clustered index. The question is we
definitely will query a particular enrollment for a student using student id
.
I read from many articles that non-clustered index always include the
location of the clustered index. If so, does it mean an index on student_id
(which is already there because of the FK) is the same as building one using
year, session, course, and student_id which would cover many of our queries?> If so, does it mean an index on student_id[vbcol=seagreen]
> (which is already there because of the FK) is the same as building one usi
ng
> year, session, course, and student_id which would cover many of our queries?[/vbco
l]
Yes. By explicitly naming the cl ix columns, you can control the order of th
e columns in the index.
Just be careful, having a wide cl ix makes you nc indexes wide.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...[vbcol=seagreen]
> From what I've read, clustered index is particularly good for returning a
set
> of data, expecially if the result set requires a sort by on those columns.
My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to studen
t
> table), and marks.
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student
id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_i
d
> (which is already there because of the FK) is the same as building one usi
ng
> year, session, course, and student_id which would cover many of our queries?[/vbco
l]|||Thanks Tibor. I'm just curious if I do create an index on all four columns.
Is SQL Server smart enough to see the first 3 are in the clustered index and
not duplicate them in the nc index?
Thanks also for the advice on the wide cl. We were debating whether it is
better to have year + session (5 digits) or year + session + course code (8
digits) as our cl ix. If we have time (yeah, like that will ever happens),
we'll go through the exec plan of our heavy queries with different set up an
d
see what happens.
"Tibor Karaszi" wrote:

> Yes. By explicitly naming the cl ix columns, you can control the order of
the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
>
>|||> Thanks Tibor. I'm just curious if I do create an index on all four columns.">
> Is SQL Server smart enough to see the first 3 are in the clustered index a
nd
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysinde
xes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding
of index architecture
along with how SQL Server can use indexes (like covering a query with non-cl
ustered indexes) will
take you a long way. A big thing is to ask the right questions, and of cours
e later to verify your
thinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor. I'm just curious if I do create an index on all four columns
.
> Is SQL Server smart enough to see the first 3 are in the clustered index a
nd
> not duplicate them in the nc index?
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (
8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up
and
> see what happens.
> "Tibor Karaszi" wrote:
>

Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a set
of data, expecially if the result set requires a sort by on those columns. My
company is running education services. Let's say our enrollment table
contains the fields year, session (term), course, student_id (fk to student
table), and marks.
We do a lot of queries based on year, session, and course. I'm planning to
put year, session, and course on clustered index. The question is we
definitely will query a particular enrollment for a student using student id.
I read from many articles that non-clustered index always include the
location of the clustered index. If so, does it mean an index on student_id
(which is already there because of the FK) is the same as building one using
year, session, course, and student_id which would cover many of our queries?
> If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?
Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
Just be careful, having a wide cl ix makes you nc indexes wide.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> From what I've read, clustered index is particularly good for returning a set
> of data, expecially if the result set requires a sort by on those columns. My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to student
> table), and marks.
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?
|||Thanks Tibor. I'm just curious if I do create an index on all four columns.
Is SQL Server smart enough to see the first 3 are in the clustered index and
not duplicate them in the nc index?
Thanks also for the advice on the wide cl. We were debating whether it is
better to have year + session (5 digits) or year + session + course code (8
digits) as our cl ix. If we have time (yeah, like that will ever happens),
we'll go through the exec plan of our heavy queries with different set up and
see what happens.
"Tibor Karaszi" wrote:

> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
>
>
|||> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding of index architecture
along with how SQL Server can use indexes (like covering a query with non-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course later to verify your
thinking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up and
> see what happens.
> "Tibor Karaszi" wrote:

Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a set
of data, expecially if the result set requires a sort by on those columns. My
company is running education services. Let's say our enrollment table
contains the fields year, session (term), course, student_id (fk to student
table), and marks.
We do a lot of queries based on year, session, and course. I'm planning to
put year, session, and course on clustered index. The question is we
definitely will query a particular enrollment for a student using student id.
I read from many articles that non-clustered index always include the
location of the clustered index. If so, does it mean an index on student_id
(which is already there because of the FK) is the same as building one using
year, session, course, and student_id which would cover many of our queries?> If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?
Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
Just be careful, having a wide cl ix makes you nc indexes wide.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> From what I've read, clustered index is particularly good for returning a set
> of data, expecially if the result set requires a sort by on those columns. My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to student
> table), and marks.
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?|||Thanks Tibor. I'm just curious if I do create an index on all four columns.
Is SQL Server smart enough to see the first 3 are in the clustered index and
not duplicate them in the nc index?
Thanks also for the advice on the wide cl. We were debating whether it is
better to have year + session (5 digits) or year + session + course code (8
digits) as our cl ix. If we have time (yeah, like that will ever happens),
we'll go through the exec plan of our heavy queries with different set up and
see what happens.
"Tibor Karaszi" wrote:
> > If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> > From what I've read, clustered index is particularly good for returning a set
> > of data, expecially if the result set requires a sort by on those columns. My
> > company is running education services. Let's say our enrollment table
> > contains the fields year, session (term), course, student_id (fk to student
> > table), and marks.
> >
> > We do a lot of queries based on year, session, and course. I'm planning to
> > put year, session, and course on clustered index. The question is we
> > definitely will query a particular enrollment for a student using student id.
> > I read from many articles that non-clustered index always include the
> > location of the clustered index. If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
>
>|||> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding of index architecture
along with how SQL Server can use indexes (like covering a query with non-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course later to verify your
thinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...
> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up and
> see what happens.
> "Tibor Karaszi" wrote:
>> > If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the
>> index.
>> Just be careful, having a wide cl ix makes you nc indexes wide.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
>> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
>> > From what I've read, clustered index is particularly good for returning a set
>> > of data, expecially if the result set requires a sort by on those columns. My
>> > company is running education services. Let's say our enrollment table
>> > contains the fields year, session (term), course, student_id (fk to student
>> > table), and marks.
>> >
>> > We do a lot of queries based on year, session, and course. I'm planning to
>> > put year, session, and course on clustered index. The question is we
>> > definitely will query a particular enrollment for a student using student id.
>> > I read from many articles that non-clustered index always include the
>> > location of the clustered index. If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>>