hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.
Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts
Monday, March 26, 2012
Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field
hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.sql
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.sql
Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field
hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.
>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
>> hi,
>> i have a table with IDENTITY field.
>> i set a CLUSTERED UNIQUE index on this field.
>> i wonder whether is it right to set this index's Fill-
>> factor to 100?
>> i though that if the values of this field are always
auto-
>> incremented so there would never be a need to split
places
>> between records, so there is no need to save space for
new
>> records(or updated records) that might need to be placed
>> between any other prior inserted records.
>> is it true?
>> is it the optimal choise for this case?
>> thanks,
>> edo.
>
>.
>|||Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
> >hi,
> >
> >thanks for replying me.
> >
> >increasing (decreasing too?) the WIDTH of a row because of
> >an update is a new information for me, and i would also
> >say it is quite shocking for me.
> >
> >i allways thought table structure is a fixed-width, so the
> >space for all of the fields is allocated in the same time
> >and PLACE in advance for each inserted record.
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> >i guess it's a long story to explain why a clustered index
> >is actually needs to contain ALL fileds ...?
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.
>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
>> hi,
>> i have a table with IDENTITY field.
>> i set a CLUSTERED UNIQUE index on this field.
>> i wonder whether is it right to set this index's Fill-
>> factor to 100?
>> i though that if the values of this field are always
auto-
>> incremented so there would never be a need to split
places
>> between records, so there is no need to save space for
new
>> records(or updated records) that might need to be placed
>> between any other prior inserted records.
>> is it true?
>> is it the optimal choise for this case?
>> thanks,
>> edo.
>
>.
>|||Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
> >hi,
> >
> >thanks for replying me.
> >
> >increasing (decreasing too?) the WIDTH of a row because of
> >an update is a new information for me, and i would also
> >say it is quite shocking for me.
> >
> >i allways thought table structure is a fixed-width, so the
> >space for all of the fields is allocated in the same time
> >and PLACE in advance for each inserted record.
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> >i guess it's a long story to explain why a clustered index
> >is actually needs to contain ALL fileds ...?
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
Friday, February 24, 2012
File name as yesterday's date
I'm running a DTS daily that needs to create a file with a unique name, as
yesterday's date. (Ex: 20050223)
I figured out how to get the date part of it:
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
but I can't seem to get it to create a table with that unique name each day.
Any ideas?
CREATE TABLE 'Date'
Ed>> I'm running a DTS daily that needs to create a file [sic] with a
unique name, as yesterday's date. (Ex: 2005-02-23) <<
No. no. no.
1) You have just re-discovered the old IBM tape system management
convention, which used a "yyddd' numbering scheme on the tape labels.
It was one of the thing that blew up in the Y2K disaster. You are over
50 years behind current IT standard practices.
2) We do not alter a schema by adding new tables without a good
reason. Altering a schema on a daily basis is sure sign of a major
design flaw.
3) This design flaw is called attribute splitting. you have taken the
value of a temporal attribute (date of creation) and split it into
separate tables. Would you keep a personnel table in two parts, male
and female employees? Of course not. You need one table with a date
column in it.|||Create an os file or a table?
***** OS File *****
Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?292
***** TABLE *****
Use dynamic sql.
Example:
declare @.tn sysname
declare @.sql nvarchar(4000)
set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
execute sp_executesql @.sql
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Edo" wrote:
> I'm running a DTS daily that needs to create a file with a unique name, as
> yesterday's date. (Ex: 20050223)
> I figured out how to get the date part of it:
> CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
> but I can't seem to get it to create a table with that unique name each da
y.
> Any ideas?
> CREATE TABLE 'Date'
> Ed|||Thanks for your help on that matter.
But how would this apply to inserting, especially when appling sums and
inner joins.
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
Select COLMAT_TRACK_EMPS.EMPLOYEE_NAME,
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'H' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'H',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'M' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'M',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'P' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'P',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'A' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'A',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'V' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'V',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'L' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'L',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'S' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'S',
SUM (TRACK_DETAIL_CI.QTY) AS QTYSUM
FROM COLMAT_TRACK_EMPS INNER JOIN
TRACK_DETAIL_CI ON COLMAT_TRACK_EMPS.EMPLOYEE_NO =
TRACK_DETAIL_CI.EMPLOYEE_NO
INNER JOIN ITEM_MAST ON ITEM_MAST.EDP_NO = TRACK_DETAIL_CI.EDP_NO
WHERE TRACK_DETAIL_CI.TRANS_DATE =
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
GROUP BY COLMAT_TRACK_EMPS.EMPLOYEE_NAME
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
SELECT 'TOTAL' AS EMPLOYEE_NAME, SUM (EDS_TRIAL.H), SUM (EDS_TRIAL.M), SUM
(EDS_TRIAL.P), SUM (EDS_TRIAL.A), SUM (EDS_TRIAL.V), SUM (EDS_TRIAL.L), SUM
(EDS_TRIAL.S), SUM (EDS_TRIAL.QTYSUM)
FROM CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
"Alejandro Mesa" wrote:
> Create an os file or a table?
> ***** OS File *****
> Working with files and the FileSystemObject
> http://www.sqldts.com/default.aspx?292
> ***** TABLE *****
> Use dynamic sql.
> Example:
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
> set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
> execute sp_executesql @.sql
> go
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
> "Edo" wrote:
>
yesterday's date. (Ex: 20050223)
I figured out how to get the date part of it:
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
but I can't seem to get it to create a table with that unique name each day.
Any ideas?
CREATE TABLE 'Date'
Ed>> I'm running a DTS daily that needs to create a file [sic] with a
unique name, as yesterday's date. (Ex: 2005-02-23) <<
No. no. no.
1) You have just re-discovered the old IBM tape system management
convention, which used a "yyddd' numbering scheme on the tape labels.
It was one of the thing that blew up in the Y2K disaster. You are over
50 years behind current IT standard practices.
2) We do not alter a schema by adding new tables without a good
reason. Altering a schema on a daily basis is sure sign of a major
design flaw.
3) This design flaw is called attribute splitting. you have taken the
value of a temporal attribute (date of creation) and split it into
separate tables. Would you keep a personnel table in two parts, male
and female employees? Of course not. You need one table with a date
column in it.|||Create an os file or a table?
***** OS File *****
Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?292
***** TABLE *****
Use dynamic sql.
Example:
declare @.tn sysname
declare @.sql nvarchar(4000)
set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
execute sp_executesql @.sql
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Edo" wrote:
> I'm running a DTS daily that needs to create a file with a unique name, as
> yesterday's date. (Ex: 20050223)
> I figured out how to get the date part of it:
> CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
> but I can't seem to get it to create a table with that unique name each da
y.
> Any ideas?
> CREATE TABLE 'Date'
> Ed|||Thanks for your help on that matter.
But how would this apply to inserting, especially when appling sums and
inner joins.
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
Select COLMAT_TRACK_EMPS.EMPLOYEE_NAME,
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'H' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'H',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'M' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'M',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'P' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'P',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'A' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'A',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'V' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'V',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'L' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'L',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'S' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'S',
SUM (TRACK_DETAIL_CI.QTY) AS QTYSUM
FROM COLMAT_TRACK_EMPS INNER JOIN
TRACK_DETAIL_CI ON COLMAT_TRACK_EMPS.EMPLOYEE_NO =
TRACK_DETAIL_CI.EMPLOYEE_NO
INNER JOIN ITEM_MAST ON ITEM_MAST.EDP_NO = TRACK_DETAIL_CI.EDP_NO
WHERE TRACK_DETAIL_CI.TRANS_DATE =
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
GROUP BY COLMAT_TRACK_EMPS.EMPLOYEE_NAME
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
SELECT 'TOTAL' AS EMPLOYEE_NAME, SUM (EDS_TRIAL.H), SUM (EDS_TRIAL.M), SUM
(EDS_TRIAL.P), SUM (EDS_TRIAL.A), SUM (EDS_TRIAL.V), SUM (EDS_TRIAL.L), SUM
(EDS_TRIAL.S), SUM (EDS_TRIAL.QTYSUM)
FROM CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
"Alejandro Mesa" wrote:
> Create an os file or a table?
> ***** OS File *****
> Working with files and the FileSystemObject
> http://www.sqldts.com/default.aspx?292
> ***** TABLE *****
> Use dynamic sql.
> Example:
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
> set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
> execute sp_executesql @.sql
> go
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
> "Edo" wrote:
>
Subscribe to:
Posts (Atom)