Sql Create error: indexes

Showing posts with label indexes. Show all posts

Sunday, March 11, 2012

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
Mike
Mike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike
|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Just a minor point, if you don't mind, David (and some additional points for Mike):

> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clustering key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not having that column as
the first column in the index makes it useless for SEEK over the column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) and by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of columns should be unique
> because the business says they are, then a unique index should be used. If the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If you create a
> non-unique, clustered index, each column value that has repeating values has a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an UNIQUE INT value as
> suggested, could decrease index size if there are many repeating values. In this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column added onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID would give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your index statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-clustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I then created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clustered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. However, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
MikeMike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just a minor point, if you don't mind, David (and some additional points for
Mike):

> 2- A table with a clustered index uses the clustered index key as the poin
ter in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would
have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Und
erneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clusterin
g key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and
want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index
is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not
having that column as
the first column in the index makes it useless for SEEK over the column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) an
d by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a
matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of colu
mns should be unique
> because the business says they are, then a unique index should be used. If
the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the poin
ter in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would
have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Und
erneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If
you create a
> non-unique, clustered index, each column value that has repeating values h
as a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an U
NIQUE INT value as
> suggested, could decrease index size if there are many repeating values. I
n this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column a
dded onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID wou
ld give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your i
ndex statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and
looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + C
OL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-cl
ustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c
5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I the
n created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clus
tered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. H
owever, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
MikeMike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just a minor point, if you don't mind, David (and some additional points for Mike):
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clustering key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not having that column as
the first column in the index makes it useless for SEEK over the column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
>> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
>> Could anyone please explain:
>> i) if this is indeed best practice?
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) and by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of columns should be unique
> because the business says they are, then a unique index should be used. If the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If you create a
> non-unique, clustered index, each column value that has repeating values has a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an UNIQUE INT value as
> suggested, could decrease index size if there are many repeating values. In this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column added onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID would give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your index statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
>> 2- A table with a clustered index uses the clustered index key as
>> the pointer in all non-clustered indexes. Using your co-workers
>> recommended scenario in this case you would have a unique, clustered
>> index on ID, and a unique, non-clustered index on COL1 + ID.
>> Underneath, the index will really have ID + COL1 + ID, which just
>> adds overhead.
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-clustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Just a minor point, if you don't mind, David (and some additional
>> points for Mike):
>> 2- A table with a clustered index uses the clustered index key as
>> the pointer in all non-clustered indexes. Using your co-workers
>> recommended scenario in this case you would have a unique, clustered
>> index on ID, and a unique, non-clustered index on COL1 + ID.
>> Underneath, the index will really have ID + COL1 + ID, which just
>> adds overhead.
>> SQL Server will not store the clustering key twice if you name the
>> clustering key in the nc index. In other words, say you have a unique
>> clustered index on the "ID" column and want to create a nc index on
>> the "Data" column, then both below will do the exact same thing:
>> (data, id)
>> (data)
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I then created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clustered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. However, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Thursday, March 8, 2012

create only sp's during schema initialization

Hey, I've created the indexes and tables and stuff manually. I just need SQL
to create the 3 sp's on each table during Trans Repl. Does it do that or it's
all or nothing kind of situation?
Right now, I manually edited about 20 schema files from snapshot to not drop
and recreate the table. And, it takes time.
Tejas,
for a nosync initialization, if you run sp_scriptpublicationcustomprocs
'publicationname' at the publisher, the results (in text format) are 3
stored procedure creation scripts. These are then run on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||see, when you say, 'no, the subscriber already has the schema and data' even
the data doesn't get transferred over. How would I be able to do it through
replication process? or i have to sue bcp or dts or something EXTERNALLY?
Thank you.
|||Tejas,
now I'm confused

I thought you were trying to achieve a nosync
initialization? If not, then the normal initialization process will take
care of the data transfer. Please can you clarify a bit more for me exactly
what you want to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
What had happened is this.
I was asked to script the tables and indexes from the publisher and run them
on the sub. Now, I cannot use the 'yes,initialize the schema' option as it
would overwrite all that. But at the same time, I could not use 'no, the sub
already has the schema and data' option cuz that would not transfer over the
data. That's what I was asking you about.
|||Tejas,
what I don't understand is the point of putting just the schema on the
subscriber. It's standard practice to do a full initialization (schema and
data) or a nosync one (neither). So, if there is no reason for creating the
shema on the subscriber, then I'd do a standard initialization and let it
drop the articles on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I do it the way you asked me to, the replication finishes doing the
schema and most of the data and when it starts doing the indexes, the log
file grows like crazy an i dont have that much space to fulfill the logspace
need. Is there a work around this? And i think it evetually times out. I read
somewhere to increase the querytimeout for this. But still, how about the log
file space?
thank you.
by the way, I tried it the way you had said first
|||Tejas,
if you're struggling for space to host the log file, there's no simple fix

Options include creating a separate log file on another disk, trying
simple recovery mode etc
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Saturday, February 25, 2012

Create indexes for Query Optimization

I am using ASP.net application on SQL server 2005.
I am getting data from the DB using this SQL statement from 2 tables:
Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
Table1 has around 2,000,000 Records and table has 3 records.
The query is running very slow.
How do Optimize the query using Indexes ?
On Which fields should I create the indexes ?
Thanks
ra294
ra294@.hotmail.comra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>
try using database engine tuning advisor, find it in tools menu of the
management studio|||Without looking at the data Im just guessing but try doing this:
Table1 index: Key columns: a,b,e
Table2 index: key columns: e included: c
MC
"ra294" <ra294@.hotmail.com> wrote in message
news:uTLstxSUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>|||ra294,
You might want to recheck your posting. The query is not valid (because
of typos?), and the number of rows are suspect (really just 3 rows in
one table and 2 million rows in the other table?).
How many rows does the query return? 3? 6 million? How big is the table
with the 2 million rows (how many pages or how much MB)?
How slow is the query currently? 200 milliseconds, 5 seconds, 5 minutes?
In general, you should always define a Primary Key for each table. This
will automatically create a corresponding unique index. You should also
define any Foreign Key relations. In general, it is a good idea to index
Foreign Key relations.
If you would like more assistence, then please post simplified DDL (and
the answers to the questions above).
--
Gert-jan
ra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com|||On Jan 7, 6:56=A0pm, "ra294" <ra...@.hotmail.com> wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=3DTable.e Where Table1.a=3DXXX AND Table1.b>YYY Order Table1.a DE=SC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes =A0?
> Thanks
> ra294
> ra...@.hotmail.com
Do you have index on either columns a or b? Also, you might consider
remove the order by clause and do it later.

Create Indexes & extra columns!

Hi guys
2 questions.
Is it recomenable to create
1. Indexes
2. extra columns
on a table while the database is being used?
I have not yet had the guts to do it while the database was in use however
it would be somewhat easier if I could?
Regards
Jonas
Jonas Larsen wrote:
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use
> however it would be somewhat easier if I could?
> Regards
> Jonas
Recommended is a loaded word. You can certainly perform both operations
while users are accessing the database. Adding a column should be quick
as long as you don't have to load data into the new column. Adding an
index is a more time consuming process if the table is large. If you
create a clustered index, you'll likely take the table offline until the
operation is complete and all non-clustered indexes are rebuilt.
If you are concerned about affecting the availability of the table in
question, schedule the operation to take place at night using the SQL
Server Agent.
David G.
|||I agree with David, if you choose to do this during the day, the structures
will still ( automatically) be unavailable until the process completes,
(which could take some time.)
most people would schedule this during off hours... and backup prior...
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
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23m%23Ai4liEHA.3288@.TK2MSFTNGP10.phx.gbl...
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use however
> it would be somewhat easier if I could?
> Regards
> Jonas
>

Create Indexes & extra columns!

Hi guys
2 questions.
Is it recomenable to create
1. Indexes
2. extra columns
on a table while the database is being used?
I have not yet had the guts to do it while the database was in use however
it would be somewhat easier if I could?
Regards
JonasJonas Larsen wrote:
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use
> however it would be somewhat easier if I could?
> Regards
> Jonas
Recommended is a loaded word. You can certainly perform both operations
while users are accessing the database. Adding a column should be quick
as long as you don't have to load data into the new column. Adding an
index is a more time consuming process if the table is large. If you
create a clustered index, you'll likely take the table offline until the
operation is complete and all non-clustered indexes are rebuilt.
If you are concerned about affecting the availability of the table in
question, schedule the operation to take place at night using the SQL
Server Agent.
David G.|||I agree with David, if you choose to do this during the day, the structures
will still ( automatically) be unavailable until the process completes,
(which could take some time.)
most people would schedule this during off hours... and backup prior...
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
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23m%23Ai4liEHA.3288@.TK2MSFTNGP10.phx.gbl...
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use however
> it would be somewhat easier if I could?
> Regards
> Jonas
>

Create Indexes & extra columns!

