Using bigint with FORMATMESSAGE

SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following

SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50) 
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50); 

Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification		Represents
d or i		Signed integer
o		Unsigned octal
s		String
u		Unsigned integer
x or X		Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer


Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------
The value you supplied 1 is incorrect!


That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.


As you can see that did not work, so what can we do?

One thing we can do is converting the value to a varchar and then use s as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output

---------------------------------------
The value you supplied 1 is incorrect!

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);

You will get this

---------------------------------------
The value you supplied 1 is incorrect!

So there you have it, if you want to use bigint with FORMATMESSAGE use I64d as the type specification, or convert to varchar and use s as the type specification

Creating the Wide World Importers sample database v1.0

Before SQL Server 2005, we had the northwind and pubs sample database, these actually shipped with the product. If you did a SQL Server 2000 install, you would see these two database in addition to the system databases.

With SQL Server 2005 this changed, there were no more sample databases included. Instead new databases were made available for download, for a while now we have the AdventureWorks available. Today I wanted to see if there was a AdventureWorks 2016 database available. I couldn't find it. Instead I found the Wide World Importers sample database v1.0.

These sample databases live now on github


Here is the link  https://github.com/Microsoft/sql-server-samples


As a normal person you will probably navigate to the databases folder https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases  and in there you will probably go to https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers/wwi-database-scripts

Then you will see the following scripts

Now if your name is Denis or you like to waste time (or both), you think..cool I will download this repo and run these files.  That is not unreasonable to think......  but

To save you some time, here is the easiest way to put this database on your SQL Server instance

Instead of running those scripts, go to this page https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0  scroll down to downloads, download the 121 MB file named WideWorldImporters-Full.bak

While that is downloading, create a folder on you C drive named SQL, in that folder create 2 folders one named Data and one named Log, it should look like in the image below

Now assuming the backup file is on the C drive, here is what the restore command should look like

USE [master]
RESTORE DATABASE [WideWorldImporters] 
FROM  DISK = N'C:\WideWorldImporters-Full.bak' WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'C:\SQL\DATA\WideWorldImporters.mdf',  
MOVE N'WWI_UserData' TO N'C:\SQL\DATA\WideWorldImporters_UserData.ndf',  
MOVE N'WWI_Log' TO N'C:\SQL\Log\WideWorldImporters.ldf',  
MOVE N'WWI_InMemory_Data_1' TO N'C:\SQL\DATA\WideWorldImporters_InMemory_Data_1',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

On SQL Server vNext 1.1, the output looks like this from the command above

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 860.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
Database 'WideWorldImporters' running the upgrade step from version 853 to version 854.
Database 'WideWorldImporters' running the upgrade step from version 854 to version 855.
Database 'WideWorldImporters' running the upgrade step from version 855 to version 856.
Database 'WideWorldImporters' running the upgrade step from version 856 to version 857.
Database 'WideWorldImporters' running the upgrade step from version 857 to version 858.
Database 'WideWorldImporters' running the upgrade step from version 858 to version 859.
Database 'WideWorldImporters' running the upgrade step from version 859 to version 860.
100 percent processed.
RESTORE DATABASE successfully processed 58455 pages in 6.105 seconds (74.803 MB/sec).

To make sure everything is cool after the restore is done, run the following

USE WideWorldImporters
GO

select [Website].[CalculateCustomerPrice](1,number,'20160101')
from master..spt_values
where type = 'p'


select [Website].[CalculateCustomerPrice](customerid,1,'20160101')
from Sales.customers

And now we can even check that the function is visible in the new sys.dm_exec_function_stats DMV

SELECT TOP 10 d.object_id, d.database_id, 
OBJECT_NAME(object_id, database_id) AS 'function name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_function_stats AS d  
ORDER BY [total_worker_time] DESC;  

And here is the output

As you can see, we can see this function was executed a bunch of time

So there you have it, this is how you create the DB.... I also now understand why Brent Ozar uses the Stackoverflow DB for his posts, at least it is easy to find  .., if you prefer that over this example, head over here: https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/

What's new in SQL Server 2016: CREATE OR ALTER

SQL Server 2016 Service Pack 1 added the CREATE OR REPLACE functionality, however in SQL Server, it is called CREATE OR ALTER.  Finally it is here, this has been asked for since the 90s, it was on the SQL Server wishlist (who remembers that before they had connect :-)

CREATE OR ALTER can be used with the following four object types

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

Let's take a look how this all works

If you want to run this code, you need to be on SQL Server 2016 Service pack 1 or higher or on vNext, the @@VERSION function on SP1 and vNext returns the following


Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)   Nov  1 2016 23:24:39


Before they added CREATE OR ALTER, you had  a couple of options to create rerunable scripts. The first way was dropping the proc if it existed and having the CREATE PROC in the same script, it looked like this

--old way of dropping a proc then having a create script
IF OBJECT_ID('procTest') is not null
DROP PROCEDURE procTest
GO

CREATE PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

Another way would be to create a dummy proc if the proc didn't exist already and then just have the ALTER PROC part in your script

--another way is to have a dummy proc created,
--that way your alter part is always the same
IF OBJECT_ID('dbo.procTest') IS NULL
  EXEC ('CREATE PROCEDURE dbo.procTest AS RETURN 0;')
GO

ALTER PROCEDURE  procTest
AS
BEGIN
 PRINT (1)
END;
GO

In SQL Server 2016 with Service Pack 1, this becomes so much easier. Here is what it looks like

-- the easier way in sql server 2016 SP1 and up
CREATE OR ALTER PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

Here is how that looks with a function, pretty much the same as a procedure

-- works the same with functions
CREATE OR ALTER FUNCTION fnTest()
RETURNS INT
AS
BEGIN
 RETURN(1)
END;
GO

The view looks like this

-- also works with views
CREATE OR ALTER VIEW vwTest
AS
 SELECT 1 AS col;
GO

With triggers it is pretty much the same, here is the code that you can run, I first created a table otherwise I would not be able to create a trigger

-- first we need a table for the trigger
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'BooFar')
CREATE TABLE BooFar(id int)
GO

-- works with triggers
CREATE OR ALTER TRIGGER trTest
ON BooFar
AFTER INSERT, UPDATE
AS
 RAISERROR ('Hello from trigger', 1, 10);
 GO

 -- just a test to make sure the trigger works
 INSERT BooFar values(1)

 -- you should see this in the message tab
 /*
Hello from trigger
Msg 50000, Level 1, State 10

(1 row(s) affected)
*/

The CREATE OR REPLACE syntax does NOT work with DDL triggers. If you execute this the first time it will run fine.

CREATE OR ALTER TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  

Now run it again, you will get this error

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77]
There is already an object named 'safety' in the database.

So be aware that CREATE OR ALTER does not work with DDL triggers

I submitted a connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/3111866

I want them to either disable CREATE OR ALTER for DDL triggers or they should fix it so it doesn't error on a second run


Tor wrap this up, let's drop all these objects we just created, we will do that by using the Drop if exists syntax

--  drop everything by using
--  DROP object IF EXISTS
DROP TABLE IF EXISTS  BooFar
DROP PROCEDURE IF EXISTS  procTest
DROP VIEW IF EXISTS  vwTest

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

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