collation conflict resolution

I have a number of databases with quite similar structure, but with
different COLLATION
settings. Unfortunately when the structure (including keys and constraints)
has been created
no one took care of COLLATION at all.
At the moment statements like:
--
SELECT
. . .
(case FieldA
when 1 then (select Source1 from Table1 WHERE ...)
when 2 then (select Source2 from Table2 WHERE ...)
end) as Result,
--
gives COLLATION CONFLICT because Table1 and Table2 have different collation
setting.
But for a half of number of tables it works fine.
What aa I asking about: How to change COLLATION SETTING "on fly" for
different tables.fields ?
May be someone knows any fancy tools or "script wizards" to do so?
Please don't waste this newsgroup space with offering me to insert "COLLATE
DATABASE_DEFAULT"
to each statement which causes such problem. Ok?
Thanks a lot!
GnumHi,
CREATE VIEW FROM Table2
ADD COLLATE AFTER EACH FIELDS.
> when 1 then (select Source1 from Table1 WHERE ...)
(select Source1 from View1 WHERE ...)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2004
"Gnum" <no-email@.mail.boo> wrote in message
news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> I have a number of databases with quite similar structure, but with
> different COLLATION
> settings. Unfortunately when the structure (including keys and
constraints)
> has been created
> no one took care of COLLATION at all.
> At the moment statements like:
> --
> SELECT
> . . .
> (case FieldA
> when 1 then (select Source1 from Table1 WHERE ...)
> when 2 then (select Source2 from Table2 WHERE ...)
> end) as Result,
> --
> gives COLLATION CONFLICT because Table1 and Table2 have different
collation
> setting.
> But for a half of number of tables it works fine.
> What aa I asking about: How to change COLLATION SETTING "on fly" for
> different tables.fields ?
> May be someone knows any fancy tools or "script wizards" to do so?
> Please don't waste this newsgroup space with offering me to insert
"COLLATE
> DATABASE_DEFAULT"
> to each statement which causes such problem. Ok?
>
> Thanks a lot!
> Gnum
>|||Thank you very much for so big
MISUNDERSTANDING!
Have you ever read the question up to the end?
I've written there "
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?"
I need a help, but not a collection of stupid wisdom-like erudition esseys!
Thanks!
"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote in message
news:OOcY6TlvDHA.2880@.tk2msftngp13.phx.gbl...
> Hi,
> CREATE VIEW FROM Table2
> ADD COLLATE AFTER EACH FIELDS.
> > when 1 then (select Source1 from Table1 WHERE ...)
> (select Source1 from View1 WHERE ...)
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2004
> "Gnum" <no-email@.mail.boo> wrote in message
> news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> > I have a number of databases with quite similar structure, but with
> > different COLLATION
> > settings. Unfortunately when the structure (including keys and
> constraints)
> > has been created
> > no one took care of COLLATION at all.
> > At the moment statements like:
> > --
> > SELECT
> > . . .
> > (case FieldA
> > when 1 then (select Source1 from Table1 WHERE ...)
> > when 2 then (select Source2 from Table2 WHERE ...)
> > end) as Result,
> > --
> > gives COLLATION CONFLICT because Table1 and Table2 have different
> collation
> > setting.
> > But for a half of number of tables it works fine.
> >
> > What aa I asking about: How to change COLLATION SETTING "on fly" for
> > different tables.fields ?
> > May be someone knows any fancy tools or "script wizards" to do so?
> >
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?
> >
> >
> > Thanks a lot!
> >
> > Gnum
> >
> >
>

Clustered Indexes

