How do we technically know ?

Posted by Sarvesh under Sql Server on 9/17/2009 | Views : 2091 | Status : [Member] | Replies : 3
hi sql experts

1. How do we know the limitations of "No.of columns" per table ?
2. In one sql server book, they says maximum row size is 8060 Bytes(But its not allowing 8060). why ?

please clear my doubts

thanks in advance
sarvesh s




Responses

Posted by: Pandians on: 9/17/2009 [Member] [MVP] Silver

Up
0
Down
Hi

1.Maximum columns in a Table:
DECLARE @intSerial	INT,

@strSQL NVARCHAR(MAX)

SELECT @intSerial = 1
SELECT @strSQL = 'CREATE TABLE #TBLSAMPLE1(ID INT IDENTITY,'
WHILE (@intSerial <= 1024)
BEGIN
SELECT @strSQL = @strSQL + 'COL' + CAST(@intSerial AS VARCHAR) + ' VARCHAR(10),'
SELECT @intSerial = @intSerial + 1
END
SELECT @strSQL = @strSQL + ')'
EXEC SP_EXECUTESQL @strSQL

Error Message :
Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column 'COL1024' in table '#TBLSAMPLE1' exceeds the maximum of 1024 columns.

2.Maximum size of a Row:
1. According to MS specification, Maximum size of a Row in table is : 8060 Byte(s).
2. But it will allow only 8052 Byte(s)
3. What about the remaining 8 Byte(s)?

Normally, 8 Byte(s) reserved by SQL Server for internal reference. The 8 byte(s) will be used for Header level information. So, the remaining 8052 Byte(s) only we can use.

Total Bytes : 8060 Byte(s)
Reserved : 8 Byte(s)
Remaining : 8052 Byte(s)


So, we can use 8052 byte(s) only. See the following sample..
CREATE TABLE TBL_MAX_ROWS

(
COL1 CHAR(1000),
COL2 CHAR(1000),
COL3 CHAR(1000),
COL4 CHAR(1000),
COL5 CHAR(1000),
COL6 CHAR(1000),
COL7 CHAR(1000),
COL8 CHAR(1000),
COL9 CHAR(60)
)

Error Message :
Msg 1701, Level 16, State 1, Line 2
Creating or altering table 'TBL_MAX_ROWS' failed because the minimum row size would be 8068, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

But you can use 8052 byte(s)
CREATE TABLE TBL_MAX_ROWS

(
COL1 CHAR(1000),
COL2 CHAR(1000),
COL3 CHAR(1000),
COL4 CHAR(1000),
COL5 CHAR(1000),
COL6 CHAR(1000),
COL7 CHAR(1000),
COL8 CHAR(1000),
COL9 CHAR(52)
)

Command(s) completed successfully.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 9/17/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Sarvesh-

Check This Link for detailed Information:

http://msdn.microsoft.com/en-us/library/ms143432.aspx


Thanks & Regards
Lakhan Pal Garg
Free Code Snippets
http://lakhangarg.blogspot.com/

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rtpharry on: 9/18/2009 [Member] [MVP] Bronze

Up
0
Down
Hey,

I did this search - it depends what version of sql server you are talking about?

http://weblogs.sqlteam.com/mladenp/archive/2007/07/24/60267.aspx

This says the maximum columns in a base table is 1024.

I had to look up what a base table was and it basically seems to means a permanent table rather than a temporary table that you sometimes use.

http://books.google.co.uk/books?id=93J-cHpIQlkC&pg=PA48&lpg=PA48&dq="base+table"+sql+server&source=bl&ots=TXDCcH8jBn&sig=tYxCelKIoi6uGmsfefsJUT72z3c&hl=en&ei=hDyzSv2JIpej4ga_yJR9&sa=X&oi=book_result&ct=result&resnum=9#v=onepage&q="base table" sql server&f=false

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response