index and key: rows

Showing posts with label rows. Show all posts

Friday, March 30, 2012

index tepmorary table

Hi All,
I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
I've tried adding a few indexes to the temporary table, but with little
improvement, however it has prompted the following questions:
is there ever a good situation to index a local temporary table, or
does the overhead of building and maintaining the index always outweigh
the benefits?
will there be concurrency issues for the index? you can have several
temporary tables of the same name, if I create an index against it,
will another temporary table be able to have an index of that name
created against it as well (or is there something like a temporary
index)?
Like I said, in this case indexes don't appear to help anyway, but it
would be nice to know if it's ever an option.
Cheers
WillWill
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
Well defined indexes will improve performance

> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
Can you be more specific? Are you going to JOIN the tables ?

> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
What are you trying to achive, can you explain ? DDL+ sample data +
expectedresult will be helpful.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
> Hi All,
> I'm looking at a query at the moment which builds up a large-ish
> (50,000 rows) temporary table, then goes about performing various
> updates and selects. This is taking ages as the table is too large, so
> full table scans are being performed on ab out 8 or 9 separate selects.
> I've tried adding a few indexes to the temporary table, but with little
> improvement, however it has prompted the following questions:
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
> Cheers
> Will
>|||If you create a local temp table (i.e. name begins with a single #), then
there are no concurrency issues. As for performance, they often do help.
I'd populate the table first and then add the indexes. there are no hard
and fast rules, other than to try it and see if it helps.
If you post your DDL and the code that runs slowly, perhaps we can
troubleshoot further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
Hi All,
I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
I've tried adding a few indexes to the temporary table, but with little
improvement, however it has prompted the following questions:
is there ever a good situation to index a local temporary table, or
does the overhead of building and maintaining the index always outweigh
the benefits?
will there be concurrency issues for the index? you can have several
temporary tables of the same name, if I create an index against it,
will another temporary table be able to have an index of that name
created against it as well (or is there something like a temporary
index)?
Like I said, in this case indexes don't appear to help anyway, but it
would be nice to know if it's ever an option.
Cheers
Will|||Uri, Tom,
Thanks for your replies. I can't post the exact code as I don't own it,
but the basic layout is as follows:
Create table #result(col1 nvarchar(100), col2 nvarchar(100), ... ,...,
coln decimal(18,2))
INSERT INTO #result
select stuff
from applicationtables
Update #Result
SET col5 = col4*col6
FROM #Result as r
JOIN applicationTable as a on a.Col9 = r.Col9
WHERE Col1 = 'a fixed filter'
The code then does a few more updates with different formulas and joins
on different columns, but the same basic statement
I thought that it would be made more efficient by indexing #Result such
that the updates could more quickly complete the table scans (e.g.
create an index on #results.col9), however the improvement was
negligeable.
In this case it was just a bit of opportunistic optimisation, I was
really posting the question more to know if this was a route to attempt
if I encounter any other queries with large temp tables - which it
appears it can be.
Thanks for the help
Will|||Be sure that when you do the joins that you have identical datatypes. For
example, if you join an nvarchar to a varchar, it will usually skip the
index.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144676705.952576.294480@.t31g2000cwb.googlegroups.com...
Uri, Tom,
Thanks for your replies. I can't post the exact code as I don't own it,
but the basic layout is as follows:
Create table #result(col1 nvarchar(100), col2 nvarchar(100), ... ,...,
coln decimal(18,2))
INSERT INTO #result
select stuff
from applicationtables
Update #Result
SET col5 = col4*col6
FROM #Result as r
JOIN applicationTable as a on a.Col9 = r.Col9
WHERE Col1 = 'a fixed filter'
The code then does a few more updates with different formulas and joins
on different columns, but the same basic statement
I thought that it would be made more efficient by indexing #Result such
that the updates could more quickly complete the table scans (e.g.
create an index on #results.col9), however the improvement was
negligeable.
In this case it was just a bit of opportunistic optimisation, I was
really posting the question more to know if this was a route to attempt
if I encounter any other queries with large temp tables - which it
appears it can be.
Thanks for the help
Will|||This may sound a little backward, but have you considered using a real table
to do the job?
I'll explain a little more. Add another column to your temp table called
say, ExecutionGUID, as a GUID column. Then inside your SP, create a GUID
variable, and populate it with NewID(), Now for all queries, including the
initial insert use your GUID to identify your new subset.
If you pass the Temp table to another stored procedure, this will cause the
procedure to recompile, which is extremely expensive. Using a real table
the procedure does not have to recompile every execution.
You can also combine this code with a cleanup job, which runs automatically,
you'll need to know what you can delete but it'll mean your client isn't
hanging about whilst the delete takes place.
This may not be suitable for your situation, but it might be worth a try.
Colin.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
> Hi All,
> I'm looking at a query at the moment which builds up a large-ish
> (50,000 rows) temporary table, then goes about performing various
> updates and selects. This is taking ages as the table is too large, so
> full table scans are being performed on ab out 8 or 9 separate selects.
> I've tried adding a few indexes to the temporary table, but with little
> improvement, however it has prompted the following questions:
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
> Cheers
> Will
>|||Hi Colin,
Thanks for the reply,
In this case it's not possible however...
You see, first off the procedure is building up for a crystal report
(it's where I see the most horrendous use of temporary tables).
Therefore this has to be able to have 2 people running the report at
the same time (though I dread to think of the load on the server).
If I start using Guids and globalising this behaviour I'll start
getting issues with 2 competing processes locking this table, when in
fact the data is entirely separate, and can be kept that way. Also, the
data is temporary, and I don't see the benefit in actually creating the
table, just to drop it afterwards.
I don't quite get where you're coming from with the "pass to another
stored procedure issue" - this was never presented as an issue.
Everything is done in just the one procedure, and works fine. The issue
is that it's got no indexes, and therefore takes a couple of minutes to
process all the data it's just created in the temporary table.
Finally, and most critically, it was (as is usual for the people
posting and trying to fix the issue) not me that wrote this.
it's written, live, and I can't justify such a change without a lot of
procedure, and strong arguments of the benefit compared to the cost and
risk.
I agree however that there are times where a good use of a table would
have exceeded the use of a temporary one, this is just unfortunately
not one of them
Cheers
Will|||Hi Will,
I've inserted my comments in the text below...
<snip>
> In this case it's not possible however...
> You see, first off the procedure is building up for a crystal report
> (it's where I see the most horrendous use of temporary tables).
> Therefore this has to be able to have 2 people running the report at
> the same time (though I dread to think of the load on the server).
Ooo yuck, I hate Crystal Reports. I used to love it, but over the last 5
years, my opinion has changed, it tends to be extremely expensive on DB
Resources. You have my sympathy :-p

> If I start using Guids and globalising this behaviour I'll start
> getting issues with 2 competing processes locking this table, when in
> fact the data is entirely separate, and can be kept that way. Also, the
> data is temporary, and I don't see the benefit in actually creating the
> table, just to drop it afterwards.
There's a couple of interesting points here...
1. You can avoid the competing process issue by using the NoLock table hint.
Normally, GUID's generated are completely different, so in theory you should
be working on different parts of the table when inserting/updating, however
you can use the RowLock table hint to ensure that Page/Table locks do not
occur. This should allow 2 or more people to work on the table at the same
time.
2. The thing about a temporary table is that it's not really a temporary
table. It's a real one. It's just that SQL has added a cleanup routine to
so that the table is destroyed when the connection closes (or scope changes
to a parent level), it all intents and purposes it has all the same problems
as a real table, but you also need to allocate resources to create the
temporary table every time that the code is executed.

> I don't quite get where you're coming from with the "pass to another
> stored procedure issue" - this was never presented as an issue.
> Everything is done in just the one procedure, and works fine. The issue
> is that it's got no indexes, and therefore takes a couple of minutes to
> process all the data it's just created in the temporary table.
>
Your using just one procedure, so that's not an issue. If your stored proc
used either exec or sp_executesql the called code will need to be recompiled
every time, this happens because the temp table is in fact a different table
every time that it is called. To test this create a temporary table using
SSMS or QA then execute Select * from tempdb.sysobjects where xtype='U' your
temporary table will have a suffix which changes every session!
The problem is that as SQL needs to re-compile everytime, this can take alot
of processor resources.

> Finally, and most critically, it was (as is usual for the people
> posting and trying to fix the issue) not me that wrote this.
> it's written, live, and I can't justify such a change without a lot of
> procedure, and strong arguments of the benefit compared to the cost and
> risk.
I completely understand that problem.
Regards
Colin Dawson
www.cjdawson.com|||>> I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
<<
Why can you not avoid proprietary temp table code and procedural
processing with a single query? What you have describes (vaguely) is
the way we wrote code with magnetic tape and punch cards -- multiple
passes over the data.
Good SQL programmers do things in one statement, not by mimicking
magnetic tape file from the 1950's.|||Celko,
I can't avoid the temporary table code as I'm not allowed to re-write
the stored procedure. However I would argue that temporary tables have
their place just as anything else in SQL (even cursors). The reason
they are of benefit here is that the stored procedure is having to
produce a result set for crystal reports. This means that there has to
be a large amount of presentation logic within the stored procedure
(which I really hate, in my opinion crystal and other reporting tools
should provide a c# mid layer to allow that sort of manipulation of the
data). The requirements of the report mean that we need to update rows
based on others already in the table, and generally manipulate the
table to get it into the right format for crystal. There may be a
purely set based solution, but in this case I think it would be
detrimental to performance.
I would love it if there was a way to keep the queries simple
functional requests, but with no mid layer it's not that simple.

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.

>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regu
lar
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2
,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and reg
ular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then ru
n
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows.
I
>notice that the values returned in the IndexName column are all valid inde
x
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way
,
>in order to get the SQL 2000 version to return just index names (and not al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "1
8
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.

>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
>>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>>sysindexes.status & 0x20=0 to identify real indexes."
>>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>>Roy Harvey
>>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Wednesday, March 28, 2012

Index size question

I have a SQL 2000 table with 16 million rows. I made a copy of it. The
two tables have the same number of rows, 16,152,139 to be exact.
The old table had a clustered, composite primary key across the first four
columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
(varchar(20)), and transaction date (smalldatetime). The database size was
1,708,032 KB and the index was 8,520 KB.
To the new table, I added an ID field of type Int, and made it the primary
key nonclustered, also an identity field. The only other index is a
different date field in the table that's a clustered index (smalldatetime).
I also set the index fill factor to 80% from 90% in the old one.
The new table takes 2,406,592 KB; it's bigger because of the extra field.
BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
two single-column indexes take 19 times the storage space as one 4-column
composite index?* I have run dbcc dbreindex on the table.
I also ran dbcc updateusage on the new table and got trivial differences.
Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
DBCC SHOWCONTIG scanning 'Transactions-Old' table...
Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 212443
- Extents Scanned.......................: 26688
- Extent Switches.......................: 26687
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.50%
- Avg. Bytes Free per Page................: 766.4
- Avg. Page Density (full)................: 90.53%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 280366
- Extents Scanned.......................: 35103
- Extent Switches.......................: 35102
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.11%
- Avg. Bytes Free per Page................: 1562.7
- Avg. Page Density (full)................: 80.69%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 19966
- Extents Scanned.......................: 2500
- Extent Switches.......................: 2499
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.60%
- Avg. Bytes Free per Page................: 6.2
- Avg. Page Density (full)................: 99.92%
I would post the DDL but I can't find the link to get the format...
Thanks.
David WalkerThis should be the DDL:
CREATE TABLE [dbo].[Transactions] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] [int] NULL ,
[Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Transactions-Old] (
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
(month,[Trade_Date])) ,
[Acct_Pre] AS (left([Acct_Number],3))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[SSN_TIN],
[ACCT_NUMBER],
[Settle_Date],
[SEQ_NUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
> I have a SQL 2000 table with 16 million rows. I made a copy of it.
> The two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first
> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
> number (varchar(20)), and transaction date (smalldatetime). The
> database size was 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the
> primary key nonclustered, also an identity field. The only other
> index is a different date field in the table that's a clustered index
> (smalldatetime). I also set the index fill factor to 80% from 90% in
> the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra
> field. BUT the index (as shown in the Task Pad summary) is 163,656
> KB. *How could two single-column indexes take 19 times the storage
> space as one 4-column composite index?* I have run dbcc dbreindex on
> the table.
> I also ran dbcc updateusage on the new table and got trivial
> differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can
> look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
>|||Oops -- the new Transactions table actually has the ID (int) field
indexed twice, both clustered and non-clustered, with 2 different
indexes. Now how did that happen? :-)
I'll fix the indexes and check again.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> I have a SQL 2000 table with 16 million rows. I made a copy of it.
>> The two tables have the same number of rows, 16,152,139 to be exact.
>> The old table had a clustered, composite primary key across the first
>> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
>> number (varchar(20)), and transaction date (smalldatetime). The
>> database size was 1,708,032 KB and the index was 8,520 KB.
>> To the new table, I added an ID field of type Int, and made it the
>> primary key nonclustered, also an identity field. The only other
>> index is a different date field in the table that's a clustered index
>> (smalldatetime). I also set the index fill factor to 80% from 90% in
>> the old one.
>> The new table takes 2,406,592 KB; it's bigger because of the extra
>> field. BUT the index (as shown in the Task Pad summary) is 163,656
>> KB. *How could two single-column indexes take 19 times the storage
>> space as one 4-column composite index?* I have run dbcc dbreindex on
>> the table.
>> I also ran dbcc updateusage on the new table and got trivial
>> differences.
>> Here is what SHOWCONTIG gives, if that helps. Anything else I can
>> look at?
>> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
>> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 212443
>> - Extents Scanned.......................: 26688
>> - Extent Switches.......................: 26687
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.50%
>> - Avg. Bytes Free per Page................: 766.4
>> - Avg. Page Density (full)................: 90.53%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 280366
>> - Extents Scanned.......................: 35103
>> - Extent Switches.......................: 35102
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.11%
>> - Avg. Bytes Free per Page................: 1562.7
>> - Avg. Page Density (full)................: 80.69%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
>> LEAF level scan performed.
>> - Pages Scanned........................: 19966
>> - Extents Scanned.......................: 2500
>> - Extent Switches.......................: 2499
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 0.60%
>> - Avg. Bytes Free per Page................: 6.2
>> - Avg. Page Density (full)................: 99.92%
>> I would post the DDL but I can't find the link to get the format...
>> Thanks.
>> David Walker
>|||Um, I fixed the incorrect index to be a clustered index on the
Trade_Date column like it should have been, and the results are
essentially the same. I'm still confused.
Thanks for any insights.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> I have a SQL 2000 table with 16 million rows. I made a copy of it.
>> The two tables have the same number of rows, 16,152,139 to be exact.
>> The old table had a clustered, composite primary key across the first
>> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
>> number (varchar(20)), and transaction date (smalldatetime). The
>> database size was 1,708,032 KB and the index was 8,520 KB.
>> To the new table, I added an ID field of type Int, and made it the
>> primary key nonclustered, also an identity field. The only other
>> index is a different date field in the table that's a clustered index
>> (smalldatetime). I also set the index fill factor to 80% from 90% in
>> the old one.
>> The new table takes 2,406,592 KB; it's bigger because of the extra
>> field. BUT the index (as shown in the Task Pad summary) is 163,656
>> KB. *How could two single-column indexes take 19 times the storage
>> space as one 4-column composite index?* I have run dbcc dbreindex on
>> the table.
>> I also ran dbcc updateusage on the new table and got trivial
>> differences.
>> Here is what SHOWCONTIG gives, if that helps. Anything else I can
>> look at?
>> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
>> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 212443
>> - Extents Scanned.......................: 26688
>> - Extent Switches.......................: 26687
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.50%
>> - Avg. Bytes Free per Page................: 766.4
>> - Avg. Page Density (full)................: 90.53%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 280366
>> - Extents Scanned.......................: 35103
>> - Extent Switches.......................: 35102
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.11%
>> - Avg. Bytes Free per Page................: 1562.7
>> - Avg. Page Density (full)................: 80.69%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
>> LEAF level scan performed.
>> - Pages Scanned........................: 19966
>> - Extents Scanned.......................: 2500
>> - Extent Switches.......................: 2499
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 0.60%
>> - Avg. Bytes Free per Page................: 6.2
>> - Avg. Page Density (full)................: 99.92%
>> I would post the DDL but I can't find the link to get the format...
>> Thanks.
>> David Walker
>|||David,
The table holds the leaf level pages of the clustered index. This means
that adding a clustered index to a heap (a table without clustered
index) will add only a few percent to the total size. A nonclustered
index is stored entirely separate from the table.
Maybe you hadn't noticed, but it is impossible to index 16 million rows
in 8 million bytes. (16,152,139 rows in 8,520 KB).
When you create a nonclustered index, it needs space for the indexed
column(s) plus space for the clustered key of each row. So the wider the
clustered index, the bigger any nonclustered index will be. When adding
a nonclustered index, all of a sudden the size of the clustered index
shows...
Hope this helps,
Gert-Jan
DW wrote:
> I have a SQL 2000 table with 16 million rows. I made a copy of it. The
> two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first four
> columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
> (varchar(20)), and transaction date (smalldatetime). The database size was
> 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the primary
> key nonclustered, also an identity field. The only other index is a
> different date field in the table that's a clustered index (smalldatetime).
> I also set the index fill factor to 80% from 90% in the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra field.
> BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
> two single-column indexes take 19 times the storage space as one 4-column
> composite index?* I have run dbcc dbreindex on the table.
> I also ran dbcc updateusage on the new table and got trivial differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
--
(Please reply only to the newsgroup)|||Hi David,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you informaiton provided, you generate the SQL script from one table.
You noticed that the ID column in the following part:
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
There is an clustered index and nonclustered index bulid on the table. You
wonder how it come, right?
To get the information on the table, you could run this statement in you
Query Analyzer:
exec sp_help transactions
OR
sp_helpindex transactions
From my experience, in the Enterprise Manger you have first create a
clustered index 'IX_CL_Transactions_TradeDate' on the column ID, then you
create a PRIMARY KEY constraint on this same column. So, finaly, you will
found that the 'sp_help transactions' or 'sp_helpindex transactions' will
show that the index on the column ID is non-clustered.
For maintenance purpose, you could run the DBCC INDEXDEFRAG
Hope this helps and if you still have questions, please feel free to post
your message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Index size question

Monday, March 26, 2012

Index Rebuilding question

Hi,
Novice DBA here.
Is there any reason or bad side effect why I should NOT rebuild/reorganise
an index?
We have a table that has 500,000 leaf rows on 2500 pages that is 70%
fragmented. There are a couple others that are similar too.
We have never done a maintenance plan (other than backups) and things appear
to be running fine. Noone is complaining about speed. We have had this
DB/application for about 6 months now.
I was looking through the available reports and saw the Index Physical Stats
report and I'm wondering if things can get much better than just fine if I
do the operation recommended.
Thanks
Brian
Brian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, queries
that scan part or all of a table.
If you have fragmentation and have not seen any changes in the performance
of your application is maybe because your indexes are not big enough (See
number of pages scanned as shown on dbcc showcontig) or maybe your
application is not doing enough range scans.
According to BOL the recommendation to reindex depends on the value of
avg_fragmentation_in_percent value in sys.dm_db_index_physical_stats.
BOL recommends to reorganize if this value is between 5 and 30 % and to
rebuild if this value is greater than 30 %.
Anyway, even if indexing were not needed it will not hurt your system either
(except the resources used when you are running the index operation and maybe
some additional transaction log space).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Brian" wrote:

> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things appear
> to be running fine. Noone is complaining about speed. We have had this
> DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical Stats
> report and I'm wondering if things can get much better than just fine if I
> do the operation recommended.
> Thanks
> Brian
>
>
|||1) Make sure the transaction log is sized big enough to avoid growths while
the index is rebuilding.
2) Performance will suffer.
3) unless you have enterprise edition and have online rebuild on the index
will be unavailable.
4) Ensure you size the database sufficiently in advance so that there is
sufficient contiguous space to lay the pages down in order.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>
|||Thank you both for your replies.
Together your replies indicate it is better to do it than not but not a
great priority.
We are using the SQLExpress so I will reindex one day when noone is about.
Thanks
Brian
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>