We use uniqueidentifiers as our primary key because we have many oltp
systems that get merged into one corporate reporting structure.
Some tables accepted the default behaviour of SQL Server and made the
primary key the clustered index.
Does a clustered index on a GUID sort them in order or does it use them like
a heap table and insert all rows at the end?
Thanks in advance.Yes, the values are sorted, which can also lead to frequent page splits,
which in effect downgrades insert performance. Columns of type
UNIQUEIDENTIFIER are IMHO a lousy choice for clustered indexes for two main
reasons:
1) the size of the data; and
2) high selectiveness (pages get reordered quite frequently).
A performance gain can be achieved, though, by spreading the table on
several files, while keeping the FILL FACTOR of the clustered index low. Thi
s
would increase possibility of paralelism by enabling paralel inserts to occu
r
on different files concurrently.
I'd use a different column for a clustered index - where inserts are
frequent the best candidate IMHO is a column with a small datatype (int,
bigint) with incremental values (such as an IDENTITY column).
ML
http://milambda.blogspot.com/|||Rather than always inserting at the bottom page, a clustered index will
attempt to insert the row within a page so that the rows are clustered
(sorted) based on the value of the key. It's best not to use a clustered
index unless you are attempting to achieve a specific outcome.
You can use DBCC SHOWCONTIG to check for index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"JI" <jidawgs@.gmail.com> wrote in message
news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
> We use uniqueidentifiers as our primary key because we have many oltp
> systems that get merged into one corporate reporting structure.
> Some tables accepted the default behaviour of SQL Server and made the
> primary key the clustered index.
> Does a clustered index on a GUID sort them in order or does it use them
> like a heap table and insert all rows at the end?
> Thanks in advance.
>|||GUIDs are probably the worst choice for a clustered index, at least as far
as INSERTs go. IDENTITY is probably the best choice for a clustered index
because of its increasing nature, but with many data sources feeding one, it
can be tricky to manage. If the database is designed correctly, each table
should have at least one alternate key, so I'd put the clustered index
there, unless you expect a lot of ranged queries, in which case it may be
better to put the clustered index on the target of the ranged query.
Neither solution may offer much INSERT performance improvement, but it may
significantly improve query performance.
"JI" <jidawgs@.gmail.com> wrote in message
news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
> We use uniqueidentifiers as our primary key because we have many oltp
> systems that get merged into one corporate reporting structure.
> Some tables accepted the default behaviour of SQL Server and made the
> primary key the clustered index.
> Does a clustered index on a GUID sort them in order or does it use them
> like a heap table and insert all rows at the end?
> Thanks in advance.
>|||For sql2k, you can use Gert's xp_new_sequential_guid. This should eliminate
the issues (fragmentation, page split, etc.) with using guid as the primary
key.
http://sqldev.net/xp/xpguid.htm
-oj
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eb86LrNLGHA.2780@.tk2msftngp13.phx.gbl...
> GUIDs are probably the worst choice for a clustered index, at least as far
> as INSERTs go. IDENTITY is probably the best choice for a clustered index
> because of its increasing nature, but with many data sources feeding one,
> it can be tricky to manage. If the database is designed correctly, each
> table should have at least one alternate key, so I'd put the clustered
> index there, unless you expect a lot of ranged queries, in which case it
> may be better to put the clustered index on the target of the ranged
> query. Neither solution may offer much INSERT performance improvement, but
> it may significantly improve query performance.
> "JI" <jidawgs@.gmail.com> wrote in message
> news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||This is interesting. It generates a GUID based on the MAC address of the
NIC in the server. The only problem I see is that there are NICs out there
with the same MAC address--even though that's not supposed to happen.
Therefore it's possible for duplicates to be generated on different
machines. Barring that extremely remote possibility, this looks like a good
answer to the problem. SQL 2005 apparently has a NEWSEQUENTIALID() function
that provides this same functionality.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23GEoJ4NLGHA.668@.TK2MSFTNGP11.phx.gbl...
> For sql2k, you can use Gert's xp_new_sequential_guid. This should
> eliminate the issues (fragmentation, page split, etc.) with using guid as
> the primary key.
> http://sqldev.net/xp/xpguid.htm
>
> --
> -oj
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:eb86LrNLGHA.2780@.tk2msftngp13.phx.gbl...
>|||Actually it does not depend on the NIC (as with newid()). ;-)
http://msdn.microsoft.com/library/e...esequential.asp
-oj
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%238WfHXOLGHA.360@.TK2MSFTNGP12.phx.gbl...
> This is interesting. It generates a GUID based on the MAC address of the
> NIC in the server. The only problem I see is that there are NICs out
> there with the same MAC address--even though that's not supposed to
> happen. Therefore it's possible for duplicates to be generated on
> different machines. Barring that extremely remote possibility, this looks
> like a good answer to the problem. SQL 2005 apparently has a
> NEWSEQUENTIALID() function that provides this same functionality.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23GEoJ4NLGHA.668@.TK2MSFTNGP11.phx.gbl...
>|||Only if you don't have a NIC in the box. If there's a NIC, then it uses the
MAC.
"oj" <nospam_ojngo@.home.com> wrote in message
news:eYvlNvOLGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Actually it does not depend on the NIC (as with newid()). ;-)
> http://msdn.microsoft.com/library/e...esequential.asp
> --
> -oj
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%238WfHXOLGHA.360@.TK2MSFTNGP12.phx.gbl...
>

