Statistical Aggregate functions in SQL Server — Parv The IT Geek

Have you ever wondered how to do program or perform simple statistical function within SQL Server? Well the kind people at Microsoft have enabled us to all to save time by giving us a few inbuilt functions which save us from having to solve them programmatically.
 I have used the unit Price column in the SalesOrderDetail table within the Adventureworks database to illustrate the in built functions

SELECT --UnitPrice,

COUNT(UnitPrice) AS 'Count of UnitPrice',

AVG(UnitPrice) AS 'Average UnitPrice',

MIN(UnitPrice) AS 'Minimum UnitPrice',

MAX(UnitPrice) AS 'Maximum UnitPrice',

SUM(UnitPrice) AS 'Total UnitPrice',

VAR(UnitPrice) AS 'Variance of UnitPrice',

STDEV(UnitPrice) AS 'Standard deviation of UnitPrice'

FROM [Sales].[SalesOrderDetail]

The COUNT function counts the number of values within the column.
 The AVG function returns the average unit price column.
 The MIN function returns the smallest value within the column.
 The MAX function returns the largest value within the column.
 The SUM function returns the sum total of all the values within the column.
 The VAR function returns the variance of all the values within the column.
 The STDEV function returns the standard of all the values within the column.

I am sure that I have probably missed out a few functions so please let me know via the comments below.


Originally published at https://parvtheitgeek.com on April 7, 2015.

SQL Cursor to Kill all connections to a database — Parv The IT Geek

Have you ever tried to restore over a database but found that all attempts are being blocked by an annoying SPID? Or had hundreds of orphaned SPIDs running crazy on your instance?

Well I have the solution for you with the below script which uses a cursor to kill all connections to a database.

DECLARE @spid varchar(10)</pre>

DECLARE kill_spid CURSOR fast_forward FOR

SELECT SPID FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'AdventureWorks2012' AND spid > 50

OPEN kill_spid

FETCH NEXT FROM kill_spid INTO @spid

while @@FETCH_STATUS = 0

BEGIN EXEC ('Kill ' + @Spid)

FETCH NEXT

FROM kill_spid INTO @spid

END

CLOSE kill_spid

DEALLOCATE kill_spid

Make sure that you change AdventureWorks2012 for your database name and double check that you have the correct database name as I have seen it when people put the wrong database name in and its never a pretty sight.


Originally published at https://parvtheitgeek.com on March 27, 2015.

SQL Cursors — Parv The IT Geek

Cursors are a way of manipulating data and interacting with them one at a time. They have a bad reputation within the SQL world as they go against the SET based logic and they can have a very high performance cost. Where possible you should ask yourself whether you could avoid using a Cursor. This is because of the performance advantages a SET based solution has and that Cursor problems only increase when the tasks are scaled up.

The five general steps of a cursor are:

  1. Declaration of the cursor
  2. Opening the cursor
  3. Fetching and manipulating the data
  4. Closing the cursor
  5. Deallocating the cursor

A simple example of a Cursor is below.

Declare @Databases varchar(50)</pre>

Declare DatabasesOnIntance CURSOR READ_ONLY FOR SELECT name FROM sys.databases order by name

Open DatabasesOnIntance

Fetch next from DatabasesOnIntance into @Databases

While @@FETCH_STATUS = 0

Begin

Print @Databases

Fetch next from DatabasesOnIntance into @Databases

End

Close DatabasesOnIntance

Deallocate DatabasesOnIntance

As you can see the Cursor I have created has printed each database on the instance


Originally published at https://parvtheitgeek.com on March 23, 2015.

WHILE, BREAK, and CONTINUE Statements — Parv The IT Geek

Have you ever wondered how to create a loop in SQL? Or wondered how to break and escape a loop?

Well I am going to briefly introduce you to the WHILE, BREAK, and CONTINUE Statements which will satisfy your curiosity

The first command I will introduce you to is WHILE

DECLARE @i int = 1; 
WHILE @i < = 5
BEGIN
PRINT @i;
SET @i = @i + 1;
END

Loop

As you can see the WHILE statement will force the loop to continue until we reach 5. This is very useful if you need to batch process tasks and limit the amount done within each batch.

