bigint field vs int

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int

i need to know about the performance and difference

will it affect the performance much and second will i have to change any code like delete,update

A standard Int ranges from -2,147,483,648 through 2,147,483,647 so you can use them to store values much larger than 100000.

If you do use bigints, you would need to use Int64 in your code

|||

HI,

There will be some performance difference in using int and bigint. If you use bigint the performance is slower in an 32 bit processer. But in case of a 64 bit processor the performance should not be a problem

|||

Int will give you up to 2,147,483,647 while Bigint gives up to 9,223,372,036,854,775,807. The only difference in your stored procedure is that you will need to change the parameter declarations from INT to BIGINT.

>i need to know about the performance and difference
Obviously more working memory will be required for BIGINT instead of INT (8 bytes instead of 4 for for each value), however SQL's will still work very efficiently provided there is enough RAM. If you only have enough RAM for INT, changing to BIGINT will put you at a disadvantage - so make sure you have enough RAM.

sql

BI Accelerator: multiply source databeses

Hi All!
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?
If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>
|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or
>
|||That is what we call the PAG (Prescriptive Architecture Guides).
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package
> or
>

BI Accelerator: multiply source databeses

Hi All!
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or[vbcol=seagreen]
>|||That is what we call the PAG (Prescriptive Architecture Guides).
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package[vbcol=seagreen]
> or
>

Best Way to transfer a table

