collation issue

Hi,
I am trying to change the collation of a database
from TblA of ServerA , i look at the field's collation which says "database
default" and i looked into it which says "SQL Collation"
SQL_Latin1_General_CP1_CI_AS
so i issued this statement
ALTER DATABASE dbA COLLATION LATIN_General_BIN --> this shld be a windows
collation ?
after that statment , shldn't it be changed to Windows Collation -->
LATIN_General_BIN ?
kindly advise
tks & rdgs
after i have issued the statement i looked in TblA but the collation is now
also "SQL_Latin1_General_CP1_CI_AS"
No. That changes the database collation and any new objects (provided you
don't explicitely declare a different collation when you create them) will
have that default. You have to issue ALTER statements for every character
column though and change each of them. Don't forget about the indexes, which
you also have to change.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"maxzsim" wrote:

> Hi,
> I am trying to change the collation of a database
> from TblA of ServerA , i look at the field's collation which says "database
> default" and i looked into it which says "SQL Collation"
> SQL_Latin1_General_CP1_CI_AS
> so i issued this statement
> ALTER DATABASE dbA COLLATION LATIN_General_BIN --> this shld be a windows
> collation ?
> after that statment , shldn't it be changed to Windows Collation -->
> LATIN_General_BIN ?
> kindly advise
> tks & rdgs
> after i have issued the statement i looked in TblA but the collation is now
> also "SQL_Latin1_General_CP1_CI_AS"
|||Hi ,
i c but how do i actually change the collation for the indexes now ?
apprecite ur advice
tks & rdgs
"MeanOldDBA" wrote:
[vbcol=seagreen]
> No. That changes the database collation and any new objects (provided you
> don't explicitely declare a different collation when you create them) will
> have that default. You have to issue ALTER statements for every character
> column though and change each of them. Don't forget about the indexes, which
> you also have to change.
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "maxzsim" wrote:
|||> i c but how do i actually change the collation for the indexes now ?
You don't. Drop the index. Change collation for the columns. Create the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BA2027F4-F137-4014-918F-E9045C55B63F@.microsoft.com...[vbcol=seagreen]
> Hi ,
> i c but how do i actually change the collation for the indexes now ?
> apprecite ur advice
> tks & rdgs
> "MeanOldDBA" wrote:
|||Hi,
tks for ur reply
rdgs,
"Tibor Karaszi" wrote:

> You don't. Drop the index. Change collation for the columns. Create the index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BA2027F4-F137-4014-918F-E9045C55B63F@.microsoft.com...
>

Collation and views

Suppose you have your databases's collation as X but the value of collation
on the varchar fields of some of your tables as Y.
Suppose you create a view like this:
SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
Table_A
UNIONA ALL
SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
Table_B
You've got an error of incompatble collation on the first two columns of the
view. I think because on the constant string values the db assign the
collation X while the corresponding varchar fields of Table_B have
collation Y.
Is there any solution to this problem?
Thank you all
Andreayes, there is: use COLLATE clause in the select statement.
dean
"Andrea Temporin" <NOSPAM_temporin@.encopro.it> wrote in message
news:%232K2hKyGFHA.3108@.tk2msftngp13.phx.gbl...
> Suppose you have your databases's collation as X but the value of
collation
> on the varchar fields of some of your tables as Y.
> Suppose you create a view like this:
> SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
> Table_A
> UNIONA ALL
> SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
> Table_B
> You've got an error of incompatble collation on the first two columns of
the
> view. I think because on the constant string values the db assign the
> collation X while the corresponding varchar fields of Table_B have
> collation Y.
> Is there any solution to this problem?
> Thank you all
> Andrea
>

collation

Hi!
I am having a problem concerning collations.
I have an SQL Server 2000 with Latin1_General_CI_AS
which contains the company data.Some of the fields are Greek and although I
cannot view them correctly in the Query Analyzer , the tool we are using
(powerbuilder) displays them OK.
now that w e want to go Internet we are facing problems with the Greek
fields not displayes correctly...
What can we do in order to have them displayed correctly?
Thanx in advance!
Hi
Do you define the column which displays the database as NAVARCHAR(n)?
"P Platan" <pplat@.exnds.com> wrote in message
news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I am having a problem concerning collations.
> I have an SQL Server 2000 with Latin1_General_CI_AS
> which contains the company data.Some of the fields are Greek and although
> I cannot view them correctly in the Query Analyzer , the tool we are using
> (powerbuilder) displays them OK.
> now that w e want to go Internet we are facing problems with the Greek
> fields not displayes correctly...
> What can we do in order to have them displayed correctly?
> Thanx in advance!
>
|||unfortunately no!
it is an old database.migrated from ASA...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi
> Do you define the column which displays the database as NAVARCHAR(n)?
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
|||Well. please read article aboit UNICODE in the BOL
If so, you are going to alter the table and change the datatype
"P Platan" <pplat@.exnds.com> wrote in message
news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
> unfortunately no!
> it is an old database.migrated from ASA...
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
|||thanx!!
...what is BOL?
and if I alter the table will I be able to have the already stored Greek
fields displayed correctly?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Well. please read article aboit UNICODE in the BOL
> If so, you are going to alter the table and change the datatype
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
>
|||> ..what is BOL?
Books On Line

> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
Yes, read the article in the BOL
"P Platan" <pplat@.exnds.com> wrote in message
news:OWcXWKGPGHA.2300@.TK2MSFTNGP15.phx.gbl...
> thanx!!
> ..what is BOL?
> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
>

collation

Hi!
I am having a problem concerning collations.
I have an SQL Server 2000 with Latin1_General_CI_AS
which contains the company data.Some of the fields are Greek and although I
cannot view them correctly in the Query Analyzer , the tool we are using
(powerbuilder) displays them OK.
now that w e want to go Internet we are facing problems with the Greek
fields not displayes correctly...
What can we do in order to have them displayed correctly?
Thanx in advance!Hi
Do you define the column which displays the database as NAVARCHAR(n)?
"P Platan" <pplat@.exnds.com> wrote in message
news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I am having a problem concerning collations.
> I have an SQL Server 2000 with Latin1_General_CI_AS
> which contains the company data.Some of the fields are Greek and although
> I cannot view them correctly in the Query Analyzer , the tool we are using
> (powerbuilder) displays them OK.
> now that w e want to go Internet we are facing problems with the Greek
> fields not displayes correctly...
> What can we do in order to have them displayed correctly?
> Thanx in advance!
>|||unfortunately no!
it is an old database.migrated from ASA...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi
> Do you define the column which displays the database as NAVARCHAR(n)?
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Well. please read article aboit UNICODE in the BOL
If so, you are going to alter the table and change the datatype
"P Platan" <pplat@.exnds.com> wrote in message
news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
> unfortunately no!
> it is an old database.migrated from ASA...
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||thanx!!
..what is BOL?
and if I alter the table will I be able to have the already stored Greek
fields displayed correctly?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Well. please read article aboit UNICODE in the BOL
> If so, you are going to alter the table and change the datatype
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
>|||> ..what is BOL?
Books On Line

> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
Yes, read the article in the BOL
"P Platan" <pplat@.exnds.com> wrote in message
news:OWcXWKGPGHA.2300@.TK2MSFTNGP15.phx.gbl...
> thanx!!
> ..what is BOL?
> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
>

Code suggestions for database searches

I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.

Thank you

>> I need to create an admin section that lets me search on one field or the combination of several.

Do you means within a given table or across all tables?

>> database search strategy and admin edits

If across all tables, what about validation? A generic edit solution would bypasss data validation checks - not a good idea.

|||I need it across several tables and I am experimenting with the Multi_View control because although it only displays one view at a time all controls are accessible because the Views do not function as seperate containers. So far it seems to be meeting the major requirements however the displays are a little hard to figure out.|||

Have you considered how to handle data validation?

|||

I am doing that using Validation controls on the database submission form and in the View Edit template. At least I expect Validation will work in the Views.

code a hyperlink

Hi

I have developed a report using microsoft reporting services with certain fields

In my report the user enters name (which is a parameter) and the report is displayed

Inside my report. I have a field studentID which should be a link which when clicked should take me to a new report which is a report in extranet.

Currently I dont have access to that rdl and for certain reasons, I am asked to link to that report by

coding a hyperlink in development

I know that in the text box under action properties I need to give the url , but its not working

Should i specify the student id anywhere .

What and where should I code?

Thanks

If I were going about this, I would use the "Jump to URL" radio button.

However, the problem that you are going to have is passing and retrieving the parameter (as you already see).

Usually, the way you pass a parameter in a URL is as follows:

http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=2009786&SiteID=1

The parameters in the above URL are PostID with value 2009786 and SiteID with value 1.

Now, how you go about retrieving those values in the report from the URL is beyond my experience.

Possibly someone else knows how.

COALESCE with parameters

I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):

select * from dbo.employee

where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)

