There are many, many details one can learn about Microsoft SQL Server over a very long career in data science. This blog, with over 120 posts, only begins to scratch the surface!
In this weeks tutorial, I’d like to talk about just a few unique factoids you might find interesting:
Factoid # 1: Table names (and any other object name) can be up to 128 characters
That’s probably more than you would ever need, but now you know there is indeed an limit!
There are many different kinds of objects in SQL Server, such as:
All of these objects can have a name that is up to 128 characters.
Factoid # 2: Tables can have up to 1,024 columns
Wow, that’s quite the many. Again, that’s probably much more than you would ever need.
I’ve heard of something called a wide table, which allows up to 30,000 column. Have you heard of wide tables and have used them? Let me know in the comments!
Factoid # 3: Stored procedures and functions can have up to 2,100 parameters
It’s very common to use parameters in your stored procedures and user defined functions. If you need, you know, a couple thousand parameters, SQL Server has you covered.
In the case of stored procedures, that’s 2100 parameters that can be either input or output parameters.
Factoid # 4: The N value in data types CHAR(N) and VARCHAR(N) can be up to 8,000
In the case of VARCHAR, you could also specify an N value of MAX. This indicates the data type can store values up to 2^31-1 bytes, which is 2GB.
The MAX specification cannot be used with regular CHAR. It only applies to VARCHAR.
Factoid # 5: The N value in CHAR and VARCHAR is not the number of characters, but instead the number of bytes that can be stored
This is an important distinction to make. Many of the standard characters we use in regular speech use something called single-byte encoding, which means one character per byte. So you would be forgiven if you thought N meant the number of characters that can be stored because most of the time, the two are one in the same.
But things get interesting when we think about multi-byte encoding, which means one character requires two or more bytes of storage. An example of a multi-byte encoding is UTF-8.
If we specified CHAR(100) with the UTF-8 encoding, for example, we would probably only be able to store somewhere around 50 characters.
Think about this next time you need to store character string values!
Next Steps:
Leave a comment if you found this tutorial helpful!
Do you know how output parameters work in stored procedures? Click the link to find out!
Also make sure you check out my full tutorials on user defined functions. They are very helpful tools that you should understand:
User Defined Functions in SQL Server: A Complete Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
