Hi, all.
Recently one of my collegues asked me to create two indexes:
ix_1 : col1, col2, col3
ix_2 : col1, col2
I wonder this is a good practice to create the second index, ix_2. Because
ix_1 includes all the columns for ix_2.
And another question. Someone asked me to create indexes like following:
ix_1: col1, col2, col4
ix_2: col1, col2, col3
How about this case? Is it a good practice to create each index, or can I
make just one index like following:
ix_1:col1, col2, col3, col4
Any suggesion would be appreciated.See in line
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:uBjyLYxrDHA.3456@.tk2msftngp13.phx.gbl...
> Hi, all.
> Recently one of my collegues asked me to create two indexes:
> ix_1 : col1, col2, col3
> ix_2 : col1, col2
> I wonder this is a good practice to create the second index, ix_2. Because
> ix_1 includes all the columns for ix_2.
Depend on queries being run and the nature of the data.
If the col3 is small then there will be little difference. However, if col3
has an average size of say 1000bytes, then ix_2 will provide some
performance gains in queries that do not need col3. This is because there
will be less physical reads in ix_2
Eg lets say col1 and col2 are ints and col3 is a nvarchar(4000) with an
average of 1024 bytes per record. Also lets say there are 1 million rows in
this table.
So ix_1 will take up about 978Mb (125000) of space, and ix_2 will take up
about 7.5Mb (1000 pages).
Now lets look at the following queries
Select col1, col2 FROM ...
Select col2 FROM ... WHERE col1 = ...
If you do not have ix_2 then you will need to load more pages to get the
results. If you have ix_2 then this will be used as it uses less pages
> And another question. Someone asked me to create indexes like following:
> ix_1: col1, col2, col4
> ix_2: col1, col2, col3
> How about this case? Is it a good practice to create each index, or can I
> make just one index like following:
> ix_1:col1, col2, col3, col4
Similar to above.
> Any suggesion would be appreciated.
>
Showing posts with label practice. Show all posts
Showing posts with label practice. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Question about design practice - Address table
In a db, is it a good practice to have Address info in Address table in uppe
r
case or lower case?
ex: street name, city, state, county, country etc..
How about name [first name, middle name, last name, prefix, suffix...]
If you have any links that talk in detail about this, I'd like to read them
too
[ Some background: I am looking for some strong points so I could make an
impact in decision making...about best practice]
thanks everyone,
_UdayUsing all lower case for data is terrible. All upper case is bad -
takes me back to my mainframe days when we hardly knew there was a
shift key. I strongly prefer data read by people to be in proper
case: John Smith, Main St, Springfield.
State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
WA.
However, when addresses are printed for the US Postal Service, they
prefer all caps. That change can be made during the printing process,
if at all.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 12:11:02 -0700, Uday
<Uday@.discussions.microsoft.com> wrote:
>In a db, is it a good practice to have Address info in Address table in upp
er
>case or lower case?
>ex: street name, city, state, county, country etc..
>How about name [first name, middle name, last name, prefix, suffix...]
>If you have any links that talk in detail about this, I'd like to read them
>too
>[ Some background: I am looking for some strong points so I could make an
>impact in decision making...about best practice]
>thanks everyone,
>_Uday|||Default server collation is case in-sensitive.
So first step is, if you are worried about the case. check the collation of
the server,database. and upper case or lower case is a display property and
should be handled in the front end and usually based on requirements.
Hope this helps.|||I agree with you Ron. But the people who make decisions don't see data the
way I see it. So I need some strong points to make an impact on the decision
.
If I just say, 'keep the data in its original format' -- I dont think that'l
l
make a strong point. Hope you can understand the position I am in.
thanks to you all...Ron and Omnibuzz
_Uday
"Roy Harvey" wrote:
> Using all lower case for data is terrible. All upper case is bad -
> takes me back to my mainframe days when we hardly knew there was a
> shift key. I strongly prefer data read by people to be in proper
> case: John Smith, Main St, Springfield.
> State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
> WA.
> However, when addresses are printed for the US Postal Service, they
> prefer all caps. That change can be made during the printing process,
> if at all.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 2 May 2006 12:11:02 -0700, Uday
> <Uday@.discussions.microsoft.com> wrote:
>
>|||Uday.
Few points to remember.
1. If you define some rules saying the names should have the first letter
caps, how are you going to enforce it. Can you prevent a user from inserting
a value that doesn't comply to the standards. You can , by doing a check
constraint with a series of string functions (which SQL Server is not exactl
y
built for). But does it really have any sense to do the check.
Think of SQL Server as a datastore. If the format of storage changes at a
later date (say all caps for the names). How will you enforce the new
standards. Its always better to handle the formatting part in the front end.
That would be my standpoint.
Hope this helps.
--
"Uday" wrote:
> I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the decisi
on.
> If I just say, 'keep the data in its original format' -- I dont think that
'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||it would seem to me that you can only start "doing anything" with the
case in SQL server if you change the collation of the database. I
expect that if used well then this wouldn't be a problem, but I've had
numerous headaches from having to move databases between servers, then
finding the collation is different to the master (which means any SPs
with WHERE colA = 'some value' throw collation conflict errors).
I'd keep with the default collation, it makes everything simpler. As
others have stated, use your front end to do all the validation etc.
then you won't need to wrestle with DTS etc. just to change collation.
Incidentally, I haven't looked into it, but does anyone know if they've
made it easier with 2005 to change a database collation?
Cheers
Will|||You should always store data with as much information contained in it as
possible. You can always convert to all lower or upper or any combination
coming out.
It's like using your digital camera, and saving in a robust (granted, large
file size) on the memory card. You can always shrink it later when you want
to email the pic
Jeff
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:1162A9EF-6E79-431D-9FF4-FB6FC1AD39F3@.microsoft.com...
>I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the
> decision.
> If I just say, 'keep the data in its original format' -- I dont think
> that'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> 1. If you define some rules saying the names should have the first
> letter caps, how are you going to enforce it. Can you prevent a user
> from inserting a value that doesn't comply to the standards. You can ,
> by doing a check constraint with a series of string functions (which SQL
> Server is not exactly built for). But does it really have any sense to
> do the check.
Yeah, think about Ludwig van Beethoven for example.
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|||Thanks Omnibuzz..!! It sure helps.
I'll try the case-insensitive coallation... and let the data be in which
ever format it is entered. I'll change it on the fly in front-end or as
needed.
Thanks everyone,
_Uday
r
case or lower case?
ex: street name, city, state, county, country etc..
How about name [first name, middle name, last name, prefix, suffix...]
If you have any links that talk in detail about this, I'd like to read them
too
[ Some background: I am looking for some strong points so I could make an
impact in decision making...about best practice]
thanks everyone,
_UdayUsing all lower case for data is terrible. All upper case is bad -
takes me back to my mainframe days when we hardly knew there was a
shift key. I strongly prefer data read by people to be in proper
case: John Smith, Main St, Springfield.
State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
WA.
However, when addresses are printed for the US Postal Service, they
prefer all caps. That change can be made during the printing process,
if at all.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 12:11:02 -0700, Uday
<Uday@.discussions.microsoft.com> wrote:
>In a db, is it a good practice to have Address info in Address table in upp
er
>case or lower case?
>ex: street name, city, state, county, country etc..
>How about name [first name, middle name, last name, prefix, suffix...]
>If you have any links that talk in detail about this, I'd like to read them
>too
>[ Some background: I am looking for some strong points so I could make an
>impact in decision making...about best practice]
>thanks everyone,
>_Uday|||Default server collation is case in-sensitive.
So first step is, if you are worried about the case. check the collation of
the server,database. and upper case or lower case is a display property and
should be handled in the front end and usually based on requirements.
Hope this helps.|||I agree with you Ron. But the people who make decisions don't see data the
way I see it. So I need some strong points to make an impact on the decision
.
If I just say, 'keep the data in its original format' -- I dont think that'l
l
make a strong point. Hope you can understand the position I am in.
thanks to you all...Ron and Omnibuzz
_Uday
"Roy Harvey" wrote:
> Using all lower case for data is terrible. All upper case is bad -
> takes me back to my mainframe days when we hardly knew there was a
> shift key. I strongly prefer data read by people to be in proper
> case: John Smith, Main St, Springfield.
> State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
> WA.
> However, when addresses are printed for the US Postal Service, they
> prefer all caps. That change can be made during the printing process,
> if at all.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 2 May 2006 12:11:02 -0700, Uday
> <Uday@.discussions.microsoft.com> wrote:
>
>|||Uday.
Few points to remember.
1. If you define some rules saying the names should have the first letter
caps, how are you going to enforce it. Can you prevent a user from inserting
a value that doesn't comply to the standards. You can , by doing a check
constraint with a series of string functions (which SQL Server is not exactl
y
built for). But does it really have any sense to do the check.
Think of SQL Server as a datastore. If the format of storage changes at a
later date (say all caps for the names). How will you enforce the new
standards. Its always better to handle the formatting part in the front end.
That would be my standpoint.
Hope this helps.
--
"Uday" wrote:
> I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the decisi
on.
> If I just say, 'keep the data in its original format' -- I dont think that
'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||it would seem to me that you can only start "doing anything" with the
case in SQL server if you change the collation of the database. I
expect that if used well then this wouldn't be a problem, but I've had
numerous headaches from having to move databases between servers, then
finding the collation is different to the master (which means any SPs
with WHERE colA = 'some value' throw collation conflict errors).
I'd keep with the default collation, it makes everything simpler. As
others have stated, use your front end to do all the validation etc.
then you won't need to wrestle with DTS etc. just to change collation.
Incidentally, I haven't looked into it, but does anyone know if they've
made it easier with 2005 to change a database collation?
Cheers
Will|||You should always store data with as much information contained in it as
possible. You can always convert to all lower or upper or any combination
coming out.
It's like using your digital camera, and saving in a robust (granted, large
file size) on the memory card. You can always shrink it later when you want
to email the pic
Jeff
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:1162A9EF-6E79-431D-9FF4-FB6FC1AD39F3@.microsoft.com...
>I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the
> decision.
> If I just say, 'keep the data in its original format' -- I dont think
> that'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> 1. If you define some rules saying the names should have the first
> letter caps, how are you going to enforce it. Can you prevent a user
> from inserting a value that doesn't comply to the standards. You can ,
> by doing a check constraint with a series of string functions (which SQL
> Server is not exactly built for). But does it really have any sense to
> do the check.
Yeah, think about Ludwig van Beethoven for example.
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|||Thanks Omnibuzz..!! It sure helps.
I'll try the case-insensitive coallation... and let the data be in which
ever format it is entered. I'll change it on the fly in front-end or as
needed.
Thanks everyone,
_Uday
Friday, March 9, 2012
Question about BPA
Is it possible to use the Best Practice Analizer on a remote DB? I mean a DB
hosted in a hosting company?
Kenny M.
I believe as long as you can point it to the server you are all set. Have
you tried it?
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:D307CAF6-7126-497E-8421-CB5C13BB2E4A@.microsoft.com...
> Is it possible to use the Best Practice Analizer on a remote DB? I mean a
> DB
> hosted in a hosting company?
> --
> Kenny M.
|||umm well I did not ask very well, I already made login into the remote DB, my
question is how to specify my instance?
BPA has an input field: SQLServerinstance: 98.00.88.00
the login is OK but there are many DB on the server(it is a hosting server)
and I want to use only my instance.
I don't know how to specify it.
Kenny M.
"Andrew J. Kelly" wrote:
> I believe as long as you can point it to the server you are all set. Have
> you tried it?
> --
> Andrew J. Kelly SQL MVP
>
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:D307CAF6-7126-497E-8421-CB5C13BB2E4A@.microsoft.com...
>
>
|||OK I see now. Sorry about the confusion. Unfortunately I do not know that
answer as I no longer have that tool installed. I thought there was a way
to specify a particular db but I can't confirm that one way or another.
Hopefully someone else will reply that does have the tool installed.
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:82637589-85C7-4B5B-AFDC-57620DBF1F89@.microsoft.com...[vbcol=seagreen]
> umm well I did not ask very well, I already made login into the remote DB,
> my
> question is how to specify my instance?
> BPA has an input field: SQLServerinstance: 98.00.88.00
> the login is OK but there are many DB on the server(it is a hosting
> server)
> and I want to use only my instance.
> I don't know how to specify it.
>
> --
> Kenny M.
>
> "Andrew J. Kelly" wrote:
|||Kenny M. (KennyM@.discussions.microsoft.com) writes:
> umm well I did not ask very well, I already made login into the remote
> DB, my question is how to specify my instance?
> BPA has an input field: SQLServerinstance: 98.00.88.00 the login is OK
> but there are many DB on the server(it is a hosting server) and I want
> to use only my instance.
OK. Normally "instance" in the context of SQL Server means a whole instance
of SQL Server with master, tempdb, model, msdb and a whole lot of user
databases. One machine can have several instance of SQL Server.
> I don't know how to specify it.
When you add a Server, there is an entry field "Database list" where you
can enter the database that are of interest to you. By default the value
is * for all databases.
Whether you actually will be able to run BPA, I don't know. Some tests are
on server-level and requires sysadmin access (I would suppose). Then again,
I would expect to be able to access a database for which I am the DB ovner.)
I should add that I believe that I have a pre-release version of BPA, so
the final interface may look different.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||mell at this point I'm almost sure that it is not possible, I'm creating the
BPA repository on my local local machine but I can not analyze a my remote db
located in a hosting
Kenny M.
"Erland Sommarskog" wrote:
> Kenny M. (KennyM@.discussions.microsoft.com) writes:
> OK. Normally "instance" in the context of SQL Server means a whole instance
> of SQL Server with master, tempdb, model, msdb and a whole lot of user
> databases. One machine can have several instance of SQL Server.
>
> When you add a Server, there is an entry field "Database list" where you
> can enter the database that are of interest to you. By default the value
> is * for all databases.
> Whether you actually will be able to run BPA, I don't know. Some tests are
> on server-level and requires sysadmin access (I would suppose). Then again,
> I would expect to be able to access a database for which I am the DB ovner.)
> I should add that I believe that I have a pre-release version of BPA, so
> the final interface may look different.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
hosted in a hosting company?
Kenny M.
I believe as long as you can point it to the server you are all set. Have
you tried it?
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:D307CAF6-7126-497E-8421-CB5C13BB2E4A@.microsoft.com...
> Is it possible to use the Best Practice Analizer on a remote DB? I mean a
> DB
> hosted in a hosting company?
> --
> Kenny M.
|||umm well I did not ask very well, I already made login into the remote DB, my
question is how to specify my instance?
BPA has an input field: SQLServerinstance: 98.00.88.00
the login is OK but there are many DB on the server(it is a hosting server)
and I want to use only my instance.
I don't know how to specify it.
Kenny M.
"Andrew J. Kelly" wrote:
> I believe as long as you can point it to the server you are all set. Have
> you tried it?
> --
> Andrew J. Kelly SQL MVP
>
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:D307CAF6-7126-497E-8421-CB5C13BB2E4A@.microsoft.com...
>
>
|||OK I see now. Sorry about the confusion. Unfortunately I do not know that
answer as I no longer have that tool installed. I thought there was a way
to specify a particular db but I can't confirm that one way or another.
Hopefully someone else will reply that does have the tool installed.
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:82637589-85C7-4B5B-AFDC-57620DBF1F89@.microsoft.com...[vbcol=seagreen]
> umm well I did not ask very well, I already made login into the remote DB,
> my
> question is how to specify my instance?
> BPA has an input field: SQLServerinstance: 98.00.88.00
> the login is OK but there are many DB on the server(it is a hosting
> server)
> and I want to use only my instance.
> I don't know how to specify it.
>
> --
> Kenny M.
>
> "Andrew J. Kelly" wrote:
|||Kenny M. (KennyM@.discussions.microsoft.com) writes:
> umm well I did not ask very well, I already made login into the remote
> DB, my question is how to specify my instance?
> BPA has an input field: SQLServerinstance: 98.00.88.00 the login is OK
> but there are many DB on the server(it is a hosting server) and I want
> to use only my instance.
OK. Normally "instance" in the context of SQL Server means a whole instance
of SQL Server with master, tempdb, model, msdb and a whole lot of user
databases. One machine can have several instance of SQL Server.
> I don't know how to specify it.
When you add a Server, there is an entry field "Database list" where you
can enter the database that are of interest to you. By default the value
is * for all databases.
Whether you actually will be able to run BPA, I don't know. Some tests are
on server-level and requires sysadmin access (I would suppose). Then again,
I would expect to be able to access a database for which I am the DB ovner.)
I should add that I believe that I have a pre-release version of BPA, so
the final interface may look different.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||mell at this point I'm almost sure that it is not possible, I'm creating the
BPA repository on my local local machine but I can not analyze a my remote db
located in a hosting
Kenny M.
"Erland Sommarskog" wrote:
> Kenny M. (KennyM@.discussions.microsoft.com) writes:
> OK. Normally "instance" in the context of SQL Server means a whole instance
> of SQL Server with master, tempdb, model, msdb and a whole lot of user
> databases. One machine can have several instance of SQL Server.
>
> When you add a Server, there is an entry field "Database list" where you
> can enter the database that are of interest to you. By default the value
> is * for all databases.
> Whether you actually will be able to run BPA, I don't know. Some tests are
> on server-level and requires sysadmin access (I would suppose). Then again,
> I would expect to be able to access a database for which I am the DB ovner.)
> I should add that I believe that I have a pre-release version of BPA, so
> the final interface may look different.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
Subscribe to:
Posts (Atom)