The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.

I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?

Or is there a better way to use the COALESCE function?

Thanks all!

I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.

-Chris

|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||

Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.

Once this has been done you can use:

WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))

OR (@.checkdateisnull = 1 AND checkdate IS NULL))

As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.

Chris

|||

There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.

http://www.sommarskog.se/dyn-search.html

|||

Thanks!!!

I'll be studying that document for quite some time!

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.
edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.
|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.
|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>
|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.
|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>
|||
>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.
|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||hi,
i guess i made a basic mistake while trying to "calculate"
the fill-factor percentage needed:
i guess the server probably does not gain sagnificant
improvment by saveing only 20% space of the possible row
size. it probably save place for at least 1 WHOLE row, so
it will be able to place it as a whole without page-split.
actually, i believe 20 percent means that there is enough
space for much more than 1 row.
so, as to my questions:
1. may be i can even set fill-factor to 99% and still be
sure that there is no chance for a split-page at all?
2. same question with no principle changes.
thanks again.
edo.|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>|||>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx
.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx
.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx
.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>|||
>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

CLUSTERED INDEX or NONCLUSTERED

I have 3 table A, B, C

Table A (15 field, 4 fields indexed and Primary Key) approximate rows: 50.000 60.000

Table B (18 field, 6 fields indexed and Primary Key) approximate rows: 350.000 500.000