Hi guys
2 questions.
Is it recomenable to create
1. Indexes
2. extra columns
on a table while the database is being used?
I have not yet had the guts to do it while the database was in use however
it would be somewhat easier if I could?
Regards
JonasJonas Larsen wrote:
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use
> however it would be somewhat easier if I could?
> Regards
> Jonas
Recommended is a loaded word. You can certainly perform both operations
while users are accessing the database. Adding a column should be quick
as long as you don't have to load data into the new column. Adding an
index is a more time consuming process if the table is large. If you
create a clustered index, you'll likely take the table offline until the
operation is complete and all non-clustered indexes are rebuilt.
If you are concerned about affecting the availability of the table in
question, schedule the operation to take place at night using the SQL
Server Agent.
David G.|||I agree with David, if you choose to do this during the day, the structures
will still ( automatically) be unavailable until the process completes,
(which could take some time.)
most people would schedule this during off hours... and backup prior...
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
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23m%23Ai4liEHA.3288@.TK2MSFTNGP10.phx.gbl...
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use however
> it would be somewhat easier if I could?
> Regards
> Jonas
>

Create indexes - own File Group

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173725223.934126.197140@.30g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Greg,
Its a data warehouse. I have tools that could rebuild an empty
database pretty quickly (few hours) and I have daily backups. Builds
take about 2 hours, then its read only the rest of the day.
I use enterprise because of our licensing.

Thanks for the info!

Quote:

Originally Posted by

>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
>
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
>

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Create Index option in the table designer

Hi All,
In the table designer, I don't seem to find any option to create indexes.
Can indexes on tables be only created using the SQL Query Analyser?
kd
In Enterprise Manager, right click on the table and select
All Tasks and then Manage Indexes. Click on the new button
to create a new index.
If you are in design view of a table, you can right click in
the designer and select Indexes/Keys.
-Sue
On Mon, 11 Apr 2005 03:51:01 -0700, "kd"
<kd@.discussions.microsoft.com> wrote:

>Hi All,
>In the table designer, I don't seem to find any option to create indexes.
>Can indexes on tables be only created using the SQL Query Analyser?
>kd

create index on table

Hi,
What's the difference/performance difference when you
create a index with two columns combined vs create two
indexes with each one of them as showed below?
1.
CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
WITH FILLFACTOR = 75 ON [primary]
2.
CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
FILLFACTOR = 75 ON [primary]
CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
FILLFACTOR = 75 ON [primary]
thanks a lot!
JJOne Index with both columns will provide roughly the same index relief as
two individual columns.
Two separate indexes adds over head as each individual index has to be
maintained by SQL Server.
Greg Jackson
PDX, Oregon|||"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:122f001c4429d$86cb8500$a301280a@.phx.gbl...
> Hi,
> What's the difference/performance difference when you
> create a index with two columns combined vs create two
> indexes with each one of them as showed below?
>
An index can only be accessed by its leading column(s). So an index on two
columns is usefull when accessing the table by the leading column of the
index or both the columns of the index. But an index on two columns cannot
be used when accessing the table by the second columns of the index only.
So in your example
CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
WITH FILLFACTOR = 75 ON [primary]
index1 cannot be used to filter
select * from table1 where date = '2002-05-05'
but can be used to filter
select * from table1 where market = 3 and date = '2002-05-05'
and a query of the form
select market, date from table1 where market = 1
is covered by the query and can be processed completely from the index
without hitting the base table.
On the other hand two seperate indexes have more overhead, and don't do
particularly well with queries which specify both columns, as either index
may be used but not both.
select * from table1 where market = 3 and date = '2002-05-05'
David|||Actually that is not quite true on two counts. The idea is correct but the
details are misleading.
> An index can only be accessed by its leading column(s). So an index on
two
SQL Server can actually still use the index when searching for the second
column but not with a SEEK, only a SCAN. If no other index is available it
may be cheaper to scan the compound index than scanning the entire table.
But obviously this is not the recommended way to do this.
> On the other hand two separate indexes have more overhead, and don't do
> particularly well with queries which specify both columns, as either index
> may be used but not both.
This is not totally true either. SQL Server can in fact use two separate
indexes in what is called "Index Intersection" to find common rows between
the two indexes. So if you do have a situation where you need to search on
both columns individually it is often helpful to have two separate indexes.
Sometimes you may find having a compound index and a second one with a
single column useful as well. As always it depends.
--
Andrew J. Kelly
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23UTTNJqQEHA.644@.tk2msftngp13.phx.gbl...
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:122f001c4429d$86cb8500$a301280a@.phx.gbl...
> > Hi,
> >
> > What's the difference/performance difference when you
> > create a index with two columns combined vs create two
> > indexes with each one of them as showed below?
> >
> An index can only be accessed by its leading column(s). So an index on
two
> columns is usefull when accessing the table by the leading column of the
> index or both the columns of the index. But an index on two columns
cannot
> be used when accessing the table by the second columns of the index only.
> So in your example
> CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
> WITH FILLFACTOR = 75 ON [primary]
> index1 cannot be used to filter
> select * from table1 where date = '2002-05-05'
> but can be used to filter
> select * from table1 where market = 3 and date = '2002-05-05'
> and a query of the form
> select market, date from table1 where market = 1
> is covered by the query and can be processed completely from the index
> without hitting the base table.
>
> On the other hand two seperate indexes have more overhead, and don't do
> particularly well with queries which specify both columns, as either index
> may be used but not both.
> select * from table1 where market = 3 and date = '2002-05-05'
> David
>|||wow! you are all so knowledgable! thank you so much for
being so helpful!
JJ
>--Original Message--
>Hi,
>What's the difference/performance difference when you
>create a index with two columns combined vs create two
>indexes with each one of them as showed below?
>1.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET],
[DATE])
>WITH FILLFACTOR = 75 ON [primary]
>2.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
>FILLFACTOR = 75 ON [primary]
>CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
>FILLFACTOR = 75 ON [primary]
>
>thanks a lot!
>JJ
>.
>|||wow! you are all so knowledgable! thank you so much for
being so helpful!
JJ
>--Original Message--
>Hi,
>What's the difference/performance difference when you
>create a index with two columns combined vs create two
>indexes with each one of them as showed below?
>1.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET],
[DATE])
>WITH FILLFACTOR = 75 ON [primary]
>2.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
>FILLFACTOR = 75 ON [primary]
>CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
>FILLFACTOR = 75 ON [primary]
>
>thanks a lot!
>JJ
>.
>

CREATE INDEX on large table

SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes incrementally vs adding an index??An index on a sorted table is quicker as the indexing process does not need
to reorganized it as its creating the index.

"dfurtney" <dfurtney@.hotmail.com> wrote in message
news:1105492090.497574.233360@.z14g2000cwz.googlegr oups.com...
> SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
> that we need to add some indexes for. In a test, it took over 12 hours
> to CREATE a new INDEX against this table. One of us suggested that we
> create a temp table with the new index and copy the data from the old
> table into the new one, then rename it. I understand this took 15
> minutes. Why the heck would it be faster to move the data and build
> multiple indexes incrementally vs adding an index??|||Hi

This seems unlikely.
You did not mention if the table is a heap (i.e. there is no clustered
index).
You probably ran into a case where the data in the old table was so out
of order that building the additional index was constatnly splitting
pages.|||dfurtney (dfurtney@.hotmail.com) writes:
> SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
> that we need to add some indexes for. In a test, it took over 12 hours
> to CREATE a new INDEX against this table. One of us suggested that we
> create a temp table with the new index and copy the data from the old
> table into the new one, then rename it. I understand this took 15
> minutes. Why the heck would it be faster to move the data and build
> multiple indexes incrementally vs adding an index??

12 hours to create an index for three million rows sounds abnormal.

Of course, if the table did not have a clustered index, but already had
several non-clustered index, and you added a clustered index, then it
will take some time, but still not 12 hours.

One possible reason, is that the CREATE INDEX process was blocked by
another process most of the time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The table, vehicle_history, had a clustered primary key and 2
additional indexes. We were adding an additional index on a single
integer column. That column was filled with a single default value of
0 in this case. It was running on a dedicated QA server.

We want to add this index to speed up a query against the new column.
But we found it was taking much longer than we expected to add the
index. The only thing that seemed somewhat unusual is the size of the
table.|||dfurtney (dfurtney@.hotmail.com) writes:
> The table, vehicle_history, had a clustered primary key and 2
> additional indexes. We were adding an additional index on a single
> integer column. That column was filled with a single default value of
> 0 in this case. It was running on a dedicated QA server.
> We want to add this index to speed up a query against the new column.
> But we found it was taking much longer than we expected to add the
> index. The only thing that seemed somewhat unusual is the size of the
> table.

Does all three million rows have 0 in this column? In that case it would
not be a very good index.

I have no idea whether large amount of duplicate values could be reason
that creating the index so long. I still lean towards that there was some-
thing else, for instance blocking, that was the cause. It simply doesn't
take 12 hours to create a non-clustered index on a three-million row table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The query/index would be used by a subset of our customers that utilize
a specific feature of the product. For them, the values would be
non-zero, of course, and the resulting index quite selective. However,
we were going to add the index for all customers since we generally
don't know what functionaly they will be utilizing. This dataset was
from a customer not using that function.

I thought it was awfully long and one of my co-workers was going to do
some testing. What order of time would you expect?|||dfurtney (dfurtney@.hotmail.com) writes:
> The query/index would be used by a subset of our customers that utilize
> a specific feature of the product. For them, the values would be
> non-zero, of course, and the resulting index quite selective. However,
> we were going to add the index for all customers since we generally
> don't know what functionaly they will be utilizing. This dataset was
> from a customer not using that function.
> I thought it was awfully long and one of my co-workers was going to do
> some testing. What order of time would you expect?

