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;

What is equivalent of Oracle ROWNUM in SQL Server?

We are executing openquery from SQL server to Oracle:

select * from emp where rownum<5

Its equivalent is:

with cte as 
(
select *, row_number() over(order by <column>) as rownum 
from emp
)
select * from cte
where rownum < 5

SQL server does not have rownum pseudo column.
So if we would like to fetch row number along with select statement we have to use a ranking function ROW_NUMBER or Top 1.

 

 

SQL OVER and PARTITION BY T-SQL

Jen McCown blog explains so nicely Over and Partition By usage of SQL functions.

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost    OrderNum
10.00     345
10.00     346
10.00     347
10.00     348

Quick translation:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo

FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00   345          1
8.00   346          1
8.00   347          1
2.00   348          2

The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.

Using the OVER clause with aggregate functions

The following example uses the OVER clause with aggregate functions over all rows returned by the query. In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Avg”
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Count”
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Min”
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Max”
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

Here is the result set.

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max  
------------ ----------- -------- ----------- ----------- ----------- ------ ------  
43659        776         1        26          2           12          1      6  
43659        777         3        26          2           12          1      6  
43659        778         1        26          2           12          1      6  
43659        771         1        26          2           12          1      6  
43659        772         1        26          2           12          1      6  
43659        773         2        26          2           12          1      6  
43659        774         1        26          2           12          1      6  
43659        714         3        26          2           12          1      6  
43659        716         1        26          2           12          1      6  
43659        709         6        26          2           12          1      6  
43659        712         2        26          2           12          1      6  
43659        711         4        26          2           12          1      6  
43664        772         1        14          1           8           1      4  
43664        775         4        14          1           8           1      4  
43664        714         1        14          1           8           1      4  
43664        716         1        14          1           8           1      4  
43664        777         2        14          1           8           1      4  
43664        771         3        14          1           8           1      4  
43664        773         1        14          1           8           1      4  
43664        778         1        14          1           8           1      4  

Various time and date datatypes in SQL server overview

Reference: Robert Hartmann  on SQLSERVERCENTRAL.COM 

Various time and date datatypes in SQL server:

Type Format Range Accuracy Storage in bytes User defined precision
time hh:mm:ss [.nnnnnnn] 00:00:00.0000000
through
23:59:59.9999999
100 nanoseconds 3 to 5 Yes
date YYYY-MM-DD 0001-01-01 through
9999-12-31
1 day 3 No
smalldatetime YYYY-MM-DD
hh:mm:ss
1900-01-01 through
2079-06-06
1 minute 4 No
datetime YYYY-MM-DD
hh:mm:ss [.nnn]
1753-01-01 through
9999-12-31
0.00333 seconds 8 No
datetime2 YYYY-MM-DD
hh:mm:ss [.nnnnnnn]
0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
100 nanoseconds 6 to 8 Yes
datetimeoffset YYYY-MM-DD 0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
(int UTC)
100 nanoseconds 8 to 10
(2 for
zone)
Yes

SQL OpenQuery Samples

The OPENQUERY command is used to initiate an ad-hoc distributed query using a linked-server.
It is initiated by specifying OPENQUERY as the table name in the from clause.
Essentially, it opens a linked server, then executes a query as if executing from that server.

While executing queries directly and receiving data directly in this way is not bad, there are implications when trying to join the results of OPENQUERY with a local table.
Typically joining remote tables with local tables across the network is not the most efficient method of querying.
In some cases it may be better to subscribe to a remote table by importing it locally, then joining it locally.

Examples:

select * from OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE id = 101’)

UPDATE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE id = 101’)
SET name = ‘ADifferentName’;

INSERT OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles’)
VALUES (‘NewTitle’);
INSERT OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles’)
SELECT name FROM DBO.LOCALSQLSERVERTABLETILES;

DELETE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE name = ”NewTitle”’);

MSDN OPENQUERY Reference and SqlServerPlanet OpenQuery Reference