Is using varchar() as index a bad idea?

Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:

> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks

Is this SQL related problem?

Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please

Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)

Timeout means that the server does not replies or completes the job within 30 sec (default)
Are you sure the the database is online ?
Can you execute a simple query such as select @.@.spid without having error ?
Can you post the query ?

is this a bad idea? (replicating from pub to dist, back to pub)

We need to create a copy of a db with updates on the same publisher.
Problem is that the publisher is already using a separate distributor
server. Since you can only have 1 distributor per publisher we are in a
bind here. We can't change the distributor because it's handling
replication for other publishers on this box. So we were thinking that
we just replicated the db to the publisher via the separate distributor.
Make sense? Is this a bad idea since technically it's leaving the
machine traveling across the network to the distributor then back down
to the exact same machine it came from.
What other solution might we use? They do not want to dts it for some
reason. They want it to replicate. Not sure why
tia
-comb
Comb,
this is a possible topology and I agree with what you're saying re
transactional replication, but merge replication and snapshot will not send
data via the distributor. As alternatives, you could also investigate
database mirroring to a separate instance (+ database snapshot if they want
to run reports) on the same box or a custom log shipping solution. It
depends on the business requirements really.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OKh7ap1QGHA.3052@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> this is a possible topology and I agree with what you're saying re
> transactional replication, but merge replication and snapshot will not send
> data via the distributor. As alternatives, you could also investigate
> database mirroring to a separate instance (+ database snapshot if they want
> to run reports) on the same box or a custom log shipping solution. It
> depends on the business requirements really.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul
Thanks for your response.
I am going to look into database mirroring and database snapshot right
now. I am not a sql dude (i am a network guy that gets stuck with
messing with replication). For some reason, i was thinking that those
two terms were only part of sql2005. I will do my research though and
make sure.
I guess one other issue is that this db is 300+gb and they only want a
few tables out of it.
thanks again paul.
-comb
|||Comb,
for a custom log shipping solution you'd be ok with sql server 2000. Apart
from that you're right that you'd need sql 2005 for a database mirroring
solution. The point about mirroring is that it is available but not
currently supported, so that might be a factor. if it's a case of just
needing a duplicate of the complete database, I'd script up a solution for
log shipping, or use some of the free scripts out there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#PB2d75QGHA.4696@.tk2msftngp13.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> for a custom log shipping solution you'd be ok with sql server 2000. Apart
> from that you're right that you'd need sql 2005 for a database mirroring
> solution. The point about mirroring is that it is available but not
> currently supported, so that might be a factor. if it's a case of just
> needing a duplicate of the complete database, I'd script up a solution for
> log shipping, or use some of the free scripts out there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul.
Thanks for your response. You wouldn't happen to have any of those free
scripts on your replicationanswers site would you?
tia
-comb
|||This should help:
http://www.sql-server-performance.co...g_shipping.asp
The other place I've seen them is on the resource kit - which you'll have if
you've got MSDN at work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#FkCWncRGHA.4952@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> This should help:
> http://www.sql-server-performance.co...g_shipping.asp
> The other place I've seen them is on the resource kit - which you'll have if
> you've got MSDN at work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
thanks again paul.

Is there any good idea of creating KPI report on SSRS2005 for Analysis Services?

Hi, experts,

Thanks for your kind attention.

Do you have any good idea of creating reports for KPI from Analysis Services 2005 OLAP cubes? And is it worth of creating KPI reports from SSRS?

I am looking forward to hearing from you and thanks a lot.

With kind regards,

Hello Helen! Can this link be of help?

http://www.databasejournal.com/features/mssql/article.php/3627351

HTH

Thomas Ivarsson

|||

Hi,

It′s a good idea to use the SSRS or Excel 2007 to delivery your reports.

Regards|||

Hi, Thomas,

Thanks for your suggestion of the article. But as we have to struggle to import the KPI indicators for the KPI status and trends, I cant think of any way to change the KPI indicators dynamically according to the change of the status and trends?

Thanks again and I am looking forward to hearing from you for your further advices.

With kind regards,

Yours sincerely,

|||

Hi, Lucas,

Thanks for that.

Yes, I do use Excel and see the fantastic capability of viewing KPI within Excel 2007.

But with SSRS we rather can easily view the KPI values instead of indicators of KPI if we dont struggle to import any KPI indicators?

Thanks again.

With kind regards,

Yours sincerely,

|||

OK! You are talking about the Business Scorecard Server?

Regards

Thomas Ivarsson

|||

No, I am talking about SSRS still. As there are no KPI indicators residing in SSRS2005 you will have to always import static KPI indicators which wont change according to the change of the KPI values?

Hope it is clear for your help.

With kind regards,

Yours sincerely,

|||

OK! I have sent a link to an article that will help you with that. SSAS2005 status and trend graphics(KPI) are not supported in Reporting Services 2005.

You will have to build them from scratch in SSRS2005.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your help.

With kindest regards,

Yours sincerely,

Is there any good idea of creating KPI report on SSRS2005 for Analysis Services?

Hi, experts,

Thanks for your kind attention.

Do you have any good idea of creating reports for KPI from Analysis Services 2005 OLAP cubes? And is it worth of creating KPI reports from SSRS?

I am looking forward to hearing from you and thanks a lot.

With kind regards,

Hello Helen! Can this link be of help?

http://www.databasejournal.com/features/mssql/article.php/3627351

HTH

Thomas Ivarsson

|||

Hi,

It′s a good idea to use the SSRS or Excel 2007 to delivery your reports.

Regards|||

Hi, Thomas,

Thanks for your suggestion of the article. But as we have to struggle to import the KPI indicators for the KPI status and trends, I cant think of any way to change the KPI indicators dynamically according to the change of the status and trends?

Thanks again and I am looking forward to hearing from you for your further advices.

With kind regards,

Yours sincerely,

|||

Hi, Lucas,

Thanks for that.

Yes, I do use Excel and see the fantastic capability of viewing KPI within Excel 2007.

But with SSRS we rather can easily view the KPI values instead of indicators of KPI if we dont struggle to import any KPI indicators?

Thanks again.

With kind regards,

Yours sincerely,

|||

OK! You are talking about the Business Scorecard Server?

Regards

Thomas Ivarsson

|||

No, I am talking about SSRS still. As there are no KPI indicators residing in SSRS2005 you will have to always import static KPI indicators which wont change according to the change of the KPI values?

Hope it is clear for your help.

With kind regards,

Yours sincerely,

|||

OK! I have sent a link to an article that will help you with that. SSAS2005 status and trend graphics(KPI) are not supported in Reporting Services 2005.

You will have to build them from scratch in SSRS2005.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your help.

With kindest regards,

Yours sincerely,