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 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

High Availability System (P.A.S.S) - Database Mirroring - SQL Server 2008

Whats is database mirroring ?

Database mirroring maintains a hot standby database (known as the mirror database) that can quickly assume client connections in the event of a principal database outage.

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database).

Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.

Following are some basic database mirroring terms.

Principal: In a database mirroring configuration, there are two copies of a single database, but only one copy is accessible to the clients at any given time. The copy of the database that the applications connect to is called the principal database. The server that hosts the principal database is known as the principal server.

Mirror: The mirror is the copy of the principal database. The mirror is always in a restoring state; it is not accessible to the applications. To keep this database up-to-date, the log records are transferred from the principal and applied on the mirror database. The server that hosts the mirror database is known as the mirror server.

Witness: The optional witness is an SQL Server instance in a database mirroring configuration. It is separate from the principal and mirror instances. When database mirroring is used in synchronous mode, the witness provides a mechanism for automatic failover.

Send Queue: While sending the log records from the principal to the mirror, if the log records can’t be sent at the rate at which they are generated, a queue builds up at the principal. This is known as the send queue. The send queue does not use extra storage or memory. It exists entirely in the transaction log of the principal. It refers to the part of the log that has not yet been sent to the mirror.

Redo Queue: While applying log records on the mirror, if the log records can’t be applied at the rate at which they are received, a queue builds up at the mirror. This is known as the redo queue. Like the send queue, the redo queue does not use extra storage or memory. It exists entirely in the transaction log of the mirror. It refers to the part of the hardened log that remains to be applied to the mirror database to roll it forward.

Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number.

Failover: When the principal database (or the server hosting it) fails, database mirroring provides a mechanism to fail over to the mirror database.

Some important aspects of database mirroring are:
• The granularity of database mirroring is a database. Mirroring is configured for one database at a time. The whole instance is not mirrored.
• Two copies of the same database are involved in database mirroring, but only one database is accessible to the applications at any given time. You can create a snapshot on the mirror and use it for read-only purposes (a good solution for reporting requirements). However, you cannot directly access the mirror database or back up the mirror database.
• You cannot mirror the master, msdb, temp, or model databases.
• Database mirroring requires that the database use the full recovery model. You can’t use the simple or bulk-logged recovery models.
• SQL Server 2005/2008 allows only one mirror database for each principal database.
• One instance can serve as the principal for one database, the mirror for another database, and the witness for yet another database.
• Multiple databases in an instance can be mirrored.
• Applications that connect to a database with ADO.NET or the SQL Native Client (SNAC) can automatically redirect connections when the database fails over to the mirror.
• Data between the principal and the mirror is transferred encrypted by default.
• A database which is mirrored to another server can also be the source database for a log-shipping scenario

Network bandwidth can be a bottleneck for mirrored databases where the rate of transaction log generation is high enough to saturate the network. On synchronously mirrored databases (where a transaction cannot commit on the principal until all the transaction log for it has been hardened in the log of the mirror database), this can even lead to workload throughput degradation.

Log compression which is a new feature of SQL Server 2008 is a way to fix this problem. The downside of this is that compression is not free - extra CPU must be used. The obvious CPU load increases comes from having to compress the transaction log on the principal and then decompress it again on the mirror before hardening it in the log. The non-obvious increase will come because the principal and mirror should end up being able to process more transactions/second, which means more CPU is needed.

This extra CPU could be a problem for some systems that are already pegged in terms of CPU load, and so they may actually see a drop in performance when log stream compression is enabled.

Apart from the obvious benefits of this feature for systems that can afford the extra CPU, it may allow more mirrored databases to perform effective database maintenance - in the form of fragmentation control and index maintenance. When database mirroring is enabled, the database has to be running in the full recovery mode. This means that operations such as index rebuilds are fully logged - that's a lot of extra log being generated if you're used to switching to bulk-logged recovery mode to perform index operations. Of course, this isn't an issue for reorganizing an index (with ALTER INDEX ... REORGANIZE) as this has been fully logged (and has to be that way because of how it works)

