Grouping and Windowing - Part 3

So another part of Grouping and Windowing in SQL Server is Pivoting and Unpivoting

Pivoting 

I am going to use this dataset for this example

Using this query

WITH PivotData AS

(

SELECT

Type , -- grouping column

Column1, -- spreading column

Number -- aggregation column

FROM [dbo].[Pets]

)

SELECT Type, [DOGS], [CAT], [FISH]

FROM PivotData

PIVOT(SUM(Number) FOR Column1 IN ([DOGS],[CAT],[FISH]) ) AS P;

produces this result. By looking at the code above, you can work out what is going on from the comments.

Unpivoting 

Using this dataset from the previous section 

We can unpivot by using this code.

SELECT Column1,Number,[Type]

FROM PivotData2

UNPIVOT( Number FOR Column1 IN([DOGS],[CAT],[FISH]) ) AS U;

and it will produce these results

Orphaned users after a restore

When you restore a database, you can be left in a state where some users are orphaned from the server (even if there is a login with the same name)

Within SQL Server, there is a quick fix.

Step 1 - Run this in the newly restored database and it will show all the orphaned users

EXEC sp_change_users_login 'Report'


Step 2 - Run this and will assign the user to a login name 

EXEC sp_change_users_login 'UPDATE_ONE','',''

so for example

EXEC sp_change_users_login 'UPDATE_ONE','testuser','testuser'

And that's it. 

Grouping and Windowing - Part 2

Following on from Part 1, in this blog post we go deeper into Grouping and Windowing and talk about Multiple Grouping Sets

Multiple Grouping Sets

In summary, running this query

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY GROUPING SETS
(
( ID, Column1 ),
( Column1 ),
( )
)

produces this result

As you can see, the query adds the Cats,Dogs,Birds and Fish totals and produces a total at the end.

( ID, Column1 ), = Count indivisual rows
( Column1 ), = Sub Total
() = Grand Total

The query below uses the CUBE syntax

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY CUBE ( ID, Column1 )

and it produces this result.

 

As you can see, its does all the different combinations compared to the GROUPING SETS query.

Finally, there is the ROLLUP query

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY ROLLUP ( ID, Column1 )

 

This produces the same results as the GROUPING SETS query but its done in a hierarchy layout.

That is about it for multiple grouping sets in SQL Server.

Grouping and Windowing - Part 1

With this blog post, I am going to pay attention to the data analysis function within SQL Server and talk about Windowing and Grouping Functions. A data analysis function is when a function is used with a set of rows and in return, a single value is produced. The SUM() function is an example of a data analysis function.

Using this table, I can show you some examples

ID    Column1      Number
1      DOGS         1
2      CAT            10
3      FISH           9
4      BIRD          NULL
5      CAT           4
6      BIRD          8

Single Grouping Sets Examples

SELECT COUNT(*) NumberOfPets, COUNT(Number)  NumberOfPetsDistincts
FROM [Pets]

Returns

NumberOfPets NumberOfPetsDistincts
6                 5

This is because there is not a GROUP BY statement

SELECT [Column1],COUNT(*) NumberOfPets, COUNT( DISTINCT Number)  NumberOfPetsDistincts
FROM [Pets]
GROUP BY Column1

Returns

The reason the there are less BIRD's in the NumberOfPetsDistincts columns is because  COUNT( DISTINCT Number) ignores NULLs

Part 2 can be found here

http://sqlservertipsandtricks.blogspot.co.uk/2014/07/grouping-and-windowing-part-2.html

The Temporary Tables Of SQL Server

Within SQL Server there are 5 different temporary tables that you can use to store data.

They are :-

Table Variables

DECLARE @Table TABLE(

name varchar(30) NOT NULL, 
location varchar(30) NOT NULL 
);

Tables Values Parameters

CREATE TYPE LocationTableType AS TABLE 

( LocationName VARCHAR(50)
, CostRate INT );

Common Table Expressions (CTE)

