SQL SERVER STORAGE/SIZE/CAPACITY RELATED INTERVIEW QUESTIONS ANSWERS

1). What is the fundamental unit of storage in SQL Server data files and it's size? 
Ans: A page with a size of 8k

2). How many (maximum) no. of columns can be created in a MS SQL Table?
Ans: Max Columns per 'nonwide' table: 1,024
Max Columns per 'wide' table: 30,000

3). What is the difference between Wide and Nonwide tables in SQL Server?
Ans: 1) Wide table can contain 30,000 columns, Non-wide table(basic table) can contain only 1024 columns.
2) Wide Tables are considered to be denormalized tables, Non-wide tables are considered to be Normalized tables.
3) Wide tables are used in OLAP systems, Narrow tables are used in OLTP system.
4) Wide table is new feature in SQL Server 2008. To over come the problem of having only 1024 columns in Narrow tables.
5) Wide tables don't work with transactional or merge replication, but Non-wide can work.

4). Maximum how many rows can be in the SQL Server tables?
Ans: According to Microsoft specification:
Rows per table: Limited by available storage

But there are some cases where SQL Server will prevent you from adding more rows

• If you have an IDENTITY column and you hit the end of the range for the data type, e.g. 255 for TINYINT, 2,147,483,647 for INT, some ungodly number that starts with a 9 - possibly the number of inches to the sun and back - for BIGINT, etc. When you try to insert the next row, you'll get error message 815 about overflowing the type.

• If you have a heap with a non-unique index, or a clustered index that is not unique, you won't be able to store more than 2 * 2,147,483,647 unique index key combinations. When you try to insert (2 * 2,147,483,647) + 1 rows with a value of 1 in an INT column that is the only column in a clustered index, you will get error message 666 about exhausting the uniqueifier. This is because the uniqueifier (which helps SQL Server identify a row when there is no true key) is only 4 bytes, which means it can't exceed the capacity of an INT (it does use both positive and negative, unlike IDENTITY unless configure it as such, which is why you get double). Now why you would ever do this, <shrug>... but you could.

• In the VLDB space, a database can only be 524,272 terabytes. Again a very edge case, but if you have a humongous data warehouse then obviously at some point the number of rows - depending on row size - will put you near this limit.

5). What is the maximum size of a varchar(max) variable?
Ans: Maximum size for a varchar(max) is 2GB, or looked up a more exact figure (2^31-1, or 2147483647).

6). What are the difference Between varchar(8000) and varchar(max)?

• Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.
• VARCHAR(MAX) uses the normal datapages until the content actually fills 8k of data as varchar(8000). When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content.
• Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index
• VARCHAR(MAX) has some ambiguity, if the size of the cell is < 8000 chars, it will be treated as Row data. If it's greater, it will be treated as a LOB for storage purposes. You can know this by querying RBAR.

7). How can i query my sql server to only get the size of database?
Ans: Use "YourDatabaseName"
exec sp_spaceused

8). What would be the LEN and DATALENGTH of NULL value in SQL Server?
Ans: Both above function will return NULL as the length of NULL.

9). How much size “Null” value takes in SQL Server?
Ans:

• If the field is fixed width storing NULL takes the same space as any other value - the width of the field.
• If the field is variable width the NULL value takes up no space.

10). What would be the output of the following script?
Select LEN('A value') --Without space at end
Select LEN('A value  ') --With 2 space at end
Ans: Both will return 7 because LEN function not including trailing spaces in SQL Server.

11). How you will find the LEN in above case?
Ans: We can use following tick
Select LEN('A value  ' + 'x') - 1

12). Difference between Len() and DataLength()?
Ans: DATALENGTH()- returns the length of the string in bytes, including trailing spaces.
LEN()- returns the length in characters, excluding trailing spaces.

For example
SELECT LEN('string'), LEN('string '), DATALENGTH('string'), DATALENGTH('string '),
LEN(N'string'), LEN(N'string '), DATALENGTH(N'string'), DATALENGTH(N'string ')

will return 6, 6, 6, 9, 6, 6, 12, 18