The next command to learn is CONTINUE. This command forces you to go back to the beginning of the loop.

DECLARE @i int = 1; 
WHILE @i < = 5
BEGIN
PRINT @i;
SET @i = @i + 1;
CONTINUE; -- This will cause the WHILE to loop back
PRINT 'You wont see this due to the CONTINUE commands cleverness.';
END

Once the WHILE command is satisfied the CONTINUE command will allow the loop to complete.

The final command related to loops is the BREAK command.

DECLARE @i int = 1; 
WHILE @i < = 5
BEGIN
PRINT @i;
SET @i = @i + 1;
BREAK; -- Force the WHILE loop to terminate
PRINT 'You wont see this due to the BREAK commands cleverness..';
END

As you can see from the example when the BREAK is encountered the loop is broken and it only ever prints 1.

In the real world many developers try not to use the BREAK and CONTINUE commands as they can be easily avoided in code and many people find it makes code less readable and unnecessarily complex.


Originally published at https://parvtheitgeek.com on March 15, 2015.

Learn about the WAITFOR TIME & WAITFOR DELAY — Parv The IT Geek

Have you ever wanted to pause a command for a short period or wanted to run a transaction at a specific time.

Well I am going to quickly show you how to do them both using the WAITFOR command.

SELECT GETDATE()

WAITFOR DELAY '00:00:10';

BEGIN

SELECT GETDATE()

END;

The above code shows you how to delay two print current date and time commands by ten seconds. It can be easily modified for any time such as 43 minutes, 43 hours….

SELECT GETDATE()

WAITFOR TIME '12:22:00';

BEGIN

SELECT GETDATE()

END;

The above command will cause the transaction to wait until that time before executing the command. The time can be modified at your pleasure for whatever time you require.

The two processes do carry a processor overhead as the transaction will be running until it’s completed. Also, these commands can usually be replaced by an appropriately timed SQL Agent job which reduces the process overhead and makes the administration of it far easier.


Originally published at https://parvtheitgeek.com on March 12, 2015.

Declaring Variables and Retrieving Variables — Parv The IT Geek

A variable can be best described as being a place holder for information which you then fill in with relevant information which you will want to retrieve later.

The below query shows the result from the CURRENT_TIMESTAMP which returns the current date and time.

PRINT CURRENT_TIMESTAMP

Jan 15 2015 11:44AM

The CURRENT_TIMESTAMP result can be made into a variable which can be retrieved later.

DECLARE @ThisIsTheCurrentDateandTime Datetime = CURRENT_TIMESTAMP PRINT @ThisIsTheCurrentDateandTime

The DECLARE command creates the variable, assigns the datatype and the information I would like to put into the variable.

After I have created the variable I then retrieve it using the PRINT command to show the value stored.


Originally published at https://parvtheitgeek.com on January 26, 2015.

TSQL Challenge on BeyondRelational.COM — Parv The IT Geek

I was asked by one of my junior colleagues to help him with a puzzle he had seen on BeyondRelational.COM which I thought would be a nice challenge. I liked the premise of the scenario and I always liked a challenge decided to show my colleague how I would approach and resolve this problem.

I was a little annoyed that the example code to create the test data didn’t work so did a quick fix which I have posted below.

