Converting DateTime

Hi,
When I open a tabe in the SQL enterprise manager I see the Timestamp Field
in this Format :
24/01/2005 16:45:00

However when I'm using the Query analyzer or other SQL Client I see the
Timestamp Field in this Format :
2005-01-24 16:44:59.997

Does Anybody know what to do in order to display the Timestamp Field in
first Format (24/01/2005 16:45:00) ?

Please Advise,
Yariv

--
Message posted via http://www.sqlmonster.comLook up "Date Time String Transformation" in SQL Server BOL

"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com|||"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com

Check out CONVERT() in Books Online. But you need to remember that MSSQL
stores datetime values in an internal format, and each client (including EM
and QA) decides how to display them. See here for more details:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Yariv via SQLMonster.com (forum@.SQLMonster.com) writes:
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?

Under Tool->Options->Connections, check the third checkbox, "Use
regional settings...".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx
.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert SQL Srv 2000 Enterprise to SQL Srv Standard?

We are running SQL Srv 2000 Enterprise on a production SQL Server but are not
using the Enterprise features and want to conver to SQL Srv 2000 Standard.
Is it possible to "downgrade" without doing a full reinstall of SQL Srv?
I thought that was the answer based on what I have read but was holding out
some hope.
We currently own an Enterprise license but when we renew our yearly
agreement (We have educational pricing from MSFT), we wnat to remove that
license and use just standard.
"Immy" wrote:

> I'm afraid there is no option to downgrade.
> You'd need to backup your databases, uninstall and re-install the SQL
> server.
> Note that there is a cost different between Standard and Enterprise. Not
> sure you can get any refunds back from MS on this, so why don't you just
> keep the Ent. Edition installed if you've already paid for it. You may need
> the features in the future?
> Immy
> "jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
> news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
>
>
|||i have a similar issue
i'm thinking of moving to the developer edition
does this move require uninstall then reinstall? is there more difference
between these editions than simply the license agreement?
"jcs0277" wrote:
[vbcol=seagreen]
> I thought that was the answer based on what I have read but was holding out
> some hope.
> We currently own an Enterprise license but when we renew our yearly
> agreement (We have educational pricing from MSFT), we wnat to remove that
> license and use just standard.
> "Immy" wrote:

Convert SQL Srv 2000 Enterprise to SQL Srv Standard?

We are running SQL Srv 2000 Enterprise on a production SQL Server but are no
t
using the Enterprise features and want to conver to SQL Srv 2000 Standard.
Is it possible to "downgrade" without doing a full reinstall of SQL Srv?I'm afraid there is no option to downgrade.
You'd need to backup your databases, uninstall and re-install the SQL
server.
Note that there is a cost different between Standard and Enterprise. Not
sure you can get any refunds back from MS on this, so why don't you just
keep the Ent. Edition installed if you've already paid for it. You may need
the features in the future?
Immy
"jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
> We are running SQL Srv 2000 Enterprise on a production SQL Server but are
> not
> using the Enterprise features and want to conver to SQL Srv 2000 Standard.
> Is it possible to "downgrade" without doing a full reinstall of SQL Srv?|||I thought that was the answer based on what I have read but was holding out
some hope.
We currently own an Enterprise license but when we renew our yearly
agreement (We have educational pricing from MSFT), we wnat to remove that
license and use just standard.
"Immy" wrote:

> I'm afraid there is no option to downgrade.
> You'd need to backup your databases, uninstall and re-install the SQL
> server.
> Note that there is a cost different between Standard and Enterprise. Not
> sure you can get any refunds back from MS on this, so why don't you just
> keep the Ent. Edition installed if you've already paid for it. You may nee
d
> the features in the future?
> Immy
> "jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
> news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
>
>|||i have a similar issue
i'm thinking of moving to the developer edition
does this move require uninstall then reinstall? is there more difference
between these editions than simply the license agreement?
"jcs0277" wrote:
[vbcol=seagreen]
> I thought that was the answer based on what I have read but was holding ou
t
> some hope.
> We currently own an Enterprise license but when we renew our yearly
> agreement (We have educational pricing from MSFT), we wnat to remove that
> license and use just standard.
> "Immy" wrote:
>

Convert SQL Srv 2000 Enterprise to SQL Srv Standard?

