I have just been informed by a colleague that if you assign a length to a VARCHAR field in the database, eg VARCHAR(1000), then the database will force the database to hold 1000 bytes of space for each row, whether or not it is needed.  However he claims that an NVARCHAR(1000) field will simply hold the number of bytes required.  I have researched this as best I can, both here and around the web, but I cannot find anything to support this, nor does the SQL Server Help documentation mention this anywhere that I can find.
120547
What is meant by nvarchar?  What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
77077
I was creating a table in SQL Server CE and realized it doesn't support varchar.   Looking this up, I found that "non-Unicode text columns (varchar, char, text) and smallmoney are not supported; although nvarchar, nchar, ntext, and money are supported" as .  Is this true? Why is this, exactly? It would seem that a compact database would support data types that take less bytes to store...I'm assuming it takes more space to save the Unicode characters.   What is the reasoning behind this?
123154
With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.  With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760.  Is there a setting where it will actually show the server memory in the Task Manager?  Or is it a result of them changing how memory is used in SQL Server
118096
My company has recently exceeded our data storage cap (at 102% right now.. :/ ).  A large portion of the data that pushed us over our limit was due to an import of records.  The weird thing about this is that the records only had a couple of fields populated each out of the 200 total fields on the object.  What this tells me is that SalesForce holds the space for each field (since it knows the max-length of each field) rather than populating space only when the field is not null.  Does anyone know if this is the case?  To reiterate, and perhaps present this question more clearly:  If a field has a max character count of 1,000 and only 250 of those characters are actually being used, does SalesForce count the total data storage consumed by that field as 250 or 1,000?
81938
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
71212
When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following:    mysql&gt; create table boolean_test (var1 boolean, var2 tinyint);     Query OK, 0 rows affected (0.10 sec)     mysql&gt; show create table boolean_test;    +--------------+-------------------------    | Table        | Create Table +--------------+-------------------------   | boolean_test | CREATE TABLE `boolean_test` (     `var1` tinyint(1) DEFAULT NULL,     `var2` tinyint(4) DEFAULT NULL   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |    +--------------+---------------------------   1 row in set (0.00 sec)     First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.    mysql&gt; insert into boolean_test values(101,112);   Query OK, 1 row affected (0.03 sec)     mysql&gt; select * from boolean_test;   +------+------+   | var1 | var2 |   +------+------+   |   10 |  112 |   |  101 |  112 |   +------+------+   2 rows in set (0.00 sec)     I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1). Shouldn't I see a 1 for var1? I.e. just 1 display digits?
2459
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:  In SQL Server -  What are some reasons the transaction log grows so large? Why is my log file so big? What are some ways to prevent this problem from occurring? What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?
83262
I was wondering what you do when developing a new application in terms of estimating database size.  E.g. I am planning to launch a website, and I am having a hard time estimating what size I could expect my database to grow. I don't expect you to tell me what size my database will be, but I'd like to know if there are general principles in estimating this.  E.g. When Jeff developed StackOverflow, he (presumably) guesstimated his database size and growth.  My dilemma is that I am going for a hosted solution for my web application (its about cost at this stage), and preferably don't want to shoot myself in the foot by not purchasing enough SQL Server space (they charge a premium for this).
1723
Can anyone confirm this: in a SOQL Where clause, when comparing an ID field to a 15-character string variable, the comparison works.  I didn't expect it to work, but it does and I am surprised.  I can't find any documentation to confirm it.  I thought 18-character ID was always in effect, and comparing 18-char ID to 15-char string would fail.  Example (Country__c is a lookup field):  String tmpID = 'a1he0000000EkAi'; Account acct = [select ID from Account where Country__c = :tmpID];
23110
Somebody did something stupid and tempdb exploded in size.  Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked).    There is lots of free space in tempdb (SQL2014 SP1); tempdb > Tasks > Shrink > Files > (shows how much available space I can release)  I shrink (SSMS 2016) to leave more space then the 'minimum shrink to size'.  But the files do not actually shrink.  I am working with the business owner, I am watching the server (sp_whoisactive, etc) the server is at rest.  Searching for a solution leads me to   I run the following, and it works tempdb, is back to original size.    DBCC FREEPROCCACHE   USE [tempdb]  GO  DBCC SHRINKFILE (N'tempdev' , 2048)  GO   I understand that  releases all the cache data, and impacts plans.  But if it was taking space in tempdb, it should have showed as used space, and not been available for normal shrinking.     Why did this work?   Based on the comments pointing to  Let me phrase this question from the other way.  Tempdb thinks it has space to shed as evidenced by 'minimum shrink to size', the space that won't go away got created in a temp processes that are no longer running. There is a big chunk of allocated and unused space, why can't I release it without destroying the query plan(s)?    Are the query plans or cache not living on allocated disc space, but still being protected from shrinking?   Analogy: 'minimum shrink to size' shows me the glass is half full/empty but it is not possible to transfer to a smaller glass without breaking the glass and loosing almost all of the contents.    A couple years later, same issue on a SQL 2014 instance, less emergent allows time to try experiment.  If you could somehow identify the query plan, you might be able to delete just that plan releasing the hold on the pages, and then be able to shrink tempdb.  I am looking for ways to accomplish this but have not yet been successful.  Maybe you can use  to see what is on that page so you can address it?   DBCC TRACEON (3604)  -- By default, the output is sent to the errorlog. If you want the output to come back to your current connection, turn on trace flag 3604. GO DBCC PAGE ([tempdb], 1, 1, 3); -- detailed dump of the PFS page GO    I see the last page is 937, so I go look at it.   DBCC PAGE ([tempdb], 1, 937, 3); GO   Nothing clearly an issue there, The last few pages look empty.   I have tried the suggestion by  Multiple attempts did not even free 1MB.     I ran into this problem yesterday, using the SSMS task to shrink files never accomplished anything, then I re-read the documentation on Shrink File and realized I needed to try this:   DBCC SHRINKFILE (N'tempdev' , NOTRUNCATE) -- Move allocated pages from end of file to top of file DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) -- Drop unallocated pages from end of file      I did this for all 8 of the tempdev files and slowly, over time, it finally recovered about 80% of the space. I was truly surprised by this as nothing else had worked. I had to run a job every 15 minutes through the day but it eventually recovered the space   On a SQL 2014 instance I have 'naturally' cleared the cache when a large database was rebuilt with a clustered column store indexes (nightly process). The same SHRINKFILE that would not do anything the previous day works as expected in the morning.   USE [tempdb] GO DBCC SHRINKFILE (N'tempdev' , 2000) GO   Maybe if I could recreate the problem on demand on a 2016+ instance with Query Store, it would be easier to track down exactly what is happening?
82037
I'm the only one working on this server and followed documentation about optimizing SQL memory usage when I saw that SQL was using all of the memory available of the server.  One of the steps I did is to fix the max memory size to 20 Gb (server for test purposes with 32 Gb memory).  After tuning, yesterday, SQL Server was only using 3-4 Gb but today, it has again reached the max memory usage I allocated.  Why is SQL Server keeping all 20 Gb? What will happen when users will begin to use the server if the max memory size is already used?
115024
I have a table with a row Page_Number_N some of the rows have values and others just have a space. For example record 4 is just a space. Field values are Strings.  1 2 3  5   When I start an edit session and delete the space it deletes it. However, as soon as I save edits, the space in the data returns.  I have tried removing the space in the field calc without edit session turned on and the result is the same.  I need this record to equal "" ie. No spaces or null and I can't seem to get ArcGIS Desktop to cooperate.  Does anyone know how to fix this?
118249
I have a basic understanding of  (I come across it often when I'm migrating WordPress installs) and I just discovered .  Given that serialize causes so much trouble with replacing strings in the database (eg. editing an optinos field directly), why does WordPress prefer serialize to (the seemingly similar) json_encode, which doesn't insert the string length into the stored value.  My first thought was that it may provide some security or error-checking benefits - WordPress will ignore the option if the string lengths are incorrect.. but I'm interested in a qualified explanation :)
24518
Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?
92690
I know that if you create a custom object, with an Auto-number Name field with a format, e.g. MYC-{0000} you will be able to create records from MYC-0000 to MYC-9999.  The moment you create 10,000 records Salesforce happily extends the range and you can then create MYC-10000 to MYC-99999.  My question is, what is the limit to this - for obvious reasons its a difficult one to test. I know instances that have exceeded 999,999 e.g. MYC-1000000 but am interested to know how far this extends to.  EDIT: When the field has a data type of Text the maximum length is 80. Is it safe to assume that the same is true for Autonumber (though that would be a very big number)?
103452
In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.  So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file). But after creating the database  I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)  But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?  In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.  Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?  EDIT:  From microsoft shrink related article: Using DBCC SHRINKDATABASE you can not shrink database below its initial size.  My question is Can I change the initial size of existing database itself?  EDIT:  I have about 5 sql server instance. All the database where created with default size of 3 mb for data and 1 mb for log(about 3 years back). Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB(mdf file itself as i insert so much data in it). So when database is large I will shift unimportant and old contents from this database to another server as it is not required in that server for me. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. Now I  want to specify database size to be reduced to 2 GB so that i will get 1 GB free space(Also 2 Gb is the ideal size for this database), Which can be used to avoid autogrowth till next 1 Gb of data is filled. Also i have set 100 MB auto-growth for this kind of large database which has more writes. So instead of specifying size as 2 GB in shrinkfile command,if it was possible to change initial size to 2 GB (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. Now i am planning to set autogrowth etc. I cant again create new database with new initial size and shift data. So I was asking whether I can change the Initial size for existing database?
119820
I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB. SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again. The files are growing at about 900MB an hour. Running the SQL below shows that all the space is in Precreated Data - so it's not even actually storing large amounts of data, just allocating space to use later.  SELECT state_desc, file_type_desc, COUNT(*) AS [count],    SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]    FROM sys.dm_db_xtp_checkpoint_files   GROUP BY state, state_desc, file_type, file_type_desc   ORDER BY state, file_type     File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.  Is there a setting in SQL Server that controls this file precreation? Is it safe to set a limit on the autogrowth for the file group?