I am brand new to SQL Server. I had to move my data tables from Access 2003
onto SQL Server 2000. I have just upsized my .mdb using the upsizing wizard
and, after some minor changes, have gotten everything to work.
Now, I'm trying to focus on speeding things up. I have several nested
queries, using many tables, with several IIF statements that are used just
for selecting data to be displayed on forms and reports. I attempted to
convert one into a view, but have discovered that views don't allow IIF
statements. Is there a better way to do this? Can you create a procedure tha
t
is linked to Access? How do you reference this in Access?(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||Thanks for the speedy response. Another question: If I create my view using
query analyzer, how do I save it as a query to link to Access?|||If you have a view in SQL Server, like:
CREATE VIEW dbo.MyView
AS
SELECT 1;
Then from Access you can just treat it like a table, e.g. SELECT * FROM
dbo.MyView instead of SELECT * FROM dbo.MyTable.
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:D6BF7042-48B7-4FC0-9EE2-D66026A461D7@.microsoft.com...
> Thanks for the speedy response. Another question: If I create my view
> using
> query analyzer, how do I save it as a query to link to Access?|||Sorry, I'm not following you.
Ok, using SQL Query Analyzer, I created my Select statement using the case
statements instead of IIF's, and parsing completed successfully. It runs and
selects the right information. Now what?
Do I have to save this somewhere special? Then what.|||You do not save it somewhere else. View is a server object, meaning it has
to be created on the SQL Server database (and saved there after creating
it).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:C8C7BE9D-70BD-4BB3-BCA5-7D2ABFD4C3D2@.microsoft.com...
> Sorry, I'm not following you.
> Ok, using SQL Query Analyzer, I created my Select statement using the case
> statements instead of IIF's, and parsing completed successfully. It runs
> and
> selects the right information. Now what?
> Do I have to save this somewhere special? Then what.|||Hello
You can use a CASE expression instead of the IIF, using one of these
syntaxes:
CASE
WHEN condition
THEN result_if_true
ELSE result_if_false
END
or:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
..
ELSE other_result
END
For more informations about CASE, see Books Online.
Razvan|||Use Query Analyzer(QA) to transfer ALL the old data from Access to SQL.
Copy it to tables - or better yet use DTS package to copy entire database.
If you are using a view to call the data for reports, use QA to test and
create a stored procedure with the test script. Call the stored procedure
from your reports.
If you want to use the QA script on occasion and not for any so-called
recurring reports, then save the script written in QA to any location (just
liek a text script) and open up the script when you want to run it and run i
t
at will.
"Holly" wrote:
> Thanks for the speedy response. Another question: If I create my view usin
g
> query analyzer, how do I save it as a query to link to Access?|||On Thu, 1 Jun 2006 11:26:01 -0700, Holly wrote:
>Sorry, I'm not following you.
>Ok, using SQL Query Analyzer, I created my Select statement using the case
>statements instead of IIF's, and parsing completed successfully. It runs an
d
>selects the right information. Now what?
>Do I have to save this somewhere special? Then what.
Hi Holly,
If you have a SELECT statement that returns the data you need, e.g.
SELECT au_fname FROM authors
than you can create a view by typing a CREATE VIEW statement before it
and executing the complete code:
CREATE VIEW Author_FirstNames
AS
SELECT au_fname FROM authors
Once this has executed successfullym you can use the Authro_FirstName
view just as you would use any regular table - and that includes
creating a linked table for it in Access.
Hugo Kornelis, SQL Server MVP
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
convert a non-partitioned tables into partitioned Options
Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
[url]http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm[/url]
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
[url]http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm[/url]
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>
convert a non-partitioned tables into partitioned Options
Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql
convert a non-partitioned tables into partitioned Options
Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/...20Beta%20II.htm
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/...20Beta%20II.htm
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>
Tuesday, March 27, 2012
convert
Hi,
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:
> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:
> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>
Sunday, March 25, 2012
Conversion of data
Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
Sue
If you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
sqlsql
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
Sue
If you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
sqlsql
Conversion of data
Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
Conversion of data
Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
--
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
--
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
Conversion from SQL 7.0 to SQL 2000
We have recently converted a SQL 7.0 database to SQL
2000. There are a number of tables that have default
values set for inserting data. When accessing via the
front-end application (ASP page), the tables are not
inserting the default values instead it is inserting
<NULL> into the field. Any insight?Tom,
Thanks for the quick response. I did include the defaults
in the migration and the tables indicates the default
values (as well as the sp_help on the table). Our front
end application was running successfully when using the
SQL 7.0 database. We are not explicitly inserting the
NULL values into the table. If there are no values for
the specific fields it should just insert the default
values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>1. The defaults were not added in the migration. Use
sp_help on the table in question.
>2. The front-end is actually explicitly inserting
NULL's. Use the Profiler to find out.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in
message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL
>2000. There are a number of tables that have default
>values set for inserting data. When accessing via the
>front-end application (ASP page), the tables are not
>inserting the default values instead it is inserting
><NULL> into the field. Any insight?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This isn't an INSERT statement, since the word INSERT does not appear =anywhere in it. I'd be looking for something along the lines of:
INSERT MyTable (Col1, Col2) VALUES ('X', 3)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jp" <jp@.p.com> wrote in message =news:eADPuc6VDHA.1676@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert statement:
exec sp_cursor 180150001, 4, 0, N'tbl_Worksheets', @.L2 =3D '01', @.RepID ==3D '886048', @.SalesWk =3D 30, @.Name =3D 'FORTUNE, AMY = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D =36.00, @.WMU1 =3D 0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 ==3D 165.60, @.Sales_UC1 =3D 70.55, @.Cancels1 =3D 8, @.Cancels_Comp1 =3D =0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 =3D NULL, @.WMU2 =3D =NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, =@.Cancels_UC2 =3D NULL, @.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, =@.Comp3 =3D NULL, @.Sales3 =3D NULL, @.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D =NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, @.Cancels_UC3 =3D =NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, =@.Other_Doe =3D ' ', @.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O768Gn2VDHA.1676@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give you, i.e. what was the exact T-SQL =INSERT statement going into SQL Server. Also, have you tried something =like the following in QA?:
insert MyTable default values
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message =news:5a6201c35767$b817c780$a001280a@.phx.gbl...
Tom,
Thanks for the quick response. I did include the defaults in the migration and the tables indicates the default values (as well as the sp_help on the table). Our front end application was running successfully when using the SQL 7.0 database. We are not explicitly inserting the NULL values into the table. If there are no values for the specific fields it should just insert the default values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>
>1. The defaults were not added in the migration. Use sp_help on the table in question.
>2. The front-end is actually explicitly inserting NULL's. Use the Profiler to find out.
>
>-- >Tom
>
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL >2000. There are a number of tables that have default >values set for inserting data. When accessing via the >front-end application (ASP page), the tables are not >inserting the default values instead it is inserting ><NULL> into the field. Any insight?
> --=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This isn't an INSERT statement, since =the word INSERT does not appear anywhere in it. I'd be looking for =something along the lines of:
INSERT MyTable (Col1, Col2) =VALUES ('X', 3)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jp" wrote in =message news:eADPuc6VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert =statement:
exec sp_cursor 180150001, 4, 0, =N'tbl_Worksheets', @.L2 =3D '01', @.RepID =3D '886048', @.SalesWk =3D 30, @.Name =3D ='FORTUNE, AMY &nbs=p; =; = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D 36.00, @.WMU1 =3D =0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 =3D 165.60, @.Sales_UC1 =3D 70.55, =@.Cancels1 =3D 8, @.Cancels_Comp1 =3D 0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 ==3D NULL, @.WMU2 =3D NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, @.Cancels_UC2 =3D NULL, =@.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, @.Comp3 =3D NULL, @.Sales3 =3D NULL, =@.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, =@.Cancels_UC3 =3D NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, @.Other_Doe =3D ' ', =@.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau"= wrote in message news:O768Gn2VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give =you, i.e. what was the exact T-SQL INSERT statement going into SQL Server. =Also, have you tried something like the following in QA?:
insert MyTable default values
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura Reynolds"--Original Message-->Two =possibilities:>>1. The defaults were not added in the migration. Use sp_help on the =table in question.>2. The front-end is actually =explicitly inserting NULL's. Use the Profiler to find =out.>>-- =>Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON =Canada>www.pinnaclepublishing.com/sql>>>"Laur=a Reynolds" We have recently converted a SQL 7.0 database to SQL >2000. =There are a number of tables that have default >values set for =inserting data. When accessing via the >front-end application (ASP =page), the tables are not >inserting the default values instead it is inserting > into the field. Any insight?>
--=_NextPart_000_000D_01C35787.64B12BC0--
2000. There are a number of tables that have default
values set for inserting data. When accessing via the
front-end application (ASP page), the tables are not
inserting the default values instead it is inserting
<NULL> into the field. Any insight?Tom,
Thanks for the quick response. I did include the defaults
in the migration and the tables indicates the default
values (as well as the sp_help on the table). Our front
end application was running successfully when using the
SQL 7.0 database. We are not explicitly inserting the
NULL values into the table. If there are no values for
the specific fields it should just insert the default
values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>1. The defaults were not added in the migration. Use
sp_help on the table in question.
>2. The front-end is actually explicitly inserting
NULL's. Use the Profiler to find out.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in
message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL
>2000. There are a number of tables that have default
>values set for inserting data. When accessing via the
>front-end application (ASP page), the tables are not
>inserting the default values instead it is inserting
><NULL> into the field. Any insight?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This isn't an INSERT statement, since the word INSERT does not appear =anywhere in it. I'd be looking for something along the lines of:
INSERT MyTable (Col1, Col2) VALUES ('X', 3)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jp" <jp@.p.com> wrote in message =news:eADPuc6VDHA.1676@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert statement:
exec sp_cursor 180150001, 4, 0, N'tbl_Worksheets', @.L2 =3D '01', @.RepID ==3D '886048', @.SalesWk =3D 30, @.Name =3D 'FORTUNE, AMY = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D =36.00, @.WMU1 =3D 0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 ==3D 165.60, @.Sales_UC1 =3D 70.55, @.Cancels1 =3D 8, @.Cancels_Comp1 =3D =0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 =3D NULL, @.WMU2 =3D =NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, =@.Cancels_UC2 =3D NULL, @.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, =@.Comp3 =3D NULL, @.Sales3 =3D NULL, @.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D =NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, @.Cancels_UC3 =3D =NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, =@.Other_Doe =3D ' ', @.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O768Gn2VDHA.1676@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give you, i.e. what was the exact T-SQL =INSERT statement going into SQL Server. Also, have you tried something =like the following in QA?:
insert MyTable default values
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message =news:5a6201c35767$b817c780$a001280a@.phx.gbl...
Tom,
Thanks for the quick response. I did include the defaults in the migration and the tables indicates the default values (as well as the sp_help on the table). Our front end application was running successfully when using the SQL 7.0 database. We are not explicitly inserting the NULL values into the table. If there are no values for the specific fields it should just insert the default values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>
>1. The defaults were not added in the migration. Use sp_help on the table in question.
>2. The front-end is actually explicitly inserting NULL's. Use the Profiler to find out.
>
>-- >Tom
>
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL >2000. There are a number of tables that have default >values set for inserting data. When accessing via the >front-end application (ASP page), the tables are not >inserting the default values instead it is inserting ><NULL> into the field. Any insight?
> --=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This isn't an INSERT statement, since =the word INSERT does not appear anywhere in it. I'd be looking for =something along the lines of:
INSERT MyTable (Col1, Col2) =VALUES ('X', 3)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jp"
Tom,
this is the insert =statement:
exec sp_cursor 180150001, 4, 0, =N'tbl_Worksheets', @.L2 =3D '01', @.RepID =3D '886048', @.SalesWk =3D 30, @.Name =3D ='FORTUNE, AMY &nbs=p; =; = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D 36.00, @.WMU1 =3D =0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 =3D 165.60, @.Sales_UC1 =3D 70.55, =@.Cancels1 =3D 8, @.Cancels_Comp1 =3D 0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 ==3D NULL, @.WMU2 =3D NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, @.Cancels_UC2 =3D NULL, =@.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, @.Comp3 =3D NULL, @.Sales3 =3D NULL, =@.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, =@.Cancels_UC3 =3D NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, @.Other_Doe =3D ' ', =@.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau"
What did the Profiler trace give =you, i.e. what was the exact T-SQL INSERT statement going into SQL Server. =Also, have you tried something like the following in QA?:
insert MyTable default values
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura Reynolds"
--=_NextPart_000_000D_01C35787.64B12BC0--
Thursday, March 22, 2012
Conversion errors on date field from Access to SQL server7
Hi
1st time trying to migrate Access 2000 tables to SQLServer7.
The tables transport but I'm getting errors on the data transfer.
The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.
When the error occurs in transport it reads:
Error at Destination for Row number 310...
Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.
**What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.
I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!
Any ideas on how I get the SQL Server to accept these records??use datetime rather than smalldatetime.
valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999|||I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!
If I change those to 01/01/1900...they will transfer.|||I must be missing the point... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?|||Here goes...there were some records that had dates like this:
01/01/9194
01/01/1897
01/01/1583 etc...
You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999
Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.
[And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].
Hope this clears it up.|||thanks!
You may have other problems here, consider the following code:
declare @.dt datetime, @.vc varchar(100)
set @.dt = '01/01/9194'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1897'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1583'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?
1st time trying to migrate Access 2000 tables to SQLServer7.
The tables transport but I'm getting errors on the data transfer.
The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.
When the error occurs in transport it reads:
Error at Destination for Row number 310...
Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.
**What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.
I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!
Any ideas on how I get the SQL Server to accept these records??use datetime rather than smalldatetime.
valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999|||I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!
If I change those to 01/01/1900...they will transfer.|||I must be missing the point... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?|||Here goes...there were some records that had dates like this:
01/01/9194
01/01/1897
01/01/1583 etc...
You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999
Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.
[And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].
Hope this clears it up.|||thanks!
You may have other problems here, consider the following code:
declare @.dt datetime, @.vc varchar(100)
set @.dt = '01/01/9194'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1897'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1583'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?
Tuesday, March 20, 2012
Converse to BulkLoad...
I've an Annotated Schema file (XSD) which describes how data is laid out in
multiple tables in SQL Server.
I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.
Question is, how do I use the same XSD to then read that data from the
database?
Thanks for your time.
Daniel.Daniel,
Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).
Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
> I've an Annotated Schema file (XSD) which describes how data is laid out
> in multiple tables in SQL Server.
> I've a .Net application that creates an instance of the BulkLoad COM
> object, and calls the Execute method, populating my tables using the XSD
> with my XML data.
> Question is, how do I use the same XSD to then read that data from the
> database?
> Thanks for your time.
> Daniel.
>|||Thanks for your prompt reply.
The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.
In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...
As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...
What do you think?
Thanks,
Dan.
"Nicholas Paldino [.NET/C# MVP]" <mvp@.spam.guard.caspershouse.com> wrote in
message news:OR0cSHxvHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> Well, you don't really need the XSD to read the data from the database.
> You can just read the data from the field when you perform a select. You
> can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
> that you use a FOR XML clause, or that you select one column, one row that
> returns text/ntext/xml data).
> Because you used an XSD to validate the data going in, you can assume
> that the field only contains data which conforms to that XSD. You don't
> have to worry about validating on the way out.
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@.spam.guard.caspershouse.com
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
>
multiple tables in SQL Server.
I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.
Question is, how do I use the same XSD to then read that data from the
database?
Thanks for your time.
Daniel.Daniel,
Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).
Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
> I've an Annotated Schema file (XSD) which describes how data is laid out
> in multiple tables in SQL Server.
> I've a .Net application that creates an instance of the BulkLoad COM
> object, and calls the Execute method, populating my tables using the XSD
> with my XML data.
> Question is, how do I use the same XSD to then read that data from the
> database?
> Thanks for your time.
> Daniel.
>|||Thanks for your prompt reply.
The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.
In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...
As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...
What do you think?
Thanks,
Dan.
"Nicholas Paldino [.NET/C# MVP]" <mvp@.spam.guard.caspershouse.com> wrote in
message news:OR0cSHxvHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> Well, you don't really need the XSD to read the data from the database.
> You can just read the data from the field when you perform a select. You
> can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
> that you use a FOR XML clause, or that you select one column, one row that
> returns text/ntext/xml data).
> Because you used an XSD to validate the data going in, you can assume
> that the field only contains data which conforms to that XSD. You don't
> have to worry about validating on the way out.
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@.spam.guard.caspershouse.com
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
>
Monday, March 19, 2012
Convenient offline tables copy ?
Hi!
I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).
Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).
Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.
Thanks for any pointer
Thibaut Barrère
Once built your packages should be error free, unless something changes. If this is a feature, then SSIS is not going to ber a good solution. BCP can be better because it is simple enough to just dump a table without knowing structures in advance. Bulk Insert Task offers this as well, but there is no Bulk Export Task. I wrote one for DTS, because I liked the way you did not have to manage the changes, just keep source and destination in synch.
(Where did the Xml Source come from, you mean CSV I assume as that was the export format you said.)
For now I'd use BCP, and probably the raw format as well.
|||Thibaut Barrère wrote:
Hi! I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).
Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).
Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.
Thanks for any pointer
Thibaut Barrère
SSIS isn't really a tool for managing objects, only data. Hence I like to rely on SQL scripts for deployig the objects (Very easy, just generate the script in SSMS, change the connection, and hit execute) and use the Import/Exprt wizard to pump data between them.
Or try Darren's method!
-Jamie
|||
This could well be part of a script based deployment scenario, and in that scenario the data Import/Export does not cut it for me. You would have to manually maintain or at least run the Wizard, and in this case twice, since we need to stage in files, as you cannot do direct. Makes sense to allow you to version control it as well.
Using BCP to build the data "script" works rather well here, and is just easier to maintain and faster compared to other methods like the Wizard, or generating scripts.
|||Hi!
thanks for your answers first. Actually I'm not designing a backup strategy or a deployment of some kind : I have a consolidation process on a production machine, and I want to take benefits of a couple of tables which are handled outside the production site (inhouse tables), to achieve clean-up, lookups etc.
What I'm looking for is the easiest way of using this bunch of tables in production, as data sources for the consolidation process.
Following your advices, I've tried bcp and it works just perfectly to export the data. But when calling it for import, sometimes it doesn't insert some rows, but won't return a non-zero errorresult either (I've googled and saw that it seems to happen to others) ! This is quite embarrassing - did you meet such an issue ?
I've also tried the import/export method - but is it supposed to work if I have relationships and constraints between my source tables ?
cheers
Thibaut
Control-of-flow Temp Tables
My question is:
I am having problems with a recompiling stored procedure and am trying to
pinpoint where it is recompiling.
I know that it is not recommended to use temp tables in control-of-flow
statements but.........if you do and the creating of the temp table does
not fit the IF statement will it still spot the creation of a temp table and
recompile?
EG, with num=1
BEGIN
IF @.num=2
CREATE TABLE #TEMP
ELSE
select * from blah
END
Will this still recompile or will it skip the create temp table altogther?
ThanksHi
Without seeing the whole procedure it is hard to recommend anything
concrete! It is recommended that you create your temporary tables at the
start of the procedure to avoid recompilation. Other options may be to use a
derived table and/or split the (parts of the) procedure into multiple
procedures.
John
"Wendy" wrote:
> My question is:
> I am having problems with a recompiling stored procedure and am trying to
> pinpoint where it is recompiling.
> I know that it is not recommended to use temp tables in control-of-flow
> statements but.........if you do and the creating of the temp table doe
s
> not fit the IF statement will it still spot the creation of a temp table a
nd
> recompile?
> EG, with num=1
> BEGIN
> IF @.num=2
> CREATE TABLE #TEMP
> ELSE
> select * from blah
> END
> Will this still recompile or will it skip the create temp table altogther?
'
> Thanks
I am having problems with a recompiling stored procedure and am trying to
pinpoint where it is recompiling.
I know that it is not recommended to use temp tables in control-of-flow
statements but.........if you do and the creating of the temp table does
not fit the IF statement will it still spot the creation of a temp table and
recompile?
EG, with num=1
BEGIN
IF @.num=2
CREATE TABLE #TEMP
ELSE
select * from blah
END
Will this still recompile or will it skip the create temp table altogther?
ThanksHi
Without seeing the whole procedure it is hard to recommend anything
concrete! It is recommended that you create your temporary tables at the
start of the procedure to avoid recompilation. Other options may be to use a
derived table and/or split the (parts of the) procedure into multiple
procedures.
John
"Wendy" wrote:
> My question is:
> I am having problems with a recompiling stored procedure and am trying to
> pinpoint where it is recompiling.
> I know that it is not recommended to use temp tables in control-of-flow
> statements but.........if you do and the creating of the temp table doe
s
> not fit the IF statement will it still spot the creation of a temp table a
nd
> recompile?
> EG, with num=1
> BEGIN
> IF @.num=2
> CREATE TABLE #TEMP
> ELSE
> select * from blah
> END
> Will this still recompile or will it skip the create temp table altogther?
'
> Thanks
controlling temdb size
We have a create table query against 2 large tables (about 12 gig) that
balloons our tempdb to about 17 gigs. Is there a way to constrain the growt
h
of tempdb by turning off logging of some operations. The query is fairly
clean and the join and group by clauses are fully covered by indexes.There is no way to turn off logging. Is it the log file that is ballooning
or the data file? If it's the data file (which I suspect is most of it)
then logging has nothing to do with it anyway. You can pretty much expect a
lot of activity in TempDB when you have that much data that you are joining
and especially grouping by. It has to keep the intermediate results
somewhere while it groups them and that is tempdb. Maybe if you post the DDL
for the tables involved and the actual query someone can suggest something.
Andrew J. Kelly SQL MVP
"Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
> We have a create table query against 2 large tables (about 12 gig) that
> balloons our tempdb to about 17 gigs. Is there a way to constrain the
> growth
> of tempdb by turning off logging of some operations. The query is fairly
> clean and the join and group by clauses are fully covered by indexes.|||Andrew,
Thanks for your reply. The problem is the tempdb file, not the data file.
Using table hints to force use of appropriate indexes and a 'merge' join hin
t
the query now has the same 1hr 50min time but only brought temdb to 9 gig
(rather than 17gig). Below is the DDL for the table and query. By the way
the accnt table is 1.1 million rows and the proft table is 25 million rows.
___
CREATE TABLE [dbo].[accnt] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BAL_OTHR] [money] NOT NULL ,
[CNT_OTHR] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[proft] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[HOUSE_REVENUE] [money] NOT NULL ,
[TRADING_REVENUE] [money] NOT NULL ,
[TOTAL_EXPENSE] [money] NOT NULL ,
[CLIENT_PROFITABILITY] [money] NOT NULL ,
[HasParent] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
[PRIMARY]
GO
CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
GO
CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON [PRIMARY]
GO
CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
GO
CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
GO
SELECT
accnt.BRANCHLABEL,
accnt.REP,
proft.DATABASE_DATE,
accnt.REGIONLABEL,
COUNT(*) AS ACCOUNTS,
SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
SUM(CAST(0 AS money)) AS OTHER_REVENUE
INTO RepMonth
FROM accnt WITH (INDEX (pk_firm_acct))
INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DAT
E
ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE, accnt.REGIONLABE
L
___
"Andrew J. Kelly" wrote:
> There is no way to turn off logging. Is it the log file that is balloonin
g
> or the data file? If it's the data file (which I suspect is most of it)
> then logging has nothing to do with it anyway. You can pretty much expect
a
> lot of activity in TempDB when you have that much data that you are joinin
g
> and especially grouping by. It has to keep the intermediate results
> somewhere while it groups them and that is tempdb. Maybe if you post the D
DL
> for the tables involved and the actual query someone can suggest something
.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
> message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
>
>|||OK well each database including tempdb has at least one data file and one
log file. The data file normally has a .mdf extension and the log file has
a .ldf extension. Which one of these for TempDB is growing to 17GB? A
couple of comments from what I see here. You don't have a primary Key
defined on the Proft table and you don't have a clustered index on that
table either. Both of which are very important. I suggest you drop the
nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in a
true merge fashion using the clustered indexes and should dramatically cut
down on the time to do this. You might even consider making the clustered
indexes on both these tables with ACCT_NO as the first column instead of
Firm_ID. The reason being that this is more selective but this depends a
lot on how you access these tables. Do you look for Acc_No or Firm_ID more?
In either case there is no need to have a non-clustered index on Firm_ID if
the clustered index already has Firm_ID as the first column in the index
expression. Also why does the Order By have the same columns as the group by
but in a different order? It may help to have them the same so the engine
does not potentially have to order twice.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
> Andrew,
> Thanks for your reply. The problem is the tempdb file, not the data file.
> Using table hints to force use of appropriate indexes and a 'merge' join
> hint
> the query now has the same 1hr 50min time but only brought temdb to 9 gig
> (rather than 17gig). Below is the DDL for the table and query. By the
> way
> the accnt table is 1.1 million rows and the proft table is 25 million
> rows.
> ___
> CREATE TABLE [dbo].[accnt] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BAL_OTHR] [money] NOT NULL ,
> [CNT_OTHR] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[proft] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [HOUSE_REVENUE] [money] NOT NULL ,
> [TRADING_REVENUE] [money] NOT NULL ,
> [TOTAL_EXPENSE] [money] NOT NULL ,
> [CLIENT_PROFITABILITY] [money] NOT NULL ,
> [HasParent] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
> CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
> ([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
> GO
> CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
> GO
> CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
> REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
> GO
> SELECT
> accnt.BRANCHLABEL,
> accnt.REP,
> proft.DATABASE_DATE,
> accnt.REGIONLABEL,
> COUNT(*) AS ACCOUNTS,
> SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
> SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
> SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
> SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
> SUM(CAST(0 AS money)) AS OTHER_REVENUE
> INTO RepMonth
> FROM accnt WITH (INDEX (pk_firm_acct))
> INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
> ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
> GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP,
> proft.DATABASE_DATE
> ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE,
> accnt.REGIONLABEL
> ___
> "Andrew J. Kelly" wrote:
>|||Andrew,
Thanks. Not quite sure why the order and group by statements have different
field order, but that's easy to fix.
The mdf for tempdb is the one growing.
We will try variations of your good suggestions. Firm_id is baggage, part
of the account number and in this implementation of the database always
containing the same value, so we will flip that around.
Monthly we use DTS to add 1 million records to proft and drop and replace
all rows in the accnt table. Because the proft is so large I drop and
rebuild all indexes (loading the data in between), so if I have a primary ke
y
I have to deal with all the dependencies, constraints, etc. Proft by the wa
y
is unique on acct_no, firm_id, database_date, so its primary key would need
all three but this field order should be ok.
With the foreign key constraint connecting proft into accnt I assumed the db
engine would recognize what is supposed to be happening.
"Andrew J. Kelly" wrote:
> OK well each database including tempdb has at least one data file and one
> log file. The data file normally has a .mdf extension and the log file ha
s
> a .ldf extension. Which one of these for TempDB is growing to 17GB? A
> couple of comments from what I see here. You don't have a primary Key
> defined on the Proft table and you don't have a clustered index on that
> table either. Both of which are very important. I suggest you drop the
> nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
> Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in
a
> true merge fashion using the clustered indexes and should dramatically cu
t
> down on the time to do this. You might even consider making the clustered
> indexes on both these tables with ACCT_NO as the first column instead of
> Firm_ID. The reason being that this is more selective but this depends a
> lot on how you access these tables. Do you look for Acc_No or Firm_ID mor
e?
> In either case there is no need to have a non-clustered index on Firm_ID i
f
> the clustered index already has Firm_ID as the first column in the index
> expression. Also why does the Order By have the same columns as the group
by
> but in a different order? It may help to have them the same so the engine
> does not potentially have to order twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
> message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
>
>|||If the data file keeps growing back to that size then you should leave it
there. Growing a data file is very expensive. There is no penalty for too
much free space but a big one for too little. If Firm_ID is always the same
value I would argue that you don't have it in the Clustered index at all.
The column(s) of the clustered index are appended to the end of all the
nonclustered indexes so you are propagating 4 bytes per row * every
nonclustered index. Let us know how it works out.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:36502EBA-6CFA-4F94-92B8-1402769CE178@.microsoft.com...
> Andrew,
> Thanks. Not quite sure why the order and group by statements have
> different
> field order, but that's easy to fix.
> The mdf for tempdb is the one growing.
> We will try variations of your good suggestions. Firm_id is baggage, part
> of the account number and in this implementation of the database always
> containing the same value, so we will flip that around.
> Monthly we use DTS to add 1 million records to proft and drop and replace
> all rows in the accnt table. Because the proft is so large I drop and
> rebuild all indexes (loading the data in between), so if I have a primary
> key
> I have to deal with all the dependencies, constraints, etc. Proft by the
> way
> is unique on acct_no, firm_id, database_date, so its primary key would
> need
> all three but this field order should be ok.
> With the foreign key constraint connecting proft into accnt I assumed the
> db
> engine would recognize what is supposed to be happening.
> "Andrew J. Kelly" wrote:
>
balloons our tempdb to about 17 gigs. Is there a way to constrain the growt
h
of tempdb by turning off logging of some operations. The query is fairly
clean and the join and group by clauses are fully covered by indexes.There is no way to turn off logging. Is it the log file that is ballooning
or the data file? If it's the data file (which I suspect is most of it)
then logging has nothing to do with it anyway. You can pretty much expect a
lot of activity in TempDB when you have that much data that you are joining
and especially grouping by. It has to keep the intermediate results
somewhere while it groups them and that is tempdb. Maybe if you post the DDL
for the tables involved and the actual query someone can suggest something.
Andrew J. Kelly SQL MVP
"Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
> We have a create table query against 2 large tables (about 12 gig) that
> balloons our tempdb to about 17 gigs. Is there a way to constrain the
> growth
> of tempdb by turning off logging of some operations. The query is fairly
> clean and the join and group by clauses are fully covered by indexes.|||Andrew,
Thanks for your reply. The problem is the tempdb file, not the data file.
Using table hints to force use of appropriate indexes and a 'merge' join hin
t
the query now has the same 1hr 50min time but only brought temdb to 9 gig
(rather than 17gig). Below is the DDL for the table and query. By the way
the accnt table is 1.1 million rows and the proft table is 25 million rows.
___
CREATE TABLE [dbo].[accnt] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BAL_OTHR] [money] NOT NULL ,
[CNT_OTHR] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[proft] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[HOUSE_REVENUE] [money] NOT NULL ,
[TRADING_REVENUE] [money] NOT NULL ,
[TOTAL_EXPENSE] [money] NOT NULL ,
[CLIENT_PROFITABILITY] [money] NOT NULL ,
[HasParent] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
[PRIMARY]
GO
CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
GO
CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON [PRIMARY]
GO
CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
GO
CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
GO
SELECT
accnt.BRANCHLABEL,
accnt.REP,
proft.DATABASE_DATE,
accnt.REGIONLABEL,
COUNT(*) AS ACCOUNTS,
SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
SUM(CAST(0 AS money)) AS OTHER_REVENUE
INTO RepMonth
FROM accnt WITH (INDEX (pk_firm_acct))
INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DAT
E
ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE, accnt.REGIONLABE
L
___
"Andrew J. Kelly" wrote:
> There is no way to turn off logging. Is it the log file that is balloonin
g
> or the data file? If it's the data file (which I suspect is most of it)
> then logging has nothing to do with it anyway. You can pretty much expect
a
> lot of activity in TempDB when you have that much data that you are joinin
g
> and especially grouping by. It has to keep the intermediate results
> somewhere while it groups them and that is tempdb. Maybe if you post the D
DL
> for the tables involved and the actual query someone can suggest something
.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
> message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
>
>|||OK well each database including tempdb has at least one data file and one
log file. The data file normally has a .mdf extension and the log file has
a .ldf extension. Which one of these for TempDB is growing to 17GB? A
couple of comments from what I see here. You don't have a primary Key
defined on the Proft table and you don't have a clustered index on that
table either. Both of which are very important. I suggest you drop the
nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in a
true merge fashion using the clustered indexes and should dramatically cut
down on the time to do this. You might even consider making the clustered
indexes on both these tables with ACCT_NO as the first column instead of
Firm_ID. The reason being that this is more selective but this depends a
lot on how you access these tables. Do you look for Acc_No or Firm_ID more?
In either case there is no need to have a non-clustered index on Firm_ID if
the clustered index already has Firm_ID as the first column in the index
expression. Also why does the Order By have the same columns as the group by
but in a different order? It may help to have them the same so the engine
does not potentially have to order twice.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
> Andrew,
> Thanks for your reply. The problem is the tempdb file, not the data file.
> Using table hints to force use of appropriate indexes and a 'merge' join
> hint
> the query now has the same 1hr 50min time but only brought temdb to 9 gig
> (rather than 17gig). Below is the DDL for the table and query. By the
> way
> the accnt table is 1.1 million rows and the proft table is 25 million
> rows.
> ___
> CREATE TABLE [dbo].[accnt] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BAL_OTHR] [money] NOT NULL ,
> [CNT_OTHR] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[proft] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [HOUSE_REVENUE] [money] NOT NULL ,
> [TRADING_REVENUE] [money] NOT NULL ,
> [TOTAL_EXPENSE] [money] NOT NULL ,
> [CLIENT_PROFITABILITY] [money] NOT NULL ,
> [HasParent] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
> CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
> ([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
> GO
> CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
> GO
> CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
> REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
> GO
> SELECT
> accnt.BRANCHLABEL,
> accnt.REP,
> proft.DATABASE_DATE,
> accnt.REGIONLABEL,
> COUNT(*) AS ACCOUNTS,
> SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
> SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
> SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
> SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
> SUM(CAST(0 AS money)) AS OTHER_REVENUE
> INTO RepMonth
> FROM accnt WITH (INDEX (pk_firm_acct))
> INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
> ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
> GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP,
> proft.DATABASE_DATE
> ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE,
> accnt.REGIONLABEL
> ___
> "Andrew J. Kelly" wrote:
>|||Andrew,
Thanks. Not quite sure why the order and group by statements have different
field order, but that's easy to fix.
The mdf for tempdb is the one growing.
We will try variations of your good suggestions. Firm_id is baggage, part
of the account number and in this implementation of the database always
containing the same value, so we will flip that around.
Monthly we use DTS to add 1 million records to proft and drop and replace
all rows in the accnt table. Because the proft is so large I drop and
rebuild all indexes (loading the data in between), so if I have a primary ke
y
I have to deal with all the dependencies, constraints, etc. Proft by the wa
y
is unique on acct_no, firm_id, database_date, so its primary key would need
all three but this field order should be ok.
With the foreign key constraint connecting proft into accnt I assumed the db
engine would recognize what is supposed to be happening.
"Andrew J. Kelly" wrote:
> OK well each database including tempdb has at least one data file and one
> log file. The data file normally has a .mdf extension and the log file ha
s
> a .ldf extension. Which one of these for TempDB is growing to 17GB? A
> couple of comments from what I see here. You don't have a primary Key
> defined on the Proft table and you don't have a clustered index on that
> table either. Both of which are very important. I suggest you drop the
> nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
> Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in
a
> true merge fashion using the clustered indexes and should dramatically cu
t
> down on the time to do this. You might even consider making the clustered
> indexes on both these tables with ACCT_NO as the first column instead of
> Firm_ID. The reason being that this is more selective but this depends a
> lot on how you access these tables. Do you look for Acc_No or Firm_ID mor
e?
> In either case there is no need to have a non-clustered index on Firm_ID i
f
> the clustered index already has Firm_ID as the first column in the index
> expression. Also why does the Order By have the same columns as the group
by
> but in a different order? It may help to have them the same so the engine
> does not potentially have to order twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
> message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
>
>|||If the data file keeps growing back to that size then you should leave it
there. Growing a data file is very expensive. There is no penalty for too
much free space but a big one for too little. If Firm_ID is always the same
value I would argue that you don't have it in the Clustered index at all.
The column(s) of the clustered index are appended to the end of all the
nonclustered indexes so you are propagating 4 bytes per row * every
nonclustered index. Let us know how it works out.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:36502EBA-6CFA-4F94-92B8-1402769CE178@.microsoft.com...
> Andrew,
> Thanks. Not quite sure why the order and group by statements have
> different
> field order, but that's easy to fix.
> The mdf for tempdb is the one growing.
> We will try variations of your good suggestions. Firm_id is baggage, part
> of the account number and in this implementation of the database always
> containing the same value, so we will flip that around.
> Monthly we use DTS to add 1 million records to proft and drop and replace
> all rows in the accnt table. Because the proft is so large I drop and
> rebuild all indexes (loading the data in between), so if I have a primary
> key
> I have to deal with all the dependencies, constraints, etc. Proft by the
> way
> is unique on acct_no, firm_id, database_date, so its primary key would
> need
> all three but this field order should be ok.
> With the foreign key constraint connecting proft into accnt I assumed the
> db
> engine would recognize what is supposed to be happening.
> "Andrew J. Kelly" wrote:
>
controlling merge syncronisation at subscriber with ActiveX
Hi,
If I am to use the merge activex control to run syncs at the subscriber, in
what system tables will I find merge history, error messages etc ?
I read in another thread about querying the subscriber prior to attempting a
sync to determine if the subscriber db needed reinitialisation (by comparing
version number values in a user table) - so that active x could flag the
subscription for reinitialisation automatically. Any idea how this would
actually be done ?
It would be great to have the sunscriber db be automatically flagged for
reinitialisation when needed, and not require user input.
Thanks for your help
Darren
query the msmerge_history table in the distribution database on the
subscriber if its a pull.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:e5pPdbNlEHA.3392@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If I am to use the merge activex control to run syncs at the subscriber,
in
> what system tables will I find merge history, error messages etc ?
> I read in another thread about querying the subscriber prior to attempting
a
> sync to determine if the subscriber db needed reinitialisation (by
comparing
> version number values in a user table) - so that active x could flag the
> subscription for reinitialisation automatically. Any idea how this would
> actually be done ?
> It would be great to have the sunscriber db be automatically flagged for
> reinitialisation when needed, and not require user input.
> Thanks for your help
> Darren
>
If I am to use the merge activex control to run syncs at the subscriber, in
what system tables will I find merge history, error messages etc ?
I read in another thread about querying the subscriber prior to attempting a
sync to determine if the subscriber db needed reinitialisation (by comparing
version number values in a user table) - so that active x could flag the
subscription for reinitialisation automatically. Any idea how this would
actually be done ?
It would be great to have the sunscriber db be automatically flagged for
reinitialisation when needed, and not require user input.
Thanks for your help
Darren
query the msmerge_history table in the distribution database on the
subscriber if its a pull.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:e5pPdbNlEHA.3392@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If I am to use the merge activex control to run syncs at the subscriber,
in
> what system tables will I find merge history, error messages etc ?
> I read in another thread about querying the subscriber prior to attempting
a
> sync to determine if the subscriber db needed reinitialisation (by
comparing
> version number values in a user table) - so that active x could flag the
> subscription for reinitialisation automatically. Any idea how this would
> actually be done ?
> It would be great to have the sunscriber db be automatically flagged for
> reinitialisation when needed, and not require user input.
> Thanks for your help
> Darren
>
Saturday, February 25, 2012
contains/fuzzy/substring - type of join between two tables - MS SQL 2000
ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care :)|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care :)|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
contains/fuzzy/substring - type of join between two tables - MS SQL 2000
ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care
|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,
descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care
|||You should also be able to use fulltext for this.Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,
descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
contains/fuzzy/substring - type of join between two tables - MS SQL 2000
ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?
You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care
|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptio nColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?
You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care
|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptio nColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
Sunday, February 19, 2012
Construction of view or sp
I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SThis is a multi-part message in MIME format.
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Something like:
SELECT
ProjectID,
Account,
OrderAmt =3D isnull(( SELECT sum( Amount ) FROM tblOrder WHERE =Account =3D a.Account GROUP BY Account ), 0 )
TransAmt =3D isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE =Account =3D a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and =how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 =wasted keystrokes since they provide no additional value. (Make every =keystroke useful.) You know it is a table because it follows the FROM =keyword.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<staeri@.gmail.com> wrote in message =news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...
>I have the following tables:
> > tblAccount:
> -Account
> > tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> > tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> > tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> > I would like to show all accounts in tblAccount and if there are =amount
> values on the accounts in the other tables they should be shown next =to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> > Which is the best way to do this, a view or sp and with which syntax?
> > Regards,
> > S
>
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Something like:
SELECT
=ProjectID,
=Account,
OrderAmt =3D =isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account =3D a.Account GROUP BY =Account ), 0 )
TransAmt =3D =isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =3D a.Account GROUP BY =Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends =upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table =prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional =value. (Make every keystroke useful.) You know it is a table because it follows =the FROM keyword.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...>I =have the following tables:> > tblAccount:> -Account> => tblAmount:> -ProjectID> -Account> -Amount1> -Amount2> > tblOrder:> -OrderID> =-ProjectID> -Account> -Amount> > tblTransaction:> -TransactionID> -ProjectID> -Account> =-Amount> > I would like to show all accounts in tblAccount and if there =are amount> values on the accounts in the other tables they should be =shown next to> the account number. If there are no values in the other =tables the> account without value should still be shown.> => Which is the best way to do this, a view or sp and with which syntax?> => Regards,> > S>
--=_NextPart_000_1050_01C6EBFD.FFAA9360--
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SThis is a multi-part message in MIME format.
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Something like:
SELECT
ProjectID,
Account,
OrderAmt =3D isnull(( SELECT sum( Amount ) FROM tblOrder WHERE =Account =3D a.Account GROUP BY Account ), 0 )
TransAmt =3D isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE =Account =3D a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and =how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 =wasted keystrokes since they provide no additional value. (Make every =keystroke useful.) You know it is a table because it follows the FROM =keyword.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<staeri@.gmail.com> wrote in message =news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...
>I have the following tables:
> > tblAccount:
> -Account
> > tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> > tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> > tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> > I would like to show all accounts in tblAccount and if there are =amount
> values on the accounts in the other tables they should be shown next =to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> > Which is the best way to do this, a view or sp and with which syntax?
> > Regards,
> > S
>
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Something like:
SELECT
=ProjectID,
=Account,
OrderAmt =3D =isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account =3D a.Account GROUP BY =Account ), 0 )
TransAmt =3D =isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =3D a.Account GROUP BY =Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends =upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table =prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional =value. (Make every keystroke useful.) You know it is a table because it follows =the FROM keyword.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
--=_NextPart_000_1050_01C6EBFD.FFAA9360--
Tuesday, February 14, 2012
Constraints and Triggers
We have a database that is using triggers to support referntial integrity.
Is it possible to use constraints on new tables and slowly replace triggers
with constraints in the future?
David
Yes you can migrate to foreign key constraints instead of using triggers.
This is the preferred method and can improve performance as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:u1uday5MFHA.1308@.TK2MSFTNGP15.phx.gbl...
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace
> triggers with constraints in the future?
> David
>
|||Yes, it is much better to use FK instead of triggers
Look at cascade on delete,cascade on update in the BOL.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:u1uday5MFHA.1308@.TK2MSFTNGP15.phx.gbl...
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace
triggers
> with constraints in the future?
> David
>
|||David,
Sure, and this is what Microsoft recommend, to enforce RI using DRI.
AMB
"David C" wrote:
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace triggers
> with constraints in the future?
> David
>
>
Is it possible to use constraints on new tables and slowly replace triggers
with constraints in the future?
David
Yes you can migrate to foreign key constraints instead of using triggers.
This is the preferred method and can improve performance as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:u1uday5MFHA.1308@.TK2MSFTNGP15.phx.gbl...
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace
> triggers with constraints in the future?
> David
>
|||Yes, it is much better to use FK instead of triggers
Look at cascade on delete,cascade on update in the BOL.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:u1uday5MFHA.1308@.TK2MSFTNGP15.phx.gbl...
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace
triggers
> with constraints in the future?
> David
>
|||David,
Sure, and this is what Microsoft recommend, to enforce RI using DRI.
AMB
"David C" wrote:
> We have a database that is using triggers to support referntial integrity.
> Is it possible to use constraints on new tables and slowly replace triggers
> with constraints in the future?
> David
>
>
Subscribe to:
Posts (Atom)