Index Rebuilding question

Hi,
Novice DBA here.
Is there any reason or bad side effect why I should NOT rebuild/reorganise
an index?
We have a table that has 500,000 leaf rows on 2500 pages that is 70%
fragmented. There are a couple others that are similar too.
We have never done a maintenance plan (other than backups) and things appear
to be running fine. Noone is complaining about speed. We have had this
DB/application for about 6 months now.
I was looking through the available reports and saw the Index Physical Stats
report and I'm wondering if things can get much better than just fine if I
do the operation recommended.
Thanks
BrianBrian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, querie
s
that scan part or all of a table.
If you have fragmentation and have not seen any changes in the performance
of your application is maybe because your indexes are not big enough (See
number of pages scanned as shown on dbcc showcontig) or maybe your
application is not doing enough range scans.
According to BOL the recommendation to reindex depends on the value of
avg_fragmentation_in_percent value in sys.dm_db_index_physical_stats.
BOL recommends to reorganize if this value is between 5 and 30 % and to
rebuild if this value is greater than 30 %.
Anyway, even if indexing were not needed it will not hurt your system either
(except the resources used when you are running the index operation and mayb
e
some additional transaction log space).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Brian" wrote:

> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things appe
ar
> to be running fine. Noone is complaining about speed. We have had this
> DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical Sta
ts
> report and I'm wondering if things can get much better than just fine if I
> do the operation recommended.
> Thanks
> Brian
>
>|||1) Make sure the transaction log is sized big enough to avoid growths while
the index is rebuilding.
2) Performance will suffer.
3) unless you have enterprise edition and have online rebuild on the index
will be unavailable.
4) Ensure you size the database sufficiently in advance so that there is
sufficient contiguous space to lay the pages down in order.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>

Index Rebuilding question

Hi,
Novice DBA here.
Is there any reason or bad side effect why I should NOT rebuild/reorganise
an index?
We have a table that has 500,000 leaf rows on 2500 pages that is 70%
fragmented. There are a couple others that are similar too.
We have never done a maintenance plan (other than backups) and things appear
to be running fine. Noone is complaining about speed. We have had this
DB/application for about 6 months now.
I was looking through the available reports and saw the Index Physical Stats
report and I'm wondering if things can get much better than just fine if I
do the operation recommended.
Thanks
BrianBrian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, queries
that scan part or all of a table.
If you have fragmentation and have not seen any changes in the performance
of your application is maybe because your indexes are not big enough (See
number of pages scanned as shown on dbcc showcontig) or maybe your
application is not doing enough range scans.
According to BOL the recommendation to reindex depends on the value of
avg_fragmentation_in_percent value in sys.dm_db_index_physical_stats.
BOL recommends to reorganize if this value is between 5 and 30 % and to
rebuild if this value is greater than 30 %.
Anyway, even if indexing were not needed it will not hurt your system either
(except the resources used when you are running the index operation and maybe
some additional transaction log space).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Brian" wrote:
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things appear
> to be running fine. Noone is complaining about speed. We have had this
> DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical Stats
> report and I'm wondering if things can get much better than just fine if I
> do the operation recommended.
> Thanks
> Brian
>
>|||1) Make sure the transaction log is sized big enough to avoid growths while
the index is rebuilding.
2) Performance will suffer.
3) unless you have enterprise edition and have online rebuild on the index
will be unavailable.
4) Ensure you size the database sufficiently in advance so that there is
sufficient contiguous space to lay the pages down in order.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>|||Thank you both for your replies.
Together your replies indicate it is better to do it than not but not a
great priority.
We are using the SQLExpress so I will reindex one day when noone is about.
Thanks
Brian
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>sql