-- Define the CTE expression name and column list.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Local Temporary Tables
SELECT *  INTO #Temp1 FROM Table1
Global Temporary Tables
SELECT * INTO ##Temp2 FROM Table1

Local Temporary Tables is the most common type of temporary table to use but I have been using CTE's more often now if I need to do extra work with the result set.

Local Temporary Tables are good if you want to do multiple processing on a set of data. CTE are good for readability or recursive processing.

Global Temporary Tables, I don't really use them much because I have never seen a business case to use one.

Tables Values Parameters are good when used in combination with a .NET application because you can pass an entire result set back to the calling program in one go.

Table Variables are very similar to Local Temporary tables but have these differences

Item	#Temp Tables	@Table Variables
Can participate in a transaction	YES	NO
Writes to Log File	YES	YES
Writes only to memory (not disk)	NO	NO
Can Qualify for Parallelism	YES	NO
Allows creation of statistics	YES	NO
Does not affect recompilation	NO	YES
Allows nonclustered indexes	YES	NO
Can perform SELECT INTO	YES	NO
Can access in nested stored procedures	YES	NO
Can define globally	YES	NO
Can use in user defined functions	NO	YES
Can insert from EXEC	YES	YES
Allows TRUNCATE	YES	NO
Allows ALTER TABLE	YES	NO

so this is a basic summary of using temporary tables in SQL Server and should be a start for you to us them.

The Basic SQL Server Functions

Sometimes you read books on subjects about SQL Server and you just want the basics, well here is the basics of SQL Server Functions

Functions can be Scalar or Table-valued

Basically Scalar returns one value and Table-valued functions (TVF) returns...well a table of results and this are usually found in the FROM clause of a statement.

 

Functions can be Deterministic or Nondeterministic

Demerministic =  This means they return the same value any time they are called with a specific set of input values.

i.e SELECT LEN('TEST) will always returns 4

Nondeterministic = different results every time

i.e SELECT GETDATE()

 

Example code taken from the MSDN website

 

The following example creates the user-defined function ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called. 

IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

The following example returns an inline table-valued function. 

It returns three columns ProductID, Name and the aggregate of 

year-to-date totals by store as YTD Total for each product sold to the store.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
); 

 

Constraints in SQL Server

Constraints in SQL Server play a big role in keeping data accurate in a database.

Within SQL Server there are several methods of doing this and one way is using constraints

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column

This is the code to assign a NOT NULL constraint to a Column. Column1 doesn't allow NULLS, Column2 allows NULLS.

CREATE TABLE TestTable1

(

PKID int NOT NULL,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign UNIQUE constraint to a Column

CREATE TABLE TestTable2

(

PKID int NOT NULL UNIQUE,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign Primary Key constraint to a column

CREATE TABLE TestTable3

(

PKID int PRIMARY KEY,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

If you assign a primary key to a column, it automatically gains a unique constraint. You can have many unique constraints on a table, but you can only have 1 primary key. Each table should have a primary key and primary keys cant have null values.

This is the code to assign a foreign key constraint to a column

CREATE TABLE TestTable4

(

P_Id int PRIMARY KEY,

PKID2 int NOT NULL,

Column1 int NOT NULL,

CONSTRAINT FK_CON FOREIGN KEY (P_Id)

REFERENCES TestTable3(PKID)

)

A foreign key constraint is used to make sure that data can't be deleted if it matches data in another table.

This is the code to assign a CHECK constraint to a column

CREATE TABLE TestTable5
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL,
CONSTRAINT chk_Column1 CHECK (Column1> 0 AND Column2='Test')

)

This makes sure that the value in Column1 is greater than 0 and Column2 contains the word 'Test'

This is the code to assign DEFAULT constraint to a column

CREATE TABLE TestTable6
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL DEFAULT ('Test'),

)

If a value is not entered in column2, the word 'Test' will be inserted.

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

Like Me on Facebook - https://www.facebook.com/sqlserverdev

So you want to improve your SQL skills and have come across some new operators and wondering when you should use them.

In this blog, I will focus on the following operators in the simplest terms and show you how to use them.

CROSS APPLY and INNER JOIN is very similar and
OUTER APPLY and LEFT JOIN is very similar.

In certain situations, you can write queries using CROSS APPLY or INNER JOIN and using OUTER APPLY and LEFT JOIN and they could bring back the exact results.

The only real difference that CROSS APPLY and OUTER APPLY is better than LEFT and INNER JOIN is when using a function or you want to write an advanced SQL Command to bring back certain results

Run the following script in a SQL Server Environment to see these operators in action.

You can examine the script to see how they differ and you should be able to play around with the script to fully understand what is going on.

create table Company (

    companyId int identity(1,1)

,   companyName varchar(100)

,   zipcode varchar(10) 

,   constraint PK_Company primary key (companyId)

)

GO

create table Person (

    personId int identity(1,1)

,   personName varchar(100)

,   companyId int

,   insertdate DATETIME

,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)

,   constraint PK_Person primary key (personId)

)

