Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!
Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter table statement

When you issue an alter table statement to add columns it will add them at
the end of the table. Is it possible to specify a column id and tell it
where you want the column added?
Say you have a table with 10 columns and you want to alter the table and add
a column into the middle of the table. Can you alter the table and specify
colid 5?
Any help is appreciated.Andy wrote:
> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and a
dd
> a column into the middle of the table. Can you alter the table and specif
y
> colid 5?
> Any help is appreciated.
No. You have to drop the table and recreate it. Column order matters
but if you have good programming practices (avoid SELECT * for example)
then it will not matter too much.
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
--|||Hi
I do this sort of stuff in enterprise manger, I believe that behind the
sceans this creates your new table and then insert rows from the old table s
o
I suppose that the DDL has to be hand crafted if you choose to use the alter
table
"Andy" wrote:

> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and a
dd
> a column into the middle of the table. Can you alter the table and specif
y
> colid 5?
> Any help is appreciated.|||Hi Andy
There is no such thing as the 'middle of the table'. On the actual physical
pages where your data rows are stored, the order of columns might have no
relationship to the order in which the columns were created, or the order
they are returned when you SELECT *
If you want the columns returned in a particular order, list them in that
order. It is never a good idea to use SELECT *, for many reasons.
If you really really really want a column to be returned in a particular
position when you SELECT *, you'll have to re-create the table. That is what
the Enterprise Manager does behind the scenes and it can be a LOT of work
for a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:2AEA34D6-E743-4E16-A3C0-9DDA2F12A8B5@.microsoft.com...
> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and
> add
> a column into the middle of the table. Can you alter the table and
> specify
> colid 5?
> Any help is appreciated.|||Kalen Delaney (replies@.public_newsgroups.com) writes:
> There is no such thing as the 'middle of the table'. On the actual
> physical pages where your data rows are stored, the order of columns
> might have no relationship to the order in which the columns were
> created, or the order they are returned when you SELECT *
I guess that Andy means "in the middle of the table definition". And
at least my table definitions have a middle, and then they have a top
and a bottom or a left and a right, depending on how I look at them.

> If you want the columns returned in a particular order, list them in that
> order. It is never a good idea to use SELECT *, for many reasons.
SELECT * does not belong in application code, but I gladly admit that
I type SELECT * many times a day for ad hoc queries for testing, debugging
and supporting issues. For me it's very important to have colunms in
some sort of logical order.
There is also the important relation to the database documentation. Our
system have a long history, and reading table descriptions where columns
appear in historical order would make matters even worse for new kids
in our block.
Most database design tools permits to add new columns anywhere in
the table (sic!), so it's a shortcoming of SQL of not supplying that
syntax, but forcing people to the risky path creating a new table
and move over data. (It's risky, because there are a couple of errors
you can make. Just look at EM - it has a couple of bugs with its
scripts.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alter table and column order

Is it possible to add a column to a table using the "alter table"
statement and specify where in the sequence of columns the new column
sits. If not is there any way to alter the order of columns using TSQL
rather than Enterprise Manager / Design Table.

TIA

Laurence BreezeNo and no.

The physical column order should only be significant when you use SELECT * -
and you shouldn't use SELECT * in production code. List the columns in your
SELECT statements in whatever order you want them. Alternatively, create a
view over the table with the columns in the required order.

Your other option is to create a new table, populate it from the original
table and then rename it. This is what Enterprise Manager does behind the
scenes.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> No and no.
> The physical column order should only be significant when you use SELECT
> * - and you shouldn't use SELECT * in production code. List the columns
> in your SELECT statements in whatever order you want them.

However, in support or debug situations, SELECT * is very convenient to
use. And in this case, I find it important that columns are in some
reasonable order. And historic order is rarely reasonable.

Furthermore, if you want to move data between databases, it is far
simpler if columns are in the same order in both databases, as this
makes bulk-copying easier.

One should also not ignore the documenation aspect of it. When you read
the documentation of a 50-column table, do you prefer to have the columns
in historic order, or do you prefer some logic order with the primary
key first, and related columns close to each other.

So, while there is no support to insert a column in the middle other
than creating a new table and move data over, it is certainly a valid
question. Column order does matter!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Alter Table Alter Column sometimes fails when changing to Not

Hi Martin,
I think that solves my problem -- perhaps slightly indirectly. In the Alter
statements I did not specify the datatype because I was not changing it.
Perhaps the statements defaulted to something other than the float(8) of the
original columns?
I'll try explicitly repeating the current datatype and see if that makes the
message disappear.
Follow-up question: If I drop the offending "DF__Temporary__..." constraints
do they get recreated automatically?
Thanks much and Best regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> I think the error message raises when you change a column definition from
> float(8) to int, that is having a default value of data type "float(8)".
> Use "sp_help tblVendQuotePrice" to check the constraints on the table
> tblVendQuotePrice.
> Check the constraint_type and constraint_keys columns from the last result
> as returned from sp_help, and check the default values.
> Upon altering the column definition , you will need to drop the default
> constraint "DF__Temporary__VQuot__22B77893" when the data type of the colu
mn
> does not match the data type of the default value.
> ALTER TABLE tblVendQuotePrice DROP CONSTRAINT DF__Temporary__VQuot__22B778
93
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@.microsoft.com ¤¤???g...
> tables:
> the
>
>> In the Alter
> statements I did not specify the datatype because I was not changing it.

I see datatype 'int' as specified in the ALTER TABLE statement from original
post. Perhaps the wrong datatype (int instead of float) was inadvertently
specified.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
There is no automatic recreation of constraints using Transact-SQL scripts.
If you want to change the column datatype, you need to drop constraints
referencing the column, alter the column and then recreate the constraints.
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
> Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
> the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
> the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>|||Please refer to BOL for more information.
- CREATE DEFAULT
- sp_binddefault
- sp_unbinddefault
- DROP DEFAULT
When you create a column with a default value (either by using CREATE
TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
"default" automatically. This default object will then bound to a column.
To verify this, you can obtain the list of default objects from the
following query. You can find your "DF__Temporary__..." default objects from
the result.
SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
name
You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
After creating the default objects, you can use sp_binddefault and
sp_unbinddefault to bind/unbind the default objects to your columns.
For the current case, the "DF__Temporary__..." default objects will *not* be
binded to your columns automatically.
You will need to create a default object (using CREATE DEFAULT) and bind it
to the column (using sp_binddefault).
Martin C K Poon
Senior Analyst Programmer
====================================
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> bl
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com g...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>
from
result
column
DF__Temporary__VQuot__22B77893[color=dar
kred]
fails
make
objects
explicitly
error.|||Thanks much, Martin.
Best Regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> Please refer to BOL for more information.
> - CREATE DEFAULT
> - sp_binddefault
> - sp_unbinddefault
> - DROP DEFAULT
> When you create a column with a default value (either by using CREATE
> TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
> "default" automatically. This default object will then bound to a column.
> To verify this, you can obtain the list of default objects from the
> following query. You can find your "DF__Temporary__..." default objects fr
om
> the result.
> SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
> name
> You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
> After creating the default objects, you can use sp_binddefault and
> sp_unbinddefault to bind/unbind the default objects to your columns.
> For the current case, the "DF__Temporary__..." default objects will *not*
be
> binded to your columns automatically.
> You will need to create a default object (using CREATE DEFAULT) and bind i
t
> to the column (using sp_binddefault).
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com ¤¤???g...
> Alter
> the
> the
> constraints
> from
> result
> column
> DF__Temporary__VQuot__22B77893
> fails
> make
> objects
> explicitly
> error.
>
>

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).