Index Question

Index question

When I have tables with small rows and few rows, are indexes needed?
ex: the U.S. federal government numbers every county in the USA. In my
state there are 100 counties. Create Table Counties (
CountyCode smallint,
CountyName VarChar(30),
Region smallint)
All data for all 100 counties will easily be read by SQL in one physical
read. So, is there any reason to create an index? Most applications
written in my office have lookup tables with a small number of small
rows. Same question.
Thanks in advance
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
Tom
If you have small table so optimizer will choose scan table to retrieve
the data. An index may improve the performance if your WHERE clause searches
for a few rows and the index is created on selective (at least 95%) column
Just testing,testing....
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:O4dVMNqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> When I have tables with small rows and few rows, are indexes needed?
> ex: the U.S. federal government numbers every county in the USA. In my
> state there are 100 counties. Create Table Counties (
> CountyCode smallint,
> CountyName VarChar(30),
> Region smallint)
> All data for all 100 counties will easily be read by SQL in one physical
> read. So, is there any reason to create an index? Most applications
> written in my office have lookup tables with a small number of small rows.
> Same question.
> Thanks in advance
> Tom
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
>
|||if you modify tables without any indexes, you may end up locking up the
whole table.
Also the best practice is to have a primary key for every table.
|||[repost]
Every table should have at least one key. Keys exist to maintain the
integrity of your data not for performance. In SQL Server a key
constraint, whether PRIMARY KEY or UNIQUE, always has an index. So the
answer is yes, but not all indexes are required for performance
reasons.
BTW, under SQL Server's default ANSI configuration your sample table
can't have a key because all the columns are nullable. If the CREATE
TABLE statement you posted is accurate then I suggest you fix that.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Bah.
small reference tables do not require indexes. Keys are kind of nice,
but the average schmuck dba can ususally figure out how a reference
table works.
for really small tables, it is about 6's to add an index or not.
Personal preference.
My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
pretty close to 20.
|||Doug wrote:
> Bah.
> small reference tables do not require indexes. Keys are kind of nice,
> but the average schmuck dba can ususally figure out how a reference
> table works.
> for really small tables, it is about 6's to add an index or not.
> Personal preference.
> My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
> pretty close to 20.
The job of a key is to help out the DBA? That's a good one!
I really hope you meant your reply ironically. If not, I hope no
database I see will ever be a victim of your rule of thumb.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Friday, March 23, 2012