Anyway, for some people the fact that these two operations are fully logged means that running them produces so much additional log that, in conjunction with the log from the workload itself, the network becomes a bottleneck for mirroring and it slows down both the maintenance and reduces the workload throughput. The addition of log stream compression means that for these systems, the log may be compressed enough so that the network is no longer a bottleneck and more regular fragmentation control can take place. This in turn will increase workload throughput as well - a double benefit!

High Availability System (P.A.S.S) - Hardware Methods

SQL Server Database Solutions P.A.S.S Checklist

There are many ways to design a database solution for SQL Server and I try to make sure that I stick to my P.A.S.S checklist

• High Performance
• High Availability
• High Security
• High Scalability

In the following articles, I will go through my checklist to show you how you can use it within your database designs and implementations.

High Availability

Some people may judge that a high availability system is a system that never has any unscheduled downtime. This can be a mistake as even a scheduled downtime can make your system a non-available system.

To judge if a database solution is a highly available system, you should look at it from a users point a view. A user should be able to do what ever he/she wants to do any time of the day and if that’s not the case, then your system in not a highly available solution and should be redesigned for any potential problems that could occur.

Other things can make your system non-available, from index rebuilding, backing up the database to locking of tables. All these potential problems should be thought of when designing a database solution to make your users more productive.


How can you make your database system, highly available?

One method is to understand your environment and what is meant by the recovery models of a SQL Server database. The longer it takes to recover a database from a failover, the more unavailable your system is. Recovery models can impact on how long your database solution can take to recover.

Another method of making your system more available is to have disk redundancy and placing your databases file on RAID which can also shorten the recovery time.

Other methods to make your system more available is using one or more of the following solutions

Hardware Methods
* Remote Mirroring
* Failover Clustering

Software Methods
* Database Mirroring
* Log Shipping
* Peer to Peer replication

Each one of the have pro and cons and I will try to explain the benefits of each

Remote Mirroring

Remote mirroring is the method of mirroring your database to another SAN (Storage Area Network). This is a good idea if you have two business locations and you want to make sure that if one building is affected by a unspecified problem, the second database at another location would be able to takeover. What happens is that two or more SANS are connected and through this connection, they can make sure that they are in sync. Depending on hardware being used, there are different ways for this method to be implemented.

As remote mirroring can be expensive, you should only use this technique if your organisation uses the same hardware throughout your organisation as each remote mirroring solution is different between vendors. Also you need a dedicated dark fibre connection between the SANS as DSL shared connections will not do.
A negative feature about remote mirroring is that there is not an automatic failover process, so you would have to manual recover the secondary database when a failure occurs.


Failover Clustering

The main features of Failover Clustering are :-

• Windows Feature
• Prevents Data Loss
• Automatic Client Redirection
• Requires Specialised Hardware (Certified Clustering Software)
• Not as fast as Database Mirroring
• Can be geographically dispensed
  

The way failover clusting works is that you have nodes (SQL Servers) that all connect using a shared hard drive(Shared Disk Array). and on the shared disk array, each node would be given its own resources to be the owners of

i.e.
Node A is the Owner of Drive D
Node B is the Owner of Drive E

Only one node can be the owner of one resource at anytime.

Each node listens to the other node via a heartbeat and if one node detects that the other node, is not functioning, the functioning node would take over the ownership of the non functioning node. This way the functioning node will be able to bring back the SQL Server instance back online.

i.e
Node B is not functioning
Node A is the Owner of Drive D
Node A is the Owner of Drive E

All uncommitted transactions are rolled back and can be restarted on the new server.

The good thing about failover clustering is that if a failure happens on a Node, the other nodes can easily take ownership and business tasks can continue.

Future parts will contain information on
* Database Mirroring
* Log Shipping
* Peer to Peer replication