What is the best way to send someone a table - Complete definition and data.
I have tried creating a new empty database and just add the one table and
back that up but it is a huge file. I tried exporting the rows to Excel but
that will only allow 65K rows. I was surprised to see not option for xml in
E.M.
thanks,
GMS Access? FoxPro? There are dozens of ways to _send_ the data.
However, no matter what you do, unless you send the end user an MSSQL DDL
Script, they'll never really know just _exactly_ how it was in SQL Server.
Why not just send a DDL script and a fixed-width text file?
James Hokes
"Gary" <gb@.nospam.com> wrote in message
news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> What is the best way to send someone a table - Complete definition and
data.
> I have tried creating a new empty database and just add the one table and
> back that up but it is a huge file. I tried exporting the rows to Excel
but
> that will only allow 65K rows. I was surprised to see not option for xml
in
> E.M.
> thanks,
> G
>|||My opinion is that the data should be sent in TDF (tab
delimited) format. Comma-separated is no good - since so
much data can have commas.
TDF's BULK INSERT perfectly.
In query analyzer, do a SELECT * From table and in the
RESULT GRID - select all / copy&paste into NOTEPAD.
This gives you the TDF data in a text file.
If you were to BULK INSERT this back into the table it
would be identical.
We have two little VB programs here called TextImport and
TextOutput that we use constantly. Part of
our "distribute" table data to a customer and back from a
customer. We use these because copy/paste and notepad do
have limitations on width that we have encountered...
>--Original Message--
>MS Access? FoxPro? There are dozens of ways to _send_
the data.
>However, no matter what you do, unless you send the end
user an MSSQL DDL
>Script, they'll never really know just _exactly_ how it
was in SQL Server.
>Why not just send a DDL script and a fixed-width text
file?
>James Hokes
>"Gary" <gb@.nospam.com> wrote in message
>news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> What is the best way to send someone a table -
Complete definition and
>data.
>> I have tried creating a new empty database and just
add the one table and
>> back that up but it is a huge file. I tried exporting
the rows to Excel
>but
>> that will only allow 65K rows. I was surprised to see
not option for xml
>in
>> E.M.
>> thanks,
>> G
>>
>
>.
>|||Even Tab is not bullet-proof.
Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
I'll send you a file that'll break your app right now if you like.
The only bullet-proof text file is a fixed-width one.
Absolutely bullet-proof.
Ask any mainframe DBA.
James Hokes
"Steve z" <szlamany@.antarescomputing.com> wrote in message
news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> My opinion is that the data should be sent in TDF (tab
> delimited) format. Comma-separated is no good - since so
> much data can have commas.
> TDF's BULK INSERT perfectly.
> In query analyzer, do a SELECT * From table and in the
> RESULT GRID - select all / copy&paste into NOTEPAD.
> This gives you the TDF data in a text file.
> If you were to BULK INSERT this back into the table it
> would be identical.
> We have two little VB programs here called TextImport and
> TextOutput that we use constantly. Part of
> our "distribute" table data to a customer and back from a
> customer. We use these because copy/paste and notepad do
> have limitations on width that we have encountered...
>
> >--Original Message--
> >MS Access? FoxPro? There are dozens of ways to _send_
> the data.
> >
> >However, no matter what you do, unless you send the end
> user an MSSQL DDL
> >Script, they'll never really know just _exactly_ how it
> was in SQL Server.
> >
> >Why not just send a DDL script and a fixed-width text
> file?
> >
> >James Hokes
> >
> >"Gary" <gb@.nospam.com> wrote in message
> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> >> What is the best way to send someone a table -
> Complete definition and
> >data.
> >> I have tried creating a new empty database and just
> add the one table and
> >> back that up but it is a huge file. I tried exporting
> the rows to Excel
> >but
> >> that will only allow 65K rows. I was surprised to see
> not option for xml
> >in
> >> E.M.
> >> thanks,
> >> G
> >>
> >>
> >
> >
> >.
> >|||Actually if this is going to another SQL Server I would use the native
format of BCP instead of text.
--
Andrew J. Kelly SQL MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:eiwloA3xDHA.2396@.TK2MSFTNGP09.phx.gbl...
> Even Tab is not bullet-proof.
> Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
> I'll send you a file that'll break your app right now if you like.
> The only bullet-proof text file is a fixed-width one.
> Absolutely bullet-proof.
> Ask any mainframe DBA.
> James Hokes
> "Steve z" <szlamany@.antarescomputing.com> wrote in message
> news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> > My opinion is that the data should be sent in TDF (tab
> > delimited) format. Comma-separated is no good - since so
> > much data can have commas.
> >
> > TDF's BULK INSERT perfectly.
> >
> > In query analyzer, do a SELECT * From table and in the
> > RESULT GRID - select all / copy&paste into NOTEPAD.
> >
> > This gives you the TDF data in a text file.
> >
> > If you were to BULK INSERT this back into the table it
> > would be identical.
> >
> > We have two little VB programs here called TextImport and
> > TextOutput that we use constantly. Part of
> > our "distribute" table data to a customer and back from a
> > customer. We use these because copy/paste and notepad do
> > have limitations on width that we have encountered...
> >
> >
> > >--Original Message--
> > >MS Access? FoxPro? There are dozens of ways to _send_
> > the data.
> > >
> > >However, no matter what you do, unless you send the end
> > user an MSSQL DDL
> > >Script, they'll never really know just _exactly_ how it
> > was in SQL Server.
> > >
> > >Why not just send a DDL script and a fixed-width text
> > file?
> > >
> > >James Hokes
> > >
> > >"Gary" <gb@.nospam.com> wrote in message
> > >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > >> What is the best way to send someone a table -
> > Complete definition and
> > >data.
> > >> I have tried creating a new empty database and just
> > add the one table and
> > >> back that up but it is a huge file. I tried exporting
> > the rows to Excel
> > >but
> > >> that will only allow 65K rows. I was surprised to see
> > not option for xml
> > >in
> > >> E.M.
> > >> thanks,
> > >> G
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Been a VAX mainframe programmer since late 70's - used
fixed width for 20+ years on the mainframe. Lots of
effort in mapping data - input and output sides - lots of
room for bugs (we always consider the maintenance
nightmare).
We happen to be very familiar with our data - no tabs
anywhere - and we have medical claim tables with 2.5
million rows... (program checks for tab in data anyway -
simple to do)
What we like about our method is that our VB textoutput
program puts dates in a nice format so that BULK INSERT
works without issue. But as an app development shop, we
only support a handful of datatypes - we like the variety
that SQL has, but no reason to beat up the programmers
with each ones quirks...
Our textoutput program also uses an .INI file to specify
server/database/table and even a SELECT statement if you
want to override the defaults of SELECT *. We actually
use it inside the SQL Agent to run nightly jobs that push
text files back to the legacy VAX's at several sites.
The VAX-11 BASIC programs on the mainframe only have to
cutup the input lines by looking for TAB's - so column
issues are the only ones to consider - no mapping of
specific byte positions.
We even have mainframe programs that analyze the legacy
database structures and create .SQL scripts to "CREATE
TABLE's" and text files for BULK INSERT's. We even
include the BULK INSERT statement (with proper server
path to text file) in the .SQL script created on the
mainframe.
We can take a mainframe's application data and get it
into SQL in short order with these tools.
>--Original Message--
>Even Tab is not bullet-proof.
>Tab is ASCII character 9. Plenty of tabs in varchar
fields, I assure you.
>I'll send you a file that'll break your app right now if
you like.
>The only bullet-proof text file is a fixed-width one.
>Absolutely bullet-proof.
>Ask any mainframe DBA.
>James Hokes
>"Steve z" <szlamany@.antarescomputing.com> wrote in
message
>news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
>> My opinion is that the data should be sent in TDF (tab
>> delimited) format. Comma-separated is no good - since
so
>> much data can have commas.
>> TDF's BULK INSERT perfectly.
>> In query analyzer, do a SELECT * From table and in the
>> RESULT GRID - select all / copy&paste into NOTEPAD.
>> This gives you the TDF data in a text file.
>> If you were to BULK INSERT this back into the table it
>> would be identical.
>> We have two little VB programs here called TextImport
and
>> TextOutput that we use constantly. Part of
>> our "distribute" table data to a customer and back
from a
>> customer. We use these because copy/paste and notepad
do
>> have limitations on width that we have encountered...
>>
>> >--Original Message--
>> >MS Access? FoxPro? There are dozens of ways to _send_
>> the data.
>> >
>> >However, no matter what you do, unless you send the
end
>> user an MSSQL DDL
>> >Script, they'll never really know just _exactly_ how
it
>> was in SQL Server.
>> >
>> >Why not just send a DDL script and a fixed-width text
>> file?
>> >
>> >James Hokes
>> >
>> >"Gary" <gb@.nospam.com> wrote in message
>> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> >> What is the best way to send someone a table -
>> Complete definition and
>> >data.
>> >> I have tried creating a new empty database and just
>> add the one table and
>> >> back that up but it is a huge file. I tried
exporting
>> the rows to Excel
>> >but
>> >> that will only allow 65K rows. I was surprised to
see
>> not option for xml
>> >in
>> >> E.M.
>> >> thanks,
>> >> G
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.

