Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>
Showing posts with label define. Show all posts
Showing posts with label define. Show all posts
Friday, March 30, 2012
question about secondary file
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>sql
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>sql
question about secondary file
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>
Friday, March 23, 2012
question about numeric type
Hi,
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
ThanksThose are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numer
ic
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
>|||How many bytes on disk a value of that datatype requires.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...[vbcol=seagreen]
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
>|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>|||No, that is now how it work. Compare to int vs. smallint, for instance. You
would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A large
r storage area can hold
a larger *span* of values. We are not really talking about digits, we are ta
lking about the values
that these digits represent. The values can be represented as binary, 10 bas
e decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
ThanksThose are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numer
ic
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
>|||How many bytes on disk a value of that datatype requires.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...[vbcol=seagreen]
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
>|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>|||No, that is now how it work. Compare to int vs. smallint, for instance. You
would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A large
r storage area can hold
a larger *span* of values. We are not really talking about digits, we are ta
lking about the values
that these digits represent. The values can be represented as binary, 10 bas
e decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>
question about numeric type
Hi,
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
ThanksThose are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numeric
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
> > Hi,
> >
> > I am kind of confused regarding the numeric data type. If I define a numeric
> > column to be precision 38 (the maximum allowed), in EM the column length
> > automatically turns to be 17. What's the relationship between 38 and 17?
> > Thanks
>|||How many bytes on disk a value of that datatype requires.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
>> Those are storage bytes, lookup decimal and numeric in BOL
>> Precision Storage bytes
>> 1 - 9 5
>> 10-19 9
>> 20-28 13
>> 29-38 17
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> Jen wrote:
>> > Hi,
>> >
>> > I am kind of confused regarding the numeric data type. If I define a numeric
>> > column to be precision 38 (the maximum allowed), in EM the column length
>> > automatically turns to be 17. What's the relationship between 38 and 17?
>> > Thanks
>>|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > what is Storage bytes? Thanks
> >
> > "SQL Menace" wrote:
> >
> >> Those are storage bytes, lookup decimal and numeric in BOL
> >>
> >> Precision Storage bytes
> >> 1 - 9 5
> >> 10-19 9
> >> 20-28 13
> >> 29-38 17
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
> >>
> >> Jen wrote:
> >> > Hi,
> >> >
> >> > I am kind of confused regarding the numeric data type. If I define a numeric
> >> > column to be precision 38 (the maximum allowed), in EM the column length
> >> > automatically turns to be 17. What's the relationship between 38 and 17?
> >> > Thanks
> >>
> >>
>|||No, that is now how it work. Compare to int vs. smallint, for instance. You would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A larger storage area can hold
a larger *span* of values. We are not really talking about digits, we are talking about the values
that these digits represent. The values can be represented as binary, 10 base decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>> How many bytes on disk a value of that datatype requires.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>> > what is Storage bytes? Thanks
>> >
>> > "SQL Menace" wrote:
>> >
>> >> Those are storage bytes, lookup decimal and numeric in BOL
>> >>
>> >> Precision Storage bytes
>> >> 1 - 9 5
>> >> 10-19 9
>> >> 20-28 13
>> >> 29-38 17
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>> >>
>> >> Jen wrote:
>> >> > Hi,
>> >> >
>> >> > I am kind of confused regarding the numeric data type. If I define a numeric
>> >> > column to be precision 38 (the maximum allowed), in EM the column length
>> >> > automatically turns to be 17. What's the relationship between 38 and 17?
>> >> > Thanks
>> >>
>> >>
>>|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
> > How many bytes on disk a value of that datatype requires.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > > what is Storage bytes? Thanks
> > >
> > > "SQL Menace" wrote:
> > >
> > >> Those are storage bytes, lookup decimal and numeric in BOL
> > >>
> > >> Precision Storage bytes
> > >> 1 - 9 5
> > >> 10-19 9
> > >> 20-28 13
> > >> 29-38 17
> > >>
> > >> Denis the SQL Menace
> > >> http://sqlservercode.blogspot.com/
> > >>
> > >>
> > >>
> > >> Jen wrote:
> > >> > Hi,
> > >> >
> > >> > I am kind of confused regarding the numeric data type. If I define a numeric
> > >> > column to be precision 38 (the maximum allowed), in EM the column length
> > >> > automatically turns to be 17. What's the relationship between 38 and 17?
> > >> > Thanks
> > >>
> > >>
> >
> >
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
ThanksThose are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numeric
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
> > Hi,
> >
> > I am kind of confused regarding the numeric data type. If I define a numeric
> > column to be precision 38 (the maximum allowed), in EM the column length
> > automatically turns to be 17. What's the relationship between 38 and 17?
> > Thanks
>|||How many bytes on disk a value of that datatype requires.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
>> Those are storage bytes, lookup decimal and numeric in BOL
>> Precision Storage bytes
>> 1 - 9 5
>> 10-19 9
>> 20-28 13
>> 29-38 17
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> Jen wrote:
>> > Hi,
>> >
>> > I am kind of confused regarding the numeric data type. If I define a numeric
>> > column to be precision 38 (the maximum allowed), in EM the column length
>> > automatically turns to be 17. What's the relationship between 38 and 17?
>> > Thanks
>>|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > what is Storage bytes? Thanks
> >
> > "SQL Menace" wrote:
> >
> >> Those are storage bytes, lookup decimal and numeric in BOL
> >>
> >> Precision Storage bytes
> >> 1 - 9 5
> >> 10-19 9
> >> 20-28 13
> >> 29-38 17
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
> >>
> >> Jen wrote:
> >> > Hi,
> >> >
> >> > I am kind of confused regarding the numeric data type. If I define a numeric
> >> > column to be precision 38 (the maximum allowed), in EM the column length
> >> > automatically turns to be 17. What's the relationship between 38 and 17?
> >> > Thanks
> >>
> >>
>|||No, that is now how it work. Compare to int vs. smallint, for instance. You would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A larger storage area can hold
a larger *span* of values. We are not really talking about digits, we are talking about the values
that these digits represent. The values can be represented as binary, 10 base decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>> How many bytes on disk a value of that datatype requires.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>> > what is Storage bytes? Thanks
>> >
>> > "SQL Menace" wrote:
>> >
>> >> Those are storage bytes, lookup decimal and numeric in BOL
>> >>
>> >> Precision Storage bytes
>> >> 1 - 9 5
>> >> 10-19 9
>> >> 20-28 13
>> >> 29-38 17
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>> >>
>> >> Jen wrote:
>> >> > Hi,
>> >> >
>> >> > I am kind of confused regarding the numeric data type. If I define a numeric
>> >> > column to be precision 38 (the maximum allowed), in EM the column length
>> >> > automatically turns to be 17. What's the relationship between 38 and 17?
>> >> > Thanks
>> >>
>> >>
>>|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
> > How many bytes on disk a value of that datatype requires.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > > what is Storage bytes? Thanks
> > >
> > > "SQL Menace" wrote:
> > >
> > >> Those are storage bytes, lookup decimal and numeric in BOL
> > >>
> > >> Precision Storage bytes
> > >> 1 - 9 5
> > >> 10-19 9
> > >> 20-28 13
> > >> 29-38 17
> > >>
> > >> Denis the SQL Menace
> > >> http://sqlservercode.blogspot.com/
> > >>
> > >>
> > >>
> > >> Jen wrote:
> > >> > Hi,
> > >> >
> > >> > I am kind of confused regarding the numeric data type. If I define a numeric
> > >> > column to be precision 38 (the maximum allowed), in EM the column length
> > >> > automatically turns to be 17. What's the relationship between 38 and 17?
> > >> > Thanks
> > >>
> > >>
> >
> >
question about numeric type
Hi,
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
Thanks
Those are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numeric
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks
|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
>
|||How many bytes on disk a value of that datatype requires.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...[vbcol=seagreen]
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>
|||No, that is now how it work. Compare to int vs. smallint, for instance. You would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A larger storage area can hold
a larger *span* of values. We are not really talking about digits, we are talking about the values
that these digits represent. The values can be represented as binary, 10 base decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
Thanks
Those are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numeric
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks
|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
>
|||How many bytes on disk a value of that datatype requires.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...[vbcol=seagreen]
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>
|||No, that is now how it work. Compare to int vs. smallint, for instance. You would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A larger storage area can hold
a larger *span* of values. We are not really talking about digits, we are talking about the values
that these digits represent. The values can be represented as binary, 10 base decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:[vbcol=seagreen]
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
Monday, March 12, 2012
question about designing tables
Hi,
I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:
[prodID][qty][price]
That is to attch a collection of products to one order entry.
Thank you,
shlomi711Then you should create a table that looks like this:
(RowID Integer Identity) - Not really necessary, but I like to have unique row number
OrderID Integer - The order number (to which the details are connected)
prodID Integer - The product ID
qty money - the amount of products
price money - the price of the products.
Regards
Fredrik
question about delete statement
Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !Can you give more details on what you are trying to do? I suspect that there are better ways to accomplish whatever you need to do.
To answer your question as it was posted, you could create a trigger on the T2 table that updated the T1 table appropriately. I'm a little vague on the implementation details because it seems that you are looking for more than a simple counter, and you haven't provided a lot of details on what you want to happen.
-PatP|||CREATE TRIGGER myTriggerForDelete
ON T2
AFTER DELETE
AS
UPDATE T1
SET C1=COALESCE((SELECT MIN(C1) FROM T1),0)-1|||It should be something like my previous post.
But as Pat said, I don't think this makes senses, maybe you should give us more explanations.
Hope it can helps you|||please go to books online and read the article on CREATE TRIGGER and pay special attention to the section on the inserted and deleted tables.|||Are you trying to prevent "holes" in a meaningless "key"|||Are you trying to prevent "holes" in a meaningless "key"
If it's the case you should read that:
http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !Can you give more details on what you are trying to do? I suspect that there are better ways to accomplish whatever you need to do.
To answer your question as it was posted, you could create a trigger on the T2 table that updated the T1 table appropriately. I'm a little vague on the implementation details because it seems that you are looking for more than a simple counter, and you haven't provided a lot of details on what you want to happen.
-PatP|||CREATE TRIGGER myTriggerForDelete
ON T2
AFTER DELETE
AS
UPDATE T1
SET C1=COALESCE((SELECT MIN(C1) FROM T1),0)-1|||It should be something like my previous post.
But as Pat said, I don't think this makes senses, maybe you should give us more explanations.
Hope it can helps you|||please go to books online and read the article on CREATE TRIGGER and pay special attention to the section on the inserted and deleted tables.|||Are you trying to prevent "holes" in a meaningless "key"|||Are you trying to prevent "holes" in a meaningless "key"
If it's the case you should read that:
http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml
Saturday, February 25, 2012
Question about "Data Transformation Services " : How to define myselfs query without DTS W
hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS
the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard.
but my requirement seems can't be done by DTS wizard.
here are my requirement below.
[move online Database to offline Database ]
1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..)
2. we will have only 2 kind of preserve time one is 6 months another is 12 months
3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data
4. We will have to reference the history data preserve time to delete data after finished data movement
those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?
thank you very much and have a nice day
anyone ? please help...
|||Hi,
Is there any field (such like datetime) in your datatable which can identify the time of the records. If so, you can use a query to specify the data to transfer. When you run your wizard, choose "use a query to transfer" item in "Specify table copy or query" step. Click on QueryBuilder button to build your filter sql statement and create your query criteria.(datetime filed) In this way, you can transform the data by datetime condition.
Hope that helps. Thanks.
|||hi thank you is the only way ( run wizard) to finish this job?
could it be done by write a program? thank you
|||Hi,
Of cause you can write programs to achieve your goal. Just filter the data according to the datetime condition and insert the data into your local(or backup) data server.But actually it's much complicated I think. Since we have a such a tool to ease our work, why don't we enjoy it
Thanks.
Subscribe to:
Posts (Atom)