We are running SQL Srv 2000 Enterprise on a production SQL Server but are not
using the Enterprise features and want to conver to SQL Srv 2000 Standard.
Is it possible to "downgrade" without doing a full reinstall of SQL Srv?I'm afraid there is no option to downgrade.
You'd need to backup your databases, uninstall and re-install the SQL
server.
Note that there is a cost different between Standard and Enterprise. Not
sure you can get any refunds back from MS on this, so why don't you just
keep the Ent. Edition installed if you've already paid for it. You may need
the features in the future?
Immy
"jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
> We are running SQL Srv 2000 Enterprise on a production SQL Server but are
> not
> using the Enterprise features and want to conver to SQL Srv 2000 Standard.
> Is it possible to "downgrade" without doing a full reinstall of SQL Srv?|||I thought that was the answer based on what I have read but was holding out
some hope.
We currently own an Enterprise license but when we renew our yearly
agreement (We have educational pricing from MSFT), we wnat to remove that
license and use just standard.
"Immy" wrote:
> I'm afraid there is no option to downgrade.
> You'd need to backup your databases, uninstall and re-install the SQL
> server.
> Note that there is a cost different between Standard and Enterprise. Not
> sure you can get any refunds back from MS on this, so why don't you just
> keep the Ent. Edition installed if you've already paid for it. You may need
> the features in the future?
> Immy
> "jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
> news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
> > We are running SQL Srv 2000 Enterprise on a production SQL Server but are
> > not
> > using the Enterprise features and want to conver to SQL Srv 2000 Standard.
> > Is it possible to "downgrade" without doing a full reinstall of SQL Srv?
>
>|||i have a similar issue
i'm thinking of moving to the developer edition
does this move require uninstall then reinstall? is there more difference
between these editions than simply the license agreement?
"jcs0277" wrote:
> I thought that was the answer based on what I have read but was holding out
> some hope.
> We currently own an Enterprise license but when we renew our yearly
> agreement (We have educational pricing from MSFT), we wnat to remove that
> license and use just standard.
> "Immy" wrote:
> > I'm afraid there is no option to downgrade.
> > You'd need to backup your databases, uninstall and re-install the SQL
> > server.
> > Note that there is a cost different between Standard and Enterprise. Not
> > sure you can get any refunds back from MS on this, so why don't you just
> > keep the Ent. Edition installed if you've already paid for it. You may need
> > the features in the future?
> > Immy
> >
> > "jcs0277" <jcs0277@.discussions.microsoft.com> wrote in message
> > news:D88E3328-FCA2-4ED5-B4FF-92BD0538769C@.microsoft.com...
> > > We are running SQL Srv 2000 Enterprise on a production SQL Server but are
> > > not
> > > using the Enterprise features and want to conver to SQL Srv 2000 Standard.
> > > Is it possible to "downgrade" without doing a full reinstall of SQL Srv?
> >
> >
> >

Convert Clustered Index to Non-Clustered

How do I convert an SQL Server 2000 clustered index to non-clustered?
Enterprise Manager would not let me.You drop the clustered index and create a non-clustered index with the same
keys. Note however that the clustered index IS the table and that if you
drop the clustered index you will be left with a heap (unsorted collection
of pages) for the table and that the non-clustered index will just contain
the keys and pointers to the actual data pages.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> How do I convert an SQL Server 2000 clustered index to non-clustered?
> Enterprise Manager would not let me.|||Here is why asked the question to begin with:
I have a table with almost 2 million records. The primary is is also the
clustered index. Records are added all day, every day. There is no order to
the way in which new records "arrive". New records/primary keys are not
presented in ascending order therefore a new record could be required to be
inserted anywhere within the table. Sometimes when the system slows down I
can't find any assignable cause; no locks, blocks, etc. I am thinking that
maybe SQL Server is taking a long time keeping the clustered index in order.
Maybe if I make the index non-clustered things will speed up. Does this make
sense?
"Jerry Spivey" wrote:
> You drop the clustered index and create a non-clustered index with the same
> keys. Note however that the clustered index IS the table and that if you
> drop the clustered index you will be left with a heap (unsorted collection
> of pages) for the table and that the non-clustered index will just contain
> the keys and pointers to the actual data pages.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> > Enterprise Manager would not let me.
>
>|||Is there an auto incrementing key on the table i.e., an IDENITY property on
a column? If so that may be a good possible candidate for the clustered
index as the data page for the next insert is likely to already be in the
cache. If not you could add one. You could also try dropping the clustered
index trying it without the NC index, then add the NC index to see which is
faster. Additional NC indexes generally degrade write performance.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> Here is why asked the question to begin with:
> I have a table with almost 2 million records. The primary is is also the
> clustered index. Records are added all day, every day. There is no order
> to
> the way in which new records "arrive". New records/primary keys are not
> presented in ascending order therefore a new record could be required to
> be
> inserted anywhere within the table. Sometimes when the system slows down
> I
> can't find any assignable cause; no locks, blocks, etc. I am thinking
> that
> maybe SQL Server is taking a long time keeping the clustered index in
> order.
> Maybe if I make the index non-clustered things will speed up. Does this
> make
> sense?
> "Jerry Spivey" wrote:
>> You drop the clustered index and create a non-clustered index with the
>> same
>> keys. Note however that the clustered index IS the table and that if you
>> drop the clustered index you will be left with a heap (unsorted
>> collection
>> of pages) for the table and that the non-clustered index will just
>> contain
>> the keys and pointers to the actual data pages.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> > How do I convert an SQL Server 2000 clustered index to non-clustered?
>> > Enterprise Manager would not let me.
>>|||Yes, there is an Identity column named [Index]. Is an Identity column by
default indexed? The column that is the primary key needs to remain so.
Are you suggesting that I make the primary key a non-clustered index and make
the [Index] column a clustered index?
"Jerry Spivey" wrote:
> Is there an auto incrementing key on the table i.e., an IDENITY property on
> a column? If so that may be a good possible candidate for the clustered
> index as the data page for the next insert is likely to already be in the
> cache. If not you could add one. You could also try dropping the clustered
> index trying it without the NC index, then add the NC index to see which is
> faster. Additional NC indexes generally degrade write performance.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> > Here is why asked the question to begin with:
> > I have a table with almost 2 million records. The primary is is also the
> > clustered index. Records are added all day, every day. There is no order
> > to
> > the way in which new records "arrive". New records/primary keys are not
> > presented in ascending order therefore a new record could be required to
> > be
> > inserted anywhere within the table. Sometimes when the system slows down
> > I
> > can't find any assignable cause; no locks, blocks, etc. I am thinking
> > that
> > maybe SQL Server is taking a long time keeping the clustered index in
> > order.
> > Maybe if I make the index non-clustered things will speed up. Does this
> > make
> > sense?
> >
> > "Jerry Spivey" wrote:
> >
> >> You drop the clustered index and create a non-clustered index with the
> >> same
> >> keys. Note however that the clustered index IS the table and that if you
> >> drop the clustered index you will be left with a heap (unsorted
> >> collection
> >> of pages) for the table and that the non-clustered index will just
> >> contain
> >> the keys and pointers to the actual data pages.
> >>
> >> HTH
> >>
> >> Jerry
> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> >> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> >> > Enterprise Manager would not let me.
> >>
> >>
> >>
>
>|||Hmmm...not sure I would have named the column 'index' as that is a SQL
Server reserved word. No, a column with an IDENTITY property is not indexed
by default. It is an option as the PRIMARY KEY column is not "required" to
be a clustered index and a clustered index may be better suited to the
IDENTITY column in this case. I would try a few indexing strategies and
choose the best one in performance from your tests.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:A6EBD861-9E73-4896-8AFB-2AE09CC177FB@.microsoft.com...
> Yes, there is an Identity column named [Index]. Is an Identity column by
> default indexed? The column that is the primary key needs to remain so.
> Are you suggesting that I make the primary key a non-clustered index and
> make
> the [Index] column a clustered index?
> "Jerry Spivey" wrote:
>> Is there an auto incrementing key on the table i.e., an IDENITY property
>> on
>> a column? If so that may be a good possible candidate for the clustered
>> index as the data page for the next insert is likely to already be in the
>> cache. If not you could add one. You could also try dropping the
>> clustered
>> index trying it without the NC index, then add the NC index to see which
>> is
>> faster. Additional NC indexes generally degrade write performance.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
>> > Here is why asked the question to begin with:
>> > I have a table with almost 2 million records. The primary is is also
>> > the
>> > clustered index. Records are added all day, every day. There is no
>> > order
>> > to
>> > the way in which new records "arrive". New records/primary keys are
>> > not
>> > presented in ascending order therefore a new record could be required
>> > to
>> > be
>> > inserted anywhere within the table. Sometimes when the system slows
>> > down
>> > I
>> > can't find any assignable cause; no locks, blocks, etc. I am thinking
>> > that
>> > maybe SQL Server is taking a long time keeping the clustered index in
>> > order.
>> > Maybe if I make the index non-clustered things will speed up. Does
>> > this
>> > make
>> > sense?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> You drop the clustered index and create a non-clustered index with the
>> >> same
>> >> keys. Note however that the clustered index IS the table and that if
>> >> you
>> >> drop the clustered index you will be left with a heap (unsorted
>> >> collection
>> >> of pages) for the table and that the non-clustered index will just
>> >> contain
>> >> the keys and pointers to the actual data pages.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> >> > How do I convert an SQL Server 2000 clustered index to
>> >> > non-clustered?
>> >> > Enterprise Manager would not let me.
>> >>
>> >>
>> >>
>>