I’m currently preparing to take the exam 70-761 – Querying Data with Transact-SQL and have been going through various resources including Itzit Ben-Gan’s exam guidebook which is certainly worth reading if you’re planning on taking this exam.
There are quite a few smaller bits of T-SQL functionality that I’m not that familiar with that I’m going to demonstrate in this and further posts. It’s a bit of a random selection but hopefully it will help me remember them and might be of use to others as well.
- ALL ANY SOME with subqueries
- TOP (x) WITH TIES
- Truncate partition
- TRY_CAST, TRY_CONVERT, TRY_PARSE
1. ALL ANY SOME with subqueries
This is something that I’ve never seen anyone use anywhere but I’ve had questions come up on this in two different exams!
We use the ALL, ANY and SOME keywords when compare data against rows returned by a subquery. For example, if we have two tables: the first containing numbers 1 to 5 and the second containing 3 to 5 we can use ALL to return all rows from the first table that are less than ALL the values in the second table. The only values that are less than all the values in the second table are 1 and 2. We can also use ANY or SOME (they both mean the same thing) to return all numbers less than any of the numbers in the second table. 1 and 2 are less so they all so they get returned, 3 is less than 4 and 5 that that get’s returns, and 4 is less than 5 so that gets returned.
Here’s the code, so it should make more sense looking at this…
DROP TABLE IF EXISTS dbo.AllAnySome; GO CREATE TABLE dbo.AllAnySome (SomeNumber INT); GO INSERT INTO dbo.AllAnySome (SomeNumber) VALUES (1), (2), (3), (4), (5); GO DROP TABLE IF EXISTS dbo.AllAnySomeLookup; GO CREATE TABLE dbo.AllAnySomeLookup (SomeNumber INT); GO INSERT INTO AllAnySomeLookup (SomeNumber) VALUES (3), (4), (5); GO SELECT * FROM dbo.AllAnySome WHERE SomeNumber < ALL (SELECT * FROM dbo.AllAnySomeLookup); GO
SELECT * FROM dbo.AllAnySome WHERE SomeNumber < SOME (SELECT * FROM dbo.AllAnySomeLookup); GO
SELECT * FROM dbo.AllAnySome WHERE SomeNumber < ANY (SELECT * FROM dbo.AllAnySomeLookup); GO
The query above using ALL is returning all the row from dbo.AllAnySome that are less than the minimum value from dbo.AllAnySomeLookup, so it’s equivalent to…
SELECT * FROM dbo.AllAnySome WHERE SomeNumber < (SELECT MIN(SomeNumber) FROM dbo.AllAnySomeLookup); GO
We can also do the following…
SELECT * FROM dbo.AllAnySome WHERE SomeNumber = ALL (SELECT * FROM dbo.AllAnySomeLookup); GO
… which is the same as using the IN keyword…
SELECT * FROM dbo.AllAnySome WHERE SomeNumber IN (SELECT * FROM dbo.AllAnySomeLookup); GO
In this example, ALL could also be replaced with SOME or ANY to return the same results.
2. TOP (x) WITH TIES
You can specify TOP (x) immediately after a SELECT clause to restrict the returned result set to only return x number of rows. Here’s an example where we’ve got a table with 12 rows and we’re just asking the top 10 rows…
DROP TABLE IF EXISTS dbo.TiesTest; CREATE TABLE dbo.TiesTest (Id INT); INSERT INTO dbo.TiesTest (Id) VALUES (1), (1), (1), (2), (2), (2), (3), (3), (3), (4), (4), (4); SELECT TOP (10) * FROM dbo.TiesTest ORDER BY Id;
As we can see this query has returned all the rows with values 1, 2 and 3 but only one of the three rows with value 4. If we wanted the TOP 10 rows but also wanted to include any other rows outside of the top 10 with the same values we can use the WITH TIES clause, meaning any subsequent rows where the value ties with the last value will be included.
SELECT TOP (10) WITH TIES * FROM dbo.TiesTest ORDER BY Id
As we can see we’ve now got 12 rows returned instead of the 10 as the values in rows 11 and 12 tie with the value in row 10.
One last thing to note is we must use ORDER BY if we’re using WITH TIES.
3. TRUNCATE PARTITION
In SQL Server 2016 we now have the ability to truncate a single partition. Prior to this we could only truncate the entire table Here’s an example of how to truncate a single partition…
CREATE PARTITION FUNCTION PF1 (DATETIME2(0)) AS RANGE RIGHT FOR VALUES ('20171001', '20171101', '20171201', '20180101', '20180201', '20180301', '20180401'); GO CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]); GO CREATE TABLE dbo.PartitionedTable ( Id INT IDENTITY(1,1) NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100) NOT NULL, DateOfBirth DATETIME2(0) NOT NULL, Address VARCHAR(400) NOT NULL, RegisteredDate DATETIME2(0) NOT NULL ) ON PS1(RegisteredDate); INSERT INTO dbo.PartitionedTable (FirstName, LastName, DateOfBirth, Address, RegisteredDate) VALUES ('Angela', 'Bennett', '1985-06-03', '1 The Road, This Town, AA1 2BB', '20171015'), ('Colin', 'Denton', '1952-12-05', '12 The Road, This Town, AA1 2BB', '20171125'), ('Eleanor', 'Francis', '1988-07-23', '9 The Road, This Town, AA1 2BB', '20171214'), ('Gerald', 'Harris', '1985-07-25', '102 The Road, This Town, AA1 2BB', '20180109'), ('Iris', 'Jones', '1947-10-18', '93 The Road, This Town, AA1 2BB', '20180206'), ('Kirk', 'Lennox', '2001-04-19', '2 The Road, This Town, AA1 2BB', '20180328'); SELECT * FROM dbo.PartitionedTable;
So now we’ve got a partitioned table with a single row on different partitions.
SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.PartitionedTable');
As we can see there is a row on partitions 2 to 7.
We can now truncate partitions 2, 3 and 4 which contains the rows for Angela Bennett, Colin Denton and Eleanor Francis as follows…
TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (2 TO 4));
We can now see that the data has been removed from the table and the partitions now have no rows…
SELECT * FROM dbo.PartitionedTable;
SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.PartitionedTable')
4. TRY_CAST, TRY_CONVERT, TRY_PARSE
Let’s say, for example, that we have a fairly simple process that loads data from one table to another. We get a feed of the source data into a table from a third party and it contains date values stored as text strings. Our target table needs to store the date as a DATE data type rather than a string so we explicitly convert it into a DATE when loading into the target. We’re just using an insert statement to load the data but the data from the supplier is sometimes invalid and causes the whole batch to fail. We want a way to allow the valid data to go through (and possibly store the failed data somewhere else). Let’s set up some test data to demo this…
CREATE TABLE T_Source ( ItsADate VARCHAR(20) ); INSERT INTO T_Source (ItsADate) VALUES ('20180203'), ('20180204'), ('ThisIsntADate'), ('20180205'); SELECT * FROM T_Source;
As we can see the third row does not contain a valid date.
Let’s now create our target table and try and load the source data in…
CREATE TABLE T_Target ( DefinitelyADate DATE ); INSERT INTO dbo.T_Target (DefinitelyADate) SELECT CAST(ItsADate AS DATE) FROM dbo.T_Source; Msg 241, Level 16, State 1, Line 32 Conversion failed when converting date and/or time from character string.
So because of the one invalid row, the other valid rows do not get loaded. Instead of using CAST we can use TRY_CAST instead which will return NULL if the value can’t be cast and won’t return an error.
SELECT TRY_CAST(ItsADate AS DATE) FROM dbo.T_Source
We probably wouldn’t want to load NULL in but we could do some filtering or maybe just use ISDATE() = 1.
CONVERT and PARSE have “TRY” equivalents…
SELECT TRY_CONVERT(DATE, 'simon') SELECT TRY_PARSE('99 March 2018' AS DATE USING 'en-gb')
These two queries just return NULL without throwing an error.