Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: T-SQL

The HAVING clause is used in conjunction with a GROUP BY so that aggregate functions can be used in a comparison. Think of it as a WHERE clause for groupings.

The following example should help to explain further.

The following example will fail because the WHERE clause is used to evaluate data on a row by row basis. The query is an attempt to limit the results returned to those customers with an order count greater than five.

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
WHERE COUNT(customer_id) > 5
GROUP BY customer_id

You will receive an aggregate related error, as the WHERE clause can only operate on a single row. To achieve the desired result, we remove the WHERE clause and add a HAVING clause after the GROUP By. Running the following query will now work:-

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
GROUP BY customer_id
HAVING COUNT(customer_id) > 5
Advertisements

Tags: , , , , ,

UK Postcodes can be difficult to handle in SQL, especially when trying to group results based on area code. Postcodes in the UK come in a variety of formats, the most common being AA9 9AA, A9 9AA, A99 9AA and AA99 9AA. Problems arise due to the variation in the total number of characters in a code, the different combinations of numbers and letters, and also the location of the space.

If you need to write a query to select the characters up to but excluding the space (e.g. AA9, A9, A99 or AA99), then the following approach can be used. It uses the LEFT command in conjunction with CHARINDEX to select the left side of the full code.

SELECT CompanyName, LEFT(Postcode, CHARINDEX(' ', Postcode) -1) AS 'Postcode'
FROM CompanyAddresses

However, if you need to select only the leading letters and not numbers (A or AA), then you can use a CASE statement:-

SELECT CompanyName, CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode'
FROM CompanyAddresses

Using either of the above two methods, you can successfully group results into Postcode areas. Use either method to group and select on Postcode.

SELECT CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode', COUNT(CompanyName) AS 'Companies in Postal Area'
FROM CompanyAddresses
GROUP BY CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END

Tags: , , , , , , , ,

Simple information about indexes can be found by running

sp_helpindex 'HumanResources.Employee'

where ‘HumanResources.Employee’ is the name of a table. index_name, index_description and index_keys are displayed in the results window:-

sp_helpindex

Tags: , , ,

It is possible to obtain the ‘date of creation’ of a table, as well as its last modification date (DDL, not DML) by running the following query:-

SELECT [name] AS 'Table', 
[create_date] AS 'Creation Date', 
[modify_date] AS 'Modification Date' 
FROM sys.objects
WHERE type_desc = 'USER_TABLE' 
ORDER BY [modify_date] DESC

Tags: , , , ,

I am often called upon to restore a copy of a live database to a development server. Taking a one-off backup in the normal manner would cause problems, as the sequence of LSNs (Log Sequence Numbers) would be disrupted on the production server. This means that subsequent log or differential backups would follow the one-off backup, rather than the file produced from the regular scheduled backup plan. Thankfully there is a solution….

Issuing a backup with the COPY_ONLY option stops the log sequence chain from being broken. Use the option as follows:-

BACKUP DATABASE [LiveDB]
TO DISK = 'E:\Backups\TestRestoreFile.bak'
WITH INIT,  NAME = 'Full Database Backup', COPY_ONLY,  STATS = 10

Unfortunately, it is not possible to select a COPY_ONLY backup through the Management Studio GUI. The backup command has to be issued through T-SQL. It is also not possible to restore through the interface. As you can see from the following screenshot, you can select the backup file, but the backup set will not appear in the list.

RestoreTestDB
To perform the restore, issue the following command, specifying the target location(s) for data and log files. Include REPLACE to overwrite any existing test database. I would also recommend you rename the files so that they now match the name of the test database, rather than the live database.

RESTORE DATABASE [TestDB]
FROM  DISK = 'E:\Backups\TestRestoreFile.bak'
WITH  FILE = 1,
MOVE 'LiveDB_data' TO 'E:\Data\TestDB.mdf',
MOVE 'LiveDB_log' TO 'E:\Logs\TestDB_log.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

If you now issue log backups on the production server, the LSNs will be in the correct sequence and the COPY_ONLY backup will effectively have been ignored.

Tags: , , , , , , ,

For this demo, you will require SQL Server with the AdventureWorks database and a copy of Windows Powershell. We intend to build up a number of UPDATE statements based on data within AdventureWorks.HumanResources.Employee. We want to give all 157 production technicians an extra ten vacation hours, but need an easy way of generating the many lines of SQL we require. First of all, we need to run the following query to return the employeeID of all employees who meet this criteria:-

USE AdventureWorks
SELECT EmployeeID
FROM HumanResources.Employee
WHERE Title LIKE 'Production Technician%'

With a fresh installation of the AdventureWorks database, this should return 157 results. Create a file on the root of C: called myfile.txt and copy and paste in the results using notepad or other text editor. You should have one EmployeeID per line.

Now we can build up our UPDATE queries by using Windows Powershell. Open Powershell and enter the following:-

get-content c:\myfile.txt | Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"} | out-file c:\output.txt

If you now check the newly created file (ouput.txt) on C: drive, you should have 157 UPDATE statements which you can copy and paste into SQL Server. Executing will give the production assistants their extra hours.

output.txt

Now to explain the powershell code…..

get-content c:\myfile.txt

simply reads the contents of our file. If you solely run this part of the command in Powershell, the contents of the file will be displayed on screen.

The main part of our command is the foreach loop which builds our SQL statements by adding our text before and after the contents of each row. The contents of each row is held in “$_

Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"}

The final part of our code simply writes the changes out to our output.txt file

out-file c:\output.txt

There are much more elegant ways of writing our SQL statement. We could of course, have written a single UPDATE statement and included,

WHERE Title LIKE 'Production Technician%'

…. but that would not have been as much fun and you would not have just learnt this handy Powershell tip!!

Tags: , , , , , , ,

Following on from my previous post on sp_help, the sp_helpdb stored procedure provides useful information at the database level.

Running sp_helpdb without specifying a database provides an overview of all databases within the instance.

sp_helpdb_resultsThe query returns the following information:-

name
database size
owner
database id
creation date
database status
compatibility level

By specifying a database,

sp_helpdb 'AdventureWorks' 

additional information is provided on that databases data files and log files:-

sp_helpdb

Tags: , , , , ,

%d bloggers like this: