Defragging Tools?

Has anyone got any top tips, or a link to a tool that can help me with
defragging the drive my SQL Server 2K files sit on?
Something that can run on a timer, or be called by scheduled tasks would be
cool.
Thanks
PeterHow about Diskeeper from Executive Software
I think you will need to stop the services to run an External(to SQL Server
defrag) as the files may be passed over because they are locked by SQL
Server itself.
Also look at internal fagmentation
http://www.sql-server-performance.com/rd_index_fragmentation.asp
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would
be
> cool.
> Thanks
> Peter
>|||In addition to Allan's reply:
If this is a dedicated SQL Server then you shouldn't have to defrag (assuming you don't abuse
autogrow and shrink, of course).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would be
> cool.
> Thanks
> Peter
>

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>|||A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J
>--Original Message--
>I need to know if there are any good tools available to
defrag/reindex
>SQL2000 databases? I have to manage my own server and I
am not a SQL guru. I
>run Diskkeeper to take care of the physical files, but I
need an easy and
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I need to know if there are any good tools available to defrag/reindex
> > SQL2000 databases? I have to manage my own server and I am not a SQL
guru.
> I
> > run Diskkeeper to take care of the physical files, but I need an easy
and
> > effective way to defrag/reindex the SQL databases.
> >
> > Thanks,
> > Derek
> >
> >
>|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi Derek,
> >
> > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
> the
> > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
run
> > them both. DBRINDEX will take out locks on your tables though, so you
can
> > only run it when your database is not in use or very quiet.
> >
> > You can run DBCC DBREINDEX on all tables with:
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > And you can create a script to defrag all indexes with:
> > SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
> (id)
> > + ''''+ CHAR(13) +
> > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> > indid) + ')' + CHAR(13) +
> > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 254
> > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > ORDER BY OBJECT_NAME(id), indid
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Derek" <derekb@.nospamderekb.com> wrote in message
> > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > I need to know if there are any good tools available to defrag/reindex
> > > SQL2000 databases? I have to manage my own server and I am not a SQL
> guru.
> > I
> > > run Diskkeeper to take care of the physical files, but I need an easy
> and
> > > effective way to defrag/reindex the SQL databases.
> > >
> > > Thanks,
> > > Derek
> > >
> > >
> >
> >
>|||Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> >--Original Message--
> >I need to know if there are any good tools available to
> defrag/reindex
> >SQL2000 databases? I have to manage my own server and I
> am not a SQL guru. I
> >run Diskkeeper to take care of the physical files, but I
> need an easy and
> >effective way to defrag/reindex the SQL databases.
> >
> >Thanks,
> >Derek
> >
> >
> >.
> >|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
> indexes that are being reindexed though, which might be inconvenient if
the
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks. Just to make sure, I would ut the following into Query Analyzer
> and
> > run:
> >
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > Also, How do I make my databases offline to run?
> >
> > Thanks much!
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi Derek,
> > >
> > > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to
defragment
> > the
> > > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
> run
> > > them both. DBRINDEX will take out locks on your tables though, so you
> can
> > > only run it when your database is not in use or very quiet.
> > >
> > > You can run DBCC DBREINDEX on all tables with:
> > > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> > >
> > > And you can create a script to defrag all indexes with:
> > > SELECT 'SELECT ''Defragging index ' + name + ' on table ' +
OBJECT_NAME
> > (id)
> > > + ''''+ CHAR(13) +
> > > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' +
CONVERT(VARCHAR,
> > > indid) + ')' + CHAR(13) +
> > > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 254
> > > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > > ORDER BY OBJECT_NAME(id), indid
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Derek" <derekb@.nospamderekb.com> wrote in message
> > > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > > I need to know if there are any good tools available to
defrag/reindex
> > > > SQL2000 databases? I have to manage my own server and I am not a SQL
> > guru.
> > > I
> > > > run Diskkeeper to take care of the physical files, but I need an
easy
> > and
> > > > effective way to defrag/reindex the SQL databases.
> > > >
> > > > Thanks,
> > > > Derek
> > > >
> > > >
> > >
> > >
> >
> >
>

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.

---

I

quote:

---

> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>

---

|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment

---

the

quote:

---

> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME

---

(id)

quote:

---

> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>

---

|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Thanks. Just to make sure, I would ut the following into Query Analyzer

---

and

quote:

---

> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>

---

|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...

quote:

---

> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables

---

and

quote:

---

> indexes that are being reindexed though, which might be inconvenient if

---

the

quote:

---

> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>

---

Default Values properties (table level) not working.

I am using SQL Server Management Studio Express (SSMSE) with SQL Server Express as my database tools/database to assign the ‘Default Value’ for a column at the table level.

Going over the basics… using the database tools (SSMSE) and when inserting a new row; all rows by default have a 'Null' value. Ok.
If a default value is assigned to a column (table level) using the database tools, the default value is inserted correctly if that column has a null value upon the creation of a new row. Ok.

This works fine when I am working with SSMSE on tables (inserting, deleting editing rows etc.) within my database…

But this doesn’t apply or work for adding new rows with datasets (example: using the default insert, update, delete statements provided by the wizard and using a DataGridView). My table level default values are not inserted into the new row, instead my column that had a default value assigned; now has a 'Null' value in the new row that was created by the dataset.

Isn’t a Null value is still a Null value for a new row?

Shouldn’t the database engine supply that ‘default value’ for a field that had a ‘Null’ value upon row creation?

I have always thought of a table level column ‘Default Value property’ as a trigger that tests for nulls and inserts the default value if that column has a null value when the new row is created. So I am expecting the database engine to insert the default value for that column, not the dataset when the value inserted into that column is null for a new row. I really don't need a (table level) column default property that only works with database tools for inserting new rows, that doesn't help me... totally baffled here...
Thanks

Hey Rick.

Default values will be applied to a column when NO explicit value is specified for the column in the corresponding insert (this includes a <NULL> explicit value)...so, for example, assume I have a table with 2 columns, colA and colB, and on colB I have a default value of 'colBDefault' specified...the following statement will end up with a record that includes a row with 'colAvalue' for colA, and null for colB, because I am explicitly saying to use a null value for colB:

insert table (colA, colB) select 'colAvalue', null

However, the following statement will end up with a value of 'colAvalue' for colA, and the default value of 'colBDefault' for colB, because no explicit value is specified for colB:

insert table (colA) select 'colAvalue'

I'd bet that the DataGridView is specifying all columns with a null value for anything you don't specify. To prove this, you could run a trace on the Sql server to see what the actual insert command being executed is...

HTH,

|||Hello Chad,

Thanks for the reply. That did help.

Unfortunately I couldn't get the ADO.Net trace logging to work... my tracing abilities are pretty much nil...

Another way to look at this is I am only pulling certain text fields that I want (no default value assigned) from the adapter / dataset for that table, not all of the fields from that particlular table.
The fields that I have designated a default value for are not included in the dataset, so they do not have an insert command etc. (or value assigned) for those fields for that table.
But... those fields that are *not* included in the insert statement etc. for that table do in fact show a value of 'Null' for the new row even though they have a default value assigned for them at the table level...

Example:
Fields: (ID), (LastName), (FirstName), ((Age) - default value set to 0), ((DeptNo) - default set to 100)

The adapter is only pulling fields: (ID), (LastName) and (FirstName); the insert etc. commands only pertain to those fields.
When a new row is inserted fields: (Age) and (DeptNo) do show a 'Null' value, not their assigned default value.

Thanks,
Rick

|||Whooooops...

My apologies!

It does work as you suggested!
I literally had six different forms to test things and simply got them mixed up, of what worked and what didn't!!!

Thanks,
Rick

default values in SQL Server 2005

I'm running the September CTP of SQL Server 2005 and I noticed that the way default values are scripted and displayed in the tools has changed.

In SQL Server 2000, if I create a table with an INT field with a default value of 0, it gets scripted as

FIELDNAME INT DEFAULT (0)

Enterprise Manager also shows (0). If you type in 0 for the default (no parentheses) and save, it converts it to (0) in the UI, and it will be scripted with one set of parentheses.

In SQL Server 2005 it seems to add an additional set of parentheses. Hence, if you type in 0 for the default in Management Studio, it changes it to ((0))

Furthermore, if you script the table you get
FIELDNAME INT DEFAULT ((0))

Is this change intended? I couldnt find any references in BOL.

Hi,

I've the same problem, do you find a solution?

Kind regards

|||

Why you are using CTP as already full version of SQL 2005 has been released in Nov-2005?

You can use the RTM edition and test, then install the SP1 in this case.

|||This does appear to be fixed in SP1. I dont think it was in the RTM build, and I'm not sure he's saying he's running the CTP. If you look at the time of my post, it was during the beta of SQL Server 2005, and as I recall, it was not fixed by RTM.|||True and I believe we wait until the second poster comes back.|||

I have the same issue with BIT fields on a server with SP1 applied

has this been addressed in SP2 ?

default values in SQL Server 2005

I'm running the September CTP of SQL Server 2005 and I noticed that the way default values are scripted and displayed in the tools has changed.

In SQL Server 2000, if I create a table with an INT field with a default value of 0, it gets scripted as

FIELDNAME INT DEFAULT (0)

Enterprise Manager also shows (0). If you type in 0 for the default (no parentheses) and save, it converts it to (0) in the UI, and it will be scripted with one set of parentheses.

In SQL Server 2005 it seems to add an additional set of parentheses. Hence, if you type in 0 for the default in Management Studio, it changes it to ((0))

Furthermore, if you script the table you get
FIELDNAME INT DEFAULT ((0))

Is this change intended? I couldnt find any references in BOL.

Hi,

I've the same problem, do you find a solution?

Kind regards

|||

Why you are using CTP as already full version of SQL 2005 has been released in Nov-2005?

You can use the RTM edition and test, then install the SP1 in this case.

|||This does appear to be fixed in SP1. I dont think it was in the RTM build, and I'm not sure he's saying he's running the CTP. If you look at the time of my post, it was during the beta of SQL Server 2005, and as I recall, it was not fixed by RTM.|||True and I believe we wait until the second poster comes back.|||

I have the same issue with BIT fields on a server with SP1 applied

has this been addressed in SP2 ?