CREATE TABLE Firstchallenge(

EmployeeID INT IDENTITY,

EmployeeName VARCHAR(15),

Department VARCHAR(15),

Salary NUMERIC(16,2)

)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('T Cook','Finance', 40000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Michael','Finance', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('A Smith','Finance', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Adams','Finance', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('M Williams','IT', 80000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Jones','IT', 40000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('J Miller','IT', 50000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('L Lewis','IT', 50000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('A Anderson','Back-Office', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('S Martin','Back-Office', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('J Garcia','Back-Office', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('T Clerk','Back-Office', 10000)

I told him that it would be quite simple to get the Ranking by using the RANK command and Partitioning the data by Department. However, I know you can’t filter by rankings from past experience but I knew if I put it into a subquery I could then filter the data which I have done below.

SELECT [EmployeeID]

,[EmployeeName]

,[Department]

,[Salary]

FROM

(

SELECT [EmployeeID]

,[EmployeeName]

,[Department]

,[Salary]

,RANK() OVER (PARTITION BY [Department]ORDER BY [Salary] DESC) AS 'Salary Rank'

FROM [TESTDB].[dbo].[Firstchallenge]

) A

WHERE [Salary Rank] = 2

I know that I could improve the query by using a CTE but I was happy that I was able to to get the result required and will probably put the CTE query in a future update of this article.

Originally published at https://parvtheitgeek.com on January 19, 2015.

IF ELSE Control of flow statements — Parv The IT Geek

The IF ELSE statements are one of the most frequently used statement within SQL and control of

flow statements are one of the core statements within any programing language. Once you have

understood the concept you can easily make powerful scripts.

An IF statement is a check to see whether a condition is TRUE and is used in hundreds of different

scenarios such as checking whether an object exists, checking data for a value…… I always liken the

IF statement to checking your fridge for your favourite meal and if its there you eat otherwise you

check out all your other options.

The below is a simple script which tests to see whether I have the AdventureWorks2012 database.

IF EXISTS (SELECT * FROM sys.databases 
WHERE name = 'AdventureWorks2012' )
PRINT 'AdventureWorks2012 is installed'

The result you will get is below.

AdventureWorks2012 is installed

The query checks whether the AdventureWorks2012 returns a value or is TRUE from my query and

then runs the PRINT command to confirm the value was returned as I expected.

You can make the IF statement more powerful by adding the ELSE statement. The ELSE Statement is

used as we often don’t just want to check if a statement is TRUE but also want to produce an action if its false.

DECLARE @TestValue int 
SET @TestValue = 1
IF @TestValue >1
BEGIN
PRINT 'The Test value is greater than 1'
END
ELSE
PRINT 'The Test value is less than or equal to 1'

The result you will get is below.

The Test value is less than or equal to 1


Originally published at https://parvtheitgeek.com on January 14, 2015.

How to attach AdventureWorks 2012 to SQL Server — Parv The IT Geek

Have you ever wanted to install the sample database (AdventureWorks )provided by Microsoft which a lot of the tutorials and books refer to? Well one of my colleagues has started to learn SQL Server so I thought I would knock up a quick guide for him.

The correct classical way is to use T-SQL would be to use the below script which you should adjust for wherever you have located the data/ MDF file.

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAAdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG ;

Or you could use the simple GUI method and Right click on databases and select Attach.

Then go to the correct path and select the data / MDF file and press OK.

If you use the T-SQL method ensure that you do not have the LDF / Log file in the directory of the data / MDF file otherwise you may encounter an error. Also, when recreating the database from just the data / MDF file you will get the following error message.

File activation failure. The physical file name “C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAAdventureWorks2012_log.ldf” may be incorrect.

New log file ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAAdventureWorks2012_log.ldf’ was created.


Originally published at https://parvtheitgeek.com on January 14, 2015.

ABC The best trait of a SQL DBA — Parv The IT Geek

Have you ever wondered what the best trait all super SQL DBAs possess? Well I will tell what the best trait is and an easy acronym to remember it.

I had a conversation with one of my colleagues we talked about the different DBAs and System Admins in the company both past and present and he asked me what I had learned having met them all.

It was an easy answer and I replied my ‘ABC.’ He looked perplexed and then I told it him what it stood for.

ABC = Always Be Checking.

As a DBA I see myself as the protector of the data and liaison between users, developers and system admins to ensure that everyone is happy.

The only way I can do that is to follow the ABC.

Check backups

Check SQL jobs

Check database mirroring

Check SQL Logs

Check for unused indexes

Check SA password

Check index suggestions and liaise with developers to implement them and maximise their potential

Check with application managers that users are having a good experience connecting to the database

Etc

Along the DBA journey there are many problems which you will face and many of them can be fixed at source by following the ABC mantra.

If you don’t want to follow the ABC mantra then you will always find yourself reacting to situations rather than managing them.


Originally published at https://parvtheitgeek.com on April 14, 2014.