Clustered Index performance

I have a table with the structure as below. I am running the query 'update
DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
'BTCH00000000030'. There are only 25,000 records in the table and all of the
m
qualify for the update. Since there is a clustered index on the predicate
(BatchID), I would naturally expect this query to run quick. Unfortunately i
t
is taking over 3 seconds to run which is way too long.
If I look at the execution plan, it says 83% of the cost is on a Sort
operation. The arguments of the Sort operation are PrintIndicator desc,
BatchID asc. Can anyone explain what this Sort operation is? I didn't expect
to see it as I'm not retreiving records, just updating.
Thanks,
Dean
CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
[StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[StatementAmount] [money] NOT NULL ,
[StatementDate] [smalldatetime] NOT NULL ,
[CurrentBalance] [money] NOT NULL ,
[OverdueBalance] [money] NOT NULL ,
[CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[EntryDateTime] [datetime] NOT NULL ,
[PrintIndicator] [tinyint] NOT NULL ,
[RowID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
[dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
[dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
GO
CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
[dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
GOThere might be a composite key on the table and which is causing the
sort operation.
If you always use 'BTCH00000000030' to update, just create a view for
this and try to update the view
Please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Dean,
I think we need more info about the execution plan. Which index is the
optimizer using during this operation?

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Can you tell us a little bit more about possible values for column
[PrintIndicator]?
What is the selectivity for those values?
Based on the selectivity, is it valuable to have an index by [PrintIndicator]?
Tips on Optimizing Non-Clustered
SQL Server Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Dean" wrote:

> I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of t
hem
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick. Unfortunately
it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't expe
ct
> to see it as I'm not retreiving records, just updating.
> Thanks,
> Dean
>
>
> CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
> [StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [StatementAmount] [money] NOT NULL ,
> [StatementDate] [smalldatetime] NOT NULL ,
> [CurrentBalance] [money] NOT NULL ,
> [OverdueBalance] [money] NOT NULL ,
> [CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryDateTime] [datetime] NOT NULL ,
> [PrintIndicator] [tinyint] NOT NULL ,
> [RowID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
> [dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
> [dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
> GO
> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
>|||"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:13F04AE4-2B00-4CB2-AB55-DA9A416CD759@.microsoft.com...
>I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of
> them
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick.
Why would you expect that? The clustered index makes it cheap to identify
the set of records that qualify for the update. But you said that all of
the rows qualify!

>Unfortunately it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't
> expect
> to see it as I'm not retreiving records, just updating.
Since the update changes the PrintIndicator on every row, it must
completely rewrite this index:

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Since BatchID is the clustered index, this nonclustered index contains
PrintIndicator and BatchID. Thus the two-column sort.
However, while not cheap, 3 seconds does seem a bit long to update 25,000
rows, even with a sort. Is this a disk sort? (Look at physical IO). How
much memory does SQL Server have?
David|||I'm not sure which index is the optimizer. Is this something I can see in th
e
execution plan?
Print indicator is really a bit field but had to make it tinyint to put on
index on it. Possible values are only 0, 1 but testing has shown the index o
n
PrintIndicator helps considerably.
Thanks,
Dean|||Dean,

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
You can use "set statistics profile on" to display the profile information
for the statement.
AMB
"Dean" wrote:

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
> Print indicator is really a bit field but had to make it tinyint to put on
> index on it. Possible values are only 0, 1 but testing has shown the index
on
> PrintIndicator helps considerably.
> Thanks,
> Dean
>