Table C (16 filed, 9 fields indexed and Primary Key) approximate rows: 500.000 1.000.000

Structure is something like this:
A (master) --> B (detail) --> C (sub detail)

On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX

Thanks
Sorry for my englishIt is not clear about relations between tables (number of fields, etc.) by anyway clustered index for PK and nonclustered for others will be OK.|||Not enough info, these links may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp|||Thank you for your answer.
The diagram is attached, form left to right table A; B; C|||The diagram|||Still not enough info. Some questions:

What is your ratio of inserts to queries? Are you heavy insert or heavy queries or both?

What is typically used for your select criterias?

I would reccomend you start with reading those articles and you may play around with "set statistics IO on" to evaluate your logical IO when you have added a clustered index, taken it off, added a nonclustered index, etc. This to me is the best advice to become self sufficient on indexing questions.

HTH

Clustered Index on Date Field or Identity Field ....

Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
PradPradeep Kutty wrote:
> Hi All,
> I have a table (detail table) with fields ID (Identity) primary Key and
> a DTTM datetime field which is a heap.
> Right now there is a non clustered index on ID which is used to join
> with its master table.
> I have many reports which uses this table and for all the reports the
> basic criteria is between DTTM.
> say I run the report for say for a date range of 1 month, 1 w or so.
> Im planning to add a clustered index on DTTM field so that the reports
> would become faster compared to a table scan what its doing now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create a
> non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a primary
> key,
> since an identity field is already ordered. Does it make sense to create
> a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve uses
> ID to join with its master table.
> DTTM would be used only in reports...
> Thanks,
> Prad
>
it seems that a clustered index is better. try both ways and look at the
execution plan(s)|||Pradeep,