Best way to insert data to MSDE db

Hello.
I'm creating a stand-alone windows application using the MSDE server.
Performance issues in this application are big-deal to my client.
I need to insert data to the database in rate of – 2MB / sec.
Each insert action should insert ~ 10-40 MB to the database.
My questions are:
1. What is the fastest way to INSERT data to the database? Using insert from
c# code, scripts, stored procedure, other?
2. What is the fastest way to retrieve information from the database?
Thanks.
hi,
Eli wrote:
> Hello.
> I'm creating a stand-alone windows application using the MSDE server.
> Performance issues in this application are big-deal to my client.
> I need to insert data to the database in rate of - 2MB / sec.
> Each insert action should insert ~ 10-40 MB to the database.
> My questions are:
> 1. What is the fastest way to INSERT data to the database? Using
> insert from c# code, scripts, stored procedure, other?
nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
or BCP.exe) that can even advantage from combined CPUs... and using BULK
LOGGED recovery model will help ...

> 2. What is the fastest way to retrieve information from the database?
data retrival is demanded to SELECT statements, and you have non other
ways.. you can optimize your index design to make the application works
better, you can even over-index your structure, but this will slow down
insertions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the quick response.
Can you direct me to a link of how to implement the command by code ?
I'm working with C#, and need to send the data from the code.
Do you know of a way to insert the data has an object?
I mean, the rows I want to insert are allready in a class/struct format, and
sending them as-is to the database, insetd of parsing them to insert command,
will be, I think,a fast way. What do you think?
"Andrea Montanari" wrote:

> hi,
> Eli wrote:
> nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
> or BCP.exe) that can even advantage from combined CPUs... and using BULK
> LOGGED recovery model will help ...
>
> data retrival is demanded to SELECT statements, and you have non other
> ways.. you can optimize your index design to make the application works
> better, you can even over-index your structure, but this will slow down
> insertions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
Eli wrote:
> Thanks for the quick response.
> Can you direct me to a link of how to implement the command by code ?
> I'm working with C#, and need to send the data from the code.
> Do you know of a way to insert the data has an object?
> I mean, the rows I want to insert are allready in a class/struct
> format, and sending them as-is to the database, insetd of parsing
> them to insert command, will be, I think,a fast way. What do you
> think?
>
unfortunately BULK operations only supports flat data format and not
structured formatted data... you can use CSV, native SQL Server exported
data via BCP and so on, but not data from xml files and the like...
BULK operations accept a (txt) file to be imported, as described in
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Best way to do a dynamic bulk insert to a table

My current project is creating a social network for the university I work for. One of the features allows members of a group to send a message to all other group members. Currently, I run a foreach loop over each of the group members, and run a separate INSERT statement to insert a message into my messages table. Once the group has several hundreds members, everybody starts getting timeout errors. What is the best way to do this?

Here are two suggestions I've received: construct one sql statement that would contain multiple INSERT statements. It would be a large statement like:

INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user, @.to_user, @.subject, @.body); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user2, @.to_user2, @.subject2, @.body2); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user3, @.to_user3, @.subject3, @.body3);

etc...

Or, do the foreach loop in a stored procedure. I know the pros and cons of sprocs versus dynamic sql is a sticky subject, and, personally, I'd prefer to keep my logic in the C# code-behind file. What is the best way to do this is an efficient manner? I'd be happy to share some code, if that would help. Thanks for your input!

I think what you want is something like this. 1 single query.

INSERT into [messages] (from_user, to_user, subject, body)
SELECT @.from_user,userid,@.subject,@.body FROM groupmembers WHERE groupid=@.groupid AND userid<>@.from_user

Your input parameters:

@.from_user = the sending user
@.subject = the subject
@.body = the body
@.groupid=the recieving groupid

|||

If you going to insert data to a table that alreay has some data, I suggest you to use whatgunteman wrote.

If not, I suggest you to use:

1SELECT *2INTO MyNewTable-- it will be created automatically here3FROM MyTable1 t14INNERJOIN5 MyTable2 t26ON (t1.rid = t2.rid)7

Good luck.

|||

Thanks that worked like a charm. I can't believe I never thought of putting a subquery in an insert statement.

Best way to create dynamic update statement

In general, What is the best approach in creating a dynamic update
stored procedure, that can handle recieving varying input paramters and
update the approporiate columns.Depends on the requirements but one possibility is to use NULL
parameters to represent values that shouldn't be changed:

UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

--
David Portas
SQL Server MVP
--|||>> In general, What is the best approach in creating a dynamic update
stored procedure, <<

In general, building dynamic is a bad idea. It says that you don't
know what you are doing, so you are turning over control of the system
at runtime to any random user, present or future. SQL is a compiled
language, not like BASIC.|||what would be wrong with using:
UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

if i want to have one stored procedure to update a table.|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||I think this is just some confusion over terminology. The term "dynamic
update" or "dynamic code" refers to code that references metadata
(usually table and column names) dynamically - elements of the code
being constructed at runtime. This is not generally good practice for
various reasons to do with performance, security, maintainability and
modular design. In your case however, no dynamic code is necessary.

--
David Portas
SQL Server MVP
--

Best way to create a remote database and data within it?

Is there any tool that will allow me to create a script for creating a database with all its objects like triggers, indexes, relationships etc. and also populating the tables with data ? So far I haven't found any.

Enterprise Manager will easily let you script all the objects. Right click on an object and select All Tasks and then Generate SQL Script. You can then elect to generate scripts for all objects.

