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.

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