Index question

When I have tables with small rows and few rows, are indexes needed?
ex: the U.S. federal government numbers every county in the USA. In my
state there are 100 counties. Create Table Counties (
CountyCode smallint,
CountyName VarChar(30),
Region smallint)
All data for all 100 counties will easily be read by SQL in one physical
read. So, is there any reason to create an index? Most applications
written in my office have lookup tables with a small number of small
rows. Same question.
Thanks in advance
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Tom
If you have small table so optimizer will choose scan table to retrieve
the data. An index may improve the performance if your WHERE clause searches
for a few rows and the index is created on selective (at least 95%) column
Just testing,testing....
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:O4dVMNqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> When I have tables with small rows and few rows, are indexes needed?
> ex: the U.S. federal government numbers every county in the USA. In my
> state there are 100 counties. Create Table Counties (
> CountyCode smallint,
> CountyName VarChar(30),
> Region smallint)
> All data for all 100 counties will easily be read by SQL in one physical
> read. So, is there any reason to create an index? Most applications
> written in my office have lookup tables with a small number of small rows.
> Same question.
> Thanks in advance
> Tom
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
>|||if you modify tables without any indexes, you may end up locking up the
whole table.
Also the best practice is to have a primary key for every table.|||[repost]
Every table should have at least one key. Keys exist to maintain the
integrity of your data not for performance. In SQL Server a key
constraint, whether PRIMARY KEY or UNIQUE, always has an index. So the
answer is yes, but not all indexes are required for performance
reasons.
BTW, under SQL Server's default ANSI configuration your sample table
can't have a key because all the columns are nullable. If the CREATE
TABLE statement you posted is accurate then I suggest you fix that.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Bah.
small reference tables do not require indexes. Keys are kind of nice,
but the average schmuck dba can ususally figure out how a reference
table works.
for really small tables, it is about 6's to add an index or not.
Personal preference.
My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
pretty close to 20.|||Doug wrote:
> Bah.
> small reference tables do not require indexes. Keys are kind of nice,
> but the average schmuck dba can ususally figure out how a reference
> table works.
> for really small tables, it is about 6's to add an index or not.
> Personal preference.
> My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
> pretty close to 20.
The job of a key is to help out the DBA? That's a good one!
I really hope you meant your reply ironically. If not, I hope no
database I see will ever be a victim of your rule of thumb.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Friday, March 30, 2012

index tepmorary table

Index statement on SQL 2000 vs. SQL 2005

Index statement on SQL 2000 vs. SQL 2005

Index statement on SQL 2000 vs. SQL 2005

Wednesday, March 28, 2012

Index size question

Index size question

Monday, March 26, 2012

Index Rebuilding question

Index Rebuilding question

Index Rebuilding question

Index Question

Index question

Friday, March 23, 2012

Index question

Index Question

index and key

Blog Archive

About Me