If there is one thing that I always had a pet peeve with, was the table designer column data type default in SQL Server Management Studio. You have no option (that I am aware of) to change the default column data type when creating a new data table in table designer. SQL Server Management Studio decides that using ‘nchar’ is the way to go.
Looking for something else? Try these links instead:
- Shrink Bloated Development Databases – Save Precious Space
- Execute Script Error Resolved In SQL Server Management Studio
- Easily Generate Table Change Scripts In SQL Server Management Studio
- Azure Databases – Restore Local Database To Azure
- Automate Standard Comment Blocks For New SQL Queries
Well if you want to make your columns another data type, you need to manually select the default value and change it to the data type you want. I wanted to find a way to change that default. Turns out that there is an easy way to accomplish this.
SQL Server Management Studio
If you look at the screenshot below, you will see what I am talking about.
I wanted a way to change this behaviour. You can do this in the registry. Now seeing as all the sites I visit that involve some sort of registry change always have a disclaimer, I think that I need one too. If you screw up your registry, your PC will become self aware and try and strangle you with its power chord. Thereafter it will explode and die a digital death never to work again.
Modifying the Registry
Ok, fire up the registry editor and head on over to HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\DataProject. Please note that I am running SQL Server Management Studio 2008 R2 on this machine. If you have another version, the registry path might be a little different for you. On SSMS 2017 the Registry path is: HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\14.0\DataProject. Pro tip: You can always just search the registry for the keyword below.
Locate the keyword SSVDefaultColumnType and change the value to the data type you prefer. I changed mine to nvarchar. Exit the registry editor and restart SQL Server Management Studio.
This time, when you create a new table and add a new column, your data type defaults to the desired data type. Personally, I don’t like fooling around in the registry, but if it gets the job done, then I’m all for it. If anyone has another solution to this, some other setting in the SQL Server options please let me know in the comments below.