GO

insert Company

select 'ABC Company', '19808' union

select 'XYZ Company', '08534' union

select '123 Company', '10016' 

insert Company

select 'No Employees', '11111' 

insert Person

select 'Alan', 1, '20141011 09:00:00' union

select 'Bobby', 1,'20141011 10:00:00' union

select 'Chris', 1, '20141011 11:00:00' union

select 'Xavier', 2, '20141011 12:00:00' union

select 'Yoshi', 2, '20141011 13:00:00' union

select 'Zambrano', 2, '20141011 14:00:00' union

select 'Player 1', 3, '20141011 15:00:00' union

select 'Player 2', 3, '20141011 16:00:00' union

select 'Player 3', 3, '20141011 17:00:00'

/* using CROSS APPLY */

select *

from Person P

cross apply (

    select *

    from Company C

    where p.companyid = c.companyId

) CompanyPerson

/* the equivalent query using INNER JOIN */

select *

from Person p

inner join Company c on p.companyid = c.companyId

/* using CROSS APPLY - brings back the oldest inserted person by company*/

select *

from  Company c 

cross apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

/* using OUTER APPLY - brings back all the companies and the oldest inserted person by company*/

select *

from  Company c 

outer apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

order by companyName desc

/*  brings back all the companies and the people */

select *

from company c

left join person p on p.companyid = c.companyId

order by companyName desc

The script produces the following results

Like Me on Facebook - https://www.facebook.com/sqlserverdev

COALESCE vs ISNULL

Like Me on Facebook - https://www.facebook.com/sqlserverdev

So you have come across a problem in a SQL Server query where you want to find out if a value is null and if it is, use another value. Which function should you use, ISNULL or COALESCE?

Well if you just want to do something simple and to evaluate a value to see if its NULL and use another value, then both commands would work if the other number is NOT NULL.

If you wanted to do something more advanced and compare a series of numbers and use the first number that is not null, then COALESCE is the function that you have to use.
Run the code into a SQL Server Environment to see how COALESCE works

SET NOCOUNT ON;
GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
      WHERE name = 'wages')
   DROP TABLE wages;
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS 'Total Salary'
FROM wages;

There are some things to think about when using COALESCE, if you want to evaluate a series of numbers and all the values are NULL, at least one has to be a typed NULL

e.g

SELECT COALESCE(NULL, NULL);

will result in 

Msg 4127, Level 16, State 1, Line 1
     At least one of the arguments to COALESCE must be an
     expression that is not the NULL constant.

but this will work

SELECT COALESCE(CAST(NULL AS INT), NULL);

Using ISNULL does not error the code so this will work
SELECT ISNULL(NULL, NULL);

There are some minor differences between ISNULL and COALESCE and can act differently in different scenarios that you have to think about.

Like Me on Facebook - https://www.facebook.com/sqlserverdev