SQL Server: Is there record limit for SQL server database table?

Question

Not an IT guy, not DBA. But I do build application sometimes, if I use SQL server as backend database, is there record limit for each table? For example, excel spreadsheet has maximum of 1,048,576 rows.

So is there such limit for SQL server table? I know it can handle a lot, but is it unlimited?

The question may sound silly, but I am really curious. This forum has a link Activities under profile page. If I use a similar table to record users' activities (more than 1000 users), there will be a lot of records over the time.

Thanks.

Edit: Suddenly I have another curious question, for organization SQL server, employees can only access the database from work computer and organization network too. Another curious question, how is SQL database set up? Does database always require password? Can SQL database be set up without password? The database can be only accessed from work computer anyway (and from organization network too), employees don't really need the password. It is non sensitive database, and it can be open to all employees.

Answer 1

So is there such limit for SQL server table? I know it can handle a lot, but is it unlimited?

See here: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

Suddenly I have another curious question, for organization SQL server, employees can only access the database from work computer and organization network too.

That depends on the network topology. If you expose your SQL Server instance on the internet, anyone can access it, if they have a username and password. However, exposing SQL Server on the internet is a very bad idea.

Another curious question, how is SQL database set up? Does database always require password?

No, a database never requires a password. Normally, you authenticate to the SQL Server instance. You can do that in two ways: Windows authentication or SQL Server authentication. In the former case, you have logged into Windows, and Windows passes your authentication token to SQL Server without you specify ing username/password anew. You must however have been given access to the SQL Server instance, either directly to your login or an AD group you are a member of. With SQL authentication you specify username and password, and this is the only option if you are not logged to the domain where SQL Server is installed. Note that by default, SQL authentication is disabled.

Once you are connected to the instance, you still need be permitted to access the individual databases. You don't need a password to access a certain database, but you need to have permission, either directly to your login or to an AD group you are a member of.

But just because you have been granted access to a database, does not mean that you can do a lot in it. You still need permissions to access tables, and this access can be granted per table or on higher level. And there is different permissions for reading and writing data.

The database can be only accessed from work computer anyway (and from organization network too), employees don't really need the password. It is non sensitive database, and it can be open to all employees.

The normal way you would do this is to add an AD group that all employees are member of as login on the instance and as a user in the database. Then you need to grant the desired permissions to this group.

Finally, when you ask entirely disparate questions like you do here, it is better to post two questions.

Answer 2

Hi @VAer-4038 ,

>So is there such limit for SQL server table? I know it can handle a lot, but is it unlimited?

SQL Server does not limit the number of rows in a table (different from columns), nor does it have a specific maximum number of rows.
See documentation: Maximum capacity specifications for SQL Server.

"Rows per table -limited by available storage"

Therefore, the maximum number of rows in the table is limited to storage. But SQL Server Express has a database size limit of 10GB, it indirectly limits the maximum number of rows in a table.

>Does database always require password? Can SQL database be set up without password?

When connecting to SQL Server, you do not need to provide a password if use Windows authentication.
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server