You can use a third party tool to script the data (I have usedRed Gate's SQL Data Compare). Or you could generate scripts using a SELECT statement if you really wanted to.

Best way for the performance

I would like to know which way is better for the performance of my
system:
Creating a huge table with more than 5 000 000 000 rows or multiple
tables with the same structure containing 5 000 000 rows?
Here the context of our application:
Our application offers the possibility to create up to 3000 projects.
Each project contains a list of tags and a list of value-timestamp for
each tag.
So, a project can contain up to 500 tags with a maximum of 5000 values
for each tag. In a hypothetical scenario where everything is at
maximun, only one table containing every value-timestamp would be huge
with more than 7 500 000 000 rows.
My first thought was to create a table containning the values for each
project.
Project
=========== Project_ID - PK
ValDataName_ID
... etc...
ValDataName
=========== ValDataName_ID - PK
TableName (represents the ValDataXXX)
Project_ID
ValDataXXX where XXX is an unique id
=========== ValDataXXX_ID - PK
Tag_ID
TagVal
TagTime
So, I would have a table (ValDataName) containing the names of each
ValData table linked to the project and a table containing my projects.
Then, I would have up to 5000 tables of type ValData.
Since my ValData tables are completely independant, any action
affecting one table does not affect the other.
So, which way would be the better approach?
Thanks.Francis B. wrote:
> I would like to know which way is better for the performance of my
> system:
> Creating a huge table with more than 5 000 000 000 rows or multiple
> tables with the same structure containing 5 000 000 rows?
> Here the context of our application:
> Our application offers the possibility to create up to 3000 projects.
> Each project contains a list of tags and a list of value-timestamp for
> each tag.
> So, a project can contain up to 500 tags with a maximum of 5000 values
> for each tag. In a hypothetical scenario where everything is at
> maximun, only one table containing every value-timestamp would be huge
> with more than 7 500 000 000 rows.
> My first thought was to create a table containning the values for each
> project.
> Project
> ===========> Project_ID - PK
> ValDataName_ID
> ... etc...
> ValDataName
> ===========> ValDataName_ID - PK
> TableName (represents the ValDataXXX)
> Project_ID
> ValDataXXX where XXX is an unique id
> ===========> ValDataXXX_ID - PK
> Tag_ID
> TagVal
> TagTime
> So, I would have a table (ValDataName) containing the names of each
> ValData table linked to the project and a table containing my projects.
> Then, I would have up to 5000 tables of type ValData.
> Since my ValData tables are completely independant, any action
> affecting one table does not affect the other.
> So, which way would be the better approach?
> Thanks.
You didn't mention one very important piece of information. What
version of SQL Server are you using? SQL Server 2005 has a lot of new
scalability features, especially table partitioning. For a major new
application I assume SQL Server 2005 will be the obvious choice for
you.
Achieving a scalable solution will depend very much on the design of
your indexing, queries, your application and your hardware
implementation. You've told us nothing of those. I can only suggest
that creating new tables in a bid to improve performance should
generally be a long way down your list of options. Usually there are
much more effective ways to optimise performance. More likely you might
choose to implement horizontal partitioning in order to make certain
admin tasks easier. How you do that depends a lot on the version and
edition you are using.
My other suggestion would be that you rethink very, very carefully
about logical design. When I see tables with names like "ValDataXXX"
and columns with names like "Tag_ID" and "TagVal" I just get an a awful
sinking feeling that this is some naive data-model-on-the-fly kind of
solution - what is sometimes euphemistically called an "EAV" model. If
I'm right then you can probably say goodbye to scalability, performance
and data integrity. Your database will have none of them.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 19 Sep 2006 12:59:25 -0700, "Francis B."
<francisbeliveau@.gmail.com> wrote:
>I would like to know which way is better for the performance of my
>system:
>Creating a huge table with more than 5 000 000 000 rows or multiple
>tables with the same structure containing 5 000 000 rows?
>Here the context of our application:
>Our application offers the possibility to create up to 3000 projects.
>Each project contains a list of tags and a list of value-timestamp for
>each tag.
No way should a project management system have 10^9 rows.
Beyond that, I second what David has already said.
J.