Skip to main content
added 648 characters in body
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown below. However, it is much simpler to simply "disallow" use of particular datatypes. You could create a DDL trigger to trap CREATE TABLE and ALTER TABLE statements and if someone is trying to use DATETIME, simply issue the following:

ROLLBACK TRAN;
RAISERROR('How many times have I said: DON''T use DATETIME! Try again with DATETIME2.', 16, 1);
RETURN;`

This does not require complex text parsing or any additional logic. Nor will it make you many friends.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted and if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind:

  • the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

  • this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted and if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind:

  • the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

  • this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown below. However, it is much simpler to simply "disallow" use of particular datatypes. You could create a DDL trigger to trap CREATE TABLE and ALTER TABLE statements and if someone is trying to use DATETIME, simply issue the following:

ROLLBACK TRAN;
RAISERROR('How many times have I said: DON''T use DATETIME! Try again with DATETIME2.', 16, 1);
RETURN;`

This does not require complex text parsing or any additional logic. Nor will it make you many friends.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted and if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind:

  • the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

  • this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

added 368 characters in body
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted anand if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind that the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.:

  • the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

  • this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted an if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind that the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards. So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted and if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind:

  • the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

  • this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.

Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted an if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind that the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards. So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.