Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name =
SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
sqlsql

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 Index on Non-Unique Column

I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity). You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
Is the clustered index unique? If not, why not? Also, what is the PK?
Have you got the complete build script for the table, including indexes and
constraints?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:036D72E7-F282-4316-AA6B-53F555B9DF19@.microsoft.com...
I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity).
You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
|||It does sound like the clustered index is being wasted on field that doesn't
usualy get searched.
Clustered indexes are great for range queries. Say you wanted to get a list
of records from 07/010/2007 to 07/31/2007, having a clueterd index on the
date field would be great.
Nonclustered indexes are better for queries that return one or a few records.
MG
"jason7655" wrote:

> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
|||jason7655 wrote:
> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
Why is it a problem that the clustered index is never used?
Anyway, if the clustered index is not unique, then a uniqueifier is only
added to "duplicate" rows. For those rows, the key requires more
storage. Depending on your table structure it will require anywhere
between 6 and 8 bytes more per row. This extra space might not be a
problem for you, but that is something for you to decide.
Another thing to remember is that the clustered index keys are also
stored in all nonclustered indexes. So a narrow clustered index key
(such as int) is preferred over a wide index key. If you choose a very
wide clustered index key, then you other nonclustered index will grow a
lot (and slow down just as much).
HTH,
Gert-Jan
|||PK = Primary Key
Let me try to explain a little better.
This table has 3 indexes.
Index 1
Primary key, clustered. 1,2,3,4,5,etc.
Index 2
non-clustered. Use 2nd most.
Index 3
non-clustered, but the main column used by most queries. Used the most.
From my limited reading of sites (including
http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
various other documents) it would seem that we could gain performance by
changing the Index 3 to clustered.
It's not unique and that raises another question. I would hope that the SQL
Server "uniquefier" that gets added would not change the makeup of the
current data, only create a new column.
Does that make things more clear? Is this a good idea?
"Gert-Jan Strik" wrote:

> jason7655 wrote:
> Why is it a problem that the clustered index is never used?
> Anyway, if the clustered index is not unique, then a uniqueifier is only
> added to "duplicate" rows. For those rows, the key requires more
> storage. Depending on your table structure it will require anywhere
> between 6 and 8 bytes more per row. This extra space might not be a
> problem for you, but that is something for you to decide.
> Another thing to remember is that the clustered index keys are also
> stored in all nonclustered indexes. So a narrow clustered index key
> (such as int) is preferred over a wide index key. If you choose a very
> wide clustered index key, then you other nonclustered index will grow a
> lot (and slow down just as much).
> HTH,
> Gert-Jan
>
|||I've also found the following snippet that has gotten me a little confused
and seems to go counter to other things I've read:
"Think of a clustered index as the foundation of the table. Keep it simple
and clean. The point is that if you add non-clustered indexes to the table,
they repeat the clustered index and add the indexed column(s). So ideally
your clustered index should be on a single column.
Ideally, a clustered index should be on a column where new rows have a
steadily increasing value. Otherwise the clustered index will get fragmented,
and so will the non-clustered indexes based on it.
In short, for a table that sees lots of inserts, use only the identity
column for the clustered index."
(http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I could use some clarification on this one.
"jason7655" wrote:
[vbcol=seagreen]
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
> "Gert-Jan Strik" wrote:
|||Jason,
Finding rows by seeking the clustered index is less expensive than
seeking a nonclustered index followed by a bookmark lookup. However, for
exact match selects, the difference usually isn't that big, for example
WHERE my_id = 15.
Where the clustered index really comes in handy is when you select a
range that potentially returns a lot of rows (or causes a lot of rows to
be processed). For example WHERE order_date >= '20070701' AND order_date
< '20070801'. In that case, the column order_date would really benefit
from a clustered index.
Another situation is a table that is joined very often to the table that
it is referrencing, and the Primary Key contains the Foreign Key of the
referenced table. For example a "Order Details" table with a Primary Key
on (order_id, line_no) that references an Orders table on column
(order_id). In such a case, creating the clustered index on the Primary
Key/Foreign Key can be a good choice. Basically it is the same rule
here. When joining, there are potentially many rows that need to be
processed.
So changing your index 3 to be the clustered index could be a good
choice, but there is not enough information for me to decide. If you
join this table a lot on the columns of index 1, then join performance
could drop. You should analyse your workload, and decide which queries
are most important (with respect to performance). The, if possible, you
should test the different scenario's and see what works best for you.
BTW: if the clustered index is defined as not unique, the uniqueifier is
added under the covers. Its existance of columns values will never be
exposed when you select from the table.
As for your other post, see my opinion inline:

> I've also found the following snippet that has gotten me a little confused
> and seems to go counter to other things I've read:
> "Think of a clustered index as the foundation of the table. Keep it simple
> and clean. The point is that if you add non-clustered indexes to the table,
> they repeat the clustered index and add the indexed column(s).
Correct.

> So ideally your clustered index should be on a single column.
The point is not how many columns it concerns. The point is how many
bytes the key uses. From that point of view, two int columns are better
than one varchar(50) column with an average fill of 25 characters. Even
6 int columns are better than a varchar(50) column with average string
size of 25 characters.

> Ideally, a clustered index should be on a column where new rows have a
> steadily increasing value. Otherwise the clustered index will get fragmented,
True. So from that point of view, a clustered index on a GUID is not a
good idea, unless you defragment regularly.

> and so will the non-clustered indexes based on it.
I think this is false. Nonclustered indexes will fragment if new values
of the (nonclustered) indexed key are not "steadily increasing".
However, since nonclustered indexes are much smaller than the clustered
index (which includes the table data), the cost of fragmentation of a
nonclustered index is typically a lot smaller. Of course, regular
defragmenting is the solution for this too.
There is one exception. If your nonclustered index has a very low
selectivy (just a few distinct values), then the nonclustered index'
fragmentation will be very similar to the clustered index'
fragmentation.

> In short, for a table that sees lots of inserts, use only the identity
> column for the clustered index."
> (http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I definitely disagree with this statement as a general rule, especially
because of the compulsory tone.
For starters, if you don't use Identity as a (surrogate) key, then IMO
you should not add one.
Next, in many situations, the primary key's index is used a lot in
joins, but in other situations (such as filtering on "Lookup Tables")
the optimizer's access path is different and the primary key's index is
used very little.
And finally: it is very unusual to have range selects on an Identity
column. Such range selects are much more common for datetime columns.
So putting a clustered index on an Identity Primary Key could be a good
choice, but it really depends on your situation, and IMO you should not
have policy to always add an Identity and/or to always create the
clustered index on the Identity.
HTH,
Gert-Jan
jason7655 wrote:
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
>
[snip]

Clustered Index

Hi everybody.
I've have a few questions about Clustered Index.
Somebody can help me?
1.- How many Clustered Index can i have a table?
2.- How many fields can i include in a Clustered Table?
3.- Can exists a clustered and unclustered index in a table?
Thanks for your advice.
Pablo Salazar.
1.One - clustered indexes physically order the data, so more than one would
be impossible.
2.16
3.Yes
All this and more is available in books online under "specifications-SQL
Server objects"
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:ugLICulIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Hi everybody.
> I've have a few questions about Clustered Index.
> Somebody can help me?
> 1.- How many Clustered Index can i have a table?
> 2.- How many fields can i include in a Clustered Table?
> 3.- Can exists a clustered and unclustered index in a table?
> Thanks for your advice.
> Pablo Salazar.
>
>
|||Pablo
You can only have 1 clustered index on any given table. seeing as how it is Clustered and ordered only by that column. You cannot order it another physical way also.
You can have up to 16 columns specified in the index.
and Yes you can have multiple clustered indexes and only 1 clustered.
You normally try and make the Clustered index out of the most unique column in the database. Usually a Primary Key field. Then you can build the nonclustered indexes.
Jeff
|||If you use the Enterprise Manager tool to create yoouable, and add the
PK...that PK field is going to be your clustered index, even if it is not
what you wanted.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:OeNp9xlIEHA.3248@.TK2MSFTNGP12.phx.gbl...
> 1.One - clustered indexes physically order the data, so more than one
would
> be impossible.
> 2.16
> 3.Yes
> All this and more is available in books online under "specifications-SQL
> Server objects"
> HTH,
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
> news:ugLICulIEHA.3276@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Jeff
I'm sure you didn't mean to say 'multiple clustered indexes'.
However, I'm not sure you didn't mean the last sentence. This is definitely
not a universal truth. A clustered index can retrieve duplicate data much
more quickly than a nonclustered, for example if you want everyone in the
same zip code. WIth a clustered index on zipcode, all the relevant rows will
be together. A nonclustered works well for unique data where you're
retrieving a single row, so why not use a nc index there, and save your
clustered for where it will do more good and a nc won't help?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:3855777C-E84F-4FB7-AC62-95F1C7C22A9C@.microsoft.com...
> Pablo
> You can only have 1 clustered index on any given table. seeing as how it
is Clustered and ordered only by that column. You cannot order it another
physical way also.
> You can have up to 16 columns specified in the index.
> and Yes you can have multiple clustered indexes and only 1 clustered.
> You normally try and make the Clustered index out of the most unique
column in the database. Usually a Primary Key field. Then you can build
the nonclustered indexes.
> Jeff
|||Thank you Kalen.
I did not mean to mislead him. My fingers were faster than my head. Of course I did not mean that.
Thanks
Jeff
|||We develop K-12 school admin packages and Municipal accounting packages...
Most of our tables have SCHOOL YEAR (or FISCAL YEAR) and BUILDING as the first two columns.
We then CLUSTER our PRIMARY INDEX with these first two columns.
For example, our STUDENT CLASS table has a primary clustered index of:
YEAR, BUILDING, COURSE, SECTION, MEETING TIME, STUDENT ID
then our MEETING TIME table has a primary clustered index of:
YEAR, BUILDING, COURSE, SECTION, MEETING TIME
The JOINS are clear - the data is obvious.
If the SQL DB engine is going to page several rows with one I/O, they are related (same year, same building).
Seems to be working very well for us.
|||Hi Steve
I didn't mean to imply that clustering the primary key (or other unique
data) was always a bad idea. I just told Jeff that putting the clustered
index on a unique key wasn't a universally accepted rule of thumb. There is
no 'one size fits all' to determining the best columns for the clustered
index.
YMMV
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:5E8B6B28-83F6-47FD-94F4-85ADF2CC4293@.microsoft.com...
> We develop K-12 school admin packages and Municipal accounting packages...
> Most of our tables have SCHOOL YEAR (or FISCAL YEAR) and BUILDING as the
first two columns.
> We then CLUSTER our PRIMARY INDEX with these first two columns.
> For example, our STUDENT CLASS table has a primary clustered index of:
> YEAR, BUILDING, COURSE, SECTION, MEETING TIME, STUDENT ID
> then our MEETING TIME table has a primary clustered index of:
> YEAR, BUILDING, COURSE, SECTION, MEETING TIME
> The JOINS are clear - the data is obvious.
> If the SQL DB engine is going to page several rows with one I/O, they are
related (same year, same building).
> Seems to be working very well for us.
|||Kalen
I must agree - it is so much about the data. In the example I described, actually, the keys, although unique, are barely unique. We typically have 100,000+ rows with the same YEAR+BLDG.
I recall situations where "reversing" the ASN # of a record created a better "key" value for binary tree searching.
When we migrated from VAX/VMS ISAM files to SQL SERVER three years ago, your book "Inside SQL Server 2000" helped us understand what was under the hood of this DB engine (and it was very familiar).
The reason I posted my reply was to promote that "multi-column" keys are ok in the world (although I still get resisitance from ACCESS programmers who worship "identity" columns for primary keys).
Steve
|||> The reason I posted my reply was to promote that "multi-column" keys are
ok in the world (although I still get resisitance from ACCESS programmers
who worship "identity" columns for primary keys).
Some of us have crossed over :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:A83AFDE9-1246-4B65-9E0C-7F467CCEBA37@.microsoft.com...
> Kalen
> I must agree - it is so much about the data. In the example I described,
actually, the keys, although unique, are barely unique. We typically have
100,000+ rows with the same YEAR+BLDG.
> I recall situations where "reversing" the ASN # of a record created a
better "key" value for binary tree searching.
> When we migrated from VAX/VMS ISAM files to SQL SERVER three years ago,
your book "Inside SQL Server 2000" helped us understand what was under the
hood of this DB engine (and it was very familiar).
> The reason I posted my reply was to promote that "multi-column" keys are
ok in the world (although I still get resisitance from ACCESS programmers
who worship "identity" columns for primary keys).
> Steve

Cluster-aware SP

Hi,
I've read that installing a SQL Server SP is cluster-aware.
I'm just not sure what this means.
Does this mean that when running the setup on the active node (given 2
nodes), the setup will
- install files on the passive node (node2)
- failover to the other node (node2)
- install files on the first node (node1)
- failback to node1
or is this way off?
After installing the nodes probably needs a reboot. Running on node1;
- reboot node2
- when back up, failover to node2
- reboot node1
- when back up, failback to node1
?
Thanks for any enlightening.
Regards,
audunj@.gmail.com wrote:
> Hi,
> I've read that installing a SQL Server SP is cluster-aware.
> I'm just not sure what this means.
> Does this mean that when running the setup on the active node (given 2
> nodes), the setup will
> - install files on the passive node (node2)
> - failover to the other node (node2)
> - install files on the first node (node1)
> - failback to node1
> or is this way off?
> After installing the nodes probably needs a reboot. Running on node1;
> - reboot node2
> - when back up, failover to node2
> - reboot node1
> - when back up, failback to node1
> ?
> Thanks for any enlightening.
> Regards,
>
SP will install files on both nodes but will ask for reatart only the
node you started sp, you need to restart the other one also (you'll
probably want to wait till the first one boots up)
|||Cluster aware means acting appropriately in all cluster situations. If you
install a SP to an instance starting on the host node, it will update all
binaries on all accessable nodes AND run SQL update scripts. It will advise
you about rebooting both the local and remote node(s) but will not force a
reboot. If you run the SP from an node that does not currently host the
target instance, it will update local binaries only. Again, it will advise
but not force a reboot. This is covered in the Service Pack README file.
Hotfixes exhibit the same behavior. In some circumstances, SPs will not do
a binary-only local update and you will have to run the service pack again
on the entire instance.
Geoff N. Hiten
Microsoft SQL Server MVP
<audunj@.gmail.com> wrote in message
news:1118300953.720619.248550@.g44g2000cwa.googlegr oups.com...
> Hi,
> I've read that installing a SQL Server SP is cluster-aware.
> I'm just not sure what this means.
> Does this mean that when running the setup on the active node (given 2
> nodes), the setup will
> - install files on the passive node (node2)
> - failover to the other node (node2)
> - install files on the first node (node1)
> - failback to node1
> or is this way off?
> After installing the nodes probably needs a reboot. Running on node1;
> - reboot node2
> - when back up, failover to node2
> - reboot node1
> - when back up, failback to node1
> ?
> Thanks for any enlightening.
> Regards,
>
|||Hi, thanx for the feedback.
How about availability during this SP installation? Will the server be
available for user connections as long as I do not reboot the nodes at
the same time? Or will I have to schedule with downtime?
TIA,
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:eNMygcPbFHA.2696@.TK2MSFTNGP09.phx.gbl:

> Cluster aware means acting appropriately in all cluster situations.
> If you install a SP to an instance starting on the host node, it will
> update all binaries on all accessable nodes AND run SQL update
> scripts. It will advise you about rebooting both the local and remote
> node(s) but will not force a reboot. If you run the SP from an node
> that does not currently host the target instance, it will update local
> binaries only. Again, it will advise but not force a reboot. This
> is covered in the Service Pack README file. Hotfixes exhibit the same
> behavior. In some circumstances, SPs will not do a binary-only local
> update and you will have to run the service pack again on the entire
> instance.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> <audunj@.gmail.com> wrote in message
> news:1118300953.720619.248550@.g44g2000cwa.googlegr oups.com...
>
|||If you are applying the SP to an entire instance, that SQL server instance
will be unavailable. If you are updating local binaries only such as
replacing a failed node, then the instance will remain online.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns96718D200BF43gurbaohotmailcom@.129.250.171. 67...
> Hi, thanx for the feedback.
> How about availability during this SP installation? Will the server be
> available for user connections as long as I do not reboot the nodes at
> the same time? Or will I have to schedule with downtime?
> TIA,
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:eNMygcPbFHA.2696@.TK2MSFTNGP09.phx.gbl:
>

cluster white paper

Is there a reliable white paper for clustering sql2k on win2k3. Ive already
got the OS clustered.
SQL2K SP3
TIA, ChrisR
ChrisR wrote:
> Is there a reliable white paper for clustering sql2k on win2k3. Ive
> already got the OS clustered.
Some of these might help:
http://www.microsoft.com/resources/d...rt4/c1261.mspx
http://support.microsoft.com/default...lurb101802.asp
http://msdn.microsoft.com/library/de...ering_2icn.asp
David Gugick
Imceda Software
www.imceda.com
|||This is great information. Does anyone have any updated information on
setting up SQL Server 2000 in a Windows 2003 environment?
Just wondering if 2003 brings anything more to the table over Windows Server
2000.
Thanks!
Tony
"David Gugick" wrote:

> ChrisR wrote:
> Some of these might help:
> http://www.microsoft.com/resources/d...rt4/c1261.mspx
> http://support.microsoft.com/default...lurb101802.asp
> http://msdn.microsoft.com/library/de...ering_2icn.asp
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

cluster white paper

Is there a reliable white paper for clustering sql2k on win2k3. Ive already
got the OS clustered.
--
SQL2K SP3
TIA, ChrisRChrisR wrote:
> Is there a reliable white paper for clustering sql2k on win2k3. Ive
> already got the OS clustered.
Some of these might help:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part4/c1261.mspx
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fwcd101802%2Fwcdblurb101802.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
--
David Gugick
Imceda Software
www.imceda.com|||This is great information. Does anyone have any updated information on
setting up SQL Server 2000 in a Windows 2003 environment?
Just wondering if 2003 brings anything more to the table over Windows Server
2000.
Thanks!
Tony
"David Gugick" wrote:
> ChrisR wrote:
> > Is there a reliable white paper for clustering sql2k on win2k3. Ive
> > already got the OS clustered.
> Some of these might help:
> http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part4/c1261.mspx
> http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fwcd101802%2Fwcdblurb101802.asp
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> --
> David Gugick
> Imceda Software
> www.imceda.com
>