The EM can produce SQL Scripts of tables/indexes/constraints/default values etc from a database. When doing so, the create statement for tables and the alter statements for constraints (including the default values) are separated. In effect, the create table-statement will only create the table; keys, default values, indexes are changes on the table. My question is: when saving the results in separated files as a basis to continue working on, would you take the effort of changing the create-tables statements so that will also fill in the default values or keep it separate?I've never bothered to mangle the generated scripts... I've never seen any need for it.
-PatP|||Pat: Thing is, there's a db here without any of the tables/views/sp's stored on disk making it, imho, an unhealthy basis to continue building on. So I've scripted the db layout, views and all that allowing me to create a dev. I'm just considering whether I should take the effort or not; when changes are made to the db/tables I'll process it into the create-scripts as well.
I wonder though, do you have any create scripts that create all the tables in a database, do you keep it separate from the indexes/constraints? I don't want to start a flame-thing on "who's responsible", just wondering if you'd take the trouble?|||I'm not really clear what you are asking, so this response may seem way off base.
If you are talking about the option that causes Enterprise Mangler to script the PK/FK/Default/Check Constraints as part of the table versus scripting the table "raw" then creating ALTER TABLE statements to add the constraints, I usually don't care how it gets done. If there was some compelling reason to set it one way or the other, then I would. Since the net effect after running the whole script will be the same, I've never bothered to monkey with it... Either way works fine for me, and produces the same results in the end.
-PatP
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
EM in SS2000
In EM when I right-click on a database and choose "view", "taskpad" I get a
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create statistics database
option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
place, so best is most often to leave them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get a
> list of tables. Under some of the tables, I see the names of indexes that I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Those are not indexes. They are auto-creates statistics. See the auto create statistics database
> option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> > In EM when I right-click on a database and choose "view", "taskpad" I get a
> > list of tables. Under some of the tables, I see the names of indexes that I
> > recognize but then I also see something like hind_909246294_10A_1A or
> > _WA_Sys_dtCCreationDate_3631FF56. What are these?
> >
> > Thanks,
> > --
> > Dan D.
>
>
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create statistics database
option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
place, so best is most often to leave them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get a
> list of tables. Under some of the tables, I see the names of indexes that I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Those are not indexes. They are auto-creates statistics. See the auto create statistics database
> option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> > In EM when I right-click on a database and choose "view", "taskpad" I get a
> > list of tables. Under some of the tables, I see the names of indexes that I
> > recognize but then I also see something like hind_909246294_10A_1A or
> > _WA_Sys_dtCCreationDate_3631FF56. What are these?
> >
> > Thanks,
> > --
> > Dan D.
>
>
EM in SS2000
In EM when I right-click on a database and choose "view", "taskpad" I get a
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create
statistics database
option. You can drop these using DROP STATISTICS, but they were created for
a reason in the first
place, so best is most often to leave them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get
a
> list of tables. Under some of the tables, I see the names of indexes that
I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Those are not indexes. They are auto-creates statistics. See the auto crea
te statistics database
> option. You can drop these using DROP STATISTICS, but they were created fo
r a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
>
>
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create
statistics database
option. You can drop these using DROP STATISTICS, but they were created for
a reason in the first
place, so best is most often to leave them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get
a
> list of tables. Under some of the tables, I see the names of indexes that
I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Those are not indexes. They are auto-creates statistics. See the auto crea
te statistics database
> option. You can drop these using DROP STATISTICS, but they were created fo
r a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
>
>
Sunday, February 19, 2012
Efficiently Inserting 1 Million records
I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:
> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:
> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan
Subscribe to:
Posts (Atom)