The below script which emulates the situation you have described ran
in eight minutes on my workstation, a 2.8 GHz HT box with 1 GB of RAM
(but with SQL Server constrained to some 120 MB), running Windows XP SP2.
The particular part of creating a non-clustered index on a column with
non-variant values took 30 seconds. (But then all data was in cache.)

Of course, not only number of rows count, but the size of the rows as
well, since wider the rows, the more pages you get. Then again, for
the sorting phase there are still only three million rows.

It occurred to me that one thing you could have run into is autogrow.
If the database is 300 GB, and you have 10% autogrow and this happens to
set in during the index creation, you're in for a pause. Initializing
30 GB of data does take some time. Not 12 hours though. 20-30 minutes
may be expected.

use master
go
drop database klump
go
create database klump
go
use klump
go
select TOP 3000000 klumpid = identity(int, 1, 1),
slaskcol = 0,
a.* into klump
from Northwind..Orders a
cross join Northwind..Orders b
cross join Northwind..Orders c
go
ALTER TABLE klump ADD CONSTRAINT pk_klump PRIMARY KEY (klumpid)
go
CREATE INDEX orderidix ON klump (OrderID)
CREATE INDEX customerid ON klump (CustomerID)
go
SELECT getdate()
go
CREATE INDEX slaskix ON klump(slaskcol)
go
SELECT getdate()

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Although this happened twice on two machines when analyzing the upgrade
script via profiler -- our latest attempts to isolate what is happening
ended up not reproducing our earlier results. The indexes are building
in 2 minutes when tested in isolation outside the script. The only
operation which we have reliably reproduced as slow is adding a column
with a default value to a large table - which is taking on the order of
20 minutes in the million row range. We do this a number of times. We
don't yet have an explanation for why the script took so long, although
we are running the analysis one more time as I speak.

Sorry to have presented you with a problem that didn't reproduce - but
we were about to make some drastic changes based on the faulty
assumption that building million row indexes was much more expensive
then it really is. I appreciate the help you folks have provided!|||dfurtney (dfurtney@.hotmail.com) writes:
> Although this happened twice on two machines when analyzing the upgrade
> script via profiler -- our latest attempts to isolate what is happening
> ended up not reproducing our earlier results. The indexes are building
> in 2 minutes when tested in isolation outside the script. The only
> operation which we have reliably reproduced as slow is adding a column
> with a default value to a large table - which is taking on the order of
> 20 minutes in the million row range. We do this a number of times.

This sounds like a perfectly normal time for such an operation. Since
this column has to be copied into every row, the entire table has to
be rewritten. And unless every page bas bytes to spare for the new column,
you also get rows rearranged, and it is not a simple update in place.

In this case, it can sometimes be better to create a new table and
copy data over. (This in fact what we always do in our update scripts,
although that more has to do with the greater flexibility this
technique offers.)

> Sorry to have presented you with a problem that didn't reproduce - but
> we were about to make some drastic changes based on the faulty
> assumption that building million row indexes was much more expensive
> then it really is. I appreciate the help you folks have provided!

Oh, never mind! I think your concern was very valid, and I am glad to
have helped by telling you that it must have been a false alarm.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I suggest the real issue is:

> The only operation which we have reliably reproduced as slow is
adding a column
> with a default value to a large table - which is taking on the order
of
> 20 minutes in the million row range. We do this a number of times.

Why would you frequently add columns to a three million row table?

As far as selectivity goes, you don't gain any advantage to having a
default value of 0 vs. having a default value of NULL - it is a
disadvantage because you are frequently adding columns to a 3,000,000
row table. If the default value is NULL, SQL Server does not have to
rebuild the table when you add the value - NULL is nothing as far as
SQL Server is concerned.

The statistics histogram, based on only a select few companies having
this feature, would look something like:
_________________________||||Point well taken regarding NULL vs 0. The reason we use 0 instead of
NULL is because most of the software and reporting is "confused" by
nulls. MFC recordsets return the inconvenient tvalue of

#define AFX_RFX_LONG_PSEUDO_NULL (0x4a4d4120L)

for null integers unless you specifically write code to check for null.
We have a large, old codebase which has no null handling code.

This is a function/service pack, so we commonly add a number of new
fields to support new functionality. In our 3rd test, the upgrade
script took 3 hours - which is in the "normal" range. I guess we are
going to attribute the earlier results as anomalous for now and monitor
for another occurrence. The index is useless for customers not
utilizing the feature, however, if the cost is only 2 minutes, it is
easier to just add the index for all customers. For those customers
using the feature/index, it is highly selective.

Friday, February 24, 2012

Create Index

