THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SSMS generating non-ANSI scripts

When using SSMS to script a database object that had been created with ANSI compliant syntax, I noticed that the generated script was not ANSI compliant. Have you noticed anything like this? It's more of an annoyance than a bug, but it is not the desired behavior. I opened Connect item 781321. If you are aware of any similar problems, please add them to the Connect item so that they can all be addressed at once.

Here's how to reproduce the problem. First, create a table with a default constraint that uses CURRENT_TIMESTAMP for ANSI compliance.

create table table1 (
    a DATETIME DEFAULT CURRENT_TIMESTAMP
NULL
);

Second, go to the Object Explorer in SSMS and select the table. RIght-click it to generate a create table script. You'll get something like this:

CREATE TABLE [dbo].[table1](
   
[a] [datetime] NULL
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[table1] ADD DEFAULT (getdate()) FOR [a]
GO

While the script will work just fine, getdate() is not ANSI compliant. The scripting tool shouldn't cause you to lose ANSI compliance.

 

Published Wednesday, March 13, 2013 9:20 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

TiborKaraszi said:

Square brackets is also not in ANSI SQL. I'd love it if we could convince MS to turn off generating square brackets, but no...

March 14, 2013 11:03 AM
 

Sunil Boga said:

thats a good catch john :)

March 18, 2013 11:24 AM
 

Andreas Wolter said:

While CURRENT_TIMESTAMP is ANSI-compatible, SYSDATETIME is more accurate - while it is also not ANSI-compliant

The Point out if this imho: There are other things of more importance than pure ANSI-compatibility

July 9, 2013 11:36 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement