Anyone have an idea why the varchar data type is limited to be 1 through 8000 or MAX(2GB) but none of the numbers between 8001 and 2GB?
Just one of those little gremlins of "Huh" that vex me.
Hope you all have a great day. Dodged 5-13 inches of snow myself, so mine already has a good start. :-)
It's a byproduct of the design for memory usage.
basically it tell SQL whether it can store a value in the data in a single page of data, or if it needs to use alternative storage for BLOBS.
SQL tries to cache items in pages of RAM memory so that subsequent queries for the same data is faster.
a page is 8,060 bytes. anything larger than that needs to be stored in either multiple pages, or a pointer gets stored and it points to a location of the data on the disk instead.
so that's 128 pages per megabyte.
so say on a SQL server, the most SQL can cache/keep ready for fast access would be 131072 pages per gigabyte of RAM.
it would adversely affect performance if a lot of 2 gig items were stored in the memory pages of RAM, so a pointer might be in the page,and if you request the data, SQL will go thru the extra step behind the scenes of reading the data from disk.
think of this as the same as having reference books on top of my desk for instant access, vs, a sheet of paper indexing where the book is on a bookshelf, so i can go get it if i need it.
if your desk was covered with reference books, you would be less efficient doing your normal work. better to "know" where the data is if you need it, vs cluttering up your workspace with something you don't need as often as other items.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!