I need to create an index or two indexes. I just don't know on what columns
to create this indexes. I have a stored procedure that I'm supposed to go by
deciding what indexes to create. Please take a look at this and give me your
recommendations. I appreciate your assistance. Thank you.
James
CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
/**********************************************************************************************************
--STEP-2
--Check for Duplicates within the file. If found remove them.
***********************************************************************************************************/
SET NOCOUNT ON
TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set BBR_Sequence_No = '';
/********************************************************************************
-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO TEMP_BBR_FILE_DUP
*********************************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
SELECT
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType,
DispositionType,BBR_Sequence_No,bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox
FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
GROUP BY
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType,
DispositionType,BBR_Sequence_No,bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox
HAVING COUNT(*)>1
ORDER BY STATETOPS
/**********************************************************************/
-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO BBR_FILE_DUP
****************************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
SELECT
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType, DispositionType, BBR_Sequence_No,
bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/*********************************************************************
-- DELETE FROM THE TABLE NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
ENTRIES
**********************************************************************/
DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A, NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
WHERE A.STATETOPS=B.STATETOPS
AND A.COUNTYTOPS=B.COUNTYTOPS
AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
AND ISNULL(A.DFIRSTNAME,'')=ISNULL(B.DFIRSTNAME,'')
AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
AND ISNULL(A.FILINGTYPE,'')=ISNULL(B.FILINGTYPE,'')
AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL(B.DISPOSITIONTYPE,'')
AND ISNULL(A.MENTALDATE,'')=ISNULL(B.MENTALDATE,'')
AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
AND ISNULL(A.DATASOURCE,'')=ISNULL(B.DATASOURCE,'')
AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
AND ISNULL(A.DSTADDRESS,'')=ISNULL(B.DSTADDRESS,'')
AND ISNULL(A.DAPARTMENT,'')=ISNULL(B.DAPARTMENT,'')
AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
AND ISNULL(A.DALIASLASTNAME,'')=ISNULL(B.DALIASLASTNAME,'')
AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL(B.DALIASFIRSTNAME,'')
AND ISNULL(A.DALIASMIDNAME,'')=ISNULL(B.DALIASMIDNAME,'')
AND ISNULL(A.DALIASSUFFIX,'')=ISNULL(B.DALIASSUFFIX,'')
AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
AND ISNULL(A.CODLASTNAME,'')=ISNULL(B.CODLASTNAME,'')
AND ISNULL(A.CODFIRSTNAME,'')=ISNULL(B.CODFIRSTNAME,'')
AND ISNULL(A.CODMIDNAME,'')=ISNULL(B.CODMIDNAME,'')
AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
AND ISNULL(A.PFIRSTNAME,'')=ISNULL(B.PFIRSTNAME,'')
AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
AND ISNULL(A.PSTADDRESS,'')=ISNULL(B.PSTADDRESS,'')
AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
AND ISNULL(A.COMPLAINTDATE,'')=ISNULL(B.COMPLAINTDATE,'')
AND ISNULL(A.MENTALDATE,'')=ISNULL(B.MENTALDATE,'')
AND ISNULL(A.SATISFIEDDATE,'')=ISNULL(B.SATISFIEDDATE,'')
AND ISNULL(A.DISMISSALDATE,'')=ISNULL(B.DISMISSALDATE,'')
AND ISNULL(A.POSTEDDATE,'')=ISNULL(B.POSTEDDATE,'')
AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
AND ISNULL(A.RESEARCHERNO,'')=ISNULL(B.RESEARCHERNO,'')
AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
/**************************************************************************
--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO DBO.BBR_DAILY_FEED
***************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/********************************************************************************
-- TRUNCATE THE TEMP TABLE
***********************************/
TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/************************************************************
transfer of all unique records
*************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops, countyTops, dlastName,
dfirstName, FilingType, Dispositiontype, mentalDate, amount)
SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim(dlastName)),''),
isnull(ltrim(rtrim(dfirstName)),''), isnull(filingType,''),
isnull(Dispositiontype,''), substring(mentalDate, 1,6) as mentalDate,
ltrim(rtrim(amount)) from NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
/**************************************************************
Select records where just the amount are different
***************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT (stateTops, countyTops,
DLastName, DFirstName, MentalDate, filingType, dispositionType)
SELECT stateTops, CountyTops, isnull(ltrim(rtrim(DLastName)),'') as
DLastName,isnull(ltrim(rtrim(DFirstName)),'') as DFirstName, mentalDate,
isnull(filingType,'') as FilingType, isnull(dispositionType,'') as
dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
GROUP BY stateTops, countyTops, isnull(ltrim(rtrim(DLastName)),''),
isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull(filingType,'') ,
isnull(dispositionType,'')
HAVING COUNT(*) > 1
/**************************************************************************************
Open a Cursor which will delete records from TEMP_BBR_FILE_DUP if that
record is a dup
***************************************************************************************/
exec REMOVE_DUPLICATE_AMOUNTS
/*********************************************************************************
update the remaining fields
**********************************************************************************/
UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
, COURTCODE = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
, DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
, PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
, MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
, COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
, DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
, DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
, DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
, DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
, DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
, DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
, DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
, DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
, DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
, DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLASTNAME
, DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIRSTNAME
, DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNAME
, DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
, CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
, CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
, CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
, CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
, CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
, CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
, PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
, PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
, PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
, PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
, PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
, PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
, PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
, COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDATE
, SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDATE
, DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDATE
, POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
, KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
, RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
, DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
, PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
ON
NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.STATETOPS -- STATETOPS HAS TO BE SAME
AND
NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COUNTYTOPS
AND
ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'') --
HAS TO BE SAME (LOW ERRORS)
AND ISNULL(
LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')=ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
TAKES CARE OF HUSBAND WIFE SCENARIOS
AND
ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'') -- HAS TO BE SAME
AND
ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')=ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION STATUS
AND
ISNULL(SUBSTRING(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL(SUBSTRING(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
DATE OF THE WISE, HAS TO BE SAME
AND ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') = ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
/**********************************************************************************************************************
insert the rejected records into the reject table
***********************************************************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a where NOT EXISTS
(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
isnull(a.caseno,'') = isnull(b.caseno ,'')
and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
and isnull(a.DZIP,'')=isnull(b.DZIP,'')
and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
and isnull(a.DALIASLASTNAME,'')=isnull(b.DALIASLASTNAME,'')
and isnull(a.DALIASFIRSTNAME,'')=isnull(b.DALIASFIRSTNAME,'')
and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
and isnull(a.PCITY,'')=isnull(b.PCITY,'')
and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
and isnull(a.PZIP,'')=isnull(b.PZIP,'')
and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim(rtrim(b.DLASTNAME)),'')
AND
isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim(b.DFIRSTNAME)),'')
AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL(b.DISPOSITIONTYPE,'')
)
/************************************************************************************************************************
remove from staging all the records that are not in DUP table
*************************************************************************************************************************/
DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT * FROM
NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
/***********************************************************************************************************************
clean up
************************************************************************************************************************/
truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
/************************************************************************************
COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG INTO TRANSACTION LOG
**************************************************************************************/
EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
GOHi James,
My first question is what kinda index are you trying to
creates, there are two types, clustered and non clustered,
clustered is faster, but your only allowed and per table.
As for the SQL well thanks but it doesn't really help. The
reason is that index's work best on SELECT statements, the
best canditates being the where clauses, which is not
included in your SQL.
Finally (and here comes the real downer) non clustered
indexes are not really recommended on a table that is
going to primary used as an insert table, the reason being
there is a lot of maths involved how an index works
internally.
However here is the good part, you need not do a thing,
SQL Server can work out which indexes it needs itself.
Here is how to do it.
When you installed SQL Server, you also installed
something called Profiler, start up profiler.
Click File -> New -> Trace and connect to the server where
the db is.
In Trace Name put anything you want.
In Template name put in SQLProfilerTuning
Click save to table and put in a database, table name to
save it to, don't worry about creating the table it will
do it automatically.
Then leave it running for about a week.
Then close it, and open up Enterprise Manager
Select the database the table resides in
Click on Wizards 'Index Tuning Wizards', the rest you can
work out for yourself ;)
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
"That makes soooo knowledgable"
Peter The Spate
>--Original Message--
>I need to create an index or two indexes. I just don't
know on what columns
>to create this indexes. I have a stored procedure that
I'm supposed to go by
>deciding what indexes to create. Please take a look at
this and give me your
>recommendations. I appreciate your assistance. Thank
you.
>James
>CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
>/*********************************************************
*************************************************
>--STEP-2
>--Check for Duplicates within the file. If found remove
them.
>**********************************************************
*************************************************/
>SET NOCOUNT ON
>TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
BBR_Sequence_No = '';
>/*********************************************************
***********************
>-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
TEMP_BBR_FILE_DUP
>**********************************************************
***********************/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>SELECT
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
rNo,DPoBox,PPoBox
>FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>GROUP BY
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
rNo,DPoBox,PPoBox
>HAVING COUNT(*)>1
> ORDER BY STATETOPS
>
>/*********************************************************
*************/
>-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
BBR_FILE_DUP
>**********************************************************
******************************/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>SELECT
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType, DispositionType, BBR_Sequence_No,
>bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
>FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>
>/*********************************************************
************
>-- DELETE FROM THE TABLE
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
>ENTRIES
>**********************************************************
************/
>DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> WHERE A.STATETOPS=B.STATETOPS
> AND A.COUNTYTOPS=B.COUNTYTOPS
> AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
> AND ISNULL(A.DFIRSTNAME,'')=ISNULL
(B.DFIRSTNAME,'')
> AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
> AND ISNULL(A.FILINGTYPE,'')=ISNULL
(B.FILINGTYPE,'')
> AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
(B.DISPOSITIONTYPE,'')
> AND ISNULL(A.MENTALDATE,'')=ISNULL
(B.MENTALDATE,'')
> AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
> AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
> AND ISNULL(A.DATASOURCE,'')=ISNULL
(B.DATASOURCE,'')
> AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> AND ISNULL(A.DSTADDRESS,'')=ISNULL
(B.DSTADDRESS,'')
> AND ISNULL(A.DAPARTMENT,'')=ISNULL
(B.DAPARTMENT,'')
> AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
(B.DALIASLASTNAME,'')
> AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
(B.DALIASFIRSTNAME,'')
> AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
(B.DALIASMIDNAME,'')
> AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
(B.DALIASSUFFIX,'')
> AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> AND ISNULL(A.CODLASTNAME,'')=ISNULL
(B.CODLASTNAME,'')
> AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
(B.CODFIRSTNAME,'')
> AND ISNULL(A.CODMIDNAME,'')=ISNULL
(B.CODMIDNAME,'')
> AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
> AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> AND ISNULL(A.PFIRSTNAME,'')=ISNULL
(B.PFIRSTNAME,'')
> AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> AND ISNULL(A.PSTADDRESS,'')=ISNULL
(B.PSTADDRESS,'')
> AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
(B.COMPLAINTDATE,'')
> AND ISNULL(A.MENTALDATE,'')=ISNULL
(B.MENTALDATE,'')
> AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
(B.SATISFIEDDATE,'')
> AND ISNULL(A.DISMISSALDATE,'')=ISNULL
(B.DISMISSALDATE,'')
> AND ISNULL(A.POSTEDDATE,'')=ISNULL
(B.POSTEDDATE,'')
> AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> AND ISNULL(A.RESEARCHERNO,'')=ISNULL
(B.RESEARCHERNO,'')
> AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
>/*********************************************************
*****************
>--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
DBO.BBR_DAILY_FEED
>**********************************************************
*****************/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>/*********************************************************
***********************
>-- TRUNCATE THE TEMP TABLE
>***********************************/
>TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>/*********************************************************
***
>transfer of all unique records
>**********************************************************
***/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
countyTops, dlastName,
>dfirstName, FilingType, Dispositiontype, mentalDate,
amount)
>SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim
(dlastName)),''),
>isnull(ltrim(rtrim(dfirstName)),''), isnull
(filingType,''),
>isnull(Dispositiontype,''), substring(mentalDate, 1,6) as
mentalDate,
>ltrim(rtrim(amount)) from
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>/*********************************************************
*****
>Select records where just the amount are different
>**********************************************************
*****/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
(stateTops, countyTops,
>DLastName, DFirstName, MentalDate, filingType,
dispositionType)
>SELECT stateTops, CountyTops, isnull(ltrim(rtrim
(DLastName)),'') as
>DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
DFirstName, mentalDate,
>isnull(filingType,'') as FilingType, isnull
(dispositionType,'') as
>dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
(DLastName)),''),
>isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
(filingType,'') ,
>isnull(dispositionType,'')
>HAVING COUNT(*) > 1
>/*********************************************************
*****************************
>Open a Cursor which will delete records from
TEMP_BBR_FILE_DUP if that
>record is a dup
>**********************************************************
*****************************/
>exec REMOVE_DUPLICATE_AMOUNTS
>/*********************************************************
************************
>update the remaining fields
>**********************************************************
************************/
>UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> , COURTCODE =NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> ,
PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> ,
MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> ,
COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> ,
DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> ,
DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> ,
DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> ,
DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> ,
DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> , DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> , DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> ,
DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
NAME
> ,
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
STNAME
> ,
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
ME
> ,
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> ,
CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> ,
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> ,
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> ,
CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> ,
CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> ,
CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> ,
PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> ,
PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> ,
PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> , PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> ,
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
TE
> ,
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
TE
> ,
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
TE
> ,
POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> ,
KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> ,
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> , DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> , PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
>FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>ON
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
ILY_STAGE_BANDS.STATETOPS
-- STATETOPS HAS TO BE SAME
> AND
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
AILY_STAGE_BANDS.COUNTYTOPS
> AND
>ISNULL(LTRIM(RTRIM
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM
(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
--
>HAS TO BE SAME (LOW ERRORS)
> AND ISNULL(
>LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
=ISNULL(LTRIM(RTRIM
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
>TAKES CARE OF HUSBAND WIFE SCENARIOS
> AND
>ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
-- HAS TO BE SAME
> AND
>ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
=ISNULL
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
>IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION
STATUS
> AND
>ISNULL(SUBSTRING
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL
(SUBSTRING
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
>DATE OF THE WISE, HAS TO BE SAME
> AND ISNULL
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') =>ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
>/*********************************************************
***********************************************************
**
>insert the rejected records into the reject table
>**********************************************************
***********************************************************
**/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
where NOT EXISTS
>(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
>isnull(a.caseno,'') = isnull(b.caseno ,'')
>and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
>and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
>and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
>and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
>and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
>and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
>and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
>and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
>and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
>and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
>and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
>and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
>and isnull(a.DZIP,'')=isnull(b.DZIP,'')
>and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
>and isnull(a.DALIASLASTNAME,'')=isnull
(b.DALIASLASTNAME,'')
>and isnull(a.DALIASFIRSTNAME,'')=isnull
(b.DALIASFIRSTNAME,'')
>and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
>and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
>and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
>and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
>and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
>and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
>and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
>and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
>and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
>and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
>and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
>and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
>and isnull(a.PCITY,'')=isnull(b.PCITY,'')
>and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
>and isnull(a.PZIP,'')=isnull(b.PZIP,'')
>and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
>and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
>and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
>and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
>and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
>and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
>and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
>and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
>and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
>and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
>AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
>AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
(rtrim(b.DLASTNAME)),'')
>AND
>isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim
(b.DFIRSTNAME)),'')
>AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
>AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
(b.DISPOSITIONTYPE,'')
>)
>/*********************************************************
***********************************************************
****
>remove from staging all the records that are not in DUP
table
>**********************************************************
***********************************************************
****/
>DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
FROM
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***********************************************************
***
>clean up
>**********************************************************
***********************************************************
***/
>truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
>truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***************************
>COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
INTO TRANSACTION LOG
>**********************************************************
****************************/
>EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
>GO
>.
>|||Peter - I'd love to know what info source you're using for some of the
things you say so that I can try to correct it.
Why do you think that clustered indexes are faster than non-clustered
indexes?
Non-clustered indexes do slow down insert operations (because a record has
to be inserted into the non-clustered index AND the base table), but even if
the table is primarily used for inserts, if you have one query that uses a
different predicate than the key of your clustered index, you're going to
want a non-clustered index on it. Given a sufficiently fast IO subsystem you
should be ok.
The ITW advice is correct.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
> Hi James,
> My first question is what kinda index are you trying to
> creates, there are two types, clustered and non clustered,
> clustered is faster, but your only allowed and per table.
> As for the SQL well thanks but it doesn't really help. The
> reason is that index's work best on SELECT statements, the
> best canditates being the where clauses, which is not
> included in your SQL.
> Finally (and here comes the real downer) non clustered
> indexes are not really recommended on a table that is
> going to primary used as an insert table, the reason being
> there is a lot of maths involved how an index works
> internally.
> However here is the good part, you need not do a thing,
> SQL Server can work out which indexes it needs itself.
> Here is how to do it.
> When you installed SQL Server, you also installed
> something called Profiler, start up profiler.
> Click File -> New -> Trace and connect to the server where
> the db is.
> In Trace Name put anything you want.
> In Template name put in SQLProfilerTuning
> Click save to table and put in a database, table name to
> save it to, don't worry about creating the table it will
> do it automatically.
> Then leave it running for about a week.
> Then close it, and open up Enterprise Manager
> Select the database the table resides in
> Click on Wizards 'Index Tuning Wizards', the rest you can
> work out for yourself ;)
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
> "That makes soooo knowledgable"
> Peter The Spate
>
>
> >--Original Message--
> >I need to create an index or two indexes. I just don't
> know on what columns
> >to create this indexes. I have a stored procedure that
> I'm supposed to go by
> >deciding what indexes to create. Please take a look at
> this and give me your
> >recommendations. I appreciate your assistance. Thank
> you.
> >James
> >
> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
> >/*********************************************************
> *************************************************
> >--STEP-2
> >--Check for Duplicates within the file. If found remove
> them.
> >**********************************************************
> *************************************************/
> >
> >SET NOCOUNT ON
> >
> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
> BBR_Sequence_No = '';
> >/*********************************************************
> ***********************
> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> TEMP_BBR_FILE_DUP
> >**********************************************************
> ***********************/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >SELECT
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType,
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> rNo,DPoBox,PPoBox
> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >GROUP BY
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType,
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> rNo,DPoBox,PPoBox
> >HAVING COUNT(*)>1
> > ORDER BY STATETOPS
> >
> >
> >/*********************************************************
> *************/
> >
> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> BBR_FILE_DUP
> >**********************************************************
> ******************************/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >SELECT
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType, DispositionType, BBR_Sequence_No,
> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >
> >/*********************************************************
> ************
> >-- DELETE FROM THE TABLE
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
> >ENTRIES
> >**********************************************************
> ************/
> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> > WHERE A.STATETOPS=B.STATETOPS
> > AND A.COUNTYTOPS=B.COUNTYTOPS
> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> > AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
> (B.DFIRSTNAME,'')
> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> > AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
> (B.FILINGTYPE,'')
> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
> (B.DISPOSITIONTYPE,'')
> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> (B.MENTALDATE,'')
> > AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
> > AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
> > AND ISNULL(A.DATASOURCE,'')=ISNULL
> (B.DATASOURCE,'')
> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
> (B.DSTADDRESS,'')
> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
> (B.DAPARTMENT,'')
> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
> (B.DALIASLASTNAME,'')
> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
> (B.DALIASFIRSTNAME,'')
> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
> (B.DALIASMIDNAME,'')
> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
> (B.DALIASSUFFIX,'')
> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
> (B.CODLASTNAME,'')
> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
> (B.CODFIRSTNAME,'')
> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
> (B.CODMIDNAME,'')
> > AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
> (B.PFIRSTNAME,'')
> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
> (B.PSTADDRESS,'')
> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
> (B.COMPLAINTDATE,'')
> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> (B.MENTALDATE,'')
> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
> (B.SATISFIEDDATE,'')
> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
> (B.DISMISSALDATE,'')
> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
> (B.POSTEDDATE,'')
> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
> (B.RESEARCHERNO,'')
> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
> >
> >/*********************************************************
> *****************
> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
> DBO.BBR_DAILY_FEED
> >**********************************************************
> *****************/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >/*********************************************************
> ***********************
> >-- TRUNCATE THE TEMP TABLE
> >***********************************/
> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >/*********************************************************
> ***
> >transfer of all unique records
> >**********************************************************
> ***/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
> countyTops, dlastName,
> >dfirstName, FilingType, Dispositiontype, mentalDate,
> amount)
> >SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim
> (dlastName)),''),
> >isnull(ltrim(rtrim(dfirstName)),''), isnull
> (filingType,''),
> >isnull(Dispositiontype,''), substring(mentalDate, 1,6) as
> mentalDate,
> >ltrim(rtrim(amount)) from
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >
> >/*********************************************************
> *****
> >Select records where just the amount are different
> >**********************************************************
> *****/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
> (stateTops, countyTops,
> >DLastName, DFirstName, MentalDate, filingType,
> dispositionType)
> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
> (DLastName)),'') as
> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
> DFirstName, mentalDate,
> >isnull(filingType,'') as FilingType, isnull
> (dispositionType,'') as
> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
> (DLastName)),''),
> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
> (filingType,'') ,
> >isnull(dispositionType,'')
> >HAVING COUNT(*) > 1
> >
> >/*********************************************************
> *****************************
> >Open a Cursor which will delete records from
> TEMP_BBR_FILE_DUP if that
> >record is a dup
> >**********************************************************
> *****************************/
> >exec REMOVE_DUPLICATE_AMOUNTS
> >
> >/*********************************************************
> ************************
> >update the remaining fields
> >**********************************************************
> ************************/
> >
> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> > , COURTCODE => NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> > ,
> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> > ,
> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> > ,
> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> > ,
> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> > ,
> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> > ,
> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> > ,
> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> > ,
> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> > , DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> > , DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> > ,
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
> NAME
> > ,
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
> STNAME
> > ,
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
> ME
> > ,
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> > ,
> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> > ,
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> > ,
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> > ,
> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> > ,
> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> > ,
> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> > ,
> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> > ,
> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> > ,
> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> > , PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> > ,
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
> TE
> > ,
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
> TE
> > ,
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
> TE
> > ,
> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> > ,
> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> > ,
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> > , DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> > , PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >ON
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
> ILY_STAGE_BANDS.STATETOPS
> -- STATETOPS HAS TO BE SAME
> > AND
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
> AILY_STAGE_BANDS.COUNTYTOPS
> > AND
> >ISNULL(LTRIM(RTRIM
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM
> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
> --
> >HAS TO BE SAME (LOW ERRORS)
> > AND ISNULL(
> >LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
> =ISNULL(LTRIM(RTRIM
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
> >TAKES CARE OF HUSBAND WIFE SCENARIOS
> > AND
> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
> -- HAS TO BE SAME
> > AND
> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
> =ISNULL
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
> >IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION
> STATUS
> > AND
> >ISNULL(SUBSTRING
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL
> (SUBSTRING
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
> >DATE OF THE WISE, HAS TO BE SAME
> > AND ISNULL
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') => >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
> >
> >/*********************************************************
> ***********************************************************
> **
> >insert the rejected records into the reject table
> >**********************************************************
> ***********************************************************
> **/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
> where NOT EXISTS
> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
> >isnull(a.caseno,'') = isnull(b.caseno ,'')
> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
> >and isnull(a.DALIASLASTNAME,'')=isnull
> (b.DALIASLASTNAME,'')
> >and isnull(a.DALIASFIRSTNAME,'')=isnull
> (b.DALIASFIRSTNAME,'')
> >and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
> >and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
> >and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
> >and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
> (rtrim(b.DLASTNAME)),'')
> >AND
> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim
> (b.DFIRSTNAME)),'')
> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
> (b.DISPOSITIONTYPE,'')
> >)
> >
> >/*********************************************************
> ***********************************************************
> ****
> >remove from staging all the records that are not in DUP
> table
> >**********************************************************
> ***********************************************************
> ****/
> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
> FROM
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >/*********************************************************
> ***********************************************************
> ***
> >clean up
> >**********************************************************
> ***********************************************************
> ***/
> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >
> >/*********************************************************
> ***************************
> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
> INTO TRANSACTION LOG
> >**********************************************************
> ****************************/
> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
> >GO
> >
> >.
> >|||The Book Inside Microsoft SQL Server by Karen Delany,
chapter 8, page 406 quite 'The query optimizer strongly
favours a clustered index because such an index allows the
data to be found directly at the leaf level'.
The Book SQL Server Performance tuning - Page 343
Quote 'The index itself (non clustered) is completely
sepatate from the data, like a book with an index at the
back'
Therefore am clusted index points directly to a page where
as a non clustered does not.
Now I have given you my source will you give yours ?
Peter
>--Original Message--
>Peter - I'd love to know what info source you're using
for some of the
>things you say so that I can try to correct it.
>Why do you think that clustered indexes are faster than
non-clustered
>indexes?
>Non-clustered indexes do slow down insert operations
(because a record has
>to be inserted into the non-clustered index AND the base
table), but even if
>the table is primarily used for inserts, if you have one
query that uses a
>different predicate than the key of your clustered index,
you're going to
>want a non-clustered index on it. Given a sufficiently
fast IO subsystem you
>should be ok.
>The ITW advice is correct.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
>> Hi James,
>> My first question is what kinda index are you trying to
>> creates, there are two types, clustered and non
clustered,
>> clustered is faster, but your only allowed and per
table.
>> As for the SQL well thanks but it doesn't really help.
The
>> reason is that index's work best on SELECT statements,
the
>> best canditates being the where clauses, which is not
>> included in your SQL.
>> Finally (and here comes the real downer) non clustered
>> indexes are not really recommended on a table that is
>> going to primary used as an insert table, the reason
being
>> there is a lot of maths involved how an index works
>> internally.
>> However here is the good part, you need not do a thing,
>> SQL Server can work out which indexes it needs itself.
>> Here is how to do it.
>> When you installed SQL Server, you also installed
>> something called Profiler, start up profiler.
>> Click File -> New -> Trace and connect to the server
where
>> the db is.
>> In Trace Name put anything you want.
>> In Template name put in SQLProfilerTuning
>> Click save to table and put in a database, table name to
>> save it to, don't worry about creating the table it will
>> do it automatically.
>> Then leave it running for about a week.
>> Then close it, and open up Enterprise Manager
>> Select the database the table resides in
>> Click on Wizards 'Index Tuning Wizards', the rest you
can
>> work out for yourself ;)
>> Peter
>> "Real knowledge is to know the extent of one's
ignorance."
>> Confucius
>> "That makes soooo knowledgable"
>> Peter The Spate
>>
>>
>> >--Original Message--
>> >I need to create an index or two indexes. I just don't
>> know on what columns
>> >to create this indexes. I have a stored procedure that
>> I'm supposed to go by
>> >deciding what indexes to create. Please take a look at
>> this and give me your
>> >recommendations. I appreciate your assistance. Thank
>> you.
>> >James
>> >
>> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
>/*********************************************************
>> *************************************************
>> >--STEP-2
>> >--Check for Duplicates within the file. If found remove
>> them.
>**********************************************************
>> *************************************************/
>> >
>> >SET NOCOUNT ON
>> >
>> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
>> BBR_Sequence_No = '';
>/*********************************************************
>> ***********************
>> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
>> TEMP_BBR_FILE_DUP
>**********************************************************
>> ***********************/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >SELECT
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
>> rNo,DPoBox,PPoBox
>> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >GROUP BY
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
>> rNo,DPoBox,PPoBox
>> >HAVING COUNT(*)>1
>> > ORDER BY STATETOPS
>> >
>> >
>/*********************************************************
>> *************/
>> >
>> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
>> BBR_FILE_DUP
>**********************************************************
>> ******************************/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>> >SELECT
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType, DispositionType,
BBR_Sequence_No,
>> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
>> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>> >
>/*********************************************************
>> ************
>> >-- DELETE FROM THE TABLE
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
>> >ENTRIES
>**********************************************************
>> ************/
>> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
>> > WHERE A.STATETOPS=B.STATETOPS
>> > AND A.COUNTYTOPS=B.COUNTYTOPS
>> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
>> > AND ISNULL(A.DLASTNAME,'')=ISNULL
(B.DLASTNAME,'')
>> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
>> (B.DFIRSTNAME,'')
>> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
>> > AND ISNULL(A.PLASTNAME,'')=ISNULL
(B.PLASTNAME,'')
>> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
>> (B.FILINGTYPE,'')
>> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
>> (B.DISPOSITIONTYPE,'')
>> > AND ISNULL(A.MENTALDATE,'')=ISNULL
>> (B.MENTALDATE,'')
>> > AND ISNULL(A.COURTCODE,'')=ISNULL
(B.COURTCODE,'')
>> > AND ISNULL(A.COURTTYPE,'')=ISNULL
(B.COURTTYPE,'')
>> > AND ISNULL(A.DATASOURCE,'')=ISNULL
>> (B.DATASOURCE,'')
>> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
>> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
>> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
>> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
>> (B.DSTADDRESS,'')
>> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
>> (B.DAPARTMENT,'')
>> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
>> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
>> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
>> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
>> (B.DALIASLASTNAME,'')
>> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
>> (B.DALIASFIRSTNAME,'')
>> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
>> (B.DALIASMIDNAME,'')
>> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
>> (B.DALIASSUFFIX,'')
>> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
>> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
>> (B.CODLASTNAME,'')
>> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
>> (B.CODFIRSTNAME,'')
>> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
>> (B.CODMIDNAME,'')
>> > AND ISNULL(A.CODSUFFIX,'')=ISNULL
(B.CODSUFFIX,'')
>> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
>> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
>> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
>> (B.PFIRSTNAME,'')
>> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
>> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
>> (B.PSTADDRESS,'')
>> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
>> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
>> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
>> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
>> (B.COMPLAINTDATE,'')
>> > AND ISNULL(A.MENTALDATE,'')=ISNULL
>> (B.MENTALDATE,'')
>> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
>> (B.SATISFIEDDATE,'')
>> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
>> (B.DISMISSALDATE,'')
>> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
>> (B.POSTEDDATE,'')
>> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
>> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
>> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
>> (B.RESEARCHERNO,'')
>> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
>> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
>> >
>/*********************************************************
>> *****************
>> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
>> DBO.BBR_DAILY_FEED
>**********************************************************
>> *****************/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>/*********************************************************
>> ***********************
>> >-- TRUNCATE THE TEMP TABLE
>> >***********************************/
>> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>/*********************************************************
>> ***
>> >transfer of all unique records
>**********************************************************
>> ***/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
>> countyTops, dlastName,
>> >dfirstName, FilingType, Dispositiontype, mentalDate,
>> amount)
>> >SELECT distinct stateTops, countyTops, isnull(ltrim
(rtrim
>> (dlastName)),''),
>> >isnull(ltrim(rtrim(dfirstName)),''), isnull
>> (filingType,''),
>> >isnull(Dispositiontype,''), substring(mentalDate, 1,6)
as
>> mentalDate,
>> >ltrim(rtrim(amount)) from
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >
>/*********************************************************
>> *****
>> >Select records where just the amount are different
>**********************************************************
>> *****/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
>> (stateTops, countyTops,
>> >DLastName, DFirstName, MentalDate, filingType,
>> dispositionType)
>> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
>> (DLastName)),'') as
>> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
>> DFirstName, mentalDate,
>> >isnull(filingType,'') as FilingType, isnull
>> (dispositionType,'') as
>> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
>> (DLastName)),''),
>> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
>> (filingType,'') ,
>> >isnull(dispositionType,'')
>> >HAVING COUNT(*) > 1
>> >
>/*********************************************************
>> *****************************
>> >Open a Cursor which will delete records from
>> TEMP_BBR_FILE_DUP if that
>> >record is a dup
>**********************************************************
>> *****************************/
>> >exec REMOVE_DUPLICATE_AMOUNTS
>> >
>/*********************************************************
>> ************************
>> >update the remaining fields
>**********************************************************
>> ************************/
>> >
>> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
>> > , COURTCODE =>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
>> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
>> > ,
>> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
>> > ,
>> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
>> > ,
>> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
>> > ,
>> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
>> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
>> > ,
>> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
>> > ,
>> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
>> > ,
>> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
>> > ,
>> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
>> > ,
DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
>> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
>> > ,
DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
>> > ,
DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
>> NAME
>> > ,
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
>> STNAME
>> > ,
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
>> ME
>> > ,
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
>> > ,
>> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
>> > ,
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
>> > ,
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
>> > ,
>> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
>> > ,
>> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
>> > ,
>> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
>> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
>> > ,
>> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
>> > ,
>> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
>> > ,
>> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
>> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
>> > ,
PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
>> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
>> > ,
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
>> TE
>> > ,
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
>> TE
>> > ,
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
>> TE
>> > ,
>> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
>> > ,
>> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
>> > ,
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
>> > ,
DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
>> > ,
PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
>> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >ON
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
>> ILY_STAGE_BANDS.STATETOPS
>> -- STATETOPS HAS TO BE SAME
>> > AND
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
>> AILY_STAGE_BANDS.COUNTYTOPS
>> > AND
>> >ISNULL(LTRIM(RTRIM
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL
(LTRIM
>> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
>> --
>> >HAS TO BE SAME (LOW ERRORS)
>> > AND ISNULL(
>> >LTRIM(RTRIM
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
>> =ISNULL(LTRIM(RTRIM
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
>> >TAKES CARE OF HUSBAND WIFE SCENARIOS
>> > AND
>> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')
=ISNULL
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
>> -- HAS TO BE SAME
>> > AND
>> >ISNULL
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
>> =ISNULL
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') -
-
>> >IN CASES WHERE THE COMPARISION IS BETWEEN A
SATISFACTION
>> STATUS
>> > AND
>> >ISNULL(SUBSTRING
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')
=ISNULL
>> (SUBSTRING
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') -
-
>> >DATE OF THE WISE, HAS TO BE SAME
>> > AND ISNULL
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') =>> >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
>> >
>/*********************************************************
***********************************************************
>> **
>> >insert the rejected records into the reject table
>**********************************************************
***********************************************************
>> **/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
>> where NOT EXISTS
>> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
>> >isnull(a.caseno,'') = isnull(b.caseno ,'')
>> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
>> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
>> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
>> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
>> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
>> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
>> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
>> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
>> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
>> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
>> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
>> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
>> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
>> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
>> >and isnull(a.DALIASLASTNAME,'')=isnull
>> (b.DALIASLASTNAME,'')
>> >and isnull(a.DALIASFIRSTNAME,'')=isnull
>> (b.DALIASFIRSTNAME,'')
>> >and isnull(a.DALIASMIDNAME,'')=isnull
(b.DALIASMIDNAME,'')
>> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
>> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
>> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
>> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
>> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
>> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
>> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
>> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
>> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
>> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
>> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
>> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
>> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
>> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
>> >and isnull(a.COMPLAINTDATE,'')=isnull
(b.COMPLAINTDATE,'')
>> >and isnull(a.SATISFIEDDATE,'')=isnull
(b.SATISFIEDDATE,'')
>> >and isnull(a.DISMISSALDATE,'')=isnull
(b.DISMISSALDATE,'')
>> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
>> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
>> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
>> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
>> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
>> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
>> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
>> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
>> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
>> (rtrim(b.DLASTNAME)),'')
>> >AND
>> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim
(rtrim
>> (b.DFIRSTNAME)),'')
>> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
>> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
>> (b.DISPOSITIONTYPE,'')
>> >)
>> >
>/*********************************************************
***********************************************************
>> ****
>> >remove from staging all the records that are not in DUP
>> table
>**********************************************************
***********************************************************
>> ****/
>> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
>> FROM
>> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***********************************************************
>> ***
>> >clean up
>**********************************************************
***********************************************************
>> ***/
>> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
>> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>> >
>/*********************************************************
>> ***************************
>> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
>> INTO TRANSACTION LOG
>**********************************************************
>> ****************************/
>> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
>> >GO
>> >
>> >.
>> >
>
>.
>|||My source is the SQL Server Storage Engine code - look at my signature
below.
The context of Kalen's point implies that the clustered index is covering
the query you're doing. Non-clustered indexes exist to cover queries that
aren't covered by the clustered index. Whether one is faster than the other
depends on the query and the density of the index rows on the page. Bigger
rows imply a deeper index and so seeks to the leaf level will have to
traverse more intermediate levels of the index - there are many factors to
consider. Simply saying that a clustered index is faster than a
non-clustered index is very misleading.
The second quote is again very misleading taken out of context. Yes, a
non-clustered index is separate from the data - that's the whole point. Just
because the whole data row exists in the clustered index doesn't mean you
have to retrieve it every time you want some of the columns from it. If the
keys of a non-clustered index cover the query, there's no need to go to the
clustered index at all - basic index theory.
If all you want to do is select ranges of your data based on a single key, a
clustered index will be faster and always used by the optimizer. For
anything more complex, non-clustered indexes are usually involved - that's
why they exist. Ask any DBA or DB app designer.
Best regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d0801c514d5$8edf36f0$a401280a@.phx.gbl...
> The Book Inside Microsoft SQL Server by Karen Delany,
> chapter 8, page 406 quite 'The query optimizer strongly
> favours a clustered index because such an index allows the
> data to be found directly at the leaf level'.
> The Book SQL Server Performance tuning - Page 343
> Quote 'The index itself (non clustered) is completely
> sepatate from the data, like a book with an index at the
> back'
> Therefore am clusted index points directly to a page where
> as a non clustered does not.
> Now I have given you my source will you give yours ?
> Peter
>
> >--Original Message--
> >Peter - I'd love to know what info source you're using
> for some of the
> >things you say so that I can try to correct it.
> >
> >Why do you think that clustered indexes are faster than
> non-clustered
> >indexes?
> >
> >Non-clustered indexes do slow down insert operations
> (because a record has
> >to be inserted into the non-clustered index AND the base
> table), but even if
> >the table is primarily used for inserts, if you have one
> query that uses a
> >different predicate than the key of your clustered index,
> you're going to
> >want a non-clustered index on it. Given a sufficiently
> fast IO subsystem you
> >should be ok.
> >
> >The ITW advice is correct.
> >
> >Regards
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
> >> Hi James,
> >>
> >> My first question is what kinda index are you trying to
> >> creates, there are two types, clustered and non
> clustered,
> >> clustered is faster, but your only allowed and per
> table.
> >>
> >> As for the SQL well thanks but it doesn't really help.
> The
> >> reason is that index's work best on SELECT statements,
> the
> >> best canditates being the where clauses, which is not
> >> included in your SQL.
> >>
> >> Finally (and here comes the real downer) non clustered
> >> indexes are not really recommended on a table that is
> >> going to primary used as an insert table, the reason
> being
> >> there is a lot of maths involved how an index works
> >> internally.
> >>
> >> However here is the good part, you need not do a thing,
> >> SQL Server can work out which indexes it needs itself.
> >> Here is how to do it.
> >>
> >> When you installed SQL Server, you also installed
> >> something called Profiler, start up profiler.
> >>
> >> Click File -> New -> Trace and connect to the server
> where
> >> the db is.
> >>
> >> In Trace Name put anything you want.
> >> In Template name put in SQLProfilerTuning
> >> Click save to table and put in a database, table name to
> >> save it to, don't worry about creating the table it will
> >> do it automatically.
> >>
> >> Then leave it running for about a week.
> >>
> >> Then close it, and open up Enterprise Manager
> >> Select the database the table resides in
> >> Click on Wizards 'Index Tuning Wizards', the rest you
> can
> >> work out for yourself ;)
> >>
> >> Peter
> >>
> >> "Real knowledge is to know the extent of one's
> ignorance."
> >> Confucius
> >>
> >> "That makes soooo knowledgable"
> >> Peter The Spate
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I need to create an index or two indexes. I just don't
> >> know on what columns
> >> >to create this indexes. I have a stored procedure that
> >> I'm supposed to go by
> >> >deciding what indexes to create. Please take a look at
> >> this and give me your
> >> >recommendations. I appreciate your assistance. Thank
> >> you.
> >> >James
> >> >
> >> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
> >>
> >/*********************************************************
> >> *************************************************
> >> >--STEP-2
> >> >--Check for Duplicates within the file. If found remove
> >> them.
> >>
> >**********************************************************
> >> *************************************************/
> >> >
> >> >SET NOCOUNT ON
> >> >
> >> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
> >> BBR_Sequence_No = '';
> >>
> >/*********************************************************
> >> ***********************
> >> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> >> TEMP_BBR_FILE_DUP
> >>
> >**********************************************************
> >> ***********************/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >SELECT
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType,
> >>
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> >> rNo,DPoBox,PPoBox
> >> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >GROUP BY
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType,
> >>
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> >> rNo,DPoBox,PPoBox
> >> >HAVING COUNT(*)>1
> >> > ORDER BY STATETOPS
> >> >
> >> >
> >>
> >/*********************************************************
> >> *************/
> >> >
> >> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> >> BBR_FILE_DUP
> >>
> >**********************************************************
> >> ******************************/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >> >SELECT
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType, DispositionType,
> BBR_Sequence_No,
> >> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
> >> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >> >
> >>
> >/*********************************************************
> >> ************
> >> >-- DELETE FROM THE TABLE
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
> >> >ENTRIES
> >>
> >**********************************************************
> >> ************/
> >> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> >> > WHERE A.STATETOPS=B.STATETOPS
> >> > AND A.COUNTYTOPS=B.COUNTYTOPS
> >> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> >> > AND ISNULL(A.DLASTNAME,'')=ISNULL
> (B.DLASTNAME,'')
> >> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
> >> (B.DFIRSTNAME,'')
> >> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> >> > AND ISNULL(A.PLASTNAME,'')=ISNULL
> (B.PLASTNAME,'')
> >> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
> >> (B.FILINGTYPE,'')
> >> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
> >> (B.DISPOSITIONTYPE,'')
> >> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> >> (B.MENTALDATE,'')
> >> > AND ISNULL(A.COURTCODE,'')=ISNULL
> (B.COURTCODE,'')
> >> > AND ISNULL(A.COURTTYPE,'')=ISNULL
> (B.COURTTYPE,'')
> >> > AND ISNULL(A.DATASOURCE,'')=ISNULL
> >> (B.DATASOURCE,'')
> >> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> >> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> >> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> >> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
> >> (B.DSTADDRESS,'')
> >> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
> >> (B.DAPARTMENT,'')
> >> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> >> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> >> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> >> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
> >> (B.DALIASLASTNAME,'')
> >> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
> >> (B.DALIASFIRSTNAME,'')
> >> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
> >> (B.DALIASMIDNAME,'')
> >> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
> >> (B.DALIASSUFFIX,'')
> >> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> >> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
> >> (B.CODLASTNAME,'')
> >> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
> >> (B.CODFIRSTNAME,'')
> >> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
> >> (B.CODMIDNAME,'')
> >> > AND ISNULL(A.CODSUFFIX,'')=ISNULL
> (B.CODSUFFIX,'')
> >> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> >> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> >> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
> >> (B.PFIRSTNAME,'')
> >> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> >> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
> >> (B.PSTADDRESS,'')
> >> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> >> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> >> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> >> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
> >> (B.COMPLAINTDATE,'')
> >> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> >> (B.MENTALDATE,'')
> >> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
> >> (B.SATISFIEDDATE,'')
> >> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
> >> (B.DISMISSALDATE,'')
> >> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
> >> (B.POSTEDDATE,'')
> >> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> >> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> >> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
> >> (B.RESEARCHERNO,'')
> >> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> >> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
> >> >
> >>
> >/*********************************************************
> >> *****************
> >> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
> >> DBO.BBR_DAILY_FEED
> >>
> >**********************************************************
> >> *****************/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >>
> >/*********************************************************
> >> ***********************
> >> >-- TRUNCATE THE TEMP TABLE
> >> >***********************************/
> >> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >>
> >/*********************************************************
> >> ***
> >> >transfer of all unique records
> >>
> >**********************************************************
> >> ***/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
> >> countyTops, dlastName,
> >> >dfirstName, FilingType, Dispositiontype, mentalDate,
> >> amount)
> >> >SELECT distinct stateTops, countyTops, isnull(ltrim
> (rtrim
> >> (dlastName)),''),
> >> >isnull(ltrim(rtrim(dfirstName)),''), isnull
> >> (filingType,''),
> >> >isnull(Dispositiontype,''), substring(mentalDate, 1,6)
> as
> >> mentalDate,
> >> >ltrim(rtrim(amount)) from
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >
> >>
> >/*********************************************************
> >> *****
> >> >Select records where just the amount are different
> >>
> >**********************************************************
> >> *****/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
> >> (stateTops, countyTops,
> >> >DLastName, DFirstName, MentalDate, filingType,
> >> dispositionType)
> >> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
> >> (DLastName)),'') as
> >> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
> >> DFirstName, mentalDate,
> >> >isnull(filingType,'') as FilingType, isnull
> >> (dispositionType,'') as
> >> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
> >> (DLastName)),''),
> >> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
> >> (filingType,'') ,
> >> >isnull(dispositionType,'')
> >> >HAVING COUNT(*) > 1
> >> >
> >>
> >/*********************************************************
> >> *****************************
> >> >Open a Cursor which will delete records from
> >> TEMP_BBR_FILE_DUP if that
> >> >record is a dup
> >>
> >**********************************************************
> >> *****************************/
> >> >exec REMOVE_DUPLICATE_AMOUNTS
> >> >
> >>
> >/*********************************************************
> >> ************************
> >> >update the remaining fields
> >>
> >**********************************************************
> >> ************************/
> >> >
> >> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> >> > , COURTCODE => >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> >> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> >> > ,
> >> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> >> > ,
> >> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> >> > ,
> >> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> >> > ,
> >> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> >> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> >> > ,
> >> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> >> > ,
> >> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> >> > ,
> >> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> >> > ,
> >> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> >> > ,
> DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> >> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> >> > ,
> DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> >> > ,
> >>
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
> >> NAME
> >> > ,
> >>
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
> >> STNAME
> >> > ,
> >>
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
> >> ME
> >> > ,
> >>
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> >> > ,
> >> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> >> > ,
> >>
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> >> > ,
> >>
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> >> > ,
> >> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> >> > ,
> >> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> >> > ,
> >> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> >> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> >> > ,
> >> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> >> > ,
> >> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> >> > ,
> >> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> >> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> >> > ,
> PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> >> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> >> > ,
> >>
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
> >> TE
> >> > ,
> >>
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
> >> TE
> >> > ,
> >>
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
> >> TE
> >> > ,
> >> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> >> > ,
> >> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> >> > ,
> >>
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> >> > ,
> DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> >> > ,
> PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
> >> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >ON
> >>
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
> >> ILY_STAGE_BANDS.STATETOPS
> >> -- STATETOPS HAS TO BE SAME
> >> > AND
> >>
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
> >> AILY_STAGE_BANDS.COUNTYTOPS
> >> > AND
> >> >ISNULL(LTRIM(RTRIM
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL
> (LTRIM
> >> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
> >> --
> >> >HAS TO BE SAME (LOW ERRORS)
> >> > AND ISNULL(
> >> >LTRIM(RTRIM
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
> >> =ISNULL(LTRIM(RTRIM
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
> >> >TAKES CARE OF HUSBAND WIFE SCENARIOS
> >> > AND
> >> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')
> =ISNULL
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
> >> -- HAS TO BE SAME
> >> > AND
> >> >ISNULL
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
> >> =ISNULL
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') -
> -
> >> >IN CASES WHERE THE COMPARISION IS BETWEEN A
> SATISFACTION
> >> STATUS
> >> > AND
> >> >ISNULL(SUBSTRING
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')
> =ISNULL
> >> (SUBSTRING
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') -
> -
> >> >DATE OF THE WISE, HAS TO BE SAME
> >> > AND ISNULL
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') => >> >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
> >> >
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> **
> >> >insert the rejected records into the reject table
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> **/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
> >> where NOT EXISTS
> >> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
> >> >isnull(a.caseno,'') = isnull(b.caseno ,'')
> >> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
> >> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
> >> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
> >> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
> >> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
> >> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
> >> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
> >> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
> >> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
> >> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
> >> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
> >> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
> >> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
> >> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
> >> >and isnull(a.DALIASLASTNAME,'')=isnull
> >> (b.DALIASLASTNAME,'')
> >> >and isnull(a.DALIASFIRSTNAME,'')=isnull
> >> (b.DALIASFIRSTNAME,'')
> >> >and isnull(a.DALIASMIDNAME,'')=isnull
> (b.DALIASMIDNAME,'')
> >> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
> >> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
> >> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
> >> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
> >> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
> >> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
> >> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
> >> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
> >> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
> >> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
> >> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
> >> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
> >> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
> >> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
> >> >and isnull(a.COMPLAINTDATE,'')=isnull
> (b.COMPLAINTDATE,'')
> >> >and isnull(a.SATISFIEDDATE,'')=isnull
> (b.SATISFIEDDATE,'')
> >> >and isnull(a.DISMISSALDATE,'')=isnull
> (b.DISMISSALDATE,'')
> >> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
> >> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
> >> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
> >> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
> >> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
> >> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
> >> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
> >>
> >> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
> >> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
> >> (rtrim(b.DLASTNAME)),'')
> >> >AND
> >> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim
> (rtrim
> >> (b.DFIRSTNAME)),'')
> >> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
> >>
> >> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
> >> (b.DISPOSITIONTYPE,'')
> >> >)
> >> >
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> ****
> >> >remove from staging all the records that are not in DUP
> >> table
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> ****/
> >> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
> >> FROM
> >> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> ***
> >> >clean up
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> ***/
> >> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
> >> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >> >
> >>
> >/*********************************************************
> >> ***************************
> >> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
> >> INTO TRANSACTION LOG
> >>
> >**********************************************************
> >> ****************************/
> >> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
> >> >GO
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Sunday, March 11, 2012

Create only unique indexes

Create only unique indexes

Create only unique indexes

Thursday, March 8, 2012

create only sp's during schema initialization

Saturday, February 25, 2012

Create indexes for Query Optimization

Create indexes for Query Optimization

Create Indexes & extra columns!

Create Indexes & extra columns!

Create Indexes & extra columns!

Create indexes - own File Group

Create Index option in the table designer

create index on table

CREATE INDEX on large table

Friday, February 24, 2012

Create Index

Sql Create error

Blog Archive

About Me