Blog Post

Core Infrastructure and Security Blog
6 MIN READ

How Many Tables Can I Have In SQL Azure DB & SQL Server

Bradley_Ball's avatar
Bradley_Ball
Icon for Microsoft rankMicrosoft
Mar 15, 2019

First published on MSDN on Aug 22, 2016
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.

sp_help '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.



DEMO TIME…KINDA

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:

2,147,483,647 Seconds
35,791,394 Minutes
596,523 Hours
24,855 Days
68 Years




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?


553,228 Seconds
92,204 Minutes
1,536 Hours
64 Days
0.175427399 Years




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:


2147483647 Tables
17179869176 Bytes (One Row + One Index, 4 bytes each total 8 bytes)
16777215.99 KB
16383.99999 MB
15.99999999 GB




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
11,930,464 Seconds
198,81 Minutes
3,314 Hours
138 Days
0.378 Years




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.

Thanks,



Brad

Updated Apr 28, 2020
Version 4.0
No CommentsBe the first to comment