2

I am able to deploy a User Defined Table Type in SSMS with a default value like this:

CREATE TYPE [MySchema].[Test] AS TABLE
(
    [TestColumn] BIT NOT NULL DEFAULT 0
)

When I pull the source after deploying the DEFAULT 0 is gone:

CREATE TYPE [MySchema].[Test] AS TABLE(
    [TestColumn] [bit] NOT NULL
)

Why doesn't SSMS show the DEFAULT 0 anymore?

NoAlias
  • 9,218
  • 2
  • 27
  • 46
  • What do you mean by 'won't persist'? Did you get default value or not. **[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bfa291f0748a23a2f52d09751b8cc32d)** Generated SQLtext for object may be different than what is stored in internal metadata. – Lukasz Szozda May 01 '18 at 15:38
  • When I context menu "Script User Defined Table Type as" through SSMS and get the code from the server it no longer shows "DEFAULT 0". – NoAlias May 01 '18 at 15:40
  • Ok, but DEFAULT is still there and it is working. So the question is why SSMS doesn't show that. – Lukasz Szozda May 01 '18 at 15:41
  • Running your EXACT code it works just fine. I can declare a variable with that type and the default populates. And when creating the script it has the default. Not sure what you are seeing but from the code posted this can't be reproduced. – Sean Lange May 01 '18 at 15:43
  • No, default value does not work. This is not a table, but a User Defined Table Type. – NoAlias May 01 '18 at 15:43
  • `"No, default value does not work"` **[Metadata shows something different](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f6cea8b991b508cd89d5182461b606a8)** So the real question is "why SSMS tool does not show correctly entire definition" – Lukasz Szozda May 01 '18 at 15:46
  • 1
    The default DOES work. – Sean Lange May 01 '18 at 15:48
  • 1
    @lad2025 I took your suggestion and edited the question. – NoAlias May 01 '18 at 15:56
  • @N0Alias You should first upgrade [SSMS to the newest version](https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017) and check if error still occurs. For me it looks like SSMS bug. – Lukasz Szozda May 01 '18 at 16:00
  • I wonder if this is a bug in SSMS. It is not reproducible in 2008R2, 2012 or 2014. I don't have 2016 or later at work so can't test it out. – Sean Lange May 01 '18 at 16:03
  • @SeanLange This does appear to be something with SSMS. I tested using different versions against the same database and got different results. OP, Try upgrading to the latest version. 2016 was the only one I had issues with. Had no issues with 17.5 – SQLChao May 01 '18 at 16:10
  • I'll give upgrading a try, thanks. – NoAlias May 01 '18 at 16:13
  • Is it not showing up as a constraint as well? It may not show on that line but it should be as a constraint after the table. At least that is where all of my default constraints show up when I script the table. – Anthony Hancock May 01 '18 at 16:14

1 Answers1

2

I tried this in my SQL Server 2008 R2 version.

Step 1. Run the below sql on the database

CREATE TYPE [Test] AS TABLE
(
    [TestColumn] BIT NOT NULL DEFAULT 0
)

Step 2. Go to object explorer, , programmability, Types, User-Defined Table Types, Right click on the Test Table type and choose script user-defined table type as, choose create to. The output is as below.

USE [test_database]
GO

/****** Object:  UserDefinedTableType [dbo].[Test]    Script Date: 5/1/2018 12:21:08 PM ******/
CREATE TYPE [dbo].[Test] AS TABLE(
    [TestColumn] [bit] NOT NULL DEFAULT ((0))
)
GO

It retained the default 0. I am using SSMS 14.0 version.

Surendra
  • 711
  • 5
  • 15
  • 1
    Yeah this is the same thing I documented. Tried this with 2008R2, 2012 and 2014. All worked perfectly. It seems to happen only with SSMS 2016. – Sean Lange May 01 '18 at 18:21