Showing posts with label TRUNCATE. Show all posts
Showing posts with label TRUNCATE. Show all posts

Saturday, November 12, 2016

What's new in SQL Server 2016: Truncate partitions


In my The best thing about the PASS summit for me is... post I said that I would do the following

I will also do a series about new stuff in SQL Server 2016, but that will be in between all the other posts. Whenever someone asked during a session how many people were already on SQL Server 2016, very few hands went up, this is the reason, I want to blog about this as well.
For all these series of posts, I plan to have a repo on github with a yet to be determined name. Then I will have all the SQL code for all the series organized there
This is the first post in that series

You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here TruncatePartitions

New in SQL Server is the ability to truncate a partition or more than 1 partition, this is very nice because we all know that truncate is much faster than a delete. Let's see how this all works

First create this simple table


CREATE TABLE SalesPartitioned(
 YearCol SMALLINT NOT NULL,
 OrderID INT NOT NULL, 
 SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid())
GO


Now it is time to insert some data, we are going to create data for 6 years, we will use those years then to partition the data on. This query will insert 6 times 2048 rows, 2048 rows per year


INSERT SalesPartitioned (YearCol,OrderID)
SELECT 2013,number
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2014,number + 2048
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2015,number + 4096
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2016,number + 6144
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2017,number + 8192
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2018,number + 10240
FROM master..spt_values
WHERE type = 'P'


Now let's create the partition function

CREATE PARTITION FUNCTION pfFiscalYear(SMALLINT)
AS RANGE LEFT FOR VALUES(2013,2014,2015,2016,2017)
GO


Create the partition scheme


CREATE PARTITION SCHEME psFiscalYear
AS PARTITION pfFiscalYear ALL TO ([PRIMARY])
GO


Add a primary key to the table, add it to the partition scheme we created above


ALTER TABLE dbo.SalesPartitioned ADD CONSTRAINT
    PK_Sales PRIMARY KEY CLUSTERED (YearCol,OrderID)
ON psFiscalYear(YearCol)
GO

Now let's see what we have as far as counts for each partition


SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

Here are the results

partition_number rows
1 2048
2 2048
3 2048
4 2048
5 2048
6 2048

As you can see we have 6 partitions, each partition has 2048 rows


We can verify this by running a count ourselves


SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO


YearCol Partition COUNT
2013 1 2048
2014 2 2048
2015 3 2048
2016 4 2048
2017 5 2048
2018 6 2048


Now it is time to truncate a partition.
In order to truncate a partition, you use the following syntax




TRUNCATE TABLE ........
WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

So if we just want to truncate partition 2, we would execute the following


TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (2));
GO


Checking those same count queries from before shows that partition 2 has no rows after we executed the truncate command


SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')


partition_number rows
1 2048
2 0
3 2048
4 2048
5 2048
6 2048



SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO


YearCol Partition COUNT
2013 1 2048
2015 3 2048
2016 4 2048
2017 5 2048
2018 6 2048



There are two ways you can truncate a bunch of partitions, for example if you want to remove 4,5 and 6, you can use WITH (PARTITIONS (4, 5, 6)) or you can use a range like shown in the code below


TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (4 TO 6));
GO

After we execute that and we check the counts again, we see that we are now only left with partitions 1 and 3


SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

Here are the results from that query

partition_number rows
1 2048
2 0
3 2048
4 0
5 0
6 0



SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

Here are the results

YearCol Partition COUNT
2013 1 2048
2015 3 2048



What happens if you specify an invalid partition in your truncate statement?


TRUNCATE TABLE SalesPartitioned 
WITH (PARTITIONS (12)); 

You get the following message

Msg 7722, Level 16, State 2, Line 1
Invalid partition number 12 specified for table 'SalesPartitioned', partition number can range from 1 to 6.


There you have it, this is how truncate partitions work


You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here TruncatePartitions


Monday, November 07, 2016

Why you need additional privileges to truncate tables compared to delete statements



One of the people on our team wanted to have the ability to truncate tables on the staging database while this person was doing some testing.

 Here is what Books On Line has about permissions for the TRUNCATED statement

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Before I answer why someone would need ALTER TABLE permissions when the person already has DELETE permissions, let’s run some code that will show the ‘problem’.

CREATE DATABASE Test
go
 
USE Test
GO
 
CREATE TABLE TestTruncate(Id int)
GO
 
INSERT TestTruncate values(1)
GO

Now create a new user and give the user datareader and datawriter permissions

USE master
GO
CREATE LOGIN TestLogin WITH PASSWORD=N'Test', 
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Test
GO
CREATE USER TestLogin FOR LOGIN TestLogin
GO
USE Test
GO
ALTER ROLE db_datareader ADD MEMBER TestLogin
GO
USE Test
GO
ALTER ROLE db_datawriter ADD MEMBER TestLogin
GO

Now that the user is created, login as that user and run the TRUNCATE TABLE command


TRUNCATE TABLE TestTruncate

Msg 1088, Level 16, State 7, Line 1
Cannot find the object “TestTruncate” because it does not exist or you do not have permissions.
As you can see, you don’t have permission. Executing a delete will work just fine


DELETE TestTruncate


(1 row(s) affected)

Before I give you a workaround, let’s try to figure out why the minimum requirement is ALTER TABLE.
What is the difference between a DELETE and a TRUNCATE in terms of logging? When a TRUNCATE occurs, the operation does not log individual row deletions, a DELETE operation does. The reason this is important is because if you have a trigger on the table, in needs to be disabled before the TRUNCATE occurs. Now you know why ALTER TABLE is required, triggers need to be disabled.


ALTER TABLE SomeTable DISABLE TRIGGER SomeTrigger



And in order to disable the trigger, ALTER TABLE permissions are required as a minimum.

But I don’t want people altering tables on our staging and QA servers, so here is one way of giving the person the ability to TRUNCATE a table without giving them permissions explicitly. Create a stored procedure and use WITH EXECUTE AS, this will define the execution context of the stored procedure. In the example below, I picked a user that has sufficient privileges to perform the TRUNCATE.



CREATE PROCEDURE prTruncate
WITH EXECUTE AS 'SuperUser'
AS
TRUNCATE TABLE TestTruncate
GO


All you have to do is give your user execute permissions to the stored procedure you just created



GRANT EXECUTE ON prTruncate TO TestLogin
GO


Now if you execute the stored procedure as the TestLogin user, you will see it will run just fine



EXEC prTruncate

Hope this helps someone in the future who is filling up his or her transaction log these days with all those DELETE statements

Wednesday, June 13, 2007

SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

I am still amazed at how many people still think that TRUNCATE TABLE is not logged. There is some logging going on but it is minimal, here is what Books On Line says:

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

Let’s prove that we can rollback a truncate

Create this table and do the select

CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator VALUES(1)



SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Now run this part

BEGIN TRAN
TRUNCATE TABLE
dbo.Enfarkulator
SELECT * FROM dbo.Enfarkulator
ROLLBACK TRAN


ID SomeOtherCol
(0 row(s) affected)

As you can see the table was truncated, now select from the table again


SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.

Truncate doesn’t preserve the identity value but delete does

This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works


CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator2 VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)


SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator


DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator

The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.

DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)

Now insert again and you will see that the values are the same.

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator



You can’t truncate tables that are referenced by a foreign key constraint.

If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that

CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator3 VALUES(1)



Now let’s add the foreign key

ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]
FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])


Now try to truncate.

TRUNCATE TABLE Enfarkulator

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.

See? You cannot do that

--Clean up time ;-)
DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator


Cross-posted from SQLBlog! - http://www.sqlblog.com/