How to create SQL Server table with computed column of specific data type

The key is to use CAST as shown below

CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS CAST(QtyAvailable * UnitPrice AS Numeric(4,2))
);

— Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
VALUES (25, 2.00), (10, 1.5);

— Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

 

Advertisements

How to create SQL Server table with computed column

The syntax is Field Name “AS”.

Example:

CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
);

— Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
VALUES (25, 2.00), (10, 1.5);

— Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

So long AdventureWorks, and hello Wide World Importers!

Any future development on Microsoft sample databases should use Wide World Importers instead of AdventureWorks since it will not be updated or maintained after 2016.

The Wide World Importers can be downloaded from here.

Can I still use AdventureWorks with SQL Server 2016?

Yes, the final AdventureWorks build will be updated on the official download site here and you can still use AdventureWorks with your SQL Server 2016 instance.

Microsoft Reference

We need to include the NORESEED option when running DBCC CHECKIDENT to find the current identity value

Without NORESEED option if we run DBCC CHECKIDENT, then it checks the current identity value and if the identity and current value don’t match, it reseeds the identity.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’);

GO

If your intent is to find the current identity value, then we will have to include NORESEED option.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’, NORESEED);

GO

SQL Server job step failed with SQLSTATE 01000] (Message 7989) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Checking identity information: current identity value ‘-1’.

Today suddenly SQL server job step failed with message:

[SQLSTATE 01000] (Message 7989)  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[SQLSTATE 01000] (Message 2528)  Checking identity information: current identity value ‘-1’.

Same step was running with success till yesterday with the above message as info type message.