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 (m...

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

Related

TECHNICAL 5404148562176521745

Post a comment

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item