>Im planning to add a clustered index on DTTM field so that the reports would become
faster compared to a table scan what its doing now.
Thats a good idea because clustered index is ideal for range search.

>But I always had the doubt that, what is the purpose of creating a clustere
d index on an identity field that too which is already a primary key,
>since an identity field is already ordered. Does it make sense to create a clustere
d in index on Identity field.
One advantage of having a clustered index on the IDENTITY column is that it
will help you avoid page split problems.
But your assumption about the order of IDENTITY value is wrong. IDENTITY onl
oy provides a logical sequence, whereas a clustered index
controls the order in which the rows are physically stored.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||One note of caution (playing devil's advocate here).
I don't know how many people you have updating your table or the hardware yo
u use but...
...one problem with clustered indexes based on the ID is that all WRITES mu
st occur on the same place on the disk, or on the same disk if you're using
an array of disks...everyone's writing data to a new row that goes in after
the last row.
If you have a huge number of updates occurring (which you probably don't) th
en this can cause a problem as you effectively get a "hot spot" on the disk
where everyone is attempting to write to the same part of the disk. Compare
this to a clustered index on (say) the surname, where new rows are added to
different parts of the disk (or on different disks in an array of disks).
Griff
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||If it is used in joins, then I would put the clustered index on the IDENTITY
column. This can speed up inserts into this table, inserts into related ta
bles, and joins between this table and related tables. If the order of the
IDENTITY increment matches the order of the clustered index on the IDENTITY
column, then all inserts will occur at the end of the table, which minimizes
the required index maintenance operations.
If a table has a clustered index, then all nonclustered indexes use the clus
tered index key to locate rows in the table. If you put a nonclustered inde
x on the primary key, then every join will result in an additional step in t
he execution plan--a bookmark lookup. This extra level of indirection can s
ignificantly reduce the performance of every join. In addition, if you use
a clustered index on a datetime column, and the datetime column is not a can
didate key, then SQL Server will add a 4-byte uniqifier to every index row s
o that the index key can be used in nonclustered indexes to locate rows. Th
is increases the size of each nonclustered index, and can further reduce que
ry performance, especially with respect to joins.
To boost performance for reporting, you have other options aside from simply
adding an index. Here are a couple: (1) use a covering index so that the b
ookmark lookup will not be necessary, or (2) create an indexed view, and use
both the datetime and the identity column (in that order) as the clustered
index key for the view. If all of the columns necessary for the query exist
in the index key, then there is no need for SQL Server to access the actual
data row, so the performance degradation resulting from the use a noncluste
red index will be minimized. If that doesn't provide adequate reporting per
formance, the indexed view option will at least meet the select performance
of accessing a table with a clustered index directly, without degrading the
performance of the joins. It should be noted, however, that insert performa
nce will be degraded by the addition of any index or indexed view.
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:#EVpg8TrFHA
.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||Pradeep Kutty wrote:
> Hi All,
> I have a table (detail table) with fields ID (Identity) primary Key
> and a DTTM datetime field which is a heap.
> Right now there is a non clustered index on ID which is used to join
> with its master table.
> I have many reports which uses this table and for all the reports the
> basic criteria is between DTTM.
> say I run the report for say for a date range of 1 month, 1 w or
> so.
> Im planning to add a clustered index on DTTM field so that the
> reports would become faster compared to a table scan what its doing
> now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create
> a non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a
> primary key,
> since an identity field is already ordered. Does it make sense to
> create a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve
> uses ID to join with its master table.
> DTTM would be used only in reports...
Either I overlooked it or nobody actually mentioned a composite index. If
you always do queries that join by your PK and use only a date range then
a composite clustered index on (timestamp, ID) might also be worth
considering. Or am I missing something here?
Kind regards
robert