How Many Tables Can I Have In SQL Azure DB & SQL Server
Hello Dear Reader! This is my first full fledged blog for my friends here at the PFE blog. I wanted to start off with something interesting. A friend asked a very intriguing question yesterday. “How many tables can a SQL Azure Database have?”. The context was a migration from MySQL to SQL Server and they had over 100K tables in the MySQL database! There is a simple answer to this, this is documented on MSDN (click here to read, https://msdn.microsoft.com/en-us/library/ms143432.aspx ). Here is an exert from the page:
|Tables per database||Limited by number of objects in a database||Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.|
Interesting number. It just so happens that 2,147,483,647 is the maximum positive value for an integer (click here to read that, https://msdn.microsoft.com/en-us/library/ms187745.aspx ). The fact that they match is not on accident. That is because object ID’s are defined using an integer value. We could leave it at that, but I have a lot of delayed flights and I’m going to start by proving this out. We shall start by running a quick query to confirm my suspicion on DMV sys.objects.
As you can see Object_ID is an integer value.
“So Balls”, you say, “Is that it? You’re just going to tell us the max value of an integer, run sp_help, take a screen shot, and end of blog?”
Of course not Dear Reader! That would be lame. So how will we prove it out? In order to do so we will have to try to create more than 2.1 billion objects in a database. I’ve got some spare time after all, there are a LOT of flight delays.
This is a pretty simple demo. We’ll start by creating our database numberOfObjectsInADB. Next I’m going to validate how many objects we have in our database. In this case I found 82 in SQL Server and 93 in SQL Azure DB. This means I cannot create 2,147,483,647. But that’s exactly what we are going to try to do. I’m going to loop over 2 billion times to not only create all the objects I can in a database. For my local SQL Server Instance here is the scripts to use.
/* Create our database */ use master go if exists( select name from sys.databases where name ='numberOfObjectsInADB') begin drop database numberOfObjectsInADB end go create database numberOfObjectsInADB go use numberOfObjectsInADB go /* Count how many default objects we have in a database */ select count(*) from sys.objects go /* Currently we have 82 The max size of an integer is 2147483647 This means we can add 2147483565 objects I'm going to try to add 2147483647 */ declare @i int, @x int, @sqltext nvarchar(4000) set @i = 0 set @x = 2147483647 while (@i<@x) begin set @i=@i+1 set @sqltext = 'Create table dbo.mytable' + cast(@i as nvarchar(200)) + ' (c1 int)' exec sys.sp_executesql @sqltext end
For my P4 I create the database and the tier I want, and they I just run the loop.
/* Count how many default objects we have in a database */ select count(*) from sys.objects go /* Currently we have 93 The max size of an integer is 2147483647 This means we can add 2147483554 objects I'm going to try to add 2147483647 */ declare @i int, @x int, @sqltext nvarchar(4000) set @i = 0 set @x = 2147483647 while (@i<@x) begin set @i=@i+1 set @sqltext = 'Create table dbo.mytable' + cast(@i as nvarchar(200)) + ' (c1 int)' exec sys.sp_executesql @sqltext end
As I said, lot of delays on the flights today.
“So Balls” , you say, “Are you really going to create over two billion tables!!!”
NO, no I am not. I know I wrote out the script, and it looks nice. But NO. Dear Reader let’s do a little math.
IF I create 1 table a second, with a goal of creating 2,147,483,647 then it would take a lot of time. Here’s a breakdown:
Remember our motto “trust but verify”. How slow is it? Let’s do a quick test to establish a baseline and adjust our math. I ran the above script for a little over 27 minutes and generated 628,841 tables (going with the highest number out of the two scripts from above).
Let’s adjust our math. 628,841 Tables divided by 27 minutes give us a rate of a little over 23,290 tables per minute. That gives us a little over 388 per second, that’s better than 1 per second so how long will this run?
It’s better than we estimated but not great. If we couldn’t wait for 68 years for a demo, then we probably wouldn’t want to wait 64 days for a demo. Hopefully my flight isn’t delayed that long!
WHAT ABOUT WITH IN-MEMORY OLTP
I hear you Dear Reader, never give up never surrender! Memory seems like it should be faster than a disk based table. Will the math support it? Let's find out.
Let’s say we keep the size of the table small. One integer value worth 4 bytes. We will also need to declare one index as well, say another 4 bytes. Here’s how the math breaks down for that:
|17179869176||Bytes (One Row + One Index, 4 bytes each total 8 bytes)|
If I upgraded my SQL Azure DB to a P11 I could handle this size of InMemory OLTP storage, but I'm not going to do that just yet. I could take this up to my Azure DS4 VM with 28 GB of RAM, but before I waste a lot of compute let’s do a smaller insert and see how quick we get this to happen. So let's try this two ways and compare. I'll do it on my P4 SQL Azure DB and also on my local instance. We’ll make a simple script that creates In-Memory OLTP tables that are non-durable and I will let it run for 10 minutes or 10,000 tables (20,000 objects). Which ever we get to first.
First here is the script for the local instance, please note you will have to change the drive letter if you do not have an E drive.
use master go if exists( select name from sys.databases where name ='InMemoryOLTPnumberOfObjectsInADB') begin drop database InMemoryOLTPnumberOfObjectsInADB end go create database InMemoryOLTPnumberOfObjectsInADB go alter database InMemoryOLTPnumberOfObjectsInADB add filegroup imoltp_Javris1 contains memory_optimized_data alter database InMemoryOLTPnumberOfObjectsInADB add file (name=N'inmoltp_Jarvis2', filename=N'E:\Databases\inmoltp_Jarvis2') to filegroup imoltp_Javris1 go use InMemoryOLTPnumberOfObjectsInADB go declare @i int, @sqltext nvarchar(4000) set @i = 0 while (@i<10000) begin set @i=@i+1 set @sqltext = 'Create table dbo.mytable' + cast(@i as nvarchar(200)) + ' (c1 int identity(1,1) NOT NULL PRIMARY KEY NONCLUSTERED) with (memory_optimized = ON, durability=schema_only)' exec sys.sp_executesql @sqltext end
Inside of my P4 SQL Azure DB I will just need to run the loop.
declare @i int, @sqltext nvarchar(4000) set @i = 0 while (@i<10000) begin set @i=@i+1 set @sqltext = 'Create table dbo.mytable' + cast(@i as nvarchar(200)) + ' (c1 int identity(1,1) NOT NULL PRIMARY KEY NONCLUSTERED) with (memory_optimized = ON, durability=schema_only)' exec sys.sp_executesql @sqltext end
So this avenue is a bit of a bust as well. We created as schema_only for around 10 minutes. At that rate of around 3 objects created per second it would take us slightly over 138 days to run out of objects!
|2,147,483,647||Tables & Indexes|
|1,073,741,824||Divided by 2 because we get 2 structures|
GET TO THE POINT
The original question that was posed was how many tables can SQL Azure Database have. The answer is in objects. It is a collective total of 2,147,483,647 objects. This applies to Azure and SQL Server.
What I’ve done here is, hopefully, show you that running out of tables or objects should not be an overwhelming concern. Even with small tables and a system 100% dedicated to creating objects, you should be fine. This is true for SQL Azure Database as well as SQL Server 2012, 2014, and 2016. Just in time, my final flight is finally boarding.
As always Dear Reader, Thank You for stopping by.