6 Replies - 599 Views - Last Post: 09 August 2016 - 10:56 PM

#1 SophieneeRichards  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 102
  • Joined: 18-March 16

Changing Data types

Posted 07 July 2016 - 09:06 AM

Hi,

I'm trying to write a database table in SQLServer and I've discovered that it will only let me save my table if the datatype is nchar(10) and is Nulls are allowed. I need to change the data types for different columns and set some as nulls allowed and some as nulls not allowed.

I imagine that somewhere in the settings there's a tick box or something which is stopping me from changing things- does anyone have any ideas on how to change this?

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Changing Data types

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,479
  • Joined: 12-June 08

Re: Changing Data types

Posted 07 July 2016 - 09:12 AM

Change the actual table? Crank open Management Studios, log in, get to the right db, find the table, right click 'design'.

Of course you would want to spend some time making sure you won't break things by changing the table on a whim.
Was This Post Helpful? 0
  • +
  • -

#3 SophieneeRichards  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 102
  • Joined: 18-March 16

Re: Changing Data types

Posted 07 July 2016 - 09:24 AM

Hi,

That's what I'm doing- I created the table this afternoon, and I'm in the process of adding my columns at the moment- the table doesn't hold any data yet.

I was in the design screen, when I was adding my columns, but whenever I try to change the data types or the nulls it says

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

So it doesn't seem like a simple task of opening the design screen and making the changes.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,358
  • Joined: 03-December 12

Re: Changing Data types

Posted 07 July 2016 - 09:30 AM

Quote

or enabled the option Prevent saving changes that require the table to be re-created


If there is no data in the table, there is no harm in dropping it and rebuilding. If there was data, it would be an issue.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,479
  • Joined: 12-June 08

Re: Changing Data types

Posted 07 July 2016 - 09:30 AM

It is that straight forward, but it is an important warning. As in 'loss of all your data' sort of thing.

Typically I would just create a fresh table with the right column/datatypes to start off with, but you can circumvent this by updating your options.

Tools -> options -> designers -> Prevent saving changes that require the table re-creation.. you can turn this off for a short time, make your changes, and turn it back on.

https://support.micr...en-us/kb/956176
Was This Post Helpful? 1
  • +
  • -

#6 SophieneeRichards  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 102
  • Joined: 18-March 16

Re: Changing Data types

Posted 07 July 2016 - 09:48 AM

Thanks!

I've changed that setting, and will set it back once I've finished creating all my columns.

Its weird though, that as I was creating the columns it would let me have any data type other than nchar(10).

Thanks again :)
Was This Post Helpful? 0
  • +
  • -

#7 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Re: Changing Data types

Posted 09 August 2016 - 10:56 PM

You might also want to look in to Alter Table query, as much as GUI would seem to make things easier, it is even easier to just write a query.

You may need to run the following Query according to your tables' needs

ALTER TABLE SCHEMA_NAME.TABLE_NAME
ALTER COLUMN COLUMN_NAME DATATYPE (NULL/NOT NULL)

OR

ALTER TABLE SCHEMA_NAME.TABLE_NAME
DROP COLUMN COLUMN_NAME DATATYPE (NULL/NOT NULL)
ADD COLUMN COLUMN_NAME DATATYPE (NULL/NOT NULL)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1